Fellow Listers,
I see that when I pull in a particular object from a Universe…it pulls in a JOIN which uses a VIEW. And that VIEW wouldn’t be listed in the ‘from’ clause of the SQL. So I get invalid-column name error, which is understandable.
My feeling is that, this join and the view are not defined in the same CONTEXT. How can I see what are all the TABLES and JOINS a CONTEXT has in it. I have Designer open with LIST VIEW MODE. Now guide me from there.
In a message dated 00-03-06 15:37:41 EST, you write:
My feeling is that, this join and the view are not defined in the same CONTEXT. How can I see what are all the TABLES and JOINS a CONTEXT has in it. I have Designer open with LIST VIEW MODE. Now guide me from there.
Right-click on the context name, and select Properties. That screen will list all of the joins.
Alternatively, click on the small triangle symbol on the border between the list of contexts and the list of joins. Click on the triangle pointing to the joins after selecting a context. The joins included in that context will be the only ones listed.
Just thinking again . . .
I have a universe with three different sets of transaction tables (sales orders, invoices, credits) that all use the Customer_Master and Customer_Addresses tables for reference tables. I do not currently have the Customer_Master or Customer_Addresses tables aliased as I don’t ever pull information from more than one set of transaction tables at a time. I also don’t have any loops doing this as each set of transaction tables is not linked to the next. (It looks like a tree: Customer_Master to Customer_Addr, then branched out Customer_Addr to Sales_Order_Header to etc.; Customer_Addr to Invoice_Header to etc.; Customer_Addr to Credit_Header to etc.)
I’m wondering, however, if I should add contexts, and would this speed up reports where information from the Customer tables is referenced?
Currently I have reports that, when running only transaction table data, will take 3-4 seconds to return, but as soon as I add an object that would reference the Customer tables, the report takes 30-40 minutes. Is this because, without a context, BO is actually going up the tree to the customer tables and then back down all three “branches” instead of only one (i.e. going thru data from all transaction tables instead of just the one I’m looking for)?
If anyone can confirm/disprove my hypothesis, I would greatly appreciate it.
Why not look at the SQL that is generated at the moment. Then add the contexts you described and revisit the SQL. You should be able to work out what is happening from this.
********************************************************************** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager.
This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses.
You don’t have loops since you only have 1 real dimension. Contexts will help alot. You need 1 for each transaction table.
The other benefit of contexts is in the report processing itself. If you try to report invoices and credits, BO will create 2 cubes and link them together. This makes report navigation slower. If you use contexts BO runs the 2 queries automatically and puts the results into a single cube.
When creating contexts manually, do I include the joins to reference tables along the way or only the tables involved in the main path? (Sorry - I haven’t “played with” contexts and I took the class almost a year ago).
When creating contexts manually, do I include the joins to reference tables along the way or only the tables involved in the main path? (Sorry - I haven’t “played with” contexts and I took the class almost a year ago).
When using contexts you will have to include all joins except joins to your other fact tables. If you have three contexts for INVOICES, CREDITS AND ADJUSTMENTS. The INVOICES context must include all joins except fot those that connect to the CREDITs and ADJUSTMENTS tables. The CREDITS context must contain all joins except for those that connect INVOICES and ADJUSTMENTS tables. The ADJUSTMENTS context must include all joins except for those that connect the INVOICES and CREDITS tables.
The best way to check that your contexts contain the right joins is in the structure window. First activate the list mode so you can see the display that shows Tables, Joins and Contexts. To check the context in question click on the context and see what tables are highlighted in the structure window. For the INVOICES context all tables should be highlighted EXCEPT the CREDITS and ADJUSTMENTS tables. For the CREDITS context all tables should be highlighted EXCEPT the INVOICES and ADJUSTMENTS tables. For the ADJUSTMENTS context all tables should be highlighted EXCEPT the INVOICES and CREDITS tables.
Another test to make sure that you have set up contexts properly is to perform the integrity check for contexts. When you perform this test there should be no “orphaned” joins. Orphaned joins are joins that do not participate in any context. Once you use contexts you must also make sure when adding tables and joins that they are also added to the appropriate context(s).
Mike McErlain
McErlain Consulting
_________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
Share information about yourself, create your own public profile at http://profiles.msn.com.
In a message dated 00-08-31 12:08:57 EDT, you write:
Is is a better practice to use ‘Contexts’ versus ‘Aliasing’?
Not necessarily.
Or, would there be certain circumstances where you should probably use one method rather than the other?
Most definitely.
Also, can Contexts and Aliasing be used together in a universe?
Certainly.
Both contexts and aliases can be used to resolve loops. However, an alias actually breaks the loop up so that it doesn’t exist any more, while a context identifies a path within the loop to be used for SQL generation. In general, aliases are used for lookup tables while contexts are used for more complex scenarios.
An alternative to contexts, depending on the structure of your database, may be to create two separate (smaller, and less complex) universes rather than attempting to replicate the entire structure in a single universe.
Did I send you this before. I spoke with Denise Legault, she thought this might be useful for you. I’m going to be at SGI on Wednesday 9/6 if you want to talk about this.
I have 2 contexts set up in my universe call them flash and weekly. I’ve created a report that I excpect it to use flash context. For the most part it seem like it is. I have set up a scope of analysis on the report a pre-defined one. The sql look fine when I don’t have the scope of analysis on the report but when I add the scope of analysis it adds a second sql statement and that sql statment has a table that its using that has nothing to do with my query its using the weekly table instead of the flash and I think its causing my #'s to be drastically overstated. Why would it be using a table that nots set up in my context with a query when I add the scope of analsis but not when I take it off. The numbers are correct when I take off the scope of analyis. Any ideas?
I took a look at the context again and there were 2 joins that should have been incuded in the context that BO didn’t automatically detect when it created the context. I added them reran the query and it worked fine both with the hierarchy and without now!
Stacy