nerdexam
Microsoft

70-463 · Question #23

You are developing a SQL Server Integration Services (SSIS) package to load data into a data warehouse. You need to establish the correct order for loading each table to maximize parallel processing.

The correct answer is A. 1. DimCurrency, DimScenario, DimAccount in parallel. When loading a data warehouse with SSIS, independent dimension tables should be loaded in parallel in the first step before any fact tables are processed.

Load data

Question

You are developing a SQL Server Integration Services (SSIS) package to load data into a data warehouse. You need to establish the correct order for loading each table to maximize parallel processing. Which order should you use?

Exhibit

70-463 question #23 exhibit

Options

  • A
    1. DimCurrency, DimScenario, DimAccount in parallel
  • B
    1. DimCurrency, DimOrganization in parallel
  • C
    1. DimCurrency, FactFinance in parallel
  • D
    1. FactFinance
  • E
    1. DimCurrency

How the community answered

(20 responses)
  • A
    70% (14)
  • B
    15% (3)
  • D
    10% (2)
  • E
    5% (1)

Why each option

When loading a data warehouse with SSIS, independent dimension tables should be loaded in parallel in the first step before any fact tables are processed.

A1. DimCurrency, DimScenario, DimAccount in parallelCorrect

DimCurrency, DimScenario, and DimAccount are independent of each other with no foreign key relationships between them, so they can all be loaded simultaneously in step 1. Loading all available independent dimension tables in parallel maximizes throughput and minimizes total load time before fact tables can begin processing.

B1. DimCurrency, DimOrganization in parallel

Loading only DimCurrency and DimOrganization in parallel fails to include DimScenario and DimAccount, which are also independent and could be loaded concurrently to maximize parallelism.

C1. DimCurrency, FactFinance in parallel

FactFinance contains foreign key references to dimension tables, so loading it in parallel with a dimension table risks referential integrity violations and is logically incorrect.

D1. FactFinance

Loading FactFinance first is invalid because fact tables depend on dimension tables being fully populated before they can reference them via foreign keys.

E1. DimCurrency

Loading only one dimension table at a time in step 1 serializes work that could run in parallel, preventing maximum throughput.

Concept tested: SSIS data warehouse dimension and fact table load order

Source: https://learn.microsoft.com/en-us/sql/integration-services/data-flow/data-flow

Topics

#parallel loading#table load order#dimension tables#fact tables

Community Discussion

No community discussion yet for this question.

Full 70-463 Practice