Case Study

A Business Intelligence Solution for an Energy Conservation Company in Texas

Client

The Client is an Energy Conservation Company based in the state of Texas who has for three decades revolutionized how their clients view, consume and use energy nationwide.

Industry

Manufacturing

Business Challenge

The client wanted to study and analyze the travel cost incurred by its employees when visiting its customers. The below is the list of challenges faced by them

• Manual extraction of raw data from Vendor system.
• Loading extracted data from various travels (Air, Hotel, Car) into an excel Workbook.
• Processing data and building calculations.
• Difficulty in getting a single view.
• Loss of Time and resources due to repetition of work.

Solution

Use the Microsoft BI technology to extract data with SSIS, store the extracted data in Sql Server database, build customized views, process it with the help of SSAS and use Excel to display.

• Extracting Data – Packages were created in order to retrieve data from the Vendor System into the Staging Database. Jobs were created to run these packages on a monthly basis. The Staging database is deleted and reloaded every time the package run’s with fresh data.
• Storing Data – Data stored in the Staging database is then moved to the Data Warehouse with the help of Stored procedure which run’s after the Staging Packages run. Only New and Updated data is inputted in the data warehouse and Audit trail is created in order to know the details of the load.
• Building Analytical Cube – This is where the all the calculations happens. The cube dynamically calculates values based on the criteria selected by user.
• Excel Reporting – Excel is used as a BI tool to report the data processed by the cube.

Below Diagram shows the architecture of the system:

Benefits

The Client is not only able to get all their data stored and secured in one place but also to take business decisions intelligently by analyzing the data in the dashboards. This has helped the client save time and resources by investigating the Travel of their employees.

Technology Used