Skip to Content
author's profile photo Former Member
Former Member

cube performance

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Jan 05, 2005 at 02:45 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.