Function Taking part in Dimensions in Energy BI

On this submit I wish to clarify how you can deal with function taking part in dimensions in Energy BI. I wrote an article awhile in the past concerning function taking part in dimensions in SSAS Tabular which is legitimate for Energy BI Desktop.

To recap, within the function taking part in dimensions in SSAS Tabular article I defined three completely different options:

1. Importing function taking part in dimensions a number of instances into the mannequin
2. Creating database views within the supply aspect (in case your supply is a from of RDBMS like SQL Server, Oracle and so on…) then import the information into the mannequin
3. Preserve the inactive relationships within the mannequin and create a number of measures to deal with completely different roles utilizing USERELATIONSHIP features in DAX

On this submit I present you alternative routes for the primary two options to deal with function taking part in dimensions with out importing information a number of instances into the Energy BI mannequin. You additionally don’t should create database views in your supply database. I present you how you can handle this in each DirectQuery and Import modes when connecting Energy BI Desktop to a SQL Server database. I clarify the third possibility in one other submit.

I used AdventureWorksDW, however, you should utilize another variations of AdventureWorksDW database or you may mimic the method to your individual mannequin.

Notice: If you’re designing a star schema to your information warehouse you may simply create a Date dimension as defined right here.

The concept is to handle function taking part in dimensions in Energy BI Desktop itself within the simplest way doable.

• Open Energy BI Desktop
• Get information
• Choose “SQL Server”
• Enter the server and database names then click on OK

• Choose DimDate and FactInternetSales from the checklist then click on “Load”
• “Import” mode is chosen by default. Click on OK

• Rename DimDate and FactInternetSales to make them extra consumer pleasant

• Create a brand new calculated desk by clicking “New Desk” button from “modelling” tab from the ribbon

• We’re creating a replica of “Date” desk utilizing DAX expression. To take action simply kind the next DAX expression:

              Order Date = All(‘Date’)

• As you would possibly seen the icon for “Order Date” desk () is a bit completely different than the conventional desk icon () as the brand new desk is a calculated desk
• Create two extra calculated tables for “Ship Date” and “Due Date” utilizing the identical expression

• Click on “Relationships” to create new relations between the calculated tables we created and “Web Gross sales” desk

We’re performed now. We’ve all roles in our mannequin.

• Put a column chart on the report and put “Fiscal 12 months” column from “Order Date” to the chart axis then put “Gross sales Quantity” from “Web Gross sales” on values. (You may create an express measure for “Gross sales Quantity” however for simplicity I maintain utilizing the implicit one.)

• Repeat this for the opposite two date dimensions

You may conceal the “Date” dimension in information mannequin, or you may unload it within the Energy Question layer to cut back the complexity of the mannequin.

Though the DirectQuery is an superior function in Energy BI it has some few limitations. Certainly one of them is that we CANNOT create calculated tables. So we now have to go for another resolution.

Notice: You may be taught extra about DirectQuery right here. However, some info is out-dated as within the new launch of Energy BI among the limitations like creating calculated column and calculated measure are resolved. So the limitation on making a calculated desk in DirctQuery mannequin would possibly wager resolved within the subsequent releases of Energy BI. (Energy BI Desktop present model: Model: 2.33.4337.281 64-bit (March 2016)).

Let’s get the job performed in DirectQuery.

The primary steps of getting information are the identical simply you could choose “DirecyQuery” in “Connection Settings” web page as a substitute of “Import”.

After loading the mannequin comply with the steps beneath:

• Click on “Edit Queries” from “Dwelling” tab from the ribbon

• Rename DimDate and FactInternetSales to consumer pleasant names

• Proper click on on “Date” and choose “Reference”

• If you wish to see the Energy Question “M” scripts behind the seen for a reference desk click on on “Superior Editor” from “View” tab

• Rename the reference desk to “Order Date”
• Repeat this and create Due Date and Ship Date reference tables
• Click on “Shut & Apply”

• Swap to “Relationships” view

• Preserve simply the right relationships and take away undesirable ones between “Order Date”, “Due Date”, “Ship Date” and “Web Gross sales”

Alright, we now have all of the function taking part in dimensions in our mannequin. You may conceal the “Date” dimension in information mannequin, or you may unload it within the Energy Question layer to cut back the complexity of the mannequin.

Professionals:

1- In case your mannequin is a small mannequin then you may rapidly import new function taking part in dimensions into the mannequin both in Import Mode or DirectQuery Mode, creating relationships and also you’re able to go

2- It might be simpler for the tip consumer to have completely different Date choices. You’ll have separate slicers within the Energy BI. Nevertheless, you’ll must deal with filter interactivities and cross filtering which implies extra improvement time.

3- You’ll have only one measure to be sliced and diced by completely different roles individually

Cons:

1- In case your mannequin just isn’t small and you’ve got way more function taking part in dimensions to handle you then’ll find yourself importing the size a number of instances which isn’t environment friendly

2- Having a number of completely different function taking part in dimensions as separate tables everywhere in the mannequin might be actually complicated for the tip consumer and you’ll really want to spend extra time/cash to coach the tip customers. It is usually fairly arduous to take care of such a giant mannequin with a number of function taking part in dimensions

3- You devour extra storage and reminiscence which is once more not that environment friendly

On the finish of the day it actually relies on your buyer wants. Learn extra in regards to the different methodology to handle Function Taking part in Dimensions right here.

All performed!