World of SAP BI/BO

   Ans : This “virtual” characteristic is getting a value assigned at query runtime and must not be loaded with data in data target. Therefore, no change to existing update rules.

   The implementation can be divided into the following areas:

   1. Create of InfoObject [Key Figure / Characteristics] and attach the InfoObject to the InfoProvider.

 2. Implementation of BADI RSR_OLAP_BADI (Set filter on Infoprovider while defining BADI implementation)

   3. Adding the InfoObject into the Query.

   Ans :

   i. Don’t show too much data in initial view of report output

   ii. Limit the level of hierarchies on initial view

   iii. Always use Mandatory variables

   iv. Utilize filters based on InfoProviders 

   v. Suppress Result rows if not needed

   vi. Eliminate or Reduce Not logic in query selection

Ans      : Standard DSO – Max. 16 key fields can be created.

  · Standard DSO

  · Write-Optimized DSO -

  · Direct DSO - The DataStore object for direct update differs from the standard DataStore object in terms of how the data is processed. In a standard DataStore object, data is stored in different versions (active, delta, modified), whereas a DataStore object for direct update contains data in a single version. Therefore, data is stored in precisely the same form in which it was written to the DataStore object for direct update by the application. In the BI system, you can use a DataStore object for direct update as a data target for an analysis process

5. Line Item Dimension : If dimension table size (no. of rows) exceeds 20% of fact table size, then the dimension should be flagged as Line Item Dimension. This means that the system does not create a dimension table. Instead, the SID table of the characteristic takes the role of dimension table. Removing the dimension table has the following advantages:

      ○ When loading transaction data, no IDs are generated for the entries in the dimension table. This number  range operation can compromise performance precisely in the case where a degenerated dimension is involved.

     ○ A table- having a very large cardinality- is removed from the star schema. As a result, the SQL-based     queries are simpler. In many cases, the database optimizer can choose better execution plans.     Nevertheless, it also has a disadvantage: A dimension marked as a line item cannot subsequently include   additional characteristics. This is only possible with normal dimensions.

6.  High cardinality: This means that the dimension is to have a large number of instances (that is, a high cardinality). This information is used to carry out optimizations on a physical level in depending on the database platform. Different index types are used than is normally the case. A general rule is that a dimension has a high cardinality when the number of dimension entries is at least 20% of the fact table entries. If you are unsure, do not select a dimension having high cardinality.

    Real-time-enabled InfoCubes can be distinguished from standard InfoCubes by their ability to support      parallel write accesses, whereas standard InfoCubes are technically optimized for read accesses. Real-time InfoCubes are used when creating planning data. The data is written to the InfoCube by several users at the same time. Standard InfoCubes are not suitable for this. They should be used if you only need read access (such as for reading reference data).

    Real-time-enabled InfoCubes can be filled with data using two different methods: Using the BW-BPS transaction for creating planning data and using BW Staging. You have the option to switch the real-time InfoCube between these two methods. From the context menu for your real-time InfoCube in the InfoProvider tree, choose Switch Real-Time InfoCube. If Real-Time InfoCube Can Be Planned, Data Loading not Allowed is selected by default, the Cube is filled using BW-BPS functions. If you change this setting to Real-Time InfoCube Can Be Loaded with Data; Planning Not Allowed, you can then fill the Cube using BW Staging.

      For real-time InfoCubes, a reduced read performance is compensated for by the option to read in parallel (transactionally) and an improved write performance.

     Remodeling is a new feature available as of NW04s BI 7.0 which enables to change the structure of an InfoCube already loaded without disturbing data. This feature does not yet support remodeling of DSO and InfoObjects.

     Using remodeling a characteristic can be simply deleted or added/replaced with a constant value, value of another InfoObject (in the same dimension), with value of an attribute of another InfoObject (in the same dimension) or with a value derived using Customer Exit.

Similarly a KeyFigure can be deleted, replaced with a constant value or a new KeyFigure can be added and populated using a constant value or a Customer Exit.

This article describes how to add a new characteristic to InfoCube using the remodeling feature and populating it using a Customer Exit.

Note following before you start remodeling process:

