Different Grains in the Model but use the higher grain in the Fact

[sudip.bandyopadhyay]

I am having Sales Fact and a Product Dimension associated. Now new requirement is, for each Product there are several Assessments (PROD_ASSESSMENT_ID). So, Product to Prod_Assessement is 1:M relation. But the Sales Fact will be related to Product Dimension and there is rerquirement to relate Sales_Fact with Prod_Assessment.

How shall I model this relationship in my Dimensional Model?

[BrianJarrett]

Most likely you'll need a fact table at the grain of product assessment. You may have to allocate some fact data down to that level; I'm not familiar with your fact data so I can't say for sure. You'll then end up with multiple sales fact rows per product; one for each product assessment. That'll be joined to your new sales assessment dimension table.

Sounds to me that the product assessment rolls up to product (a flattened hierarchy within the product assessment dimension), so your existing product dimension is now really a shrunken dimension. Your existing sales fact (one row per product) would be attached to this shrunken product dimension. That dimension wouldn't hold any assessment information.

Aggregate navigation would then need to be applied to determine which fact table to get your data from. If your users don't need anything related to product assessment, they pull from your sales_fact table. That'll be joined to your existing shrunken product dimension. If they need the product assessment info, they'll then get pushed down to your new sales_assessment_fact table and, subsequently, that'll be joined to your new sales_assessment_dim table.

Hope this helps.

Brian