Number of Dimensions around a FACT Table.

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 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.

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.

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.