I’m designing a universe that contains 4 fact areas, and therefore 4 contexts. The number of dimensions and aliases thereof that are common to all facts is not the same.
In this case because I would like to avoid synchronized joins in the dataprovider and to minimize the incompatible objects message when a report designer/ad-hoc user creates a report I’ve done a couple things and want an opinion on the design.
As I’ve mentioned we have 4 contexts, and this is reflected in the objects pane with 4 main classes. Aside from the facts that are unique to each of the master classes and the uncommon dimensions we will repeat the common dimensions in all of these master classes and within the SQL tab of the parameters we have disabled the ability to create queries that cross multiple contexts. I realize that we are losing the power of contexts but in this case I think it could be more of an issue if we let the adhoc users create dataproviders with the possibility of synchronized joins.
The main reason for having everything in one universe is to minimize the number of universes, I’m sure this is not a new one :roll: . I think this is a common practice, best practice maybe not but as long as the users understand that there is a one to one relationship between master classes to dataproviders than I think that all is well.
I’ve been thinking about this design concept, and it seems ok to me. I can’t think of any problems except that the universe will contain a lot of objects, but as long as they are organized properly in a good class/subclass I can’t see any issues…anyone.
Comments, Concerns or Suggestions are what I’m hoping for. Thanks in advance.
Users will have to be trained to confine themselves to one class. If they don’t do this, they will receive the “incompatible combination of objects” message. Proper user training (as well as warning the help desk) can address this.
Ongoing maintenance of the universe will be more tedious and error-prone since updates to the database design will have to be reflected in more than one class.
If the users really can’t stand the thought of multiple universes, and you have warned them about the above 2 points, then you could go for it and see what happens.
What about the huge disadvantage not to be able to report against 2 or more facts against the same dimension table (conformed dimension) by just creating one data provider, this is usually a HUGE thing in my books.
If you follow your proposal a user would have to create 2 separate data providers (to report against 2 facts).
What is so bad about synchronized queries anyway?
Sure the one fact/measure cannot be split up by the unconformed dimension (which is related to the other fact table), but the information shown will still be correct. You could also create a dummy record (e.g. Not Applicable) for each dimension table and join on it to those fact tables, which usually do not have that dimension…
Thanks for the replies, I was beginning to lose faith.
Judy,
I did explain both your points, however we have been told that our department should keep the number of universes to a minimum, meaning one for now as there will be more coming. But what we have is a logical grouping of fact tables. I figured the repeating of the classes and keeping objects grouped based on their relationship to the facts was the best way to help the user to understand to query from one class. The maintenance can be aided by integrity checks, and search and replace. It is all kind of political on why we can only create one universe. Again, thanks for the reply.
Andreas,
I do realize that there is a one to one relationship between queries and fact tables in my design, but the rules of thumb I’ve read for universe design was to always avoid synchronized queries, as I’ve seen this in many posts and documentation as well. I do like and understand the power of one dataprovider with multiple queries. Trust me, I don’t want to do anymore work than I have to, and if it benefits the project and the user community I’m all for it. Although, I did run across some posts, very few, stating that they can sometimes be ok, but that sometimes is not good enough for me, especially without an supporting explanation in the posts. Unfortunately, I cannot suggest to have a record included for the unconformed dimensions in the tables. I would appreciate a bit of an explanation on how synchronized queries would be ok, if possible.
From my understanding BO always did it’s “best guess” at merging data when synchronized queries were joined.
Well alright, I’m not sure I’ve ever heard anybody say this but I’ve always considered synchronized queries to be a huge strength. I’ve often found that users are often reluctantly developing reports that they used to do in excel or some other tool. They are often intimidated by the range of functionality available to them. Merging data providers is pretty much the most complicated thing a user will do, except for calculation context. Why would you want to force them to deal with merging data and figuring how to write the correct queries with your redundant Dimension objects when BO will do it automatically
What is there to guess at when synchronizing? Synchronization is based on common objects. Things get dicey when your users expect results to be synchronized on the dimensions that are not common to all the measures in the report.
I’m not trying to beat up on you but I think you are making some assumptions that may be a bit misinformed. If I were you I’d keep in mind your user base, will they really be able to manually merge data providers and undertand the implications of mismatched dimensions in that situation.
Here are a few examples of my concerns with synchronized queries in one dataprovider.
(note: see the sticky stating that synchronized queries are very rarely acceptable)
I really didn’t think it would be that difficult for users to merge data, we are talking about power users here so they are trained in BO…and if they were to bring in extra dimensions from one dataprovider and wanted to add them to the common ones, they could by creating a report level detail and associating the detail (as long as it is 1:1) with a like dimension that it made sense to associate with. The merging is the step in which they would understand that they had less or more objects than what was required, and could remove objects that didn’t make sense, add ones they forgot or as I mentioned use report level details. If the results of the synchronized queries are not always going to be correct, than how can I explain that to my user base? I thought I would rather them see what is happening straight up and allow them to merge the dimensions as I mentioned.
You could be totally correct, maybe I am a bit misinformed, but I have done a fair bit of reading on this subject. I’ve found a few good posts, but when others state simply that they are not a good idea without a good example, than I look at synchronized queries in a negative light. It does seem that it is a topic that people struggle with as views whether they are acceptable or not are not consistent.
Since the dimensions are not all conformed across all fact tables, and it is a suggestion in most cases to separate these facts into separate universes as well the requirement was to create as few universes as possible, the design was to in essence create these universes as contexts that were not compatible and grouping them into master classes. If these contexts existed as separate universes the user base would have to go through the same process.
“join” queries are fine. Queries joined with the “synchronization” operation can provide interesting results. Posts that talk about synchronized queries are generally talking about that specific case. Perhaps you are using synchronized in a more general sense?
Synchronization is a form of a cartesian product. If that’s what you expect / require then you’re fine. What you normally want is a Join query, which uses common dimensions across all parts of the data provider.
I’m familiar with “Join” queries and that is what I would be striving for if I could, however due to unconformed dimensions I can’t say for sure that our power users would not end up getting “Synchronized” queries.
I need to account for both our deployment of reports and for ad-hoc when it comes to the development of this universe.
I am speaking to both cases of synchronization I guess, the specific case which produces “interesting results” as you noted is what I don’t want to run into, and a cartesian product does not sounds like what we are looking for.
I will have to delve into this a bit further as I’m still on the fence, but with Andreas and Chris speaking to the strength of syncrhonization I need to understand what it can do, and what lies beneath as well. Unfortunately, I cannot do any testing in our current environment as the datamart has not been deployed yet.
A join query occurs when all of the dimensions from the query are shared. A synchronized query occurs when the dimensions cannot be shared.
For example, let’s take a query with four dimensions and two measures, and the measures come from different fact tables. If all four dimensions are valid for both fact tables you will get a join query.
If one of those four dimensions is not valid for one of the two fact tables, then you get the synchronized query. The measure that fits 3 dimensions (instead of all four) will be “cartesianed” … the values will be correct for the 3 dimensions that do work, and you will see repeated values for the fourth dimension.
In the case where you have dimension values (flags or otherwise) coming from the fact tables then you can see this.
Something just occurred to me after reading a few articles on Syncrhonization that I found mentioned on BOB and then another article from the BO support site. I thought I stumbled on some good documents for my topic and then realized that the Synchronization that they were referring to was two separate dataproviders and then merging the common dimensions between these two dataproviders.
Now, I may be asking a dumb question here but is synchronization with two dataproviders, “linking” as I had always heard it called when there was more than one dataprovider, acting the exact same for unlinked dimensions as when there is “Synchronization” of two queries behind one dataprovider, for instance when you are using contexts?
VERY ASTUTE! Bringing the multiple SQL results back together across universe contexts in a single data provider, and linking of multiple data providers is EXACTLY the same thing! No doubt shares the same code, I suspect.
Just thought I would follow-up on this topic. After consulting with the client they have decided to allow for querying across contexts as the resulting reporting structure creates a block for each query that exists in the dataprovider if it is a “Synchronized” query.
“Synchronized” in this case refers to a result set of universe objects that crosses contexts in one dataprovider and includes dimensions that are not conformed.
(BOB member since 2003-05-28)
(BOB member since 2002-06-20)
(BOB member since 2002-06-20)
Posts that talk about synchronized queries are generally talking about that specific case. Perhaps you are using synchronized in a more general sense?
here but is synchronization with two dataproviders, “linking” as I had always heard it called when there was more than one dataprovider, acting the exact same for unlinked dimensions as when there is “Synchronization” of two queries behind one dataprovider, for instance when you are using contexts?
(BOB member since 2006-12-18)