Universe Contexts in a Nutshell

Way back in the second millennium, when I started doing universe development, one of the things that scared me most about the process, was Contexts. I did anything, and everything, to avoid using contexts in a universe. My first production universe was developed without contexts, even though contexts were really needed, and would have made the universe much more user friendly. Instead, I created aliases of nearly every dimension table, so as to avoid any loops. Here’s a frightening picture of that universe.

Of course, I look back at that universe and think, “If only I knew then what I know now.” You see, once I truly understood universe contexts, and their proper use, I found them easy to implement, and easy to maintain. So, let’s start with the basics.

When do I use Contexts in a Universe?

It’s really quite simple. If your universe has multiple fact (transaction) tables, you will need one context for each fact. And just to be clear, for this purpose, I am defining a fact table as the lowest level of transactional data. There may be multiple tables that belong to one fact, such as Invoice Header and Invoice Detail. In this case, the Invoice Detail table would be considered the lowest level of transactional data.

So, if your universe has only one fact table, you don’t need any contexts. But if you have multiple facts, you will need contexts.

OK, So what is a Context?

A context is a list of joins that define a logical path through a universe. Contexts are used to resolve loops that are caused by multiple fact tables accessing common dimension tables. The joins that belong to a context are the joins from the fact table, leading to any and all dimension tables that might be needed for that fact. In the picture below, the SALE_MODEL table is the fact. The joins highlighted in blue, are all members of the Sales Context.

As you can see, the highlighted joins include not only the joins that touch the SALE_MODEL table, but also joins that extend to distant dimension tables, such as REGION, that contain data related to the fact table.

IMPORTANT: Once you add contexts to a universe, all joins must be a member of at least one context (Shortcut joins are an exception to this rule). Joins that are not members of a context are called isolated joins. Isolated joins will not be recognized by the reporting tools. (Note: This rule will go away in BI 4.0.)

As you can imagine, joins that do not connect directly to a fact table, can be in multiple contexts. For example, in the image above, the join between the CLIENT and REGION tables would be used for both Sales and Rentals, so it would be a member of both contexts.

When properly implemented, contexts will prevent Fan Traps and Chasm Traps from returning incorrect results. If a user runs a query which includes objects from two different contexts, Web Intelligence will generate two separate SQL statement, and join the results on the common dimensions after results have been retrieved.

If you are using universes with Crystal Reports, you will need to change the value of the JOIN_BY_SQL parameter to Yes. This is because Crystal Reports doesn’t support the generation of separate SQL statements like Web Intelligence. So the JOIN_BY_SQL parameter will cause Crystal Reports to generate multiple select statements in a single SQL sentence with a FULL OUTER JOIN between them.

How do I create a Context?

There are several methods for creating contexts. They can be created manually, or automatically. The most accurate method is to create them manually. If you choose to create them automatically, you will still need to manually check to make sure that the context is correct.

To create the context manually, Ctrl-Click on the joins connected to the fact table, as well as all joins to dimension tables that contain data related to that fact. Once you have them all selected, click on the Insert Context button.

This opens the New Context box.

Enter a user friendly name for the context, and a user friendly description. More on this later. Click OK, and you have a new context created.

If you wish to create your contexts automatically, make sure that all joins have the correct cardinalities set. The detection tool uses the cardinalities to determine which joins to include in a context. There are several ways to create a context automatically, but they all work basically the same way, so we’ll just talk about one way.

Once you have verified that your cardinalities are set correctly on all joins, click the Detect Context button.

First, this button will look for fact tables. It recognizes a fact table as a table that is at the many end of all of its joins. Next, it checks to see if there is an existing context by the same name as that fact table. If not, it formulates the context, based on the cardinality of the joins in the universe, and recommends the context. You can then choose to accept, or reject, the proposed context. If you accept it, you can rename it with a more user friendly name. Once you have added the context, you can edit the context by double clicking on it in List View.

Editing an Existing Context

There are several things that may need to be edited in  a context. These include the name, description, and which joins are included. To edit a context, open List View, and double click on the context in the right pane. In the Edit Context box, you can change the name, add, or edit, the description, or add or remove joins. All joins will be shown in this box, but only the ones highlighted in blue are members of the context. To add a join, find it in the list, and click on it. To remove a join, find the highlighted join in the list, and click on it. (NOTE: You don’t have to hold down the Ctrl key when clicking on the joins in this box.)

