IBM FileNet Case Analyzer Cubes’ Deep Customizations
Part 3: Last month amount, month-on-month growth rate
[Calculated Member in OLAP]
[May 9, 2012]
IBM
Shi Peng & Yiwei Song
Abstract: This is part 3 of the series, focuses on the report Last month amount and month-on-month growth rate. This part analyzes XYZ Insurance’s requirement, lists cons of Excel Pivot Table solution, introduces calculated member in OLAP and how to implement the calculated members required by the report.
Peng Shi previously worked as advisory software engineer in IBM China Software Development Lab Enterprise Content Management Center of Excellence. Currently, he works as the Knowledge Management Center senior manager in New Oriental Education and Technology Group in China. Reach out to her atshipeng3@xdf.cn.
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 at http://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
This part and next part explains how to create an important statistic for the reports: Growth rate comparing to last month and same month last year. This kind of statistic is applicable to most reports; let’s take process amount growth rate as example.
Requirement analysis
When creating a report of process (i.e. workflow instance) amount by process type, CA’s cube Work Load is the best choice. This cube includes dimensions Workflow and Time, measures Incoming, Outgoing, WL (i.e. Work Load) and etc.
Assume the customer is generating a report for Feb 2013, the Workflow dimension as rows, and the Incoming measure as column. Beyond this, customer needs an extra column showing Incoming amount of last month, Jan 2013, as well as a column showing the growth rate between Jan and Feb. So that customer may have the idea of their business growth in short term.
It’s definitely doable to use Excel’s power to achieve this.
- “Flatten” the Pivot Tables in Jan 2013 and Feb 2013 reports.
- Use Jan 2013 worksheet as base>copy Incoming column from Feb 2013 as another column, of course, a precondition in the Workflow rows Jan 2013 and Feb 2013 worksheets should be same.
- Create a new column with a function specified, and format as percentage.
Result is as follows:
Figure 1. Sample sheet of month-on-month growth in Excel
However, it’s not a good way to calculate in Excel because:
- The Excel Pivot Table doesn’t allow complex manipulations; the“flatten” operation is a must.
- “Flatten” and merging have to be done manually or by some script.
- Workflow row correspondence cannot be guaranteed.
- Most importantly, we expect to tune the data source rather than a presentation.
What if customer has Cognos BI or other OLAP clients as alternative presentations?
It is the best if the Work Load cube also provides measures Last Month Incoming and Month-on-month Incoming Growth Rate. The section below will try to adjust the Work Load cube by adding calculated members.
Calculated Member in OLAP
Calculated member is a customized measure or dimension member, defined by combining cube data, arithmetic operators, numbers, and functions. For example, we can create a calculated member named CNY (Chinese Yuan) that converts USD dollars to CNY by multiplying an existing USD measure by a conversion rate. CNY can then be displayed to end users in a separate row or column.
For a calculated member, its values are presentable but exist only in memory, which are not stored as cube data. (Referred to http://technet.microsoft.com/en-us/library/ms174952(SQL.90).aspx)
Creating Calculated Member last month amount in the cube
Here two calculated members will be created
- Last Month Incoming and
- Month-on-month Incoming Growth Rate
1. Again, bring up BI Studio, then connect to the same OLAP database or simply open recent project if you’ve created it in the last chapter.
Figure 2. Open existing solution in BI Studio
2. Open the Work Load cube>witch to Calculations tab.
Figure 3. Calculations tab of a cube
3. Click New Calculated Member button to create new Calculated Member.
Figure 4. Create new calculated member
4. Specify Name with [Last Month Incoming], Format string with “#”, fill Expression field with the following MDX expression.
Figure 5. Specify necessary fields of the new calculated member Last Month Incoming
Here is the MDX expression:
Listing 1. MDX expression for calculated member Last Month Incoming
IIF([Time].[Time].LEVEL IS [Time].[Time].[Month],
IIF([Time].[Time].CURRENTMEMBER.Properties(“KEY0”) –
[Time].[Time].PREVMEMBER.Properties(“KEY0”) = 1 OR
[Time].[Time].CURRENTMEMBER.Properties(“KEY0”) –
[Time].[Time].PREVMEMBER.Properties(“KEY0”) = -11,
([Measures].[Incoming],[Time].[Time].PREVMEMBER),
“0”),
“N/A”)
The outer IIF function judges whether the level of current dimension member is the month. If not, the expression returns “N/A”. It doesn’t make any sense to calculate the Last Month Incoming at year or day level.
Typically, in a Time dimension, all the members should be consecutive, like Dec (2012), Jan (2013), Feb, Mar, Apr, etc., so that MDX PREMEMBER function will definitely return to the last month. However, there is a possible exception in CA cubes. CA only processes those times which PE has process related activities. That is to say, in some certain month, there is not a single user or systems operate the PE, CA will bypass this month, the member of this month won’t exist. For example, we may find there is Oct (2012), Nov (2012), Jan (2013), Feb (2013) members in Month level of Time hierarchy, Dec (2012) is missing, maybe all employees are on vacation at the same time, who knows? There should be a Dec member indicating there are 0 incoming processes, but due to underlying design, CA just bypasses it. As a result, PREMEMBER won’t be safe.
The inner IIF function in above MDX expression handles such exception. The condition argument compares current month member and previous month member, to see if the two are consecutive. A Properties(“KEY0”) property converts month string value to number value, like “Feb” to 2, “Dec” to 12, so that the “-” operator can be used. The later “-11” condition implies current month is Jan and previous month is Dec. The return value is either incoming measure of previous month or a “0”.
Save the change, and then switch to Browser tab. Add Year and Month of Time dimension as columns, Workflow of Workflow dimension as rows, and add both Incoming and Last Month Incoming as measures. You can see the value of Last Month Incoming of a month is exactly the same as Incoming of previous month, while if previous month is missing, the Last Month Incoming will be “0”.
Figure 6. Preview the cube with new calculated member
You may hide Subtotal columns in context menu if you find it disturbing.
Figure 7. Hide the subtotal column of a level
You also need to check using levels other than Month level in Time dimension. It shows “N/A” as expected to avoid confusing.
Figure 8. The “N/A” value works well
Creating Calculated Member month-on-month incoming growth rate
A month-on-month growth is calculated by: (Current – Last)/Last, which is easy to achieve by Calculated Member.
Create new Calculated Member in same Work Load cube, and then specify Name with [Month-on-month Incoming Growth Rate], Format string with “Percent”, Expression with the following MDX:
Figure 9. Create new calculated member Month-on-month Incoming Growth Rate
Listing 2. MDX expression for calculated member Month-on-month Incoming Growth Rate
IIF([Measure].[Last Month Incoming] > 0,
([Measure].[Incoming] – [Measure].[Last Month Incoming])/[Measure].[Last Month
Incoming],
0)
Append Month-on-month Incoming Growth Rate measure in Browser and check the result.
Figure 10. Preview the cube with new calculated member Month-on-month Incoming Growth Rate
Note: The MDX expression above is simplified. Developers should consider current level and “N/A” hints as last section mentioned, as well as other optimizations.
Summary
This part implements the last month amount and month-on-month growth rate reports, which shows a different method of extending CA dimensions and cubes. Calculated member is widely used when calculations are needed on top of existing measures.
In the next part 4, calculated member is still the core, however some restrictions of CA block us from a straightforward implementation. It’s worthwhile to outline these restrictions and corresponding workarounds.
Appendix
Download PDF: FileNet Case Analyzer cubes deep customization.part3.pdf
Next part in series: IBM FileNet Case Analyzer Cubes’ Deep Customizations, Part 4: Same month last year amount, monthly growth rate on a year-overyear basis