Mar 10 2008
What Is a Context Anyway?
A few days ago I finally got started on what will become a series of posts about fan and chasm traps. The post was “Do I Have a Fan Trap?” and it included a definition of a fan trap in a universe and showed a basic solution. However, it also mentioned that the basic solution would not work in most cases, and that contexts would be required for a more complete solution. If you are new to the Universe Designer, or even if you just have not worked with contexts that much, the first step is understanding exactly what a context does for you. That is what I hope to do with this post.
Just the Facts
One of the comments that you will see quite frequently on BOB is that you need one context for each fact table. That assumes that you have fact tables. š It turns out that the statement is, in fact (heh, pun intended š ), true, but why is that? What is it about a fact table that requires a context?
Universe Structure
A universe is a combination of two things: the structure (map) of the tables and the required joins, and the classes and objects that are presented to the user as the interface to the data. For the most part contexts are associated with the first part (structure) although they do have a user interface component as well. In the last post on this subject I included this quote:
As a designer you cannot control which objects a user selects to use on a query. You are not building a specific query, you are building a framework for any query.
What this means is that as a designer you include all of the potential joins in your universe structure without knowing in advance which query path a user is going to require for any given report. In most cases that isn’t a problem. But in some cases it might be, and that’s where contexts become valuable.
Context Definition
A context is nothing more than a set of joins. That’s it. If you were expecting something more complicated, I’m afraid that you’re going to be disappointed. š The complicated part is how that set of joins is identified and then utilized, and I will talk more about that a bit later. For now, let me talk about getting home from work.
There is a train station at your office building. There is another train station at your house. Think about tables in your universe as stops you might make on the train. The joins are the routes that the train system offers. Each trip that you take has a specific starting point (a table), an ending point (another table), and a route in between (zero or more tables). Normally you take the route home that includes a stop at your neighborhood store for milk and other groceries. But occasionally you don’t need milk and instead you stop at the news stand to pick up a magazine or something else to read that evening. That stop requires you to take a different route home. Something like this:
Both trips end up with you getting home, but each trip provides different information. You either come home with a gallon of milk or a magazine. š
Making Two Trips
So what do you do if you need both a magazine and some milk to go with it? In that case you have to make two stops. But the only way you can do it is to take the first route, and then return back to work and take the second route to pick up the magazine. Or you could clone yourself and take two trips at the same time. That is essentially what happens if the “Multiple SQL Statements for Each Context” option is enabled in your universe; you have the option to make more than one trip. If that option is not enabled, then you simply are not allowed to pick up both milk and a magazine.
What if you are at work, and you are ready to take the train home, and you don’t need either of those? The choice before you at that point is: Which route do you want to take to get home? Since you have not made a decision that eliminates one route (context) or the other you have to decide before you start your trip which way to go. (Maybe you’ll take a shortcut, but that’s a different technique. š ) This type of query is called an “ambiguous query” because there is nothing about your trip that predetermines the route to take based on the end points (work and home). In this case the user (either the person riding the train or the user writing the query) will have to make the selection manually. In the case of a query writer the decision is done by answering a prompt with a list of available contexts (routes).
In theory, the route doesn’t matter, because you just want to get home and aren’t stopping to pick anything up on the way. Either route will work, but there is nothing about your trip plan that identifies or requires a specific route. There is an option for “Allow selection of multiple contexts” that is off by default. This really a strange option, as it allows you to make two (or more) trips on an ambiguous query.
Using the same scenario as earlier it would be like taking two trips home (both routes) without making any stops along the way just because you can’t make up your mind which way to go. Doesn’t make much sense when riding a train, and it doesn’t make much sense for a database query either. If you don’t need anything along the way, don’t make two trips. You just end up wasting time.
How Do I Define My Routes?
If a context is nothing more than a set of joins, why is it such a big deal? The first reason is that it allows you to make one larger universe rather than two smaller ones. I can make a “Milk and Magazine” universe instead of having two separate universes. Without contexts I would have to take both routes everytime I wanted to get home from work. Not only is that a waste of time, it would quite probably end up generating the wrong results. The train analogy is starting to fall apart at this point, I think. š
But how do I know which routes (joins) to include in a trip (context)? There are certain signals that you can read (think of them as traffic lights) that will tell you which routes can be used together. These signals are given by the cardinality of the joins, which essentially provides information about how far you can travel on the route before changing trains. Erm. Contexts. š I intend for that to be my next post in this series, as this post was intended to define what a context is and provide some examples of the concept.
Summary
Here are the most important points for this post, as I see them:
- A context is nothing more than a set of joins
- The setting Multiple SQL Statements for each Context allows you to split a single query into two or more passes based on the contexts in your universe.
- The setting “Allow selection of multiple contexts” is off by default and probably should stay off. It doesn’t really seem to help anything.
The next post will talk about how to identify the set of joins that will be used to make up a context in your universe, and it will also explain why every join has to be in at least one context.
Great post, I like the train example, it makes it a bit easier to understand! š
– Josh
Wait until I start talking about climbing mountains, that’s in a future post. š
Great post. Quick question which I am hoping you can help me with…”>
I have a client who is using aliasing way more than I believe they should plus, are replicating over and over the same objects.
I feel like contexts can help BUT one caveat.
The reason for why they chose alias’ over contexts, is that they are joining 4 tables, 1 client, 1 shipment status, 1 event and 1 orders table. The event table (Event Id and name only) are denormalized and represent a slowly changing dimension, in that each client can load their events relative to their individual business. Next, the status table is a derived table, based on the subtraction of two date objects within the 3rd table, orders. It needs to be joined to the Event table to get final status. Orders is joined to Clients in a straightforward join.
Can a context be used to simplify and erradicate aliasing (where appropriate) on that event and status table joins, considering the status table is derived off a join to TWO columns from Orders table and 1 column from Event, considering that events and client are denormalized (for reporting purposes)?
I know this is a confusing question, but after watching all of the manual headache of naming alias’ for each Event, Client and Status, I thought it warrant my exploration into the topic further. Thanks a ton. Sassydatachic (Laura)
Simply put, aliases are not a substitute for contexts and vice versa. The question is simple: do you have multiple paths between tables, or tables with multiple purposes? If you have something like event date that is tied to multiple different types of events, then aliases are indeed the correct technique to use.
Here is another example to help clarify things. In the Island Resorts universe there are two uses for the Country table. One is the location of the resort, and the other is the location of the customer. If you used a context to resolve this issue, you could only pick one of the two values at a time. By using aliases (the correct solution) you can answer the question, “Who visited a resort in their home country?” by applying the proper condition.
In general there is only one correct technique to apply. I intend to provide more details in a future post. Thanks, I hoped this helped some.
Dave, thanks for posting this useful information. I clicked over here from BOB and have been visiting frequently ever since!
Don
Hi, Don, I am glad you are finding some value in my ramblings. Thank you for your comment. š
That was gr8 explanation. liked the anology used for context
I am a great fan of you,sir.I get inspiration from ur replies in BOB.I am budding BO developer.My kudos to BOB forum,as it is my guru.I learnt several things and will be learning.
A small request….Can you post an article on “Aggregate Awareness”?
Hi, varuna, and I am quite happy to hear that you find BOB useful. š
I do have plans to post about the Aggregate Aware feature of Business Objects, but for now you can find a number of presentations already available on this subject at the Integra Solutions web library. Here is a link that will get you to the design area of the presentations page. I hope you find it useful to get started.
Dave, it is too nice to see you describing things with just a simple scenario. For me, it became a hobby to visit BOB and Dagira to understand concepts better.
Thanks Dave,
Your post really helped understand the Context Concept. Great post I must say !!
Thanks you so much for the great posts!
These are just great.. Helped me a lot…
I simply chanced upon this website and literally speaking you turned out to be my Angel š
All the while I was looking for some answers on BO XI R2/R3, I happened to found all of them here.
Thanks a gazillion for sharing such valuable information with us
I am new to Business Objects and learning to work with universes. This is a great post. Great examples. Thank you!!!
how to detect traps in universe?
and how to resolve them?
Hey man, Love the post. The trip home analogy is perfect. I have a question about contexts and their limitations, and i think i can keep your analogy going. What if i needed to go home, grabbing both milk and a magazine along the way. But when i get home, i’d like to know the total amount of $$ i spent on the items. Currently, i cant find a way to create an object that is the sum (or division, subtraction, etc) of 2 other objects from separate contexts. Any suggestions?
Hi, AP, there’s a simple answer. You can’t tell how much you spent until you get home, meaning you have to do this calculation on the report itself rather than in the universe. š There’s no way to combine calculations across contexts because the specific point of contexts is to split things up. Once you have all of the data (from both trips) then and only then can you create the calculations because everything is in one place.
=( not the response but i wanted, but kind of what i expected. Thanks for the fast turn-around!
Dave, this is the first time i am into this site and i am new to BO, the way you explain is amazing, thanks a ton.
Dave, you rock the way you explain things.
Hi Dave,
Beg your pardon if it’s a silly question. If I have 3 contexts in my universe, and if I’m selecting measure objects from tables A, B and C and if A, B and C are in separate contexts, then is it safe to say that ‘BusinessObjects will generate one SQL query per context’? In general, can you please tell me if the statement ‘One SQL per used context’ is correct?
Thanks,
Chandru
great analogy .. the train example works …
excellent edifying info…thanks a lot …
vreddy.chr@gmail.com
This is a simple post to a complicated issue like context for a newcomer like who has very little context of BO š
Right from the get go you say you ‘need’ contexts if you have fact tables. Not only is that not true, I have never ever found a universe situation where I needed a context. It seems to me most people use them to avoid loops. You can use shortcut joins or aliased tables to avoid loops. In my experience you should avoid contexts at all costs. They provide confusion to users. If creating a universe with more tables, or even creating more universes makes it easier to the user, I’m all for that. Once you start using contexts you pretty much get locked into them, and that seems more confusing to me than aliased tables or even another universe.
Hi, Dean, welcome. I believe that there are a couple of issues with your comment. First, strictly speaking, having more than one fact does not immediately require contexts. If the facts are isolated (not joined to each other) then no contexts would be needed. I assumed that there would be shared dimensions connected to those facts as in a multi-star schema, and therefore there would be loops to resolve. In that case you most definitely need to use contexts. Aliases don’t fix the problem, they just propagate it. You can see one extreme example of that in a post by Michael Welter about universe contexts. His screen shot shows what happens if you try to use aliases to resolve loops that should be resolved by contexts. Having aliases also means lots and lots of duplicated objects which results in less flexibility to the users. In a properly built multi-star schema there is only one copy of each dimension, and I can switch from one query definition to another at any time without having to remove anything. It certainly makes it easier to build reports.
Second, shortcut joins should never be used to fix a loop, that’s not what they are for. It is entirely possible to break up a loop with a shortcut, but you run the risk of improper SQL generation from that point. A universe should work 100% correctly before shortcuts are added, else they’re not being used as intended. I have a lengthy review of shortcut joins here on my own blog.
Bottom line is, if your process works for you and you are comfortable with it, then you might be okay. But I would be doing a disservice to my other readers if I let your comment stand without a response. I hope you understand. š
Dave…Love your posts…I am building my first star schema BO Universe. The universe has 5 fact tables with shared dimension tables. So, I have a context for each fact table. My question is when I create a report containing a dimension object that happens to be shared by two or more facts, the Webi report asks what context path I want to use. This is not desirable for my users to have to select. How do I get around this? Many Thanks.
Don, if you use dimensions from only one source (one dimension table) you should not be prompted for a context. If you are, then generally there is something wrong with the way the contexts have been defined. If you’re using dimensions from two or more different dimension tables without specifying any measures, then the context prompt is expected because the query tool doesn’t know which path to take. Is that what is happening in your case?
Dave…Yes, I am in the academic world and creating a universe for our university’s admissions department. I have 4 fact tables that contain a “Acad_Prog_SID” FK that all link to 1 dimension table “Acad_Prog_Tbl”. In addition, all 4 fact tables link to the dimension table “PERSON” that contains a students ID, NAME, Address, etc. I created a context for each fact. When I create a report that just contains Student ID, Name, and Acad Program Description (all dimensions) and all shared by the facts, I am prompted to choose a context. If I include any measure specific to one fact then it does not ask for a context. Is this normal behavior? If so, I need to find a way to prevent my users from having to choose.
Dave…Yes, I am using two or more different dimension tables without specifying any measures. Is there a prefered way to automate the context selection for users who will not understand what to select? I just read a article that suggests creating a filter object in each fact class and require a user to include it in their report to resolve context questions. Does this sound reasonable to you?
Thank you for an excellent explanation of contexts! I think I understand, but wanted to see if this makes sense.
I have an “Order Item” table in my universe containing records for product orders. Each record has a product#, quantity, and price.
The “Product” table contains detailed information about each product in our catalog.
The tables can be JOINed to the “Order” table via the product# field.
Data integrity rules in the database ensure that every product number in the “Order Item” table exists in the “Product” table, but of course, there may be some products in the “Product” table that have never been ordered.
My users would like to be able to do two types of reports.
1) Order reports, that list all items that have been ordered, including both quantity/price information from the “Order” table, and detailed information about the associated product from the “Product” table. This requires a simple INNER JOIN.
2) Catalog reports, that list all of the products in our catalog, and the quantity sold. The tricky bit is that want to include all products in this report, including products that have never been ordered. I believe that requires an OUTER JOIN.
Would I accomplish this by providing a “Catalog Report” context (that uses an INNER JOIN) and an “Order Report” context (that uses an OUTER JOIN)? Or is there a better way to do this?
Thank you!
Darren, unfortunately contexts cannot be used to generate two different paths between the same tables. That would mean that you would have to alias your orders table in order to create a truly separate path. However, that’s not how I would solve this question. I would leave it as an inner join, and create what I often call a “dummy object” in your universe that returns the value of zero. Make sure this object is numeric. To answer your second question (the catalog report) you would create a union query that uses the product information with the quantity sold and UNION with the same product information and the zero dummy object. What this will do is ensure that every single product comes back with a quantity of zero, and those will be added to the products that do have order quantity values. By the time you’re through grouping and sorting you should have what you need.
hi Dave….
nice explaning …easy to understand…thanks
I have this large universe named Accounting Setup.
I cam across an issue that there were incompatible objects. On further investingation, I found that there is a loop which I was able to drill down to following to three joins creating a loop.
APP_ORG_UNIT.ORG_ID = FGL_ACCOUNT.ORG_ID AND APP_ORG_UNIT.ORG_UNIT_ID = FGL_ACCOUNT.ORG_UNIT_ID
APP_ORG_UNIT.ORG_ID = PRODUCT.ORG_ID AND APP_ORG_UNIT.ORG_UNIT_ID = PRODUCT.ORG_UNIT_ID
PRODUCT.ORG_ID = FGL_ACCOUNT.ORG_ID AND PRODUCT.ORG_UNIT_ID = FGL_ACCOUNT.ORG_UNIT_ID
Now what will be the best way to correct the situation. To add a context, or to create an alias for one of the three tables.
Please help.
VIPUL
It depends on how the various tables are used in the universe. I can’t give you a solution based on what you have provided.
Hi Dave,
Great post on contexts. Looking forward to the post that you have mentioned in the last line of this post. Can you please clarify a related question?
Will a loop always result in incorrect results? Iām asking this because in one of the universe we are using the universe designer is asking us to use a filter object (which has a couple of joins defined) and this forms a loop in the SQL.
A1 >- B
B — A
A1 is an alias of A and F is a fact table.
Adding the ‘enforced’ filter is introducing these joins in addition to the one listed above thereby resulting in a couple of loops.
A1 ā F
A1 ā A
The short answer is no, a loop will not always cause incorrect results. I have built universes that purposefully included loops in fact because they were required in order to generate the correct results. In your scenario, however, I suspect that they might. Can you share a little more about the purpose of table A and the alias A1?
For example, in the Motors universe, if I assume A is country and is linked to Customer, and A1 is an alias of country that is linked to Showroom, then if I include all of the joins I will only see customers who purchased cars from a showroom in their own country, and my sales reports will be way under the expected amounts due to the extra joins.
Thanks for your response Dave. For some reason even teh second post did not come through correctly.
Table A is an address table, which has address dimension id and office id. (address dimension id is the primary key. There can be multiple office IDs)
Table B is application table, which has office id and application received time.
Table F is the fact table, which has address dimension id and application received time.
Table A1 is an alias of Table A
The joins are like this
Table A1 is joined to Table B on office id (many to one)
Table B is joined to Table F on application received time (one to many, though it is actually many to many)
Table F is joined to Table A on address dimension id
The filter object which we are forced to use enforces these two joins in addition to the above:
Table A1 is joined to Table F on address dimension id
Table A1 is joined to Table A on office id
The report we are trying to build should have:
office name (from Table A)
facts (from Table F)
application received month/ year (from Table B)
What I’m unable to understand is:
why Table B is joined to Table F only on application received time, which is a timestamp.
why we even need Table A1
why we need the enforced join that is causing a loop in the SQL
Thanks for taking the time to look at this.
Dave,
In my model, I have a join between 3 tables A, B,C (say Sales, Delivery, Products) that causes a loop. When I select objects only from one 2 tables (say Sales/Material or Sales/Delivery) it still asks for context selection. Sales –> Material join is included in Context_A and suppressed in Context_B. The vice-versa is true for Sales –> Delivery join in Context_B. Despite that I am being prompted for a Context_A and Context_B every time. Would you please share your thoughts on this? I have checked this behavior in 3.x and 4.0.
Have been looking for an answer in almost all forums, so any help will be very great!
Thanks,
Anil
Hi Dave,
Made my job so much easier to explain contexts to the business.
Thanks heaps!!!
C
Hi,
When I have different joins between 2 tables and I want it being defined in two different contexts.What can i do apart from creating an alias table? Is there any other option that I can take into considerations?
Hoping to hear from you soon
hi Dave,
is there any way to drag 2 dimnesions objects from 2 fact tables in one data provider?
X1 dimension object from A fact table
X2 dimnesion object from B fact table
There is one context defined for A and another for B table.
Getting “imcompatible” error while running query.
One solution is to create seperate data provider for A and B table, then merge objects in WEBI report but user does not want in seperate data provider.
Kindly suggest.
Thanks,
Ravish
Generally all fact tables define their own context because of where they sit in the universe and the cardinality of their related joins. With measures you can split the SQL into two passes and it works fine. But dimensions must be included in all SQL passes in order to form a proper GROUP BY clause, and that’s where you’re falling into a problem. The dimensions are in different contexts, and therefore can’t be used in the same SQL statement. One way around this is to create a join between the two fact tables and use them in the same context.
Prachi, I’ve tried this in the past as well, and it just doesn’t work. š If you need to join to a table two different ways, you’re basically saying that you have two different purposes for that table, and that calls for an alias.
Hi Dave,
I understand needing to include all dimensions in every SQL pass when they are result objects, but why do they need to be included in every pass when used as a filter? I get an “Incompatible combination of tables” error when trying to do this. I feel like it should apply the filter to the queries the filter dimension is valid in, and skip it in the others.
For example, I have 3 result objects: DimA, MeasA, and MeasB where MeasA is from context 1, MeasB is from context 2, and DimA is valid in both contexts. The query gets split into 2 as expected. But if I add a filter on DimB from context 1, why doesn’t it only apply the filter to the SQL query for MeasA? DimB doesn’t even make sense in context 2 but I want to limit the results of MeasA in some way.
Is there a way to do this?
Remember a context is all about joins. If you include DimB in the query, then the source table has to be in the from clause and therefore the joins, which in the case of joining fact tables typically means you have a chasm trap to deal with.
The only way that I have come up with to apply a filter on DimB without impacting the query is to do the filter as a sub-query, which isolates the source table and eliminates the chasm trap.
Thank you Dave, For explaining this with very simple and Informative
Hi
I have 3 dimension tables say DimA DimB DimC
DimA -> DimB (M:1)
DimB -> DimC (1:M)
As this is creating a chasm trap, i have created two contexts DimA+DimB and DimB+DimC, But still when i try to get objects from all the 3 tables , Data is not getting synchronized and getting separate results for both the Queries.
Is there any workaround, as these are all Dimension tables
Thanks
If all you have are dimensions, then separate results are appropriate. There’s no way to merge the “child” dimension from DimA with the “child” dimensions of DimC without a Cartesian product.
What is your expectation for what would happen? How would you expect the query results to behave?