Data Modeling - V1.0

8/6/2019 Data Modeling - V1.0

1/157

India SAP CoE, Slide 1

BI1001BI1001 -- Data ModelingData Modeling -- v1.0v1.0


2/157


1 PrepareMe

2 TellMe

3 ShowMe

4 LetMe

5 HelpMe

Data Modeling


3/157


1 PrepareMe

2 TellMe

3 ShowMe

4 LetMe

5 HelpMe

Data Modeling


4/157


Prepare Me

PurposePurpose

UseUse

ChallengesChallenges

THE GOALData needs to be converted intoInformation in such a manner

that the Decision Support

System can exploit it to the

fullest.

THE MEANSBW achieves this goal for the

Business community and

enhances the analyticalcapabilities afterExtracting,

Transforming and Storing data

from Operational source

systems.

Designing efficient

Data Model

helps achieve this goal

HOW?Shows this

course


5/157


Purpose

Data modeling is the backbone of the BW system.

It enables staging of information from large amount of

operative and historical data and permits multi-dimensional analysis according to various businessperspectives.

Thus the main purpose of data modeling is to structureand organize all the necessary data for business users for

the purpose of analysis.


6/157


Use Data Modeling serves as the tool for managing the middle

phase of data warehousing i.e. it helps managing thestaging and transforming phase of data warehousing lifecycle.

Its main use is to store the summarized data physically inorder to facilitate the BI reporting for the organization.

It helps shaping and transforming the data to make itsuitable for reporting.

It provides more flexibility in terms of data transformationas compared to the source system due to availability ofvarious methods of data transformation.


7/157


Challenges

Designing a complete and efficient data model that satisfiesthe business need is the most important factor to thesuccess of a BI implementation.

Identification of relevant business entities and theirrelationships is critical while designing a Data model.

Accurate mapping of the business entities with respect toBusiness content objects in BW.

Designing a comprehensive data model to support requisitereporting KPIs.


8/157


1 PrepareMe

2 TellMe

3 ShowMe

4 LetMe

5 HelpMe

Data Modeling


9/157


Introduction

A data model represents classes of business entities,A data model represents classes of business entities,their attributes, and relationships among thosetheir attributes, and relationships among thoseentities and (often implicit) relationships among thoseentities and (often implicit) relationships among thoseattributes.attributes.

In data modeling, we structure, organize and storeIn data modeling, we structure, organize and storedata for business reporting.data for business reporting.


10/157


Introduction

Process Flow

Data Models

ERM

MDM

Conversion From ERM to MDM

BW Data model

Star Schema

Extended Star Schema


11/157


Process Flow

PSA --> InfoSource-> Data Targets-> Infoproviders


12/157


Process Flow Data that logically belongs together is stored in the source systemData that logically belongs together is stored in the source systemin the form ofin the form ofDataSourcesDataSources. DataSources are used for extracting. DataSources are used for extracting

data from a source system and for transferring data into the BW.data from a source system and for transferring data into the BW.

TheThe PSAPSA is the input storage for data from the source systems inis the input storage for data from the source systems inSAP BW. The required data is then saved in the sources systems inSAP BW. The required data is then saved in the sources systems in

unchanged form.unchanged form.

AnAn InfoSourceInfoSource maps the individual fields of the DataSource to themaps the individual fields of the DataSource to therelevant InfoObjects. The data can then be transformed usingrelevant InfoObjects. The data can then be transformed usingtransfer rules.transfer rules.

Update R

ulesUpdate R

ules specify how the data is updated from an InfoSourcespecify how the data is updated from an InfoSourceinto a Data Targetinto a Data Target

Afterwards, the data can be updated to otherData targets /Afterwards, the data can be updated to otherData targets /InfoProvidersInfoProviders. The InfoProvider provides the data for evaluation in. The InfoProvider provides the data for evaluation inqueries.queries.


13/157


Data Models

The two Data Models relevant in Data Warehousing are:The two Data Models relevant in Data Warehousing are: ERM (Entity Relationship Model)ERM (Entity Relationship Model)

MDM (MultiMDM (Multi--Dimensional Model)Dimensional Model)

ERM produces a data model of the specific area ofERM produces a data model of the specific area ofinterest, using two basic concepts:interest, using two basic concepts: Entities andEntities and

Relationships between entitiesRelationships between entities

In M

DM information model consists of: Numerical facts (or key figures in BW terminology) and

Dimension attributes, meaning the business terms thatdescribe these facts


14/157


ER Model


15/157


The strong entities cover the whole

model. All other entities are

dependent on these strong entities.

ER Model


16/157


Multi-Dimensional Model


17/157


Multi-Dimensional Model

The Starschema is the most common way ofdisplaying the relations in an MDM and is thereforeoften used as a synonym for the MDM itself.

The facts (KPIs) are collected in a separate Fact tableand the dimensions (group of entities)(group of entities) are collected inthe Dimension tables.

The dimension tables are joined to the fact table byThe dimension tables are joined to the fact table byforeign key relationships and primary key relationshipsforeign key relationships and primary key relationships


18/157


19/157


BW Data Model : StarSchema


20/157


Dimension tables are

groupings of related

characteristics.

A dimension table

contains a generated

primary key and

characteristics.

The keys of the

dimension tables are

interpreted as foreign

