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.
Tuesday, November 6, 2007
SAP BW/BI Extended Star Schema
Subscribe to:
Post Comments (Atom)
How is it different from Snowflaking
ReplyDeleteIt has master data table which is separate from dimension table.
ReplyDeleteDue 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.
hi syed
ReplyDeletewhat is the understanding about snowflaking?
what are the tcodes used in bw and what is the purpose of the each tcode
ReplyDeletesnowflake is normalized form of star schema, ideally it is not good for data warehouse.
ReplyDeletethere are so many tcodes in bw,
ReplyDeletethese tcodes are just command which execute to display different screen.
hi, syed,
ReplyDeletehow long it took you to recognise the sarcastic question from Anonymous
This comment has been removed by a blog administrator.
ReplyDeleteStar Schema has only relational diagram.
ReplyDeletehi syed,,this is alok from india,,a newcomer in this field...can i ask u some doubts regarding this?
ReplyDelete@alok: Yes sure.
ReplyDeleteHi Syed,
ReplyDeleteCould you please give a simple example to understood the sid table, and star schema for BI.
hi syed,
ReplyDeletedoes sap bw also support the galaxy schema with more than one fact table? if so, how would that work?
hi syed would u plz explain the concept of extended star schema by taking a real time scenario
ReplyDeleteSK Parvin: If info cube contain info object with master data then it is perfect example of extended star schema.
ReplyDelete