Current Position:Home > Multiple fact tables, aggregation and model problems

Multiple fact tables, aggregation and model problems

Update:10-11Source: network consolidation
Advertisement
Hi,
I am developing a OLAP Application with 2 dimensions (product,location) and a few measures (sales_qty, sales_value, sale_price, cost_price, promotion_price, average_market_price, etc). I also have a BiBean Crosstab to explore the data.
I'm having the following problems:
- The measures are in different fact tables.
When using cwm2_olap_table_map.Map_FactTbl_Measure, I can only map the measures from the first fact table, the others return "Exact fetch returns more than the request number of rows".
- The 'price_' measures shouldn't aggregate to higher levels of the dimension hierarchies. I have changed the default aggregation plan, but in the crosstab data is still shown in the higher levels. Is there any way to show N/A in levels that I don't want to aggregate?
- How can I add a calculated field that is the result of a complex set of business rules (with IF/THEN/ELSE statements and calls to existing oracle functions) and precalculate it during batch?
Thanks,
Ricardo

The Best Answer

Advertisement
Keith, thanks for the quick answer!
Some questions regarding your comments:
1) The measures are in different fact tables
- My application will show all the measures in the same report. My question is, will performance be affected by creating separate cubes or creating one cube? I believe that if I don't use an AW, it shouldn't, but I will have an AW. Performance is the main reason why I'm using Oracle OLAP. I need fast access to measures in different fact tables in one report. Those measures will be used in complex calculated fields, and probably in 'what if' analysis.
2) When using cwm2_olap_table_map.Map_FactTbl_Measure, I can only map the measures from the first fact table, the others return "Exact fetch returns more than the request number of rows".
Here is the complete script I am using to create the cube:
execute cwm2_olap_cube.Create_Cube('OLAP','CUBE_REL_3','CUBE_REL_3','MY_CUBE','MY_CUBE');
execute cwm2_olap_cube.add_dimension_to_cube('OLAP', 'CUBE_REL_3','OLAP', 'DIM_STORE');
execute cwm2_olap_cube.add_dimension_to_cube('OLAP', 'CUBE_REL_3','OLAP', 'DIM_ITEM');
-- MEASURES - FACT TABLE 1 (F_SALES)
execute cwm2_olap_measure.create_measure('OLAP', 'CUBE_REL_3','SALES_MARGIN', 'Sales Margin','Sales Margin', 'Sales Margin');
execute cwm2_olap_measure.create_measure('OLAP', 'CUBE_REL_3','SALES_QTY','Sales Quantity','Sales Quantity','Sales Quantity');
execute cwm2_olap_measure.create_measure('OLAP', 'CUBE_REL_3','SALES_VALUE', 'Sales Value','Sales Value', 'Sales Value');
-- MEASURES - FACT TABLE 2 (F_PVP_MIN_MAX)
execute cwm2_olap_measure.create_measure('OLAP', 'CUBE_REL_3','PVP_MIN','pvp min','pvp min','pvp min');
execute cwm2_olap_measure.create_measure('OLAP', 'CUBE_REL_3','PVP_MAX', 'pvp max','pvp max', 'pvp max');
-- FACT TABLE 1 MAPPINGS
execute cwm2_olap_table_map.Map_FactTbl_LevelKey('OLAP','CUBE_REL_3','OLAP','f_sales','LOWESTLEVEL','DIM:OLAP.DIM_STORE/HIER:STORE/LVL:L0/COL:COD_STORE;DIM:OLAP.DIM_ITEM/HIER:ITEM_HIER1/LVL:L0/COL:COD_ITEM;DIM:OLAP.DIM_ITEM/HIER:ITEM_HIER2/LVL:L0/COL:COD_ITEM;');
execute cwm2_olap_table_map.Map_FactTbl_Measure('OLAP', 'CUBE_REL_3','SALES_MARGIN','OLAP','f_sales','SALES_MARGIN', 'DIM:OLAP.DIM_STORE/HIER:STORE/LVL:L0/COL:COD_STORE;DIM:OLAP.DIM_ITEM/HIER:ITEM_HIER1/LVL:L0/COL:COD_ITEM;DIM:OLAP.DIM_ITEM/HIER:ITEM_HIER2/LVL:L0/COL:COD_ITEM;');
execute cwm2_olap_table_map.Map_FactTbl_Measure('OLAP', 'CUBE_REL_3','SALES_QTY', 'OLAP', 'f_sales', 'SALES_QTY','DIM:OLAP.DIM_STORE/HIER:STORE/LVL:L0/COL:COD_STORE;DIM:OLAP.DIM_ITEM/HIER:ITEM_HIER1/LVL:L0/COL:COD_ITEM;DIM:OLAP.DIM_ITEM/HIER:ITEM_HIER2/LVL:L0/COL:COD_ITEM;');
execute cwm2_olap_table_map.Map_FactTbl_Measure('OLAP', 'CUBE_REL_3','SALES_VALUE', 'OLAP', 'f_sales', 'SALES_VALUE','DIM:OLAP.DIM_STORE/HIER:STORE/LVL:L0/COL:COD_STORE;DIM:OLAP.DIM_ITEM/HIER:ITEM_HIER1/LVL:L0/COL:COD_ITEM;DIM:OLAP.DIM_ITEM/HIER:ITEM_HIER2/LVL:L0/COL:COD_ITEM;');
-- FACT TABLE 2 MAPPINGS
execute cwm2_olap_table_map.Map_FactTbl_LevelKey('OLAP','CUBE_REL_3','OLAP','f_pvp_min_max','LOWESTLEVEL','DIM:OLAP.DIM_STORE/HIER:STORE_HIER1/LVL:L1/COL:COD_STORE;DIM:OLAP.DIM_ITEM/HIER:ITEM_HIER1/LVL:L4/COL:COD_ITEM;DIM:OLAP.DIM_ITEM/HIER:ITEM_HIER2/LVL:L4/COL:COD_ITEM;');
execute cwm2_olap_table_map.Map_FactTbl_Measure('OLAP', 'CUBE_REL_3','PVP_MIN','OLAP','f_pvp_min_max','PVP_MIN', 'DIM:OLAP.DIM_STORE/HIER:STORE_HIER1/LVL:L1/COL:COD_STORE;DIM:OLAP.DIM_ITEM/HIER:ITEM_HIER1/LVL:L4/COL:COD_ITEM;DIM:OLAP.DIM_ITEM/HIER:ITEM_HIER2/LVL:L4/COL:COD_ITEM;');
execute cwm2_olap_table_map.Map_FactTbl_Measure('OLAP', 'CUBE_REL_3','PVP_MAX', 'OLAP', 'f_pvp_min_max', 'PVP_MAX','DIM:OLAP.DIM_STORE/HIER:STORE_HIER1/LVL:L1/COL:COD_STORE;DIM:OLAP.DIM_ITEM/HIER:ITEM_HIER1/LVL:L4/COL:COD_ITEM;DIM:OLAP.DIM_ITEM/HIER:ITEM_HIER2/LVL:L4/COL:COD_ITEM;');
-- CUBE VALIDATE
execute CWM2_OLAP_VALIDATE.Validate_Cube('OLAP','CUBE_REL_3');
The error is in the cwm2_olap_table_map.Map_FactTbl_Measure command for the first measure of the second fact table. Am I doing something wrong?
Regarding issues 3) and 4), I will follow your sugestions. I'll get back to you on this later.
Once again, thanks for the help, it is being most helpful.
Ricardo Sá

Database error: [Table 'ac_posts' is marked as crashed and should be repaired]

SELECT a.*, b.post_content FROM ac_posts a INNER JOIN ac_posts_content b ON a.ID = b.ID WHERE a.ID IN(485235,888001,164051,1302107,1580067,1446154,190972,362961,481318,765362) ORDER BY FIELD(a.ID, 485235,888001,164051,1302107,1580067,1446154,190972,362961,481318,765362);