keys in the fact table.

BW Data Model: StarSchema


21/157


The individual data records in the fact table are identified uniquely

by the keys of the dimension tables

The fact table is maintained when transaction data is loaded

Fact tables must have a relatively small number of columns (key

figures) and a large number of rows (data records)

Fact Table

BW Data Model: StarSchema


22/157


Drawbacks of StarSchema

Multilingual descriptions for attributes in the dimensionMultilingual descriptions for attributes in the dimensiontables are not supported.tables are not supported.

Secondary indexes for the data are stored asSecondary indexes for the data are stored as

alphanumeric fields in comprehensive tables. Thisalphanumeric fields in comprehensive tables. Thismakes it more difficult to access the data.makes it more difficult to access the data.

If attributes of the dimensions change over time, there isIf attributes of the dimensions change over time, there isno way of maintaining the old and new values for theno way of maintaining the old and new values for the

attribute.attribute.


23/157


Extended StarSchema


24/157


In contrast to the Classic

Star schema,

characteristics are not

components of the

dimension tables.

A numeric key SID

(surrogate ID) is generated

for each characteristic. It

replaces the characteristicas a component of the

Dimension table.

Extended StarSchema


25/157


Extended StarSchema

Each Dimension table has a system generated numeric'primary key' called the Dimension key ofDIM ID.

The primary key of the Fact table is then made up of the

combination ofD

IM ID

s of various dimensions.


26/157


Avoids data redundancy and maintains data consistency asmaster data is shared by all InfoCubes

Multi-lingual capability is possible

The use ofDimIDs and SIDs enable faster access of dataas compared to the alpha-numberic keys of the Classic Starschema

It is easy to model Slowly changing dimensions andHistorisation of data using time reference

Advantages of Extended Star

Schema


27/157


BW ObjectsBW Objects

The various structures provided for creating a DataThe various structures provided for creating a Data

Model in SAP BW are:Model in SAP BW are:

InfoObjectsInfoObjects

InfoCubeInfoCube

ODS (Operational DataStore)ODS (Operational DataStore)

MultiProviderMultiProvider

InfoSetInfoSet


28/157


InfoObjects

Business evaluation objects are known in BW as

InfoObjects. They are the basic information providers

of BW and the smallest information units in BW.

They structure the information needed to create

InfoCubes/ODS Objects.

InfoObject types:

Characteristics

Basic

Time

Technical

Key figures


29/157


Characteristics InfoObjects are business reference

objects which are used to analyze key figures such as

company code, material, product, customer group.

Key Figures InfoObjects provide values to be evaluated

such as quantity, amount, or number of items.

Types of InfoObjects


30/157


Time characteristics are characteristics such as date,fiscal year which are time indicators.

Units Characteristic are required so that the values for the

key figures have meanings. Key figures of type amount arealways assigned a currency key and key figures of typequantity also receive a unit of measurement.

Technical characteristics are used for administrative

purposes only within BW. An example of a technicalcharacteristic is the request number in the InfoCube. This isgenerated when you load a request as an ID and helpslocate the request at a later date.

Types of InfoObjects


31/157


MasterDataMasterData Attributes, Texts &Attributes, Texts &

HierarchyHierarchy Characteristics bear master data (i.e. attributes, texts

or hierarchies) in BW.

Attributes are InfoObjects that are logicallysubordinate to a characteristic.

Types of MasterData attributes Display Attribute Used for display purpose in report and

cannot be used for drill-down and filtering purpose. (for e.g.Material weight or Material colour)

Navigational Attribute Navigational attribute can be used forfurther analysis in reporting (e.g. Material group, Materialclass)


32/157


MasterDataMasterData Attributes, Texts &Attributes, Texts &

HierarchyHierarchy The description of the characteristic is stored asThe description of the characteristic is stored as

InfoObject text.InfoObject text. e.g. Material # 1000023456 has a description viz.e.g. Material # 1000023456 has a description viz.

"Metallic Black Part 1L."Metallic Black Part 1L.

A hierarchy forms a summarization and grouping for aA hierarchy forms a summarization and grouping for acharacteristic according to individual evaluationcharacteristic according to individual evaluationcriteria.criteria. e.g. Material can have the 4 levels of hierarchy ase.g. Material can have the 4 levels of hierarchy as

Product CategoryProduct Category --> Product class> Product class--> Material group> Material group-->>Material. These levels can be used in reporting forMaterial. These levels can be used in reporting forfurther drill down in datafurther drill down in data


33/157


InfoObject Catalog

An InfoObject catalog is a collection of InfoObjects

grouped according to application-specific criteria

Types of InfoObject catalog

Characteristic: This is the collection of Characteristics

InfoObjects

Key figure: This is the collection of Key figures

InfoObjects


34/157


InfoObject Catalog


35/157


InfoCube InfoCubes are central objects of multiInfoCubes are central objects of multi--dimensional model in SAPdimensional model in SAP

BW. Reports and analysis are based on InfoCubes.BW. Reports and analysis are based on InfoCubes.

An InfoCube describes an selfAn InfoCube describes an self--enclosed dataset for businessenclosed dataset for business--areaareafrom a reporting point of view.from a reporting point of view.

An InfoCube consists of several InfoObjects and is structured

according to the star schema.

