In this post I want to explain how to handle role playing dimensions in Power BI. I wrote an article awhile ago regarding role playing dimensions in SSAS Tabular which is valid for Power BI Desktop. But, in this post I show you two new alternative ways to handle role playing dimensions without importing tables, for instance DimDate, into the Power BI model several times. You also don’t have to create database views on your source database. I show you how to manage this in both DirectQuery and Import modes when connecting Power BI Desktop to a SQL Server database.
I used AdventureWorksDW2016CTP3, but, you can use any other versions of AdventureWorksDW database or you can mimic the process to your own model.
Note: If you are designing a star schema for your data warehouse you can easily create a Date dimension as explained here.
The idea is to manage role playing dimensions in Power BI Desktop itself in the easiest way possible.
Role Playing Dimensions in Import Mode
- Open Power BI Desktop
- Get data
- Select “SQL Server”
- Enter the server and database names then click OK
- Select DimDate and FactInternetSales from the list then click “Load”
- “Import” mode is selected by default. Click OK
Continue reading Role Playing Dimensions in Power BI
In this post I show you a simple trick to make your Power BI model more organised and more readable. I call it creating a “Measure Table”. Let me explain. The story is that I was working on a model with lots of tables. The database schema was NOT a proper star schema so there were a bunch of measures spread into lots of different tables. On top of that we’ve created lots of calculated measures with different home tables which made it really hard to find a particular measure or calculated measure. I thought, well, when it is that hard to find the calculated measures at development time how hard it could be for a customer to find, understand and use the measures we created. The visibility of the calculated measures could be an issue when we have lots of measures in lots of different tables. You will soon feel the issue in customer training sessions when you need to navigate between lots of different tables to find a calculated measure.
Consider you create a Power BI model with direct connect to a SSAS Multidimensional instance. You will immediately notice that all measure groups have a special calculation icon () rather than a normal table icon () which makes the measure groups more recognisable for the end users. For instance, you can easily find any calculated measure related to “Internet Sales” under the “Internet Sales” measure group.
I know, we can search and find the measures very easily, but, our model would be more organised and more user friendly if we can put all measures in one or more tables which contain just related calculated measures and nothing else. For instance, we can create a measure table for time intelligence calculations and name it “Sales Time Intelligence Measures” and put all calculated measures like “Sales YTD”, “Sales LYTD”, “Sales Period Over Period” on it. It will make your model nice and clean, easy to use and easy to learn for your customers. It will also help you to train your customers more easily.
In this article I’ll connect to a SQL Server instance and will use the famous Adventure Works database. I also show you how to get the job done in both “Import” and “DirectQuery” modes as there are some limitations applied to the DirectQuery mode which makes it harder to do what we want.
Continue reading How to Define A Measure Table in Power BI Desktop