DMR Filtering

[CogUser16]

Hi Gurus,

I am new to Cognos and trying to create a crosstab from a HR DMR model that requires a filter on measure values (not the default measure). In more detail:

I need to return [Total Headcount Measure] (this would be the default measure) when the [FTE Measure] is greater than 0.5 for each [Employee] (The most granular level). The crosstab is based on [Process] in the left node, and [Division] across the top, for the prior month. These are groupings in seperate dimensions.

Prior month is covered as the Time dimension has an aggregate rule of last.

I thought I could create a filtered Measure of 'filter([Total headcount Measure], [FTE measure] > 0.5)' and place in the default measure section of crosstab but this filters the total of the crosstab. I need the filter to be applied before any aggregation but am unable to figure out how.

I'm thinking I might need to use some sort of tuple function but not sure where to put the measure condition.


**EDIT**

The crosstab will have totals that also need to reflect the filtering. 


Any help appreciated.


Thanks,

[MFGF]

Hi Gurus,

I am new to Cognos and trying to create a crosstab from a HR DMR model that requires a filter on measure values (not the default measure). In more detail:

I need to return [Total Headcount Measure] (this would be the default measure) when the [FTE Measure] is greater than 0.5 for each [Employee] (The most granular level). The crosstab is based on [Process] in the left node, and [Division] across the top, for the prior month. These are groupings in seperate dimensions.

Prior month is covered as the Time dimension has an aggregate rule of last.

I thought I could create a filtered Measure of 'filter([Total headcount Measure], [FTE measure] > 0.5)' and place in the default measure section of crosstab but this filters the total of the crosstab. I need the filter to be applied before any aggregation but am unable to figure out how.

I'm thinking I might need to use some sort of tuple function but not sure where to put the measure condition.


**EDIT**

The crosstab will have totals that also need to reflect the filtering. 


Any help appreciated.


Thanks,

Hi,

The first challenge is to get the set of employees whose FTE Measure is greater than 0.5. An expression to do this would be:

filter([your Employees level], [FTE Measure] > 0.5)

Assuming you want this set to affect the measure values in the rest of your crosstab (ie to act as context), just use this as the expression for a slicer member set in the Slicer Filter section of your query.

If I've misunderstood, please advise on the detail of how the set of employees should affect things in your crosstab :)

Cheers!

MF.

[CogUser16]

Hi MF,

You understood my query correctly, I let myslef get confused, apologies for the simple, simple query!

Is there any technical documentation on how cognos builds the cubes within DMR. I'm assuming it sends a sql query to the underlying BD (Oracle in my case), then builds a cube (in cognos?) with the resulting data.

With for example the above query, will cognos essentailly select all data from the DB, build a cube, then filter as per the slicer?

Our dataset is relatively small (<50,000 in FACT table) which isn't a problem, but I imagine performance would be poor on larger star schemas, if the above is correct?

Thanks,
Louis

[MFGF]

Hi MF,

You understood my query correctly, I let myslef get confused, apologies for the simple, simple query!

Is there any technical documentation on how cognos builds the cubes within DMR. I'm assuming it sends a sql query to the underlying BD (Oracle in my case), then builds a cube (in cognos?) with the resulting data.

With for example the above query, will cognos essentailly select all data from the DB, build a cube, then filter as per the slicer?

Our dataset is relatively small (<50,000 in FACT table) which isn't a problem, but I imagine performance would be poor on larger star schemas, if the above is correct?

Thanks,
Louis

Hi,

DMR is essentially a cube that isn't really a cube :)  It comprises logical structures that look and behave like dimensions, levels, members and measures in a cube, but in reality when you use these, SQL queries are sent to the underlying tables in the database to retrieve the data. DMR works well with small to medium(ish) data volumes, but doesn't scale up very effectively, so you might get quite poor performance over large data volumes. IBM doesn't appear to publish any specific guidelines on actual volumes (as I'm sure it will vary on a case-by-case basis), but my gut feel (just my own opinion) is that you wouldn't want a fact dataset of more than 20 million rows if using DMR. Probably smaller if you're not using Dynamic Query Mode too.

Cheers!

MF.