What about the User Experience?

There has been an eternal debate regarding the user experience with contexts. When you add contexts to a universe, users may occasionally be prompted to select a context when running a query. There are those who believe that this should never happen. I disagree. I believe that it should be very rare that a user is prompted to select a context. But I wouldn’t say it should never happen. A context prompt, in my mind, is no different than any other prompt. It’s a way to allow the user to select query options at run time.

Having said that, I find that many universes prompt for contexts unnecessarily. We want to avoid this. Most of the time, a user is prompted for a context for one of two reasons:

1. The query includes no objects from a fact table. In this case, the prompt may be legitimate.

2. The universe isn’t built properly.

If users are being legitimately prompted for a context, that’s fine. Make sure that the contexts have user friendly names (Proper case and no underscores), and a user friendly description, that helps the user make the right choice. Note that, in Web Intelligence, if the user is prompted for a context, the prompt will appear each time the query is refreshed. This is the default behavior, but can be changed in the query properties.

If the users are being prompted for a context, but shouldn’t be, then you may need to take steps to minimize this. For example, if an object should always use one context, you can force it through that context by adding the fact table from that context into the Tables button of that object. This will prevent that object from prompting for a context. This is an excellent method for keeping those prompts to a minimum.

In Conclusion

When properly implemented, Contexts make universes much simpler for the users, as well as the developers. If you take the time to understand the correct use of Contexts, you will find them to be quite simple to implement and maintain. And if you don’t get it right the first time, fear not. Contexts are easy to edit.

Advertisements

