dmm302 - sap hana data warehousing: models for sap bw and sql dw on sap hana
TRANSCRIPT
Public
DMM302 - SAP HANA Data Warehousing: Models for SAP BW and SQL DW on SAP HANA
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 2Public
Speakers
Bangalore, October 5 - 7
Sreepriya, G
Las Vegas, Sept 19 - 23
Marc Bernard
Josh Djupstrom
Barcelona, Nov 8 - 10
Juergen Haupt
Ulrich Christ
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 3Public
Disclaimer
The information in this presentation is confidential and proprietary to SAP and may not be disclosed without the permission of SAP. Except for your obligation to protect confidential information, this presentation is not subject to your license agreement or any other service or subscription agreement with SAP. SAP has no obligation to pursue any course of business outlined in this presentation or any related document, or to develop or release any functionality mentioned therein.
This presentation, or any related document and SAP's strategy and possible future developments, products and or platforms directions and functionality are all subject to change and may be changed by SAP at any time for any reason without notice. The information in this presentation is not a commitment, promise or legal obligation to deliver any material, code or functionality. This presentation is provided without a warranty of any kind, either express or implied, including but not limited to, the implied warranties of merchantability, fitness for a particular purpose, or non-infringement. This presentation is for informational purposes and may not be incorporated into a contract. SAP assumes no responsibility for errors or omissions in this presentation, except if such damages were caused by SAP’s intentional or gross negligence.
All forward-looking statements are subject to various risks and uncertainties that could cause actual results to differ materially from expectations. Readers are cautioned not to place undue reliance on these forward-looking statements, which speak only as of their dates, and they should not be relied upon in making purchasing decisions.
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 4Public
Agenda
SAP HANA DW directions
SAP HANA DW - evolving the DWH to a Business Analytics Platform
SAP HANA DW and DWH data model Dynamic dimensional model for BW on HANA – business and pattern-driven HANA DW
– Building a flexible DWH core capable absorbing changes with minimal impact– Building agile extensions of the DWH core integrating any raw/ field data
Composition Model for SAP HANA SQL DW – customer-defined HANA DW
Summary
Public
SAP HANA DW directions
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 6Public
Application driven approach, SAP BW as EDW application with integrated services
• SAP BW as an application serves as a platform offering all required data warehousing services via one integrated repository
No additional tools for modelling, monitoring and managing the data warehouse required, but can be integrated
SAP BW
SAP HANA
Scheduling & Monitoring
Modeling Planning
OLAPLifecycle
ManagementETL
SchedulingTool
Modeling ToolsPlanning
Tool
MonitoringTool
Lifecycle Management Tool
ETL Tool
SQL driven approach, SAP HANA with loosely coupled tools and platform services, logically combined
Database approaches require several loosely couple tools to fulfill the necessary tasks with separate repositories
A combination of tools (such as best of breed) used to build the data warehouse
SAP – Data Warehousing approachesTwo approaches
SAP HANA
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 7Public
SAP HANA DWSAP HANA DW SAP HANA DWSAP HANA DWSAP HANA DWSAP HANA DWOptional Components
DW Foundation
PowerDesigner
HANA EIM
Business Warehouse
SAP HANA Platform
SAP HANA Platform
Planning and Definition VisionExecution and delivery
2015 2016 - 2018
Market presence in Data Warehousing with a clear roadmap
Strong and simplified offering with tight integration
Convergence into one technology stack addressing BW and SQL
based DW needs
DWH Foundation
PowerDesigner
HANA EIM
Business Warehouse
SAP HANA PlatformSAP HANA Platform
DW Modeling DW Modeling DW ETL & DMDW ETL & DM
SAP HANA Platform
SAP HANA Platform
Analytics , BI Suite, Predictive Analytics , BI Suite, Predictive Analytics , BI Suite, Predictive
HadoopSAP HANA Vora
HadoopSAP HANA Vora
HadoopSAP HANA Vora
HadoopSAP HANA Vora
HadoopSAP HANA Vora
HadoopSAP HANA Vora
Statement of Direction – Current DW Portfolio
Public
SAP HANA DW - evolving the DWH to a Business Analytics Platform
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 9Public
Traditional DWH layer architecture / LSA (Layered Scalable Architecture)
Still appropriate on SAP HANA ?
Business Integrated DWH/ Propagation Layer
Business Integrated DWH/ Propagation Layer
Architected Data Marts
Source Source
StagingAcquisition Layer
Raw DWH/Open ODS Layer
Query-ready data
Cross source harmonized, cleansed data
• Source related data with DWH services
• For comparison with integrated DWH
Prepare data for DWH
Source data
Top
dow
n m
odel
ing • Hierarchical Architecture
• Data Mart Layer as the query able layer
• All Layers primarily as service provider for the Data Mart Layer
• Data moved from layer to layer
• Costly top down modeling process – time to market ….
• ..
Still the only way?
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 10Public
Business Integrated DWH/ Propagation Layer
historic
historic
SAP HANA DWThe ‚simplified DWH‘ perspective of LSA++
Business/ Service Level Requirements
Architected Data Marts Architected Data Marts
most recent
Virtual Data Marts/ Virtual Transformations
Bot
tom
up
Bot
tom
up
Top
dow
n
Data In-Hub/ StagingAcquisition Layer
Raw DWH/Open ODS Layer
actual/ most recent
Source Source / Data Lake/ Data Lake
• Integrated business entities & values • Integrate multiple sources/ raw DWHs
Virtual Data Marts on any Layer Virtual Transformations
Source dominated DWH Source driven DWH entities & values
Normally obsolete
Virtual Transforms Data Consumption
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 11Public
SAP HANA DW and Business Analytics PlatformEmancipation of data, communication, integration, orchestration
SAP HANA promotes a DWH Core that supports
1. Flexibility extending the persisted DWH
2. Agility virtually extending the persisted DWH
3. Direct Analytics on DWH layers – no explicit Data Mart Layer
4. Virtual Combination of DWH layers – reduce redundancies
5. Virtual Combination of DWH with remote data (federation, the Business Analytics Platform)
6. Evolutionary Data Warehouse – complement Top-Down solutions with Bottom-Up approach - service level driven
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 12Public
From DWH to Business Analytics PlatformThe Logical Data Warehouse perspective of LSA++ for SAP HANA DW
Data In-Hub/ ODS Raw DWHBusiness
Integrated DWH
Data Lake Analytical Area/Virtual Solution
Communication Communication Communication Communication Communication Communication
Communication Communication Communication Communication Communication Communication
Communication, Integration & OrchestrationSAP HANA & BW Services
Communication, Integration & OrchestrationSAP HANA & BW Services
Non hierarchical, loosely coupled Information Areas
Clear service definitions
Communication, Integration, Orchestration rules
ERP, S/4HANA
Public
SAP HANA DW and DWH data model
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 14Public
Business Integrated DWH
Data-InHub
Raw DWH
Source
SAP HANA DW & DWH data modelsData Models in concert – for query-performance, flexibility & ease of integration
Data Models everywhere:
Dimensional Model
Data Marts: Dimensional Model
• Dimensional Model• Composition Model• Data Vault Model
3NF Model
3NF Model
The data warehouse data model for a SAP HANA DW should promote
• Performant querying on persisted DWH Layer data without creation of additional persisted Data Marts
• Ease of integration of any data outside of the HANA DW (Agility)
• Flexibility covering classic DWH requirements
Per
sist
ed d
ata
Virtual Data Marts
• Dimensional Model• Composition Model• Data Vault Model
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 15Public
Legacy Source Model Observations:
• GUIDs (BINARY(16)) as Keys e.g. NODE_KEY of SNWD_SO
• Date-fields as DECIMAL(21,7) e.g. CREATED_AT of SNWD_SO
• Hierarchical relations via foreign-key e.g. PARENT_KEY of SNWD_EMPLOYEES
• Business-Keys as attributes e.g.
PRODUCT_ID of SNWD_PD
From legacy 3NF source model to a HANA DW data modelBasis for our examples
Master dataT
rans
actio
n da
taMaster data
Public
Dynamic dimensional model for BW on HANA – business and pattern-driven HANA DWBuilding a flexible DWH core capable absorbing changes with minimal impact
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 17Public
Data In-Hub/ StagingAcquisition Layer
BW on HANA and LSA++Modeling the core DWH using InfoObjects - examples
Business/ Service Level Requirements
Architected Data Marts Architected Data Marts
Virtual Data Marts/ Virtualization
Bot
tom
up
Bot
tom
up
Top
dow
n Source dominated DWH Source driven DWH entities & values
• Integrated business entities & values • Data from multiple sources/ raw DWHs
Raw DWH/Open ODS Layer
Source Source / Data Lake/ Data Lake
Business Integrated DWH/ Propagation Layer
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 18Public
BW dimensional modeling - BW flat star schema until BW 740 SP8
Automated star schema generation
Flat InfoCube, DSO (classic)FACT Table
Dimensione.g. Product
BW on HANAFlat Star Schema
InfoObject
InfoObjects
Nav. Attributes
InfoObject
Nav. Attributes
InfoObject
Nav. Attributes
InfoObject
Nav. Attributes
Dimensione.g. Customer
Dimensione.g. Location
DimensionTime
Keywords:
• Fact data & dimension data defined via InfoObjects
• InfoObjects of Fact-table define Dimensions
• Automated Association of Master Data (Dimensions)
• Highly de-normalized i.e. all attributes of a Dimension in one place (InfoObject p-/q-table)
• High performance schema but
• Limited flexibility adding new attributes
• Limited flexibility adding new relationships (e.g. n:m)
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 19Public
BW dimensional modelingBW views define the dynamic star schema – 1st overview
FACT Table
Dimensione.g. Product BW on HANA
Dynamic Star Schema InfoObject
InfoObjects/ Fields
Nav. Attributes
Open ODS View Master
Nav. Attributes
Nav. Attributes
InfoObject
Nav. Attributes
Dimensione.g. Customer
Dimensione.g. Location
DimensionTime
DSO (advanced), DB-Table/ View,InfoCube, DSO (classic),
Open ODS View Master
CompositeProvider/ Open ODS View Type Fact
Keywords:
• Persistency's defined by InfoObjects or Fields
• Star Schema defined by a BW View i.e. CompositeProvider/ Open ODS View type fact
• Flexible and Agile Association of Dimensions i.e. InfoObject or Open ODS View type master
• Partitioned/ split Dimensions
• Snow-flaked Dimensions (transitive attributes)
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 20Public
BW on HANA dimensions modeling using InfoObjects Complete de-normalization of master data into InfoObjects – example 1
SNWD_PD
NODE_KEYPRODUCT_IDTYPE_CODECREATED_BYCREATED_ATCHANGED_BYCHANGED_ATNAME_GUIDDESC_GUIDSUPPLIER_GUIDTAX_TARIF_CODEMEASURE_UNITWEIGHT_MEASUREWEIGHT_UNITCURRENCY_CODEPRICEPRODUCT_PIC_URLDUMMY_FIELD_PDCATEGORY
VARBINARY(16)NVARCHAR(10)NVARCHAR(2)VARBINARY(16)DECIMAL(21,7)VARBINARY(16)DECIMAL(21,7)VARBINARY(16)VARBINARY(16)VARBINARY(16)SMALLINTNVARCHAR(3)DECIMAL(13,3)NVARCHAR(3)NVARCHAR(5)DECIMAL(15,2)NVARCHAR(255)NVARCHAR(1)NVARCHAR(40)
<pk>
<fk3>
<fk2>
<fk5><fk6><fk4>
<fk1>
SNWD_PD_CATGOS
CATEGORYMAIN_CATEGORY
NVARCHAR(40)NVARCHAR(40)
<pk>
was created
was changedhas name
has supplier
has category
has description
from Employee master
from Employee master
from Business-Partner master
from Category master
BW Dimension modeling using InfoObjects as we did it for a long time : all Attributes
assigned to one InfoObject
InfoObject IPD_DIM_1 with Attributes
Product_IDPrimary-key
dependent attributes
Employee -Foreign-key
dependent attributes
Business-Partner -Foreign-key
dependent attributes
Employee -Foreign-key
dependent attributes
De-normalization using DB-views or Extractors:
join all Product-relevant datadirect Product-Key
dependent attributes
Product relatedSource Tables DB-View
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 21Public
Modeling the DWH-core with BW on HANAComplete de-normalization of master data into InfoObjects – example 1
Advanced DSO not for querying
Ass
ign
sour
ce t
o vi
ew f
ield
s
BusinessPartner: IBP_ID
Employess: IEMP_ID
SalesOrder: SO_ID
SalesOrder_Item: SOI_POS
Product: IPD_DIM_1
Time: SO_CR_AT
Ass
ocia
te D
imen
sion
s:A
ssoc
iate
Inf
oObj
ects
/ O
pen
OD
S V
iew
s
Dimensions / InfoObjects
Fact table = advanced DSO
Dynamic Star Schema = CompositeProvider
All product relevant attributes in InfoObject
IPD_DIM_1
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 22Public
Modeling the DWH-core with BW on HANA Complete de-normalization of master data into InfoObjects – example 1
What happens, if we forgot to bring an attribute MAIN-CATEGORY into
the InfoObject IPD_DIM_1?
Add the MAIN-CATEGORY to IPD_DIM_1 to and reload IPD_DIM_1
(the Dimension) ?
What happens, if a complete new source arise with a set of product-
attributes?
How to deal with all the time-characteristics ?
Add all new attributes to IPD_DIM_1 and reload IPD_DIM_1 (the Dimension) ?
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 23Public
Pros and cons of completely de-normalized dimensions/ InfoObjects
from Employee master
from Employee master
from Business-Partner master
from Category master
Completely de-normalized Dimensions
• High performance even for complex queries– ‚no‘ joins during run-time
• Works best in stable situations (rare changes to ‘joined‘ master data)
• Means redundancies and thus realignment situations if ‘joined‘ master data have to be initialized newly
• New attributes of ‘joined‘ master data or integrating new attributes from a new source means change and reload of dimensions / InfoObjects
• Working as a ‘shared‘ Dimension serving multiple Star Schemas/ fact tables (business needs) makes it difficult to maintain big entity dimensions / InfoObjects like for Product, Business Partner etc
InfoObject IPD_DIM_1 with Attributes
Product_IDPrimary-key
dependent attributes
Employee -Foreign-key
dependent attributes
Business-Partner -Foreign-key
dependent attributes
Employee -Foreign-key
dependent attributes
BW on HANA offers new features modeling DWH Dimensions/ InfoObjects more flexible if volatility of situation requires it
• InfoObject transitive Attributes (snow-flaking) - BW on HANA V 7.50 SP4
• Split Dimension into multiple InfoObjects/ Open ODS type master
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 24Public
BW on HANA dynamic dimensional modelMore flexibility – more agility
BW on HANA Dynamic Star Schema
• Virtual Star Schema modeling via CompositeProvider and Open ODS Views of type fact
• Dimensions modeling for flexibility• InfoObject transitive Attributes (snow-flaking)
• Partitioned/ Split Dimension into multiple InfoObjects/ Open ODS Views type master
• Federating data across layers for agility • Remote Dimensions (parts of) via Open ODS Views type fact
• Remote Fact-tables
• Mixed scenarios
• …
Flexibility as DWH capability smoothly adopting changes physically (source-model and data)
Agility as DWH capability interacting and integrating data virtually and physically with minimized IT involvement
BW on HANA goes the direction for increased flexibility of physically modeling data and of agile, scalable integration of any data
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 25Public
BW on HANA dynamic dimensional modelBW on HANA dynamic star schema – flexibility & agility
BW on HANA Dynamic Star Schema
Define Dimensions
BW managed:• Advanced DSO dominated by InfoObjects or BW-type compatible Fields
BW managed:InfoObject tables
CompositeProvider Open ODS View
BW managed:• Advanced DSO dominated by fields• HANA Upsert/ Insert-table of BW
HANA DataSource
Define Facts
Open ODS View InfoObject
De-normalizedSplit/ partitioned/ SatellitesSnow-flaked/ transitive Attr.
pers
iste
d da
ta
Logical PartitionedData w. Aging (NLS)
Just dataModeled data
Foreign managed:• Local HANA tables/ DB-views
(mix scenarios)
Foreign managed:• Remote/ virtual tables/ DB-views
(federation scenarios)
Raw DWH Layer / Open ODS Layer / Data InHub/ Data Lake/ Source Layer
Business Integrated DWH Layer / Propagation Layer
Temporal join
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 26Public
Modeling the DWH-core with BW on HANATransitive attributes – snow-flaking an InfoObject dimension – example 2
What happens, if we forgot to bring an attribute MAIN-CATEGORY into the
InfoObject IPD_DIM_1?
The MAIN-CATEGORY (IPD_CATM) is navigational attribute of CATEGORY
(IPD_CAT)
Context menu
• Assign navigational attribute IPD_CATM of navigational attribute IPD_CAT as transitive attribute
• Original, other, new InfoObject as transitive attribute
MAIN-CATEGORY (IPD_CATM) is transitive attribute and behaves like a navigational
attribute of IPD_DIM_1
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 27Public
Modeling the DWH-Core with BW on HANA Transitive attributes – snow-flaking an InfoObject dimension – example 3
de-normalized Dimension
Snow-flaking: • Eliminate attributes from the
dimension table that are not direct dependent from the primary-key
• Eliminate navigational attributes from an InfoObject that are
dependent from a navigational attributes
normalized snow-flaked Product Dimension
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 28Public
InfoObject transitive attributes – snow-flakingNormalize a de-normalized Dimension – example 3
InfoObject IPD_SNOWF has only a few navigational
attributes
• Assign navigational attribute of navigational attribute as transitive attribute
• Original, other, new InfoObject as transitive attribute
• Multiple snow-flaking of same InfoObject (Employee) via referencing InfoObjects
• All Date-InfoObjects have standard navigational time attributes
Context menu
Marked navigational attributes that have navigational attributes
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 29Public
InfoObject transitive attributes – snow-flakingNormalize a de-normalized Dimension – example 3
InfoObject IPD_SNOWF has only a few navigational
attributes
InfoObject IPD_SNOWF has active transitive attributes
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 30Public
InfoObject transitive attributes – snow-flakingBW dynamic star schema with snow-flaked dimension – example 3
Fact-aDSO
Composite Provider builds Dynamic Star Schema
Time DimensionGenerated from
SO Creation Date
InfoObjects IPD_SNOWF with transitive attributes
Product DimensionTransitive attributes
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 31Public
InfoObject transitive attributes Modeling simple hierarchical relations – example 4
SNWD_EMPLOYEES
NODE_KEYPARENT_KEYEMPLOYEE_IDFIRST_NAMEMIDDLE_NAMELAST_NAMEINITIALSSEXLANGUAGEPHONE_NUMBERFAX_NUMBERMOBILE_NUMBEREMAIL_ADDRESSLOGIN_NAMEPR_ADDRESS_GUIDVAL_START_DATEVAL_END_DATECURRENCYSALARY_AMOUNTACCOUNT_NUMBERBANK_IDBANK_NAMEEMPLOYEE_PIC_URL
VARBINARY(16)VARBINARY(16)NVARCHAR(10)NVARCHAR(40)NVARCHAR(40)NVARCHAR(40)NVARCHAR(10)NVARCHAR(1)NVARCHAR(1)NVARCHAR(30)NVARCHAR(30)NVARCHAR(30)NVARCHAR(255)NVARCHAR(12)VARBINARY(16)NVARCHAR(8)NVARCHAR(8)NVARCHAR(5)DECIMAL(15,2)NVARCHAR(10)NVARCHAR(10)NVARCHAR(255)NVARCHAR(255)
<pk><fk1>
<fk2>
Source master data e.g. EMPLOYEES references itself via PARENT_KEY – what means a hierarchical relationship
If a navigational attribute addresses the same master data like the Characteristic itself proceed as follows:
• Create Reference-Characteristic for PARENT_KEY (IEMP_PAR) that references the Employee-Characteristic (IEMP_ID) .
• Define the navigational Attributes of the Characteristic IEMP_PAR (PARENT_KEY) as transitive Attributes
• Use again reference-characteristics to define the wanted transitive attributes
SourceInput DataSource
Business Integrated DWH/Propagation Layer
Business Integrated DWH/Propagation Layer
reference
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 32Public
InfoObject transitive attributes Modeling simple hierarchical relations – example 4
From ‘parent’ InfoObject IEMP_PAR referencing
IEMP_ID
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 33Public
BW dynamic dimensional modelingBW dynamic star schemas – snow-flaked dimensions
FACT Table
Dimensione.g. Product
BW on HANADynamic Star Schema
InfoObject
InfoObjects/ Fields
Nav. Attributes
Open ODS View Master
Nav. Attributes
Nav. Attributes
InfoObject
Nav. Attributes
Dimensione.g. Customer
Dimensione.g. Location
DimensionTime
DSO (advanced), DB-Table/ View,InfoCube, DSO (classic),
Open ODS View Master
CompositeProvider/ Open ODS View Type Fact
Keywords:
• Persistency's defined by InfoObjects or Fields
• Star Schema defined by a BW View i.e. CompositeProvider/ Open ODS View type fact
• Flexible and Agile Association of Dimensions i.e. InfoObject or Open ODS View type master
• Partitioned/ split Dimensions
• Snow-flaked Dimensions (transitive attributes)
InfoObject
Nav. Attributes
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 34Public
Modeling InfoObject dimensions De-normalized or snow-flaked via transitive attributes?
de-normalized Product Dimension
normalized snow-flaked Product Dimension
Any mixture of de-normalized and normalized snow-flaked modeling
As always: this is not a black or white question!
Modeling snow-flaked InfoObject dimensions make sense • If foreign key entities and its attributes have different owners/ volatility compared to the direct attribute of the primary-key of the Dimension / InfoObject
• Keeping the core-dimension-table (InfoObject) slim, transparent and maintainable
• If the value of de-normalizing foreign key attributes isn‘t obvious in the business context of a dimension e.g. Employee that created a Product and her/ his privat address
Modeling snow-flaked InfoObject dimensions make little sense • If the values of the foreign-key attributes will not change
• Should be carefully examined if the cardinality of the primary key is high
Snow-flaking dimensions is a flavor of virtualization compared to a de-normalized Dimension. A de-normalized dimension means nothing else than materialized joins. Snow-flaking dimensions means joining the data building the dimensions at run-time.
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 35Public
Simplification – InfoObjects supporting transitive attributesNew with SAP BW 7.5 SP4 – a BW roadmap slide
Transitive Attributes for InfoObjects Add navigational attributes of one InfoObject as navigation attributes to
another InfoObject Ability to extend a star schema to snow flaking
• At present two levels are allowed
Increased Flexibility, Maintainability, Less Redundancy Changes in parent InfoObjects do not affect children Easier data provisioning / staging to InfoObjects (e.g. 3NF sources) and
advanced DataStore Objects Usage in CompositeProvider and Open ODS View to avoid redundancy
0COSTCENTER 0COMP_CODE 0PROFIT_CTR 0PCA_DEPART
0PROFIT_CTR 0RESP_USER 0PCA_DEPART
InfoObject Nav. Attr.
Nav. Attr.InfoObject Transitive Attribute
(joined at runtime)
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 36Public
BW on HANA dynamic dimensional modelBW dynamic star schema – flexibility through split/ partitioned dimensions
BW on HANA Dynamic Star Schema
Define Dimensions
BW managed:• Advanced DSO dominated by InfoObjects or BW-type compatible Fields
BW managed:InfoObject tables
CompositeProvider Open ODS View
BW managed:• Advanced DSO dominated by fields• HANA Upsert/ Insert-table of BW
HANA DataSource
Define Facts
Open ODS View InfoObject
De-normalizedSplit/ partitioned/ SatellitesSnow-flaked/ transitive Attr.
pers
iste
d da
ta
Logical PartitionedData w. Aging (NLS)
Just dataModeled data
Foreign managed:• Local HANA tables/ DB-views
(mix scenarios)
Foreign managed:• Remote/ virtual tables/ DB-views
(federation scenarios)
Raw DWH Layer / Open ODS Layer / Data InHub/ Data Lake/ Source Layer
Business Integrated DWH Layer / Propagation Layer
Temporal join
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 37Public
Complex Business Entities The need for flexible master data modeling
Customer example:Challenges of master data modeling: • Semantically different understanding of a Material
PBG – Product corporate PRD – Sales Product ART – Article and a lot more: techn. materials, packaging ....
• Elements of material hierarchy as material• Multitude of attributes
UB – Operating Division
BU – Business Unit
SBU – Strategic Business Unit
MG – Main Group
AC - Sub Group
Complex Business Entities
Pretty clear: De-normalize all Attributes into a single InfoObject is no solution!
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 38Public
Product Dimension
Dimension_Y
Dimension_X
BW on HANA dynamic star schema – Split/ partition dimensions using InfoObjects – build dimension satellites* - example 5
InfoObject_1 NAV_ATTR_1A
Dynamic Star Schema modeling
InfoObject_2 NAV_ATTR_2A NAV_ATTR_2B
BW Dynamic Star Schema – Multiple InfoObjects form a Split/ Partitioned Dimension:
InfoObjects
NAV_ATTR_2C
NAV_ATTR_1B
IPD_TEC IPD_SIZE ..
DSO (advanced) (DSO (classic), InfoCube)
Composite Provider
F_InfoObject_1 F_InfoObject_2 IPD_SNOWF_1
F_Key_Fig_1 F_Key_Fig_N
IPD_SNOWF_2
IPD_SNOWF IPD_CAT IPD_MCAT• Multiple InfoObjects associated to same source InfoObject create a split / partitioned dimension (satellites)
Dimensions/ Master Data Modeling
persisted data modeling
InfoObjects -Generated tables
InfoObject_1
InfoObject_2
Key_Fig_1 Key_Fig_N
IPD_SNOWF
Product Dimension Satellites
*The term ‘Satellites’ was introduced by Dan Linstedt
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 39Public
Managing complex master data through partitioned/ split dimensionsMultiple InfoObjects for same DWH entity – create dimension satellites - example 5
Better: split/ partition the Dimensioncreating a new BW master data object –
here a new InfoObject
new attributes for Product arrive from
different owner
Integrate new attributes into existing Dimension
(InfoObject) i.e. de-normalize further ? Snow-flaking does not help
• A CompositeProvider allows mapping (advanced DSO) source InfoObjects or Fields to multiple CompositeProvider target fields . Each CompositeProvider target field allows associating different InfoObjects together forming a split / partitioned dimension
Scenario area CompositeProvider
Model InfoObjects
SourceModel
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 40Public
Managing complex master data through partitioned/ split dimensionsCompositeProvider addressing multiple InfoObjects for same DWH entity - example 5
• A CompositeProvider allows mapping (advanced DSO) source InfoObjects or Fields to multiple CompositeProvider target fields . Each CompositeProvider target field allows associating different InfoObjects together forming a split / partitioned dimension
Product DimensionPart: InfoObject IPD_SNOWF with Transitive Attribute Main Category
Output area CompositeProvider
PreviewCompositeProviderSatellite with
commercial data
Satellite with technical data
Product DimensionPart: InfoObject IPD_TEC with
Navigational Attribute Product Size technical
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 41Public
BW dynamic star schema and business integrated layer flexibilityPartition/ split dimension into dimension satellites using InfoObjects
Attributes of a DWH entity may behave differently caused by different owners and different business requirements• Storing attributes with different behavior together in a
single dimension (InfoObject) may impact overall stability, maintenance and availability
• In this case you should examine splitting/ partitioning the dimension creating Dimension Satellites using multiple InfoObjects
Summary: We can achieve flexibility with respect to Master Data introducing new attributes without impacting existing
• Persisted data (downtime)
• Data Flows and transformations (stability, testing)
Snow-flaking (transitive Attributes) and splitting dimensions into dimension satellites means that we invest gained HANA performance into flexibility through virtualization, joining dimension data at query run-time
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 42Public
BW dynamic star schema and business integrated layer flexibility Introducing n:m relations at any time with minimal impact - example 6
What's about adding new attributes to a combination of DWH-entities i.e. adding attributes to an n:m relation between two Entities?
Example: you have PRODUCT and BUSINESS_PARTNER in the fact table
• Product has attributes
• Business-Partner has attributes
And we now we want to add attributes at PRODUCT - BUSINESS_PARTNER level e.g. DISCOUNT– How to do it without impacting existing persisted data and data flows of the Business Integrated/ Propagation Layer?Pretty simple: • Create a new InfoObject that compounds PRODUCT with BUSINESS_PARTNER and define the new Attributes like
DISCOUNT as navigational attribute and load the InfoObject• In a CompositeProvider add PRODUCT a second time (split dimension!) as target and associated the new Compound-
InfoObject – that’s it!
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 43Public
BW dynamic star schema and business integrated layer flexibility Introducing n:m relations at any time - example 6
Compound InfoObject IPDBP_ID Modeling n:m relationship between Product & B-Partner
On CompositeProvider-level virtual integration
of new compound InfoObject IPDBP_ID like any other split-dimension satellite is done
without touching any existing persistency (advanced DSO(s), InfoObject(s)) !
InfoObjects related to Product
InfoObjects related to B-Partner
InfoObjects related to Product & B-Partner
CompositeProvider
Sources
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 44Public
BW dynamic star schema and business integrated layer flexibility Introducing n:m relations at any time without changing existing persisted data or data flows - example 6
Product DimensionInfoObject IPD_SNOWF with
Transitive Attribute Main Category
Product-B-Partner DimensionNew InfoObject IPDBP_ID
On CompositeProvider-level virtual integration of new Compound InfoObject IPDBP_ID is done like any other split Dimension satellite without touching any existing persistency or flow!
Output area CompositeProvider
PreviewCompositeProvider
Associate compound InfoObjectIPDBP_ID
Public
Dynamic dimensional model for BW on HANA – business and pattern-driven HANA DWBuilding agile extensions of the DWH core integrating any raw/ field data
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 46Public
BW on HANA dynamic modelingAgility through cross layer solution modeling – from DWH to a Business Analytics Platform
So far we learned about the Flexibility modeling the Business Integrated/ Propagation Layer with BW on HANA (…LSA++ for Simplified Data Warehousing)
• A stable, consistent and flexible Core-DWH follows a Top-Down modeling approach designing first the InfoObjects and followed by Dimensions and Facts
The Core-DWH is the fundament transforming a DWH into a Business Analytics Platform i.e. enabling new solutions combining, integrating and orchestrating data across layers.
Extending the Core-DWH towards a Business Analytics Platform we need the agility of tools (BW & HANA) and processes that support
• Combination, integration, orchestration of any data with this Core-DWH
• Scalability reaching from ‘on short notice’ to ‘persisted’ integration
In short – we need a Bottom-Up Logical Data Warehousing strategy (… LSA++ for Logical Data Warehousing)
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 47Public
SAP HANA DW and Business Analytics PlatformEmancipation of data, communication, integration, orchestration
SAP HANA promotes a DWH Core that supports
1. Flexibility extending the persisted DWH
2. Agility virtually extending the persisted DWH
3. Direct Analytics on DWH layers – no explicit Data Mart Layer
4. Virtual Combination of DWH layers – reduce redundancies
5. Virtual Combination of DWH with remote data (federation, the Business Analytics Platform)
6. Evolutionary Data Warehouse – complement Top-Down solutions with Bottom-Up approach - service level driven
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 48Public
From DWH to Business Analytics PlatformThe Logical Data Warehouse perspective of LSA++ for SAP HANA DW
Data In-Hub/ ODS Raw DWHBusiness
Integrated DWH
Data Lake Analytical Area/Virtual Solution
Communication Communication Communication Communication Communication Communication
Communication Communication Communication Communication Communication Communication
Communication, Integration & OrchestrationSAP HANA & BW Services
Communication, Integration & OrchestrationSAP HANA & BW Services
Non hierarchical, loosely coupled Information Areas
Clear service definitions
Communication, Integration, Orchestration rules
ERP, S/4HANA
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 49Public
BW on HANA dynamic dimensional modelDynamic - being flexible & agile – agility through cross layer modeling & virtual integration
BW on HANA Dynamic Star Schema
Define Dimensions
BW managed:• Advanced DSO dominated by InfoObjects or BW-type compatible Fields
BW managed:InfoObject tables
CompositeProvider Open ODS View
BW managed:• Advanced DSO dominated by fields• HANA Upsert/ Insert-table of BW
HANA DataSource
Define Facts
Open ODS View InfoObject
De-normalizedSplit/ partitioned/ SatellitesSnow-flaked/ transitive Attr.
pers
iste
d da
ta
Logical PartitionedData w. Aging (NLS)
Just dataModeled data
Foreign managed:• Local HANA tables/ DB-views
(mix scenarios)
Foreign managed:• Remote/ virtual tables/ DB-views
(federation scenarios)
Raw DWH Layer / Open ODS Layer / Data InHub/ Data Lake/ Source Layer
Business Integrated DWH Layer / Propagation Layer
Temporal join
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 50Public
Recall: Modeling functions and integration with BW on HANA Cross layer modeling - integrating top-down and bottom-up modeling
Integration modeling Map fields to InfoObjects
Function modeling Queries Schemas
Persistencies, Staging
Integration modeling
InfoObjects Fields
HANA BW Modeling OptionsIntegration before Function Function before Integration
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 51Public
Making BW on HANA a Business Analytics PlatformSteps integrating data from any layer/ location with BW dynamic dimensional model
Expanding an existing BW dynamic dimensional model of Business Integration (Propagation) Layer based on InfoObjects with any data from any layer (local or remote):
• Addressing data• remote via BW HANA-DataSources and HANA Smart Data Integration
• local via BW HANA-DataSources
• Semantics and model integration • Open ODS Views type fact, master or text
• Physical integration • BW HANA DataSources managed replication of remote data (Upsert-, Insert-Tables)
• Advanced DSO using Fields/ InfoObjects
• Transformations i.e. prepare raw data for use with DWH context data• BW Transformations and Structures (DataSource, InfoSource, advanced DSO)
• HANA/ sql transformations - flavor of Mixed Scenarios
• HANA SDQ (Smart Data Quality) - flavor of Mixed Scenarios (not in focus of lecture)
• Or a combination (clear guidelines necessary!)
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 52Public
Query/ CompositeProvider / HANA Model
Table
SQL/ HANA View
HA
NA
DBTable TableaDSO View/Table
aDSO
Recall: Open ODS Views & BW dynamic dimensional modelModeling raw/ field data with Open ODS Views
Query/ CompositeProvider / HANA Model
OpenODS View Master data
InfoObject
Raw
DW
H
Inte
gra
ted
D
WH
OpenODS View Master data
OpenODS View Fact data
InfoObjectMaster data
Virtual Data Mart
View/Table
The BW metadata model for field data consists of entities – the Open ODS Views – defining
– Semantics of sources
(fact, master.. data) – Semantics of source-fields
(characteristic, key figure,…)– Associations to other Open ODS Views – Associations to InfoObjects
ODS Views are view constructs on various types of source objects
– BW aDSOs/ InfoSource/ DataSources– DB tables & SQL/ HANA views– Virtual tables -HANA Smart Data Access
The source object of an ODS view can be exchanged
From a BW-OLAP perspective, ODS Views can be consumed like InfoProviders (facts) or InfoObjects (master data, text)
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 53Public
BW on HANA dynamic dimensional modelWhat means dynamic? Being flexible & agile
Modeling a Dimension in BW on HANA
Defined by one or any combination of
1. InfoObject + Navigational Attributes
2. InfoObject + Navigational Attributes + (Navigational Attributes of a Navigational Attribute) – snow-flaking / transitive Attributes
3. Splitting Dimensions in multiple InfoObjects and/ or Open ODS Views
4. Open ODS View type master on aDSO w. Fields/ InfoObjects (Raw Layer)
5. Open ODS View type master on Table/ DB-view (replicate, Data InHub)
6. Open ODS View type master on HANA virtual table (remote/ federation)
Source
Flexible &
Agile
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 54Public
Integrating data from any layer using Open ODS ViewsModel & semantics integration: introduce new n:m relationship virtually - example 7
CompositeProvider
Open ODS View type master
Fact dataAdvanced DSO
Source
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 55Public
Integrating data from any layer using Open ODS ViewsModel & semantics integration: introduce new n:m relationship virtually - example 7
Product-B-Partner DimensionOpen ODS View O_PD_BPA_V
On CompositeProvider-level virtual integration of a table or DB-view via an Open ODS View O_PD_BPA_V
is done fully virtually like with any other split Dimension satellite without touching any existing data or flow!
New relations 1:n, n:m or just new attributes can be introduced at any time with no impact on existing solutions!
Product DimensionInfoObject IPD_SNOWF with
Transitive Attribute Main Category
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 56Public
BW dynamic dimensional modeling and Open ODS ViewsAgile data integration across layers via Open ODS Views as Dimension Satellites (Split/ Partition)
• Attributes of an entity may reside in different layers
• The BW Dynamic Model split dimension pattern allows addressing multiple persisted dimension satellites
• Dimension persistencies described by fields (table, DB-View, advanced DSO) are integrated via Open ODS Views
Open ODS ViewO_PD_BPA_V
CompositeProviderIPD_ID_1
IPD_ID_2
Attributes
INFOOBJECTIPD_SNOWFDSO (advanced)DSO (advanced)
PRODUCT: IPD_SNOWF PRODUCT: IPD_SNOWF
……
Analytic Area
Product Dimension
Business Integrated DWH Layer/Propagation Layer
Attributes
Product Dimension Satellites
DSO (advanced) / Local/ remote table/view
DSO (advanced) / Local/ remote table/view
RAW DWH Layer/ Open ODS/ Data In-Hub/ Source
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 57Public
Making BW on HANA a Business Analytics PlatformSteps integrating data from any layer/ location with BW dynamic dimensional model
Expanding an existing BW dynamic dimensional model of Business Integration (Propagation) Layer based on InfoObjects with any data from any layer (local or remote):
• Addressing data• remote via BW HANA DataSources and HANA Smart Data Integration
• local via BW HANA DataSources
• Semantics and model Integration • Open ODS Views type fact, master or text
• Physical Integration • BW HANA DataSources managed replication of remote data (Upsert-, Insert-Tables)
• Advanced DSO using Fields/ InfoObjects
• Transformations i.e. prepare raw data for use with DWH context data• BW Transformations and Structures (DataSource, InfoSource, advanced DSO)
• HANA/ SQL View transformations - flavor of Mixed Scenarios
• HANA SDQ (Smart Data Quality) - flavor of Mixed Scenarios (not in focus of lecture)
• Or a combination (clear guidelines necessary!)
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 58Public
DB-View Transform: Integration Joins, any Transform
BW on HANA transformation options for virtual data integrationVirtual integration between Business Integration Layer and Raw / Source Layers
Tables remote or local <Fields>
BW DataSource<Fields>
BW InfoSource<Fields>/ <InfoObjects>
BW advanced DSO<Fields>/ <InfoObjects>
BW advanced DSO<InfoObjects>
CompositeProvider
Master/ Fact Open ODS View<Fields>/ <InfoObjects>
InfoObject<InfoObjects>
SQL/ HANAViews
SQL/ HANA Views
virtual integration
Raw DWH /Open ODS Layer
Business Integrated DWH/Propagation Layer
InHub/ Inbound /Source
DataSource Transform : Integration Type/ Length
Open ODS View Transform - we do our best
Options performing data Transformations integrating virtually Raw DWH/ Open ODS Layer or source level data with Business Integrated DWH/ Propagation Layer data using Open ODS Views
DB-View Transform: Integration Joins, any Transform
BW Transform: Integration Transforms
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 59Public
BW on HANA transformation options for virtual data integrationVirtual integration between Business Integration Layer and Raw / Source Layers – example 8
• Join tables• Provide key-mappings from
source to DWH-key e.g.• Raw key (NODE_KEY ->
PRODUCT_ID)• EMPC_CREATED_BY ->
EMPC_EMPLOYEE_ID• Provide transforms – sql-cast
SQL/ HANAViews
BW DataSource<Fields>
• Provide BW DWH Business transforms e.g.• CUKY, CURR, DATS, UNIT,..
• Provide generic transforms e.g.• DEC -> CHAR …
Tables remote or local <Fields>
• Provide generic transforms• Provide DWH semantics and
associations• Be part of Dynamic Dimensional
Model
Master/ Fact Open ODS View<Fields>/ <InfoObjects>
a date
The DWH-key
a currency
a currency-code
Join
key-mapping
delivered
Access tables directly
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 60Public
BW on HANA transformation options for virtual data integrationVirtual integration between Business Integration Layer and Raw / Source Layers – example 8
Tables SQL-View BW DataSource Master Open ODS View Dimension (Satellite) in CompositeProvider
CompositeProvider
Satellite part of Business Integrated DWH
Satellite part of Source
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 61Public
Key transformations for virtual data integrationKey mapping between layers is the prerequisite for integration
CompositeProvider
Open ODS View
aDSO – master data
aDSO- key info
HANA Viewmaster data aDSO
HANA ViewJoin and/ or do
mapping of Raw keys to Business
keys
Business Integrated DWH/
Propagation Layer
Raw DWH /Open ODS Layer
Virtual mapping of Raw DWH keys to Business Integrated DWH keys
• HANA View for complex integration transformations between Business Integrated DWH and Raw DWH (Mix Scenario)
• Simple key-mappings with provided in an aDSO or InfoObject can be handled via CompositeProvider and Open ODS View
What is the difference to storing the key-mapping information persisted with Business Integrated data or Raw DWH data?
• If you want to become mapping changes immediately effective to all data – virtualization is the solution
• If you want to keep the key-mapping for already loaded data – persist the key mapping or go for temporal joins (Slowly Changing Dimensions Type 2)
Open ODS View
Simple map -BW Scenario
Open ODS View
HANA ViewOn Key aDSO
HANA ViewJoin
Simple map – mix Scenario
associate
join
Generated HANA Views
complex map – mix Scenario
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 62Public
Key transformations for virtual data integration – Example 9
aDSO – master data
aDSO - key info
Ra
w D
WH
/O
pe
n O
DS
La
ye
r
Business Integrated Key IPD_ID
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 63Public
Key transformations for virtual data integration – Simple transform using CompositeProvider - Example 9.1
aDSO - key info
aDSO – master data
join
Associate Open ODS View
Raw DWH /Open ODS Layer
JoinTransform
Semantics/ model
transform
Create Open ODS View
Integrate
CompositeProviderassociating
raw Product Satellite to mapped keys
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 64Public
Key transformations for virtual data integration – Simple transform using HANA Views - Example 9.2
Generated HANA Views
aDSO – master data
aDSO - key info
Generated HANA Views
"_SYS_BIC"."PM_T_2016/R_I_PD_MAP_VIEW"
Generated SQL View
Create Open ODS View
CompositeProviderassociating
raw Product Satellite with mapped keys
Key mapping establishedIn Open ODS View
JoinTransform
Semantics/ model
transform
Integrate
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 65Public
Key transformations for virtual data integration
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 66Public
Making BW on HANA a Business Analytics PlatformSteps integrating data from any layer/ location with BW dynamic dimensional model
Expanding an existing BW dynamic dimensional model of Business Integration (Propagation) Layer based on InfoObjects with any data from any layer (local or remote):
• Addressing data• remote via BW HANA DataSources and HANA Smart Data Integration
• local via BW HANA DataSources
• Semantics and model Integration • Open ODS Views type fact, master or text
• Physical Integration • BW HANA DataSources managed replication of remote data (Upsert-, Insert-Tables)
• Advanced DSO using Fields/ InfoObjects
• Transformations i.e. prepare raw data for use with DWH context data• BW Transformations and Structures (DataSource, InfoSource, advanced DSO)
• HANA/ SQL View transformations - flavor of Mixed Scenarios
• HANA SDQ (Smart Data Quality) - flavor of Mixed Scenarios (not in focus of lecture)
• Or a combination (clear guidelines necessary!)
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 67Public
SAP HANA
SAP BW
HANA
Remote Source
Table/View
Smart Data Integration
Smart Data Access
AdvancedDSO
HANA DataSource
DIRECTACCESS
OpenODS ViewVirtual
CompositeProviderVirtual
REAL TIME Streaming
Table/View
REPLICATION
Platform Integration – HANA SDINew with SAP BW 7.5 SP4
67Public© 2016 SAP SE or an SAP affiliate company. All rights reserved.
Integration Scenarios with SAP BW – General Availability• Direct access to any Smart Data Integration (SDI) remote source*
via Open ODS View• SDI real-time replication managed by HANA DataSource
• UPSERT table for actual view / INSERT table for history preserving
• Replicate source data in original format to BW (using HANA data types)
• Inbuilt mapping of source data types to ABAP data types when using HANA DataSource in reporting or ETL loads within BW
• Direct access from OpenODS View to HANA DataSource
• Full / delta upload into advanced DataStore Objects
• Real-time streaming from UPSERT / INSERT table into advanced DataStore Object possible
• SAP HANA Multi-tenant Database Container (MDC) support for HANA DataSource (SAP BW 7.5 SP5, BWMT 1.15)**
• SAP HANA DataSource Integration with Streaming Process Chains
UPSERT Table
* See Documentation: Data Provisioning Adapters** See SAP Note 2312583
INSERT Table
Public
Composition Model for SAP HANA SQL DW – customer-defined HANA DW
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 69Public
Selecting a data model for HANA SQL-DW
3NF Model
Data Vault model
Composition model
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 70Public
Extremely different ways modeling a DWH Dimensional model (Kimball) and data vault model (Lindstedt, Inmon)
SNWD_PD_DE_NORMALIZED
NODE_KEYPRODUCT_IDTYPE_CODECREATED_BYCREATED_ATCHANGED_BYCHANGED_ATNAME_GUIDDESC_GUIDSUPPLIER_GUIDTAX_TARIF_CODEMEASURE_UNITWEIGHT_MEASUREWEIGHT_UNITCURRENCY_CODEPRICEPRODUCT_PIC_URLWIDTHDEPTHHEIGHTDIM_UNITDUMMY_FIELD_PDCATEGORYMAIN_CATEGORYBP_ROLEEMAIL_ADDRESSPHONE_NUMBERFAX_NUMBERWEB_ADDRESSBP_IDCOMPANY_NAMELEGAL_FORMEMPLOYEE_IDLAST_NAMEFIRST_NAMEEMAIL_ADDRESS2PARENT_KEYNODE_KEY2NODE_KEY3LANGUAGENODE_KEY4TEXT
VARBINARY(16)NVARCHAR(10)NVARCHAR(2)VARBINARY(16)DECIMAL(21,7)VARBINARY(16)DECIMAL(21,7)VARBINARY(16)VARBINARY(16)VARBINARY(16)SMALLINTNVARCHAR(3)DECIMAL(13,3)NVARCHAR(3)NVARCHAR(5)DECIMAL(15,2)NVARCHAR(255)DECIMAL(13,3)DECIMAL(13,3)DECIMAL(13,3)NVARCHAR(3)NVARCHAR(1)NVARCHAR(40)NVARCHAR(40)NVARCHAR(3)NVARCHAR(255)NVARCHAR(30)NVARCHAR(30)NVARCHAR(255)NVARCHAR(10)NVARCHAR(80)NVARCHAR(10)NVARCHAR(10)NVARCHAR(40)NVARCHAR(40)NVARCHAR(255)VARBINARY(16)VARBINARY(16)VARBINARY(16)NVARCHAR(1)VARBINARY(16)NVARCHAR(255)
Fo
reig
n k
eys
poin
t to
ent
itie
s
‘No‘ foreign keys
foreign keys modeled as entities (links – red tables)
transactional Hub data
tran
sactio
na
l/fa
ct da
ta
‘split‘ data-atomize
Dimensional modeled persisted DWH
Data Vault modeledpersisted DWH
‘join’ data – de-normalize
3NF modeled –source data
What DWH model fits bestto SAP HANA DWH ?
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 71Public
HANA SQL DW - selecting a DWH data modelCustomer preferences – situation - requirements
De
-n
orm
aliz
ed
Ato
mic
Dimensional Composition Data Vault
SQL-DW Data Vault Fully traceable and auditable Full history tracking Full flexibility Need of persisted Star Schemas
- Agility ? Complexity ?
Composition Model for HANA SQL-DW Query-able DWH Customer-defined services Scalable flexibility
Dimensional Model High Performance DWH-Querying Business oriented
BW Dynamic Dimensional
scalable Composition model
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 72Public
Composition model for HANA SQL-DW Introduction
The composition model follows the SAP HANA direction minimizing persisted data in our landscape i.e. Enable direct querying on any persisted DWH layer Avoid persisted Star schema creation on top of persisted DWH layer Be pragmatic with respect to usage and degree of DWH services &
patterns (-> customer decisions) History/ Versioning Auditability Flexibility (e.g. usage of surrogate keys, degree of normalization) …
Be scalable with respect to later introduction of DWH services through virtualization
Satellites – split attributesSatellites – split attributesSatellites – split attributesSatellites – split attributes
Satellites – split attributesSatellites – split attributes
Entity – the key(s)Entity – the key(s)
The composition model for a HANA SQL-DW is a pragmatic modeling approach Combining strengths of the dimensional model with other modeling approaches (e.g. data vault) Having business requirements in focus instead of theoretical paradigms
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 73Public
Composition model for HANA SQL-DWEntity–satellite* pattern design basics
Ownership:Volatility - Stability
History –Snapshot
Federated - Repository Bottom up (incremental) –
Top down (comprehensive)
High volume -Normal
Entity-Satellite Design Drivers
Raw (Source Key) - Integrated (Business Key/
Surrogate Key)
Normalized (flexible) – De-normalized (agile)
Real time -DWH services
Entity as root:• Query/ retrieval• Consistency• Load synchronization• Integration
Satellites – split attributesSatellites – split attributes
Satellites – split attributesSatellites – split attributes
Satellites – split attributesSatellites – split attributes
Entity – the key(s)Entity – the key(s)
DWH Design Drivers
*The term ‘Satellites’ was introduced by Dan Linstedt
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 74Public
Modeling the HANA SQL-DW with composition modelExample A – SNWD source model
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 75Public
Modeling the HANA SQL-DW with composition modelExample A – from 3NF to composition model
3NF model tables composition model tables
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 76Public
BK-PK11 BK-PKA FROM TO time-dependent
Attr13 Attr14BK-PK11 BK-PKA FROM TO time-dependent
Attr13 Attr14
Composition model for HANA SQL-DWEntity-satellite pattern
BK-PKA
technical attributes
entity ‘A‘ entity table
BK-PKA FROM TO time-dependent attributes
A1 A2 A3 A4 BK-PKB BK-PKC C1 BK-PKD
entity ‘A‘ satellite tables (time-dependent)
BK-PKA not time-dependent
Attr11 Attr12 BK-PKXBK-PKA not time-dependent
Attr11 Attr12 BK-PKXBK-PKA not time-dependent attributes
A5 A6 A7 A8 A9 BK-PKX X1 X2 X3
entity ‘A‘satellite tables (not time-dependent)
Entity B Entity D
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 77Public
Composition model for HANA SQL-DWEntity-satellite pattern – entity tables
Entities define the subjects like Materials, Employees, Sales-Orders … and get an own entity table
• Entity tables store field(s) that define the business key as primary key
• DWH Surrogate-keys may be used
• Technical Fields
• Origin
• Date when inserted
• Once inserted these fields are never changed or deleted
Special entity design options
• n:m relationships between entities
• E.g. Material on Plant level
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 78Public
Composition model for HANA SQL-DWEntity-satellite pattern – satellites tables
The composition model seperates the attributes of an entity from the entity keys
• Satellites tables store the attributes of an entity
• Attributes of core entities should be stored in different satellite tables taken ownership, volatility, …. into account
• The satellite tables inherit the primary key of the entity
• Satellites are either of type Snapshot or Versioned
• Versioned Satellite tables have in addition a Valid_From field
as part of the primary key
• 1:n relations are stored as foreign-keys in satellites
• 1:n relations may be stored as dedicated entity table
• The field DWH_ACTIVE_RECORD addresses the actual record
• Records are never deleted (‘reset’ DWH_ACTIVE_FROM)
Satellite-Snapshot Satellite-Snapshot
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 79Public
Composition model for HANA SQL-DWEntity-satellite pattern – special aspects
• History/ versions for attribute needs always a satellite table
• Actual snapshot attributes may be stored in the entity table for simplicity reasons (transaction data entities for example)
Satellite-Snapshot
Satellite-Snapshot
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 81Public
• Versioned and snapshot data ?• Volatile situation, core entity ? Entity + snow-flaked versioned and snapshot satellites
• Versioned data ? Add a versioned satellite:
• Just actual data (snapshots) ? Add a snapshot satellite:
Evolutionary DWH design with composition modelStart simple and evolve – minimize impact of model changes
• Just actual data (snapshots) ?• Stable situation, simple entity ? A simple dimension table will do:
new attrib utes arriv e
new attributes arrive
requirements increase
challenging requirements
Start simple and evolve
two
basi
c op
tions
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 82Public
Virtual data marts with calculation views on composition modelExample – querying the DWH layers
EPM_BUSINESS_PARTNER_DIM_ACT
EPM_STAR_SO_HDR
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 83Public
Composition model for HANA DW BW on HANA takes over management of entities and satellites
3NF Model
Composition Model with BW on HANA
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 84Public
BW on HANA takes over management of entities and satellitesCalculation views build virtual data marts
BW_BUSINESS_PARTNER_DIM_ACTComposition model with BW on HANA
Generated Views on Composition Model with
BW on HANA
Build Dimension & Fact Views on Composition
Model with BW on HANA
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 85Public
Composition model for HANA DWVirtual data marts on SQL-DW and BW on HANA
Star Schema on Composition Model on HANA SQL-DW
Star Schema on Composition Model on BW on HANA
… the result is the same …
Public
Summary
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 87Public
SAP HANA DW data model Streamlining proven DWH services – use the value of virtualization - summary
SAP HANA DW data model direction:
Evolving a DWH to a Business Analytics Platform
• The BW on HANA dynamic dimensional model and
• The composition model for SAP HANA SQL-DW
Both modeling approaches support
1. A flexible persisted DWH capable absorbing changes with minimal impact
2. An agile DWH capable integrating virtually any data – local or remote
3. Direct Analytics on DWH layers – no explicit Data Mart Layer
4. Virtual Combination of DWH layers – reduce redundancies
5. Evolutionary Data Warehouse in addition to core-DWH deployment – complement Top-Down solutions with Bottom-Up approach - service level driven
two sides of the same coin
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 88Public
Thanks for attending this session.
Please complete your session evaluation for DMM302.
Contact information:
Juergen [email protected]
Ulrich [email protected]
Feedback
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 89Public
Further information
Related SAP TechEd sessions:DMM265 – SAP HANA Data Warehousing: Introduction to Data Modeling in SAP HANADMM213 – SAP HANA Data Warehousing: Data Lifecycle Management and Data AgingDMM270 – SAP HANA Data Warehousing: Simplified Modeling with SAP BW 7.5 SP4DMM272 – SAP HANA Data Warehousing: Mixed Scenario for SAP BW and SQL DW on SAP HANADMM300 – Mixed Scenarios for SAP HANA Data Warehousing: Overview and Experiences
Hands-On WorkshopLectureHands-On WorkshopHands-On WorkshopLecture
SAP Public Webscn.sap.com www.sap.com
SAP Education and Certification Opportunitieswww.sap.com/education
Watch SAP TechEd Onlinewww.sapteched.com/online
© 2016 SAP SE or an SAP affiliate company. All rights reserved. 90Public
SAP TechEd Online
Continue your SAP TechEd education after the event!
Access replays of Keynotes Demo Jam SAP TechEd live interviews Select lecture sessions Hands-on sessions …
http://sapteched.com/online