Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Two different fact granularities in same fact table? Expand / Collapse
Posted Wednesday, August 27, 2014 2:05 PM


Group: General Forum Members
Last Login: Yesterday @ 8:32 PM
Points: 2,134, Visits: 12,264
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,
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?

Post #1608020
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse