Database Reference
In-Depth Information
Figure 9-4.
An illustration of the concept of a cube. (If you have four dimensions and two measures, you
will have eight categories of measured values, one for each combination.)
The organization of data within an SSAS multidimensional cube is not the same as a three-dimensional cube
known in geometry.
OLAP Cubes vs. Reporting Tables
SSAS cubes can also be referred to as online analytical processing (OLAP) cubes. Strictly speaking, the purpose
of an OLAP cube is reporting, but then again, so are the tables in a data warehouse. Many companies find that
reporting against data warehouse tables is sufficient for their needs. So, why use a cube? And what are the
differences between reporting tables and cubes?
To answer these questions, let's take a look at Table
9-1
to see the comparison.
Table 9-1.
Cubes vs. Tables
Reporting Tables (Data Warehouse Tables)
Cubes (SSAS/OLAP)
Simpler to create than cubes.
Complex setup.
Standard performance retrieval.
Higher performance retrieval.
Requires SQL joins and subqueries.
SQL joins and subqueries are removed.
Aggregate values are discouraged and are not easily
stored.
Aggregate values are encouraged, and cubes are tuned
to store them.
Accessed via standard SQL programming statements.
Can be accessed via MDX or XMLA programming
languages, which are not commonly known in the
industry.
Cannot always replace a poorly designed existing data
warehouse database.
Is able to replace a poorly designed existing data
warehouse database.
Most companies believe that tables are sufficient for their needs, at least at first. Nevertheless, as reporting
activity grows over time, they move to a cube-based reporting system to gain its benefits.
Microsoft added two new SSAS server models in SQL 2012: a tabular model and a PowerPivot model. They
act similar to reporting tables, but with the performance of a cube. And in many cases, the performance is even
better. Both of these are somewhat easier to configure than the original multidimensional cube model, but they
do not include as rich a feature set. Table
9-2
identifies some of these differences.
