70-467 · Question #26
You are developing the database schema for a SQL Server Analysis Services (SSAS) BI Semantic Model (BISM). The BISM will be based on the schema displayed in the following graphic. You have the…
The correct answer is C. Create a multidimensional project and define measures and a many-to-many dimensional. The schema requires bidirectional aggregation between students and classes, which is a classic many-to-many dimensional relationship that SSAS multidimensional projects handle natively.
Question
You are developing the database schema for a SQL Server Analysis Services (SSAS) BI Semantic Model (BISM). The BISM will be based on the schema displayed in the following graphic. You have the following requirements:
- Ensure that queries of the data model correctly display average
student age by class and average class level by student.
- Minimize development effort.
You need to design the data model. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
Exhibit
Options
- ACreate a multidimensional project and define measures and a reference relationship.
- BCreate a tabular project and define calculated columns.
- CCreate a multidimensional project and define measures and a many-to-many dimensional
- DCreate a tabular project and define measures.
How the community answered
(34 responses)- A12% (4)
- B24% (8)
- C56% (19)
- D9% (3)
Why each option
The schema requires bidirectional aggregation between students and classes, which is a classic many-to-many dimensional relationship that SSAS multidimensional projects handle natively.
A reference relationship routes a dimension through an intermediate dimension to the fact table, which does not resolve the bidirectional many-to-many aggregation required between students and classes.
Calculated columns in a tabular project compute row-level derived values but do not define the relational cardinality needed for correct bidirectional aggregation across a many-to-many relationship.
A many-to-many dimension relationship in SSAS multidimensional uses an intermediate measure group to bridge two dimensions, enabling correct aggregation in both directions - average student age by class and average class level by student. This is the native SSAS pattern for this cardinality and requires no custom code, minimizing development effort.
A tabular project using only measures would require complex DAX to replicate many-to-many behavior, increasing development effort rather than minimizing it.
Concept tested: SSAS multidimensional many-to-many dimension relationships
Source: https://learn.microsoft.com/en-us/analysis-services/multidimensional-models/define-a-many-to-many-relationship-and-many-to-many-relationship-properties
Topics
Community Discussion
No community discussion yet for this question.