OLAP for Enterprise Analytics: SSAS, Mondrian, and How to Choose

What problems does OLAP solve on top of a DWH?

Even when a DWH is already built, reports often run slowly. The reason is usually simple: analysts and BI tools run heavy GROUP BYs and recalculations on the fly, repeatedly executing similar queries over large tables. The more users and the wider the cuts (time, regions, products, departments), the more noticeable the slowdown.

A second business pain is inconsistent numbers across reports. One report counts margin by shipment, another by payment, a third excludes returns, while a fourth does not. If there is no single place where calculation rules are fixed, disputes over figures consume time and undermine trust in analytics.

A third problem is the long cycle for adding metrics. In a pure DWH, a new metric often requires changing marts, rewriting queries, checking dozens of dashboards and coordinating logic between teams. So a request like "add a KPI by Friday" can easily turn into several weeks of work.

In enterprise analytics, OLAP is usually expected to be the layer that addresses these issues through precomputed calculations and a single model. It is especially useful when you need to speed up repeated totals with pre-aggregations, fix the "single source of truth" for KPIs and reference data, give the business clear dimensions instead of raw tables, reduce dependence on complex SQL, and safely scale access when many users appear.

A simple example: finance and procurement look at expenses by cost center and account. The DWH has the data, but each report interprets "actual" and "budget" differently, and the detail down to contract level makes queries heavy. An OLAP model fixes the rules and provides fast totals, while details remain available when needed.

OLAP in simple terms: cube, dimensions and pre-aggregations

You can think of a DWH (data warehouse) as a large, tidy library: it stores detailed records, history of changes and many tables. A data mart is a "shelf" for a specific task, for example only sales or only procurement. An OLAP cube is a ready-made "organizer" for analysis where data is arranged in advance to answer typical business questions quickly.

Most often OLAP is used in enterprise analytics as a convenient layer between the warehouse and reports. It speeds up calculations and standardizes rules.

Dimensions, facts and hierarchies

A cube contains facts and dimensions. Facts are the numbers being counted: revenue, quantity, cost, margin. Dimensions are the ways to slice those numbers: time, product, customer, region, department.

Dimensions often include hierarchies. For example:

• Time: year - quarter - month - day.
• Region: country - state/region - city.

Hierarchies matter because users can quickly drill into details or view an overall total without manual recalculations.

Why pre-aggregations speed up responses

The core idea of OLAP is pre-aggregations. A cube stores totals in advance at popular levels (for example, sales by month and region). Then the report doesn't need to recalc millions of rows from the DWH each time.

This has the most effect when queries are repeated often: the same slices are used in many reports, periods are compared regularly (month-over-month, year-over-year), many users open similar reports simultaneously, and calculations are complex enough that errors and divergent implementations easily occur.

Beyond speed, a cube often becomes the BI semantic layer: users see clear business terms and consistent formulas (for example, exactly what counts as "revenue" and how "margin" is computed) instead of arguing about different SQL variants in every report.

When OLAP truly wins on speed

OLAP provides noticeable improvement when users constantly pivot the same slices and need answers within seconds. In a pure DWH even well-designed marts often hit heavy groupings, joins and re-computation of the same metrics on every query.

Speedup usually relies on two things: pre-aggregations and caching. A cube stores (or quickly serves) precomputed sums and counts for popular combinations of dimensions. Therefore queries like "show quarterly sales by region and category" often run faster than SQL over marts, especially when there are many dimensions.

OLAP typically wins on speed if:

• totals are frequently viewed by time, region, product, customer and their combinations;
• there are many repeated calculations (margin, plan vs actual, share, YoY) that are better computed once and reused;
• high user concurrency: tens or hundreds of simultaneous reports, dashboards and Excel queries;
• users need to drill from year to month and below quickly without long re-queries;
• response speed matters more than absolute real-time accuracy of the last transaction.

