Prelude

Recently one of our regular customers called our tech support complaining of the Direct-version's slow performance: an OLAP-slice of a thousand rows and a few columns took several minutes to get calculated.

After analyzing the test database and the Cube structure he sent, I discovered to my sincere astonishment that the problem was in the wrong design of the Cube - it was so obvious that having spent just a couple of minutes on redesigning its structure, I managed to reduce the response time of RadarCube by hundreds (!!!) times - from a few minutes to one second.

"Wow", I thought to myself, "if we could just provide some simple guidelines for designing a Cube structure, it may prove extremely useful to lots of our clients - I should write an article about that!" So, here it is.

A Few Things about Data Sources

The vast majority of the desktop-OLAP clients currently at the market are able to process just one table as data source, while the real data for OLAP-analysis is spread among several ones (for the details of OLAP-data schemas see here).

So, RadarCube is the only desktop-OLAP control (at least, I haven't seen any others) that supports a multi-table fetch mode for OLAP-data. Strictly speaking, there's no point in comparing a single-table structure to a multi-table one - apart from the simplicity the former has no advantages over the latter... On the other hand, the programmer has to stuff everything into this single table: data for analysis (facts), fact descriptions - thus denormalizing the table structure and SIGNIFICANTLY increasing the volume of the fetched data. And I haven't yet mentioned the loss of information about the initial OLAP-schema structure, but more of that in the next chapters.

A Few Things about Cube Structure Elements

Any OLAP-report consists of two basic parts: the headers area and the data (fact) area. The fact area contains measures, the headers area - dimensions, hierarchies (Parent-Child, multilevel and flat), and attributes. In general, the structure of the RadarCube Direct elements looks like the elements structure conventional for Microsoft Analysis Services, so if you're acquainted with that, just skip this part and go on reading from the next chapter. However, for those, whose mind has not yet been clouded with the professional OLAP-vocabulary, I'm going to brief you:
So, the basic structural unit, describing OLAP-data is a hierarchy (a particular case of which is a simple flat list). For your convenience, the hierarchies with similar content can be grouped into folders, called "dimensions". Additional data, describing a hierarchy member, is called attributes. Now, let us give a few examples of all those:

Flat and Multilevel Hierarchies

Let's assume we have a couple of tables, describing products: Products and Categories - related by a foreign key:

Data model

Categories table

Products table

In this case, in the Cube Editor, we'll create two flat (attribute) hierarchies ("Categories" and "Products") and one multilevel ("Categories-Products") that will combine the flat ones. So, as a result we'll get the following:

Cube Structure Tree



OLAP Grid

In this case, the root node of the Cube Structure Tree is the "Products" dimension node. It's also worth mentioning that multilevel hierarchies may (even must) be created, if the fields that form them, are in the same table. For instance, there's a "Geography" table with the "Country" and "City" fields. It's obvious a city like, say, Seattle cannot be in several countries at the same time, like "USA", "UK" and "Canada". Hence, in this case the "Country" and "City" fields are interdependent and should be organized into a multilevel hierarchy "Country-City".

Parent-Child Hierarchies

As a result, the Parent-Child hierarchy in OLAP Grid will look like this:

Attributes

In our "Employees" table, in addition to the EmployeeName field and the key fields, describing the hierarchical structure of the table, there're some auxiliary fields, like Address, City, Country, HomePhone etc. that contain additional personal information. In OLAP Grid it looks like this:

The attributes are created in the Cube Editor for any hierarchy from the flat hierarchies set in the same dimension. All that needs to be done here is creating a set of flat hierarchies in the dimension, and then choosing the one, you want to create attributes for, right-clicking it and selecting "Info attributes editor..." from the menu. In OLAP Grid switching on/off hierarchy attributes is available from the context menus of the Grid cells.

Guidelines for Creating a Fast Cube

So, here comes the crux of the matter - all this article was written for the sake of this paragraph, and all the things discussed above were to make it clear ;)

Thus:

Clue #1: Create and use multilevel hierarchies, wherever possible. Analyze the data schema, locate the interdependent fields and make up multilevel hierarchies on their basis. Remember, RadarCube is able to process a multilevel hierarchy dozens times as fast as a set of flat lists that comprise it.

Clue #2: To display additional information, use ONLY attributes, but not the similar flat hierarchies!

Resume

Now I can explain how I solved the Cube performance problem for our customer:

He used flat hierarchies - 5 lists that he placed in the row area one by one. Or, frankly speaking, he was able to place just four. Upon adding the 5^th, RadarCube would simply hung...

The first two lists were interdependent fields, and I was able to create a multilevel hierarchy on their basis. The other three were per se attributes to the last level of the newly created multilevel hierarchy. So, I just made them such...

And that was all: the Cube now worked about 500 as fast, and it took me just a tiny effort to make it. :)

Maybe, it would be a good idea for you to make the same with your Cubes?

Reference:

OLAP Schema types
Hierarchies in RadarCube. Types of hierarchies
Using Cube Editor to construct a Cube

Vladimir Lyutetsky
Radar-Soft Team