Redbrick List Forum

Re: Question on Precomputed View

Posted By: Thomas Randell
Date: Friday, 20 June 2003, at 7:16 p.m.

In Response To: Question on Precomputed View (Direk Phaisitwanitkul)

Hi Direk,
I checked the Vista User's Guide and on page 2-14 it states that
"Queries that contain multiple references to the same table in the FROM
clause" cannot be re-written. I tried to use a synonym of the date table
but that didn't work either. An obvious solution would be to duplicate the
date dimension and change your fact table to reference two different date
tables.... It's Friday afternoon and I'm a little brain dead. How
general of a solution are you looking for? Are there just a few specific
queries that you want to speed up?

Tom Randell
Worldwide Data Management BI Sales Support
600 Anton Blvd, Suite 400
Costa Mesa, CA 92626
Tel: (714) 438-5773



"Direk Phais...."
<DirekP@dtac.co.t To: redbrick-list@iiug.org
h> cc:
Sent by: Subject: Question on Precomputed View [96]
forum.subscriber@
iiug.org


06/20/2003 02:51
AM






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.