Download - Lec 3 - SAP BI Modeling
Lecture 3
1
Outline OLAP/OLTP Review of Star Schema
Dimension Tables and Fact Table Real world changes
SAP BI Enhanced Star Schema Fact Table Dimension Tables Master Data Tables
MetaData Objects
2
OLTP vs. OLAP systemsOperational (OLTP) Informational (OLAP)Detailed Summarised
Can be updated Snapshot records, no updates allowed
Accurate up to the second Timestamp on each record
Used for clerical purposes Used by management
Built based on requirements Built without knowing requirements
Supports small uniform transactions Supports mixed workload
Data designed for optimal storage Data designed for optimal access
Very current data Mainly historical data
Data is application oriented Data is integrated
Referential Integrity is useful Referential integrity is not useful
High availability is normal High availability is nice to haveProduct
Dimension
Quantities Revenues
Costs Taxes
Customer Dimension Sales Dimension
Competition DimensionTime Dimension
3
Review of Star Schema Multidimensional data models are needed for the
creation of data warehouses The classic star schema is a commonly used multi-
dimensional model This database schema classifies two groups of data:
facts (sales or quantity, for example) and dimension attributes (customer, material, time, for example).
Facts are the focus of the analysis of a business' activities. The fact data (values for the facts) are stored in a highly normalized fact table.
4
5
Real World Changes The Star schema reflects changes in the ‘real world’
normally by adding rows to the fact table. More precise ‘real world’ changes like Customer ‘4711’
purchase Material ‘BBB’ at Day ‘19980802’ for $100 creates a new record in the fact table, which is identified by the combination of key attributes in the dimension tables.
6
Adding Records
7
Star Schema Limitations• No support for multiple
languages • Reduced performance due
to the use of Alphanumeric keys
• No support for time dependent changes
• Duplication of Dimensional data.
Material IDCustomer IDDate ID
Sales Amount (K)Unit Price (K)Quantity (K)
Date ID
Year Fiscal Year
QuarterMonth
Material ID
Material Name Material Type Material Group
Customer ID
Customer NameCity RegionType
Fact Table
CustomerDimension
TimeDimension
MaterialDimension
8
SAP BI SchemaThe multi-dimensional model in SAP BI is based on
the SAP BI schema, which was developed as an enhanced star schema as a response to problems experienced with the classic star schema.
The enhancement comes from the fact that the dimension tables do not contain master datainformation!
Master data is stored in separate tables, called master data tables (outside the dimension tables).
9
Snowflake model SAP BI uses a snowflake model instead of the star schema Further normalization and expansion of the dimension
tables in a star schema result in the implementation of a snowflake design.
A dimension is said to be snowflaked when the low-cardinality columns in the dimension have been removed to separate normalized tables that then link back into the original dimension table.
10
Dimensional Models
11
Snowflake
12
When do you snowflake?Snowflaking a dimension table can typically be
performed under the following two conditions: The dimension table consists of two or more sets of
attributes which define information at different grains (detail)
The sets of attributes of the same dimension table are being populated by different source systems (very common in data warehouses)
13
Details of the SAP BI Schema1. The center of an InfoCube forms the fact table
containing the key figures (e.g. sales amount).
2. The fact table is surrounded by several dimensions.
3. A dimension consist of different table types: Dimension Table
Attributes of the dimension tables are called characteristics (e.g. material). The meta data object in BI that describes characteristics and also key figures (facts) is called InfoObject.
14
SAP BI Extended Star Schema Master Data
Represents Dimensional data Independent of any Fact table Reusable in multiple Fact tables Designed to support multi lingual systems Designed to support time dependent data. Designed to improve query performance.
Fact table Consists of Dimensional Keys and Key Figures (facts).
Dimension table Acts as a link between Master data and Fact table records
InfoCube Consists of Fact and Dimensional tables.
15
InfoCubes
InfoCubes are the central objects of the multi-dimensional model in SAP BI
Reports and analyses are based on these InfoCubes It is a self-enclosed dataset for a business area from a
reporting viewpoint Queries can be defined and executed on the basis of
an InfoCube
16
InfoCubes
InfoCube consists of a number of relational tables arranged together- Fact Table – One table in which key figures are stored. Max 233 key
figures! Dimension Tables – Linked by SID tables to the master data tables.
(Remember master data is not part of the InfoCube. Those are stored and maintained outside the InfoCube.)
Dimension – Grouping of logically related characteristics. SAP BI allows a total of 16 dimensions. Max 248 characteristics per dimension.
Technical name of SAP provided InfoCubes starts with 0. Your own Cubes begin with A-Z, 3-9 characters long
17
SAP BI Schema
18
Types of InfoCubes
Standard Cube Physically contains data in the database They are data targets ie. data can be loaded into it A type of InfoProvider BI objects are called InfoProviders when queries can be
executed based on them Standard InfoCubes are technically optimized for read access.
Virtual Cubes (Virtual Provider) Only represent logical views of a dataset Data resides in data source
19
Viewing InfoCube Schema
Go to the Data Warehousing Workbench Metadata Repository
Choose InfoCube Find the InfoCube you want to view Eg. SAP Demo Sales and Distribution overview -
0D_SD_C03 Choose InfoCube - schematic display as star
schema
20
Viewing/Downloading Data in an InfoCube1. Start Data Warehousing Workbench: Modeling (RSA1)
Choose Modeling InfoProvider Navigate to the InfoCube using the hierarchy of InfoAreas (SAP Demo
Sales and Distribution overview - 0D_SD_C03)
2. Right click and Display data3. Click on Fld Selection for output.4. Select the fields (characterisitcs and key figures that you
want)5. Execute6. Choose value ranges (if desired)7. Execute8. Request export to a local spreadsheet file9. View the data in the spreadsheet. Note that this is a flat file
derived from a relational database (star schema) 21
Terms used
22
InfoCube and Master Data
23
24
Master Data Tables Additional information about characteristics is
referred to as master data. Master data types:
Attributes Texts (External) Hierarchies
Eg. the attribute 'material group' is stored in the attribute table, the text description for 'material name' is stored in the text table and the material hierarchy is stored in the hierarchy table for the characteristic 'MATERIAL'.
25
SAP BI MetaData Objects –Master Data Tables3 possible components Text
Textual description of Master Data element (AU = Australia).
Can be language and time dependent. Attributes
Characteristics which describe another Characteristic. (Address, Telephone.)
Can be language and time dependent. Hierarchies
Tree like structures to group master data. Can be language and time dependent.
26
27
Note: Master Tables = Attribute Tables in this slide
BI IconsKey Figure
Characteristic
InfoCube
Dimension
Hierarchies
Maintain Master Data
Text
InfoObject
InfoSource
Source System
InfoArea
28
SAP BI MetaData Objects
InfoObjects Represent the structure that allows data to be stored in a BI
systems. Used to describe business processes and information
requirements (fields): Customers Sales revenue
Contain technical and specialist information for master data and transaction data in the Meta Data Repository.
Used in BI to create structures and tables. Either Characteristics or Key Figures.
29
SAP BI MetaData Objects – Key Figures
Date Customer Material Sales Quantity Amount
1/1/08 Jones Racer 26 2 $2000
1/1/08 Rosemann Mountain B 5 $4000
Key Figures Any kind of numeric information used to measure a
business process.
Six different types Amount * Quantity * Number Integer Date Time* Requires Unit (currency, size) for further clarification
Amount Currency1000 EUR
1500 USD
30,000 ZWD
30
SAP BI MetaData Objects – Key Figures
Year Month SalesRepNO
ProductNO
Sales
2007 10 S1 P1 5002007 10 S1 P2 5002007 10 S2 P1 2002007 10 S2 P2 6502007 10 S3 P1 300
Aggregation Key Figures are stored
in Fact Table Aggregation defines
how they are stored Sum Maximum Minimum
OrdDate SalesRepNO
ProductNO
Sales
23.10.2007 S1 P1 30024.10.2007 S1 P1 20025.10.2007 S1 P2 10026.10.2007 S2 P1 20026.10.2007 S2 P2 30027.10.2007 S1 P2 40027.10.2007 S3 P1 10030.10.2007 S2 P2 20030.10.2007 S3 P1 20030.10.2007 S2 P2 150
Fact Table
Transaction Table
Sum
31
SAP BI MetaData Objects – Key Figures
32
SAP BI MetaData Objects –Characteristics
Date Customer Material Sales Quantity Amount
1/1/08 Jones Racer 26 2 $2000
1/1/08 Rosemann Mountain B 5 $4000
Characteristics Designed to describe objects used with in the
business process customer, product, colour, postcode, date, unit etc.
They are the attributes of the dimension table
Could be made up of more than one field Used to analyze Key Figures
33
SAP BI MetaData Objects –Characteristics
Characteristic(Single)
Region City Customer Name Customer Phone
Characteristic(Grouped)
Customer State City Customer Name Customer Phone
(Attributes of Customer)
34
SAP BI MetaData Objects –Characteristics Characteristic
definition contains: Technical field descriptions (data type, length, etc) Display properties Transfer routines (executed when data is uploaded) Master data descriptions (time dependency, navigational properties, text
properties, language dependency, Hierarchies) Unit Characteristic
currencies and units of measure are required for Amount and Quantity data types
Time Characteristic compulsory assignment to time dimension in InfoCube, (Calendar
year) Technical Characteristic
only technical use in BI (Request ID)
35
SAP BI MetaData Objects –Characteristics
36
Types of Master Data Tables
37
Characteristic - Compounding
Department
DepartNO (PK)
Cost Center
CostCenterNO (PK)
Dimension
DepartNO (PK) CostCenterNO (PK)
Entity Relationship Model
• Compounding refers to the when more than one characteristic is required to uniquely identify a master data record.
• The Characteristics combine to form a concatenated primary key within a Dimension table.
38
SAP BI MetaData Objects –InfoCube Contains two types of data
Key Figures (transactional data) Characteristics
1 Fact Table and 16 Dimension Tables 3 Dimensions are predefined by SAP
Time Unit Data Package
Dim1 Dim2 Dim3 Dim4 Dim5 Dim6 KF1 KF2
3 Pre-defined Up to 13 User-defined
39
Surrogate Keys• In order to improve query execution the system assigns a numeric
(surrogate) key to represent the structured alpha numeric key assigned in Master data.
• A SID table is used to store the reference between the two keys.
40
Dimension Table
KFSales
DIM IDTime
DIM IDProduct
DIM IDSalesRep
Fact Table
2233
11SIDDIM ID
Master DataSID Table
KFSales
DIM IDTime
DIM IDProduct
DIM IDSalesRep
Fact Table
2233
11SIDDIM ID
Master DataSID Table
• A Dimension table does not store any master data records. It contains a series of surrogate keys that link the Fact table and Master data
together.
41
SAP BI Meta Data Objects –InfoArea/InfoCatalog An InfoArea is a directory that stores all related
InfoObjects within the same business context. An InfoObject Catalog is a folder within an
InfoArea that stores specific InfoObjects. Separate InfoObject Catalogs are used for Key Figures
and Characteristics
Displayed via a tree structure or directory
42
InfoObjects SummaryInfoObjects
Key Figures Characteristics
Characteristic
Time
Unit
Package
Attribute
Text
Hierarchy
Amount
Quantity
Number
Integer
Date
Time
Unit
Unit
Key Figure InfoObject CatalogCharacteristic InfoObject Catalog
InfoArea 43
Aggregation
The system has to aggregate using multiple characteristics.
The system can aggregate with another rule for each key figure (exception aggregation).
The key figure Number of Employees would, for example, be totaled using the characteristic Cost Center, and not a time characteristic. Here you would determine a time characteristic as an exception characteristic with, for example, the aggregation Last Value.
44
Hierarchies
Hierarchies allow data to be presented in alternative ways.
Hierarchies can be either Version, Time or Interval dependent.
Version Dependent
Region
North South
S1 S3S2 S4 S5
Version 1
Region
North South
S1 S5 S2 S4
Version 2
S3
45
Hierarchies• Time Dependent
• Interval DependentInstead of each node representing a single value, node may now represent a range of values.
46
InfoCube: Quick build1
• Create InfoArea
2• Create InfoObject
Catalogues
3• Create Characteristics
and Key Figures
4• Create InfoCube and
Define Dimensions
5• Assign Characteristics to
Dimensions
6• Choose Key Figures
7• Save and Activate
InfoCube
47
1. Start with the Data Warehousing Workbench– Modeling
2. Since InfoCubes are InfoProviders, go to InfoProvider
3. Create InfoArea if needed4. Create InfoCube in the context menu of your
InfoArea5. Specify name and description (you can copy an
existing InfoCube too)
6. Choose a Standard Cube. Save
48
InfoCube: Detailed build
7. Time dimension- Add the InfoObjects that you want to be part of the Time dimension
8. Then add and modify dimensions as modeled in your star schema
9. Add InfoObjects (characteristics) to the dimensions. These InfoObjects were created previously
10. Add Key figures InfoObjects11. Save and Activate the InfoCube12. You can view the Data Model in the context menu of
the cube.
49
Summary SAP BI uses an extended (snowflake) star schema Dimension tables, fact table and master data tables are
used to create the snowflake Master data tables reside outside the InfoCube Characteristics and key figure InfoObjects are used to
model attributes of dimension tables and fact tables respectively
50