An InfoCube is additive in nature, it aggregates data on the basis ofcharacteristics combinations.

Types of InfoCubesTypes of InfoCubes BasicBasic

Basic InfoCubeBasic InfoCube

Transactional InfoCubeTransactional InfoCube

Virtual InfoCubeVirtual InfoCube

Remote CubeRemote Cube

SAP Remote CubeSAP Remote Cube

Virtual InfoCube with ServicesVirtual InfoCube with Services


36/157


InfoCube


37/157


InfoCube

The characteristics determine the granularity (the degree ofdetail) at which the key figures are kept in the InfoCube.

Characteristics that logically belong together (district and area,for example, belong to the regional dimension) are grouped

together in a dimension.

An InfoCube can have maximum of 16 dimensions (3dimensions are provided by SAP i.e. Time, Data Package andUnit). A maximum of 256 characteristics can be included ineach dimension.

Dimension tables should be small with regards to data volume,which is desirable for reasons of performance.


38/157


Transactional InfoCube

A transactional InfoCube is a special Basic InfoCube,especially developed for SAP SEM.

The system accesses data in such a InfoCube istransactional, in other words, data is written to the InfoCube

and instantly read again when required.

e.g. In BW BPS, users enter plan data via planninglayouts which is in turn stored in TransactionalInfoCubes.

A standard Basic InfoCube is optimized for pure readaccess and hence not suitable here.


39/157


Virtual InfoCube

Virtual InfoCubes are special InfoCubes. A virtual InfoCuberepresents a logical view. Unlike Basic InfoCube, no data isphysically stored in BW. The data is taken from the sourcesystem only after a query has been executed.

SAP Remote Cube allows one to define queries with directaccess to transaction data in other SAP systems.

Remote Cube allows reporting using data from non-SAPsystems. the external system transfers the requested datato the OLAP processor via the BAPI.

Virtual InfoCube with Services requires a user-definedfunction module as a DataSource.


40/157


An ODS object (Operational DataStore) acts as astorage location for consolidated and cleaned-uptransaction data (transaction data or master data, forexample) on the document (atomic) level.

An ODS object contains key fields (for example,document number/item) and data fields that can alsocontain character fields (for example, order status,customer) and key figures.

Types of O

DS Object Basic O DS

Transactional ODS

ODS


41/157


ODS


42/157


43/157


Transactional ODS

A transactional ODS object differs from a standard ODSobject in the way it prepares data.

In a standard ODS object, data is stored in differentversions (active, delta, modified), whereas a transactionalODS object contains the data in a single version. Therefore,data is stored in precisely the same form in which it waswritten to the transactional ODS object by the application.

The transactional ODS object is also required by diverseapplication such as SAP SEM.

Transactional ODS objects allow data to be availablequickly. The data from this kind of ODS object is accessedtransactionally and read again, as soon as possible.


44/157


Data Targets

A data target is an object into which data is loaded.A data target is an object into which data is loaded.

Data targets are the physical objects that are relevantData targets are the physical objects that are relevant

during data modeling and when loading the data.during data modeling and when loading the data.

Data targets are:Data targets are:

InfoObjects (characteristics with attributes or texts)InfoObjects (characteristics with attributes or texts)

InfoCubeInfoCube

ODS ObjectsODS Objects


45/157


InfoObject as Data Target

InfoObjects with attributes or texts can themselves alsoInfoObjects with attributes or texts can themselves alsobe used as data target.be used as data target.

If you want to turn a characteristic into a Data target /If you want to turn a characteristic into a Data target /InfoProvider, you have to assign an InfoArea to theInfoProvider, you have to assign an InfoArea to thecharacteristic. The characteristic is subsequentlycharacteristic. The characteristic is subsequentlydisplayed in the InfoProvider tree in the Administratordisplayed in the InfoProvider tree in the AdministratorWorkbench.Workbench.

The data is then loaded into the master data tablesThe data is then loaded into the master data tablesusing the update rules.using the update rules.


46/157


Data Targets vs InfoProvider

Pure Data targets do not have queries based onPure Data targets do not have queries based onthem.them. An O DS object can be defined so that queries are notAn ODS object can be defined so that queries are not

possible (if the BEx Reporting indicator is not set in thepossible (if the BEx Reporting indicator is not set in theODS object maintenance). In this case, it is a pure dataODS object maintenance). In this case, it is a pure datatarget.target.

A transactional ODS object is always a pure data target,A transactional ODS object is always a pure data target,for which a BEx query cannot be defined directly, butfor which a BEx query cannot be defined directly, butonly by way of an InfoSet created for it.only by way of an InfoSet created for it.

Data targets for which queries can be defined. TheseData targets for which queries can be defined. These

are called InfoProviders.are called InfoProviders. Queries can always defined with Basic InfoCubes. AQueries can always defined with Basic InfoCubes. A

Basic Cube is therefore never a pure data target, butBasic Cube is therefore never a pure data target, butboth a Data target and an InfoProvider.both a Data target and an InfoProvider.


47/157


InfoProviders

An InfoProvider is an object for which queries can beAn InfoProvider is an object for which queries can be

created or executed in BEx. InfoProviders are thecreated or executed in BEx. InfoProviders are the

objects or views that are relevant for reporting.objects or views that are relevant for reporting.

Types of InfoProviders:Types of InfoProviders:

MultiProvidersMultiProviders

InfoSetsInfoSets

Remote CubeRemote Cube

SAP Remote CubeSAP Remote Cube

Virtual InfoCube with ServicesVirtual InfoCube with Services


48/157


MultiProvider

A MultiProviders is a type of InfoProvider that combines datafrom a number of InfoProviders and makes it available foranalysis purposes.

The MultiProviders itself does not contain any data. Its datacomes entirely from the InfoProviders on which it is based.

MultiProviders only exist as a logical definition.MultiProviders only exist as a logical definition. TheseInfoProviders are connected to one another by a unionoperation.

A query based on a MultiProvider is divided internally intosubqueries. There is a subquery for each InfoProvider includedin the MultiProvider. These subqueries are usually processed inparallel. InfoProvider A contains sales actuals. InfoProvider B contains sales

plan data. One can combine the two InfoProviders into oneMultiProvider to compare actual vs plan sales.


49/157


MultiProvider

One can combine InfoCube,

ODS objects, InfoObjects and

InfoSets in a MultiProvider.

A union operation is used to

combine the data from theseobjects into a MultiProvider.

Here, the system constructs

the union set of the data sets

involved. In other words, all

values of these data sets are

combined.


50/157


InfoSet

InfoSets allows reporting on several InfoProviders byusing combinations of InfoObjects (master data-bearingcharacteristics) and ODS objects via a join condition..

An InfoSet can have following join conditions

Inner Join Left Outer Join

Inner join and left outer join are only different in thesituation where one of the involved tables does not

contain any suitable record which meets the joinconditions.


51/157


Innervs Left-OuterJoin

Consider ODS object that contains a characteristic PLANTalongside key figures in its data part. In an InfoSet, a joinbetween this ODS object and the characteristic PLANT isdefined to enable the attributes of PLANT to be accessed ina report.

If an inner join is now used and if an ODS object recordcontains a value for PLANT for which they is no entry in thecorresponding master data table, this record is not includedin the results set. Correspondingly, the key figures of thisrecord would not be considered.

If, on the other hand, a left outer join (ODS object left outerjoin PLANT) is used, the affected record is considered.However, in this case, all attributes of the (non-existent)characteristic PLANT are initial. Which behavior is correctdepends on the type of the evaluation required. Both casescan make sense.


52/157


InfoSet


53/157


Components of ETL

Components of ETL are:Components of ETL are:

Source systemSource system

DataSourceDataSource

InfoSourceInfoSource Communication StructureCommunication Structure

Transfer StructureTransfer Structure

Transfer RulesTransfer Rules

Update RulesUpdate Rules InfopackageInfopackage


54/157


Source System

All systems that provide the SAP Business InformationWarehouse with data are described as sourcesystems.

T

hese are as below: SAP systems SAP Business Information Warehouse systems,

Flat files, through which metadata is manually maintained, andtransferred into BW by using a file interface,

A database management system into which data is loaded

from a database supported by SAP, usingD

B Connect and notan external extraction program.

External systems, in which data and metadata is transferredusing staging BAPIs.


55/157


Source System


56/157


DataSource

DataSources are used for extracting data from a source system andfor transferring data into the BW.

DataSources make the source system data available on request tothe BW in the form of the (if necessary, filtered and enhanced)extraction structure.

The DataSources subdivide the data that is provided by a sourceThe DataSources subdivide the data that is provided by a sourcesystem into selfsystem into self--contained business areas.contained business areas.

In SAP source systems, the DataSources are assigned toIn SAP source systems, the DataSources are assigned toapplications, for example HR (Human Resources). The advantageapplications, for example HR (Human Resources). The advantage

here is that you are able to specifically access data evaluation for thishere is that you are able to specifically access data evaluation for thisapplication.application.

The information required for the extraction process, that is, the BWThe information required for the extraction process, that is, the BW--relevant properties of the DataSource, is replicated in the BW byrelevant properties of the DataSource, is replicated in the BW bycomparing metadata.comparing metadata.


57/157


InfoSource

An InfoSource is a set of information that logically belongs together,An InfoSource is a set of information that logically belongs together,summarized into a single unit. It prepares consolidated data forsummarized into a single unit. It prepares consolidated data forupdating to the data targets.updating to the data targets.

InfoSources contain either transaction data or master dataInfoSources contain either transaction data or master data(attributes, texts and hierarchies).(attributes, texts and hierarchies).

An InfoSource comprises of Communication structure and TransferAn InfoSource comprises of Communication structure and Transferstructure.structure.

The set of InfoObjects that logically belong together form theThe set of InfoObjects that logically belong together form thecommunication structure. The transfer structure is used to transfercommunication structure. The transfer structure is used to transferdata to the BW system. The data is transferred 1:1 from the transferdata to the BW system. The data is transferred 1:1 from the transfer

structure of the source system into the BW transfer structure.structure of the source system into the BW transfer structure.

Types of InfoSourcesTypes of InfoSources InfoSources with flexible updatingInfoSources with flexible updating

InfoSources with direct updatingInfoSources with direct updating


58/157


InfoSource

Flexible Update Here data from the communication structure is loaded

into the data targets (InfoCubes, ODS objects, masterdata) using update rules.

Several data targets can be defined by one InfoSource.

The InfoSource can contain transaction data and masterdata.


