Odd generated queries when using a dimension 1.000.000+ members

[florian]

Hi everyone,

I have a big problem I can't solve...

Let me explain the situation in a simplified way:

We run Cognos 10.2.2 with a DMR model deployed with DQM.

We have a fact table with ~800.000.000 entries, there are ~10 dimensions organized as star schema. Everything is quite simple and mostly working fine.

I have a report where I:

- have some detail filters where I limit the result set to a quite small volume
- sum up a measure grouped by the members of a dimension A
- have members of dimension B on rows of the crosstab, the measure in the fact cells

The SQL that is executed by the database looks like:

Code Select Expand


select DIM_A.members, sum(measure)
from FACT, DIM_Am some_other_DIMS
where <some filters on the other DIMS>
group by DIM_B


It looks quite different but you know what I mean...
The report runs in some seconds, everything is fine.

Now, when DIM_A is a dimension with 5.000.000 members, the way cognos tries to get the results seems to change dramatically.
On my database server I see the following statement:

Code Select Expand

select * from DIM_A

After some seconds the execution is completed and then I see the cognos server is working and working and working.
So what happens is that first of all Cognos tries to load ALL members of DIM_A into the BI server without filtering on the DB.

I can not see any reason for this behaviour. Alle dimensions are designed the same way in FM model. The only difference is the number of members.
IF the server would handle teh report like the first way, the DB query would run in some seconds...

Any idea?

Thanks in advance,
Florian

[New_Guy]

Hi Florian,
Just a thought, did you try using slicers instead of detail filters.
Good luck
New Guy

[bdbits]

Your second query has no fact table, so I would expect it to retrieve all members.

I have to wonder about a dimension with 5M members, though. It's possible I suppose, but...   ???

[florian]

Hi Florian,
Just a thought, did you try using slicers instead of detail filters.
Good luck
New Guy

No, I didn't.
Even if I would not have problems with the slicers...I WANT to know whats happening when I do it the way I do right now :-)

[florian]

Your second query has no fact table, so I would expect it to retrieve all members.

Exact. That's my problem.

I have to wonder about a dimension with 5M members, though. It's possible I suppose, but...   ???

It's a kind of customer dimension and we want to make analysis by customer.

[bdbits]

Detail filters on dimensional data sources are a very bad idea, and can contribute to problems like this. Please convert them to slicers. That may directly solve your problem.
http://www.cognoise.com/index.php/topic,18290.0.html

I would carefully examine the relationship definition in FM.

One of these two is probably causing the problem.

[Invisi]

Even if you make an analysis BY Customer, doesn't it mean that you relate your Customers to some metrics/facts?

Is processing set to database only?

[florian]

Detail filters on dimensional data sources are a very bad idea, and can contribute to problems like this. Please convert them to slicers. That may directly solve your problem.
http://www.cognoise.com/index.php/topic,18290.0.html

I would carefully examine the relationship definition in FM.

One of these two is probably causing the problem.

I tried a slicer - same behaviour.

FM model is quite simple and there are no conceptual differences between the big and small dimensions.
The fact and customer tables are modeled as 1:n and joined with a customer-ID.
Customer table has some attributes, name, gender and so on. On top of this table there is a customer-dimension with one level. ID --> business key, name --> membercaption.
Thats it, really simple and quite a few chances to make mistakes.

Than I made some further investigations:
In a test environment step by step I deleted data from the customer table, executed the report and caught the statements from my DB2.
When the size of the customer table was between 100.000 and 200.000, Cognos stopped to fetch the whole table and queried the data in one single sql statement that returned after some seconds. (as expected)

Than repopulated the customer table with all data --> Cognos fetches alle customers again, CPU usage 99%, raising RAM allocation.

Crazy.

Processing is set to database only.

[CognosPaul]

First thing - is the cardinality of the model correct?

Next, the way you're pulling the members from dim_a, is that a direct call to the level? Can you try building a set of a few members instead?

Finally, can you describe how you're totaling the dim_a members? Is that with total(currentMeasure within set [Dim_A]) or using extended data items? If it's extended data items, is the aggregation set to set or detail?

[florian]

First thing - is the cardinality of the model correct?

Next, the way you're pulling the members from dim_a, is that a direct call to the level? Can you try building a set of a few members instead?

Finally, can you describe how you're totaling the dim_a members? Is that with total(currentMeasure within set [Dim_A]) or using extended data items? If it's extended data items, is the aggregation set to set or detail?

Cardinality is definitely correct.

Until now, I just dragged the level into a crosstab.
Now I tried to build a member set with the filter function where I filtered for 10 customer-IDs --> same behaviour.

I don't totaling one of the ways you described.
I just drag the desired measure to the crosstab.

What I noticed right now:
I'm in Report Studio and navigate to my customer dimension in the model tree. Then when I click on the triangle left the 'Members'-Folder, then the only thing I see is: 'Loading'.

[Invisi]

Which indexes do you have on your related fact and dimension table? Specifically your dimension table?

[florian]

