Thursday, December 12, 2013

InfoCube

§  InfoCubes store summarized and aggregated data, for long periods of time in contrast to DSOs which store detailed transaction data
§  Both DataStore objects and InfoCubes make up the physical storage of the warehouse
§  The structure of InfoCube is based on Star Schema, in SAP NW BW it is extended to support master data known as Extended Star Schema
§  In InfoCube a Fact table is surrounded by Dimension tables
§  The characteristic InfoObject act as Dimension in Dimension table
§  While the Fact table will only contains key figure InfoObjects

Let’s we want to load a transactional data from flat file, where a customer has purchased a product which belongs to product group and has quantity and price



1)      Create and Load data to a DataSource
a.       In DWW go to DataSources section, select your Application Component, right click select Create DataSource name it ZDS_CTRN, enter your flat file system as Source System and select Transaction Data as Data Type DataSource
·   General Tab
Short Des:                           DS for Cust TRAN
Medium Des:                       DS for Cust TRAN
Long Des:                            DataSource for Customer Transactional Data
·   Extraction Tab
File Name:                                                          browse for csv file
Header Rows to be ignored:                               1
Data Format:                                                      Separated with Separator
Data Separator:                                                                ,
·   Proposal Tab
Click Load Example Data
·   Fields Tab
On Copy changes pop up click Yes

·   Preview Tab
Click Read Example Data
Click Activate button in popup window
b.      Right click your DataSource (ZDS_CTRN) and select Create InfoPackage, Provide “IP for DS for Cust Transactional” as InfoPackage description, click Save
·   Schedule Tab
Select Start Data Load Immediately
Click Start button
c.       To verify is it successful right click DataSource (ZDS_CTRN) and select Manage, the green icon in 3rd column of the popup depicts that load is successful

2)      Create and Load Data to an InfoCube
From the sample data objects required to load are Customer, Product ID, Product Group, Quantity, UOM, Price and Currency, so these must be defined before creating InfoCube
a.       Go to InfoProvider section and select your InfoArea, from context menu select Create InfoCube
b.      Provide technical name ZIC_CTRN, and description InfoCube for Customer Transactional Data, click Create icon
In Edit InfoCube window we need to define the dimensions tables and fact table of the cube. By default three dimension tables (Data Package, Time and Unit) are created, we can have a maximum of 16 dimension tables in an InfoCube out of which 3 are pre-defined.
c.       Right click Dimension and select Properties, change description to Customer and click Continue
d.      In center panel of screen select InfoObject Catalog, it will display the catalogs you have in your InfoArea, double click characteristics catalog, expend characteristics and drag and drop Customer ID on dimension Customer
e.      Right click Dimension in right panel and select Create New Dimension, provide Product as description and click Continue
f.        Drag and drop Product ID and Product Group characteristics on Product dimension
g.       Click on InfoObject catalog in the center pane of the screen and this time select key figure catalog
h.      Drag and drop Price and Quantity on Key Figures folder in right pane
i.         Quantity should have UOM and Price should have Currency, if you check the Unit dimension you will find that both of these units are automatically added here
j.        Activate your InfoCube
k.       Right click your InfoCube and select Create Transformation, in Source of Transformation section of pup up, select Object Type as DataSource, enter DataSource ZDS_CTRN and Source System name as your flat file system, click Continue
·   Map fields as:
ZDS_CTRN                                     ZIC_CTRN
Customer ID ------------------------ Customer ID
Product ID    ------------------------ Product ID
Product Grp ------------------------ Product Group
Quantity       ------------------------ Quantity
UOM             ------------------------ Quantity
Price             ------------------------ Price
Currency      ------------------------ Price
·   Active transformation
l.         Right click Data Transfer Processes icon and select Create Data Transfer Process, click Continue
·   Extraction Tab
Extraction Mode:             Delta
For mater data we used to select Full, for transaction data we can utilized delta mode which allows the system to identify the requests that are not yet uploaded to the target (InfoCube)
·   Update Tab
Error Handling:  Valid Records Update, No Reporting (Request Red)
·   Execute Tab
Activate the DTP from application toolbar to enable Execute button, Click Execute button, click Yes in popup
·   Green status icons in Monitor screen depicts data is loaded successfully, select Administer Data Target from application toolbar, Select your cube in top section and click Contents button on application toolbar. Click Execute button, you will see data is loaded to our cube.



Star Schema

You will that a column Request ID is added to our InfoCube, if our DTP has 10 records to load all these 10 records will have same Request ID, this is just a view in fact  the data in InfoCube is distributed in different tables in the back end. These tables are arranged in start schema as shown:



To execute a query that finds the key figure values for Customer ID A001, it will pull the key figure value from fact table as

10           10                           [10          10]

Using CDIM 21 it will pull Customer ID from Customer dimension table as

A001                                      [A001    10           10]

Using PDIM 32 it will pull Product ID and Product Group from Product dimension table as

P01         PG01                     [A001    P01         PG01     10           10] 

Using UDIM 41 it will pull UOM and Currency for UDIM 41 from Unit dimension table as

KG          PKR                        [A001    P01         PG01     10           KG          10           PKR]

Extended Star Schema

The following figure depicts the Extended Start Schema; here the dimension table is connected to SID (Surrogate ID) table for master data (Text, Attributes and Hierarchy). The benefit of Extended Star Schema is the re-usability; here a single SID table can be connected to different dimension tables

 



No comments:

Post a Comment