46 Responses to Universe Contexts in a Nutshell

  1. Rajender says:

    Hi Michael,
    I am little lost in universe .

    I am in little confusion well webi 3.1 sp1 works on level based hierarchy.and what if i want to extract 10 amounts like (direct expense ,sales expense etc) from one amount field based on different conditions(different account number). Webi doesn’t have dimensional hierarchy selection. I have a solution in universe designer using derived tables. But i want to know is there any alternative to derived tables what we can do in WEBI…

    Thanks

    • Rajender, I don’t understand your question. How does this relate to universe contexts? Can I suggest that you clarify the question, and post it on BOB?

      • rajender says:

        I was asking about universe not context. should i asked questions related to context?,

  2. ycx80 says:

    Hi Michael, you mention that Shortcut joins are the exception to the rule. However I have several shortcut joins that are not in contexts and I still get the “isloated join” error.

    I’m on XIR3.2 and not sure if I should just ignore it?

    In the query panel, the sql generates correctly.

    Many thanks!

    • You guessed. While technically, these joins are isolated, they still work just fine. Any other isolated join won’t work at all, while the shortcut joins will. So, you can ignore the message.

  3. Subah says:

    Hello Michael,
    I came across this article when I was looking for ways to use Universe contexts in Crystal Reports. I tried changing the Join By SQL parameter, however it does not seem to be working. Am I missing something? What options should I keep checked in the SQL tab for 1) Use Multiple queries for Contexts , and (2) allow multiple contexts to be used?
    Any help will be greatly appreciated.
    Thanks!
    Subah

  4. Subah says:

    Hello Michael,
    Thanks for responding. I was just going to leave a response on my findings. I did look at BOB and noticed one of Dave’s comments where he observed that this will only work for queries that have a ‘Join’ and not when the queries were ‘Synchronized’. I know that the latter is incorrect design but failed to observe that when building the query. When I corrected the issue, the above solution worked. Thanks again!!

  5. bose says:

    Michael

    Awesome article,many thanks for that but bit confused about below clause,could you plz clarify
    “if an object should always use one context, you can force it through that context by adding the fact table from that context into the Tables button of that object”

    Thx
    babu

    • babu, when you open an object, you will see a button labeled “Tables”. If you click on that, you see any table(s) that are associated to that object. You can Ctrl-Click on additional tables, forcing those tables, and joins, into the SQL of that object. This can force the object to default to a specific context, thereby avoiding the context prompt when that object is used.

  6. Nikunj says:

    Michael,
    Is it possible for you to give any example, where user will be promoted to choose one. Like you said above that getting promoted to select a context may be legitimate if no facts are being used. I am just not able to figure out any real life scenario for this. Suppose if user selects objects from 2 dimension tables, which are part of 2 different context. Then it doesn’t matter which context user selects, one will always get the same results.

  7. Deep says:

    Michael,

    I have a context issue. Here is what I have done and getting issue:

    1. Converted the reports from webi3.1 to WEBI4.0 using upgrade management tool.
    2. Converted the Universe using upgrade management tool to .unv copy in BO4.0
    3. Converted .unv Universe to .unx Universe using IDT in BO4.0
    4. Repointed the WEBI report which has multiple contexts (Each for a set of 3 queries) to the .unx copy of the Universe using the data-> change source option.
    5. The report uses 3 contexts — context A , context B , context C. When I try to run the query after repointing the query to .unx Universe, it prompts me for selecting the context. It shows contextA and does not give me a “Next” option to select context B and then context C. It keeps coming back with the option containing context A (And its alternate one say A1). Every query has alternate contexts (A,A1/B,B1/C,C1). But as the report was last run it was run with contexts A,B and C.

    6. As opposed to this , when I open the report in BO4.0-WEBI using the .unv copy — in the query properties, I can see there are 3 contexts saved along with the report. If I clear(remove) them and run the report again it prompts me to select context A next context B next context C.

    When I mention 3 queries I mean 3 synchronized queries ; each refering to a context. They are not 3 WEBI queries created in the query panel.

    I had tried to use options “Allow multiple context selection” and related options in the Universe and make a .unx copy. But not sure what combination would help me resolve this.

    Appreciate any light on this one.

    Regards,

    Deep

  8. Regalla Manasa Reddy says:

    Hi Michael, Can we include a shortcut join step in a context. In my universe, as per the requirement, we have used shortcut join between 2 DIM Tables.now, I am getting the “isloated join” error. if I include the shorcut join in a context, the error is gone. Will it if any affect if we include “shortcut joins” in contexts. Please suggest me.Thanks a lot for your help inadvance.

    • Regalla,

      Yes, you can include shortcut joins in a context, but you don’t need to. When you get the Isolated Join error in the integrity check, you can ignore that error. It won’t affect the way the universe generates SQL. So, I would suggest leaving it out of the contexts, and ignoring the error.

  9. Judia says:

    Hi Michael,

    Thanks for this good article. I have included a derived table in universe and i have given a join to a fact table (one to one join was given). I have nearly 8 contexts in the universe. To which context do i have to include the derived table join. If i dont include it to any context, i get incompatible multiple query in edit query (webi report).
    Thanks in advance.

    Judia

  10. Judia says:

    Hi Michael.
    Thanks for the reply. It worked for me.

    Judia

  11. Judia says:

    Hi Michael,

    I apologize for asking this question here.i didnt know where to post.

    I wanted to ask a question regarding backup of BO 4.0 . I am sorry i didnt know where to post.
    How can i take a hot back up of B0 4.0. I referred BO admin guide in which it is said to enable hot backup in CMC and in CCM backup server settings.i dont have the cluster key with me. What if i reset the cluster key as given in BO Admin guide, will that show any impact on present BO environment.

    After enabling hot backup in CMC and CCM,does it stored somewhere in the form of BIAR files in a specific location? Also i will be using Promotional management tool to create LCMBIAR files for all BI content (universe,reports,users,groups).Is this enough for a hot backup?
    Please guide me with your valuable ideas.

    Thanks
    Judia

  12. Judia says:

    Hi Michael,

    I have a question for you. Can we combine two existing contexts into a single context in universe?
    What are the steps involved in achieving this?
    Kindly share your ideas.

    Thank you.
    Judia.

  13. Isskumar Ilp says:

    Hi,

    Thanks for your info.

    I have come across one complex model. Confused with how many alias/contexts to be created.

    Not sure how to attach the data model. Please let me know how to add the data model.

    • Frank says:

      While multiple context display in the Webi ‘Edit Query’ Properties tab. A single context does not display. I believe there is a cartesian product in one of the reports and the tables used are joined to many context. How to see just which context is being used, there are no prompts being displayed during the reporting or in the properties tab.

      • Hi, Frank. In Webi, there’s no way to see the name of the context in use when only a single context is available to that query. The only way is to look at the SQL for the query, and compare the joins in the query with your universe. Also, make sure that every join, except shortcut joins, are members of at least one context. If they aren’t, you will get a Cartesian Product.

  14. Frank says:

    Will do, thanks for the suggestion.

  15. Dawei says:

    Hi Michael,

    Thanks for your detailed explanation!

    I have a question here, in your example context screenshot, if I want to retrieve values from table model_colors, how to set the context to avoid the loop? (if you do not use aliase)

    Best Regards,
    Dawei

  16. Indrani says:

    Hi Michael,

    Is it necessary to create a context in universe if we do not have any loops in universe.
    Cant we create a universe without any context.

    • Contexts aren’t just for resolving loops. If you have multiple facts in a universe, you will need a context for each, to prevent chasm traps.

      • Ram pratti says:

        Hi Michael,

        It was really great post,

        Regarding prompted for context , I had 8 fact tables in my universe. I have created separate context for each fact table. I have included the joins in those contexts and explicitly excluded the joins which were not required in that context. But some reason it is involving all common dimension objects tables and fact tables when i was trying to create query on one fact table object and one dimension table object. At the same Can you please elaborate below mentioned your statement from your post. I was not able to find below mentioned option.

        “if an object should always use one context, you can force it through that context by adding the fact table from that context into the Tables button of that object”

        Thanks,
        Ram.

      • Hi, Ram. It sounds like you’re using IDT. My experience is that contexts are more complex in IDT than in UDT (although that wasn’t SAP’s intention). What version are you using? I have seen what you’re seeing in 4.0, and even some versions of 4.1. I believe it’s a bug. However, yearrs ago, when I first saw it in 4.0, we opened a ticket with SAP over this issue, and they told us that it was “by design”. That, of course, made no sense.
        We eventually abandoned it, and rebuilt the universe in UDT, and that worked fine. If you have a universe with more than 2 contexts, I recommend building it in UDT, instead of IDT, until SAP gets this issue fully resolved. However, in the meantime, please open a ticket with SAP.

  17. Manish Lavekar says:

    Hi Michael,

    Great Post! Thank you!!

    I have a doubt. Working on IDT 4.1 SP7. Created 2 contexts for 2 paths in a loop and checked “Multiple SQL Statement for each context” and option ‘JOIN_BY_SQL’ as Yes. When I use objects from both the contexts in a query it should generate 2 separate report queries, but it is generating a single query. Please let me know what might be the issue here.

    Thanks,
    Manish

    • The JOIN_BY_SQL parameter combines the multiple SQL queries into a single SQL sentence. This pushes the data blending onto the database, rather than having it happen in the report. It can be more efficient for small to medium queries. However, for larger queries, it can cause database resources to overload.

      • Manish Lavekar says:

        Sure. Thank you! This is a small query. To explain the scenario better, there is a single table which contains questionnaire and other two tables which have answers are connected to this single table. I want to fetch answers from both these tables for a single question from the questionnaire table and hence created 2 context paths. Even when I disable JOIN_BY_SQL parameter, it still generates a single query at WebI level.

      • If your questionnaire table is the only fact, you don’t need contexts.

      • Manish Lavekar says:

        Questionnaire table is a dimension table and other 2 tables which have answer values are fact tables.
        Example of the schema (Answers_1 table >—- Questionnaire table —-< Answers_2 table)
        -Each Question has many answers_1
        -Each Question has many answers_2

      • Make sure that both joins are in different contexts.

  18. Manish Lavekar says:

    Yup, these joins are in different defined context in .dfx and when I use the columns from each of these context in WebI, it is not generating separate sql.

  19. neetu2789 says:

    Hi,

    I have converted one universe from unv to unx, The query generated in unv and unx is completely different. In unx the generated query contains almost half of the tables present in data foundation. Please suggest
    We are on BO 4.1 SP8

    Navneet

    • This was caused by a bug, which I believe has been fixed in later versions, but I don’t recall which version fixed it. Check the tech support site. You should be able to find information there about it.

  20. Manish Lavekar says:

    Hi Michael,
    Do you have a blog page where you jotted down the validation plan/checklist for developers post upgrade from BO XI 3.1 to BO 4.1?
    OR Can you tell me what major aspects are validated by developers post upgrade from 3.1 to 4.1?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: