Two different fact granularities in same fact table?

  • Say I have a database like AdventureWorks that I use to populate my data warehouse. Then I have another data source that has aggregated data by (SalesRegion, Month/Year, Product, TotalUnitsSold, TotalSalesDollars). If I want to compare "my" data (the AW stuff) to the rolled up data, how do I do it? Just create a cube and roll it up as usual, or is there something I have to watch out for? The tricky part is that the "other" data source is aggregated already - there's no breaking it down.

    Would I have two different cubes for the two or can I summarize the AdventureWorks data into the same granularity and then drill into my data too (so they're in the same cube?). Hope this all makes sense.

    The data I get is something like this:

    CREATE TABLE aggSalesData(

    SalesRegionID INT,

    ProductID INT,

    Month TINYINT,

    Year TINYINT,

    Units INT,

    Dollars MONEY

    );

    Then I would just have all the drilldown stuff in my DimProducts table. Will this work okay? (End goal: compare, as much as possible, "our" sales to the competition's, based on the summary data I get (in the form of aggSalesData). maybe I just have to sit down and read more of Data Warehouse Toolkit?

    Thanks!

    Pieter

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply