Tuesday, March 8, 2016
Why Can’t I Get Transactional Detail with My BI Dashboard?
Posted by Noah Kluge
Our business intelligence (BI) team often receives this request around a BI dashboard: we want to see transaction document number details (e.g. sales invoice, purchase order) alongside visually pleasing charts and key performance indicators (KPIs). Unfortunately, the answer with a traditional, multi-dimensional cube is simple: you can’t.
You could build into the cube the ability to drill to the detail, but not show it at the same time on the dashboard. The reason for this limitation is that a multi-dimensional cube is designed for summary reporting using pre-defined hierarchy structures. The issue with sales invoice numbers or purchase order numbers is that they cannot be aggregated.
However, hope is in sight! There are new technologies from providers like Microsoft and Qlik that now allow you to have both transaction details and visualizations in one spot. The new tabular models by Microsoft within SQL Server BI (or Enterprise) provide the foundation for this. Qlik’s in-memory data storage plus visualization will also deliver these capabilities.
Below are two examples. The first is for delivering operating expenses versus budget with the actual transactional details. Imagine how many requests to your Accounting team can be avoided using this tool! The second example below, delivers customer and item sales with voucher numbers.
Microsoft Power BI: Operating Expense
Learn more about Armanino’s Dynamic Insights solution and watch the short video below to discover how Dynamic Insights can help you gain the most out of your BI dashboards.
Noah is a Senior Manager with Armanino’s business intelligence practice. During his career, he has been responsible for the design, build, and implementation of various business intelligence projects. Noah has extensive experience with the PrecisionPoint data warehouse and a great understanding of the importance of integration among systems.