On this submit I need to clarify the best way to 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:
- Importing function taking part in dimensions a number of occasions into the mannequin
- 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 info into the mannequin
- Preserve the inactive relationships within the mannequin and create a number of measures to handle completely different roles utilizing USERELATIONSHIP capabilities in DAX
On this submit I present you other ways for the primary two options to deal with function taking part in dimensions with out importing information a number of occasions into the Energy BI mannequin. You additionally don’t must create database views in your supply database. I present you the best way to handle this in each DirectQuery and Import modes when connecting Energy BI Desktop to a SQL Server database. I clarify the third choice in one other submit.
I used AdventureWorksDW, however, you should use some other variations of AdventureWorksDW database or you’ll be able to mimic the method to your personal mannequin.
Observe: If you’re designing a star schema in your information warehouse you’ll be able to simply create a Date dimension as defined right here.
The thought is to handle function taking part in dimensions in Energy BI Desktop itself within the simplest way potential.
- 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 listing 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 duplicate of “Date” desk utilizing DAX expression. To take action simply kind the next DAX expression:
Order Date = All(‘Date’)
- As you would possibly observed the icon for “Order Date” desk (
) is a bit completely different than the traditional 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 carried out 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 possibly can create an specific measure for “Gross sales Quantity” however for simplicity I hold utilizing the implicit one.)
- Repeat this for the opposite two date dimensions
You possibly can cover the “Date” dimension in information mannequin, or you’ll be able to unload it within the Energy Question layer to cut back the complexity of the mannequin.
Though the DirectQuery is an superior characteristic in Energy BI it has some few limitations. Certainly one of them is that we CANNOT create calculated tables. So we’ve to go for an alternate resolution.
Observe: You possibly can 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 guess 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 carried out in DirectQuery.
The primary steps of getting information are the identical simply it is advisable choose “DirecyQuery” in “Connection Settings” web page as an alternative of “Import”.
After loading the mannequin comply with the steps under:
- Click on “Edit Queries” from “Residence” 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’ve all of the function taking part in dimensions in our mannequin. You possibly can cover the “Date” dimension in information mannequin, or you’ll be able to 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’ll be able to shortly 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 top consumer to have completely different Date choices. You’ll have separate slicers within the Energy BI. Nonetheless, you’ll have to handle 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 rather more function taking part in dimensions to handle then you definitely’ll find yourself importing the size a number of occasions which isn’t environment friendly
2- Having plenty of completely different function taking part in dimensions as separate tables everywhere in the mannequin shall be actually complicated for the top consumer and you’ll actually need to spend extra time/cash to coach the top customers. It’s also fairly arduous to take care of such a giant mannequin with plenty 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 is dependent upon your buyer wants. Learn extra in regards to the different technique to handle Position Enjoying Dimensions right here.
All carried out!
