Business and Data Analytics Essay

TIME
Practical Analytics
Chapter 3: Dimensional Data Modeling
CUSTOMERS
Chapter 3 Learning Objectives
▪
After completing this chapter, you will be able to:
▪
Explain the reasons for or against the use of separate transactional and informational systems.
▪
Discuss why data warehouses and other OLAP structures are used for staging and analyzing data.
▪
Explain the layered scalable architecture of data warehouse modeling.
▪
Explain multidimensional data warehouse models, including star schemas and snowflake schemas.
© 2014 Epistemy Press All rights reserved. / Page 2
Outline
▪
Data warehouse database structure
▪
The data warehousing process
▪
Data warehouse modeling
▪
Layered scalable architecture
▪
Multidimensional modeling – the star schema
▪
Multidimensional modeling – the snowflake schema
▪
Summary
© 2014 Epistemy Press All rights reserved. / Page 3
Overview of Analytics Techniques
Analytics Techniques
Exploration and
Reporting
• Slicing/dicing
• Multidimensional
analysis
• Reporting
Knowledge discovery
• Forecasting
• Descriptive data
mining
• Predictive data mining
© 2014 Epistemy Press All rights reserved. / Page 4
Visualization
• Charts
• Dashboards
• Advanced
visualization
Data Warehouse Structure
▪
Takes large quantities of data and stores them so that we can easily access the data
▪
Relational database that has been denormalized
▪
That is, some redundant data is added back into the database to reduce the number of tables. This speeds up
queries
▪
Because data warehouses are “read only”, modification anomalies are not relevant
▪
Provides a “single source of truth” for analytics
© 2014 Epistemy Press All rights reserved. / Page 5
Data Storage Structures
▪
Relational databases → transactional systems
▪
Data warehouses → informational systems
▪
Can the two be combined into one data structure?
▪
Read vs. write optimized
▪
Performance
© 2014 Epistemy Press All rights reserved. / Page 6
The Data Warehousing Process
▪
Data from the source are extracted,
transformed and loaded into the target
system
▪
▪
The process is called ETL
Data staging is the area where data are
cleaned up and prepared (transformation)
© 2014 Epistemy Press All rights reserved. / Page 7
Components of the Data Warehousing Process
© 2014 Epistemy Press All rights reserved. / Page 8
Data Warehouse Modeling
▪
The layered scalable architecture (LSA) is a framework for data warehousing
▪
Layered – The LSA model consists of multiple layers. Data flow in many predefined paths through these layers.
Each layer plays a specific and important role.
▪
Scalable – Data warehouses typically grow in scope and size. The LSA provides a means of scaling the
architecture to reflect the ways the requirements either expand or shrink.
▪
Architecture – LSA is an architecture that does not depend on any specific technology. Therefore, it can be
mapped to the specific implementation required by whatever hardware and software the user selects.
© 2014 Epistemy Press All rights reserved. / Page 9
The Layered Scalable Architecture (LSA)
© 2014 Epistemy Press All rights reserved. / Page 10
LSA Enterprise Data Warehouse (EDW)
Data acquisition layer
1.
▪
Persistent staging area (PSA)
2.
Quality and harmonization layer
3.
Propagation layer
4.
Corporate memory
▪
A data store object (DSO)
© 2014 Epistemy Press All rights reserved. / Page 11
LSA Architected Data Mart Layer
5.
Business transformation layer
6.
Reporting layer
▪
May have multiple cubes or virtual cubes
7.
Operational data store (ODS)
8.
Virtualization layer
© 2014 Epistemy Press All rights reserved. / Page 12
Data Flows in LSA
©SAP SE
© 2014 Epistemy Press All rights reserved. / Page 13
Multidimensional Modeling – Star Schema
▪
Includes 1 fact table and multiple dimension tables
▪
Fact table
▪
Stores measures / key figures
▪ Represents Transactional data
▪
Dimension tables
▪
Represent master data
▪ Provides a more detailed view of the fact
▪ Time dimension
▪ Customer dimension
▪ Product dimension
▪ Sales Org dimension
▪ Region dimension
▪ Other (default) dimensions
▪ Unit dimension
▪ Data package dimension
© 2014 Epistemy Press All rights reserved. / Page 14
Fact Table
Product_ID
Time_ID
SalesOrg_ID
Region_ID
Customer_ID
Quantity Sold
Price
Revenue
STORE
Store_ID
Name
1 Nina’s
2 Donna’s
3 Peter’s
Manager
Beth Benford
Mike Larson
Kelly Chen
Location
Downtown
Gaslamp
East Side
City
Dallas
San Diego
New York
FACT TABLE
Period_ID
Store_ID
1
2
3
2
3
Product_ID Quantity
1
1
1
2
2
1
2
1
3
3
Total
10 $ 500.00
3 $ 100.00
1 $ 3,000.00
1 $ 400.00
2 $ 2,000.00
PRODUCT
Product_ID Name
1 Pumps
2 Slippers
3 Dress
Color
Red
Blue
Gold
PERIOD
Period_ID
Day
1
2
3
© 2014 Epistemy Press All rights reserved. / Page 15
Month
1
2
3
Year
1
1
1
2015
2015
2015
Category
Shoes
Casuals
Clothing
Shortcomings of Star Schemas
▪
Duplication of dimensional data
▪
No support for multiple languages
▪
Slow joins due to alphanumeric keys
▪
No historization (changing dimensional data)
▪
Hierarchies can cause anomalies
▪
The customer dimension at the right contains
two hierarchies
▪
Sales_Territory
▪
Continent / Country
© 2014 Epistemy Press All rights reserved. / Page 16
Multidimensional Modeling – Snowflake Schema
The dimension tables are split into
more tables.
© 2014 Epistemy Press All rights reserved. / Page 17
▪
Reduces data redundancy
▪
Supports multiple languages,
currencies & UoM
▪
Quicker joins via conversion of
text keys to numbers
▪
Supports historization
▪
Support cube administration
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)
© 2014 Epistemy Press All rights reserved. / Page 18
SAP BW – Snowflake Model
▪
SAP BW uses a snowflake model instead of the star schema
▪
Further normalization and expansion of the dimension tables in a star schema results in a snowflake design.
▪
This reduces data redundancy which reduces storage space but increases the number of joins.
▪
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.
© 2014 Epistemy Press All rights reserved. / Page 19
SAP BW Schema
▪
The multi-dimensional model in SAP BW is based on the SAP BW schema, which was developed as an
extended (snowflake) 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 data information!
▪
Master data is stored in separate tables, called master data tables (outside the dimension tables).
▪
Master data tables can be used in multiple InfoCubes
© 2014 Epistemy Press All rights reserved. / Page 20
SAP BW Vocabulary
Classic Star Schema
SAP BW Schema
Fact
Key Figure
Fact Table
Fact Table
Dimension Table
Dimension
Master Data Tables – Text, Attributes,
and ierarchy
Dimension Attribute
© 2014 Epistemy Press All rights reserved. / Page 21
Characteristic
SAP BW 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)
▪ Values that can be added, subtracted, divided, multiplied, etc.
▪
Dimension table
▪ Acts as a link between Master data and Fact table records
▪
InfoCube
▪ Consists of Fact and Dimensional tables.
© 2014 Epistemy Press All rights reserved. / Page 22
What is a Dimension Inside SAP BW?
▪
A dimension is a group of characteristics which belong to the same business object (semantically).
▪
Each characteristic may contain additional attributes which are relevant for data analysis – or not!
1. Display attributes are InfoObjects for which data analysis does not make sense (such as a house
number or a family name)
2. Navigational attributes are InfoObjects which are used to perform data analysis.
3. Time-dependent attributes (example: price, payrate, etc.)
4. Time-independent attributes (example: product weight and volume)
▪
Attributes may be characterized as display OR navigational AND time-dependent OR time-independent
© 2014 Epistemy Press All rights reserved. / Page 23
Text Tables
▪
Whereas non-textual master data are stored in attributes tables, textual master data are stored in text tables
▪
Time-dependent text
▪
Time-independent text
▪
Language- dependent text
▪
Language-independent text
▪
Hierarchy tables
© 2014 Epistemy Press All rights reserved. / Page 24
Time-dependent Hierarchies
© 2014 Epistemy Press All rights reserved. / Page 25
Version-dependent
Hierarchy
© 2014 Epistemy Press All rights reserved. / Page 26
Interval-dependent Hierarchy
© 2014 Epistemy Press All rights reserved. / Page 27
Historization of Data
© 2014 Epistemy Press All rights reserved. / Page 28
Fact Tables
Fact table with Currency Key
© 2014 Epistemy Press All rights reserved. / Page 29
Fact Table with Unit of Measure Key
Multidimensional Modeling – in SAP BW
▪
©SAP SE
© 2014 Epistemy Press All rights reserved. / Page 30
Surrogate ID (SID) tables are used to
convert textual keys to numeric keys to
create links and provide for faster
queries
InfoCubes
▪
InfoCubes are the central objects of the multi-dimensional model in SAP BW
▪
Reports and analyses are based on these InfoCubes
▪
An InfoCube is a self-enclosed dataset for a business area from a reporting viewpoint
▪
Queries can be defined and executed on the InfoCube
© 2014 Epistemy Press All rights reserved. / Page 31
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 data are stored and maintained outside the
InfoCube.
▪ Dimension – grouping of logically related characteristics. SAP BW allows a total of 16 dimensions. Max 248
characteristics per dimension.
© 2014 Epistemy Press All rights reserved. / Page 32
SAP BW– 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
© 2014 Epistemy Press All rights reserved. / Page 33
Types of InfoCubes
▪
Standard Cube
▪ Physically contains data in the database
▪ They are data targets (Data can be loaded into cubes.)
▪ A type of InfoProvider
▪ BW 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 (not the virtual cube)
© 2014 Epistemy Press All rights reserved. / Page 34
A Snowflake Schema Example – GBI
© 2014 Epistemy Press All rights reserved. / Page 35
Summary
▪
Data warehouses store large volumes of archived data
▪
Layered Scaleable Architecture is a framework for data warehousing
▪
Star schemas are denormalized relational databases with one fact table and multiple dimension tables
▪
Fact tables store values of an event or transaction
▪
Dimension tables store additional detail of the event or transaction
▪
Snowflake schemas mitigate the shortcomings of the star schema
© 2014 Epistemy Press All rights reserved. / Page 36

Purchase answer to see full
attachment