DMR Functions in Framework Manager

[raviahuja21]

Hello Folks,

I am working on Cognos BI 10.2.1 version, I have created a DMR model in framework manager  (Query Mode is Compatible) I need a calculation which gives me the last 90 day average of sales for which I am using DMR functions like currentMember, bottomCount,descendants and so on. The problem is that when i write the function in the expression editor and run the test button (only for the query item) it gives me output without error, however as soon as I go and publish the package I get some error .
New Package
BMT-MD-2009 This package will publish 'measure' '[Presentation View].[Measure dim].[Average Daily Revenue]', which contains the following errors.

        BMT-MD-2004 The property 'Object Reference in Expression' references '[Presentation View].[Time].[yearno]'. This object is a 'hierarchy', which is invalid for this type of reference.
        BMT-MD-2004 The property 'Object Reference in Expression' references '[Presentation View].[Time].[yearno].[date]'. This object is a 'level', which is invalid for this type of reference.


When I try to do a test sample on the measure dimension which contains the calculated figure and one more measure which has no calculation I get the following error:
This query contains an error and can not be executed.
It is recommended that you view the query feedback on the "Query Information" tab.

GEN-ERR-0016 The 'currentMember' function is not supported in the context in which it is used.
GEN-ERR-0009 Data source type(s) '' - Function 'realDataMode' is not supported in 'NoDataModeProvider'.
GEN-ERR-0009 Data source type(s) 'OL' - Function 'currentMember' is not supported in 'RelationalQueryProvider'.
GEN-ERR-0009 Data source type(s) 'OL' - Function 'currentMember' is not supported in 'RelationalQueryProvider'.
GEN-ERR-0009 Data source type(s) '' - Function 'realDataMode' is not supported in 'NoDataModeProvider'.
GEN-ERR-0009 Data source type(s) 'OL' - Function 'currentMember' is not supported in 'RelationalQueryProvider'.
GEN-ERR-0011 The pattern 'providerQuery' is not supported in 'OlapQueryProvider'.
GEN-ERR-0009 Data source type(s) 'OL' - Function 'currentMember' is not supported in 'RelationalQueryProvider'.
GEN-ERR-0009 Data source type(s) '' - Function 'realDataMode' is not supported in 'NoDataModeProvider'.
GEN-ERR-0009 Data source type(s) 'OL' - Function 'currentMember' is not supported in 'RelationalQueryProvider'.
GEN-ERR-0011 The pattern 'providerQuery' is not supported in 'OlapQueryProvider'.
GEN-ERR-0009 Data source type(s) 'OL' - Function 'currentMember' is not supported in 'RelationalQueryProvider'.
GEN-ERR-0009 Data source type(s) '' - Function 'realDataMode' is not supported in 'NoDataModeProvider'.
GEN-ERR-0009 Data source type(s) 'OL' - Function 'currentMember' is not supported in 'RelationalQueryProvider'.
GEN-ERR-0011 The pattern 'providerQuery' is not supported in 'OlapQueryProvider'.
GEN-ERR-0009 Data source type(s) 'OL' - Function 'currentMember' is not supported in 'RelationalQueryProvider'.
GEN-ERR-0009 Data source type(s) '' - Function 'realDataMode' is not supported in 'NoDataModeProvider'.
GEN-ERR-0009 Data source type(s) 'OL' - Function 'currentMember' is not supported in 'RelationalQueryProvider'.
GEN-ERR-0011 The pattern 'providerQuery' is not supported in 'OlapQueryProvider'.

My model contains 2 dimension one is the Time dimension and the other is a regular dimension with one level (ID/Name) and 2 measures.
Here is the calculation that I am trying to put in the expression window in framework and the same function works fine in Report Studio.
total([Presentation View].[Measure dim].[NewCharges]  within set descendants (  currentMember([Presentation View].[Time].[yearno]  ),[Presentation View].[Time].[yearno].[date]  ))

Is there any restriction on using DMR functions like currentMember, etc in FM ? or is there a trick that I am missing.
Any help will be highly appreciated.

Regards
Ravi Ahuja.

[CognosPaul]

What happens if you try to use the expression in a crosstab in Report Studio? Do you get the same errors?

[raviahuja21]

Hi Paul,

Thanks for responding.

No. I do not get any error when I use these DMR functions in Report Studio, they work without any fuss. I am wondering why this weird behaviour.

Moreover what I am trying to do is to get the last 90 day average revenue for each day, unfortunately this is not possible in transformer, I have been trying this for last couple of weeks without any luck.


I am expecting an output something like this:(This certainly can be done using SQL query) but my problem is that I have more than one dimension, which makes it difficult to calculate in sql and it goes wrong once I it goes to the consolidation level, hence I have to resort to the DMR functions, again this can be done easily in Report Studio by creating a standard report, however the clients wants to use it for ad-hoc analysis, so I am stuck quite badly. Any help will be highly appreciated.

Date   ,Revenue,   Last 3 day Running Total,   3day average,   From-To Date


