I remember when I first started learning about OLAP modeling and modeling concepts. At the time, I knew the terms dimensions and measures from creating pivot tables. However, I had no idea of what their purposes were. It was difficult and often times very confusing for me when learning about OLAP. So in this blog, I’ll go over some of the basics concepts of OLAP modeling and why we use it.
For more on OLAP modeling, watch this video.
First, let’s go over basic concepts and terminology:
Remember, this is the beginner’s guide. As you progress and learn more about OLAP modeling, you’ll be exposed to more complex concepts and terminology. But for now, this is a good starting point.
A dimension can be thought of as a list or category of related items that define a certain aspect of a business. For example, if the business is an automotive manufacturer, dimensions might be store locations, internal departments, or vehicle models. Another way to think of a dimension is to think of a pivot table. Dimensions are similar to the rows or columns that make up a pivot table.
Dimensions are also what makes up a cube.
A cube is a multi-dimensional data set. You can use cubes to hold data, collect data from users, and calculate data as needed. Generally a cube stores data relating to one aspect of the business, such as employees or revenue.
You can think of a cube as a multi-dimensional spreadsheet. Below is a two-dimensional spreadsheet. It looks pretty familiar. It’s analyzing sales by the vehicle model dimension by the time dimension.
Now if you create duplicate spreadsheets, with the same data structure, and for each location, you’ll have a three-dimensional cube.
But hold on! In that example, if vehicle model and time are dimensions, what are sales?
Sales in the previous example is a measure. Measures are measurable properties such as sales, costs, or duration. Measures give you information about the quantities that you’re interested in finding out. If your report is analyzing spending across different offices by the quarters they spend in, your measure might be costs.
If a dimension can be considered as a category of related items, hierarchies can be considered as subcategories. Hierarchies have multiple levels and allow you to “drill down” or “drill up” on your data. Drilling allows you to analyze your data at different levels of granularity. For example, a dimension of time might have a hierarchy of years, quarters, months, weeks, and days.
Who builds them?
If you’ve never worked with OLAP cubes before, it might be difficult to start if you don’t already have a pre-built cube. To perform an analysis using a cube, the cube requires data. If you’re just starting on your own, it can be hard to find enough data for a meaningful analysis. In many companies, cubes are built by a cube modeler. Someone who takes unstructured data and organizes them into dimensions with hierarchies and structures them into a cube.
Why would I use cubes?
Depending on your needs, you might not. Cubes allow you to analyze data through multiple dimensions. Not only that, it excels in processing very large data sets. This type of analysis is simply not possible in a standard spreadsheet. You might find yourself making data discoveries using cubes that you might not have otherwise.