Calculations

Measures ..

Workshop - Calculations

While base measures like Sales and Quantity provide essential metrics, business users often need derived calculations that combine, transform, or aggregate these measures in specific ways. Calculated members in Mondrian schemas enable you to define these business-critical metrics once in the semantic layer - such as average unit prices, profit margins, year-over-year growth rates, or top-performing customer segments - ensuring consistent calculations across all reports and analyses rather than requiring each report developer to recreate the same logic repeatedly with potential for errors and inconsistencies.

In this hands-on workshop, you'll enhance both the Classic Models and Miniature Models schemas by adding calculated members that demonstrate two fundamental calculation patterns. First, you'll create a simple derived measure (UnitPrice) that divides Sales by Quantity to calculate average selling price - a straightforward mathematical operation between two base measures. Then you'll build a more sophisticated calculation (Top 10 Customers) that uses MDX functions to identify and aggregate the highest-performing customers - demonstrating how calculated members can incorporate filtering, ranking, and aggregation logic that would be complex or impossible to achieve with simple SQL queries.

What You'll Accomplish:

• Add a UnitPrice calculated member to the Classic Models schema using division operators

• Configure calculated member properties including dimension assignment, visibility, and format strings

• Understand the formula/formulaElement syntax for defining MDX expressions

• Create an Analyzer report that validates your calculated member displays correctly

• Build a Top 10 Customers calculated member using advanced MDX functions (Aggregate, TopCount)

• Apply set-based operations to filter and rank dimensional members

• Publish enhanced schemas and test calculated members in production reports

• Understand when to use calculated members versus calculated measures created in reporting tools

By the end of this workshop, you'll understand how calculated members centralize business logic within your semantic layer, ensuring that critical metrics like unit pricing and top customer rankings are consistently defined and always available to report builders. Rather than depending on individual report developers to correctly implement complex calculations - with inevitable variations and errors - you'll embed these calculations directly in your Mondrian schema where they're validated once, maintained centrally, and automatically available in every Analyzer report. This approach transforms your OLAP cubes from simple data aggregators into intelligent analytical platforms that encode business knowledge and deliver pre-validated metrics that business users can trust and leverage with confidence.

Prerequisites: Completion of Classic Models and Miniature Models workshops; Schema Workbench and Pentaho Server installed and configured; Understanding of base measures and MDX expression concepts; Familiarity with Pentaho Analyzer

Estimated Time: 30 minutes

Calculated Measure

---

1. Start Schema Workbench:

Windows (PowerShell):

cd \
cd Pentaho/design-tools/schema-workbench/
./workbench.bat

Linux:

cd
cd Pentaho/design-tools/schema-workbench/
./workbench.sh

1. Ensure Pentaho Server is running:

Ensure that the Pentaho Server is up and running (automatically started in Pentaho Lab):

cd
cd /opt/pentaho/server/pentaho-server
sudo ./start-pentaho.sh

1. CM - Unit Price

Calculated Member - Unit Price

Calculated measures are derived metrics defined within the OLAP semantic layer that combine, transform, or aggregate base measures to create business-critical analytics. Unlike base measures that directly represent data stored in source tables, calculated measures use MDX (Multidimensional Expressions) formulas to perform operations ranging from simple arithmetic - such as dividing Sales by Quantity to determine average unit price - to sophisticated analytical functions like identifying top-performing customer segments through ranking and aggregation.

By defining these calculations once within the Mondrian schema rather than recreating them in individual reports, organizations ensure consistent metric definitions across all analyses, eliminate calculation errors from redundant implementations, and embed business logic directly into the data model where it can be centrally maintained and automatically available to all report developers and end users.

Calculated Member

---

1. In the left pane, right-click ClassicModelsOrders Cube, and click Add Calculated Member.

2. To create the calculated member, type or choose:

Attribute	Value
name	UnitPrice
dimension	Measures
visible	selected
formula | formulaElement	[Measures].[Sales] / [Measures].[Quantity]
formatString	$#,###.00

1. Save and Publish the Schema.

---

Analyzer Report

1. From the User Console Home Perspective, click Create New > Analysis Report.

2. In the Select Data Source dialog, click Classic Models: ClassicModelsOrders.

3. Drag Sales to the Measure drop zone.

4. Drag Quantity to the Measure drop zone below Sales.

5. Drag Customer to the Rows drop zone.

6. Save the report in the Training folder as Calculated Member report.

7. Close the Calculated Member report.

2. CM - Top 10 Customers