nerdexam
Microsoft

70-466 · Question #31

You are developing a BI Semantic Model (BISM) that will be used to analyze complex budgeting and forecast data sourced from a financia database. The model will be deployed to a server with 32 GB of RA

The correct answer is C. multidimensional project. With 10 TB of source data that is rapidly growing and only 32 GB of RAM on the server, a tabular in-memory model is infeasible, and a multidimensional (OLAP) project provides proven scalability and high query performance for complex analytical workloads at petabyte scale.

Build an analysis services database

Question

You are developing a BI Semantic Model (BISM) that will be used to analyze complex budgeting and forecast data sourced from a financia database. The model will be deployed to a server with 32 GB of RAM. The source data, located in a SQL Server data warehouse, is currently using 10 terabytes of disk space and is doubling in size every three months. The model will be queried by staff in the accounting department by using Microsoft Excel 2010. You need to ensure the highest query performance and scalability for the accounting department queries. Which project type should you choose?

Options

  • APowerPivot workbook
  • Btabular project that uses the In-Memory query mode
  • Cmultidimensional project
  • Dtabular project that uses the DirectQuery query mode

How the community answered

(27 responses)
  • A
    11% (3)
  • B
    22% (6)
  • C
    63% (17)
  • D
    4% (1)

Why each option

With 10 TB of source data that is rapidly growing and only 32 GB of RAM on the server, a tabular in-memory model is infeasible, and a multidimensional (OLAP) project provides proven scalability and high query performance for complex analytical workloads at petabyte scale.

APowerPivot workbook

PowerPivot workbooks are Excel-based, client-side in-memory tools limited to the resources of a single workstation; they cannot handle 10 TB of data or serve multiple concurrent accounting users from a central server.

Btabular project that uses the In-Memory query mode

A tabular in-memory project requires the entire dataset to be loaded into RAM; 10 TB of data cannot fit into 32 GB of RAM, making this mode completely impractical for the given server configuration.

Cmultidimensional projectCorrect

SSAS multidimensional databases use aggregations stored on disk and pre-computed measures in MOLAP storage, allowing efficient querying of very large datasets without requiring the full dataset to fit in RAM. It is the established enterprise-grade solution for complex budgeting and forecasting analysis via Excel PivotTables, providing both the scalability needed for 10 TB-plus data and optimized query performance independent of RAM constraints.

Dtabular project that uses the DirectQuery query mode

Tabular DirectQuery mode avoids in-memory limitations by querying the source database directly, but it cannot leverage pre-computed aggregations, resulting in lower query performance compared to multidimensional MOLAP for complex analytical workloads.

Concept tested: SSAS project mode selection for large-scale data

Source: https://learn.microsoft.com/en-us/analysis-services/comparing-tabular-and-multidimensional-solutions-ssas

Topics

#BISM project type#multidimensional vs tabular#scalability#query performance

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-466 Practice