59/157


InfoSource

Direct UpdateDirect Update Master data (characteristicsMaster data (characteristics

with attributes, texts orwith attributes, texts orhierarchies) of an InfoObjecthierarchies) of an InfoObject

can be updated directlycan be updated directly(without update rules, only(without update rules, onlyusing transfer rules) to theusing transfer rules) to themaster data table with anmaster data table with anInfoSource with direct updating.InfoSource with direct updating.


60/157


Communication Structure

The communicationThe communicationstructure is localized instructure is localized inthe SAP BW and displaysthe SAP BW and displaysthe structure of thethe structure of the

InfoSource.InfoSource.

It contains all of theIt contains all of theInfoObjects belonging toInfoObjects belonging tothe InfoSource of the BWthe InfoSource of the BW

system.system.


61/157


TransferStructure T

ransfer Structure is aT

ransfer Structure is aselection ofselection ofDataSource fieldsDataSource fieldsfrom a source system.from a source system.

The transfer structureThe transfer structure

provides the BW withprovides the BW withall the source systemall the source systeminformation availableinformation availablefor a businessfor a businessprocess.process.

A transfer structureA transfer structurealways refers to aalways refers to aDataSource in aDataSource in asource system and ansource system and anInfoSource in a BWInfoSource in a BW


62/157


TransferRules

One can transform source system data in theOne can transform source system data in the

InfoSource with the help ofTransfer rules.InfoSource with the help ofTransfer rules.

Transfer rule map the fields of the transferTransfer rule map the fields of the transferstructure to that those in the communicationstructure to that those in the communication

structure, using the different methodsstructure, using the different methods

Direct mapping of InfoObjectsDirect mapping of InfoObjects

ConstantsConstants FormulaeFormulae

RoutinesRoutines


63/157


Update Rules

Update rules specify how the data (key figures, timeUpdate rules specify how the data (key figures, timecharacteristics, characteristics) is updated to data targetscharacteristics, characteristics) is updated to data targetsfrom the communication structure of an InfoSource. Thus anfrom the communication structure of an InfoSource. Thus anupdate rule connects an InfoSource to a Data target.update rule connects an InfoSource to a Data target.

An update rule must be specified for each key figure andAn update rule must be specified for each key figure and

the corresponding characteristics of the InfoCube. For anthe corresponding characteristics of the InfoCube. For anODS Object, it must be specified for the data and key fields,ODS Object, it must be specified for the data and key fields,and for an InfoObject it must be specified for the attributeand for an InfoObject it must be specified for the attributeand key fields.and key fields.

The following update types exist:The following update types exist:

No updateNo update AdditionAddition

Overwriting (with ODS objects and InfoObjects only)Overwriting (with ODS objects and InfoObjects only)


64/157


Update Rules

The different updateThe different update

methods providedmethods provided

are:are: Source fieldSource field

ConstantConstant

Master data attributeMaster data attribute

FormulaFormula

RoutineRoutine


65/157


Infopackage

Infopackage is used to load data from source systemInfopackage is used to load data from source system

to BW.to BW.

Following are the different tabs of an InfopackageFollowing are the different tabs of an Infopackage

D

ata Selection : Restricts the data to be loadedD

ata Selection : Restricts the data to be loaded External Data : For selection of flat filesExternal Data : For selection of flat files

Processing : PSA staging optionsProcessing : PSA staging options

Data Target : Targets to be loadedData Target : Targets to be loaded

Update : Full / Delta / Initial LoadUpdate : Full / Delta / Initial Load

Schedule : Foreground or Background processingSchedule : Foreground or Background processing


66/157


Infopackage : Processing Types


67/157


Infopackage


68/157


PSA is the inbound storage area for data from thesource systems in BW system.

The requested data is saved, unchanged from thesource system

Requested data is stored in the transfer structureformat in transparent, relational database tables in theBW system.

The data format remains unchanged, meaning that nosummarization or transformations take place, as is thecase with InfoCubes

PSA (Persistent Staging

Area)


69/157


Data Mart Concept T

heD

ata mart interface makes it possible to update dataT

heD

ata mart interface makes it possible to update datafrom one data target into another.from one data target into another.

We have the following options:We have the following options: Data exchange of several BW Systems: The dataData exchange of several BW Systems: The data--

delivering system is then referred to as the Source BW, thedelivering system is then referred to as the Source BW, the

datadata--receiving system as the Target BW. The individualreceiving system as the Target BW. The individualBusiness Information Warehouses arranged in such a wayBusiness Information Warehouses arranged in such a wayare called data marts.are called data marts.

Data exchange between BW Systems and other SAPData exchange between BW Systems and other SAPSystemsSystems

Data distribution within a BW System (Myself connection) :Data distribution within a BW System (Myself connection) :The data mart interface in the Myself System is used toThe data mart interface in the Myself System is used toconnect the BW System to itself. This means you canconnect the BW System to itself. This means you canupdate data from data targets into other data targets withinupdate data from data targets into other data targets withinthe system.the system.


70/157


PSA Staging scenarios


71/157



Use the staging process for storing data in an ODS object without

InfoCubes, when the transaction data is highly volatile and

constant changes are required to reporting data


72/157



Use a staging scenario without ODS levels if you can work without

the advantages of an ODS object (cleansing, transforming,

consolidating, integrating data from different processes, level ofdetail of information)

This applies in particular if the system already guarantees the

integrity of the data (CO-PA, General Ledger)


73/157



This scenario is also known as a data mart scenario and is

characterized by the data flow from InfoCube to InfoCube, which

is possible within a BW System as well as between severalsystems. Data marts created like this offer better overview and

dataset (snapshot) maintenance and allow you to separate the

data according to task area


74/157


Data Flow

Steps involved in creation ofSteps involved in creation ofData model in BW systemData model in BW system

Identify and create InfoObjectsIdentify and create InfoObjects

Create Data target / InfoproviderCreate Data target / Infoprovider

Replicate Datasource from sourceReplicate Datasource from sourcesystemsystem

Create InfosourceCreate Infosource

Assign Datasource to InfosourceAssign Datasource to Infosource

Define Transfer RulesDefine Transfer Rules

Create Update RulesCreate Update Rules

Create Infopackage and load dataCreate Infopackage and load data


75/157


1 PrepareMe

2 TellMe

3 ShowMe

4 LetMe

5 HelpMe

Data Modeling


76/157


Creating InfoObjects Catalog

Step1: Enter transaction RSA1. Right click on theInfoArea and click on create InfoObject catalog

Step2: Specify the description and technical name

for the catalog

Step3: Select between a characteristic or key figurecatalog

Step4: Click on create icon

C i I f Obj C l


77/157


Creating InfoObject Catalog1. Right click on the InfoArea

and click on create InfoObjectcatalog to get this screen

3. Select between a

characteristic orkey figure

catalog

2. Give the description and

technical name for the

catalog

4. Click theCreate icon.


78/157


Adding InfoObjects to catalog

Step1: Right click the InfoObject Catalog andchoose Change

Step2: Select the characteristics or key figures that

needs to be added to the catalog

Step3: Click on Transfer arrow to add the selectedInfoObjects to the InfoObject catalog.

Step4:Activate the InfoObject catalog


79/157


2. Click here to activate

the catalog afteraddingthe InfoObjects

1. Select therequired InfoObjects

from the Template

window and use the

Transfer button to

add these Objects to

the InfoObject

Catalog.

Adding InfoObjects to Catalog


80/157


Creating InfoObject-Characteristic

Step1: Select the characteristic catalog and right click and select createInfoObject

Step2: Provide a technical name and description for characteristic.

Step3: Specify the general properties for the characteristics (e.g. Data type,Length etc) in the general tab.

Step4: In Business Explorer tab, specify the Reporting properties for theInfoObject.

Step5: In Master data/Text tab specify if the InfoObject characteristics hasany master data or text by ticking the appropriate checkbox.

Step6: In the Hierarchy tab you can specify if the InfoObject characteristichas any hierarchy

Step7: In Attribute tab you specify the characteristics that are attributes ofthis characteristic.

Step8: In Compounding tab indicate the compounding key for the InfoObjectif the master data stored in this characteristic has compounded primary key.


81/157


Creating InfoObjects-Characteristics

1. To create InfoObject in

existing catalog right clickand select create

InfoObject


82/157


Here you can specify the

reference characteristics

name in case InfoObject is

using any reference char

2. Mention the

Reference/ Template

characteristic (if any)



83/157


3. Add the general properties like Data

type CHAR, NUMC etc and data length

etc under this tab General



84/157


4. In this tab you

can specify the

reporting

properties for the

InfoObject



85/157


5. When the masterdata check

box is ticked, the InfoObject will

storem

aster

data attribu

tes. Onewill have to specify these

attributes in the Attributes tab

subsequently.

When the with Texts box is

checked then the Characteristic

will contain texts as well.


C f O C


86/157


6. This box is ticked if the

InfoObject has hierarchy to be

stored and then add the hie

rarchyproperties accordingly (e.g. Time

dependency and Intervals)


C i I f Obj Ch i i


87/157


7. If the InfoObject is

ticked as with master

data then in this tab we

can add attributes of the

masterdata


C ti I f Obj t Ch t i ti


88/157


8. In this tab you

can specify the

compounding key

for the InfoObject

(if any)



89/157


Creating InfoObject - Key Figure Step1: Select the Key figure catalog and right click and select

create InfoObject

Step2: Give the technical name and description for key figure.Mention the template key figure to be used if any

Step3: In Type/Unit tab you specify the key figure properties likedata type quantity or amount etc. and relevant unit

Step4: In Aggregation tab you specify the aggregation methodfor the key figure (summation, average etc)

Step5: In Additional Properties tab you specify the displayproperties. These properties are applicable to display of data inreports.


90/157


Creating InfoObject - Key Figure

1. To create a key figure

InfoObject in existingcatalog right click and

select create InfoObject


91/157


2. Enter the key

figure technical

name and the

description.



92/157


3. In this tab you can

specify the data typeand details of

currency/unit etc


C f O


93/157


4. Specify the aggregation

method here for the key

figure


C i I f Obj K Fi


94/157


5. Describe the display properties

here. These settings affect the

display of data in reporting


C ti I f C bC ti I f C b


95/157


Step1Step1:: EnterTransaction RSA1. Right Click on the InfoAreaand choose Create InfoCube and provide technical nameand description for the InfoCube.

