Microsoft
70-467 · Question #28
70-467 Question #28: Real Exam Question with Answer & Explanation
The correct answer is A: Define the surrogate key as an INT data type.. INT is the most efficient surrogate key data type for a date dimension because it provides sufficient range while minimizing storage and improving cube processing performance.
Question
You are modifying a star schema data mart that feeds order data from a SQL Azure database into a SQL Server Analysis Services (SSAS) cube. The data mart contains two large tables that include flags and indicators for some orders. There are 100 different flag columns, each with 10 different indicator values. Some flags reuse indicators. The tables both have a granularity that matches the fact table. You have the following requirements: - Allow users to slice data by all flags and indicators. - Modify the date dimension table to include a surrogate key of a numeric data type and add the surrogate key to the fact table. - Use the most efficient design strategy for cube processing and queries. You need to modify the schema. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
Options
- ADefine the surrogate key as an INT data type.
- BDefine the surrogate key as an INT data type.
- CDefine the surrogate key as a BIGINT data type.
- DDefine the surrogate key as a BIGINT data type.
Explanation
INT is the most efficient surrogate key data type for a date dimension because it provides sufficient range while minimizing storage and improving cube processing performance.
Common mistakes.
- B. Although labeled INT, this choice represents a duplicate or variant that does not add correct additional configuration, making it a less precise answer than A.
- C. BIGINT uses 8 bytes per value versus INT's 4 bytes, unnecessarily doubling key storage for a date dimension that will never require 64-bit range, degrading cube processing efficiency.
- D. Same as C - BIGINT is oversized for a date dimension surrogate key and introduces avoidable storage and processing overhead without any functional benefit.
Concept tested. Surrogate key data type selection for SSAS dimension design
Community Discussion
No community discussion yet for this question.