Note following after you finish remodeling process and start daily loads and querying this InfoCube:

     With Export - Allows you to revert back to 3.x DataSource, Transfer Rules, etc...when you choose this option (Recommended) Without Export - Does not allow you to ever revert back to the 3.x DataSource.

   The replacement of formula variables with the processing type Replacement Path acts differently in calculated key figures and formulas:

If you use a formula variable with “Replacement from the Value of an Attribute” in a calculated key figure, then the system automatically adds the drilldown according to the reference characteristic for the attribute. The system then evaluates the variables for each characteristic value for the reference characteristic. Afterwards, the calculated key figure is calculated and, subsequently, all of the other operations are executed, meaning all additional, calculated key figures, aggregations, and formulas. The system only calculates the operators, which are assembled in the calculated key figure itself, before the aggregation using the reference characteristic.

If you use a formula variable with Replacement from the Value of an Attribute in a formula element, then the variable is only calculated if the reference characteristic is uniquely specified in the respective row, column, or in the filter.

   In the Query Designer, you use selections (e.g. Characteristic restriction in Restricted Key Figure) to  determine the data you want to display at the report runtime. You can alter the selections at runtime using navigation and filters. This allows you to further restrict the selections. The Constant Selection function allows you to mark a selection in the Query Designer as constant. This means that navigation and filtering have no effect on the selection at runtime.

    The customer exit for variables is called three times maximally. These three steps are called I_STEP.      The first step (I_STEP = 1) is before the processing of the variable pop-up and gets called for every variable of the processing type “customer exit”. You can use this step to fill your variable with default values. 

     The second step (I_STEP = 2) is called after the processing of the variable pop-up. This step is called only for those variables that are not marked as “ready for input” and are set to “mandatory variable entry”.

     The third step (I_STEP = 3) is called after all variable processing and gets called only once and not per variable. Here you can validate the user entries.

    Please note that you cannot overwrite the user input values into a variable with this customer exit. You can only derive values for other variables or validate the user entries.

    A structure is created first for extract structure which will contain all datasource fields. Then a Function module is created by copying the FM RSAX_BIW_GET_DATA_SIMPLE. The code needs to be modified as per requirement.

For delta functionality, if the base tables (from where data will be fetched) contain date and time field then include a dummy field (Timestamp) in the extract structure created for the FM and use this field in code. (by splitting the timestamp in date and time).

Type-Pools : SBIWA, SRSC.

15. In which scenario you have used Generic DataSource ?

We had a requirement to send Contract (Sales Order) data from BI to MERICS (external system). Selection criteria to extract data was –

o Billing Plan Date(FPLT-AFDAT) < Current month date

o Billing Status(FPLT-FKSAF) = Not yet Processed

o Contract Type(VBAK-AUART) = Fixed Price (ZSCC)

o Item Category(VBAP-PSTYV) = ZSV2

We could have used Data Source 2LIS_11_VAITM and could have enhanced that for FPLT fields. But the problem was that whenever there will be status change in a Billing Plan, that will not be captured by data source 2LIS_11_VAITM.

Thereby we have created a generic data source using Function Module.

The upper limit for safety interval contains the difference between the current highest value at the time of    the delta or initial delta extraction and the data that has actually been read. If this value is initial, records that are created during extraction cannot be extracted."

This would mean that if your extractor takes half an hour to run , then ideally your safety upper limit should be half hour or more , this way records created during extraction are not missed.

For example : If you start extraction at 12:00:00 with no safety interval and then your extract runs for 15 minutes , the delta pointer will read 12:15:00 and subsequent delta will read records created / changed on or after 12:15:00 - this would mean that all records created / changed during extraction are skipped.

Estimate the extraction time for your datasource and then accordingly set the safety upper limit so that no records are skipped. But then this being an additive delta - you need to be careful not to double your records . Ideally this being an additive delta - either extract records during periods of very low activity or have smaller safety limits to make sure data does not get duplicated.

Safety Interval Lower Limit : This field contains the value taken from the highest value of the previous delta extraction to determine the lowest value of the time stamp for the next delta extraction.

1. If delta field is Date (Record Create Date or change date), then use Upper Limit of 1 day.

