ProductHierarchy_Flatten
Source SQL File - Google Cloud Cortex Data Foundation
Google Cloud Cortex Foudantion - Cortex Reporting - (s4) - ProductHierarchy_Flatten
Overview
This reporting view is designed to provide a hierarchical view of product hierarchies in SAP systems. It combines data from multiple tables to create a comprehensive view of product relationships.
Data Sources
| Source Table/View | Description |
|---|---|
{{ project_id_src }}.{{ dataset_cdc_processed_s4 }}.t179 |
SAP table containing product hierarchy data. |
Key Use Cases
- Analyzing product hierarchies for reporting purposes.
- Identifying relationships between products at different levels of the hierarchy.
- Understanding the structure of product categories and subcategories.
Query Logic
The query uses a series of LEFT OUTER JOINs to combine data from multiple rows in the t179 table. It first creates a CTE called h1_h2 that joins rows where the stufe (level) is '1' with rows where the stufe is '2' and the prodh (product ID) of the second row starts with the prodh of the first row. This creates a hierarchical relationship between products at levels 1 and 2. The main query then joins the h1_h2 CTE with the t179 table again to find products at level 3 that have a prodh starting with the prodh of the level 2 product.
Possible relation with SAP Transactions
| Transaction Code | Transaction Description |
|---|---|
| MM01 | Create Material Master |
| MM02 | Change Material Master |
| MM03 | Display Material Master |
Data Lineage
Under Development
Table/View Fields
| Field | Field Description | Description in SAP Dictionary (Comming soon) |
|---|---|---|
| prodh1 | Product hierarchy level 1 | |
| prodh2 | Product hierarchy level 2 | |
| prodh3 | Product hierarchy level 3 |