Monday, December 23, 2013

Loading Data from ECC System (Standard DataSources)



§  BI Content defines 60% of BI system
§  For flat files we were creating our custom data sources
§  Data sources defined by SAP is called Standard data sources
§  Standard data sources exists in ECC system
§  We will need to get this standard data source from ECC system to our BI system
§  Once that available in BI system then we can create an InfoPackage and extract data to standard data sources

·      Non Logistics DataSources /General DataSources
§  Non-LOs DataSources name start with 0App_Area like 0FI

·      Logistics DataSources
§  LOs DataSources name start with 2lis

§  To use standard DataSources you need to activate it

Non Logistics DataSources
In Non-LO DataSources the flow is Base Table ---> InfoPackage ---> DataSource
RSA2 (Check DataSource Structure) --->RSA5 (Activation of DataSource) --->RSA6 (Post Processing) ---> RSA3 (Extract Checker) ---> Replicate Application Component (in BW system)
1)      Logon to ECC system
a)      To activate general DataSource go to RSA5
                                 i.            Expend SAP/R3, expend FI application component, expend FI-AP, FI-AP-I0 is a transactional data source
                               ii.            To check properties of the data source copy its name go to RSA2, provide name of DataSource it shows this DataSource is based on functional module
Data sources can be made by three ways:
§  Tables or View
§  Queries or InfoSets
§  Functional Module / ABAP Code, which will create an interface b/w DataSource and base table
Verify the fields if it suits your need, Data comes to standard DataSource from the tables of ECC called Base tables, Functional consultants load data to the tables of ECC. The job of BI consultant is to extract data from all these base tables to DataSource and from DataSources to the BW system
                              iii.            Select FA-AP-I0 in the RSA5 and click the Activate DataSources button from application toolbar
b)      To verify is it activated successfully go to RSA6, expend SAP-R/3, then FI, then expend FI-AP, if you find your DataSource here its mean its activated
c)       To check validity of the general DataSource go to RSA3, give DataSource name, click Extraction button, then select List button to check data

2)      Logon to BW system to get the structure of activated Non-LO DataSource
a)      In DWW go to Source System, expend SAP, double click on your ECC system
b)      Find your application component, right click Financial Accounting Vendors and select Replicate Metadata
3)      To load data from source system to our DataSource, create InfoPackage and Execute it

Logistics DataSources
In LO DataSources the flow is Base Table ---> Setup Table ---> InfoPackage ---> DataSource
RSA2 (Check DataSource Structure) --->RSA5 (Activation of DataSource) --->RSA6 (Post Processing) ---> LBWG (Empty Setup tables) ---> LBWE (LO Cockpit) ---> SBIW (Fill Setup tables) --->RSA3 (Extract Checker) ---> Replicate Application Component (in BW system)

1)      Logon to ECC System
a)      To activate LO DataSource go to RSA5
                                 i.            Expend SAP-R/3, expend MM then MM-I0, note logistics can be from MM, SD most of the times, select 2LIS_02_ITM
                               ii.            To check properties of the data source copy its name go to RSA2, provide name of DataSource
                              iii.            In the RSA5 and click the Activate DataSources button from application toolbar
b)      To verify it go to RSA3, you will get 0 records, it doesn’t mean that Base tables of ECC don’t have data, rather we need to fill the Setup tables
c)       To fill Setup tables, go to SBIW, expend Setting for Application Specific DataSources, expend Logistics, expend Managing Extract Structures, then Initialization, to make sure that there is no data in setup tables click Delete the Contents of Setup tables, it will ask for application no
                                 i.            To find application number  go to LBWE, you will find that there is 02 in front of Purchasing, it’s the application number enter it which will delete all the data from purchasing DataSources
                               ii.            In SBIW select Filling in the Setup tables then Application specific setup of statistical data click on execute in front of perform setup-purchasing, give name of run and make sure the termination date is always in future bcz data is in huge volume which takes time to load to avoid losing any records added during load time we specify termination date in future, always execute it back end, so go to Program->Execute in Background from top menu bar, enter default printer name like lp01, select immediately on next window and click save
                              iii.            To monitor this job go to SM37, once the job is complete, you can verify the records from extract checker
d)      To check validity of the LO DataSource go to RSA3, give DataSource name (2LIS_02_ITM), click Extraction button, then select List button to check data
2)      Logon to BW system to get the structure of activated LO DataSource
a.       In DWW go to Source System, expend SAP, double click on your ECC system
b.      Find your application component (MM) , and select Replicate Metadata
3)      To load data from source system to our DataSource, create InfoPackage and Execute it
a.       Right click 2LIS_02_ITM and select Create InfoPackage, provide description and click Save
b.       Update Tab
                                                   i.      Full update will load all the records without setting the marker
                                                 ii.      Initialize of Delta Process will set a marker, which indicate till this record data has been loaded
                                                iii.      Initialization with Data Transfer is used when we are loading data for the very first time and the marker was not set, it is almost similar to full update but it will also set the marker till the records are loaded
                                               iv.      Initialize without Data Transfer will just set marker so that we can use the delta load in the upcoming loads to our InfoProviders, means when in this InfoPackage is executed it won’t  load records to InfoProvider instead it will set the marker for future delta  load, select here this option
