cancel
Showing results for 
Search instead for 
Did you mean: 
intapiuser
Community Team Member
Community Team Member

mceclip1.png Analytical Need

There are many cases where our data comes in with different granularity levels. For instance, Actual vs. Target where the actuals are at the product level and the targets are per sub category (higher hierarchy of the product). How can we connect them?

mceclip2.png Modeling Challenge 

Logically the connection between two tables are on the same level, but here we need to find a way to make sense of the data while the model needs to support it.

mceclip3.png Solution

Example: Sales vs. Targets
This is how the schema looks before our changes:
Different_Granularity_before.jpg
Image 1. Schema with different Granularity levels. The product only appears in one fact & the subcategory appears in another fact.
We will create a new dim product and it will connect to both fact tables based on the product ID (the current connection of ProductSubCategoryID will be removed).
This will be achieved by creating a master record for each of the sub categories and calling its ID as Product ID.
So in fact the data will hold product sub categories but will be called product ID. So now the products table will contain 2 types of data - Product & sub category in one field (productID).
SQL:
SELECT ProductId, Name, ProductSubCategoryId FROM [Dim_Product] P
union
SELECT -S.ProductSubcategoryID as ProductId, 'N/A' as Name, ProductSubcategoryID FROM [ProductSubcategory] S
SQL explanation:
The query takes the current dim product (first query) and adds new lines to it as master records per each of the sub categories. 
Ordinarily the subcategories will have a different set of identifiers than the products, but in this case they are both small numbers so we added a minus sign to the subcategories.
The minus sign (-S.ProductSubcategoryID) is used in order to define a new set of identifiers for the subcategories, so it won't create any duplicates with the actual Product IDs - they're in the same column). This needs to be done on the other side of the connection (the fact table).
Different_Granularity_query_after.jpg
Image 2. Results from this query. The 1st & 3rd column are the same for the sub categories (except for the minus sign).
Now our schema will look like this:
Different_Granularity_after.jpg
Image 3. Result Schema

Attachments

Rate this article:
Version history
Last update:
‎03-02-2023 08:51 AM
Updated by:
Contributors