In Designer FAQ, on the question “What is synchronization?” I see the comment,
“If you’re getting synchronized queries, you’ve got messed up contexts, joins not in the proper contexts or objects in the query that aren’t from dimension tables joined to both facts. It is very rarely ever acceptable to have synchronized queries.”
Is this true?
I understand that each query I explicitly create in WebI (via Add Query) is a separate “data provider”. Is each select statement generated by WebI in a Synchronized SQL set considered a separate “data provider”, or is the whole set considered to be one data provider?
I found this (below) from Dave Rathbun, in an old post on Synchronization, and I think if I really understood Dave’s three sentences, my understanding of contexts and synchronization would greatly improve. I’m confused by the words “split” and “join”, and I also wonder how this relates to the two universe settings for multiple SQL statements. Can anyone make this crystal clear?
“Measures from different contexts will cause SQL to split. Dimensions from different contexts will cause a synchronization rather than a join. Dimensions that are shared between contexts will provide a join query.”
It depends on what is acceptable to your users. It is far more desirable to have joined queries than synchronized queries, but synchronized queries are not always a sign of a bad universe. There may be information at different levels of detail, or from different facts that don’t share all dimensions.
I consider each tab on the query panel a data provider. The multiple passes created within the query are all part of the same data provider.
Back to your earlier question about multiple SQL passes… if you have measures from different contexts and dimensions are shared across both (or all) contexts then you will see your SQL split into different passes but it’s still one joined data provider. If you use the same measures but your dimensions are not shared across the contexts then you get a synchronization.
If all of your measures and dimensions are from the same context, then I would expect to see a single SQL pass as long as the measures are using the same aggregate function and coming from the same table. The setting for “Multiple SQL statements for each measure” can still cause things to split if you combine different functions (sum and count) or use measures from different tables within the same context.
Thanks for your reply, Dave. Here are some follow-up questions.
By SQL “splits” or SQL “passes”, I assume you mean WebI’s automatically-created, multiple select statements. When WebI creates multiple select statements for a query, will I ALWAYS see “Synchronized” when I look at the query SQL?
When you say “Dimensions that are shared between contexts will provide a join query,” what do you mean by “join query”? If you mean that WebI will add a join to a select statement, how does WebI know the appropriate join, since by definition, when the dimensions are from separate contexts, there is no join specified?
I assume that join queries are more desirable because their objects can ALWAYS be dragged to the same report block.
Suppose you have dimensions D1, D2, D3, and D4. D1 and D2 and D3 participate in one context. D1, D2, and D4 in the other. There are some measures as well. If you use D1, D2, and measures from both contexts on the query panel, you will see a join query because D1 and D2 are shared. As soon as you add either D3 or D4 you will get a synchronized query because there is no way to join them together.
(BOB member since 2007-10-20)
(BOB member since 2002-06-20)