SAP BW Extraction Overview Basics -Sample Notes

Business inteligence---DELTAS-----DBW70E///DBW70R///DB70P
service orinted architecture----sap---Netweaver---2004(bw 3.5)/2004s(bi 7.0)


Netwevaer---------user integartion///information integration//process integration///devolopmnt plat forma


information integration---bi--------EDW///ERQA///BUSINESS PLANNING

1.EDW----------ENTERPRIZE DATA WAREHOUSE---DESIGNG THE DW//ACQURING THE DATA  TO DW//MAINTAING THE DW

2.ERQA--------ENTERPRIZE REPORTING AND QUERY ANALSYS---BEX SUITE--Query deisgner///Analyzer///web Application designer///web anlzyer////bex broadcaster///Report designer///visual composer

3.Business planing----Integrated plang


EDW:DELTAS

3.X DATA FLOW

SS(DS)---TS---PSA--TR---CS--UR---DT

IN 7.0

1.IN 7.0 NO TRNASFER STRU/NO PSA-----DATA SOURCE(NW DS)(UR DATA SOURCE IS PSA)----3 TYPES OF NW DS---TRNSACTION DATA LOADING///MDAT--ATTR/TEXT///NO HIERACHIES R SUPPORTED FOR NW DS ---3.X TS CONCEPT TO LOAD THE HIERACHYS

2.NO Transfer Rules(TR)/NO UPDATE RULES(UR)------------BOTH R REPLACED BY TRANSFORMATION-----TYPES OF TRANSFORMATIONS
---INFO OBJECT////CONSTANT/FORMULA///ROUTINE-----END ROUTINE///EXPERT ROUTINE

 End routine is used after the transformation
 start routine----before transformation
 Expert routine---used in with in the Transformation

3.Infosurce---communication stru---is optional--not mandatory

4.Dat targets-------Master data--------char table/////Trnsaction data---Ods//DSO(data store object)
 3.x(ods)  7.0(DSO)
 standard  standard
 transactional Direct update Dso
   Write-optimized dso

 info cube-----Std info cube///Virtual providers(remote cube)////Trnsactional infocube---Real time info cube

infosets-in 3.x---ods/chars but in 7.0 we can also include infocubes into the infosets(max of 2 info cubes)

Multi providers---ic/ods/chars/infosets in 3.x but in 7.0  Aggregation level in to multiporvider


