nerdexam
Microsoft

70-466 · Question #113

You are designing a SQL Server Analysis Services (SSAS) cube based on a Windows Azure SQL Database data warehouse. You need to implement a degenerate dimension. What should you do?

The correct answer is A. Use the fact table as the data source for the dimension. Tests understanding of what a degenerate dimension is and how to correctly implement it in an SSAS cube.

Build an analysis services database

Question

You are designing a SQL Server Analysis Services (SSAS) cube based on a Windows Azure SQL Database data warehouse. You need to implement a degenerate dimension. What should you do?

Options

  • AUse the fact table as the data source for the dimension.
  • BCreate a junk dimension table based on the fact table in the data source.
  • CCreate snowflake dimension tables based on normalized views of the fact table in the data source.
  • DAdd a surrogate key to the fact table and use it as the degenerate dimension key.

How the community answered

(46 responses)
  • A
    93% (43)
  • C
    2% (1)
  • D
    4% (2)

Why each option

Tests understanding of what a degenerate dimension is and how to correctly implement it in an SSAS cube.

AUse the fact table as the data source for the dimension.Correct

A degenerate dimension is one whose attributes exist directly in the fact table with no corresponding separate dimension table in the data warehouse. Using the fact table itself as the data source for the dimension correctly implements this pattern, allowing attributes like order numbers or invoice IDs to serve as dimension keys without requiring a separate lookup table.

BCreate a junk dimension table based on the fact table in the data source.

Creating a junk dimension table combines low-cardinality flags or codes from the fact table into a new separate table - this describes a junk dimension, not a degenerate dimension.

CCreate snowflake dimension tables based on normalized views of the fact table in the data source.

Snowflake dimension tables represent normalized dimension hierarchies split across multiple related tables, which is a snowflake schema pattern entirely unrelated to degenerate dimensions.

DAdd a surrogate key to the fact table and use it as the degenerate dimension key.

Adding a surrogate key to the fact table describes surrogate key usage, but a degenerate dimension by definition uses a naturally occurring fact attribute such as an order number rather than a system-generated key.

Concept tested: Degenerate dimension design and implementation in SSAS

Source: https://learn.microsoft.com/en-us/analysis-services/multidimensional-models-olap-logical-dimension-objects/dimensions-introduction

Topics

#degenerate dimension#dimension design#fact table as source#SSAS cube design

Community Discussion

No community discussion yet for this question.

Full 70-466 Practice