Number of Dimensions around a FACT Table.

View previous topic View next topic Go down

Number of Dimensions around a FACT Table.

Post  akvbigb on Thu May 30, 2013 7:40 pm

Hi,

We have a dimensional model with two accumulating fact tables snapshots. One is daily and the other is monthly. We have around 25 product specific Extended fact tables that are joined to these fact tables using the Natural Key. And we have around 25 business dimensions surrounding this fact table. And the model is perfect star schema.

But, we are facing severe performance issues. Sometimes our cognos takes more than 20 minutes and then it says report execution timed out.
We have set the report time limit to 20mins.

We have indexed all the columns that are mostly used in reporting. also, we are maintaining referential integrity between the facts and dimension tables.

What could be the issues with the dimensional model.

can someone please advise what should I be checking at?

Thanks in advance,

AKV

akvbigb

Posts : 2
Join date : 2013-05-30

View user profile

Back to top Go down

Re: Number of Dimensions around a FACT Table.

Post  ngalemmo on Thu May 30, 2013 8:33 pm

akvbigb wrote:Hi,
Extended fact tables that are joined to these fact tables using the Natural Key.
AKV

Star schemas do not have FK relationships between facts. What is the nature of these extended facts?

Also, 25 dimension seems high, but not out of the question. Do you have a lot of simple one or two column dimension tables? You may want to consider consolidating some of them where it makes sense.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Number of Dimensions around a FACT Table.

Post  LAndrews on Fri May 31, 2013 12:43 pm

You need to look at the SQL generated by your cognos report.

It should clarify how the metadata has been defined in framework manager.

From there you can validate that the correct indexes/keys/joins have been defined.

Using extended facts may be the issue (they should probably be their own star schema) - but analyze the sql first.


LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Number of Dimensions around a FACT Table.

Post  hang on Fri May 31, 2013 8:17 pm

Don't over index your dimensions. You may not need any index other than PK constraint on SK for small dimensions (<1000 or even 10k). Referential constraints between fact and dimensions are fine. Replace B-tree (traditional) indexes with bitmap indexes for dimension FK in fact tables. Bitmap index boosts performance significantly for low cardinality column in big fact table, many times faster than B-tree index.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Number of Dimensions around a FACT Table.

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum