Cognos FM - creating cumulative balance measure based on another measure

[eldarkrsic]

Hi,

I'm using IBM Cognos FM with Netezza DW to implement DMR model that end users on the BI system can use in IBM Cognos Analysis Studio - for ad-hoc analysis.

In this modeling process I got to the point where I need to create measures inside measure dimension such as cumulative balance, stock value etc. but I have problem implementing them. It is very easy to implement such measures in SSAS using MDX functions (periodsToDate).

What would be the concept to implement this in IBM Cognos FM DRM model. Please note that I cannot change DW and any solution that includes writing MDX in Report Studio isnot acceptable since this model will be used for ad-hoc analysis only.

This is the behavior that I would like to have:

If in the DW data I have:

AccountNo NetChange PostingDate
123           100             2007-02-05
123           200             2007-03-09
123           30               2008-05-05
123           500             2013-02-02

In the IBM Cognos Analysis Studio users should be able to create the following analysis:

Context filter = AccountNo = 123
Year   NetChange Balance
2007  300             300
2008  30               330
2009  0                 330
2010  0                 330
2011  0                 330
2012  0                 330
2013 500             830   


Net Change exists physically in the DW but balance does not and it should be calculated.

thank you in advance.

[adik]

you have to create a time dimension.
within your query subject where you have your netchange and posting date, add a new query item displaying just the year as extract(year, PostingDate)
then in your DMR create a new regular dimension with time, where year is the top level and postingdate is the bottom most level.
your users will then select the desired year, and NetChange will rollup automatically

[blom0344]

This looks to me like a matter of using running aggregates against the data, not as a regular roll-up issue.  Report Studio offers all these functions out-of-the-box, both for relational and dimensional models. You do not need to write MDX for this.


My 2 cents would be to use the available running aggregate functions for this..

[eldarkrsic]

thank you all for your help.

I agree that in my scenario I do not have the roll-up problem but running total problem tat needs to be solved. I am also aware how I can implement running totals in Report Studio but as I said RS will not be used by the end users, so I need some more generic solution that will work when model is used in the Analysis Studio.

Br

[bdbits]

I could well be wrong, but I do not think you are going to find a solution for a running total that works in Analysis Studio. Analysis Studio does what it does well, but it is really a fairly straightforward product and has limited functionality.

You might consider using Business Insight, but I am not positive it will handle this as I do not know the product well enough yet. It appears to me that Business Insight is the Cognos direction for future ad-hoc capabilities anyway.

[Lynn]

Business Insight Advanced (not Business Insight) is a good suggestion. In 10.2 this was renamed to Cognos Workspace Advanced. It is the same XML spec as Report Studio but with a user-simplified GUI.

It is the future direction and a good choice because a professional author can take a report the user created, make a tweak or two in Report Studio and give it back to the user to work with. You can't go from RS to QS or to AS.

[bdbits]

Yes you are right, the Advanced version is the one to use.

Perhaps veering a wee bit off topic, but... I would note that while you *can* export an Analysis Studio report to Report Studio, you would never *want* to. If you've never tried it, well, let's just say it's not a pretty sight. You're better off starting from scratch. And of course you cannot send it back to AS. Not that the original poster was asking to do any of these things, but I still have nightmares trying to modify reports somebody started in AS and sharing is part of my therapy. ;-)

[Lynn]

Yes you are right, the Advanced version is the one to use.

Perhaps veering a wee bit off topic, but... I would note that while you *can* export an Analysis Studio report to Report Studio, you would never *want* to. If you've never tried it, well, let's just say it's not a pretty sight. You're better off starting from scratch. And of course you cannot send it back to AS. Not that the original poster was asking to do any of these things, but I still have nightmares trying to modify reports somebody started in AS and sharing is part of my therapy. ;-)

;D
We are here to help you heal

[eldarkrsic]

Tried Business Insight Advanced and at the beginning it seemed like a good idea. What surprised me is that the same query calculation of the cumulative balance  that works perfectly in the report studio does not work in the business insight studio when I tried drill-down. For example I have correct values on the year level and when I drill down to month level there are no values - this does not happen in report studio.
Created 2 identical reports one in report studio (works correctly) and one in insight advanced (does not work as expected).

[eldarkrsic]

Finally I Implemented this cumulative balance feature inside Framework Manager. Revelation came when I found the  document   on the IBM web site - http://www.ibm.com/developerworks/data/library/cognos/page90.html.

While reading through this document I got an idea:

- Duplicate facts table (I'll use "Net change" column from this cloned table to calculate cumulative balance) - this is my Database layer in the FM  project
- Create relation between date dimension table and this cloned facts table but as a relation I'll not use
  dim_date.DateId = ClonedFactsTable.PostingDateId but fact.POSTINGDATEID <= dim_date.DateId  - this is my Database layer in FM project
- On the presentation level add Balance measure that has the source = ClonedFactsTable.NetChange into the measure dimension that already  existed and contained Current Net Change measure - This is my Presentation Layer in FM project
- Set Aggregate Rule for Balance measure with the following properties: Dimension = Date dimension, Aggregate Rule = Last

After I published my package I was able to use Balance measure  as i expected and the values I got are correct for any scenario drill-down etc.

[eldarkrsic]

Of course one should be careful using fact.POSTINGDATEID <= dim_date.DateId relation since this will create very expensive join between facts  table and date dimension on the database level - but our customer has DW on Netezza and it works pretty fast even with this

[blom0344]

I guess you implemented the 'triangular join' solution for calculating running total and you are right , it is a very expensive solution. It is also a solution that can go quickly through the roof as the number of reads grows exponentially with the number of records involved. Exponentially , and not linear, so while it may work now, performance may drop much quicker than expected when the DW grows.  In other words, pretty fast may be short lived  ???