nerdexam
Microsoft

70-467 · Question #168

You are designing a customer dimension for a multidimensional OLAP (MOLAP) database. The dimension table will contain millions of rows of data. Customer dimension attributes will come from the…

The correct answer is D. Create a dimension table for each source, and a cube dimension for each source. Separating dimension tables and cube dimensions by source allows independent processing schedules and enables the geographic dimension to serve as a reference dimension for optimized filtering.

Design a data warehousing solution

Question

You are designing a customer dimension for a multidimensional OLAP (MOLAP) database. The dimension table will contain millions of rows of data. Customer dimension attributes will come from the following two sources:

  • An enterprise resource planning (ERP) system that contains millions

of rows of data. Attributes from this source will be updated daily.

  • A marketing database that contains thousands of rows of data

representing customers' geographic information. Attributes from this source will be updated monthly. Most report queries against the cube use attributes from both sources. Customers in the cube dimension will frequently be filtered by the geographic attributes. You need to design the dimension table and cube dimension to ensure that report queries perform well, and minimize the time required to reprocess cube attributes. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

Options

  • ACreate a dimension table for each source.
  • BCreate one dimension table that combines data from both sources.
  • CCreate one dimension table that combines data from both sources.
  • DCreate a dimension table for each source, and a cube dimension for each source.

How the community answered

(45 responses)
  • A
    27% (12)
  • B
    16% (7)
  • C
    9% (4)
  • D
    49% (22)

Why each option

Separating dimension tables and cube dimensions by source allows independent processing schedules and enables the geographic dimension to serve as a reference dimension for optimized filtering.

ACreate a dimension table for each source.

Creating separate dimension tables without separate cube dimensions still couples the two sources in SSAS processing, preventing optimized filtering and independent refresh schedules.

BCreate one dimension table that combines data from both sources.

Combining both sources into one dimension table forces the entire large dimension to be reprocessed even when only the monthly geographic data changes, increasing processing time unnecessarily.

CCreate one dimension table that combines data from both sources.

This is identical to option B - combining sources into one table carries the same performance and processing drawbacks without the flexibility of independent update cycles.

DCreate a dimension table for each source, and a cube dimension for each source.Correct

Creating a separate dimension table and cube dimension for each source lets the small geographic dimension (thousands of rows, monthly updates) be processed independently from the large ERP dimension (millions of rows, daily updates). In SSAS, the geographic cube dimension can act as a reference or related dimension, improving query performance for the frequent geographic attribute filters without reprocessing the entire combined dimension.

Concept tested: SSAS dimension table design for multiple data sources

Source: https://learn.microsoft.com/en-us/analysis-services/multidimensional-models/dimensions-in-multidimensional-models

Topics

#MOLAP dimension design#dimension partitioning#SSAS cube design#large dimensions

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-467 Practice