cube performance

[Former Member]

Hi Friends,

HAPPY NEW YEAR!!! (Belated Wishes)

Just tell me one thing,

When can we say a cube is best designed or it will give best performance (How many characteristics, keyfigures, dimensions it can contain)?

Hope i am clear..

Thanks

Surya

[Former Member]

Hi Surya,

your question is a really very deep question...and it's not easy to give a best answer because there is no magical formula that can assure an optimum star schema from a performance point of view....

The fact table and dimension tables are both relational database tables.

These tables are linked to one another thanks to identification numbers (dimension IDs), which are in the key part of the particular database table.

The characteristics determine the granularity (the degree of detail) at which the key figures are kept in the InfoCube.

Characteristics that logically belong together (district and area, for example, belong to the regional dimension) are grouped together in a dimension.

If you follow this design criterion, dimensions are to a large extent independent of each other, and dimension tables remain small with regards to data volume, which is desirable for reasons of performance. This InfoCube structure is optimized for Reporting.

But this is not always true.

Dimension tables should have a 'relatively' small number of rows (in comparison to the Fact Table: factor at least 1:10 until 1:20).

If the dimension table is approaching the same size as the fact table, the dimension should be selected as a line item (you have to flag it in dimension management inside your cube).

This dimension can then be assigned to exactly 1 InfoObject (the line item InfoObject).

When activating the InfoCube, NO database tables will be created for this dimension; a field with the data element RSSID is written in the fact table instead, that points directly to the SID table for the InfoObject.

In other words: the route using a dimension table is omitted. From the star schema diagram: InfoCube-> Dimension->InfoObject will be (for this a line item InfoObject) InfoCube->InfoObject.

Besides, there is also the "High Cardinality" flag (for every dimension) that indicates that a single dimension contains a high number of records (at least 20% the size of the fact tables, measured in number of records).

If you set this flag, when you generate the data structures for a cube, the physical layout of the tables may be adjusted. For example, suitable index types are chosen (database-specific). This is necessary for good performance when reading the records from this dimnension.

Here you have to consider also the role of the master data attributes/hierarchies and so on...

Ralph Kimball writes in his book ‘The Data Warehouse Toolkit’:

"The nine database design decision points for a dimensional data warehouse consist of deciding on the following:

1.The processes, and hence the identity of the Fact Tables ((one Fact Table - one InfoCube...) -> intersection entities)

2.The dimensions of each Fact Table (-> strong entities)

3.The dimension attributes with complete descriptions and proper terminology (-> attributes and entities)

4.The grain of each Fact Table

5.The facts, including pre-calculated facts

6.How to track slowly changing dimensions

7.The aggregations, heterogeneous dimensions, mini-dimensions, query modes and other physical storage decisions

8.The historical duration of the database (archiving aspects)

9.The urgency with which the data is extracted and loaded into the data warehouse (time frame for loading)"

Hope it helps...

Bye,

Roberto

[Former Member]

I was forgetting...

Due to technical (database) restrictions (maximum 16 key fields) a maximum of 16 dimensions managed per InfoCube and each individual dimension can contain a max. 249 characteristics.

There are 3 fixed, predefined dimensions: Data Packet (cannot be maintained by the user, serves for the consist management of data in the InfoCube), Time (the time characteristics are stored here) and Unit (this dimension contains the currencies and units of measure of the key figures of the InfoCube).

As a result 13 freely configurable dimensions remain at the user's disposal for the characteristics.

[Former Member]

Hi Roberto,

As usual you have given an excellent explanation.

Actually this is the question from an interviewer.

I could not able to answer it, and hope some of you guys can answer it.

And finally i came to a conclusion that, there is no limit for Characteristics/Keyfigures/Dimensions (Apart from our standards, 233KF, 248Chars., 13Dimensions) for better performance of a Cube.

What do you say?

-Surya

[Former Member]

Surya,

honestly I never saw a cube with 248 chars and 233 KF...technically speaking it's possible to build a similar kind of cube, but, without doubt, a cube with only 2 chars and one kf will be probably more performant than a monster like this type of godzilla !

I want to insist on this point: there are no magical formulas to build a cube with the maximum performance...

...interviewer's assertion has been probably provocative because I think that there are always valid alternative solutions to a godzilla cube, but, as you can see from my previous reply, this is a serious matter and it's not so easy to manage it between a coffe break and another one !!!

Bye,

Roberto