1-Jan   100   100   33   (30dec-1jan)
2-Jan   200   300   100   (31 dec-2jan)
3-Jan   100   400   133   (1jan-3jan)
4-Jan   50   350   117   (2jan-4jan)
5-Jan   20   170   57   (3jan-5jan)
6-Jan   15   85   28   (4jan-6jan)
7-Jan   20   55   18   (5jan-7jan)
8-Jan   30   65   22   (6jan-8jan)
9-Jan   100   150   50   (7jan-9jan)
10-Jan   20   150   50   (8jan-10jan)


Regards
Ravi

[CognosPaul]

Does this need to support Analysis Studio, or can you get away with providing a solution for Report Studio only? If you can get away with providing for RS only, you can create the expression as a standalone calculation or as a data item in a new query.

Unfortunately those don't appear in AS. It looks like the source of any dimensional object, Dimension or Measure, needs to be a purely database expression. This makes building dynamic dates difficult.

If you absolutely must have support for AS you will probably need to define a series of theta joins on the time dimension.

So for the 90 day rolling, create an alias shortcut of the time dimension table, call it 90 Day Rolling, join it back to the original with the following expression:
[Data Layer].[GO_TIME_DIM].[DAY_DATE] <= [Data Layer].[90 Day Rolling].[DAY_DATE] and[Data Layer].[GO_TIME_DIM].[DAY_DATE] >= _add_days([Data Layer].[90 Day Rolling].[DAY_DATE],-90)

Now there are a few directions you could go here. Play with this, more tomorrow.

[raviahuja21]

Hi Paul,

Thanks once again for your valuable inputs.  :)

Yes it needs support of AS, the user wants to do slice and dice on this.

I am sorry but I am not getting a direction with this join condition, it will simply return me 90 rows for each record.

May be I need to play more with it, I will keep you posted if I find a way.

So for the 90 day rolling, create an alias shortcut of the time dimension table, call it 90 Day Rolling, join it back to the original with the following expression:
[Data Layer].[GO_TIME_DIM].[DAY_DATE] <= [Data Layer].[90 Day Rolling].[DAY_DATE] and[Data Layer].[GO_TIME_DIM].[DAY_DATE] >= _add_days([Data Layer].[90 Day Rolling].[DAY_DATE],-90)

Now there are a few directions you could go here. Play with this, more tomorrow.

On the other hand what is more frustrating is that it is quite easily possible in the SSAS cube, it gives the option to write MDX query to calculate the measure value, however I could find no option of this in Transformer or FM and it is difficult to absorb the fact that it is not possible in Transformer.

Regards
Ravi

[Lynn]

Since you are on 10 then why not consider Business Insight Advanced/Cognos Workspace Advanced instead of Analysis Studio? You'd have no issues with the stand alone calculations in that case since the XML spec is the same as Report Studio. A much better authoring tool for users in my humble opinion.

[CognosPaul]

Cognos Workspace is cheating! That would be far too easy. And this is a nice modelling challenge. So nyah¹.

By creating a theta join on the time dimension, you can create a new path to a fact. Simply by creating an alias shortcut to the fact, and recreating all of the other joins to the other dimensions, you can create a "90 Days Revenue" measure in addition to the standard Revenue measure. When you drag the two in, Cognos will identify the many-to-many and create a full outer join.

Take a look at the attached model.xml

1. Nyah is a technical term. It means nyah¹.

[Lynn]

Cognos Workspace is cheating! That would be far too easy. And this is a nice modelling challenge. So nyah.

Well who could argue with that, especially using such technical terms as nyah  ;D

[raviahuja21]

Hi Paul,

Thanks for the solution  :)

That was fantastic, wondering there are so many things that are still to be discovered   :-[, many thanks to you.

Now the next step would be to get this data in Transformer or perhaps create a DMR model instead of PP cube.

Cognos Workspace is cheating! That would be far too easy. And this is a nice modelling challenge. So nyah¹.

By creating a theta join on the time dimension, you can create a new path to a fact. Simply by creating an alias shortcut to the fact, and recreating all of the other joins to the other dimensions, you can create a "90 Days Revenue" measure in addition to the standard Revenue measure. When you drag the two in, Cognos will identify the many-to-many and create a full outer join.

Take a look at the attached model.xml

1. Nyah is a technical term. It means nyah¹.

[CognosPaul]

It may be worth mentioning that dynamic cubes will allow you to create calculated members in hierarchies. You should be able to make 90day rolling members that can be applied to any measure, and not need to build the dozens of different measures to support whatever scenario might be needed.

[raviahuja21]

Hi Paul,

I would first like to thank you for all your exceptional and valuable advice.

I have not worked with Dynamic cubes but I have a fair idea of TM1, so by creating hierarchy does that mean I have to create hierarchies for each day containing 90 date elements ?

Thanks once again :)
Regards
Ravi Ahuja

[CognosPaul]

I haven't had the chance to test this, but I think you should be able to create a single hierarchy with a series of calculated members to do the time rollups. That way you just need to pull the 90-day rolling member from that hierarchy into an edge as needed.