Wednesday, August 22, 2012

SAP-BI@Reporting@Read Mode of the Queries

Read Mode of the Queries:-
-------------------------
3 read modes:-
------------
1) Query to read all data at once (A)
2) Query to read data during Navigation (X)
3) Query to read data on demand when  Navigating/Expanding Hier (H)
* RSRT - READ MODE FOR A QUERY.
* RDMD - Read mode for all the queries on a particular Info Provider.



Labels: , , , , , , ,

SAP-BI@How to Load MD -ATTR/TEXT from Flat file

How to Load MD -ATTR/TEXT from Flat file?
-----------------------------------------
Steps:-
-------
1) Create Application Component.
2) Create Info Source.
*  Info Source will define Communication Structure.
*  Communication Structure is specific to SAP BW.
* Communication Structure is grouping of logically related info objects indicating in what structure format the SAP BW takes in the data.
*  Info Source will be of 2 types
a) Direct Update
b) Flexible Update
3) Create Source System connection between faltfile and SAP BW.
Types:-
-------
1) SAP Automatic   - SAP
2) SAP MAnual    - SAP
3) SAP BIW(Myself) - SAP BW to BW
4) Flat file Interface - Flat file
5) DB Connect  - RDBMS ( oracle,sql    server,msaccess,DB2
6) External System with BAPI - ETL   Tools(Informatica,Tipco,Web   methodsd) (Hyperion)
BI 7.0
------
1) UD Connect:- runs with J2EE application   and can be used to any    database.
2) Web services:- To extract from XML files

* when we craete the SS connection the system will configure IDoc's.
* IDoc stands for Intermediate Douument and it is standard for transfering data in SAP environment.
* 2 types of Idocs
a) Data idoc - used to transfer Data
b) Info Idoc - used to transfer information
4) Assign Data Source to Info Source.
* Data Source will define Transfer structure and transfer rules
* Transfer structure is specific to source system.
* Transfer structure is grouping fileds indicating in what structure format data is coming from the SS.
* Mapping B/W Trnasfer Structure and Communication Structure - Trnafer Rules.

Types of Data Source:-
-----------------------
1) Attr
2) Text
3) Hier
4) Transaction Data
5) Create Info Package and run the load.
* Info package is specific to Data source

Labels: , , , , , , ,

SAP-BI@Query Design

Query Design:-
-------------
1) Include / Exclude.
2) Reduce the Result Set of the query.
 a) More Characteristics into Free   Characteristics block.
 b) Place one 0r two    Characteristics with less   Cardinality into Rows.
3) Reduce the Formatting.
4) Exceptions. - only for result
5) Reduce the usage of customer Exits.
 - Variable with Customer Exit
  (RSR00001).
 - Virtual Key figures & Virtual   Characteristics.
  (RSR00002).

Labels: , , , , , , ,

SAP-BI@ExtractionMethod-CO-PA Extraction

CO-PA Extraction
----------------
Introduction:-
--------------
1) CO-PA stands for Controlling - Profitablility analysis.
2) We can do Planning in CO-PA. So we will 2 types of transactions in CO-PA a) Actual and b)Plan.
3) Value type :- is used as indicatore for Actual/Plan.
4) Actual transaction is more detailed and Plan transaction is more Aggregated.
5) Planning can be in multiple versions.(Version)
6) In BW we do 0Vtype reporting on CO-PA Cube.
7) Co-PA is an Integration Module.
8) Each actual transaction is stored in 3 different curriencies.ex:- Company code Currency,Transaction currency,Operating Concern currecy.
9) Currecncy Type :- Used as indicator of the currency.
10) CO-PA comes as part of customer generated extractors.
11) CO-PA datasource is generated based on Operating Concern.
12) Operating Concern is the top most Legal entity for controlling which will have its own Data structure with Value fields and Charcteristic fields.
13) CE1xxxx - Actual Line Item table
    CE2xxxx - Plan Line Item table
    CE3xxxx - Segment level table
    CE4xxxx - Segment level table