A good rule of thumb: if the business asks questions at the level of periods (day-week-month), categories and segments, OLAP answers them faster and more reliably. If the question regularly requires seeing every posting or receipt in real time, the gain will be smaller. Cubes still need updating, and some details are more convenient to keep in the DWH and marts.

A practical example: a financial controller compares actuals and budget by departments daily while managers open the same slices in reports. If aggregates and calculation rules are prepared in OLAP, reports open predictably fast even at peak times.

When OLAP brings governance and unified calculation rules

OLAP's strength is not only speed. It converts scattered SQL queries and formulas into a single analytics language where everyone calculates metrics the same way. For enterprise analytics this is often more important than saving a few seconds per query.

When metrics live inside reports, chaos appears: one dashboard shows "revenue" with VAT, another without; "margin" sometimes by shipment, sometimes by payment; plan vs actual computed differently. In OLAP these rules are fixed in measures and calculations of the cube. Change a formula once and it applies the same everywhere.

Controlling reference data and hierarchies

OLAP helps keep dimension structures under control: organization, products, regions, channels, budget items. This reduces manual fixes when users rework groupings in Excel or BI and then argue about which version is correct.

Usually teams agree in advance on rules: who can change hierarchies (for example, the org chart or product taxonomy) and how that is coordinated; which attributes and codes are the "canonical" ones to avoid duplicates; how the calendar is defined (financial year, quarters, closed months); how plan and scenario versions are handled (plan, actual, forecast).

Access and less dependence on the "query author"

Another advantage is role-based security and dimension-level restrictions. You can restrict not the whole report but specific slices: a branch sees only its region, finance sees full P&L, procurement sees only its categories.

This reduces reliance on individual SQL developers. Business users ask for "rewrite this query urgently" less often because most logic is already packaged in the cube and reused. For example, when a large organization changes its department structure, updating the hierarchy in OLAP is often enough instead of editing dozens of reports.

When you can do without OLAP and stay on the DWH

OLAP is not always required. If reports and dashboards are reliably served by marts, a sound model and a well-tuned database, an extra layer can add more governance than value.

The first sign OLAP is unnecessary: the main load fits into a few typical queries that can be sped up with familiar methods—star schema, partitioning, indexes, materialized views and precomputed aggregates in marts.

OLAP is often unsuitable when near real-time access to detailed transactions is more important. Cubes favor pre-aggregations and scheduled processing. For "show the last 5 minutes for every receipt/payment" tasks it is usually better to stay on the DWH (or use a separate operational analytics layer) and work with details.

A DWH is usually enough if there are few users, queries are infrequent, metrics are simple and change rarely. OLAP is also not always appropriate when analytics looks more like data science: features, experiments, notebooks, pipelines. There flexibility and access to raw data matter more than multidimensional navigation.

Sometimes it makes sense to keep semantics in the BI tool: reference data, measures, rounding rules, row-level access. This works if BI is used disciplinarily as a single layer, not as a set of disjoint files.

Example: finance checks plan vs actual weekly, procurement runs a few controls on contracts. If this is 10–20 people and queries are predictable, marts and DWH optimization often deliver the same result without a separate OLAP.

SSAS, Mondrian and alternatives: how to choose

Choosing an OLAP solution usually comes down not to "who is faster" but to how you want to manage models, calculations and access, and how it should fit your stack.

SSAS is often chosen when there is a Microsoft ecosystem and predictable rules are required. It offers two approaches:

• Multidimensional is convenient for classic cubes: many dimensions, hierarchies, complex business calculations and strict governance.
• Tabular is chosen when a simpler model, faster iterations and a familiar BI measurement language are more important.

In practice, Tabular is usually easier for small teams to maintain, while Multidimensional is stronger for rich hierarchy navigation scenarios.

Mondrian is chosen for its open-source approach and ability to integrate into a Java stack closer to the DWH. It fits when vendor independence matters and you have the skills to operate it. Limitations usually show up at large volumes, in the feature set compared to commercial engines, and because some tasks must be solved engineerially rather than "out of the box."

