BI Security

some misconceptions have been found in using the correct tcodes in BI for security and here is a blog to clarify this confusion...

for 3.x concept (Reporting authorization)
use tcodes : RSSM and RSSMQ

for 7.x concept (Analysis Authorization)
use tcodes : RSECADMIN and RSUDO

POSNR - The misleading field in ECC

I was building a DSO the other day and was wondering how to list the key fields and data fields..I was using 2 data sources 2LIS_11_VASTH and 2LIS_11_VASTI and was pulling in data to 2 different datasources and was wondering what info objects should fill in the key fields and data fields. now these 2 data sources come from tables VBUK and VBUP..and in general the key fields for a data source are generally the key fields in the backend ECC tables and i decided to mention the VBELN (0DOC_NUMBER)and POSNR (0DELIV_ITEM)as my key fields for the DSO !

the way i identify fields in ECC and relate them to BI is using this table RSOSFIELDMAP in BI and also table RSRTFIELDSH in ECC....i decided to have 0DELIV_ITEM as the key field since the table VBUP has the key fields POSNR and the corresponding field in RSOSFIELDMAP is 0DELIV_ITEM .....HOW MISLEADING !!

If you look at the various SD tables, VBELN and POSNR are commonly used as the document number and document line item. For VBAP and VBUP, it's referring to the Sales Order Line Item with the columns VBELN and POSNR as the key for both tables. However, if you look at the LIPS table, which is the Delivery Line Item table, it too has VBELN and POSNR as the key. Likewise, the VTTP table, which is the Shipment Line Item table, also has VBELN and POSNR as the key.....the moral of the story for BI folks is to use the table RSOSFIELDMAP with caution and speculation ....POSNR is indeed a mileading field...I just prooved my point here SAP !!

Sales Orders Model part-1

Researching Sales Order models before building a BI model

read the order by looking at VBAK table which is the header table for the order header information. now determine the line items for this table by using VBAP table.
based on the number of line items you can now determine how man schedule lines are there for each line item. i.e some line items can be delivered by breaking down to 2or 3 schedule lines....one line item has 10 as the qty and that 10 can be delivered in 2 schedule lines 3 and 7.this can be determined by table VBEP....now to determine if these line items are delivered or not look at the delivery line table
which is LIPS and determine the several line items being delivered.
the header info for these deliveries can be found in LIKP table.

the table join happens in 2 places: between orders and delivery it is the VBAP table with fields VBAP-POSNR & VBELN with LIPS-VGBEL &VGPOS .....and combining delivery tables and billing tables the join happens between LIPS-VBELN & POSNR and VBRP-VGPOS & VGBEL

Here is a good example of how SD tables are linked and this forms the basis for any BI modelling !

http://www.erpgenie.com/sap/abap/tables_sd.htm

Researching Open Sales Orders Part -1

Building any Business Intelligence Model requires some blueprinting and investigation
from the ECC perspective and below is one classic example....more to come in part-2

so when an order is created a specific number is entered in the quantity field.
this quantity is required to be delivered to the customer when the order is
completed. now, when you create an order table VBAK gets updated with the header
information while the line item info gets updated in VBAP

when a delivery is created for this order the delivery document gets updated
in LIKP with header info and LIPS with line item. to find out if the quantity
ordered has been delivered check the delivery status for each of the line items
in VBUP table and determine if there is anything with the status B or A since
C is complete and we want to identify which of the line items have not yet been
delivered. now use table VBAP and determine the quantity for the line item
which is not yet completed and compare the quantity to the delivery line item
quantity in table LIPS- VGBEL and then check field LFIMG(delivery qty) and determine the difference. If there is a difference then the order status is still incomplete.