On this put up I present you a easy trick to make your Energy BI mannequin extra organised and extra readable. I name it making a “Measure Desk”. Let me clarify. The story is that I used to be engaged on a mannequin with a number of tables. The database schema was NOT a correct star schema so there have been a bunch of measures unfold into a number of completely different tables. On prime of that we’ve created a number of calculated measures with completely different house tables which made it actually exhausting to discover a explicit measure or calculated measure. I believed, effectively, when it’s that tough to search out the calculated measures at growth time how exhausting it could possibly be for a buyer to search out, perceive and use the measures we created. The visibility of the calculated measures could possibly be a problem when we now have a number of measures in a number of completely different tables. You’ll quickly really feel the problem in buyer coaching periods when you’ll want to navigate between a number of completely different tables to discover a calculated measure.
Think about you create a Energy BI mannequin with direct connect with a SSAS Multidimensional occasion. You’ll instantly discover that every one measure teams have a particular calculation icon (
) somewhat than a traditional desk icon (
) which makes the measure teams extra recognisable for the tip customers. As an example, you possibly can simply discover any calculated measure associated to “Web Gross sales” underneath the “Web Gross sales” measure group.
I do know, we will search and discover the measures very simply, however, our mannequin could be extra organised and extra consumer pleasant if we will put all measures in a number of tables which comprise simply associated calculated measures and nothing else. As an example, we will create a measure desk for time intelligence calculations and identify it “Gross sales Time Intelligence Measures” and put all calculated measures like “Gross sales YTD”, “Gross sales LYTD”, “Gross sales Interval Over Interval” on it. It’s going to make your mannequin good and clear, simple to make use of and simple to study to your prospects. It’s going to additionally enable you to coach your prospects extra simply.
On this article I’ll connect with a SQL Server occasion and can use the well-known Journey Works database. I additionally present you how you can get the job completed in each “Import” and “DirectQuery” modes as there are some limitations utilized to the DirectQuery mode which makes it tougher to do what we wish.
Lets begin.
The thought is including a brand new desk with only one column having only one worth in that column. Then we have to disguise that column. This makes the entire desk get hidden as there isn’t a columns to point out. Then we use this desk as “Residence Desk” for all associated calculated measures. Keep in mind, we do NOT disguise the desk, however, simply the column. On this case Energy BI Desktop recognises the desk as a measure desk so it makes use of the measure group icon (
) for it.
Create a Measure Desk in Import Mode
- Open Energy BI Desktop
- Get information
- Connect with a SQL Server Database (Journey Works as a pattern)
- Choose a desired desk (FactInternetSales in our case)
- Click on “Choose Associated Tables” button
- Click on “Load”
- Click on “Import” then OK
- Click on “Enter Information” from Residence tab
- Enter a worth for “Column1”
- Enter a reputation for the desk then click on “Load”
- Broaden the brand new desk from the “Fields” pane then disguise the “Column1” column
- It will disguise the entire desk as there isn’t a seen columns to point out
- Click on “New Measure” from “Modeling” tab from the ribbon to create a brand new calculated measure
- Kind a desired DAX expression to make the brand new calculated measure then press enter
- It will create the calculated measure within the first desk within the “Fields” pane
- Click on on system bar once more to allow the measure properties
- Click on “Residence Desk” and decide the newly created desk from the record
- As you possibly can see the desk seems on the “Fields” pane once more with the calculated measure
- As you possibly can see the tables nonetheless has the conventional desk icon, however, when you toggle proper the fields pane then toggle left it the icon might be refreshed
You may create extra tables like “Resellers Gross sales” and so forth then transfer the calculated measures to the corresponding tables. In some circumstances you possibly can disguise the entire reality tables when you have created all calculated measures you want or when your buyer received’t must see any measures from the actual fact tables.
Create a Measure Desk in DirectQuery Mode
Mainly we have to do the identical course of, however, this time we wish to connect with a SL Server database in DirectQuery mode. So to get information we do precisely the identical issues:
Open Energy BI Desktop=> Get Information=> Connect with a SQL Server database and so forth.
It’s important that you simply enter the database identify at step one of getting information.
However, you’ll instantly discover that the “Enter Information” button is disabled in DirectQuery mode.
It’s OK. There’s at all times another method to obtain the purpose.
As I discussed earlier than, we have to enter the database identify when getting information. That is essential as we’ll reuse the connection within the subsequent steps and if we haven’t enter the database identify you’ll get the next message:
“Connecting to tables from a couple of database isn’t supported in DirectQuery mode”
OK, right here is the trick.
After you related to the SQL Server database in DirectQuery mode observe the steps beneath:
- Click on “Edit Queries” from the ribbon
- Click on “Current Sources” then click on the newest one
- Choose a desk. It doesn’t matter which desk you choose as we’ll modify it through the subsequent steps. Click on OK
- It will add the brand new desk within the “Queries” pane (in my pattern it’s “AdventureWorksDWBuildVersion”)
- Click on “Superior Editor” from “Residence” tab from the ribbon
- All you’ll want to do is so as to add a easy question like “[Query=”SELECT 1 AS NEW_COLUMN”]” to the supply
- Now you need to remark out/take away the following line in addition to the output within the “in”
- Put “Supply” within the “in” block
- Don’t forget to take away the comma “,” from the tip of the “Supply” line
- Click on “Accomplished”
- You must see a desk with one column
- Rename the question then click on “Shut & Apply”
OK, now we now have a desk with only one column. From right here the remainder of the method is rather like what we’ve completed earlier than.
- Disguise the “New_Column” column
- Add a brand new measure then change the “Residence Desk” to newly created desk
- Toggle proper and left the duty pane and also you’re completed
You may suppose
“Nicely… this was a bit lengthy process. What if I wish to create a couple of measure desk? Do I must redo the entire course of time and again?”
The reply is NO, you do NOT need to redo the method for every measure desk you wish to add to the mannequin. All you’ll want to do is to create a reference question from the question you already created.
To take action:
- Click on “Edit Question” from the ribbon to open “Question Editor”
- Proper click on on the question you created beforehand and click on “Reference”
- Rename the reference desk then “Shut & Apply”
- From right here you’ll want to observe the identical course of as defined earlier than
