Tuesday, November 6, 2007

SAP BW/BI Extended Star Schema

Could you believe how SAP use the surrogate key concept in its Extended Star Schema Model.
Before, I explain this concept. I would like to say some basic design information about model.
This model have only one Fact Table and and few dimensions (maximum 16, among them 3 dimensions are predefined.).

Each dimension has one or more characteristics(It could be simple object or Master Data (attribute, text, hierarchy)). But these characteristic is referenced by SID.
The characteristic values are not stored in the dimension tables. A numerical SID key is generated for each characteristic. This foreign key replaces the characteristic as the component of the dimension table.
SID table is separetly maintained and primary key of this table comes from characteristic table.
Its mean SID take cares to each characteristic, and if any characteristic contain master data then those characteristics could be independently used by any dimension to avoid replication of master data. This is the great advantage of SID in Extended Star Schema.

Fact Table contain key figures (fact, numerical values) and dimensions ids as foreign key.

And fact table and dimension are connected relationally, and form Info Cube.

I think, this is very interesting post and I m waiting for your question.

15 comments:

  1. How is it different from Snowflaking

    ReplyDelete
  2. It has master data table which is separate from dimension table.
    Due to this, it enhance the performance of schema.
    While in snowflake you can only use master data only one dimension and you can not use it with other dimension.
    SAP BW provide master data table availability to other dimension of cube.
    Your question is really very good.

    ReplyDelete
  3. hi syed

    what is the understanding about snowflaking?

    ReplyDelete
  4. what are the tcodes used in bw and what is the purpose of the each tcode

    ReplyDelete
  5. snowflake is normalized form of star schema, ideally it is not good for data warehouse.

    ReplyDelete
  6. there are so many tcodes in bw,
    these tcodes are just command which execute to display different screen.

    ReplyDelete
  7. hi, syed,
    how long it took you to recognise the sarcastic question from Anonymous

    ReplyDelete
  8. This comment has been removed by a blog administrator.

    ReplyDelete
  9. Star Schema has only relational diagram.

    ReplyDelete
  10. hi syed,,this is alok from india,,a newcomer in this field...can i ask u some doubts regarding this?

    ReplyDelete
  11. Hi Syed,

    Could you please give a simple example to understood the sid table, and star schema for BI.

    ReplyDelete
  12. hi syed,

    does sap bw also support the galaxy schema with more than one fact table? if so, how would that work?

    ReplyDelete
  13. hi syed would u plz explain the concept of extended star schema by taking a real time scenario

    ReplyDelete
  14. SK Parvin: If info cube contain info object with master data then it is perfect example of extended star schema.

    ReplyDelete