This will load Delta in BW as of yesterday. Leave Lower limit blank.

2. If delta field is Time Stamp, then use Upper Limit of equal to 1800 Seconds (30 minutes).

This will load Delta in BW as of 30 minutes old. Leave Lower limit blank.

3. If delta field is a Numeric Pointer i.e. generated record # like in GLPCA table, then use

Lower Limit. Use count 10-100. Leave upper limit blank. If value 10 is used then last 10

records will be loaded again. If a record is created when load was running, those records

may get lost. To prevent this situation, lower limit can be used to backup the starting

sequence number. This may result in some records being processed more than once;

therefore, be sure this DataSources is only feeding an ODS Object

                                                o TimeStamp - The field is a DEC15 field which always contains the time stamp of the last change to a   record in the local time format.

                                                 o Calendar Day - The field is a DATS8 field which always contains the day of the last change.

                                                 o Numeric Pointer - The field contains another numerical pointer that  appears with each new record.

The key figures for extracted data are added up in BW. DataSources with this delta type can supply data to ODS objects and InfoCubes.

Each record to be loaded delivers the new status for the key figures and characteristics. DataSources with this delta type can write to ODS objects or master data tables.

Max. Char. – 248

Max. Key Fig. – 233

    o Standard DTP - Standard DTP is used to update data from PSA to data targets ( Info cube, DSO  etc).

      o Direct Access DTP - DTP for Direct Access is the only available option for VirtualProviders.

     o Error DTP - Error DTP is used to update error records from Error stock to the corressponding data targets.

     o Define lots of small dimensions rather than a few large dimensions.

      o The size of the dimension tables should account for less than 10% of the fact table.

     o If the size of the dimension table amounts to more than 10% of the fact table, mark the dimension as a line item dimension.

   1. In Infosets now you can include Infocubes as well.

    2. The Remodeling transaction helps you add new key figure and characteristics and handles historical data as well without much hassle. This is only for info cube. 

    3. The BI accelerator (for now only for infocubes) helps in reducing query run time by almost a factor of 10 - 100. This BI accl is a separate box and would cost more. Vendors for these would be HP or IBM.

    4. The monitoring has been improved with a new portal based cockpit. Which means you would need to have an EP guy in ur project for implementing the portal ! :) 

     5. Search functionality hass improved!! You can search any object. Not like 3.5

     6. Transformations are in and routines are passe! Yess, you can always revert to the old transactions too.

     7. The Data Warehousing Workbench replaces the Administrator Workbench.

   8. Functional enhancements have been made for the DataStore object: New type of DataStore object Enhanced settings for performance optimization of DataStore objects. 

     9. The transformation replaces the transfer and update rules.

    10. New authorization objects have been added 

    11. Remodeling of InfoProviders supports you in Information Lifecycle Management.

    12 The Data Source:There is a new object concept for the Data Source. Options for direct access to data  have been enhanced. From BI, remote activation of Data Sources is possible in SAP source systems.

    13.There are functional changes to the Persistent Staging Area (PSA). 

    14.BI supports real-time data acquisition.

    15. SAP BW is now known formally as BI (part of NetWeaver 2004s). It implements the Enterprise Data Warehousing (EDW). The new features/ Major differences include:

     a) Renamed ODS as DataStore. 

    b) Inclusion of Write-optmized DataStore which does not have any change log and the requests do need any activation

     c) Unification of Transfer and Update rules 

     d) Introduction of "end routine" and "Expert Routine"

     e) Push of XML data into BI system (into PSA) without Service API or Delta Queue f) Intoduction of BI accelerator that significantly improves the performance.

     g) Load through PSA has become a must. I am not too sure about this. It looks like we would not have the option to bypass the PSA Yes,

    16. Load through PSA has become a mandatory. You can't skip this, and also there is no IDoc transfer method in BI 7.0. DTP (Data Transfer Process) replaced the Transfer and Update rules. Also in the Transformation now we can do "Start Routine, Expert Routine and End Routine". during data load. New features in BI 7 compared to earlier versions:

      i. New data flow capabilities such as Data Transfer Process (DTP), Real time data Acquisition (RDA). 

      ii. Enhanced and Graphical transformation capabilities such as Drag and Relate options.

    iii. One level of Transformation. This replaces the Transfer Rules and Update Rules iv. Performance optimization includes new BI Accelerator feature.

    v. User management (includes new concept for analysis authorizations) for more flexible BI end user authorizations.

    RollUp : You can automatically roll up and transfer into the aggregate requests in the InfoCube with “green traffic light status”, that is, with saved data quality. The process terminates if no active, initially filled aggregates exist in the system.

      Compression : After rollup, the InfoCube content is automatically compressed. The system does this by deleting the request IDs, which improves performance.

      If aggregates exist, only requests that have already been rolled up are compressed. If no aggregates exist, the system compresses all requests that have yet to be compressed.

    First we need to do aggregate roll up before compression. when we roll up data load requests, we roll them up into all the aggregates of the infocube and then carry on the compression of the cube. For performance and disk space reasons, it is recommended to roll up a request as soon as possible and then compress the infocube.

   When you Compress the cube,"COMPRESS AFTER ROLLUP" option ensures that all the data is rolledup into aggrgates before doing the compression.

     Compression - with Zero Elimination – Zero-elimination means that data rows with all keyfigs = 0 will be deleted.

