Can we Force context or set one as a default

system · October 22, 2012, 8:33am

Hi,

My report is asking for 4 contexts to choose,shall I force one context or set a default context so that users no need to select context.

bob2210 (BOB member since 2010-09-23)

MarkP · October 22, 2012, 8:40am

Be careful.

Consider why you are being asked for more than one context and what you want to use them for.

Think of a snowflaked customer dimension, joining through to your region dim.

Say you have two optional join paths (contexts) - one through orders and one through enquiries. How do you know what you want? The user just asks for a list of addresses in the south west region to send mailshots to. There’s no mention of whether you’ve made a sale to them or not. You therefore don’t know which context to choose. So ask whether they need to be existing customers or all customers (including potential).

Now think about your universe - can you apply similar logic to determine which context you should choose? Do you have any measures defined? If you do, it may suggest that your contexts have been set up incorrectly.

system · October 22, 2012, 10:15am

Hi Mark,

Many thanks for giving more information about context selection.!

Here scenario is that we need to generate four reports (Accounts File,Daily Balance,Monthly Balance and Transaction) and we have three context in universe Inv Daily,Inv Monthly and Inv Transaction for three reports respectively . We are getting the context selection option only in Account File report.Now we are using an object fund manager as filter in all four report which is coming from derived table and this derived table is included in all three contexts so when I am draging the object in Where then I am getting option for three contexts.
Note: Results of Account File are same after choosing any of the contexts.

bob2210 (BOB member since 2010-09-23)

system · October 22, 2012, 2:14pm

I proposed an Idea to bypass the context prompt when any choice produces the same SQL.

Joe

joepeters (BOB member since 2002-08-29)

MarkP · October 22, 2012, 2:50pm

So, to summarise - the two scenarios you get prompted for contexts are:

D1 -< D2 -< D3 -< F1/2 where you only pick dimension objects from the snowflake. This is the typical scenario that Joe’s SAP Idea covers and should be voted for.

D1 -< F1/2 >- D2 where you only pick dimension objects from D1 and D2 and the query doesn’t know whether to go through the joins from F1 or F2. In this case you must understand what the question is to pick the context - the SQL generated will be different and the results will vary depending upon the contents of F1 and F2.

system · October 22, 2012, 8:11pm

It’s already supposed to work that way. There is a parameter (or supposed to be, or at least at one time there was) called COMPARE_CONTEXT_WITH_JOINS which does exactly this. According to my notes:

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

system · October 23, 2012, 12:11am

Thanks All for replies!.

The SQL is different for three contexts however results are same and COMPARE_CONTEXTS_WITH_JOINS parameter is ‘YES’.

Here are the join paths that we are using in four contexts in which first one is giving correct result different from other three required for Account File report
and results of other three contexts are same with different SQL

Investr–>InvsAcc—>Fund—>DT_Fund_Desc

Investr–>InvsAcc–>DailyBalanceSnpsht->Fund–>DT_Fund_Desc

Investr—>InvsAcc–>MonthlyBalanceSnpsht—>Fund–>DT_Fund_Desc

Investr–>InvsAcc—>Trans–>Fund–>DT_Fund_desc

Here the object Fund Manager which we are using in filter is coming from DT_Fund_Desc.

I think it is similar to this scenario stated by Mark

So If this is similar to the second scenario explained by Mark then will it be possible to force my first context for specific report i.e Account File

bob2210 (BOB member since 2010-09-23)

MarkP · October 23, 2012, 8:21am

You can do it with a predefined condition.

Call the condition “Force Account File Context”

Enter a description explaining why the condition has been built

Enter your condition as 1=1

Click on the tables button and associate the required table with it.

Click on OK and you’re done

system · October 23, 2012, 3:59pm

Hi Mark,

I tried to create predefined filter as you have mentioned and also tried with all the tables in first context (single table at one time) but I am still getting an option to choose context .

As I understand that all the tables consist in first context are common in other three context also so even after putting predefined condition BO is not sure which path to pick up.

bob2210 (BOB member since 2010-09-23)

MarkP · October 24, 2012, 7:56am

So you have something like D1 -< D2 -< D3 -< F1/2/3/4 where you are picking from D1, 2 and 3 and you have the four contexts representing the joins to F1, 2, 3 and 4?

The idea of the condition I suggested is to assoicate it with F1 so it uses that join path.

system · October 24, 2012, 9:45am

Hi Mark,

I guess you mean below joins from above statement
D1-<D2-<D3-<F1
D1-<D2-<D3-<F2
D1-<D2-<D3-<F3
D1-<D2-<D3-<F4
if yes then my join structure mentioned below is different from above and we have no fact table as it is an ODS database i.e
context1: D1-D2-D3-D4
context2:D1-D2-D5-D3-D4
context3:D1-D2-D6-D3-D4
context4:D1-D2-D7-D3-D4

All the tables required for Account File are present in context1 which is the correct one. Please suggest !

bob2210 (BOB member since 2010-09-23)

system · October 24, 2012, 1:40pm

Hi,

If you want to default one of these contexts in a specific report, then there is a option in WEB I -

Refresh the report , selecting the context you wanted to be default.
Go to ‘EDIT QUERY’ --> Properties --> uncheck the box - ‘Refresh contexts on refresh’
This would ensure the context gets defaulted and wont ask each time user refreshes the specific report

You can do this for adhoc report as follows:
If you want to defualt the below context path ‘Investr–>InvsAcc—>Fund—>DT_Fund_Desc’; then pull the objects from the other tables in the path (though they are not required) into the result object plane, but never show them in the report block.

OR

You can create a duplicate object of DT_Fund_Desc, and in the object defintion --> select all tables (InvsAcc,Fund,DT_Fund_Desc). this will ensure that every time any user uses this object that adhoc report will use this context.

Hope this helps.

romanb4u (BOB member since 2005-11-02)

system · October 24, 2012, 7:43pm

Really awesome!! Thanks a ton!!

bob2210 (BOB member since 2010-09-23)

system · November 7, 2012, 1:40pm

Hi Dave,

Thanks, I didn’t know about that setting. As you said, it is on by default, however I was still seeing the context prompt in certain cases when I didn’t expect to. I discovered that the prompt will appear if at least one of the possible contexts produce different SQL, even if all of the other produce the same SQL. The report that I am working with right now will prompt me to choose from seven contexts – six of them produce the same SQL, and one is different.

This does make sense, in that BO still needs to know which context to use. But it can still be confusing to the user, since I’m being asked to choose from seven contexts, and I can’t tell which are the same (without looking at the SQL).

I’m thinking that a better solution would be to display the contexts prompts by group when this situation occurs – so in my sample report, I would see Context Group 1, with the fix six contexts, and Context Group 2, with the last context. I would only be able to choose Group 1 or Group 2. I would see the names of the individual contexts that comprise the group, but would only be able to select one group or another.

I think I’ll update my Idea.

Thanks, Dave!

Joe

joepeters (BOB member since 2002-08-29)