In this chapter, we will discuss about Star and Extended Star Schema. We will also understand what InfoArea and InfoObjects are.
In Star Schema, each dimension is joined to one single fact table. Each dimension is represented by only one dimension and it is not further normalized. A dimension Table contains a set of attributes that are used to analyze the data.
For example − We have a fact table called FactSales that has primary keys for all the Dim tables and measures units_sold and dollars_sold to do analysis.
We have 4 Dimension tables − DimTime, DimItem, DimBranch, DimLocation as shown in the following image.
Each dimension table is connected to a fact table as the fact table has the primary Key for each dimension tables that are used to join two tables.
Facts/Measures in the Fact Table are used for analysis purpose along with the attribute in the dimension tables.
In Extended Star schema, fact tables are connected to dimension tables and this dimension table is further connected to SID table and this SID table is connected to master data tables. In an extended star schema, you have the fact and dimension tables inside the cube, however SID tables are outside the cube. When you load the transactional data into the Info cube, the Dim Id’s are generated based on SID’s and these Dim ids’ are then used in the fact tables.
In the extended star schema one fact table can connect to 16 dimension tables and each dimension table is assigned with 248 maximum SID tables. These SID tables are also called as characteristics and each characteristic can have master data tables like ATTR, Text, etc.
ATTR − It is used to store all the attribute data.
Text − It is used to store description in multiple languages.
InfoObjects are known as the smallest unit in SAP BI and are used in Info Providers, DSO’s, Multi providers, etc. Each Info Provider contains multiple InfoObjects.
InfoObjects are used in reports to analyze the data stored and to provide information to decision makers. InfoObjects can be categorized into the following categories −
InfoObjects are created in InfoObject catalog. It is possible that an InfoObject can be assigned to a different Info Catalog.
Info Area in SAP BI is used to group similar types of objects together. Info Area is used to manage Info Cubes and InfoObjects. Each InfoObjects resides in an Info Area and you can define it in a folder which is used to hold similar files together.