BPDM

Business Performance Data Mart

Executive Summary

This technical white paper depicts the challenges and the corresponding solutions to make deliberate business decisions from traditional reports. These solutions include providing required inputs to the top level management to analyze the trends and define the organization’s strategies.

Introduction

In this rapidly growing world, traditional reports may not be sufficient to perform analytics for future strategies to stay ahead the other organizations in the competition. Sufficient amount of time would be required to consolidate the various reports generated from different transactional source systems. As the organizations are growing, some of the decision making needs to be driven from past results.

Proposed Solution

Business Intelligence (BI) provides a solution for the organizations to construct strategic business decisions. BI uses technologies, processes and applications to analyze mostly internal structured data and business processes while the intelligence is gathering, analyzing and broadcasting information with or without support from technology and applications, and focuses on all-source information to support decision making.

Business Performance Data Mart (BPDM) is designed to accommodate the consolidated data from multiple source systems as a single access point.

This data mart turned into information factory to provide analysis. Data is being pumped from various source systems using Extraction, Transformation and Loading (ETL) process on a daily basis. Loading the data at most granular and aggregated to support the analytical needs. This data mart designed using following technologies.

Components Technology
Database SQL Server 2005 Enterprise edition
ETL Process SSIS (SQL Server Integration Services)
Reporting Tool Hyperion
Scheduling Tool SQL Server 2005 Agent
Solution Details

Business Performance Data Mart (BPDM) designed to accommodate the future needs of the organization to act as a consolidated centralized repository. Transaction Data, Invoice & Credit, Product Output subject areas provide the solution to overcome the traditional reporting system of an organization.

Extraction Transformation and Loading (ETL) Process extracts the data from different source systems and loads it into staging database. Transformation rules are applied while loading the data into staging database to integrate it from different flat forms. To simplify the Transformation rules, common rules are applied from stage to target loading and some of the transformation rules are applied from source to stage loading. This improves the loading performance.

ETL process consists of loading Dimensions, Facts and Aggregate tables. The process is defined to start loading first Dimensions, Facts and Aggregates. Independent dimensions/Facts/Aggregates are configured to load parallel accordingly

Custom logging information facilitates to debug the ETL process to identify the cause for failure, the user, date, time, number of rows extracted, number of rows loaded, number of rows rejected.

‘Load Validation’ process is implemented to validate the loads. This process validates the extracted and load data then raises an ‘alert’ if the defined threshold limit is exceeded. These alerts are configured to be received based on the configured recipients.

The ETL & ‘Load Validation’ processes are automated to kick off sequentially one by one, at the specified time every day.

Transaction Subject Area

Invoice & Credit Subject Area

Business Benefits

Business Performance Data Mart (BPDM) acts as a single consolidated repository of all organization’s billable and non-billable transactions. Given below are the benefits of improving business:

  • Operational reporting includes ‘Monitoring Internal Transaction Activity’, ‘Customer Usage of Products’. Monitoring internal transaction report provides a view of transactions run by internal users that do not generate any income. This report will help to determine if internal access is abused for personal gains. Customer usage of product report provides insight into how and where the products are used by customers . This helps to understand the current usage and potential for new product up-sell and cross-sell with the customers.
  • Product Usage Analysis Report provides revenue and number of transactions by customer and product.
  • Sales Representative Performance Analysis provides revenue and number of transactions by sales representative and time.
  • Hit Rate by Product, by Customer, by Time
  • Customer Analysis Reports offer Customer, User, Top/Bottom Customer analysis.
  • Revenue Analysis includes Month-To-Date, Year-To-Year, Quarter-To-Quarter, Average Revenue over a period of time Analysis.
  • Tax Related Analysis Report accommodates the flexibility to apply the different rate of taxes based on the location/region.
Conclusion

Organization Business Intelligence (BI) supports business executives and allows them to take business decisions by looking at the company as a whole instead of in terms of the departments that it comprises of. It speeds up querying & reporting which is not used by the transaction processing systems. It also makes it easy to write and maintain an environment where there is comparatively little knowledge of database technology

About Adaequare

Adaequare is a pioneer in cutting edge Data, Product and Test Engineering Solutions. Established in 2001, Adaequare is a CMMi Level 3 Certified company and has global presence across 3 different locations. Our thrust to be creative is our key to success in this contemporary world.

Data drives Performance! At Adaequare, we are committed to deliver state-of-the-art business solutions through engineering excellence. We enable organizations to improve business performance and realize higher returns on investments through the potential of our business solutions. With innovative, data-driven services and products, we ensure that our customers stay ahead of their competitors through rapid and effective decision making.

Our Data, Product and Engineering capabilities, along with our expertise and experience help organizations improve their business processes continually. We deliver value with cost-effective, high value-based and quality solutions that can precisely address your business challenges

Adaequare means ‘equal to requirement’ and it signifies our focus on providing precise, cost-effective solutions with outstanding quality. We deliver what it takes to turn your ideas into value – to your customers and employees alike!

Get in touch

For more details about our services and solutions, contact us now

Your Name *

Your Email *

Your Message

Download (PDF)

© ADAEQUARE 2014

The information contained herein is of a general nature and is not intended to address the circumstances of any particular individual or entity. Although we endeavor to provide accurate and timely information, there can be no guarantee that such information is accurate as of the date it is received or that it will continue to be accurate in the future. No one should act on such information without appropriate professional advice after a thorough examination of the particular situation.