Multiple SQL Statements for each context

building semantic layer
1

Greetings my B.O. friends.

I am trying to understand the meaning of the (multiple SQL statements for each context) option in the SQL tab of the parameter window.

What does it mean and when should it be used?

In my universe I have two contexts, should I use tick this check box or not?

Sorry if my questions sounds basic but I am trying to get a good understanding of this option.

Thanks for your help in advance.

dreameR.78 :uk: (BOB member since 2005-08-22)

2

The short answer is ‘Yes’.

It’s a pretty pointless option actually. If you have contexts in your Universe and uncheck this option, the SQL will not be split and you will just get ‘Incompatible Objects’ error messages.

anorak :uk: (BOB member since 2002-09-13)

3

Hi anorak,

Why do you reckon it’s pointless though?! Surely it would be better to uncheck it if you’re not using aggregate navigation as in my case. Is it not better for users to see the “Incompatible Object Error” message rather than end up generating meaning synchronised queries?

dreameR.78 :uk: (BOB member since 2005-08-22)

4

I agree with you in cases where the SQL is split and ‘Synchronised’, but if you uncheck the option it also prevents you running perfectly valid queries where the SQL is split and ‘Joined’ in the resulting cube. e.g when you have a common dimension and two measures from two different contexts.

anorak :uk: (BOB member since 2002-09-13)

5

Hi anorak,

Okay, I am confused now. So if I uncheck the box for the scenario you mention (e.g. shared dimension objects with measures from different contexts), what sort of query would it return?! Why would it even display the “Incompatible Object” error message if this doesn’t get set up in the aggregate navigation panel.

am I missing something here?

dreameR.78 :uk: (BOB member since 2005-08-22)

6

Be carefull with contexts when you move to XIR2 Weib there can be a few surprises.

I don’t know why that is the case but apparently Webi does not like cube synchronization. It can do them but apparently it tries everyting to avoid them.

We had a query with 2 dimensions and 2 Measures (one from each context)
In Full client it does generate 2 synchronized cubes and runs in 25 seconds.

The same objects in webi we stopped the query after 2 hours. I rebuilt the query in Webi and had a look a the SQL:
Select dimension1, dimension2, measures1 from …
UNION ALL
Select dimension1, dimension2, measures2 from …

All this was against a 13milion records table and growing fast.
The resolution was to build 2 quereies in webi and we get to the same perfromance as full client.

In other cases it did generate a big where in select …

ClaireB :de: (BOB member since 2002-08-09)

7

Just to make sure that I wasn’t talking c**p, I created a universe with just two fact tables and a common calendar table. I created two contexts, one for each fact table, each having a single measure. There is no aggreagte awareness in the universe.

I created a report with , , and . With the option checked, the SQL is split and ‘Joined’ as expected. With the option unchecked, the ‘Incompatible Objects’ error message is shown.

So, in effect I created contexts in my universe and then rendered them useless by unchecking the option.

Hence my comment that the option is kind of pointless…

anorak :uk: (BOB member since 2002-09-13)

8

You comments are absolutely valid just wanted to point out that in XI the behavior is different between webi and full client.

ClaireB :de: (BOB member since 2002-08-09)

9

But the point is you have the option. There are cases where the results from one context are, in fact, not compatible. As an example, the designer training class uses the “Motors” database, where there are transactions for both rentals and sales. In the class you leave the checkbox on, so that you get the behavior you describe. I can run a query that includes both rental objects and sales objects, get a split query (with a join operation) and therefore valid data.

But consider a case where - as a universe designer - you do not want users to be able to run that query. Then when you remove the checkbox, the user can no longer use objects from more than one context.

The error message could be better, I agree. But to say that it’s a pointless feature is a bit strong. It has a point, it is a decision that the designer needs to make, but there are cases where you will want to prevent queries that span more than one context.

Dave Rathbun :us: (BOB member since 2002-06-06)

10

I would like to thank you all for your contribution. I now have a clearer picture of this option.

Have a good evening all.

dreameR.78 :uk: (BOB member since 2005-08-22)