For fast answers on large data streams, teams often look at engines like Apache Kylin or Druid. They are used as analytic layers for pre-aggregations and fast slices but require careful architecture and data quality discipline.

Another path is cloud semantic models and BI-models as replacements for classic cubes. This is convenient when you want less infrastructure and faster report delivery, but you must verify calculation control, versioning and security up front.

Before choosing, it's useful to run a short compatibility check: which BI is already used and what connectors are supported natively; where data lives (DWH, lakehouse, on-prem, cloud); whether unified calculations are needed across all reports and who will change formulas; access requirements (row-level, roles, audit); who will support the solution — your team or an integrator.

For many organizations in the region, including the public sector, it's critical that the solution fits security and procurement requirements. So OLAP is chosen not by trend, but by where there's less risk in scaling and change.

Criteria for choosing an OLAP solution for your company

Choosing OLAP rarely comes down to "which cube is best." It's far more important to understand how people actually ask questions of the data. Maximum benefit appears when typical queries repeat, stable calculations are needed and getting a fast answer without constant mart rewrites matters.

First, assess data volume and query profile. If users frequently ask totals by period, department and product and want to drill into a limited level of detail, pre-aggregations will give a significant boost. If most reports rely on raw detail (order line, event, receipt) and nonstandard filters, the effect will be smaller. Then you must decide what to store in the cube and what to leave in the DWH.

Next — updates. A nightly batch is fine for financial reporting and management totals. Incremental loads and hybrid schemes are needed when metrics change during the day and freshness matters. The more frequent the update, the higher the data discipline and monitoring requirements.

Third — where metrics live. Simple sums and counters are convenient to fix in ETL, while business rules (for example, "margin accounting for returns" or "plan vs actual using the exchange rate on the recognition date") are better placed where they are easier to control and apply uniformly across reports. Agree in advance what is computed once and reused and what is OK to compute in BI on the fly.

When choosing, also check role-based security and access audit, total cost of ownership (licenses, team skills, maintenance complexity), compatibility with your BI ecosystem and cybersecurity requirements.

A practical guide: if you have many departments and strict access rules, pick a solution where roles and calculations are easy to verify and document.

Example scenario: finance and procurement in a large organization

Imagine a large organization with a network of branches: centralized budget, plan vs actual by accounts, procurement through tenders and constant questions about "why numbers don't match." Finance looks at budget by cost center, procurement by contracts and suppliers, managers by branches and projects.

The DWH contains data, but reports compute differently. One report takes "paid" from payments, another from postings, a third from closed acceptance acts. Plus heavy queries: to build an annual plan vs actual by "branch x account x supplier," BI repeatedly scans large tables and recalculates, and a report can take minutes.

After implementing an OLAP cube and a common metrics layer the routine changes. The cube defines dimensions (branch, cost center, account, supplier, contract, month) and unified measures (plan, actual, commitments, savings, overdue). These measures are calculated by the same rules and used consistently across reports. Users pick slices and get fast answers because aggregates are prepared in advance and navigation follows the cube structure.

Example: the CFO asks "why did expenses rise in the Southern branch in November?" Previously an analyst pulled extracts and manually reconciled formulas. With a cube they quickly drill from the overall variance to accounts, then to contracts and suppliers, and see the spike came from a one-off equipment purchase and payment timing.

There are trade-offs and it's better to accept them up front. Usually this means update lag (the cube is rebuilt nightly or every two hours), limited detail (not all fields/events are moved into the cube) and that one-off calculations are sometimes more convenient in the DWH or a mart.

Measure the effect with data: average load time of key reports (before/after), how often formulas and metric definitions changed per month, how much the number of reconciliation disputes between departments decreased, and what share of reports use the same metric set.