We have a DB2 BLU database, where it is not recommended to use indexes at all. I followed this advice :-)
There is only a non enforced foreign key between customer and fact table.

[CognosPaul]

When you say you're filtering the level, you're doing something like:

Code Select Expand

filter([Cube].[Dimension].[Hierarchy].[Level],[Cube].[Dimension].[Hierarchy].[Level].[Key] in (1,2,3,4,5,6,7,8,9,10))

That causes the problem?

What happens if you build a set directly?

Code Select Expand

set([Cube].[Dimension].[Hierarchy].[Level]:->[1]
,[Cube].[Dimension].[Hierarchy].[Level]:->[2]
,[Cube].[Dimension].[Hierarchy].[Level]:->[3]
,[Cube].[Dimension].[Hierarchy].[Level]:->[4]
,[Cube].[Dimension].[Hierarchy].[Level]:->[5]
,[Cube].[Dimension].[Hierarchy].[Level]:->[6]
,[Cube].[Dimension].[Hierarchy].[Level]:->[7]
,[Cube].[Dimension].[Hierarchy].[Level]:->[8]
,[Cube].[Dimension].[Hierarchy].[Level]:->[9]
,[Cube].[Dimension].[Hierarchy].[Level]:->[10]
)

[florian]

When I build the set by directly addressing the members, everything is fine.
The generated SQL looks like this (simplified):

Code Select Expand


SELECT
    "DIM_SALESMAN"."ID" AS "column0",
    "DIM_CUSTOMER"."ID" AS "column1",
    SUM(MY_MEASURE) AS "column2"
FROM
"FACT_TABLE" "FACT"
            INNER JOIN "DIM_CUSTOMER" "DIM_CUSTOMER"
            ON "DIM_CUSTOMER"."ID_DIM_CUSTOMER" = "FACT"."ID_DIM_CUSTOMER"
                INNER JOIN "DIM_SALESMAN" "DIM_SALESMAN"
                ON "FACT"."ID_DIM_SALESMAN" = "DIM_SALESMAN"."ID_DIM_SALESMAN"
WHERE
    "DIM_SALESMAN"."ID_DIM_SALESMAN" = 9767802 AND
    "DIM_CUSTOMER"."ID_DIM_CUSTOMER" IN (
        1287407,
        2821513,
        1288058,
        1287663,
        448608 ) 
GROUP BY
    "DIM_SALESMAN"."ID_DIM_SALESMAN",
    "DIM_CUSTOMER"."ID_DIM_CUSTOMER"

Executes immediately, great.

When I filter the set like

Code Select Expand


filter(
Customer;
Customer.[Customer-ID] in (448608; 2821513; 1287407; 1287663; 1288058)
)


or when I drag the level to the crosstab, then the SQL i can catch is:

Code Select Expand


SELECT DISTINCT
    "DIM_CUSTOMER"."ID_DIM_CUSTOMER" AS "column0"
FROM
    "DIM_CUSTOMER" "DIM_CUSTOMER"
ORDER BY
    "column0" ASC FOR FETCH ONLY


Report doesnt come back, RAM-allocation raises, very bad.

[CognosPaul]

Looks like the solution should be to build the set directly, instead of using the filter function.

Assuming a member looks like "[Cube].[Dimension].[Hierarchy].[Level]:->[123]", try using the following macro prompt:

Code Select Expand

#'set([Cube].[Dimension].[Hierarchy].[Level]:->['
+ join('],[Cube].[Dimension].[Hierarchy].[Level]:->[', split(';',promptmany('customers','token')))
+']'#


The field is set as an identifier? The level is unique? If so, I would also raise a PMR - Cognos should recognize it's filtering on the key and behave accordingly.

[florian]

Thanks for yout input Paul!

The field is set as an identifier, and the level is marked as unique.

The problem with your advise is that I don't want to filter at all. I want to see ALL customers for a salesman. Therefore the idea of drag and drop the level to the crosstab.

And the key point seems to be the size of the customer dimension. With smaller dimensions or a customer dimension with a subset of customers everything works as expected.

Right now our solution is to deploy the package Compatible Mode additionally for this one report (where it runs fine). This prevents us from using 64-bit mode, but we can live with that so far.

[CognosPaul]

What is the relationship from Salesman to Customer? Is it parent/child, or a separate dimension?

[florian]

Salesman is a separate dimension, completely independent from customer.
Connected to the fact table the same way as the customer. (1:n)

So, in the fact table there is a CUSTOMER_ID and a SALESMAN_ID.

[CognosPaul]

First, definitely open a PMR. I seem to remember a similar problem where dims were incorrectly being loaded in memory, but I don't remember how that was resolved.

Does the generated MDX look okay? Does the null suppression query hint affect the MDX/SQL?

Out of curiosity, what happens if you put the relational customer ID in the crosstab? (Yes, I know it's bad form to mix DMR and relational, but it does work.) How does affect the SQL?

[florian]

I'm no expert in MDX, but for me it looks ok.
Changing the suppression query hint doesnt change anything with the SQL.
Same with using the relational customer-ID.

Thanks for your input!
I will open a PMR.