28. What steps need to follow when a process in Process Chain fails and we need to make it green to proceed further?
   1. Right click on the failed process and go to ‘Display Messages’. From Chain tab get VARIANT and INSTANCE value. For some cases INSTANCE is not available, that case we take Job Count Number.
   2. Go to table RSPCPROCESSLOG. Give the VARIANT and INSTANCE and get LOGID, TYPE, BATCHDATE and BATCHTIME
  3. Execute program (SE38) RSPC_PROCESS_FINISH by providing LOGID, CHAIN, TYPE, VARIANT, INSTANCE, BATCHDATE, BATCHTIME and STATE = ‘G’.

    A rule group is a group of transformation rules. It contains one transformation rule for each key field of the target. A transformation can contain multiple rule groups.

    Rule groups allow you to combine various rules. This means that for a characteristic, you can create different rules for different key figures.

      Few key points about Rule Groups:

      o A transformation can contain multiple rule groups.

     o A default rule group is created for every transformation called as Standard Group. This group contains all the default rules.

      o Standard rule group cannot be deleted. Only the additional created groups can be deleted.

      Records in source system: Actual and Plan Amount are represented as separate fields.

      CompanyCode Account FiscalYear/Period ActualAmount Plan Amount

      1000 5010180001 01/2008 100 400

      1000 5010180001 02/2008 200 450

      1000 5010180001 03/2008 300 500

    Records in business warehouse: Single KeyFigure represents both Actual and Plan Amount. They are    differentiated using the characteristic Version (Version = 010 represents Actual Amount and Version = 020  represents Plan Amount in this example).

      CompanyCode Account FiscalYear/Period Version Amount

      1000 5010180001 01/2008 010 100

      1000 5010180001 02/2008 010 200

      1000 5010180001 03/2008 010 300

      1000 5010180001 01/2008 020 400

      1000 5010180001 02/2008 020 450

     1000 5010180001 03/2008 020 500

    To achieve this, In Standard Rule group(target), we will make the char. Version to constant value 010 and direct assignment form Actual Amount to Amount in target field.

