Clash of the Data Structues (limits of DMR?!)

[charon]

Heyho everyone,
im coming up with an interesting requierment.

Please take a loot at the following very simple star schema, modeled as DMR modell in Framework Manager 10.2.1, DQM enabled.

Dimension Region
Top Level is Region > Country > State > City

Dimension Time

Fact Table, wich has a daily key (20110113, yyyymmdd) and facts on city level. Therefore, the fact table is joined on the most granular level (as it should be;).

The report has a crosstab in it, with REGIONS as rows...the requested calculations have to be in the columns. The user chooses a MONTH in a prompt. Revenue is a fact in the table.

             ____ Calculation 1              Calculation 2
Region |                                    |
            |                                    |
     

Now, here comes the request/ problem:

calc. 1: "In case we had revenue in a city for at least one day in this time period (chosen month), THEN aggregate me all revenues for THIS CITY for this time period. "
Calc. 2: "In case we had revenue in a city for at least one day in this time period, then count me the distinct attributes (e.g. population category) of this city for this time period."

Sounds trivial, doesnt it? The challenge im seein are,
- i can not use levels of the same dimension in rows (region) and columns (per city)
- counting members (like cognos paul suggested in a previous post) with "count [attribute] for [city]" is unbelieveable slow.
- functions like "if ([revenue] > 0) then ([revenue] for [city]) else (0)" dont work

Ive tried every single dim. function i know, from simple tuple over complex functions with prompts macros within sets/Details and so on...didnt work  =/

I have the feeling, that the core issue is the Dimensional vs. Relational twist. I mean, i want to check each row first for revenue (relational), and in case there is at least one day with positive revenue i wantt group by city for this time period. And also count [attribute] for the next level of Dimension "Region", [state]...ive tried approaches with some logic in FM, with multiple queries in RS, my current idea is to flag the cities that have a positive revenue in a month in the etl in a staging area and then get simple total statements off..but stil, this wont solve all my calculated requests (there are 6!).

We need the DMR, cause we need the drill functionality. What REALY bothers me that im able to create a simple sql statement for this number in 2 minutes which work..but im not able to figure it out how cognos can do that :/

Is there a way to achieve this? Or is this the limit of Cognos and DMR?

looking forward for ideas...best regards :)
Charon

[charon]

Hi, might be a bit abstract...let me give you an example.

Fact Table

          City       |       Time       |      Relevance      |      Fact1     |     Fact2        |
        Atown          20130901            high                         0                    1
        Atown          20130801            low                         31                  15
        Atown          20130905            middle                      0                   53
        Btown          20130901            high                         3                   77
        Btown          20130905            low                          0                   11
        Btown          20130813            high                         66                  3

Request in Cognos:
"If there is a (Fact1 > 0) in a time period (month) and for a City for even one day, then aggregate/ sum me all fact2 for
this city and time period.


Example: User chooses 201309 for month (month coming from the time dimension of course)

RESULT:
Fact2 for Atown => 0, because the two records for 201309(xx) have no fact1>0
Fact2 for Btown => 88, because [BTown] has in this time period a fact1>0, therefore im aggregating all records for this city for this timeperiod


Is this even possible to achieve in Cognos? To aggregate for an level of a dimension ([city]) for a dynamic time period ([month]) in case there has been records that succed in this boolean logic (fact > 0)?

Also, even more sophisticated...
If there is record for a [city] with fact1>0 in the chosen timeperiod, then count distinct the attribute [relevance], but not for [City] but for [State] (next level)...

:o  Ideas?

best regards  :-X
charon

[bonniehsueh]

Hi Charon,
You may have already tried this, but thought I share a few mdx functions that come to mind. Relational expressions like if, then, case, for and in will be very slow. If it's DMR or an olap source, you'll find the best performance using mdx functions.

You can get a count using the count member summary function. An example of this can be found http://businessintelligence.ittoolbox.com/groups/technical-functional/cognos8-l/count-number-of-members-in-level-or-children-within-member-3993314

To display certain cities that meet conditions, you can use the filter function. The filter function allows you to filter members based on a boolean expression. We can call this calculated data item [filtered cities]

Summing facts can be done by using member summaries like aggregate (within set [filtered cities]).

I know its not the answer but I hope you find it helpful.

[blom0344]

If possible I would push this type of logic into the backend.  Even if you can define some sort of logic from the frontend, you may end up with serious performance issues.    So ,in post processing I would add a flag field to the table : true/false and then the fact definitions would be something like:

Code Select Expand


CASE WHEN FLAG = 'TRUE' THEN FACT_VALUE ELSE 0 END


[charon]

Hi Guys,

thx for replying!

@ blom: im glad i got the same approach as an expert like you. indeed, ive created a table that flaged me all records for an article id in case this article id has one record with fact1 > 0 per month.

@ buddy: i think your suggestion is very useful. though i did try a lot of dim functions (filter incl of course) you still were able to rise some new ideas.

Thx a lot and have a nice day, il post again in case i get a working solution  :P
cheerz