I am working on a universe (star schema) in designer that is getting quite complex (BO 4.1, Oracle 7.3). I have 7 dimension tables (agent, company, product, policy, state, time, fund) and multiple fact tables (licensed state fact, transaction fact, policy agent fact, and fund fact) that utilize these tables. Some of the primary keys for these fact tables have as many as 13 columns. Is there a formal join strategy I should be following for BusinessObjects? Up to now primary keys have been a few columns; I would join tables in the universe based on the occurrence of the .DMSN table primary keys in the fact tables. Is there a limit to the number of joins a universe can have?
I am working on a universe (star schema) in designer that is getting quite complex (BO 4.1, Oracle 7.3). I have 7 dimension tables (agent, company, product, policy, state, time, fund) and multiple fact tables (licensed state fact, transaction fact, policy agent fact, and fund fact) that utilize these tables. Some of the primary keys for these fact tables have as many as 13 columns. Is there a formal join strategy I should be following for BusinessObjects? Up to now primary keys have been a few columns; I would join tables in the universe based on the occurrence of the .DMSN table primary keys in the fact tables. Is there a limit to the number of joins a universe can have?
There should be no problems with the number of joins, and, you can use “complex” joins between tables, which contain the complete set of fields within one single join.
But what you should consider is another approach: use surrogate keys instead of “concatenated” foreign keys for the dimension tables. This will give you only the same number of key fields in the fact tables as there are dimension tables for that fact.
W.M.
Walter Muellner
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna, Austria Tel. +43-1-8151456-12, Fax: +43-1-8151456-21 e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at