Facebook Twitter LinkedIn YouTube E-mail RSS
Home Business Intelligence Automate Bus Matrix Documentation Using DMV Queries from a Microsoft BI Tabular Solution for Project Management Scope Verification – Part 2
formats

Automate Bus Matrix Documentation Using DMV Queries from a Microsoft BI Tabular Solution for Project Management Scope Verification – Part 2

Share
Use an Automated Bus Matrix to Verify Specifications of a Tabular Model

Use an Automated Bus Matrix to Verify Specifications of a Tabular Model

This is Part Two, the final 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.

In the first part of this blog post series, which can be found at this link, I discussed the benefits of a Business (Bus) Matrix that can be automatically generated from a SQL Server Analysis Services Tabular Model. Typically, Project Managers and Lead Architects cannot always confirm that a Tabular Model is built to Requirements & Design planning specifications until development is complete and testing begins. An automated Bus Matrix would allow for the Tabular Model to be compared to a Project Management specifications checklist, which usually includes a Bus Matrix that was manually created before development began. With this methodology, discrepancies can be mitigated much earlier in the development process, minimizing rework. Existing Tabular Models that have sparse documentation, or which undergo frequent iterative changes, could also benefit from an automated Bus Matrix.

As promised in the first half of this blog post series, we are going to get to the nuts and bolts of building an automated Bus Matrix using DMV queries from a Tabular Model.

Before proceeding, I wanted to repeat a few links from the first blog post to ensure that credit is given where credit is due. I was introduced to Dynamic Management View (DMV) queries by a coworker, Alex Barbeau, who had used them alongside other GNet Group employees. Alex and I worked through the first iteration of this methodology. Microsoft BI thought leaders have previously discussed DMV queries for similar uses, such as those by Chris Webb and Rob Collie. Microsoft also has a documentation page for DMV queries at this link.

Here is a star schema of the demo Tabular Model that is currently pulled into the solution. Notice that a Model with some tricky architecture (snowflakes, calculations on dimension tables, a Measures table, etc.) was used to prove out the bus Matrix template methodology:

Tabular Schema

Before discussing the queries and the methodology, a few limitations of the current version should be noted:

  • Snowflake tables will not break the solution, but they are not supported. Notice that there are a few rows with no “Y” values in the Bus Matrix image a few paragraphs down, such as ProductCategory and ProductSubcategory. Although sometimes snowflakes work fine, they should be avoided as a best practice in Power Pivot and Tabular solutions as noted by this PowerPivotPro article.
  • Columns within tables cannot be added as groups within each table on rows. We may add this functionality in the future…
  • Many-to-Many relationships are not supported. Many-to-Many relationships are not natively supported by Power Pivot or Tabular, but you can learn more about implementing them programmatically at this link from Alberto Ferrari.
  • “Partial” calculations, such as when the numerator and denominator are from different tables, will show up as “Y” even though parts of the calculation may not be filtered by the table.

While some of the above scenarios might play into Tabular solutions that you encounter in the real world, a solution with a simple best practices star schema design should not be impacted by these limitations. Even if a few of these scenarios exist, there is still value in having a means for a Project Manager or Lead Architect to evaluate the basic relationships and architecture of a Tabular Model during any stage of development after it has first been deployed. Evaluating a Tabular Model versus a specifications checklist from the Requirements & Design Planning Phase Bus Matrix can ensure that the actual Tabular Model is being built to plan, catching any differences earlier in the process and reducing rework.

The DMV queries used as sources for the Power Pivot are as follows:

  • TABLES: SELECT DISTINCT * FROM $system.DBSCHEMA_COLUMNS
  • CALCULATIONS: SELECT DISTINCT * FROM $system.DISCOVER_CALC_DEPENDENCY

Below is the Diagram View of the Power Pivot Model:

Power Pivot Bus Matrix

The table “Relationships” is another version of the “CALCULATIONS” query. Underscores are removed from the table headers to prevent the use of reserved names in Power Pivot. A few filters are placed on the tables when inputted into Power Pivot:

  • Relationships: Import CALCULATIONS table
    • OBJECT TYPE = HIERARCHY, ACTIVE_RELATIONSHIP, RELATIONSHIP
  • Tables: Import TABLES table
    • COLUMN OLAP TYPE = ATTRIBUTE
  • Calculations: Import CALCULATIONS table

Once the tables are imported and “Relationships” and “Calculations” have foreign keys pointing to “Tables,” a “Status” calculation can be created to display “Y” when a Tabular Model table in “Tables” contains values from both of the other tables.  The calculations to accomplish this are as follows:

  • Calculations:=COUNTROWS(‘Calculations’)
  • Relationships:=COUNTROWS(‘Relationships’)
  • Status:=IF(AND([Calculations]>0,[Relationships]>0),”Y”,BLANK())

With the resulting Power Pivot solution, a Pivot Table can be built in Excel that automates the creation of a Bus Matrix for the Tabular Model.  The Excel workbook can use tables from the same DMV queries, without any changes in the Excel workbook, to display a Bus Matrix for any Tabular Model.  Here’s a screenshot of an automated Bus Matrix for the Tabular solution referenced earlier in this article.  The blank rows are tables with snowflake relationships, which as noted above are not currently supported by this solution (and which generally are not a best practice for Tabular or Power Pivot Models):

An automated Tabular Model Bus Matrix using a DMV query

An automated Tabular Model Bus Matrix using a DMV query

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 can help you get started with automating a Tabular Bus Matrix by 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.

You can contact us by 1) our corporate contact page, 2) leaving a comment on this article, or 3) reaching out to me on Twitter or LinkedIn.

 
 Share on Facebook Share on Twitter Share on Reddit Share on LinkedIn
1 Comment  comments 

One Response

  1. […] 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. […]