nerdexam
Microsoft

70-466 · Question #78

You are planning to develop a SQL Server Analysis Services (SSAS) tabular project. The project will be deployed to a SSAS server that has 16 GB of RAM. The project will source data from a SQL Server 2

The correct answer is A. Configure the database to use DirectQuery mode. Create a columnstore index on all the columns of the fact table.. This question tests the ability to select the best SSAS data access mode when dataset size far exceeds available server memory.

Build a tabular data model

Question

You are planning to develop a SQL Server Analysis Services (SSAS) tabular project. The project will be deployed to a SSAS server that has 16 GB of RAM. The project will source data from a SQL Server 2012 database that contains a fact table named Sales. The fact table has more than 80 billion rows of data. You need to select an appropriate design to maximize query performance. Which data access strategy should you use? (More than one answer choice may achieve the goal. Select the BEST answer.)

Exhibit

70-466 question #78 exhibit

Options

  • AConfigure the database to use DirectQuery mode. Create a columnstore index on all the columns of the fact table.
  • BConfigure the database to use In-Memory mode. Create a clustered index which includes all of the foreign key columns of the fact table.
  • CConfigure the database to use In-Memory mode. Create a columnstore index on all the columns of the fact table.
  • DConfigure the database to use DirectQuery mode. Create a clustered index which includes all of the foreign key columns of the fact table.

How the community answered

(61 responses)
  • A
    66% (40)
  • B
    10% (6)
  • C
    18% (11)
  • D
    7% (4)

Why each option

This question tests the ability to select the best SSAS data access mode when dataset size far exceeds available server memory.

AConfigure the database to use DirectQuery mode. Create a columnstore index on all the columns of the fact table.Correct

With 80 billion rows in the fact table and only 16 GB of RAM available, the xVelocity In-Memory engine cannot load the dataset, making DirectQuery mode the only viable option as it delegates query execution directly to the SQL Server relational engine. Adding a columnstore index on all fact table columns maximizes analytical query performance in DirectQuery mode by enabling batch mode execution and column-level compression.

BConfigure the database to use In-Memory mode. Create a clustered index which includes all of the foreign key columns of the fact table.

In-Memory mode requires the compressed dataset to fit in RAM; 80 billion rows would require far more than 16 GB, making this approach infeasible regardless of index type.

CConfigure the database to use In-Memory mode. Create a columnstore index on all the columns of the fact table.

In-Memory mode remains infeasible due to the memory constraint even with a columnstore index, because the data must still be loaded entirely into RAM.

DConfigure the database to use DirectQuery mode. Create a clustered index which includes all of the foreign key columns of the fact table.

DirectQuery mode is correct, but a clustered index on foreign key columns does not optimize the broad column scans of analytical queries as effectively as a columnstore index covering all fact table columns.

Concept tested: SSAS DirectQuery mode with columnstore index for large datasets

Source: https://learn.microsoft.com/en-us/analysis-services/tabular-models/directquery-mode-ssas-tabular

Topics

#SSAS tabular DirectQuery#In-Memory mode#columnstore index#large fact table performance

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-466 Practice