A data modeler is consolidating sales data from multiple regional databases into a single Power BI model. Each regional database uses a different unique identifier for products, but a master 'Product Mapping' table exists that links all regional product IDs to a single global 'MasterProductID'. The model needs to analyze sales by 'MasterProductID' and include region-specific product names. Which data modeling approach is best for handling these varied product IDs and integrating them into a unified model for analysis?
- ACreate a composite key in the 'Sales' table by concatenating 'RegionID' and 'RegionalProductID'.
- BEstablish a single 'Product' dimension table with 'MasterProductID' and create inactive relationships to regional sales tables.
- CMerge the 'Product Mapping' table with each regional sales table in Power Query to replace regional IDs with 'MasterProductID'.
- DCreate a bridge table between the 'Product' dimension and the 'Sales' fact table to resolve multiple product IDs.
Show answer & explanationAnswer & explanation
Correct answer: C. Merge the 'Product Mapping' table with each regional sales table in Power Query to replace regional IDs with 'MasterProductID'.
Merging the 'Product Mapping' table with each regional sales table in Power Query (using a left outer join) is the most straightforward and efficient approach here. This allows replacing the regional product IDs with the 'MasterProductID' directly within the sales tables during data transformation. This results in a unified 'MasterProductID' column in the 'Sales' fact table, enabling a single, clean relationship to a global 'Product' dimension table based on 'MasterProductID', simplifying the model and improving query performance.
Why the other options are wrong
- A. Creating a composite key would introduce a new high-cardinality column and require a composite key in the 'Product' dimension as well, complicating relationships and potentially impacting performance. It's less ideal than using a single 'MasterProductID'.
- B. Using inactive relationships would require complex DAX (e.g., USERELATIONSHIP) for every measure that needs to use the 'MasterProductID', adding complexity and potentially impacting performance. It's better to unify the IDs during data load.
- D. A bridge table is typically used for many-to-many relationships between two dimension tables or to resolve multiple 'one-to-many' relationships from a single dimension to a fact table. It's not the most direct solution for unifying different foreign keys to a single primary key in the fact table.
Data Unification (ETL)
Data unification involves standardizing disparate data elements (e.g., different IDs, naming conventions) from various sources into a consistent format during the Extract, Transform, Load (ETL) process in Power Query, before loading into the data model.
- Performed during Power Query transformations.
- Standardizes identifiers and attributes.
- Simplifies the data model (cleaner relationships).
- Improves model performance and DAX measure simplicity.
- Ensures consistent reporting across disparate sources.
Memory trick: Standardize IDs in Power Query, keep the model clean and lean.