[ad_1]
To start with I’d like to elucidate what a Function-playing dimension really means. Then I’ll categorical the way in which you may implement it in a SSAS tabular mannequin.
While you hyperlink a dimension to a reality desk a number of occasions for logically distinctive roles you’re utilizing a role-playing dimension.
The important thing factors are:
1. You’re linking a reality desk to a dimension a number of occasions. The relationships are outlined by linking a number of overseas keys within the reality desk to a single key within the dimension desk.
2. Every linkage represents a single position or idea
The preferred role-playing dimensions are DimDate and DimTime. Do you need to see methods to implement Function Enjoying Dimensions in Energy BI, Click on right here and right here.
NOTE: The pattern is from Microsoft “AdventureWorksDW” for SQL Server 2012 and could be completely different from your personal knowledge warehouse design.
For example, in a gross sales system that you’ve got one thing like FactInternetSales reality desk which has a number of hyperlinks, or relationships, to a DimDate or DimAddress for distinct ideas like “Order Date”, “Ship Date” and “Due Date”.
As you see, the entire above columns clearly symbolize completely different meanings of date. Within the knowledge warehouse design you’ll see one thing like this:
Though that is completely OK within the relational database layer, however, this type of relationship is NOT permitted within the tabular mannequin, so what ought to we do?
Let’s take a look on the tabular mannequin in SQL Server Knowledge Instruments (SSDT) and see the way it appears after we import the mannequin straight from SQL Server knowledge supply.
· Open SSDT and create a brand new evaluation providers tabular mission (I assumed you understand how to create a brand new mission in SSDT)
· Click on on “Import From Knowledge Supply”, then choose “Microsoft SQL Server” then click on “Subsequent”
· Enter the server title and choose “AdventureWorksDW” from the database record then click on “Subsequent”
· Entre impersonation info and click on “Subsequent”
· Click on “Subsequent”
· Right here you may choose all tables and views that you must import into your tabular mannequin. In our pattern we simply want “FactInternetSales” and “DimDate” tables. So tick the “FactInternetSales” and “DimDate” tables after which click on “End”.
· Shut the “Desk Import Wizard”
· Swap to “Diagram View”. As you may see there is only one Energetic relationship between DimDate and FactInternetSales tables and each different relationships are Inactive meaning you can not instantly use the imported DimDate for all three functions that you must cowl the “Order Date”, “Ship Date” and “Due Date”. Which means you can not slice and cube a single measure with all roles on the identical time, which in our instance they’re “Order Date”, “Ship Date” and “Due Date”. I clarify extra later on this put up.
1. Importing DimDate into your tabular mannequin a number of occasions:
In our pattern we have to import it thrice to cowl “Order Date”, Ship Date” and “Due Date”.
a. Delete the inactive relationships
b. Double click on on the DimDate desk title to rename it to a person pleasant title. Identify it “Order Date”.
c. To make our pattern extra untestable I created a brand new hierarchy named “Order Date Particulars” which incorporates “CalendarYear”, “EnglishMonthName” and “FullDateAlternateKey”. A additionally renamed the columns to make the extra person pleasant to “12 months”, “Month” and “Full Date”. As well as, I set all different columns within the DimDate desk to “Disguise from Shopper Instruments”. I additionally renamed the “FactInternetSales” desk to “Web Gross sales”.
d. We have now efficiently setup the “Order Date” date and now we have to import the DimDate desk once more to help the “Ship Date”. To take action, from the “Mannequin” menu choose “Current Connections…”
e. Click on “Open”
f. Click on “Subsequent”
g. Choose DimDate from the record once more and click on “End”. This course of will import the DimDate desk to the mannequin once more. We’ll then set it as much as cowl “Ship Date”. To take action, hyperlink “ShipDateKey” from “Web Gross sales” desk to “DateKey” from “DimDate” desk.
h. Now repeat the above sections from b to g however, title the “DimDate” desk “Ship Date”. Repeat the above sections once more so as to add “Due Date” to the mannequin.
i. We’re carried out and we will merely slice and cube primarily based on the entire above dates.
j. Because the tabular mannequin doesn’t detect the measures routinely we have to outline at the very least a measure to have the ability to check the answer. To take action swap to “Grid View” and choose the “Web Gross sales” then outline a measure for “Whole Gross sales Quantity”. To take action simply click on on measures part below the “SalesAmount” column then click on the Sigma (
) button from the toolbar. Then rename the created measure to “Whole Gross sales Quantity”.
ok. Now we will check the answer by deciding on “Analyze in Excel” from “Mannequin” menu
l. Tick “Whole Gross sales Quantity” and “Due Date Particulars” hierarchy. You’ll be able to drilldown to month and day ranges.
m. You are able to do the identical for every of the opposite dates or you may make a mix of dates in case you want such a report.
2. Creating a number of SQL Server views within the database:
In our instance, in “AdventureWorksDW” database, we create three views for every position (Order Date, Ship Date, Due Date). We create these views on high of the prevailing DimDate with completely different names resembling the three completely different roles. Then we import these views into our tabular mannequin and hyperlink every of them to the “Web Gross sales” desk utilizing the suitable overseas key. As the entire course of is identical as what we’ve carried out beforehand within the first answer, I’m not going to elucidate it once more. So, on the finish of the day, we may have one thing like this within the database:
Now you can import the above views to your tabular as an alternative of importing the entire DimDate desk a number of occasions. This may cut back the database measurement and it’s a bit simpler to grasp. Nevertheless this answer is similar to the primary answer . Principally the structure is kind of the identical, however, the way in which we handle the tables is a bit completely different.
And the identical ends in Excel:
3. Creating a number of measures:
The third answer, which might be one of the best for almost all of use circumstances, is totally the alternative of what we’ve carried out up to now. Properly, I can say that the structure is kind of completely different. On this answer we DO NOT take away the Inactive Relationships and furthermore, we DO NOT import a number of copies of Date dimension.
What we should always do on this case is to create new measures for every position which suggests we may have the next three measures in our instance:
1- Whole Gross sales Quantity by Order Date
2- Whole Gross sales Quantity by Ship Date
3- Whole Gross sales Quantity by Due Date
What we’re doing on this answer is that we handle to make use of the connection which is related to the roles. To do this we simply must implement the information mannequin to activate the connection we’d like. We will simply energetic and inactive relationship in DAX utilizing USERELATIONSHIP operate. The USERELATIONSHIP operate, disables all energetic relationships first, then prompts a desired relationship. USERELATIONSHIP operate can be utilized as part of different capabilities that take filters as arguments. Which means we all the time use USERELATIONSHIP as part of a CALCULATE operate (or different capabilities that settle for filter arguments). Subsequently, the above three measures will appear like under:
1- Whole Gross sales Quantity by Order Date:= SUM(‘Web Gross sales'[Sales Amount])
2- Whole Gross sales Quantity by Ship Date := CALCULATE(SUM(‘Web Gross sales'[Sales Amount]), USERELATIONSHIP(‘Date'[DateKey], ‘Web Gross sales'[ShipDateKey]))
3- Whole Gross sales Quantity by Due Date := CALCULATE(SUM(‘Web Gross sales'[Sales Amount]), USERELATIONSHIP(‘Date'[DateKey], ‘Web Gross sales'[DueDateKey]))
As you may see within the first measure we haven’t used USERELATIONSHIP. The explanation is that the measure makes use of the connection which is energetic by default within the mannequin, due to this fact we don’t must implement it once more. The opposite two measures then again are imposing related relationships for use inside the measures.
Lastly, right here is the way it appears like whenever you analyse the mannequin in Excel:
Every of the three options mentioned above have professionals and cons.
Professionals of the primary two options, importing a number of Date dimensions:
1- In case your mannequin is a small mannequin then it might be faster to develop the mannequin
2- It might be simpler for the top person to have completely different Date choices. You’ll have separate slicers within the visualisation layer for every position.
3- You’ll be able to have only one measure and slice and cube it by completely different roles individually
Cons:
1- In case your mannequin will not be small and you’ve got way more position enjoying dimensions to handle then you definately’ll find yourself importing these dimensions a number of occasions which isn’t environment friendly
2- Having a lot of completely different position enjoying dimensions everywhere in the mannequin can be actually complicated for the top person and you’ll really want to spend extra time/cash to coach the top customers
3- You devour extra storage and reminiscence which is once more not that environment friendly
Professionals of the final answer, creating a number of measures:
1- You should use all of the roles side-by-side as you actually have a separate measure for every position
2– You aren’t importing a number of copies of the roles, as an illustration, you have got only one Date dimension that can be utilized to slice and cube all your measures throughout the entire mannequin
3- It’s extra environment friendly when it comes to storage and reminiscence consumption
4- Your mannequin is way more tidy whenever you don’t have a number of roles everywhere in the mannequin
Cons:
1- In massive fashions with a lot of completely different roles, creating a lot of completely different measures to help completely different roles could be time consuming and likewise a bit exhausting to keep up
2- The measure names are getting lengthy
3- Having a lot of completely different measures that look very related generally is a bit complicated for the top person
All carried out!
Associated
[ad_2]