Technically, the cube is often placed close to the DWH on corporate infrastructure (for example, on-prem servers in a data center) so data does not leave the perimeter and it's easier to maintain unified access rules.

How to implement OLAP on top of a DWH: a step-by-step plan

Start with questions, not a cube. If the business asks for "analytics," clarify which decisions people make daily and which figures must match across reports.

Draft a short list of metrics and calculation rules: revenue, margin, plan vs actual, overdue, turnover. Agree on default filters (currency, VAT, document status, recognition date) and fix definitions to avoid later disputes.

Next, prepare the DWH and marts for OLAP. This usually means aligning reference data (counterparties, products, departments), removing duplicates, stabilizing keys and unifying the calendar. If dimensions are unstable in the DWH, OLAP will simply reveal errors faster.

Then choose the data model, often a star schema: facts in the center, dimensions around. Design hierarchies people actually use (for example, Organization - Department - Unit or Product - Category - Brand). Don’t add dozens of levels "for the future": start with 2–3 most needed levels.

After that, tune performance: pre-aggregations, time partitioning, update schedule and cache behavior. Check that nightly loads fit the window and define behavior for partial rebuilds (for example, when a month is closed).

Connect BI and finalize security: roles, branch-level access, masking sensitive metrics. Add correctness checks: compare with marts, totals by periods, anomaly alerts.

Finish with a pilot in one process (for example, finance or procurement), collect feedback on speed and usability, then expand the cube by dimensions and metrics without rewriting the core.

Common mistakes when implementing OLAP

The most common reason for failure is starting to build a cube when the company has no single answer to a basic question: what exactly does each metric mean. If "revenue" in finance is counted by shipment while sales use payment, OLAP won't magically fix the mismatch. First fix definitions, formulas, filters and exceptions, then map them into the model.

A second mistake is bringing all detail into the cube "just in case." The model bloats, build and update slow down, and users get lost in dimensions. It's more practical to keep in the cube what is needed for analytics and reporting, and leave deep transactions in the DWH to be queried on demand.

Updating is often underestimated. A cube can be fast but useless if the load schedule is not well thought out: when pre-aggregations are rebuilt, how late-arriving documents are processed, how corrections in sources propagate to marts. For enterprise analytics this is critical: a morning report should be "today's" not "as available."

Reference data is another pain point. If the DWH lacks stable keys and rules for counterparties, departments, or products, OLAP will multiply duplicates and discrepancies. A typical symptom: the same supplier is created differently and amounts split across two records.

Don't postpone security. Row-level rights, dimension restrictions, and access to sensitive data (salaries, prices, public procurement) should be designed from the start, otherwise reworking the model and logic later will be costly.

Finally, ensure ownership and support. OLAP needs an owner: who approves changes, who monitors data quality, who reacts to incidents. Without this, even a good model quickly becomes outdated.

Quick checklist and next steps

Before a pilot it's useful to run a quick readiness check.

• A list of priority reports and clear SLAs: what must open within 5 seconds and what can be 30 seconds.
• Agreed definitions for 10–20 key metrics (revenue, margin, plan vs actual, receivables), including exceptions and rounding rules.
• Agreed update frequency and acceptable data delay: daily, hourly, near-line.
• Assigned roles: data owner (responsible for quality) and model owner (responsible for calculations and structure).
• Agreed process for changes: new reference data, history recalculation, new slices.

If 1–2 items are not answered yet, that's normal. Just record the risks: OLAP won't solve metric disputes and won't replace a data owner.

The easiest path is a pilot in a single domain where many repetitive queries exist by period, department and accounts (often finance or procurement). Choose 3–5 key reports, create reference calculations, build a minimal semantic layer, test speed with real filters, set up updates and a clear process for metric changes.

If pilot performance and infrastructure are critical, work with a systems integrator. For example, GSE.kz provides system integration, supplies servers and workstations they manufacture, and offers 24/7 support—useful for analytics systems with high load and strict availability requirements.