Cube not using Aggregate Cube

[zid]

Hello everybody,

I've got a little problem for some time now.
I did a report which uses customer data of several stores (purchases, etc.)
The database tables are not aggregated. So i can get every customer number if i want to. As expected the performance is rather poor.

I'll just outline it quickly:

Fact Table
Date_key, store_id, customer_id,[...], measure1, measure2, [...]

Now i've done two aggregate cubes for this cube.
One just strips the customer_id and aggregates everything else so it looks like this:

Fact Table AGG Store
Date_key, store_id, [...], measure1, measure2, [...]

the second one is for an overview over all stores, which looks like this:
Fact Table AGG Store
Date_key, [...], measure1, measure2, [...]

As I'm using the same keys as in the original cube I just modeled two aggregate cubes in cube designer, using the same shared dimensions and assigned the measures accordingly.

Now if i use the report i get better performance if I just select one store. But if I don't select any store i still get poor performance. It seems like the Cube doesn't acknowledge the full-aggregation cube.
I didn't make any use of the slicer or key assignment tabs (shouldnt be necessary?) in cube designer.

Any idea where i'm going wrong?

Greetings
zid

[bus_pass_man]

More questions than answers.    :P

It seems like the Cube doesn't acknowledge the full-aggregation cube.

What sorts of steps have you taken to determine this?   Have you traced the queries?  If so, what is being indicated? Have you used the DQA?

I didn't make any use of the slicer or key assignment tabs (shouldnt be necessary?)

Can you clarify this please.  If you didn't map the keys what did you do to define the aggregates in the cube? It might be the case that reading the relevant bits of the redbook might come in handy, especially as the aggregate UI doesn't leap out and guide you by the hand.     

What happens if you have more than one store in the report?  What happens if you have the store level in the report? If you have a level in your store dimension which is higher than store and you have it or a member from it in the report what happens?

Fact Table AGG Store
Date_key, store_id, [...], measure1, measure2, [...]

the second one is for an overview over all stores, which looks like this:
Fact Table AGG Store
Date_key, [...], measure1, measure2, [...]

I don't know what the ellipses are indicating.  Are any dimensions not listed being omitted or not from the aggregates?  A query can only be routed to a aggregate if everything in the query is contained in the aggregate.  Not everything in the aggregate needs to be in the query.  For example if your aggregate has dimensions A, B, and C and measures 1 and 2 but your query has dimensions B and C and measure 2 then the query can be routed to the aggregate.

What else do you have in the report?  It could be that your report has something in it that is not in the set  of objects defined in the aggregates so the query is routing to the detail fact table (which is why tracing would be useful as the aggregation strategy setting would tell you why an aggregate is being used or not.)

[zid]

More questions than answers.    :P

Yeah, my description could have been a little bit more detailed ;)

What sorts of steps have you taken to determine this?   Have you traced the queries?  If so, what is being indicated? Have you used the DQA?

I used the DQA to view the logged querys and looked at the querys at runtime on the database.

Can you clarify this please.  If you didn't map the keys what did you do to define the aggregates in the cube? It might be the case that reading the relevant bits of the redbook might come in handy, especially as the aggregate UI doesn't leap out and guide you by the hand.     

What happens if you have more than one store in the report?  What happens if you have the store level in the report? If you have a level in your store dimension which is higher than store and you have it or a member from it in the report what happens?

Well, I added the shared dimensions to the cube and did the join on the lowest detail-level of the dimension. (for example by store_id not by region_id)
The tab i didn't use was the one to map dimension in a higher hierarchy.

Also I should describe the Report a little bit better.
It's a summary of a shops, or the whole companys performance for the current year. In a parameter you can either select a store, multiple stores or no store at all to get data for the whole company.
So if I select one store the reports performs okay. It's about 1-3 minutes for the whole report. It uses the first Aggregate Table. If i select multiple stores it takes some more time, but still uses the aggregate table.
If no store is selected, which means it should use aggregate table #2 it still uses the normal cube which takes ages. The only reason to use the standard cube without aggregation would be to get the sales data of one particular customer, cause it's the only one to hold the customer_id.

I don't know what the ellipses are indicating.  Are any dimensions not listed being omitted or not from the aggregates?  A query can only be routed to a aggregate if everything in the query is contained in the aggregate.  Not everything in the aggregate needs to be in the query.  For example if your aggregate has dimensions A, B, and C and measures 1 and 2 but your query has dimensions B and C and measure 2 then the query can be routed to the aggregate.

What else do you have in the report?  It could be that your report has something in it that is not in the set of objects defined in the aggregates so the query is routing to the detail fact table (which is why tracing would be useful as the aggregation strategy setting would tell you why an aggregate is being used or not.)

The ellipses indicate missing dimensions or measures, simply because i was to lazy to write them all down ;)
In the first Aggregate table the customer_id is omitted, in the second aggregate table the store_id is also omitted.

The aggregate tables do have all the data needed in the report. They just don't have customer_id, or customer_id and store_id.

I use the tracing feature quite regularly.

If you need any more information I'll gladly provide them :)

Thanks!
zid


//edit:

I read a little bit more logs and it seems like it does use the aggregate cube. But only for some measures. There is one measure which uses the aggregation rule average. For this measure it querys the unaggregated table.

//edit2:

It seems this was the reason for the bad performance. I found a implementation option in the IBM Cognos Dynamic Cubes redbook. They described there that i should compute the average with count and sum and then use average on that computed measure. Well...that didn't work either because he still didn't use the aggregate cubes.
I now solved it by using aggregation rule sum with the computed measure and still using average on the aggregate cube.

[bus_pass_man]

When you say

Well...that didn't work either because he still didn't use the aggregate cubes.

can you explain why?  Did anything get logged which would tell you why the query did not route to the aggregate table?