nerdexam
Microsoft

70-466 · Question #153

A production SQL Server Analysis Services (SSAS) cube is processed daily. The users query products by using a hierarchy named Products from a dimension named Product. The DimProduct table in the data

The correct answer is B. Relate the Subcategory and Category attributes directly to the Product attribute.. When a SSAS dimension is sourced from a single denormalized table, attribute relationships should be defined directly to the key attribute rather than chained, to avoid data integrity violations during processing.

Build an analysis services database

Question

A production SQL Server Analysis Services (SSAS) cube is processed daily. The users query products by using a hierarchy named Products from a dimension named Product. The DimProduct table in the data source view is used as the source of the Product dimension. The table has the following structure: CREATE TABLE [dbo].[DimProduct]( [DimensionKey] [int] IDENTITY(1,1) NOT NULL, [ProductKey] [int] NOT NULL, [ProductName] varchar NOT NULL, [SubcategoryKey] [int] NOT NULL, [SubcategoryName] varchar NOT NULL, [CategoryKey] [int] NOT NULL, [CategoryName] varchar NOT NULL ) ON [PRIMARY] The Product dimension has three attribute hierarchies:
  • Product
  • Subcategory
  • Category
The attributes have the following relationships defined: Product > Subcategory > Category. Each attribute has a key and a name sourced from the related key and name columns in the DimProduct table. During processing, you receive the following error message: 'Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_DimProduct', Column: 'SubcategoryKey', Value: '23'. The attribute is 'Subcategory'.' You verify that the data is accurate. You need to ensure that the dimension processes successfully. What should you do?

Options

  • ADelete the Products hierarchy.
  • BRelate the Subcategory and Category attributes directly to the Product attribute.
  • CRemove the duplicate data from the DimProduct table.
  • DRemove the Subcategory attribute.

How the community answered

(60 responses)
  • A
    5% (3)
  • B
    83% (50)
  • C
    10% (6)
  • D
    2% (1)

Why each option

When a SSAS dimension is sourced from a single denormalized table, attribute relationships should be defined directly to the key attribute rather than chained, to avoid data integrity violations during processing.

ADelete the Products hierarchy.

Deleting the Products user-defined hierarchy removes navigation capability for users but does not fix the underlying attribute relationship problem causing processing or aggregation issues.

BRelate the Subcategory and Category attributes directly to the Product attribute.Correct

Because DimProduct is a single denormalized table where SubcategoryKey and CategoryKey both exist at the product row level, both Subcategory and Category should be related directly to the Product key attribute. The chained relationship (Product -> Subcategory -> Category) imposes a strict many-to-one constraint between Subcategory and Category that may be violated in the flat table, causing processing errors or incorrect aggregations. Relating them directly to Product removes this rigid dependency.

CRemove the duplicate data from the DimProduct table.

The table structure is intentionally denormalized for SSAS consumption; removing duplicate data would restructure the source table unnecessarily and would not resolve the attribute relationship design issue in the cube.

DRemove the Subcategory attribute.

Removing the Subcategory attribute eliminates useful business context and is not required - the attribute can remain as long as its relationship to the key attribute is correctly defined.

Concept tested: SSAS dimension attribute relationship design for denormalized tables

Source: https://learn.microsoft.com/en-us/analysis-services/multidimensional-models/attribute-relationships-define

Topics

#attribute relationships#dimension hierarchy#SSAS#balanced hierarchy

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-466 Practice