Has anyone come up against a problem like this…
I have a Fact table with 3 associated Aggregate Tables and a context for each. The most granular fact table has info at the Item:Customer:Day level. Then there’s one with Item:(no customer info):Wk, there’s one with CustomerRegion:ItemCategory:Wk, and one with Item:CustomerRegion:Day.
The measure object of interest uses @Aggregate_Aware to select from one of these tables.
I’m able to make selections on each of these tables when I select the corresponding dimension objects. But, if I choose the measure object along with the Customer, Item and Week objects, instead of using the lowest level Fact table and rolling up the days, I get incompatible objects.
At the moment I’m using @Aggregate_Aware in my dimension objects as well. Does that sound off to anyone?
Example:
There is a table ITEM_TBL where each row has Item and ItemCategory, and another ITEMCAT_TBL which gives only ItemCategories.
So the ItemCategory object has select statement - @Aggregate_Aware(ITEMCAT_TBL.ItemCat_FLD, ITEM_TBL.ItemCat_FLD)
These summary dimension tables belong to the same context as their appropriate Aggregate Fact Table.
So, if anyone can make sense of that and has a suggestion, please lemme have it.
Thanks.
My first thought is that you didn’t do the “Aggregate Naviagation” step. This will surely cause Incompatible Objects errors. Second thought is you don’t have a context for the underlying fact table.
When you said:
I assume you meant “from each of these tables”?
Regarding using @AA for dimension objects – sure. I assume what you’ve done is create ITEMCAT_TBL to avoid a chasm trap when joining an Item Category fact table to ITEM_TBL? This should work fine.
Now that I look again, the query I mentioned of Item, Customer, Week isn’t giving incompatible, it’s giving two synchro selects (which is also a problem).
The query that gives the incompatible is Item, CustomerRegion, Week. That one, I would expect to make a selection from the Item:CustomerRegion:Day Agg table - but no such luck.
Aggregate Navigation can get a little muddled, but I’ve got my low level fact table set to have no incompatibles, and I’ve got the Item:CustomerRegion:Day table only incompatible with Customer, so I don’t think that’s the problem.
I’ve got a context for each fact table, I’ve got no problem accessing them with a different combo of query objects. Luckily I’m not killing myself over that stupid mistake (this time).
You’re right about the measure object.
It selects from each of these table (one at a time of course).
Glad you’re down with my Agg Aware Dimension objects - didn’t find much about that in other posts.
So Item-Customer-Week give you two queries, but it should be using the fact table, correct? Which tables are the two queries referencing? If one of them includes a summary table, then that summary table doesn’t have Aggregate Navigation set correctly.
If it still doesn’t work, email me a copy of your universe and I’ll take a peek at it.
When going after Item:Customer:Week, I generate SQL and it asks for context selection. It uses this context to create a select of Item and Week. The other Select in the Synch is a selection of Item, Customer and the measure from the base fact table. The base fact table has no incompatibles accociated with it.
I can choose from contexts related or unrelated to this particular measure to create the select involving Item and Week. The really strange thing is that I can choose a context that creates a join through a fact table where that fact table has the Week object listed as incompatible. ???
You should never get a context prompt when working with @AA, that’s a sure sign that something is wrong.
Check the following:
Your contexts contain only one fact table each, and you have a context for each fact table (including the base fact). Essentially, your context for “summary 1” should contain every join except those that include the other summaries or underlying fact table.
All objects (including dimensions) should have the @AA function if they reference fields that exist in both underlying fact table and at least one summary table.
Can you send me your universe? I think I could debug it pretty quickly, after having gone through these same pains in setting mine up.
Looks like I can’t fork over the universe. I do appreciate the offer, Joe, but the whole confidentiality / information sensitivity thing isn’t going to make that possible. So I’m stuck with this beast.
I’ve got the single context for each of the fact tables, and all my @AA statements generate selects that touche one table at a time.
I can see the piece of each statement from each object that would fit together to create one select statement from one context - but it’s just not happening.
Does anyone know how the @Aggregate_Aware actually works? I want it to try and grab everything from one context as the highest priority, but I’m starting to think that’s not the function’s aim. But if it’s trying to use the ‘highest’ option from the various @AA statements, and combine those in synchro selects, shouldn’t it be able to adjust if this isn’t possible? Not getting a very good description from BO tech support.
Shared/conformed dimension tables you say? Not sure I follow. Are you thinking I should snowflake out my dimension tables to remove my @Agg_Aware for dimension objects? I guess that would only require added the joins between my tables and dropping the @AAs, I wouldn’t really need any new tables.
You’re right, I should probably start the troubleshoot from a less ‘fancy’ config. Is this the track you’re thinking of, or did I misunderstand you?
Regarding shared/conformed dimension tables:
Imagine two fact tables
Fact 1: Account & Actual Ledger
Fact 2: Account & Budget Ledger
Account information is shared between the two fact tables. All the lookup information for account (such as Created Date, Business Unit, Bank, etc.) should be stored in a separate table, which should then be joined to both fact tables (join on Account Nbr).
Dro, here are some additional debugging steps that may help:
Copy the universe to a test location
In the test universe, drop all the summary tables except one.
For all objects that use @AA: if the field in in the summary table, change the definition to point to just the summary, otherwise just point to the fact table (i.e. you’ll be removing the @AA function from all objects and using a direct table reference instead)
Build a query as you did before
If you still get syncro’d SQLs, then you, my friend, have a context problem. Double- (or triple-) check your universe to make sure that the objects in your query all exist in one (and only one) context.
You don’t mean joining two fact tables, do you? (shivers). Using an intentional synchronized SQL might work, but only if your selection of which table to use is based soley on the existence of measures. @AA allows you to define which table a measure comes from based on the existence of certain dimensions, which I’m guessing is what you’re trying to accomplish.
Andreas,
If I said different fact tables, I should have said just one fact, but different aggregation levels. With one aggregate fact table where info is at the product item level, and another where the info is rolled up to Category, I need a separate product dimension table to join at the item level (where each item has it’s category), and one to join at the Category level (without reference to individual items). How does shared/conformed dimension tables apply here?
Joe,
Don’t worry, I wasn’t talking about joining the fact tables ((shudder)).
What I meant was joining my ‘Item’ dimension table to my ‘Category’ dimension table. Then I could drop the @AA from my Category object and have it always come from the Category table. The example I gave in my first post, where the Category object had select statement…
would be changed to plain old :
ITEMCAT_TBL.ItemCat_FLD
(BOB member since 2002-08-29)
(BOB member since 2002-08-29)
(BOB member since 2002-06-20)
(BOB member since 2002-06-20)