Step2: Select the Characteristics which should be part of the

InfoCube design.

Step3: Select Time Characteristics and Key Figures.

Step4: Create dimensions and assign the characteristics to

the dimensions

Step6: Save and Activate the InfoCube.

Creating InfoCubeCreating InfoCube



96/157



1. In RSA1, right click on

an InfoArea and choose

Create InfoCube.



97/157


Creating InfoCubeCreating InfoCube2. Provide Technical

name and description

for the InfoCube

3. Select radio

button

BasicCube.4. Click onCreate

button.



98/157


Creating InfoCubeCreating InfoCube5. In the Characteristics tab, select

the various characteristics required

in the InfoCube design (from theTemplate window to the Structure

window).



99/157


Creating InfoCubeCreating InfoCube6. Similarly, select and

transfer the TimeCharacteristics and Key

Figures.



100/157



7. Create dimensions by

clicking on create button.



101/157



8. As per

bu

siness logic,assign the characteristics

to the respective

dimensions.



102/157



9. Save and Activate theInfoCube

C ti ODSC ti ODS


103/157


Step1: EnterTransaction RSA1. Right click on theInfoArea and choose Create ODS. ProvideTechnical name and description for the ODS.

Step2: Choose infoobjects in Key field and Data fields

of OD

S.

Step3: Adjust ODS settings appropriately.

Step4: Save and Activate the ODS

Creating ODSCreating ODS

C ti ODSC ti ODS


104/157





Create ODS Object.



105/157


Creating ODSCreating ODS2. Provide Technical

name and descriptionfor the ODS

3. Click on

Create

button.



106/157



5. Chooseappropriate

settings of ODS

4. Choose Infoobjects

in key fields and data

fields section from left

hand panel



107/157



6. Save and

Activate ODS

Creating MultiProviderCreating MultiProvider


108/157


Step1: EnterTransaction RSA1. Right Click on Infoarea andselect Create MultiProvider. Provide technical name anddescription for the MultiProvider.

Step2: Select Data Targets (InfoCube, ODS and InfoObjector Infosets) on which MultiProvider should be based.

Step3: Select the Characteristics which should be part of theMultiProvider design.

Step4: Select Time Characteristics and Key figures.

Step5: Create dimensions and assign the characteristics tothe dimensions.

Step6: Identify the characteristics, time characteristics andkey figures in Identification tab.

Step7: Save and Activate the MultiProvider.




109/157





Create MultiProvider.



110/157


Creating MultiProviderCreating MultiProvider2. Provide Technical

nam

e and descriptionfor the MultiProvider

3. Click on

Create

button.



111/157


4. Select Data Targets (InfoCube, ODS

and

InfoObject or Infosets) on which

MultiProviderwould be based




112/157



5. In the Characteristics, Time

characteristics and Key figures tab,

select the various characteristics/key

figures required in the InfoCube design

(from the Template window to the

Structure window).



113/157



6. Create dimensions by

clicking on create button.



114/157


Creating MultiProviderCreating MultiProvider7. As perbusiness logic,

assign the characteristics

to the respective

dimensions.



115/157



8. Click on

Identification pushbuttonon Characteristics, Time

Characteristics and Key

figures tab to identify

InfoObjects from

underlying data targets



116/157



9. Foreach InfoObject in MultiProvider,identify the corresponding InfoObject from

data target



117/157



10. Save and Activate the

MultiProvider.

Creating InfoSetCreating InfoSet


118/157


Step1: EnterTransaction RSA1. Right Click on InfoArea andselect Create InfoSet. Provide technical name anddescription for the InfoSet.

Step2: Choose O

DS or InfoObject on which you want to buildInfoSet.

Step3: Select the characteristics/key figures required inInfoSet design and join the common characteristics via Inneror Left Outer Join.

Step 4: Save and Activate InfoSet.




119/157





Create InfoSet.



120/157



2. Provide Technical

name and description

for the InfoSet.

3. Choose ODS or

InfoObject on which

you want to build

InfoSet.

Creating InfoSetCreating InfoSetCreating InfoSetCreating InfoSet


121/157



4. Select the

characteristics/key

figures required in

InfoSet design

5. Join the common

characteristics via Inneror

Left OuterJoin.




122/157


Creating InfoSetCreating InfoSet5. Save and

Activate InfoSet.

Creating Flat File Source System


123/157


Step1: In RSA1 transaction, select Source System.Source System.

Step2: Right click on Source System in right panel

and select Create.

Step3: Select File System.File System.

Step4: Provide a Logical System Name and SourceLogical System Name and SourceSystem name.System name.




124/157


1. In RSA1

transaction, select

Source System.

2. Right click on SourceSystem and select

Create.



125/157


3. Select File

System.



126/157


4. Entera Logical

system name andSource system

name.



127/157


5. Flat file Source

system is now

created.

Creating InfoSource


128/157


Step1: In InfoSource menu (RSA1 transaction), rightclick on an Application component and select CreateInfSource.

Step2: Choose the InfoSource type (Flexible orDirect

Update) and provide technical name and descriptionto InfoSource.

Step3: Enter the InfoObjects to be included inCommunication structure.

Step 4: Save and Activate the InfoSource.

Creating InfoSource

Creating InfoSource


129/157


1. Right click on

Application

component and

select CreateInfoSource.

