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(
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?