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
0 comments:
Post a Comment