Extend Model
Extending the Schema
As your organization's analytical needs mature, business users will inevitably request enhancements to your data model. This evolution is natural and expected—what begins as a simple sales analysis cube will grow to accommodate more sophisticated business questions.
Schema evolution refers to the incremental enhancement of your Mondrian cube definitions to support new analytical requirements without disrupting existing reports and analyses. The key principle is backward compatibility - new features should enhance rather than break existing functionality.
Quick Reference Table
Change the Caption, Description, or Format String of Any Element
What It Does: Modifies how elements appear to users without changing the underlying data structure or query logic.
Why It Matters: Business terminology evolves, and your schema should reflect current language. Additionally, proper formatting ensures data is presented professionally and consistently.
Example - Current State:
Evolution Scenarios:
Scenario A - Update Caption for Clarity:
Business Impact: Users now see "Total Sales Revenue" in Analyzer, which clarifies this represents gross revenue, not profit.
Scenario B - Change Format String for International Currency:
Business Impact: Revenue now displays in Euros with proper negative number formatting in parentheses.
Scenario C - Add Description for Documentation:
Business Impact: Users hovering over the measure in Analyzer see a tooltip explaining exactly what this measure represents.
Add a Hierarchy to a Dimension
What It Does: Provides alternative navigation paths through the same dimension, supporting different analytical perspectives.
Why It Matters: Different business users need to analyze the same dimension in different ways. Multiple hierarchies allow flexibility without creating separate dimensions.
Example - Current State: The Products dimension has one hierarchy: Line → Vendor → Product Name
Evolution: Add a second hierarchy based on product categories:
Business Use Case:
Marketing department analyzes by Category → Subcategory (market-facing classification)
Operations department analyzes by Line → Vendor (supply chain classification)
Both teams use the same cube with different navigation paths
Users select their preferred hierarchy in Analyzer without needing separate data sources
Add a Level to a Hierarchy
What It Does: Increases the granularity or adds intermediate aggregation points in an existing hierarchy.
Why It Matters: As reporting requirements mature, users need more detailed drill-down capabilities or intermediate summary levels.
Example - Current State: The Customers dimension has: Customer Name (single level)
Evolution Scenario A - Add Geographic Levels:
Business Use Case:
Before: Users could only see sales by individual customer (too detailed for executives)
After: Executives can view sales by Country, Regional managers by State/Province, and Sales reps by City, with the ability to drill down to specific customers
Supports geographic market analysis without creating separate dimensions
Evolution Scenario B - Add Customer Segmentation:
Business Use Case:
Segments customers as "Enterprise," "Mid-Market," "Small Business"
Enables analysis: "What's our average deal size by customer segment?"
Sales leadership can compare performance across different market segments
Add a Measure That's the Same Granularity or Dimensionality as Existing Measures
What It Does: Adds new metrics that can be analyzed at the same dimensional level as existing measures.
Why It Matters: Business needs expand to include new KPIs without requiring changes to the dimensional structure.
Example - Current State: Single measure: Sales (sum of TOTALPRICE)
Evolution - Add Complementary Sales Metrics:
Business Use Case: All these measures share the same dimensionality—they can all be analyzed by:
Product Line
Territory
Customer
Time Period
Example Analysis: "Show me Sales, Units Sold, and Average Unit Price by Product Line and Territory"
Sales by Territory and Line: $X
Units Sold by Territory and Line: Y
Average Unit Price: $X / Y
All three metrics align perfectly on the same dimensional axes
Add a Measure of Different Granularity or Dimensionality than Existing Measures
What It Does: Introduces metrics that exist at different dimensional levels than your fact table, requiring special handling for aggregation.
Why It Matters: Some business metrics don't live at the transactional level. Inventory levels, employee counts, and customer credit limits are dimensional attributes, not transaction facts.
Example - Current State: All measures are at ORDER_DETAILS grain (one row per product per order)
Evolution - Add Measures at Different Granularities:
Scenario A - Customer-Level Measure:
Why "max" aggregator?
Credit limit is stored once per customer in the CUSTOMERS dimension table
When aggregating across multiple customers, we want the maximum credit limit in that group
This prevents double-counting when a customer has multiple orders
Business Use Case: "Show me total sales versus available credit by customer segment"
Sales aggregate naturally (sum across all orders)
Credit limits use "max" to show the highest credit limit in each segment without double-counting
Scenario B - Product-Level Measure:
Business Use Case: "What's the average product cost by vendor?"
Each product has one standard cost
"avg" aggregator gives average cost across products in each vendor
Different from total COGS, which sums actual costs from transactions
Create a Calculated Column and Use It in an Attribute or Measure
What It Does: Defines derived columns at the database or ETL level, then exposes them in your schema as dimensions or measures.
Why It Matters: Not all business logic should be in MDX calculations. Some transformations are better performed once during ETL rather than repeatedly during query time.
Database Calculated Column (created in your ETL or database view):
Use Calculated Column in Measure:
Use Calculated Column in Dimension:
Business Use Case: "Show me sales and profit margin by profitability tier and product line"
Profit calculation happens once in the database
Much faster than calculating (Sales - Cost) in MDX for every query
Profitability tier allows users to filter and group by profit performance
Reduces query complexity and improves performance
Performance Benefit:
Before: MDX calculates profit for every cell in every query
After: Database calculates once; Mondrian just aggregates pre-calculated values
Add a Calculated Member to a Cube
What It Does: Defines formulas that combine existing measures to create new metrics, calculated at query time.
Why It Matters: Many business metrics are ratios, percentages, or complex formulas that can't be simply summed or averaged. Calculated members handle this business logic in MDX.
Business Use Cases:
Profit Margin %
"Which product lines have the highest profit margins?"
"How does profit margin vary by customer segment?"
Can't be calculated as average—must be (Total Profit / Total Sales) at each aggregation level
Average Order Value
"What's the average order value by territory?"
"Is our average order size increasing over time?"
Different from summing order values—requires dividing total sales by count of orders
Sales Growth vs Prior Year
"Which territories are growing fastest year-over-year?"
"What's our compound growth rate?"
Uses MDX time intelligence to compare current period to same period last year
Key Difference from Regular Measures:
Regular measures aggregate via SUM, COUNT, AVG, etc.
Calculated members use formulas that operate on aggregated values
Example: Profit Margin must be calculated AFTER aggregating profit and sales, not before
Add a Named Set to a Cube
What It Does: Defines reusable, predefined collections of dimension members that users can apply consistently across analyses.
Why It Matters: Business users repeatedly analyze the same meaningful groups. Named sets provide consistency, improve usability, and encapsulate complex MDX logic.
Example:
Business Use Cases:
Top 10 Products
Users can consistently analyze the same top-performing products
Definition is centralized—if business decides "Top 10" means top 15, change once
Appears as a pre-built filter option in Analyzer
Strategic Territories
Sales leadership wants to focus reports on key markets
Instead of manually selecting territories each time, use the named set
Ensures consistency across reports and dashboards
Current and Prior Year
Every trending report needs this comparison
Named set encapsulates the MDX logic for accessing current and prior periods
Users don't need to understand MDX to create year-over-year comparisons
High Value Customers (Dynamic)
Filter updates automatically based on current data
"dynamic=true" means the set recalculates each query
Different customers may qualify each quarter as spending patterns change
Named Sets vs Calculated Members:
Calculated members create new measures
Named sets create predefined groups of dimension members
Both improve usability by encapsulating complexity
Multiple Cubes in a Schema
As your analytical environment matures, you'll discover that different business groups need different perspectives on your data:
Sales Analysis Cube: Optimized for daily sales reporting (order-level grain)
Customer Analytics Cube: Focused on customer behavior (customer-level grain with lifetime metrics)
Inventory Management Cube: Stock levels and movements (product/warehouse/day grain)
Financial Reporting Cube: Monthly financial metrics (month-level grain with GL accounts)
Key Benefits:
Performance Optimization
Each cube optimized for specific query patterns
Appropriate aggregations for each use case
Smaller, focused cubes query faster than one massive cube
Security and Governance
Grant sales team access to Sales Analysis cube only
Finance team accesses Financial Reporting cube
Prevent cross-contamination of sensitive data
Different Granularity
Transaction cubes: Order line detail
Summary cubes: Daily or monthly snapshots
Specialized cubes: Product profitability, customer lifetime value
Simplified User Experience
Users see only relevant dimensions and measures
Less overwhelming than one cube with 50 dimensions
Tailored to specific analytical workflows
Example - Multiple Cubes in MiniatureModels Schema:
User Assignment:
Sales reps → Sales Cube
Customer success team → Customer Analytics Cube
Product managers → Product Performance Cube
Executives → All cubes (for comprehensive dashboards)
Last updated
Was this helpful?