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
Labels: sap bw Interview Points
0 Comments:
Post a Comment
Subscribe to Post Comments [Atom]
<< Home