
[ad_1]
On this publish I wish to clarify the right way to deal with position enjoying dimensions in Energy BI. I wrote an article awhile in the past relating to position enjoying dimensions in SSAS Tabular which is legitimate for Energy BI Desktop.
To recap, within the position enjoying dimensions in SSAS Tabular article I defined three totally different options:
- Importing position enjoying dimensions a number of occasions into the mannequin
- Creating database views within the supply facet (in case your supply is a from of RDBMS like SQL Server, Oracle and many others…) then import the info into the mannequin
- Maintain the inactive relationships within the mannequin and create a number of measures to deal with totally different roles utilizing USERELATIONSHIP features in DAX
On this publish I present you alternative routes for the primary two options to deal with position enjoying dimensions with out importing information a number of occasions into the Energy BI mannequin. You additionally don’t need to create database views in your supply database. I present you the right 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 publish.
I used AdventureWorksDW, however, you should use another variations of AdventureWorksDW database or you’ll be able to mimic the method to your individual 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 concept is to handle position enjoying 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 record then click on “Load”
- “Import” mode is chosen by default. Click on OK
- Rename DimDate and FactInternetSales to make them extra person 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 may observed the icon for “Order Date” desk (
) is a bit totally 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 got all roles in our mannequin.
- Put a column chart on the report and put “Fiscal Yr” column from “Order Date” to the chart axis then put “Gross sales Quantity” from “Web Gross sales” on values. (You’ll be able to create an express measure for “Gross sales Quantity” however for simplicity I preserve utilizing the implicit one.)
- Repeat this for the opposite two date dimensions
You’ll be able to 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 now have to go for another answer.
Observe: You’ll be able to study extra about DirectQuery right here. However, some data 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 may 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 carried out 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 observe the steps under:
- Click on “Edit Queries” from “Dwelling” tab from the ribbon
- Rename DimDate and FactInternetSales to person 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
- Maintain 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 position enjoying dimensions in our mannequin. You’ll be able to 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 position enjoying 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 person to have totally different Date choices. You’ll have separate slicers within the Energy BI. Nonetheless, you’ll must deal with filter interactivities and cross filtering which suggests extra growth time.
3- You’ll have only one measure to be sliced and diced by totally different roles individually
Cons:
1- In case your mannequin shouldn’t be small and you’ve got way more position enjoying dimensions to handle then you definately’ll find yourself importing the scale a number of occasions which isn’t environment friendly
2- Having numerous totally different position enjoying dimensions as separate tables everywhere in the mannequin might be actually complicated for the top person and you’ll actually need to spend extra time/cash to coach the top customers. Additionally it is fairly exhausting to take care of such an enormous mannequin with numerous position enjoying 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 Taking part in Dimensions right here.
All carried out!
Associated
[ad_2]