nnatraj

04-16-2003, 10:17 AM

After building the schema and getting Mondrian and JPivot to work great I got the rug pulled out from under me with database changes. Currently there are 8 dimension tables (including one that has a time_key that maps to YYYYMM) and two fact tables that are partitioned by time_key (in Oracle 8.1.7). Recently I found out that 7 of the 8 dimension tables (except the one that defines time_key) are going to include the time_key so that they can be partitioned by time_key. The effect of this is that a query that looked like

select a, b, sum(c) from fact_table, dim_1, dim_2, time where fact_table.time_key=time.time_key and fact_table.dim_1_key=dim_1.dim_1_key and fact_table.dim_2_key=dim_2.dim_2_key and time.period in ('YYYYMM')

where a and b come from dim_1 and dim_2 respectively.

In the partitioned case the above query becomes

select a, b, sum(c) from fact_table, dim_1, dim_2, time where fact_table.time_key=time.time_key and fact_table.dim_1_key=dim_1.dim_1_key and fact_table.dim_2_key=dim_2.dim_2_key and dim_1.time_key=time.time_key and dim_2.time_key=time.time_key and time.period in ('YYYYMM')

I can't seem to figure out how to modify my schema to be able to make the additional two joins happen. Any pointers on how to do this will be greatly appreciated. Thanks in advance.

Nattu.

select a, b, sum(c) from fact_table, dim_1, dim_2, time where fact_table.time_key=time.time_key and fact_table.dim_1_key=dim_1.dim_1_key and fact_table.dim_2_key=dim_2.dim_2_key and time.period in ('YYYYMM')

where a and b come from dim_1 and dim_2 respectively.

In the partitioned case the above query becomes

select a, b, sum(c) from fact_table, dim_1, dim_2, time where fact_table.time_key=time.time_key and fact_table.dim_1_key=dim_1.dim_1_key and fact_table.dim_2_key=dim_2.dim_2_key and dim_1.time_key=time.time_key and dim_2.time_key=time.time_key and time.period in ('YYYYMM')

I can't seem to figure out how to modify my schema to be able to make the additional two joins happen. Any pointers on how to do this will be greatly appreciated. Thanks in advance.

Nattu.