This is Part One of two blog posts about automating the creation of a Bus Matrix in Power Pivot for Excel using DMV queries from an SSAS Tabular Model. UPDATE: Part 2 is now published and can be viewed at this link.
Have you ever spent several hours making a Business (Bus) Matrix for an existing cube, and wondered why you couldn’t automate the documentation process? Well, keep reading.
Anyone working in business intelligence (BI) knows that all projects always have thorough, detailed, and accurate documentation. Just kidding. Cuts in budget, crashed schedules, disconnected remote teams, resource turnover, and iterative development cycles frequently lead to lapses and shortcomings in the documentation of solutions.
So how can a Project Manager or Lead Architect have confidence that a BI solution is being built to specifications before it can be tested? I’m sure many of you have nervously awaited the completion of a long development process, hoping that it is does not need to be followed up by a change request for the solution architecture.
In a perfect world, a Requirements & Design phase of a BI project functions as part of the planning process for a larger project management plan. A common output of this phase is a Bus Matrix document. During a BI project, a Project Manager or Lead Architect is often the translator between business and technical stakeholders. The Bus Matrix summarizes the expectations of how data within a business intelligence solution will tie together in a way that business and technical team members can both understand.
A business user looking at a Bus Matrix will understand “I can filter total revenue by Region,” while a technical team member will read it as “The Revenue Table has a foreign key for the Region Table, and I need to create a calculation that sums up revenue.” Learn more about Bus Matrix methodology at this link written by Margy Ross of The Kimball Group.
If a project is in an execution phase to build a Tabular Model in SQL Server Analysis Services, or is migrating a Power Pivot Model to Analysis Services, a tool that automatically creates a Bus Matrix would allow the Model to be evaluated versus a specifications checklist. A manually created Bus Matrix created in the Requirements & Design phase of the project could be compared to the actual Tabular Model. Any discrepancies could potentially be found before rework requiring a change request is needed.
One of my co-workers, Alex Barbeau, introduced me to Dynamic Management View (DMV) queries that he had worked on with other GNet Group consultants. These queries have previously been discussed by Microsoft BI thought leaders such as Chris Webb (click here for a link to his article), Rob Collie (click here for a link to his article), and others. This link from Microsoft references the different types of DMV queries you can use for a Tabular Model.
The screenshot below is from an Excel template that uses the output from DMV queries to build a Bus Matrix using Power Pivot with a Pivot Table. Switching the DMV queries to any other Tabular model will result in files that can be uploaded into this template without any programmatic adjustments:
The above screenshot is our first version of a template to programmatically automate a Bus Matrix using DMV queries and Power Pivot for Excel. We plan to continue building out this solution for additional functionality. Part Two of this blog post series, which details the process by which to create an automated Bus Matrix using DMV queries, will be posted later this week.
If you’d like to use this automated Bus Matrix solution to evaluate your own Tabular Model(s) in Analysis Services, but you don’t want to take the time to build it yourself, please contact GNet Group to discuss ways that we can help you use this template on your solutions. We’ll get you started with automating a Tabular Bus Matrix by either 1) a short engagement to bring this solution into your environment and train your team, or 2) an engagement to evaluate the performance and architecture of your current Tabular Model(s). If you’re an existing customer, just reach out and let us know that you’d like to give this a shot.
Check back on Thursday for the second and final part of this blog post series if you’d like to learn how to build out this solution yourself.