c.       Data Selection Tab
                                                   i.      To set the criteria (e.g. purchasing document, purchasing date) for InfoPackage execution i.e. from which the delta will be available, select From Value for  BUDAT (Purchasing Document Date)  like 20.04.2012, then from top menu bar select Scheduler---> Initialization Options for Source System, click Continue in pop up
d.      Schedule Tab
                                                   i.       Start Data Load Immediately and click Start button

Tuesday, December 17, 2013

DataStore Object (DSO)


§  In BI version 3.x  it was known as Operational DataStore (ODS)
§  DSOs act as the staging layer that stores transaction level data and/or stores data received from multiple sources, before sending it onto InfoCubes or other data targets
§  DSOs have two types of fields:
1)      Key Fields; uniquely identify a record in a DSO
2)      Data Fields; contain both characteristic and key figure InfoObjects
§  DSO will overwrite the data when all the key fields are same, While property if an InfoCube is that it aggregates when all the characteristics are same
§  It’s not advised to store master data in DSO, instead we store transaction data at detail level not at aggregated level
§  There are four types of DSOs
1)      Standard DSO
§  Standard DSO consists of three tables
·         Activation Queue (New Data) Table
·         Active Data Table
·         Change Log Table
2)      Write Optimized DSO
§  It consists of only one table; Active data table
3)      Direct-Update DSO
§  It consists of only active data table
§  Data in this type of DSO can’t loaded with standard data staging process; instead, SAP BW supplies APIs
4)      In-Memory/SAP HANA-optimized  DSO
§  The SAP HANA database is essential prerequisite for this DSO

Creating Standard DSO

1)      Create your Standard DSO
a.       In DWW under InfoProvder, select your InfoArea and from context menu of infoarea Select Create DataStore Object
b.      Provide technical name (ZDSO_01) and description (Asghar DataStore Object), and click Create icon
c.       Select Characteristics InfoObject catalog from the toolbar of the center panel, in popup window select your characteristic InfoObject catalog, Drag Product ID, Customer ID from the central panel and drop it over Key Fields in right panel
d.      Drag Customer Name, Product Group from the central panel and drop it over the Data fields in right panel
e.      Now select Key Figure InfoObject catalog from the toolbar of the center panel, in popup select your key figure InfoObjects catalog, Drag Price and Quantity from central panel and drop it over Data fields in the right panel, note key figures will always go to data fields.
f.        Activate your DSO

2)      Create and Load data to your DataSource
a.       In DWW go to DataSources section, right click your Application component select Create DataSource
b.      Provide name (ZDS_DSO01), select Data Type DataSource as Transaction Data, click Continue
·   General Tab
Short Des:                           DS for DSO01
Medium Des:                      DS for DSO01
Long Des:                           DataSource for DSO01
·   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
c.       Right click your DataSource (ZDS_DSO01) and select Create InfoPackage
·   Provide “IP for DataSource for DSO01” as InfoPackage description, click Save
·   Schedule Tab
Select Start Data Load Immediately
Click Start button
d.      To verify is it successful right click DataSource (ZDS_DSO01) and select Manage, the green icon in 3rd column of the popup depicts that load is successful

3)      Load data to your DSO
a.       In DWW go to InfoProvider section, in your InfoArea, select your DSO (ZDSO_01) and select Transformation
·   In Source of Transformation; select Object Type as DataSource, ZDS_DSO01 in DataSource and flat file name  in source system click Continue
·   Map fields as:
ZDS_DSO01                                    ZDSO_01
Customer ID ------------------------ Customer ID
Product ID    ------------------------ Product ID
Product Grp ------------------------ Product Group
Quantity       ------------------------ Quantity
UOM             ------------------------ Unit
Price             ------------------------ Price
Currency     ------------------------ Currency
·   Activate Transformation
b.      Right click Data Transfer Processes icon and select Create Data Transfer Process, click Continue on next screen
·   Extraction Tab
Extraction Mode:             Delta
·   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 DSO in top section and click Contents button on application toolbar. Click Execute button, you will see there is no data available yet.
·    It is because in standard DSO there are three types of tables (Activation Queue (New Data), Active Data, Change Log), when we execute DTP to load data from DataSource to DSO, it will be saved into Activation Queue table. Data in Activation Queue table is not available for reporting or viewing
·   Activation queue data can be seen as DSO-->Manage-->Contents Tab-->New Data button
·   When you activate the data, it will be moved parallel to active data and change log tables, Report fetch data from active data table, while when we load data from DSO to other target it is loaded from change log table
·   To activate data go to DSO-->Manage-->Requests Tab-->Activate button---Select request in Pop up-->click Start--> close pop up--->Refresh---->Apply Changes
·   To check data in Active Data table, follow path DSO-->Manage-->Contents Tab--> Active Data button

Creating Write-Optimized DSO

·         In Write-optimized DSO there is only active table
·         Data is directly loaded to active table so there is no need to activate the data

1)      Create your Write-Optimized DSO
a.       Follow steps from previous section to create Write-Optimized DSO, You only have to change the type of DSO when creating it
b.      In right panel of window, expend Settings, click on Pencil button in front of Type of DSO. choose Write-Optimized option from popup window
c.       Now you have to add key fields to semantic key and rest of the fields go to Data fields
d.      Activate DSO
2)      Create and Load data to your DataSource
a.       Follow steps from previous section
3)      Load data to your DSO
a.       Follow steps from previous section, but note here we don’t need activation of data