IBM FileNet Case Analyzer Cubes’ Deep Customizations, Part 5: Extending Case Analyzer Measures using Named Calculation – 壹维思_EVIS

IBM FileNet Case Analyzer Cubes’ Deep Customizations, Part 5

Extending Case Analyzer Measures using Named Calculation

6/21/2012

IBM

Xue Bo Zhang and Yiwei Song

Abstract: This is the last part of the 5-article series. Other than parent-child relationship and calculated member mentioned in previous parts, this part introduces another important feature of OLAP, named calculation. The article explains how to create a 72-hour completion field by named calculation, and then create new measure based on this field. Finally the article gives a conclusion to the whole series.

About the authors:

Xue Bo Zhang is a software engineer in IBM China Software Development Lab (CSDL). He works on IBM Case Management development team. He has rich experience in Dojo and web 2.0. Reach out to him atzxuebo@cn.ibm.com

Yiwei Song, a member of CDL ECM CoE (Center of Excellence) in China, certified FileNet (4.0) developer. Has 5years 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

Link to the third part of this article:

Part 3 on Evis.ME | Part 3 on developerWorks

Link to the fourth part of this article:

Part 4 on Evis.ME | Part 4 on developerWorks

This part produces the report: monthly 72-hour/7-day/30-day/3-month completion rate of each employee. Conclusion of the series is included at the end of the part.

Requirement analysis

A completion rate is an important statistic showing employee’s productivity. The 72-hour completion means, starting when a task is assigned to an employee, if the task is done within 72 hours, it’s counted one. So the 72-hour completion rate is calculated by: 72-hourCompletedAmount / AllCompletedAmount. The 7-day/30-day/3-month completion rates are similar.

Sure, there are no such measures in CA cubes. You may be thinking of Calculated Member. Unfortunately, none of measures in the CA cube Work Item Processing Time, like Time Usage Minutes, Completed Work Item Count, and Total Wait Time Minutes, can be really used for such calculated members. The reason is, all those measures are final statistics that hide related details. In order to get 72-hour completion amount, we need more “raw” data.

Fact tables contain enough raw data. Below sections will leverage fact tables and create Name Calculations.

Named Calculation in OLAP

A Named Calculation is a SQL expression represented as a calculated column. This expression appears and behaves as a column in the table. A named calculation extends the relational schema of existing tables or views in a data source view without modifying the tables or views in the underlying data source. Named calculations are calculated during processing whereas calculated members are calculated at query time. (http://msdn.microsoft.com/en-us/library/ms174859(v=sql.90).aspx)

Creating Named Calculation 72-hour completion

In BI Studio, open the data source view. Find the fact table F_DMProductivity in the view, right-click and click New Named Calculation.

Figure 1. Create new named calculation in F_DMProductivity table

Specify Column name with CompletedCount_In72hrs, and Expression with the following SQL:

Listing 1. SQL for named calculation CompletedCount_In72hrs

CASE

WHEN ((TotalWaitTimeMinutes + TotalProcTimeMinutes)/60 < 72) AND Completed = 1 THEN

1

ELSE

0

END

Figure 2. Specify fields for new named calculation

After clicked OK, a new Named Calculation is created.

Figure 3. Check the newly created named calculation

Right-click F_DMProductivity table and select Explore Data, check the new logical column:

Figure 4. Explore new column data of F_DMProductivity table

Creating new Measure and Calculated Member based on the Name Calculation in the cube

This section will leverage the newly created Calculated Member CompletedCount_In72hrs in the cubes.

Open the CA cube Work Item Processing Time again in BI Studio. Create New Measure by choosing from context menu.

Figure 5. Create new measure

Specify Usage with Sum, Source column with CompletedCount_In72hrs. Click OK.

Figure 6. Choose usage and source column for the new measure

When saving the change, BI Studio prompts to reprocess, choose Yes.

Figure 7. Save and process the cube

Then preview in Browser tab. As comparison, both Completed Count In72hrs and the original Completed Work Item Count measures are added.

Figure 8. Preview the cube with new measure Completed Count In72hrs

Now create a new Calculated Member based on this measure in the same cube. Name is [72-hour Completion Rate], Format string is “Percent”, and Expression is:

Listing 2. MDX for calculated member 72-hour Completion Rate

IIF( [Measures].[Completed Work Item Count]>0, [Measures].[Completed Count

In72hrs]/[Measures].[Completed Work Item Count], NULL)

Figure 9. Create new calculated member

Save and preview. You may see linjun’s productivity is relatively poor and lilei is playing excellently.

Figure 10. Preview the cube with calculated member 72-hour Completion Rate

The 7-day/30-day/3-month completion rates are similar. We won’t repeat here.

Note: This completion rate solution doesn’t consider the weekends or public holidays, some employees may raise their concerns saying “my 72-hour completion rate is low because many of my tasks come on Friday, I finish them in the next Monday already”. This is out of the scope of the article.

Conclusion

In this article series, we leverage OLAP features drill-up/drill-down, calculated member, and name calculation to customize Case Analyzer’s cubes, so that more reports with more business value can be achievable.

Again, it’s not necessary to worry about customer may do extra operations when creating monthly report, since above customizations won’t break CA’s mechanism. Data of newly created members/measures will change automatically except for the LDAP information import (in part 2).

CA cubes can be either presented by Excel Pivot Table, Cognos BI, or any other OLAP clients. If you buy Cognos BI because of this article please give me some credit (just kidding).

CA’s underlying OLAP technology is open. This article only unleashes a little piece of it. Readers are encouraged to extend and explore more possibilities. Any comments and new ideas are welcome.