It Is Time to Think INSIDE the Box

By Franco Ferreyra, Data Engineer

In Analysis Services, a cube is a type of multidimensional data model used for online analytical processing (OLAP). It is a collection of related measures and dimensions organized into a multidimensional structure, allowing users to analyze and summarize large amounts of data from various perspectives.

A cube typically consists of a fact table, which contains the data to be analyzed, as well as one or more dimensions tables, which provide context and additional information about the data.

The fact table and dimension tables are related through keys, which are used to link the data in the fact table to the appropriate dimensions.

Once a cube is created, users can query it using OLAP tools such as Excel PivotTables or SQL Server Management Studio. From there, it is then possible to drill down into the data and slice it into different dimensions. This enables users to quickly gain insights and make data-driven decisions based on the analysis of large datasets.

Sounds both great and daunting, right?

Don’t fret: OZ is here to help you make the most of this powerful process.

Basic Components of a Cube

The components of a cube can vary depending on the type of cube and the specific implementation. In general, however, there are several key components that are common to most cubes.

Here are some of the main components of a cube:

• Dimensions are the categories by which you want to analyze the data in the cube—from time and geography to product and customer. Dimensions define the structure of the cube and provide the context for the measures.
• Measures are the numerical values that you want to analyze in the cube, such as sales, revenue, or profit. Measures are typically aggregated or summarized over the dimensions.
• Aggregations are pre-calculated values that summarize the measures at different levels of the dimensions. Aggregations can improve query performance by reducing the amount of data that needs to be scanned during a query.
• Hierarchies are the relationships between the levels of a dimension. For example, a time dimension might have a hierarchy that includes year, quarter, month, and day levels. Hierarchies enable users to drill down or roll up the data along the levels of the dimensions.

Cubes can also include other components.

For example:

• KPIs are measures that provide a snapshot of performance against a target or goal.
• Perspectives are views of the cube that provide a subset of dimensions and measures for a specific user or audience.
• Calculated members are custom measures that are derived from other measures and can be used to perform advanced calculations.

The Importance of Partitions

Partitions help to improve query performance and manage large volumes of data.

Partitions in a cube are logical subsets of the cube’s data physically stored in separate objects such as database tables or files.

Partitioning enables you to divide the data in a cube into smaller subsets, which can help to improve query performance and manage large volumes of data.

By dividing the cube’s data into smaller subsets, queries can be processed more quickly and efficiently.

Why?

Because the query engine only needs to scan the relevant partition instead of the entire cube. This is particularly useful when dealing with large data sets, as it empowers you to process only the partitions relevant to a given query.

Partitions can be based on different criteria—time periods, geographical regions, or product categories, for example. You could create partitions for each year or quarter of sales data or for different product lines, tailoring the partitions to the specific needs of your analysis.

By enabling the processing of only what has changed or needs updating independent of the rest of the cube, partitions substantially increase efficiency.

Further, partitions can be compressed using techniques such as data or columnstore compression, which helps to reduce storage requirements and minimize the amount of storage required for the cube’s data.

Remember: Partitions can be merged or split. As the data in the cube changes, you may need to adjust the partitions to optimize query performance. This can involve merging smaller partitions into larger ones or splitting larger partitions into smaller ones.

Types of Cubes

In Analysis Services, there are two main types of cubes:

• Multidimensional Cubes. Also known as OLAP cubes, these are the traditional type of cubes used in Analysis Services since its inception, storing data in a multidimensional array in which each dimension represents a different aspect of the data, such as time, geography, or product category. The cube can be queried using MDX (Multidimensional Expressions) language specifically designed for OLAP analysis.
• Tabular Cubes. Designed to provide a simpler and more intuitive way of working with data, tabular cubes are a more recent addition to Analysis Services. Tabular cubes store data in a relational format similar to a traditional database and can be queried using DAX language, which is similar to Excel formulas. Tabular cubes are often used for smaller datasets or for scenarios where the data is better suited to a relational format.

Both types of cubes have their strengths and weaknesses.

The choice between them will depend on the specific needs of the organization and the data being analyzed.

Multidimensional cubes, for example, provide advanced features such as calculated members, named sets, and support for complex business logic—which make them ideal for large and complex datasets. Multidimensional cubes can also handle complex relationships between dimensions, such as many-to-many relationships.

However, multidimensional cubes can be complex to design and maintain, and they may require more hardware resources to run efficiently.

Tabular cubes store, on the other hand, are well-suited for smaller datasets or scenarios where the data is better suited to a relational format. Tabular cubes are easier to design and maintain than multidimensional cubes and offer faster query performance for simple queries. They also provide support for real-time data and can be integrated easily with other tools such as Power BI.

Keep in mind, though, that tabular cubes may struggle with large and complex datasets and do not always offer some of the advanced features available in multidimensional cubes.

Which Cube is Best for You?

The decision to use a tabular or multidimensional cube depends on your data modeling requirements and the analysis needs of your business.

A tabular cube is best suited for modeling data that has a simple structure and can be represented as a two-dimensional table, such as sales data or financial data. Tabular models use a columnar storage format and rely on relationships between tables to build hierarchies and support complex calculations. They are typically easier to create, manage, and maintain than multidimensional models, and provide fast query response times.

A multidimensional cube is better suited for modeling complex data structures that have multiple dimensions and hierarchies, such as inventory management or product analysis. Multidimensional models use a cell-based storage format and support more advanced calculations, such as calculations across multiple dimensions, time-based calculations, and custom aggregation functions. They also provide more flexibility in terms of defining and manipulating hierarchies and dimensions.

In short, if your data has a simple structure and can be represented as a two-dimensional table, a tabular cube is a good choice.

If your data has complex relationships and hierarchies, or you need more advanced calculations, a multidimensional cube may be a better option.

Cube Advantages

Let’s dig a little deeper into the upside of employing cubes.

1. Cubes can help you avoid resource contention with the source system.
2. They are an ideal tool for numeric analysis.
3. Cubes enable the discovery of data patterns in SSAS that may not be immediately apparent using the data mining features built into the product.
4. They offer a unified and integrated view of all your business data reporting and analysis of KPI scorecards.
5. Users can analyze data with a host of tools, including SSRS and Excel.
6. Cubes provide enterprise-grade semantic data models for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.

Also, when you build a cube in Analysis Services, you define measures and dimensions. Measures are the quantitative values in the database that you want to analyze—e.g., Mean Time Between Failures and Total Costs.

Final Thoughts

Despite their relative complexity and sometimes resource-intensive nature, cubes are a fundamental component of business intelligence providing a way to organize data into a multidimensional structure that facilitates data analysis from different perspectives and allows for complex calculations to be performed quickly.

Are you ready to make the most of your data?

The Data Analytics experts at OZ will put more than a quarter century of experience to work as we help you establish strong, responsible practices that set the stage for growth, and catalyze next-level digital transformation while turning disjointed data into lucrative opportunities.

Check out our new e-Book digging deep into 2023’s most impactful leading-edge Data Analytics trends or simply reach out today to begin.