Redbrick List Forum

Question on Precomputed View

Posted By: Direk Phaisitwanitkul
Date: Friday, 20 June 2003, at 5:51 a.m.

Hi all,

=20

Is there anyone out there using precomputed view of Red Brick. I have
problem when my fact table have 2 columns reference to the same
dimension table. And I need both columns to be included in aggregate
table. Then I can't utilize precomputed view when the query has that 2
columns.

=20

For example:

=20

I have 1 fact table & 3 dimension tables

=20

Fact: fact_tab (key_date1, key_date2, key_tab2, key_tab3,
measure1,measure2)

Dimension: dim_date (key_date,cal_date,....) , dim_tab2 (key_tab2),
dim_tab3 (key_tab3)

Reference: fact_tab.key_date1 -> dim_date.key_date, fact_tab.key_date2
-> dim_date.key_date

fact_tab.key_tab2 -> dim_tab2.key_tab2, fact_tab.key_tab3 ->
dim_tab3.key_tab3

=20

I create aggregate table like this:

=20

agg_tab (key_date1, key_date2, key_tab2, measure1, measure2)=20

Reference: agg_tab.key_date1 -> dim_date.key_date, agg_tab.key_date2 ->
dim_date.key_date

agg_tab.key_tab2 -> dim_tab2 (key_tab2)

=20

create precomputed view vw01 as

select key_date1, key_date2, key_tab2, sum(measure1), sum(measure2)

from fact_tab

group by key_date1, key_date2, key_tab2

using agg_tab (key_date1,key_date2,key_tab2,measure1,measure2)

=20

When I use only 1 field of date as follow:

=20

select dim_date.cal_date, sum(measure1)

from fact_tab, dim_date

where fact_tab.key_date1=3Ddim_date.key_date

group by dim_date.cal_date

=20

It will utilize aggregate table but for following query

=20

select dim_date1.cal_date, dim_date2.cal_date, sum(measure1)

from fact_tab, dim_date dim_date1, dim_date dim_date2

where fact_tab.key_date1=3Ddim_date1.key_date and
fact_tab.key_date2=3Ddim_date2.key_date

group by dim_date1.cal_date, dim_date2.cal_date

=20

don't utilize aggregate table. Anyone have idea ?

=20

Thanks,

Direk P.

Messages In This Thread

• Question on Precomputed View
  Direk Phaisitwanitkul -- Friday, 20 June 2003, at 5:51 a.m.
  • Re: Question on Precomputed View
    Thomas Randell -- Friday, 20 June 2003, at 7:16 p.m.

Redbrick List Forum is maintained by Administrator with WebBBS 5.12.