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:
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:
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:
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):
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.