BusinessObjects Board

Multiple returns of measure objects

I have inherited a universe that does not employ a true star-schema and it is causing me grief. The very wide fact table (Shipment) has few dimensions (many decodes of were done in ETL and placed into the very wide fact table - again, i inherited this), but most of the joined tables are not true dimensions. They instead have multiple rows for a single fact table row. An example would be a single Shipment (fact) has 2 Commodities (dimension), so the Shipment ID is on the Commodity table. Therefore, any measure from Shipment gets returned as many times as the number of objects from the Commodity when an object from the Commodity table is included in the query. How do I get around this? Can I not have any objects defined as measures if I have a join from the fact table that is actually one fact to many dimensions? If this does not make sense, I can include SQL and/or a report. Or, if this has already been covered, please point me to the discussion. My training books from Business Objects assume a star-schema and I donโ€™t see this in the Cindi Howson book.

Thank you.


stephen :us: (BOB member since 2006-02-17)

Hi,

maybe I am wrong but this looks to me as typical fan trap problem.

Please have a look at this Designer FAQ.

And also have a look at this excellent explanation of fan trap from Andreas and the way how to solve it.


Marek Chladny :slovakia: (BOB member since 2003-11-27)

The design that I have is the one-to-many that is similar to Orders to Orders Detail in the Xtreme Sample universe from Business Objects. I get the same result in the Xtreme univerese when I select a measure from a non-fact table. In that univerese, Order Amount is not in a fact table. Therefore, multiple rows are returned for each corresponding row in Orders Detail. As long as there is an attribute from Orders Detail, the report works fine. Once there are only rows from Orders, Business Objects automatically performs report level aggregation.

At least I now understand it. Next time, there will only be measures in the lowest level of granularity.


stephen :us: (BOB member since 2006-02-17)