14) We don't have a Business Content DS for Co_PA because CO-PA DS is based on Operating Concern and the Data Structure of Operating Concern can be different with specific to implementation.
How to generate the DS for CO-PA?
----------------------------------
Steps:-
-------
1) GO to T-code (KEB0)
2) Specify the CO-PA DS name in the given format. - 1_CO_PA%CL%ERK
* Max length of a CO-PA DS name is : 19.
3) Select Create,Operating Concern name
4) Specify Accounting based / Costing Based.
4.a) Costing based - Detailed Level of information, Accounting BAsed - Aggregated Information.
4.b) Costing BAsed - Product based company, Accounting based - service based company
4.c) Costing BAsed - Compnay code field is mabndatory, Accounting based - Company code, Controlling area, Cost element are mandatory fields.
5) Click on 'Execute' button.
6) In the next screen type '=init' in the command bar.
7) Field name for partitioning :- which segment.
8) generate the Catalog.
9) Save it
10) Generate
11) Replicate the DS.
How to Migrate the Data:-
-------------------------
* CO-PA runs delta based on timestamp.
* yyyymmddhhmmss (Time stamp).
* Timestamp information of a CO-PA DS can be viewed in T-Code (KEB2).
* Giving minimum of half hours time we call it as Safety delta.
1) Run thr Init load.
2) Once the Init is successful wait for minimum of Half hours time and schedule the delta loads.
Re-alignment of Timestamp:- (KEB5)--------------------------

Labels: , , , , , , ,

SAP-BI@OLTPvsOLAP

Existance of an entity is not the business, but these entities should interact with each other to perform transactions, set of these transactions becomes business.
OLTP:-
----
When ever we perform a transaction, we record this transaction into the database.
* In OLTP System we generate Transaction data.
* Transaction Data:- data representing a particular transaction.
* Master Data:- Detailed Information about the Entities.
Customer, Material, Vendor,
OLAP:-
-----
* Online Analytical Processing
* As part of OLAP we extract all the transaction data from different OLTP Systems and store data and provide Analytical Data.
* Analytical Data:- Data which can be used for Analysis.
* SAP BW is an OLAP Application.
OLTP V/s OLAP or Why OLAP or Why SAP BW:-
---------------
1. When we do Reporting in OLTP it degrades the Performance of OLTP System, So we extract all the transaction data from different OLTP Systems and store data in OLAP and provide Analytical Data.
2. In OLTP we dont store TD for Longer time, So in OLAP we store Historical Data fro 5 - 10 Yrs.
3. In OLAP we can Integrate/Consolidate the data coming from different OLTP Systems in OLAP.
4. In OLAP we can provide authorizations based on the Data, But in OLTP we provide authorizations based on the reports/Transactions. because of this if we do reporting in OLTP system it affects ROI.
5. In OLAP we can have more Analytical reports like one of the Top customer did not buy one of your Top Product.

Labels: , , , , , , ,

SAP-BI@Types of Update Rules

Types of Update Rules:-
-----------------------
Keyfigure:-
-----------
1) Direct Mapping
2) Routine
------------
 a) comm_structure
 b) routine_0001
* Return Table
* Unit CAlculation in the Routine
3) Formula
Characteristic:-
----------------
1) Direct Mapping
2) Constant
3) Routine
4) Formula
5) Master Data Attribute of
6) Initial Value
Time Characteristic:-
---------------------
1) Direct Mapping
2) Constant
3) Routine
4) Formula
5) Master Data Attribute of
6) Initial Value
7) Time Distribution
Note :- Automatic Time Conversions





 

Labels: , , , , , , ,

SAP-BI@Types of Transfer Rules

Types of Transfer Rules:-
-------------------------
a) Direct Mapping
b) Constant
c) Routine or Transfer Routine
TRAN_STRUCTURE-/BIC/ZFCSAL * 50.
'Z'
TRAN_STRUCTURE-/BIC/ZFCNO
RESULT = 'Z' + TRAN_STRUCTURE-/BIC/ZFCNO.
CONCATENATE 'Z' TRAN_STRUCTURE-/BIC/ZFCNO INTO RESULT.
d) Formula

Labels: , , , , , , ,

SAP-BI@Reporting@Variables

Variables:-
----------
* To Parameterise the queries.
* To make queries dynamic.
Types of Variables:-
--------------------
1) Characteristic value Variable
2) Text Variable
3) Formula Variable
4) Hierarchy Variable
5) Hierarchy Node Variable
Processing Types of Variables:-
-------------------------------
1) User Enter / Default
2) Customer Exit
3) SAP Exit
4) Authorization
5) Replacement Path
Case : I
--------
Characteristic value Variable with User Entry / Default.
---------------------------------------------
Case : II
---------
* Characteristic value Variable with Replacement path
---------------------------------------------
* when we want the Characteristic value Variable to be replaced with the query result of other query.
Case : III
----------
* Characteristic value Variable with SAP Exit.
---------------------------------------------
* All Business content Variables will have the process type as SAP Exit.
* We will never work with this.
Case : Iv
----------
* Characteristic value Variable with Authorizations
---------------------------------------------

