Query to fetch Dimension attribute for Fact Criteria

[Gurudattap]

Hi,

We are in the design stage of a Banking DWH. In our proposed star schema , the FACT Table stores Account Balances and Customer is one of the DIMENSIONS around it. For a particular report, I want to fetch Cusomer Details for customers having Account Balance > Constant. In such a scenario, with Oracle database, what is the best way to put the query? Basically in this case, I am fetching "Dimension Attributes" based on "Fact Data in where condition" and not other way round (which would be our normal FACT/Balance queries).

Regards,

Gurudatta

 

[blom0344]

Hello Gurudatta,

Maybe I am missing something, but how would you define the other way around when you want to collect dimension details from a condition that can only refer to data from your fact table? There is no way to get the same result from reversing collected data / condition. I can imagine that the 'normal' type of query would use a condition on the dimension table to collect data from the facttable, but this will suit totally different type of questions.

Are you perhaps wondering how to improve performance or how to get the appropriate SQL in the first place?

T. Blom
Information analyst
tbl@shimano-eu.com

 

[Gurudattap]

Yes, you have got me right.

The situation that I am talking about is kind of reverse join. I remember having read that Star Schema is supposed to improve join performance, but then generally you would be querying on Fact Table with Dimension in where condition. But here the Fact is in where Condition and DImension in Select clause. What would be the response of such reverse queries given that Star Schema is implemented. Would Oracle's response be equally good for both types of queries? Or for such queries (which will be periodic, but certainly not rare), will some different type of indexing be required?

Regards,

Gurudatta

 

[blom0344]

Hello Gurudatta,

I think there is no easy answer to your question. I use Star-schema's in a DB2 environment with something like 1.6 million records to query on (a combined sales/order table) and performance is acceptable in either way. I suggest that you start with some testing to get an idea how fast queries are with your set-up. I wonder if you want to introduce extra indexes if you start to use where clauses with such measures as Account Balance. This type of queries are inherently slower, cause normal indexing is on dimensions. Best performance will always be with conditions on indexed fields in the dimension table. Be sure , in anyway, to keep statistics up to date on the big facttable to keep query-speed optimal.

To give an idea: it takes about 12 seconds to get all customers with sales >10.000 from my facttable and about half the time to get salesdata from a specific customer using condition on an indexed dimension table....... T. Blom
Information analyst
tbl@shimano-eu.com