Microsoft
70-467 · Question #6
70-467 Question #6: Real Exam Question with Answer & Explanation
The correct answer is A. Create a multidimensional project and define measures and a many-to-many dimensional. A multidimensional SSAS project with a many-to-many dimensional relationship is the correct model when a bridge fact table links students to classes and accurate average aggregations are required.
Design BI data models
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. - Ensure that the solution supports role-based security and partitions. - 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 many-to-many dimensional
- BCreate a multidimensional project and define measures and a reference relationship.
- CCreate a tabular project and define measures.
- DCreate a tabular project and define calculated columns.
Explanation
A multidimensional SSAS project with a many-to-many dimensional relationship is the correct model when a bridge fact table links students to classes and accurate average aggregations are required.
Common mistakes.
- B. A reference relationship routes dimension queries through an intermediate dimension rather than through a bridge fact table, so it cannot correctly resolve many-to-many aggregations and would produce inflated or incorrect averages for student age by class.
- C. A tabular project with standard measures does not natively resolve many-to-many join semantics without explicit DAX bidirectional filter configuration, requiring additional development effort to produce correct averages.
- D. Calculated columns compute row-level scalar values during data refresh and do not perform aggregation across relationships, making them unsuitable for computing an average student age aggregated across a many-to-many class enrollment relationship.
Concept tested. SSAS many-to-many dimension relationship design
Topics
#BISM#multidimensional model#tabular model#many-to-many relationship
Community Discussion
No community discussion yet for this question.