IBM FileNet Case Analyzer Cubes’ Deep Customizations
Part 4: Same month last year amount, monthly growth rate on a year-over-year basis
A Workaround for CA Time Dimension Restrictions
5/16/2012
IBM
Jie Zhang & Yiwei Song
Abstract: This is part 4 of series, focuses on the report same month last year amount and monthly growth rate on a year-over-year basis. This part analyzes XYZ Insurance’s requirement, outlines a restriction in CA Time dimension, and gives a workaround to overcome this restriction.
Zhang Jie works as a software engineer in IBM CDL. He has many years’ experiences of Java/J2EE development, test and test automation. Reach out to him atzhjie@cn.ibm.com
Yiwei Song, a member of CDL ECM CoE (Center of Excellence) in China, certified FileNet (4.0) developer. Has 5 years FileNet engagement experience, especially familiar with FileNet BPM. Acted as product expert and served several customer cases across insurance, banking and E&U industries. You may reach him athttp://linkedin.com/in/yiweisong
Link to the first part of the article:
Part 1 on Evis.ME | Part 1 on developerWorks
Link to the second part of this article:
Part 2 on Evis.ME | Part 2 on developerWorks
Link to the third part of this article:
Part 3 on Evis.ME | Part 3 on developerWorks
This part continues the topic of last one, focuses on same month last year amount and monthly growth on a year-over-year basis.
Requirement analysis
The same month last year amount and monthly growth on a year-over-year basis statistic is a typical report requirement and is important to company management. For example, there are 358 IssuePolicy businesses happened in Jan 2013 and 88 in Jan 2012, the growth on a year-over-year basis is (358 – 88) / 88 = 306.8%, which shows quite a promising rate.
Actually, in MDX or SSAS, there are many Time dimension optimized operations or functions. PARALLELPERIOD is a MDX function that can retrieve a member from a prior period in the same relative position as a specified member.
So the first trial is quite straightforward. Create a new Calculated Member Same Month Last Year Incoming with the MDX below:
Listing 1. Expected MDX locating Same Month Last Year
([Measure].[Incoming],PARALLELPERIOD([Time].[Time].[Year]))
The Browser result is as follows.
Figure 1. Unexpected data found in Jan 2012 and Dec 2012
Comparing Jan 2013 and Jan 2012, the Same Month Last Year Incoming works well. But for Dec 2012 and Dec 2011, the values are missing, at least a “3-IssuePolicy” value. Actually, the test data doesn’t contains any of Jan 2011, so the “3-IssuePolicy” value in Jan 2012 is not right, seems it takes the value from Dec 2011 instead. Similarly, corresponding value in Dec 2012 is empty, which is incorrect.
This problem is also caused by CA’s bypassing non-activity months. Just as mentioned in last chapter, the members of Time dimension in CA cubes may not be consecutive. The PARALLELPERIOD function tries to locate the month with the same index in last year, however such index may not equal to the month number in CA’s case.
A workaround is needed.
Creating Calculated Member Same month last year amount in CA cubes
A Time dimension member at Month level can be presented as “[Time].[Time].[Month].&[2]&[2012]”, while the “&[2]&[2012]” suffix corresponds to composite key of the Month level. The idea is to get both current month key and last year key from current member, and then use those keys to find same month last year member.
Create new Calculated Member in the same cube. The Name is [Same Month Last Year Incoming], the Format string is “#”, and the Expression is:
Listing 2. New MDX expression for Same Month Last Year Incoming
([Measures].[Incoming], STRTOMEMBER(‘[Time].[Time].[Month].&[‘ +
[Time].[Time].CURRENTMEMBER.Properties(“KEY0”) + ‘]&[‘ +
ANCESTOR([Time].[Time].CURRENTMEMBER,
[Time].[Time].[Year]).PREVMEMBER.Properties(“KEY0”) + ‘]’))
Figure 2. Create new calculated member Same Month Last Year Incoming
The ANCESTOR function goes up and gets the Year-level member containing current Month member.
Preview in Browser tab. The problem of last section is resolved.
Figure 3. Preview the cube with new calculated member Same Month Last Year Incoming
Creating Calculated Member Monthly growth rate on year-over-year basis in CA cubes
There won’t be any difficulties creating this Calculated Member.
The Name is Monthly Growth Rate on Year-over-Year Basis, the Format string is “Percent”, and the Expression is:
Listing 3. MDX expression for Monthly Growth Rate on Year-over-Year Basis
IIF([Measures].[Same Month Last Year Incoming] > 0, ([Measures].[Incoming] –
[Measures].[Same Month Last Year Incoming]) / [Measures].[Same Month Last Year
Incoming], 0)
Figure 4. Create new calculated member Monthly Growth Rate on Year-over-Year Basis
Preview in Browser tab:
Figure 5. Preview the cube with calculated member Monthly Growth Rate on Year-over-Year Basis
Summary
Most products have restrictions, though those restrictions are reasonable for most cases in a product perspective. However customers won’t always accept those restrictions. A workaround is a way of maximizing products’ potential.
This part gives two possible options of implementing the report same month last year amount and monthly growth on a year-over-year basis. Both solutions leverage OLAP calculated member. The former solution is not applicable due to the CA Time dimension’s restrictions, while the later one can be considered as a good workaround.
Next part will be based on another different OLAP feature.
Appendix
Download PDF: FileNet Case Analyzer cubes deep customization.part4.pdf
Next part in series: IBM FileNet Case Analyzer Cubes’ Deep Customizations, Part 5: Extending Case Analyzer Measures using Named Calculation