Optimizing Data Loading in Data Warehousing: Handling Value-to-ID Mapping for Fact Tables

Azure Data Factory

Azure Data Factory (ADF) is a cloud-based data integration service provided by Microsoft as part of the Microsoft Azure ecosystem. It allows you to create, schedule, manage, and monitor workflows that move and transform data between different sources and destinations.

Scenario

In the process of loading data into the data warehouse, the dimension tables are loaded before the fact table. When loading fact data from a CSV or Excel file, a situation may arise where the file contains values instead of IDs. These IDs are typically system-generated and lack business context. In our fact table structure, we prefer storing the IDs from the associated dimension table rather than the provided values.

The blog demonstrates how to handle this scenario in a data flow, showcasing how to reference the foreign key value in our data flow when ingesting data.

Structure of Dimension Tables 

  1. Month (‘month’,’month_id’)
  2. Spend_type(‘spend_type’,’spend_type_id’)
  3. Customers(‘FullName’,’FirstName’,’LastName’,’CustomerId’)

Structure of Excel/CSV file

  1. Monthly_avg_csv(‘FirstName’,’LastName’,’month’,’spend_type’,’ Avg Amount Spent’)

Structure of Fact Table

  1. Avg_Monthly_Spends(‘avg_monthly_spend_id’, ‘CustomerID’ ,’spent_type_id’, ‘month_id’, ‘Avg Amount Spent’)

Steps to Handling Value-to-ID Mapping for Fact Tables

  1. Create the respective facts and dimensions table and insert sample values
  2. Create the sample CSV file with values present in the dimension table.
  3. Upload the file to the data lake storage
  4. Create a link service Azure blob storage and a dataset with CSV file format support
  5. Create a link service Azure SQL DB and data set to SQL DB
  6. Create a pipeline in Azure Data Factory and add data flow activity.
  7. Next click on the + icon to create a new data flow
  8. Create a source in data flow to get data from CSV file by providing an appropriate dataset and configuring source options according to requirement

  1. Create a data flow source to retrieve information from the dimension table within the SQL Database, aiming to obtain corresponding IDs.

  1. Select the “+” icon to incorporate a lookup transformation. Within the lookup stream, choose the ‘month’ field. Then, within the lookup condition, specify the business key, for instance, the actual value of the month, utilizing the dynamic editor using the byName() function and provide column name 

  1. Do a similar for getting the ID value from the other dimension table.
  2. Add sink transformation and keep mapping as auto mapping and in the dataset select the sql db and configure it with Avg_Monthly_Spends table.

Addend Analytics is a Microsoft Gold Partner based in Mumbai, India, and a branch office in the U.S.

Addend has successfully implemented 100+ Microsoft Power BI and Business Central projects for 100+ clients across sectors like Financial Services, Banking, Insurance, Retail, Sales, Manufacturing, Real estate, Logistics, and Healthcare in countries like the US, Europe, Switzerland, and Australia.

Get a free consultation now by emailing us or contacting us.

Email Us Or Contact Us