Extended Star Schema
Extended Star Schema has a fact table in the middle surrounded by Dimension tables. The Dimension tables have dimension ids and SID ids. The SID table is the table which connects Dimension table and Actual data (Master Data) Table.
In the below diagram, if we want to know the revenue from a material by a specific customer, Each and every customer number has a SID generated and in the same way, each and every Material has an SID generated.
- We have it as a characteristic in the attributes
- Add as an Navigational/Display attribute(more notes later) where it directly does not reside in the cube but can be called by drilling down
- As a hierarchy.
*Customer *
Product *
*Color***
*Sales *
*Fact *
C1
P1
Black
S1
30
Customer, Sales is individual dimensions and Product and Color belong to the same dimension.
When this record comes to BW, There will be SID-tables created for each one of them. Below you can see there are 4 different SID Tables (Highlighted in Yellow) and each of the value has a SID value created (boxed in red)
Once the SID are created, there will be DIM tables. Since Sales and Customer are different Dimensions, there will be separated DIM tables. Product and Color belong to the same Dimension. Hence, there is only one Dim-table. The values of the DIM tables are filled as shown in the figure below.
*Customer *
Product *
*Color***
*Sales *
*Fact *
C1
P1
Red
S1
40
- The Master Data is kept outside the infocube, which helps in accessing it from other InfoCubes/InfoProviders.
- Alpha numeric values are converted to SID values (Surrogate IDs) which are numbers , increasing the processing speed.
Extended Star Schema has a fact table in the middle surrounded by Dimension tables. The Dimension tables have dimension ids and SID ids. The SID table is the table which connects Dimension table and Actual data (Master Data) Table.
In the below diagram, if we want to know the revenue from a material by a specific customer, Each and every customer number has a SID generated and in the same way, each and every Material has an SID generated.
For each Multi-Dimension Data model, we have A FACT table, which holds all the key figure values. For each Dimension defined, there is a Dimension Table which has Dimension ID and SID ID. For every InfoObject value, there will a SID table generated which connects the InfoObject values to the Dimension values.
When we are defining attributes, there are 3 ways of doing it.
- We have it as a characteristic in the attributes
- Add as an Navigational/Display attribute(more notes later) where it directly does not reside in the cube but can be called by drilling down
- As a hierarchy.
*Customer *
|
Product *
|
*Color***
|
*Sales *
|
*Fact *
|
C1
|
P1
|
Black
|
S1
|
30
|
Customer, Sales is individual dimensions and Product and Color belong to the same dimension.
When this record comes to BW, There will be SID-tables created for each one of them. Below you can see there are 4 different SID Tables (Highlighted in Yellow) and each of the value has a SID value created (boxed in red)
Once the SID are created, there will be DIM tables. Since Sales and Customer are different Dimensions, there will be separated DIM tables. Product and Color belong to the same Dimension. Hence, there is only one Dim-table. The values of the DIM tables are filled as shown in the figure below.
Hence the entry is completed written. Now there comes another entry.
The values are populated as follows.
*Customer *
|
Product *
|
*Color***
|
*Sales *
|
*Fact *
|
C1
|
P1
|
Red
|
S1
|
40
|
Advantages:
- The Master Data is kept outside the infocube, which helps in accessing it from other InfoCubes/InfoProviders.
- Alpha numeric values are converted to SID values (Surrogate IDs) which are numbers , increasing the processing speed.
No comments:
Post a Comment