Labels: , , , , , , ,

SAP-BI@RRI (Report - to - Report Interface )-RSCRM_REPORT-

RRI (Report - to - Report Interface ):-
----------------------------------------
* We Use RRI to jump(Drill trough) from one Bex Report to other objects like ( BEx Report, Web Report, Web Address, Crystal Report, Transaction, Infoset Query, ABAP Report ).
* T-CODE (RSBBS).
* on CO-PA cube ( ZSDCOPA ).

RSCRM_REPORT:-
--------------
* By using RSCRM_REPORT we can sechedule the report in the background and place the output of the report into a table,Fixed length file, CSV file.
* T-Code is - RSCRM_REPORT
* Limitation of RSCRM_REPORT:-
------------------------------
* When we retract the data using RSCRM_REPORT we can only retract Data but not the Header file.

Labels: , , , , , , ,

SAP-BI@Reporting@Restriction-Condition-Exception-

Restriction:-
---------
* When u want to restrict the output of the query for particular values of a Characteristic.
*Conditions:-
-----------
* When u want to restrict the all the coloumns of the query based on the values of a Key figure - Conditions.
* Active
* Can we build the Conditions for specific Characteristics - yes.
* We can build conditions only on the Structure Elements (Keyfigures used in Coloumns).
* We can even switch on/off the condition at the runtime of the query.
* We can have multiple conditions in a report.
* When we build a condition with multiple restrictions in it - it plays with OR and when we build multiple conditions with each of the restrictions in it - it plays with AND.
* we have used the conditions To design Top 10 Customers Report and Top 10 Products Report.
* Exceptions:-
-------------
* Exceptions are used to provider some alerts on the output of the report based on the values of a key figure.
* We can build Exceptions on only those keyfigures which are acting as Structure Elements (Keyfigures used in Coloumns).
* By using 'Cell restriction' tab we restrict the cells on which the exceptions should play.
* Using Exceptions would degrade the performance of the queries.
* When we were desiging the 'Profitability Analysis Report' we have used the Exceptions to play only on to result.

Labels: , , , , , , ,

SAP-BI@Reporting@Structures-CellDefinitions-RestrictedKeyfigure-NewSelection-CalculatedKeyfigure-NewSelection-NewFormula

* Structures:-
-------------
* Structures act as the reusable components.
* Structures can be used to provide hierarchical effect or Level Up - level Down effect in the report by using Level-up level down.
* we can only have maximum of 2 structures in a report.
* Structures can be of 2 types
 a) Local
 b) Global
* By using the option 'Remove Reference' changes what we do for a global structure will not be reflected to other queries where it has been used.
* Cell Definitions:-
--------------------
* We can define Cell definitions only when we are using 2 structures in report.
* By using Cell Definitions we can do calculation or restriction specfic to a particular CELL.

* Restricted Keyfigure:-
-----------------------
* when we want restrict the value of a Key fugure based on the value of a characteristic.
* We can do restriction on Multiple characteristics.
* Restricted Key figure restricts only to a particular coloumn.
* Restricted key figure is global because once it is created it can be used on all the queries built on the same info provider.


*New Selection:-
----------------
* Same as Restricted Key figure but New selection is Local.
* New Formula:-
---------------
* Same as Calculated Key figure.
*Calculated Key figure / New Formula
------------------------------------
* Calculated Key figure is Global, New formula is Local.
* When compared to Calculated Key figure, New Formula will have 6 functions more.
* All Key figures can be used in Building Calculated Key figures but we can only use the key figures which are used as the structure elements in New formula.

* Restricted Keyfigure:-
-----------------------
* when we want restrict the value of a Key fugure based on the value of a characteristic.
* We can do restriction on Multiple characteristics.
* Restricted Key figure restricts only to a particular coloumn.
* Restricted key figure is global because once it is created it can be used on all the queries built on the same info provider.
*New Selection:-
----------------
* Same as Restricted Key figure but New selection is Local.
* New Formula:-
---------------
* Same as Calculated Key figure.
*Calculated Key figure / New Formula
------------------------------------
* Calculated Key figure is Global, New formula is Local.
* When compared to Calculated Key figure, New Formula will have 6 functions more.
* All Key figures can be used in Building Calculated Key figures but we can only use the key figures which are used as the structure elements in New formula.

Labels: , , , , , , ,

SAP-BI@Products of SAP