And another rule group(New Rule group) will be created where we will make the char. Version to constant value 020 and direct assignment form Plan Amount to Amount in target field.

     DS objects cannot admit any stock key figures (see Notes 752492 and 782314) and, among other things, they do not have a validity table, which would be necessary. Therefore, ODS objects cannot be used as non-cumulative InfoCubes, that is, they cannot calculate stocks in terms of BW technology.

   You use the Replacement Path to specify the value that automatically replaces the variable when you execute the query or Web application.

      The processing type Replacement Path can be used with characteristic value variables, text variables and formula variables.

     o Text and formula variables with the processing type Replacement Path are replaced by a corresponding characteristic value.

     o Characteristic value variables with the processing type Replacement Path, are replaced by the results of a query.

      Replacement with a characteristic value :
      Replace Variable with
        · · Key The variable value is replaced with the characteristic key.
    · · External Characteristic Value Key The variable value is replaced with an external value of the  characteristic (external/internal conversion).
    · · Name (Text) The variable value is replaced with the name of the characteristic.Note that formula variables have to contain numbers in their names so that the formula variable represents a value after replacement.
     · · Attribute Value The variable value is replaced with the value of an attribute. An additional field appears for entering the attribute. When replacing the variable with an attribute value, you can create a reference to the characteristic for which the variable is defined. Choose the attribute Reference to Characteristic (Constant 1). By choosing this attribute, you can influence the aggregation behavior of calculated key figures and obtain improved performance during calculation.
     · Hierarchy Attribute The variable value is replaced with a value of a hierarchy attribute. An additional field appears for entering the hierarchy attribute. You need this setting for sign reversal with hierarchy nodes
     o Example: Replacement with Query You want to insert the result for the query Top 5 products as a variable in the query Sales – Calendar year / month.
    1. Select the characteristic Product and from the context menu, choose New Variable. The Variable Wizard appears.
      2. Enter a variable name and a description.
      3. Choose the processing type Replacement Path.
      4. Choose Next. You reach the Replacement Path dialog step.
      5. Enter the query Top 5 Products.
      6. Choose Next. You reach the Save Variable dialog step
      7. Choose Exit
     You are now able to insert the variable into the query Sales – Calendar year / month. This allows you to  determine how the sales for these five, top-selling products has developed month for month.

    This is different from the normal delta in a way that, if you look at the data load it would say FULL LOAD instead of DELTA. But as a matter of fact, its only pulling the records that are changed or created after the previous load. This can be achieved multiple ways like logic in infopackage routine, selections identifying only the changed records and so on.

    In my past experience, we had a code in the infopackage that looks at when the previous request was loaded, using that date calculates the month and loads data for which CALMONTH is between the previously loaded date and today's date (since the data target is an ODS, even if there is a duplicate selection, overwriting will happen thus not affecting the data integrity).

    If you have less than 16 characters in an aggregate ( including the time , data and package dimensions) then the characteristic SIDs are stored as Line items meaning the E Fact table of the aggregate ( Assuming that your aggregate is compressed ) will have 16 columns and these will have the SIDs only...

     You do not have any further tables like dimension tables etc for an aggregate in this case - hence the name FLAT - meaning that the aggregate more or less is like a standard table with the necessary SIDs and nothing else.

      Flat aggregates can be rolled up on DB Server (without loading data into Application Server)

     A delta update for a master data DataSource is aborted.The data was sent to the BW in this case but it was not posted in the PSA.In addition, there are no, as yet, executed LUWs in the TRFC outbound of the source system.Therefore, there is no way of reading the data from a buffer and transferring this to the master data tables.

      Solution :

   Import the next PI or CRM patch into your source system and execute the RSA1BDCP report. Alternatively, you can import the attached correction instructions into your system and create an executable program in the customer namespace for this using transaction SE38, into which you copy the source code of the correction instructions. Execute the report.

1. P_OS (DataSource): Name of the DataSource

2. P_RS (BIW system): logical name of the BW system

3. P_TIME (generation time stamp):The generation date and time of the first change pointer, which are transferred into BW during the next upload, should be displayed as YYYYMMDDHHMMSS (for example, 20010131193000 for January 31, 2001, 19:30:00).(e.g. 20010131193000 for 31.01.2001, 19:30:00).For this time stamp select the time stamp of the last successful delta request of this DataSource in the corresponding BW system. After the report is executed, a dialog box appears with the number of records that should have the 'unread' status. Check the plausibility of this number of records. It should be larger than or the same as the number of records for the last, terminated request. 

