Author Topic: Odd generated queries when using a dimension 1.000.000+ members  (Read 799 times)

Offline florian

  • Full Member
  • ***
  • Join Date: Jan 2015
  • Posts: 17
  • Forum Citizenship: +0/-0
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]
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]
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

Offline New_Guy

  • Full Member
  • ***
  • Join Date: Mar 2016
  • Posts: 22
  • Forum Citizenship: +0/-0
Re: Odd generated queries when using a dimension 1.000.000+ members
« Reply #1 on: 10 Jan 2017 09:27:46 am »
Hi Florian,
Just a thought, did you try using slicers instead of detail filters.
Good luck
New Guy

Offline bdbits

  • Super Moderator
  • Statesman
  • ******
  • Join Date: Feb 2010
  • Posts: 1,689
  • Forum Citizenship: +97/-0
Re: Odd generated queries when using a dimension 1.000.000+ members
« Reply #2 on: 10 Jan 2017 05:08:56 pm »
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...   ???

Offline florian

  • Full Member
  • ***
  • Join Date: Jan 2015
  • Posts: 17
  • Forum Citizenship: +0/-0
Re: Odd generated queries when using a dimension 1.000.000+ members
« Reply #3 on: 11 Jan 2017 05:45:01 am »
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 :-)

Offline florian

  • Full Member
  • ***
  • Join Date: Jan 2015
  • Posts: 17
  • Forum Citizenship: +0/-0
Re: Odd generated queries when using a dimension 1.000.000+ members
« Reply #4 on: 11 Jan 2017 08:52:22 am »
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.

Offline bdbits

  • Super Moderator
  • Statesman
  • ******
  • Join Date: Feb 2010
  • Posts: 1,689
  • Forum Citizenship: +97/-0
Re: Odd generated queries when using a dimension 1.000.000+ members
« Reply #5 on: 11 Jan 2017 11:50:37 am »
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.

Offline Invisi

  • Community Leader
  • *****
  • Join Date: Sep 2016
  • Posts: 104
  • Forum Citizenship: +3/-1
    • Invisi - Vision on Information
Re: Odd generated queries when using a dimension 1.000.000+ members
« Reply #6 on: 12 Jan 2017 02:45:15 am »
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?
Few can be done on Cognos | RTFM for those who ask basic questions...

Offline florian

  • Full Member
  • ***
  • Join Date: Jan 2015
  • Posts: 17
  • Forum Citizenship: +0/-0
Re: Odd generated queries when using a dimension 1.000.000+ members
« Reply #7 on: 16 Jan 2017 04:31:37 am »
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.





Offline CognosPaul

  • Global Moderator
  • Statesman
  • *****
  • Join Date: Jan 2009
  • Posts: 1,454
  • Forum Citizenship: +226/-1
    • Paul's Cognos Blog
Re: Odd generated queries when using a dimension 1.000.000+ members
« Reply #8 on: 16 Jan 2017 11:12:56 am »
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?

Offline florian

  • Full Member
  • ***
  • Join Date: Jan 2015
  • Posts: 17
  • Forum Citizenship: +0/-0
Re: Odd generated queries when using a dimension 1.000.000+ members
« Reply #9 on: 19 Jan 2017 02:59:27 am »
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'.

Offline Invisi

  • Community Leader
  • *****
  • Join Date: Sep 2016
  • Posts: 104
  • Forum Citizenship: +3/-1
    • Invisi - Vision on Information
Re: Odd generated queries when using a dimension 1.000.000+ members
« Reply #10 on: 20 Jan 2017 04:55:38 am »
Which indexes do you have on your related fact and dimension table? Specifically your dimension table?
Few can be done on Cognos | RTFM for those who ask basic questions...

Offline florian

  • Full Member
  • ***
  • Join Date: Jan 2015
  • Posts: 17
  • Forum Citizenship: +0/-0
Re: Odd generated queries when using a dimension 1.000.000+ members
« Reply #11 on: 23 Jan 2017 02:42:44 am »
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.

Offline CognosPaul

  • Global Moderator
  • Statesman
  • *****
  • Join Date: Jan 2009
  • Posts: 1,454
  • Forum Citizenship: +226/-1
    • Paul's Cognos Blog
Re: Odd generated queries when using a dimension 1.000.000+ members
« Reply #12 on: 23 Jan 2017 09:57:56 pm »
When you say you're filtering the level, you're doing something like:

Code: [Select]
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]
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]
)


Offline florian

  • Full Member
  • ***
  • Join Date: Jan 2015
  • Posts: 17
  • Forum Citizenship: +0/-0
Re: Odd generated queries when using a dimension 1.000.000+ members
« Reply #13 on: 24 Jan 2017 01:55:31 am »
When I build the set by directly addressing the members, everything is fine.
The generated SQL looks like this (simplified):

Code: [Select]
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]
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]
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.
« Last Edit: 24 Jan 2017 02:39:10 am by florian »

Offline CognosPaul

  • Global Moderator
  • Statesman
  • *****
  • Join Date: Jan 2009
  • Posts: 1,454
  • Forum Citizenship: +226/-1
    • Paul's Cognos Blog
Re: Odd generated queries when using a dimension 1.000.000+ members
« Reply #14 on: 24 Jan 2017 01:49:30 pm »
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]
#'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.

 



           
Twittear