Products of SAP:-
----------------
1) SAP R/3:-
------------
* This is an OLTP system
* PL + DB + OS + Concept
* ABAP/4 + any DB + (UNIX / Windows ) + ERP
* ERP - Enterprise Resource Planning.
Enterprise :- Very Big Company
Resources :- 4 m's
men, Machinery, material, Money
Planning:- any thing we decide in advance
* By Using this concept of ERP we can integrate all the transactions in the Business and reduce the mis manupulation of resources.
2) SAP BW:-
----------
* This is an OLAP Application
* PL + DB + OS + Concept
* ABAP/4 + any DB + (UNIX / Windows ) + DW
3) SAP CRM:-
------------
* Customer Relationship Management
* PL + DB + OS + Concept
* ABAP/4 + any DB + (UNIX / Windows ) + To Retain the existing Customer.
* Sub Modules:-
--------------
1) Internet Sales
2) Mobile
 a) Sales
 b) Services
3) Call centers
 a) Sales
 b) Services
4) SAP APO:-
-----------
* Advanced Planning Optimizer (Planning Application).
* PL + DB + OS + Concept
* ABAP/4 + any DB + (UNIX / Windows ) + Production Planning.
* Sub Modules:-
----------------
1) DP - Demand Planning
2) SNP - System Network Planning
3) GATP - Global Availability to promise
4) PP/DS - Production planning and   Scheduling.
5) TP/VS - Transportation and Scheduling.
5) SAP SEM:-
------------
* Strategic Enterprise Management - Financial Planning
* PL + DB + OS + Concept
* ABAP/4 + any DB + (UNIX / Windows ) + Financial Planning.
* Sub Modules:-
-------------
1) BPS - Business Planning and Simulation
2) CPM - Corporate Performance Monitor
3) SRM - Stake Holder Relationship Management
4) BCS - Business Consolidation and   Simulation.
6) Enterprise Portals:-
------------------------
* Single Sign on.
* we can publish reports of any application on to portals.






















Labels: , , , , , , ,

SAP-BI@Reporting

Reporting:-
----------
* BEx ( Business Explorer )
Components:-
-------------
* Analyzer - To Design, Change, Execute, Delete BEx queries by Using a Component - Query Designer.
* Browser - To Organise the Reports.
* WAD (Web Application Designer ) - To Design Web Reports.
Note:-
-----
1) Info Provider
2) Data Target
3) Data Provider
(All Data targets are Info Providers but all Info Providers are not Data Targets)
Analyzer (MS Excel with ADD-ON Components):-
-----------
* How to Design a Simple BEx Report?
------------------------------------
* Understanding the BEx Report Output.
----------------------------------------
1) Title of the Report
2) Navigation Block
3) Result Set of the Query.
* Query Execution Process:-
---------------------------
When we execute the BEx query it triggers the OLAP Processor and this identifies the Info Cube on which the BEx report should be executed on and triggers the query on to the Info Cube and selects the records and aggregate the records based on the Characteristic Values in the OLAP Processor and transfers the records to the Front end (BEx).
* Complex reporting with all the Aspects:-
------------------------------------
* Characteristic Properties:-
------------------------------
1) Heading of the Coloumn
2) Display as
3) Suppress Result Row
contd....
* Key Figure Properties:-
--------------------------
* Query Properties:-
--------------------










Labels: , , , , , , ,

SAP-BI@Calculated Key Figure

Calculated Key Figure:-
----------------------
* When we calculate a new keyfigure based on the existing Keyfigures by performing some calculations and using some pre-defined functions.
* When we use Calculated Key figure it performs the same calculation through out the entire coloumn.
* Functions:-
-------------
- Basic Functions:-
-------------------
1) High(**) - Power of
 ex:- 2 high 5 = 2^5 = 32.
- Percentage Functions:-
-------------------------
2) Percentage Share(%A):- To calculate    Percentage.
 ex:- 40 %A 200 = 20.00%.
3) Percentage Variance(%) :- To Calcualte the         Percentage on         the Variance      (difference) on 2     operands.
 ex:- 40 % 200 = -80.00 %.
- Data Functions:-
------------------
4) Count - Value =1 if operand is not equal to 0 and if the operand is equal to 0 then it returms 0.
 Ex:- Count(12) = 1.
      Count(0)  = 0.
5) Date :- This function converts the numeric value into Date format by counting the number of days from 01.01.0001.
 ex:- Date(14) = 15.01.0001
  01.01.0001 + 14 days =      15.01.0001.
6) Delta :- Value =1 if operand is equal to 0 and if the operand is not equal to 0 then it returms 0.
 Ex:- Delta(12) = 0.
      Delta(0)  = 1.
7) NODIV - We use this function to control divide by Zero exception by converting infinite value into 0.
 Ex:- NODIV( 1500 /0 ) = 0.
