Delivering Revenue Reports Faster and Reducing Costs by Automating Business Rules
Published: December 2010
The following content may no longer reflect Microsoft’s current position or infrastructure. This content should be viewed as reference documentation only, to inform IT business decisions within your own company or organization.
The Microsoft IT Business Intelligence Engineering organization has created the Business Rules Engine (BRE) tool, which consolidates data from different sources and automates key processes. As a result, internal IT teams can deliver revenue reports much faster and can save millions in expenses.
Article, Microsoft Word file, 98 KB
Product & Technology
The Microsoft IT Business Intelligence Engineering organization develops solutions to improve efficiency and productivity for internal Microsoft teams. This article shows how the organization developed a new tool to automate key processes used in developing up-to-date financial reports.
Dealing with a Flawed Report Delivery System
For Microsoft vice presidents and other executives, getting accurate, up-to-date financial data on new products is critical. Armed with the right revenue and forecast data, they have a better ability to make smart, timely business decisions.
One internal Microsoft IT team, however, was struggling to deliver revenue reports to executives on time because of the time-consuming, manual processes they were using. The Scorecard Team, which had 27 developers, relied on hard-coded SQL Server™ Stored Procedures to gather the updated revenue metrics needed for reports. This was because the developers were dealing with many system shortcomings. For example, the developers had no user interface for entering Key Performance Indicators (KPIs) and metrics, no ability to generate dynamic cubes, and no multi-tenant capability.
The team also lacked the ability to add new Multidimensional eXpressions (MDX) functionalities. MDX is the language needed for querying and manipulating the data stored in Online Analytical Processing (OLAP) cubes. Without the MDX capabilities, the development team could not create OLAP cubes fast enough.
Manual, Time-Consuming Processes Lead to Delays
The end result was that developers routinely took more than 20 hours to create one metric for a revenue report. Additionally, up to one full day per developer was required to create or change metrics or KPIs. That process was further delayed by QA and project management, so report turnaround was rarely on schedule. "The overall system performance was not good, and it affected report delivery times," says Celestine Joseph, Senior Developer, Business Intelligence Engineering, Microsoft IT. "It took the developers around 6 to 8 months to deliver a report. Important revenue data was not getting into the hands of executive teams quickly enough."
It was also nearly impossible to validate metrics, because of the transformations that took place at various stages of the data gathering process. This led to problems such as different numbers appearing for the same metrics, in two different reports. "There was often no single version of the truth when it came to the final data," says Joseph.
Due to the manual and time-consuming nature of incorporating report changes that executives requested, these change requests presented further challenges.
To solve these issues, Microsoft IT set out to create and implement a new data consolidation tool that would greatly reduce the time required for data gathering, which resulted in much faster report turnaround. Specifically, the team wanted to integrate a centralized rule set for metrics and KPIs to support a single version of data. Other goals included improving performance and lowering development and maintenance costs.
Creating the Business Rules Engine Tool
In 2008, a team of architects and testers within the Business Intelligence Engineering organization in Microsoft IT created the Business Rules Engine (BRE) tool. This new tool, which integrates with existing revenue and expense systems, consolidates data from different subject areas (i.e. revenue and expenses) across multiple data sources such as SQL Server databases and OLAP cubes. The tool quickly organizes this asymmetrical data into user-defined metrics and KPIs.
This automated tool runs on standard SQL Server hardware, and contains built-in monitoring capabilities for database query and logging volume, as well as MDX query volume and memory usage. It offers a centralized database of shared rules, which supports a single unified version of data.
Using the BRE tool, users can easily configure KPIs, metrics, and scorecards in relational tables without the need for developer or other IT intervention. Defined metrics are query-agnostic simple metadata, and users can define those metrics as a list of fields and filter conditions.
In addition, the BRE tool automatically generates MDX queries and stores the query results back to the fact table, which is used to generate OLAP cubes. This approach provides a performance advantage in terms of extracting metrics and querying the reporting cube. And because it is metadata, it is simple for users to validate metrics and data lineage. "Essentially, we were able to engineer a fully automated, intelligent product that removes the need for a lot of the manual data gathering and analysis processes," says Joseph. "It simply runs by itself without direct user intervention, even with more metrics and volume added to it."
There are currently three internal Microsoft groups using the BRE tool, including the Managed Innovations team.
Automating Key Processes
Because BRE can consolidate and configure data for financial reports, key processes such as MDX querying and KPI configuration have been automated. Because of this automation, the Scorecard team has been able to gain impressive operational efficiencies. As a result, the team of 27 developers has been reduced to two, with the other 25 employees shifted to other teams. "Because of the automated capabilities of the BRE tool, we were able to move the developers to other areas within IT, where they can add more value," says Joseph.
Using Better Performance to Deliver Reports on Time
With the BRE tool, performance is greatly improved for metric extraction and OLAP querying through MDX. Using MDX, the solution has been able to generate metric runs in only milliseconds compared to the previous system's 15 minutes—primarily due to the OLAP aggregations that are now possible. Instead of taking 20-plus hours to generate a metric for a report, Scorecard developers can perform that task in only two and a half minutes. The Microsoft Sales RPS group, which initially needed 120 hours to generate a revenue report, can now finish the job in just over two hours. "Using the Business Rules Engine tool, we were able to deliver product revenue scorecards to executive teams on time for the first time ever," says Joseph. "Overall, report turnaround time can be as fast as the user wants it to be, because it is a user-driven metadata system that does not require much developer overhead at all."
Another advantage is that data quality is greatly improved, because the simplified, centralized database configuration using shared rules enables the data to match on all reports. "It's very easy to validate metrics numbers on reports now," says Joseph. Also, BRE components can be easily integrated with Microsoft reports, pending implementation.
Saving $1.2 Million Through New Efficiency Gains
Another benefit of the performance improvement was a substantial reduction in previously-needed operating costs such as troubleshooting, query optimization, and operational support during peak server times. Additionally, the new tool has contributed to process improvements such as the elimination of more than 2,000 change requests each year.
Many hardware costs were also eliminated, because of redundant extract, transform, and load (ETL) processes and intermediate data storage solutions.
Also, in addition to cost savings realized through reducing the development team, the time needed to develop complex revenue scorecards and reports has been significantly minimized, leading to further cost savings. "With the Business Rules Engine tool, we have eliminated about $1.2 million in IT overhead and salary for our department," says Joseph. "At the same time, the tool facilitates self-service business intelligence capabilities for users."
With the BRE tool, Microsoft IT was able to offer users a solution that easily integrates with revenue and expense systems, and consolidates critical business data from multiple data sources. As a result, users have an automated tool with a centralized set of shared rules, which makes it possible to quickly and easily collect data and produce scorecards and reports based on that data. The tool not only transforms data collection and report generation processes, but is helping IT organizations save more than one million dollars annually.
Because of this innovative tool, the Microsoft Scorecard team has received the patent award from Microsoft.
For More Information
For more information about Microsoft products or services, call the Microsoft Sales Information Center at (800) 426-9400. In Canada, call the Microsoft Canada Order Centre at (800) 933-4750. Outside the 50 United States and Canada, please contact your local Microsoft subsidiary. To access information via the World Wide Web, go to:
© 2009 Microsoft Corporation. All rights reserved.
This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY. Microsoft, SQL Server, Windows, and Windows Server are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. The names of actual companies and products mentioned herein may be the trademarks of their respective owners.