In this article, we will have a look at how ingestion routines that seem simple at first turn out to be complex in big data warehouses. The example is taken from a customer project and is used in the daily load chain to update freight cost into the overall procurement cost structure.
Refining Source Data
Freight costs are a sum of multiple sources.
- Freightcosts (AIR / SEA)
- Freightcosts (Land)
- Customs costs
The first step is to take the multiple source tables and transform them to the same granularity as the target tables.
Freightcosts (AIR/SEA)
The script creates a new table in the data warehouse that stores the calculated freight costs for items on a monthly and yearly basis. The main purpose of this table is to enable further analysis and reporting of freight costs.
Freightcosts (Land)
The SQL code creates a table named CAL_WARENBESCHAFFUNG_LANDFRACHTEN to analyze the land freight costs in warehousing. The code consists of several Common Table Expressions (CTEs) that work together to compute key performance indicators (KPIs) related to transport costs, quantities, and rates.
Customs costs
The SQL code creates a table named CAL_WARENBESCHAFFUNG_ZOLLKOSTEN to analyze the customs costs in warehousing. The code consists of several Common Table Expressions (CTEs) that work together to compute key performance indicators (KPIs) related to item quantities and customs costs.
Merging Freight cost into Procurement Cost KPI Table
After the source data has the necessary granularity, it is time to merge the data into our overall procurement cost table.
In the given SQL script, three separate MERGE operations are performed to update various cost metrics related to the procurement of goods. These operations focus on updating land freight costs, customs costs, and freight costs for air and sea transportation
- Updating Land Freight Costs: The first MERGE operation in the script is aimed at updating the land freight costs for each item. This operation calculates the costs for each piece and the total costs based on the most recent available rates. The script uses a combination of monthly and yearly rates to obtain the most accurate values. Upon successful matching of records, the target table (TMP_WARENBESCHAFFUNG_KPI) is updated with the new land freight costs per piece and the total land freight costs.
- Updating Customs Costs: The second MERGE operation updates the customs costs associated with the procurement process. Similar to the land freight costs, the script calculates the customs cost per piece and the total customs costs based on the most recent available rates. The calculations are performed using a combination of monthly and yearly rates. Once the matching records are identified, the target table is updated with the new customs costs per piece and the total customs costs.
- Updating Air and Sea Freight Costs: The final MERGE operation in the script updates both the air and sea freight costs. The script calculates the air and sea freight costs per piece and the total costs using a combination of monthly and yearly rates. If either air or sea freight costs are unavailable for a particular month, the script uses the available data to calculate the costs. The target table is then updated with the new air and sea freight costs per piece, as well as the total air and sea freight costs.
Conclusion
The SQL script offers an efficient way to update procurement cost metrics within a data warehouse. By using a combination of monthly and yearly rates, the script ensures that the most accurate and up-to-date values are used to calculate land freight, customs, and air and sea freight costs. This allows businesses to monitor their procurement performance more effectively and make informed decisions to optimize their supply chain operations.
What seemed easy at first turns out to be quite complex after all.