8) NODIM - This function can be used to see the Key figure value without unit dimension.
 Ex:- if we have Revenue as 150.00 INR Then NODIM(Revenue) = 150.00.
 Ex:- if we have Quantity as 50.00 KG Then NODIM(Quantity) = 50.00.
9) NOERR - We use this function to control the exceptions when the keyfigure is not able to define the currency/unit for it.
10) Time :- We can convert the numeric value into time format by counting each value as one second starting from 00:00:00.
 Ex:- Time(100) - 00:01:40.
- Mathematical Functions:-
---------------------------
- Boolean Operators:-
---------------------

































Labels: , , , , , , ,

SAP-BI@Line Item Dimension

Line Item Dimension:-
-------------------
* When only one characteristic is assigned to the Dimension ( and ).
* When the caridinality of Dimension table is more than the 20% of the Fact table.
* when we make a Dimension as Line Item Dimension the Dimension ID in the Fact Table will be Refering to the SID Table.
NOTE : RSDEW_INFOCUBE_DESIGNS  - will give the cardinality of the dimension tables

Labels: , , , , , , ,

SAP-BI@Indices or Index of the Info Cube

Indices or Index of the Info Cube:-
-----------------------------------
2 types of Indices:-
--------------------
1) Primary Index
2) Secondary Index
 2.a. Bitmap Index
 2.b. B-Tree Index
2.a. Bitmap Index:-
* When the caridinality of Dimension table is less than the 20% of the Fact table.
2.b. B-Tree Index:-
* When the caridinality of Dimension table is more than the 20% of the Fact table.
NOTE : RSDEW_INFOCUBE_DESIGNS  - will give the cardinality of the dimension tables.
* Card Height ( check box )
Note :- When we Build indices on a Info Cube it improves Query Performance but it Degrades Loading Performance subject to Data Volume.

 

Labels: , , , , , , ,

SAP-BI@Start Routine

Start Routine:-
---------------
* Start Routine will Execute first before the Update rules.
* Start Routine will execute for the entire Data Packaet once.
* Name of the Start Routine - ROUTINE_9998
* We can also write Start routine in Transfer rules only if the transfer method is PSA.
* In BI 7.0 we also have END Routine.
* once info source can feed multiple cubes.
* multiple info sources can feed one cube.
* one info source can have multiple Data sources but same data source cannot be assined to multiple info sources.
---------------------------------------
zstru
f1 f2 f3 f4
c1 c2 10 20
--------------------------
table : DATA_PACKAGE
F1 F2   F3 F4
-------------

C2 EUR M2  20
C3 PAC M3  30
LOOP AT DATA_PACKAGE.
 IF DATA_PACKAGE-F2 = 'AMR'.
  DELETE DATA_PACKAGE.
 ENDIF.
ENDLOOP.



 

Labels: , , , , , , ,

SAP-BI@Compression of Info Cube

Compression of Info Cube:-
-------------------------
* /F AND /E are the 2 fact tables.
* when we load data into the Info Cube it goes into /F table and when we compress the Cube it deletes the request id information and aggregates the records based on the Characteristic value combination and puts the Data into the /E table.
* When we do reporting on the Info Cube data comes from /F and /E tables.
* Once compress is done in the Info Cube we cannot delete data based on the request in the Info Cube, But we can solve the problem by doing request reverse posting only if data is available in PSA.
* In Collapse Tab when we specify a request and compress the cube - it only compresses the specified request and all the request below it.
* With Zero Elimination - after compression if it finds any records with all its keyfigure values being zero it deletes those records.

Labels: , , , , , , ,

SAP-BI@Aggregates

Aggregates:-
-----------
* Smaller Cube built on a Info Cube to Improve the Query pderformance.
* If we dont rollup the request it will not be available for reporting.
* Thumb rule for Aggregate - DB time is greater than 30% of the total time and Aggregation Ration > 10.
* Activate / Deactivate
* Switch on / off
* Delete
* nAVIGATIONAL aTTRIBUTE CAN BE USED IN BUIDING THE AGGREGATE.
* Attribute change run
* Aggregate hierachy levels
Limitation:-
----------
1) It stores data redundently.



Labels: , , , , , , ,

SAP-BI@Types of ODS

Types of ODS:-
--------------
1) Standard
2) Transactional
1) Standard:-
--------------
* Data Target and Info Provider
2) Transactional:-
--------------------
* Used only for Planning.
* Not available for Reporting.
* Only Active Data Table.
* Loading Data into Sebsequent data targets is possible.

Labels: , , , , , , ,