SS------info package-----------DS---------transfromation-------------DT(Mdata---Insert char As Infoprovider///DSO/Icube)


5.in 3.x info package can load data to data targets--but in 7.o info package can load data till data source only

in 3.x only std info package//but in 7.o we have one more---i.e--real time/push enabled info package

6.using DTP (Data transfer process)---we can load data from data source to data targets
 1.standrd dtp
 2.dtp for direct acesss
 3.error dtp
 4.real time dtp

7.Error stack:but in 7.0 all the error record r updated to error stack--modify--error dtp load to data targets
8.Bi accelerator-------------indexing server---indexs(accelerator)--improves the query performance---server
9.Daemon:in 3.x minimum delta inetrval--hourly//but in 7.0 using Daemon we can trigger info package and Dtp every minute



SS(1)------IPACK(5)-----------DS(2)---------TRANSFERMATION-(4)--DTP(6)---------DT(3)(DSO/ICUBE/MDATA--CHAR)




1.Devolop the info objects---chars(attributes)/and key figures
2.Master data loading--attr/text
3.Transaction data loading--DS/info cube


Employee-----attr/text

SS(1)--IP(5)--------DS(2)-------------TRFTN(4)---DTP(6)--------DT(3)


emp----emp name///add/dsg

1.SEE TO THAT SOURCE IS AVAILABLE
2.CREATE DATA SOURCE--MDATA --ATTR
3.INSERT CHAR AS DATA TRGET
4.CREATE TRANSFROMATION-----TRFTN CAN BE CREATED FROM THE SOURCE AND TARGET ALSO
5.CREATE INFO PACKGE---SOURCE
6.CREATE DTP---SOURCE/TARGET

Trnsaction data loading

sys architecture------------DSo(data Source Object) to info cube

ss-----dso---info cube
2333333333333333333

1.create info cube---ext star schema
2.create dso
3.load data to dso///activate dso
4.DSo to info cube load the data


SS(1)------INFOPACK(5)-----------DS(2)------TRFTN(4)--DTP(6)---DT(3)


DSO------TARFTN--DTP--INFOCUBE



VIRTULA PROVIDER//////////REMOTE CUBE(DIRECT DATA ACQUISTION)

SS--- TO BI SYS--PHYSICALLY DATA IS MOVED--IN DDA-NO DATA IS STAGED---DATA IS THERE IN SOURCE ITSELF BUT FOR OUR REPORTS WE ACESS DIRECTLY SOURCE SYS DATA USING REMOTE CUBE

STOCK MARKET----

LESS VOLUME OF DATA
LESS NO OFUSERS ADVISABLE---------------------OLTP SYS PERFORMANCE IS DEGRADED



1.SS--SOURCE--FILE
2.CREATE DATA SOURCE-------ENABLE DIRECT ACESS
3.REMOTE CUBE--
4.CREATE TRANSFORMATION
5.CREATE DTP----DIRECT ACESS
6.ACTIVATE DIRECT ACESS

Error stack and Error dtp

psa---status--red

but in 7.0 all the error records r updated to error stack,,there u can modify the records and from there using error dtp we can load the data to target

1..source sys
2.Data source
3.Data target(icube)
4.Transformation
5.info package
6.stand dtp(ds to dt)---
7.error stack---eror records---modify the record
8.create error dtp--load it



REPARTIONING-----------------------------IN 3.X PARTIONING
THIS REPARTIONING IS NOT THERE IN 3.X


PARTIONING---------E FACT TABLE------2 TIME CHARS----------CALYERA/MONTH////FISCAL YEAR PERIOD

IF THE DATA IS MORE HISTORICAL--10 YRS OF DATA(1996---2005) ---QUERY-----IT HASE TO READ ALL THE DATA----PRESENT THE REQUIRED DATA


YEAR---PARTION----P1---TILL 96//////97-----P2//98--P3/-------------2005---P10----AFTER 2005--AFTER END DATE PARTION 

NO OF PARTIONS-------------SYS CREAET 2 ADDTIONAL PARTIONS---BEFORE STAR DATE///AFTER END DATE

PARTIONING IMPROVES QUERY PERFORMANCE-------QUERY---YEAR--2004-----MINIMIZE DATA BASE READ TIME


IN 3.X REPARTIONG IS NOT ALLOWED BUT IN 7.0--REPARTIONG IS ALLOWED---MERGING OLD PARTIONS///ADD NEW PARTIONS


1.BACK UP THE DATA BASE
2.STOP PROCESS CHIANS RUNNING ON THAT INFO CUBE
3.AFTER PARTIONING CHECK THE TRANSFROMATION
4.AGGREGATES
IN 3.5---PARTIONING IS NOT ALLOWED WITH DATA//////BUT IN 7.0--REPARTIONING IS ALLOWED WITH DATA



REMODELING:THIS IS NOT THERE IN 3.5////USING REMODELING WE CAN MAKE STRUCTURAL CHANGES TO THE INFOCUBE


ADD CHARS///KEY FIG
DELET A CHAR///KEY FIGURE
///REPLACE A CHAR /////KEY FIGURE


SAME PRECAUTIONS:

bex suite-----query deigner/analyser///report deisnger///bex broadcaster////visual composer///------enterprize portal


1.Query designer----to design the queries---query elemnts-----user interface

2.Analyser:excel based fronend tool------------Design mode///Analysys Mode
 Design mode---for user---design items like web items ---using this we can design the workbooks////---design items---table/dropdown/push button/checkbox/radiobutton

 Analsys mode--for buser---borwsoe all his workbooks


3.web application designer:using this we can design web template---html///but in 7.0 xml html scrpting
migrate 3.x templates to 7.0-migration tool

all the other reporting elements when they r opend with new toola they r automatically migrated


4.Report designer-------crystal reports----formated reporting---rows/columns/images/logos/headre/fotters//pdf 

visual composer---------------dashborads----iviews---customized by using ui controls

dropdown///pushbutton///radiobutton/checkbox

ui controls similar to web items-----iviews:-------visual composer------the input is from queries-sap bi sys---------sap sys---badi/bapi

sap bi sys--------------queries----------iview--------selections----input selctions/////////out put selections


iview------can be configured by using ui controls





sap erp-----applications------functional applications-----logistics///acounting/hr


logistics-----16 business applications---sd/mm/pp/qm

standard tables----vbak/vbuk/vbrk


sap erp----4.5/4.6c/4.7ee/ecc5.0/ecc6.0

4.7ee------------60000 tables--imp table

a to x------sap
y or z---customer specifc objects


sap erp--------applications---------logistics/accounting/hr

extractors-----application specific extractors///cross application extractors


ss(ds)--ts---psa--tr---cs--ur--dt

logistics--------applications




sap erp-------applications


1.logistics-------------16 applications----11--orders//12---deliveries///13---billing

02--purchasing
03--inventry controling
05---quality magt

2.Accounting----fi/co

fi--------accounts payable/accounts recievable///general ledger///specila ledger

co----profitability analsys///product cost controling//gneral cost controling

3.HR







cross application extraction-----generic extarction:


why do u go for generic extractors:

sap erp--std tables-but--ztables--new prdouct sales data---z table-----

sap extractor----------sap std tables

ztable-----generic extractors

view-----logical join of 2 or more table---cross application data---view
open order///current product stock level--std table///ztables---common fields

table---zds
view----zds
function modules-----zds

zextractor---zds(table/view/function modules)

3 types of zds----(trnsaction data///master data--attr/text)



1.for extracting cross application data
2.z tables---z extractors


ss(ds)---ts--psa--tr--cs--ur--dt(ODS)





step by step:
SAP ERP

1.see to that ---ztable/view(se11)/Function module(se37)
2.create a zds---RSO2///CHK THE EXTRACTOR----RSA3


SAP BW
3.REPLICATE DS---TS IS COPIED(SOURCE COLUMNS)

4.ASSIGN INFOSOURCE-------ZENRIC INFOSOURCE--------CS(BI OBJECTS/////TRANSFER RULES)

5.CREATE ODS/DSO

6.CREATE UPDATERULES(IS--ODS)

7.CREATE INFO PACKAGE-INITILIZE///DELTAS


GENERIC EXTRACTION USING A TABLE
SAP ERP
1.CREATE A TABLE---SE11
 DOCNO/EID/PID/CALDAY/SALES/UOM/REV/COST/CURR
2 APPROACHES TO BUILD A TABLE---BUILT IN TYPE///DATA ELEMNT APPROACH
BUILT IN TYPE---SAP DATA TYPES
DATA ELEMNT------DOMAIN/DATA ELEMNTS
BUILT IN TYPES--SAP DATA TYPES

nOTE:GENERIC DS CAN BE INTILIZED BASED ON A SINGLE FILED----TIME STAMP(ENTRY TIME//CALDAY(CREATED ON////NUMERIC POINTER---DOC NO/ITEM NO)


SAFETY INTERVALS----TO AVOID DATA INCONSISTENCY
CDELTA--1------------
SAFETY INTERVAL----LOWER LIMIT////UPPER LIMIT
IF THE DELTA SPECIFIC FILED IS RELATED TO TIME STAMP OR CALDAY---SAFETY INTERVAL---UPPER LIMIT
TIME STAMP---ENTRY TIME---UPPER LIMIT----1800SECS-----30MS
INIT---8AM
DELTA1--------10 AM----30MS----
8AM TO 9.30 AM-----DELTA1--------9.30AM


DELTA2-------11 AM---30MS
9.30------10 30 AM-----------DELTA2---TIME 10.30


INITILIZATION---SAFETY INTERVALS--TO AVOID DATA INCOSISTENCY
TIME STAMP----1800SEC---30MS

INIT--8AM
SYS--10 AM---30MS--9.30

8AM--9.30 AM------DELT1----9.30


DELTA2-----------11 AM---30MS--10 30

9.30 TO 10 30--D2


CALDAY---UPPER LIMIT----1 DAY

INIT---2ND----------DE1--5TH--1DAY--4TH
2 TO 4TH


NUMERIC POINTER--------LOWER LIMIT------100---------THEY R UPDATE-----ODS


1.SELECTION----------------SELECTIVE DATA LOADING-----APPERAS IN INFO PACKAGE DATA SELCTION---FROM /TO

DOC NO----------1 TO 5000
1 TO 3000--------SELCTIVE


2.HIDE:TO EXCLUDE THE UNWNATED FILEDS FROM THE DS----UOM


SS(DS)---TS


SAP ERP(R3BWCON)-------RFC(SM59)---------SAP BI SYS(B3TCLNT800)(TO FIND THE LOGICAL SYS--SCC4)



0UNIT-------------TO/GAL/CAN/EA

FT--DT--SID---MDATA TABLES

DIM TABLE--DATA PACKAGE DIMENSION-----0UNIT--SID-------T006/0CURRENCY--SID----TCURC

TO---SID----T006

ONCE INTILIZATION IS DONE IF U Want to go for full load---use reapair full request

repair full request





Generic extraction using --------------view

1.create a view(se11)----2 or more table---view---common field

2.create a ds---rso2//chk the extractors---rsa3


sapbw
3.replicate ds

4.assign info source

5.create ods

6.create ur

7.schedule---init///deltas


common fileds--from only one table

amount///quantity------------referencing object---table/column


co---pa data extraction-----------------------controling and profitability analsys

types of extractors----application specifc ///cros appplication specific

cross application---genric extarctor


application specific-----business content////////generic(customer)------co/pa


ss(ds)---ts---psa--tr---cs--ur--dt

data source?


in controling----create  the data source on operating concern

what is an operating concern---root node of ur controling organizational stru---simialr to ur info cube--chars/key figures

opearting concern also---char values(chars)///value fileds-----key figures

operating concern is created by controling consultat------4 tables-----ce1(operating concn name)///ce2(operating concern name)////ce3(opconcrn name)////ce4(operating concern name)

sd-orders---user


controling------many applications

controling---------------------planed data////actual data


operating concern--co consualatnts


sap r/3
1.create a data source-------keb0
2.chk the extarctor-------rsa3

sap bw sys
3.replicate ds
4.assign infosource---application specific
5.create info cube
6.update rules
7.info package





crsoss application data---Generic extractors////application---specific--genric---co/pa

application specific-----business content extractors---sap deliverd

logistics data extractors---logistics applications


logistics----16 business applications---

02--------purchasing
03---inventry 
05---qualty mgt
11-------sales orders
12---deliveries
13--biling

11---sales orders

logistics data-------------header data////item data///schedule line data


business content-----------------2lis_11_vahdr
   2lis_11_vaitm
   2lis_11_vascl
12--------------Deliveries-----2lis_12_vchdr///2lis_12_vcitm
13--------------billing------------------2lis_13_vdhdr///vditm

lo extractors------------16 applications


ss(ds)---ts--psa--tr---cs--ur--dt

logistics----sd--orders---sales orders-------------sap tables---vbak/vbuk/vbrk

ecs---enterprize communication structure
es----extract structure

std/document table(vbak/likp/vbrk)---------ecs-----(setup table)--(full/init)-------extract stru-(10+2)-----------Data source--------sap bw sys


set up table---extract stru------------------------se11----------extractstru_setup
vbak------mcvbak
likp-----------mclikp
vbrk------mcvbrk

table---str+data
stru----structure


vbak/likp(document table)---------ecs--setup tables-----es----ds---ts--psa--tr--cs--ur--dt---queries/reports



sales orders--header data extractions---2lis_11_vahdr////////2lis_11_vaitm////////

sap r/3
1.install the data source from business content----rsa5
2.Mainatin extarct structure(addtional columns added from ecs) and data source---------lbwe


sap bw sys
3.replicate data source---ts is copied to bi sys
4.assign a business content info source----cs///tr
5.install bc infocube//////genric info cube
6.create update rules

sap erp
7.fill the data to setup tables---sbiw---(oli*bw)--11--OLI7BW////12----OLI8BW///13---OLI9BW/////EXTRACTOR CHECKER---RSA3

8.create info package---init///deltas


se11-----------------roosource/rodeltam------------ds is compatible with info cube or ods
(notes:if ur not allowed tomaintain extract stru due to data in setuptable delte data from set up tables--lbwg)



LO DELTAS:3 TYPES OF DELTA MECHANISMS--LO DELTAS

1.QUEUED DELTA
2.DIRECT DELTA
3.UNSERIALZED V3 UPDATE

SERIAILIZED V3 UPDATE---DUE TO TIME STAMP ERRORS IT IS REMOVED



lo delta---Logistics delta extraction

there r 3 types of delta mechanisms--1.Qued delta///2.Direct delta////3.Unserialized v3 update

1.lbwq---extraction queue
2.rsa7---delta queue
3.sm13---update queue


1.Queued Delta------lrage volumes of data/serializtion

sd---user creating reord---std tables----------lbwq----------collect the data to rsa7(jobs@lbwe)---info package(delta---sap bi sys)


user std table--v2------lbwq----------jobs@lbwe-----rsa7--ip(delta)--sap bw sys

2.Direct delta:

user std table----v1----rsa7--------ip(delta)---sap bi sys

small volumes data///limited serialization

3.unserialized v3 update:large volumes///no serialization

user std table---------------------sm13-------jobs@lbwe-----rsa7---infopackage(delta)----bi sys


1.setup Delta mechanism---lbwe


note:in rsa7 sys mantains luw related to last delta and current delta------
Why last delta luw?

incase of last delta load fails we have to request a repeat delta--to do thid last delta luw mantained
1 record----1 luw

direct delta--1 record---1luw
qued delta--group of reords---1 luw
unserialized----data packet---


init---d1/d2/d3/d4//d5/d6/d7

if delta4 is failed------------------how to coorect this error---repair full request



sap erp Master data extraction from sap r/3 sys---for all the applications

fl----ap/ar/gl--------------------(fi---sl)
co---pcc/gcc------pa
hr

logistics---lo extractor
ca---genric extractors

business content extractors

sap erp
1.install ur business content data source----rsa5
2.Maintain ur data source---rsa6

sap bw
3.replicate ds
4.assign bc infosource---if the infosource is direct update is--create infopackage--init/deltas
 if it is flexible update---1.use insert char as data target
   2.create update rules
   3.schedule--init/deltas


erp
vendor------attr data


0vendor_attr 





Data source enhancements:If the deleiverd data sources need to be added with addtional columns use enhancements


0consumer_attr---------sales org
0consumer_attr----10+adding --sales org


sap r/3
1.adding the columns(appending stru)----rsa6
2.populate the data------user exit---rsAP0001---cmod

sap bw/bi sys
3.replicate ds----the new filed is transferd
4.assign infoosurce---new filed maaping
5.in the target see to that the new column is available----0consumer---sales org (attr)
6.create update rules
7.info package------init/deltas



DB eXTRACTIONS:

ORACLE-----------------DB CONNECTOR---------------------SAP BI SYS(DB CLIENT//DBSL---MUST BE INSTALLED BY BASYS TEAM ON BI SERVER)


DB(TABLE/VIEWS)------SAP BI SYS----6db_table/view


1.CREATE THE DATA SOURCE-----RSDBC IN BI SYS
2.ASSIGN INFOSOURCE
3.DATA TARGET
4.UPDATE RULES
5.SCHEDULE THE DATA

PERFORMANCE:

ss-----------extraction time--------------sap bi sys------query run time---------bex suite(queries----------analyzer//web application designer)


The Query run time--------1.Query init time///2.DB read time//////3.Olap Aggregation time///4.Mdata read time///5.ovearall query run time


1.query init time----time taken by olap processor to start up ur query------rows and columns

2.DB read time----relevant data from database-----time taken to read the data base

3.Olap aggreation time---as per ur query design it has to aggregate the data

ic
eid/calweek/month///sales
100/1/jan/1000
200/1/jan/1200
100/2/jan/1300

eid/sales

4.Mdata read time--ft--dt--sid--mdata---to read the data from mdata tables

5.overal query run time---total query run time----

1.compression
2.indexes
3.aggrgates///rollups
4.partioning
5.attr change run
6.line item dimension
7.process chains