Help – Which Attributes & Measures can I combine from my Tabular Data Model?
In this blog post, I will guide you through all the required steps to get a Data Model Relationship Matrix in Power BI.
If you don’t know what I mean, I would like to have a straightforward overview where I can see which attribute groups and measure groups I can combine from my Tabular Model in (SQL Server) Analysis Server.
By the end of this blog post, we will have created the following overview:
You may think, why would you need it? Well, in my case, I inherited a data model which has a lot of attributes and measures (I really mean a lot, speaking here about more than 1000 attributes and measures) all divided over more than 50 dimensions and facts. To make it more fun, we currently have multiple models.
Quick Overview
Background Story – Use Case
Not so long ago, a couple of things changed at work, instead of working on a group level as I was used to, the BI team I was working in was split into 2 separate BI teams both focusing on a particular concept. Since we are still keeping close contact and chatting about what we are working on, we got a couple of ideas after the talk.
The colleagues from the other BI team were busy making a matrix to show the dependencies between all attributes and measures available in the Tabular Model in SQL Server Analysis Services. Which in the end could be really useful for us and our end users.
After a chat between my colleague and myself, we came up with the idea that there should be a more manageable way to do it. Our idea: “Why don’t we leverage the Tabular model information in Analysis services?”
After a couple of minutes, we had our dataset with the required information and could start to get this information in a Power BI report.
Step-by-step Example
Getting the data
For this step-by-step example, I have used the AdventureWorkDW as a source database and I quickly created a very small Tabular Model to be able to go through the process with an approachable model.
I included the following Dimensions:
- DimCalendar
- DimCurrency
- DimCustomer
- DimProduct
I imported 2 fact tables as well:
- FactInternetSales
- FactResellerSales
As a first step I renamed them to a more end-user-friendly name, and to continue I deployed my basic Tabular model to SQL Server Analysis Services.
As soon as the deployment was done, I started SQL Server Management Studio (SSMS) and connected to my SQL Server Analysis Servces Instance.
As a second step, I right click on my AdventureWorkDW Model, go to New Query and choose MDX.
In the query window, I tried the following query:
SELECT measuregroup_name AS MeasureGroupName,
[dimension_unique_name] AS DimensionName,
dimension_is_visible AS DimensionIsVisible,
1 AS Available
FROM $system.MDSCHEMA_MEASUREGROUP_DIMENSIONS
WHERE measuregroup_cardinality <> 'ONE'As you can see in the code above, I’m filtering out all objects with a one-on-one cardinality. For now, this is fine in my case but it might be that you need to include these in your solution.
As a resultset, I received:
As you can see in the result, I was able to retrieve all my existing relationships as visualized in my Data Model at the beginning of this step-by-step example.
Power BI
Since we have our basic dataset ready for now, we can start the fun part of this blog post, getting it visualized in Power BI.
I opened Power BI Desktop and started for a blank report.
I navigate to the Home Tab, go to the Data Section and continue by clicking the Get Data button, and choose “Analysis Services” as my desired Data Source.
As a next step, I need to provide the connection information. First I define to which Server I want to connect.
As a second step, I define the Database (Name of Tabular Model), which in my case is AdventureWorksDW.
The next thing I need to configure is my connection mode. Since I will be using an MDX query to get my data, I will need to choose Import as the connection method and define the MDX query in the section below.
The only thing I need to do now is to confirm my settings by clicking the OK button. As a result, the following screen shows up:
For this demo, I will just confirm by clicking the Load button. If you first want to make additional changes to your dataset, you can choose the “Transform Data” button.
As a result, I can now find my dataset in the Data section on the right-hand side of Power BI Desktop. When you take a look at your dataset, this probably will be named Query1. I changed to name to a more descriptive name, TabularModelRelationshipInfo.
Since we have our dataset available in Power BI right now, we can start to build our visuals. As a first step, I choose to add a Matrix visualisation to my report.
By clicking the Matrix Visualisation, this visual will be added to your active Power BI report. Now we can configure (in the Visualizations pane) which attributes we would like to show on the Rows & columns and which value needs to be visualized.
As a result, I now get the following visual on my report:
Now I just need to start to make it “pretty”. Since I’m no designer, I will just keep to the basics.
I will first change the values into icons. To get this done, I first select my visual. As a next step, I go to the Format your Visual pane and navigate to the Cell elements section. There I activate the Icons option and click the function sign:
Now I can easily configure which Icon I want to show when.
As for the Icon layout, I’m choosing to show the Icon only, and then I configure the if my count is at least 1, I want to see a green check mark and if the value is blank I want to show a red cross mark.
As soon as I confirm my changes, I got the following visual:
At this point, my visual is working as expected, to make the visual a bit more readable, I align the icons centred and I hide the totals.
After executing some layout tweaks, I can present my final result:
Useful links
If you want to find out more about Tabular Model metadata you can find a lot of info via the following link: Dynamic Management Views (DMVs) in Analysis Services | Microsoft Learn
