Hi all,
I have 5 fact tables and 5 dimensions tables in my schema. Initially i have created 1 conext for each fact tables. But when i select facts from 2 different fact tables there will be 2 sql statements generated, so i am thinking of aliasing each dimension table for each fact, i.e 5 aliases of one dimension table one for each fact. I hope i am clear.
Now i want to know the performance impact of creating so many aliases in my universe.
romanb4u 
(BOB member since 2005-11-02)
Use contexts in this scenario, that’s exactly what they are for.
Neither aliases or contexts will directly have an impact on performance. An alias is simply a virtual copy of a table. A context is just a list of joins that can be used to generate a query.
You started out with contexts, and that is the correct answer. You want to have a separate query for each fact.
Dave Rathbun (BOB member since 2002-06-06)
Ya i am considering aliases because, when i select measures from 2 different fact tables, i am unable to put them in one table, they are formig 2 different tables in report. Thats why i am going for aliases.
Let me know i am on right track or not/
romanb4u (BOB member since 2005-11-02)
Aliases will not fix that. You have something else going on.
If you have (for simplicity) four dimensions and two facts, that’s two contexts. You should be able to select any shared dimensions + any measures from the facts and generate a single table.
Look at the SQL being generated, it is a Join? a Synchronize?
Dave Rathbun (BOB member since 2002-06-06)
Yes, Your are corect, its giving synchronization.
What could be the problem? how to resolve it?
romanb4u (BOB member since 2005-11-02)
I Haven’t included the joins of one fact table in the context of other. I think thats the way it should be, right?
romanb4u (BOB member since 2005-11-02)
Synchronization generally means you have dimensions that are not shared. You are correct; the joins to the fact tables do not belong within the same context. Check your contexts, and make sure that every dimension table is in both contexts. Assuming that’s appropriate. 
Dave Rathbun (BOB member since 2002-06-06)
I have checked in the universe, i ahev removed the dim table that is not joined in one fact table. Now i am able to get all the facts and dimensions under one table but the sql is still in synchronization. Will this effect the result?
Basically i am report is generating one sql for each Fact table even thought the dimensions are shared. Will this in any way effect the result?
romanb4u (BOB member since 2005-11-02)
Dave,
When i uncheck the option of “Multiple SQL statements for each context”, the report is thowing “Incompatiable combination of objects”.
When i check it its creating 3 sql’s for each fact (chooses elements from 3 facts and 2 dimensions) - synchronization.
Is it correct? Willl the query give correct result in such scenario?
When this “Multiple SQL statements for each context” should be checked and unchecked?
When the “Multiple SQL Statements for each measure” should be checked and unchecked?
I would appreciate if you can you throw some light on these?
romanb4u (BOB member since 2005-11-02)
Aha, now we’re getting somewhere. If you don’t have equal dimensions in both queries, you will get a synchronization. There’s no way around that. If you don’t have common dimensions for both queries you end up with a “cartesian product” of sorts. The report tool is smart enough to know to put them into separate blocks.
Multiple SQL Statements for each context is required in your case because you are spanning more than one context. Without it, you cannot generate the sql.
Without looking at your universe (or at least a picture of it) there’s not much more to do. If you have common dimensions shared between fact tables you can generate a single sql with a “join” clause and the resulting data will be in a single block. If you do not have all of your dimensions in your query shared, you will get a synchronization.
Multiple SQL Statements for each Measure has nothing to do with your scenario.
Dave Rathbun (BOB member since 2002-06-06)
The scenario is as follows:
there is emp dimension, which is related to all fact tables shares details, options details and monthly activites. When i create a reprot with EMP id and measures in other fact tables, its creating one sql per each fact table and in the report its creating diff tables.
I have equal dimensions in both queries even then it is creating 2 sqls one for ech fact table.
When i drag all the fields together, then it comes in one table only.
i am confused
romanb4u (BOB member since 2005-11-02)
Sounds to me like you are missing a context somewhere.
Unfortunately there is to my knowledge no tool in Designer that checks for this particular problem.
But the standard way to find this is that in a report there are different SQL statements instead of the expected join.
Things to check for:
Keep in mind that once you start with context then every table in your universe has to be covered by it. That’s at least how I learned to use context.
KMB 
(BOB member since 2004-02-11)
Do you mean i should join even fact tables with each other?
Actually when i joined the fact tables with each other and selected them in context, only one sql is getting generated.
Is this the way to avoid Synch error?
Should all the fact tables be joined in the universe and inserted into context?
romanb4u (BOB member since 2005-11-02)
Sorry to say but there is no clear yes or no to that.
Let us use following example. Bit crude but it serves to show the principles.
Four tables: customer (CU) , order headers (OH), order details (OD) and products (PR).
OH and OD link on the order number dimension.
CU and OH link on the customer dimension.
PR and OD link on the product dimension.
OD is a fact table, the rest are dimension tables in this example. With no context used this easy example will yield easy results but let’s say I put in a context called “Order Product” and define it as the link between PR and OD.
After this if I select customer to see which customer has bought which product I will very likely end up with 2 SQL scripts for the same query.
This is due to CU not in any context. Besides that I will have not much fun with any big amount of data to get good timely results.
If I extend the context to include all connections I get a single query.
The main part is to generate a context that spans the selection of your query. Joins are only necessary as long as they allow BO to go from one end table to the other end table.
What can help is to generate more than a single join between fact tables and then cover them with an appropriate context that covers the intended result set.
The trouble with more than one context covering the same route is that this can result in the question of which context shall be used when the report is run.
KMB (BOB member since 2004-02-11)
Thanks Kesxex,
So the only way to resolve chasm trap is
BO report will create 2 SQL’s (2 cubes in data manager), if the report has objects from 2 different fact tables, even though the dimension is shared between those 2.
Let me know if this understanding is correct? :?
romanb4u (BOB member since 2005-11-02)