This customer was a large public utility that was transitioning to a new custom CRM solution. One of their main challenges and inefficiencies was generating reports around daily consumption data. Alianz deployed a custom Business Intelligence (BI) solution to graphically represent this data to decrease the amount of time required to generate these reports and increase the collaboration of business units. Their CRM solution primarily focused around customer management but lacked focus upon their
business critical consumption information. SQL Server 2008 allowed Alianz to design and create a custom Business Intelligence (BI) solution for them. The BI solution has allowed for faster business processes and more accurate reports.
Value to the Customer:
The customer had 10 years of historical consumption information and daily consumption information moving forward. While they could see there was great business value for analyzing large data sets, they were not able to fully leverage this historical data with their current systems in place. With the creation of the BI solution, the customer has a definitive source of data for consumption information. This has allowed each business unit to compare and to collaborate around consistent and reliable data, allowing each business unit to gain critical insight. Business units were also previously responsible for their data computation and report generation. SSAS and SSRS have allowed them to reuse reports in a common format with graphical data representations. With the proper usage of business intelligence, the creation time of regular reports has gone from weeks to minutes.
The following gives insight into the business challenges of this organization and how Alianz’s custom solution was able to streamline these process.
Pre and Post Implementation:
• There was no single source of consumption history. There were multiple systems that reloaded consumption data on a regular basis making incorrect assumptions on the transactional data.
• No ability to compare budget to actual on a consistent and simple basis.
• The original process took weeks to align the data.
• No ability analyze the actual consumption data.
• No ability to drill down on actual data, summarized totals only which limited analysis.
• No monthly and no normalized data available.
• Customer is now able to view a single source of vetted, billed, monthly and normalized data that reconcile to one another and to the source.
• Ability to compare forecast to actual data easily.
• Ability to use Excel to analyze and drill down on data to spot trends by customer type.
• Ability to get a list of actual customers by customer type in minutes, which prior would have taken weeks.
• Ability to make informed decisions using data available and to provide accurate historical data to customers.
• Ability to provide accurate and repeatable results.
Alianz created an ETL process to load consumption information into a data warehouse using SSIS packages using SQL Server 2008 R2. The data warehouse is used as the definitive source of data for both SSAS data cubes and SSRS reports.
The BI solution comprised of 3 major components of SQL Server 2008:
• SQL Server Integration (SSIS). Custom SSIS packages were primarily responsible for the Extract, Transform, and Load (ETL) of the Billed Consumption. They allowed for consistent and repeatable methods for screening and loading the data into the SQL databases.
• SQL Server Analysis Services (SSAS). SSAS allowed us to aggregate data as needed for common reports, thus reducing the time needed to present the report. It also allowed us to quickly slice data and present the data via MDX. An example is to show the aggregated Billed Consumption for 2010 across multiple regions.
• SQL Server Reporting Services (SSRS). SSRS standardized common reports used by multiple business units. Since the SSRS reports used SSAS via MDX queries instead of the SQL databases, the time required to generate the reports improved significantly. SSRS also created a web based access point for the users to access the reports.