Organizations rely on data to drive decisions, but the challenge lies in how that data is structured. Transactional systems are optimized for speed and reliability, not for analytics. This creates a fundamental disconnect: Raw operational data is too complex and slow for reporting, while traditional dimensional modeling approaches, like Kimball’s Star Schema, require extensive ETL and rigid data transformations. Let’s look at a practical example:
In Online Transaction Processing (OLTP) systems, every order, every support call, every interaction, needs to be quickly and efficiently captured and stored. Think of an ATM withdrawal. Instantly, when you withdraw cash from the ATM, that transaction is updated in all connected financial systems, your updated balance, the amount withdrawn, any ATM fees, and the transaction’s timestamp.
In Online Analytics Processing (OLAP) systems, data is analyzed in aggregate to identify trends and spot outliers to make more informed business decisions. Revisiting our ATM example, which ATMs get used the most, when they are used, do any of them run out of funds, and which ones generate higher fees.
In today’s cloud-native data and analytics ecosystem, data teams need a more flexible approach. Cube Cloud’s universal semantic layer is next-generation OLAP, bridging the gap and delivering the benefits of dimensional modeling without depending on legacy OLAP platforms.
Handling Transactional Data
Transactional data is stored in Online Transaction Processing systems (OLTP) systems.
OLTP database architecture prioritizes data write operations. It’s optimized for write-heavy workloads and can update high-frequency, high-volume transactional data without compromising data integrity.
OLTP systems use a Third Normal Form (3NF) data schema. This schema is very efficient for capturing data, but the retrieval of data from these schemas, for analytical use, is very difficult. Imagine trying to write a query against the schema above. And it’s not just complexity that hinders analysis of OLTP data.
When trying to report directly against OLTP systems you run into several problems:
- Query performance is very poor
- There is extreme potential for reporting activities to affect the source application’s performance
- A single complex query can shut down an ERP system by consuming all the database server’s hardware resources
The first solution was to replicate the OLTP data. That made sure your queries didn’t crash the operational system, but didn’t really help with the other issues.
Supporting Analytical Requirements
To support the analytical requirements of data, the data is transformed into a schema more conducive to data retrieval with Online Analytics Processing (OLAP). OLAP database architecture prioritizes data read over data write operations. You can quickly and efficiently perform complex queries on large volumes of data. This introduces a very important concept: Your data architecture choices directly affect the user experience.
Ralph Kimball, often called “The Father of the Dimension Model,” introduced the concept of dimensional modeling, as a recommended way of transforming 3NF, OLTP data schemas, into consumable dimensional schemas for OLAP.
This transformation process requires an ETL (Extract Transform and Load) data pipeline process to move the data from the source OLTP schema, transform it and load it into the desired dimensional model. This process is often referred to as denormalization (referencing the ‘normal’ from 3NF) or flattening. The model Kimball suggested is called a Star Schema:
The complexity of the 3NF schema is converted into central fact tables and surrounding dimension tables. Just looking at the star, you can see how much easier it would be to query this schema for an analytical use case. The dimensions are conformed, meaning they will work with all of your fact tables.
To manage and plan the design of these OLAP dimensional models Kimball created a Bus Matrix to standardize development:
Bus Matrix – Kimball’s Modeling Standard
The common belief is that the Kimball approach provides faster ROI, as data warehouses can be built incrementally, one Business Process at a time (the left axis). The conformed dimensions (the top axis) would be built as needed and would join to the business process (Fact), where appropriate. Some of his key concepts include:
- Dimensionally Modeled DW
- Conformed Dimensions
- Data Warehouse by Business Process Iteration
- Star Schema
- Dimensional Denormalization (Flattening)
- Surrogate Keys
- Easy Drill-Across (by Business Process)
What is the right choice for your organization?
Before launching a dimensional modeling effort, your team needs to assess business needs, as well as the realities of the underlying source data. Not all use cases are the same. The four key decisions made during the design of a Kimball dimensional model include:
- Select the business process.
- Declare the grain.
- Identify the dimensions.
- Identify the facts.
Declaring the grain is the pivotal step in a dimensional design. The grain establishes exactly what a single fact table row represents. The grain declaration becomes a binding contract on the design. The grain must be declared before choosing dimensions or facts because every candidate dimension or fact must be consistent with the grain. This consistency enforces a uniformity on all dimensional designs that is critical to BI application performance and ease of use.
Atomic grain refers to the lowest level at which data is captured by a given business process. We strongly encourage you to start by focusing on atomic-grained data because it withstands the assault of unpredictable user queries; rolled-up summary grains are important for performance tuning, but they pre-suppose the business’s common questions. Each proposed fact table grain results in a separate physical table; different grains must not be mixed in the same fact table. This data model approach provides several OLAP advantages.
- Extensibility: Dimensional models are resilient when data relationships change. Facts, dimensions, attributes and grain can be changed without altering any existing BI query or application, and without any change in query results.
- Ease of use: The simplicity of the schema makes it easier to query and involves fewer joins. Making it easier for developers and analysts to consume data from this model.
- Performance: The reduction of joins, and the use of surrogate keys, means this model provides much better query performance than an OLTP schema.
- Shorter development timelines: Because you can build incrementally, one business process (Finance, HR, Marketing, etc.), Kimball models are focused and can be built faster.
Why doesn’t everyone use star schemas?
Many organizations have taken other approaches to modeling their data. The benefits of a proper star schema model are likely worth the effort, but those benefits must be balanced against the cost and time it takes to build them. There are several drawbacks to using star schemas:
- We are duplicating data. OLTP systems eschew duplicating data, they are designed for quick and efficient capture and storage. The Kimball approach does require creating copies of your data, in order to build those useful star schemas.
- This process of transforming the data into a star, the ETL mentioned earlier, takes time, and technology. To build a proper star for just one business process could take weeks, and does require constant processing to keep loading the new records.
- Building a proper star schema takes some expertise. Improperly built Kimball models can lose some of the advantages mentioned above, and could produce incorrect results.
How can Cube’s universal semantic layer help?
This is where a semantic layer can facilitate the shorter term requirements…”I need data now so I can run the business”, versus the longer term worry of… “If I keep taking short cuts with my data model, eventually it is going to start breaking down and produce inaccurate results or intolerable query performance. Then I am going to have to throw it away and start all over’.
Kimball star schemas provide an excellent base for Cube Cloud’s universal semantic layer. In addition, Cube can also sit on top of 3NF, or other non-OLAP data schemas. Cube logically maps the source data and presents the end use with an easy-to-understand interface to the data. While this mapping exercise may take some time, we can provide useful access to data much more quickly because we don’t actually transform and physically move the data via an ETL process . Creating a logical star schema.
Which means we can meet the short term need and deliver quick access to data so we can analyze the business. We can also leverage Cube’s pre-aggregation capabilities to address performance and scalability.
If your strategy does mandate a data transformation process, like Kimball, Cube can ease the transition over time. Giving short term access with a logical model, and then as your data team rolls out proper Kimball stars, you simply repoint the Cube semantic layer to the new star. No downstream analysts are negatively affected and the organization is on a sustainable long term data roadmap/strategy.
The Future of Dimensional Modeling with Cube
Dimensional modeling has long been the foundation of analytical data architectures, but traditional approaches come with trade-offs—slow ETL pipelines, rigid transformations, and high maintenance costs. In today’s cloud-native, AI-driven world, businesses need agility. Cube’s universal semantic layer is next-generation OLAP, providing a modern alternative that enables organizations to map data logically, accelerate queries with pre-aggregations, and unify data access across OLTP and OLAP sources.
Whether your team is transitioning from legacy OLAP systems, optimizing a Kimball-style data warehouse, or working with real-time transactional data, Cube ensures a seamless and scalable analytics experience—without requiring extensive data movement or transformation. Instead of forcing your organization into a rigid model, Cube empowers you to deliver governed, high-performance data access—now and in the future.
Ready to modernize your approach to dimensional modeling? Contact sales to learn how Cube can help your team unify, govern, and optimize your data today.