After you execute the report, change the status of the last (terminated) request in BW to 'green' and request the data in 'delta' mode.

    (1) Predefined calculations that render summarized and/or aggregated information, which is useful in making strategic decisions.

     (2) Also known as Performance Measure, Performance Metric measures. KPIs are put in place and visible to an organization to indicate the level of progress and status of change efforts in an organization. KPIs are industry-recognized measurements on which to base critical business decisions.

     In SAP BW, Business Content KPIs have been developed based upon input from customers, partners, and industry experts to ensure that they reflect best practices.

   a) System Trace: Transaction ST01 lets you do various levels of system trace such as authorization checks, SQL traces, table/buffer trace etc. It is a general Basis tool but can be leveraged for BW.

     b) Workload Analysis: You use transaction code ST03

    c) Database Performance Analysis: Transaction ST04 gives you all that you need to know about what’s happening at the database level.

   d) Performance Analysis: Transaction ST05 enables you to do performance traces in different are as namely SQL trace, Enqueue trace, RFC trace and buffer trace.

   e) BW Technical Content Analysis: SAP Standard Business Content 0BWTCT that needs to be activated. It contains several InfoCubes, ODS Objects and MultiProviders and contains a variety of performance related information.

    f) BW Monitor: You can get to it independently of an InfoPackage by running transaction RSMO or via an InfoPackage. An important feature of this tool is the ability to retrieve important IDoc information.

   g) ABAP Runtime Analysis Tool: Use transaction SE30 to do a runtime analysis of a transaction, program or function module. It is a very helpful tool if you know the program or routine that you suspect is causing a performance bottleneck.

    You sometimes run into error message 'Runtime error MESSAGE_TYPE_X' when you try to open an existing delta infopackage. It won't even let you create a new infopackage, it throws the same error. The error occurs in the FUNCTION-POOL FORM RSM1_CHECK_FOR_DELTAUPD.

    This error typically occurs when delta is not in sync between source system and BW system. It might happen when you copy new environments or when you refresh you QA or DEV boxes from production.

    Solution : Try to open a existing full infopackage if you have it, you will be able to open existing full infopackage because it is not going to check delta consistency. After you open the infopacke remove the delta initialization from the infopackage as shown below. Got menu scheduleer -> Initialization options for source system ->Select the entry -> click on delete button

      After that you will be able to open existing delta infopackage. You can re initialize the delta and start using the infopackage.

    Follow the steps in the note 852443 , If you do not have a existing full infopackage. There are many troubleshooting steps in this note. You can go through all of them or do what I do follow the steps below.

     1. In table RSSDLINIT check for the record with the problematic datasource. 

     2. Get the request number (RNR) from the record.

   3. Go to RSRQ transaction and enter the RNR number and say execute, It will show you the monitor screen of actual delta init request. 

    4. Now change the status of the request to red.

    That's it. Now you will be able to open your delta infopackage and run it. Of course you need to do your delta init again as we made last delta init red. These steps have always worked for me, follow the steps in the OSS note if this doesn't work for you.

      o DataSource 0FI_AP_4 (Vendors: Line Items with Delta Extrcation):

o DataSource 0FI_AP_6 (Vendor Sales Figures via Delta Extraction) :

o DataSource 0FI_AR_4 (Cu

stomers: Line Items with Delta Extraction)

o DataSource 0FI_GL_4

o DataSource 3FI_GL_0L_TT (Leading

Ledger (Totals))

DataSource 3FI_GL_Y1_TT (Non-leading ledger (Statutory) (Totals) - Y1)

o DataSource 3FI_GL_Y1_TT (Non-leading ledger (Statutory) (Totals) - Y1)

    In 0MATERIAL: The display key figures are 0HEIGHT(Height), 0LENGTH(Length), 0GROSS_WT (Gross Weight), 0GROSS_CONT (Gross Content).

    o When designing an InfoCube, it is most important to keep the size of each dimension table as small as possible.

      o One should also try to minimise the number of dimensions.

    o Both of these objectives can usually be met by building your dimensions with characteristics that are related to each other in a 1:1 manner (for example each state is in one country) or only have a small number of entries.

    o Generally characteristics that have a large number of entries should be in a dimension by themselves, which is flagged as a "line item" dimension.

    o Characteristics that have a "many to many" relationship to each other should not be placed in the same dimension otherwise the dimension table could be huge.

     o It is generally recommended to do this if the dimension table size (number of rows) exceeds 10% of the fact table's size. You should also flag it as a "line item" dimension in an SAP InfoCube.

      Under ‘Execute’ tab select the processing mode as s

hown in the screenshot.