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.
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
Options
- A
- DimCurrency, DimScenario, DimAccount in parallel
- B
- DimCurrency, DimOrganization in parallel
- C
- DimCurrency, FactFinance in parallel
- D
- FactFinance
- E
- DimCurrency
How the community answered
(20 responses)- A70% (14)
- B15% (3)
- D10% (2)
- E5% (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.
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.
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.
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.
Loading FactFinance first is invalid because fact tables depend on dimension tables being fully populated before they can reference them via foreign keys.
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
Community Discussion
No community discussion yet for this question.