Creating InfoSource


130/157


2. Choose the InfoSource

type (Flexible orDirect

Update) and provide

technical name and

description to

InfoSource.

Creating InfoSourceCreating InfoSource


131/157


gg

3. Select a template

orkey in the

InfoObjects in

Communication

Structure.

Creating InfoSourceCreating InfoSource


132/157


4. Save and

Activate the

InfoSource.

Assigning DataSource toAssigning DataSource to


133/157


Step1: Right click the InfoSource and select Assign

DataSource.

Step2: Select the Source System of the DataSource.

Step3: Select the DataSource existing in this source

system and confirm assignment.


InfoSourceInfoSource



134/157



1. Right click the

InfoSource and

select Assign

DataSource.



135/157



2. Select the

Source System

of the

DataSource.



136/157


InfoSourceInfoSource3. Ifmultiple DataSources are

available, then system prompts to

choose Datasource. Select

appropriate Datasource and

press Yes to confirm

assignment of DataSource to

InfoSource.

Assigning DataSource toAssigning DataSource toI f SI f S


137/157



4. System proposes assignment

of InfoObjects to Datasource

fields orwe can manually assign

InfoObjects to fields from

Datasource.

Creating Update RuleCreating Update Rule


138/157


Step1: Right click on

Data

Target and then selectCreate update rule.

Step2: Specify the Update type, update method andunit for key figure calculation.

Step3: Specify the Update method for thecharacteristics

Step4: Specify the Update method for time

characteristics.

Step5: Check and activate the update rule

g pg p



139/157


g pg p

1. Right click on anInfocube and choose

Create Update Rules.



140/157


g pg p

2. In the below screen, specify

an InfoSource/ ODS/ InfoCube

appropriately.



141/157


g pg p

3. Foreach key figure,

specify mapping with

appropriate source

InfoObject.



142/157


g pg p

For InfoCube we have twoupdate type Addition

No Update

There exists three updatemethod for Key figures Source key Fig

Formula

Routine

One can also specifycurrency translationfunction here.



143/157


g pg p

4. Specify appropriate mapping

forCharacteristics and Time

Characteristics.

Creating Infopackage


144/157


Step1: Right click on Source system below the selected

InfoSource and choose Create Infopackage.

Step2: Enter Infopackage Description.

Step3: Enter appropriate restrictions in Data Selection

tab. For Flat file data load, enter the path for flat file in

External Data tab. Select appropriate processing type in

Processing tab. Select Data targets to be loaded in

Data Target tab. Select update type in Update tab.

Start Infopackage load in Schedule tab.

g p g



145/157


1. Right click on Source system

below the selected InfoSour

ceand choose Create

Infopackage.



146/157


2. Enter

Infopackage

description.



147/157


3. Enterrestrictions (if any) in

Data Selection tab.



148/157


4. Enterpath for flat file in

External Data tab.


149/157



150/157


6. Select data targets to be

loaded in Data Targets tab.



151/157


7. Select update type in Update

tab.



152/157


8. Start Infopackage load in

Schedule tab.

Data Modeling


153/157


1 PrepareMe

2 TellMe

3 ShowMe

4 LetMe

5 HelpMe

LetMeLetMe


154/157


For the following scenario develop a data model in BWFor the following scenario develop a data model in BW

A flat file source system (CSV file) provides the followingA flat file source system (CSV file) provides the followinginformationinformation

Identify InfoObjects (Characteristics and Key Figures)Identify InfoObjects (Characteristics and Key Figures)

Identify key fields and data fields of ODSIdentify key fields and data fields of ODS Create ODS Object as data staging layerCreate ODS Object as data staging layer

Create InfosourceCreate Infosource

Create Update Rules between InfoSource and ODSCreate Update Rules between InfoSource and ODS

Identify Dimensions of InfoCubeIdentify Dimensions of InfoCube

Create InfoCube for Sales summary (without sales orderCreate InfoCube for Sales summary (without sales ordergranularity)granularity)

Create Update Rules between ODS and InfocubeCreate Update Rules between ODS and Infocube

Create Infopackage for loading data in ODS and InfoCubeCreate Infopackage for loading data in ODS and InfoCube

Transactions Used


155/157


RSA1RSA1 : Administrator Work Bench.: Administrator Work Bench.

RSA11RSA11: Create and Maintenance ofData Targets and: Create and Maintenance ofData Targets and

InfoProviders.InfoProviders.

RSA12RSA12: Create and Maintenance InfoSource.: Create and Maintenance InfoSource.

RSA13RSA13: Create and Maintenance of Source System.: Create and Maintenance of Source System.

RSA14RSA14: Create and Maintenance InfoObjects.: Create and Maintenance InfoObjects.

RSA15RSA15: Create and Maintenance of PSA.: Create and Maintenance of PSA.

RSD1RSD1: Edit infoobjects: Edit infoobjects

Data Modeling


156/157


1 PrepareMe

2 TellMe

3 ShowMe

4 LetMe

5 HelpMe

HelpMe


157/157

Browse through the below links for Self Study

1. http://help.sap.com/saphelp_nw04/helpdata/en/e3/e60138

fede083de10000009b38f8cf/frameset.htm

2. http://service.sap.com/bi

Data Modeling - V1.0

Documents

Transcript of Data Modeling - V1.0