I am working on a universe with 2 facts and 6 shared dimensions. Most of the reporting requirements are based off of one fact at a time and none of them (so far) will generate cross facts queries. The way I am thinking of designing this universe is creating 2 contexts, one for each [fact + Dimensions] and unchecking the “Multiple SQL statements for each context” in SQL->Multiple Paths parameters to avoid cross-fact queries.
This is giving me “incompatible objects” message when I try to pull measures from multiple facts and which is what customer is looking for too and also they do not want 2 separate universes. I was wondering if there is a better way to do this OR what problems I might face with this solution.
Note: These 2 facts are at different grains and my understanding is multi fact universes work only if they have the same grain. So I am trying to keep these 2 facts disconnected. Am I heading in right direction??
The best way to keep users from running a single query across facts is to seperate the facts into seperate universes. Unchecking that box (Multiple queries for each context), as you’ve seen, will not solve the problem. In fact, you really should keep that checked. Unchecking it causes the problem that you’re seeing.
I agree with you on the fact that there should be 2 separate universes for this particular requirement but this is not acceptable for the customer. So at this point my question is: Is there any other way to avoid cross-fact queries?
The only problem I see so far with the above approach is the “incompatible object” message while pulling objects from both facts in one query, which is so far acceptable for customer. Is there any other problem(s) I might face using the above approach with regard to result-set returned or anything else??
Another approach is user education. Train them, and give examples of what will and won’t work…if you spend a little time explaining in simple terms why it works the way it does, and make a convincing case (e.g. backed up with meaningful examples that the users can identify with) you could save a lot of pain in the future. You can also put documentation on the classes and potentially objects saying “Oi, don’t use this with that”…sort of thing
… and “cusomer acceptance” is ultimately all that matters. We successfully use this approach in many universes (create contexts then uncheck the “multiple SQL statement” option), and we’ve educated the users (about 2,000) what that “incompatible objects” error means.
Your responses provided me good feedback on the approach I was using and now I feel comfortable because Dwayne said he had used it successfully in some universes provided user is educated.
You can also use the Aggregate Navigation feature to make tables incompatible with one another. When I worked for Business Objects I used this feature in a universe over a datawarehouse to ensure that users did not select objects from 2 dimension tables so no reason why you can’t use it to ensure that objects from one table are incompatible with objects from another table be it fact or dimension.
call me stupid, but reading this topic gave me the creeps.
And then advising 2 universes ??? Or unchecking “multiple SQL statement”?? What the…you people are joking right ??
Why do you think there’s something like contexts in there ? It’s to ENABLE you to report on tables with different grain levels in ONE dataprovider without the end user having to think about incompatible results…
Im facing one problem with 2 facts… i have one universe with 2 fact tables and many dimensions. I have a fact A and fact B, for example. On Fact A i have a field number called A.field1 and on a Fact B i have B.field2. I really need to create a pre-calculated measure on universe with a formula: A.field1 - B.field2
I have 2 contexts, A and B and these facts are not linked directly, i link them with the dimensions.
The only way i finded is: Create one measure object for each one (one for field1 and one for field2 and calculate this on reports. i already tested it and works. Do you know another solution for this? i really need to create this pre-calculated object (only one).
You cannot creation calculations that cross contexts in the universe, they have to be done in the report as you have already tested. In order to do the calculation in the universe, you will have to figure out some way to bring the two fact tables together in the same context.
Ok, but i have 2 facts with different cardinalities level. How can i link these facts? If i link whit the commons PK, the final value is not ok, because in the fact 1, i have 5 PK and in the fact two i have 4 PK.
Different fact tables do not get joined to each other in the universe, unless they are different levels of the same fact. They share common dimensions, and get linked together on the report for calculations.
Fact tables go into different contexts.
Universe calculations cannot cross contexts.
Therefore you simply cannot combine fact values into a single object in the universe.
Michael, I have a question here, how do you join same fact table which are in different levels, which in turn they will be in different context as I assumed?
lavinaluo, I was referring to two tables which combine to make up a single fact. An example would be an Invoice Header table, and an Invoice Line Item table. Together they make up a single fact, and so they are joined.
If you do that, and you get facts from both fact tables, you’ll get unexpected results. if for example the header contains the discount amount per invoice, and the lineitem would containt the amount per item then the result of the proposed solution would give you a discount per lineitem whereas you would expect just one discount per invoice.
My solution would be to duplicate the invoiceheader table, then join this duplicate header once to the header itself and once to the item table. Then create two different contexts for each fact, and both contain the duplicated table. Then if you’re creating dimension objects from the header table, take them from the duplicate table, facts can come from both fact tables.
If you’re getting a measure from each table, Webi will, by default, generate a separate SQL statement for each table. This is due to a setting in the universe called “Multiple SQL Statements for each table”.
I don’t think that setting exists, you probably mean the setting “Multiple SQL Statements for each measure.” Unless it’s recently renamed.
And that setting is not something you always want (allthough it is the default when creating a new universe)…
(BOB member since 2002-08-08)
(BOB member since 2002-08-15)
(BOB member since 2002-09-19)
(BOB member since 2005-08-01)
(BOB member since 2002-06-20)