I have a table with 2 types of unrelated data: MW and price. For example, the table has a date, datatype, value. The datatype determines if the value is price or MW. I want to create 2 classes of objects: one for price, one for MW. I don’t want the user to have to use a filter to select one or the other.
I accomplished that by having the table in the universe twice (alias) and using self join in both. My question is, is this the best way ? Since the table has joins to other 3 tables, I had to duplicate them too (alias again). Is there any other way ? I would rather not use contexts.
By the way, I should add that there’s a possibility that I would have to add another class of objects for the same table for another type of data. That would make 1 table and 2 aliases for every table involved.
You are correct to stay away from Contexts.
Alias’s are the better solution, although I’m not clear why you would need self joins in this case.
Remember that even though it seems long and tedious to create seperate sets of tables, it’s really a one-shot and you’ll be saving everyone else work later in the process.
Thanks all for the suggestions. I decided to create 2 demo universes (one with a difference class for price and MW and one with the case), show the users and see what they prefer.
I have only one question for Chris. You said you’re not clear why I needed to use self join. I’m using it to restrict the results to only price or only MW. Could I have done it any other way ?
It’s hard to say without knowing your universe requirements. With a standard star schema, you should be able to apply the condition on each leg of the alias. You also could apply the conditions on the where clause of the individual objects. Or create filters as you’ve already recognized. Personally, I like self-joins, they ensure the clause gets added to the SQL even when the user selects an object only from the table in question (thus no joins needed between tables). For that reason, you should also consider whether you need self-joins on the other tables in each alias leg.
Thanks for your response. I only applied the self join to the “main” table. Not to the tables on the star legs.
The users chose the self-join version instead of the DECODE one. Personally, I also prefer that because:
It is faster. The table in question has about 18 million rows right now, so having the selection on the where clause instead of retrieving more rows than necessary, makes a difference.
Based on what we have in mind for this universe, it will make it more clear for the user.
And, although, is a little more work for me, as it was mentioned here before, I’m only going to this once, while the users will use it a lot of times.
Thanks again all for your help and until the next time
Contexts are a very important part of the product. Basically each fact in the Universe requires a context. Unfortunately BusinessObjects doesn’t do a very good job on communicating how to create universes. But there are best practices and they should be enforced.
Speaking in general, I must professionally disagree with you, Chris; this is like saying “stay away from star schemas: a relational data model is the better solution.” Or “don’t ever use wrenches: a screw driver is always the best solution.”
Aliases and contexts, like star schemas and relational models, are tools, and so have their advantages and disadvantages, and each has specific cases where it is by far the best solution. When created and used correctly, contexts are a hugely helpful tool - improving speed, efficiency, and ease of use - for both the users and the universe designer/maintainer. Even in Carmen’s case contexts could be a good solution, if Carmen just created a separate table for MW facts and one for Prices (assuming they are both fact tables).
Carmen’s case also illustrates some of the problems of trying to force a single solution to work for all types of problems:
There was one poster here who had to create 147(!!!) aliased tables (give or take a few dozen!) just because they didn’t want to use contexts. And two or three simple contexts would have given the same result.
A good general rule is: aliases only for multiple Dimension tables, contexts for multiple Fact tables.
But you don’t have to take just my word for it, Carmen. You should search in BOB on context/alias/loop/etc…, as there have been numerous lengthy and helpful discussions on this topic, such as
Dimensions, facts, star and relational
or
Context or Alias in this situation?
or
loops - Alias and Contexts
or
Context help required
or
Duplicate Rows Problem.
A good general rule is: aliases for multiple Dimension tables, contexts for multiple Fact tables.
I concur … I wasn’t trying to make a geralization here … each solution does indeed have pros and cons … Historically, i’ve found alias’s better from a user workflow perspective. Especially when contexts force an extra prompt when running queries. If i can avoid prompting my users by creating n number of extra alias/objects, it’s usually beneficial. In cjb’s case, it sounds like there’s only 3 extra alias’s needed.
Oh good! I hoped you were just speaking of this specific case, but I couldn’t let such a broad generalization stay unclarified.
And you are right, user happiness with our software/technology is extremely important, and needs to be valued much more highly than it too often is, and contexts can occasionally be annoying. Although I must say that in my experience, when created and used properly, named clearly, and explained to users, users don’t complain about them.
(BOB member since 2002-06-18)
(BOB member since 2003-08-25)
(BOB member since 2002-10-22)