![]() The problem that occurs most with this option is maintaining two or more date dimensions. This is very powerful and shows the abilities of using Analysis Services to provide the business with an analytical database.įigure 6 Delivery and Total Sales Report More Than One Date DimensionĪnother option would be to have two different Date dimension tables in the model like Figure 7. These 2 can now be side by side in a report like Figure 6. One would also be inclined to change the description of Total Sales to Invoices Sales. ![]() The measure has a description in the title to differentiate the Sales by Invoice Date Key to Delivery Date Key. USERELATIONSHIP( Sale, 'Date')įigure 4 Delivery Sales With Delivery Date Keyįigure 5 shows the Delivery Sales YTD measure. Delivery Sales is the equivalent as Total Sales for Invoice Date Key. Here, the active relationship is Sales instead of Invoice Date Key for the measure Delivery Sales. The DAX language has a USERRELATIONSHIP function to change the context of the Date dimension in the measure. Figure 4 shows this syntax for the new Delivery Sales measure. If a Sales YTD for Delivery Date is needed, the relationship has to be defined in the measure. Note that Sales Year to Date was created in Level 6 of the series. When a measure like Sales YTD (Sales Year to Date) is created, the active relationship on Invoice Date Key will be used like in Figure 3. Figure 2 shows the Edit Relationship screen for Invoice Date Key.įigure 2 Active Relationship on Invoice Date Key So, when we create a Time Intelligence measure (Sales YTD), the active date (Invoice Date Key) relationship will be used to evaluate the measure. The Tabular Model will only recognize one relationship as active. The Invoice Date Key became the active relationship by coincidence but it is the active relationship for this model. During the import of these tables into the model, the database foreign keys created the initial relationships. The Invoice Date Key and Delivery Date Key from the Sales table are both related to the Date dimension table. The Date is considered Conformed between two facts – Sales and Transaction and within the Sales by the two relationships – Invoice Date Key and Delivery Date Key. ![]() Figure 1 shows the Date table and two relationships with the Sales table plus a relationship with a different fact – Transaction Date. Role-Playing DimensionĮven though dimensional modeling is considered a de-normalization of a database, the role-playing dimension tries to bring a non-repeating pattern back into the mix. This has to be explained in documentation and training. ![]() If the Date dimension is named Date, then a user might get confusing on why the Date table is related to two different descriptive dates in the model. For instance, the date in Sales is on order date while the relationship to the Transaction table is based on transaction date. There is also the case when the relationship between one fact table is based on a different date column from a different fact table. The Data dimension is called a role-playing dimension based on dimensional modeling concepts. The other relationship(s) with the date dimension can be present but are inactive. A DAX measure using a Date dimension column will be in the context of the active relationship. ![]() Analysis Services tabular model only allows one active relationship between a fact table and another table like the date dimension. The case always comes up when there is more than one date key in a fact table. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |