If anyone has ever spent hours trying to find a way to seamlessly implement multiple levels of aggregate tables into a universe, what modeling techniques did you find worked the best? I have aggregate tables of multiple levels (day, month, qtr, year) that need to connect to multiple dimension tables (item, store, etc).
I set up contexts and made all the measures and dimension keys aggregate aware. That works fine, however, if I want to get a description of one of the dimensions in a query (such as store name instead of store id), it creates a synchronized query which does not work correctly. If I try to run two queries (one with store Id and aggregate table data, and one with store id and store name), then try to link them in the report, it doesn’t work either.
I am considering putting the aggregate tables in their own universe, but would be appreciative of any tips or ideas any of you may have.
I checked all my contexts. I have it set up so that each fact table has a context which includes each join from that fact table. Is this the correct way to do it?
I also read an interesting approach in this post
I may try it, however, I would prefer not to start completely over.
I’ve talked to Jesse about this and I found out that the tables are not true aggregates. They are more like rollup tables. The tables in question are actually inventory snapshots.
At the end of the month, the inventory values are moved into history tables. For example on 1/31/03, all the numbers in Current_Inventory table are copied into Inventory_History_Fact with the index of month_id (200301). The month data for the last month of the quarter is copied to Inventory_Quarter_Hist. The last month data of the year is copied into Inventory_Year_Hist. Inventory_Quarter shows the inventory levels at the end of the Quarter (month_id = 200303, 200306, 200309, 200312). Inventory_Year only shows inventory at the end of the year (month_id = 200312).
In a sense Jesse was trying to use aggregate awareness to predictively branch the objects. If she wanted inventories at the end of the year, BO would pick Inventory_Year_Hist objects if she picked year_id as an object. If she wanted inventories at the end of the quarter, BO would pick Inventory_Quarter_Hist objects if she picked quarter_id as an object.
I don’t think that Aggregate Awareness can be used in such a way. Any objects used by aggregate awareness had to be aggregated in some way and that the hiearchies had to absolute. In this case, Time is not a rigid hiearchy. Complicating this issue is that sales data (Qty_Sold) is also in these tables so using these tables for Sales is correct.
I think this a report side problem. The desired reports are one that show the ending_qty for each time level. Here would be my plan. Remove the inventory information from the quarter and yearly history tables but keep all sales data. Build into the Inventory_Hist table some boolean columns like End_OF_QTR and END_OF_YEAR. Then build some conditions based on these columns. Anybody think of anything else.
It sounds like you’re on the right path, although I wouldn’t call this a “report” side problem. Your solution involves changes to the database and universe. However, it sounds logical based on the new information you’ve given us.
Jesse is lucky to be working with someone as smart as you.
(BOB member since 2002-06-25)
(BOB member since 2002-08-08)
(BOB member since 2002-09-27)
What do you mean by not making the dimensions part of the process? 