Overview on Database Statistics

Scenario:

This scenario occurred during the production support activity for our retail client. The timelines for the load
completion is very critical for our account since the dependencies are more. One such situation occurred
where the activation step for an DSO was stuck for a very long time. On checking the Activation Job Logs,
we found that the job was performing the statistics recalculation as shown in the screenshot below.
This paves way for the document on ‘Database Statistics during DSO Activation’
.
Overview on Database Statistics:

During the DSO activation process the statistics are generated for both ‘00’ (Active table) and ‘40’ (New
Table) tables of the DSO. This can under circumstances slow down the activation process. However this
step is essential for query performance over the DSO.
Note: The Statistics recalculation step does not trigger every time the activation job is executed. This is not a periodic event as well. This process starts when the statistics seemed to have been corrupted.
Ignoring Statistics Recalculation & Resetting:

The statistics recalculation step at times causes an overhead during critical data load activation processes.
For example: Consider the scenario where the DSO data has to be pushed further to a series of Infocubes
on time and the process is stuck in the activation step of the DSO.

The Statistics step can be ignored using the Report: RSSM_SUPPRESS_STATISTICS
Note: The above program mentioned ‘RSSM_SUPPRESS_STATISTICS’ is to be used during the activation process.

Also note that the statistics will have to be manually generated if this option is used.
Procedure to Ignore Statistics Recalculation:
1. Go to SE38 and type the program RSSM_SUPPRESS_STATISTICS and Execute.
2. The program RSSM_SUPRESS_STATISTICS has option to enter the DSO name and it gives 2
checkboxes. The check boxes work according to the following logic. Database Statistics During DSO Activation

1. Check DB St. Deactivated before Act and Check DB St. Deactivated after Act: This option will
suppress the Statistics step totally for the particular DSO.
2. Check DB St. Deactivated before Act and Uncheck DB St. Deactivated After Act: This option will
suppress the Statistics step before the Activation Step and Enable it after the Activation is
complete.(recommended)
3. Uncheck DB St. Deactivated Before Act and Check DB St. Deactivated After Act: This option will
suppress the Statistics step after the Activation Step (not recommended as it would have no effect).
4. Uncheck DB St. Deactivated Before Act and Uncheck DB St. Deactivated After Act: This option will
enable the Statistics step totally for the particular DSO.

Let us proceed with Step 2:

3. Once the above step is executed with DSO name the following screen appears:
4. This program basically changes the fields of the Table RSODSSETTINGS. Database Statistics During DSO Activation

Procedure to Reset the Statistics Settings:

1. Go to SE38 and type the program RSSM_SUPPRESS_STATISTICS and Execute
2. Enter the DSO name and Execute. Please note that the both the check boxes are to be unchecked.
3. Once the above step is executed the following screen appears:Database Statistics During DSO Activation
4. The changes are reverted back in the Table RSODSSETTINGS

Statistics Status During the above Process:

The Statistics will not be generated once the above process is followed. It has to be manually generated from
DB02 for the particular DSO.

Old Statistics:

The performance of the old statistics cannot be conclusive as the decision factor is on how well the current
data set is represented.

Disadvantages of Database Statistics:

Every change happening on the statistics can cause changes to the optimizer. In most of the cases regular
update of statistics will have a positive effect on performance

What's New in BI with SAP NetWeaver 7.0

Use

With SAP NetWeaver 7.0, the following terminology changes have been made in the area of Warehouse Management:

The Administrator Workbench is now called Data Warehousing Workbench.

The ODS object is now called DataStore object.

The transactional ODS object is now called DataStore object for direct update.

The transactional InfoCube is now called real-time InfoCube.

The RemoteCube, SAP RemoteCube and virtual InfoCube with services are now referred to as VirtualProviders.

The monitor is now called the extraction monitor, to distinguish it from the other monitors.

OLAP statistics are now called BI Runtime Statistics.

The reporting authorizations are now called analysis authorizations. We use the term standard authorizations to distinguish authorizations from the standard authorization concept for SAP NetWeaver from the analysis authorizations in BI.

SAP BI Architecture

Flat File Loading for Master Data Attributes

Steps to load flat file:

1.Create Info Area

      Create Info object catalog

     - Char

     - Key Figures

2.Data Source

    Insert Characteristic as info provider

        -Add info object 

Now we can see Master data Attribute/Master data text

     -Info package

3. Info Provider

    -Transformation

    -DTP

Check whether data load into SAP BI by right Click DS, we can see options select manage,  now select PSA Maintenance.

    

Tables in BW

List of BW Tables

/BIC/F (cubename) is the fact table of a content cube
/BIC/D (cubename) P is the package dimension of a content cube
/BIC/D(cubename) U is the unit dimension of a content cube
/BIC/D(cubename) T is the time dimension of a content cube
/BIC/D(cubename) <1-9>, A,B,C,D  is the user defined dimension of a content cube
/BIC/M (InfoObject name) master data of object 
/BIC/T (InfoObject name) text data of object
/BIC/H (InfoObject name) hierarchy data of object
/BIC/S(InfoObject name) SID table of the object.

/BIC/P(InfoObject name) Time-independent Master Data
/BIC/Q(InfoObject name) Time-dependent Master Data
/BIC/X(InfoObject name) table to link SID with SIDs for Time-independent Navigational Attributes
/BIC/Y(InfoObject name) table to link SID with SIDs for Time-dependent Navigational Attributes

/BIC/H(InfoObject name) Hierarchy Tables
/BIC/K(InfoObject name) Hierarchy SID Tables
/BIC/I(InfoObject name) SID Hierarchy Structure
/BIC/J(InfoObject name) Hierarchy Interval Tables

you can replace the BIC with BI0 for business content object and cubes

RSDIOBJ - index of all InfoObjects
RSSELDONE - Monitor: Selections for executed request
RSREQDONE - Monitor: Saving of the QM entries, state of the uploads, all info about the upload via request number, field: RNR

DSOTable names 

1.New data: /BIC/A (DSO NAME)40
2.Active data: /BIC/A (DSO NAME)00
3.Change log: /BIC/B ( NUMBERS)

RSRREPDIR which is the table containing all your reports.
RSTSODS - table that contain relation between "ODS Name" and PSA Table.
TBDLS - Logical system customization, when you run TA: BDLS
EDP21 - Partner Profile, when you run TA: WE20
RSBASIDOC - Assignment of source systems to BIW systems incl. IDoc type
In V_COMPDIR_COMPIC you can also see all your reports. The advantage is that you can see the name/text of the query, not only the technical id.

RSDCUBEIOBJ: Objects per InfoCube (where-used list)
RSDODSOATRNAV InfoObject in ODS (navigational attributes)
RSDODSOIOBJ InfoObject in ODS
RSDIOBJT InfoObject texts
RSDIOBJ Directory of all InfoObjects
RSDKYF Key figures
RSDBCHATR Master data attributes
RSBBSQUERYDIR BW: Directory of the Query Sender
RSBBSQUERY BW: Query Receiver
RSBBSQUERYT BW: Texts for Query Receiver
TQOP Compare Operators for View Maintenance Query
TQOPT Compare operators for view maintenance query (texts)
TQLOT Logical operators for view maintenance query (texts)
ROMSDIOBJ BIW: Assignment of master data attributes to InfoObjects
RODCHA BIW: InfoObjects / characteristics
RODIOBJCMP BIW: InfoObjects / compounding
RODKYF BIW: Key figures
ROAPPLT BIW: Name of the application components
RSUICDONE BIW: Selection table for user-selection update ICs
RODTIM BIW: Time characteristics
RODUNI BIW: Units
RSIST InfoSource texts
RSIS InfoSource (transaction data)
RSISOSMAP Mapping Between InfoSources and OLTP Sources
RSOSFIELDMAP Mapping Between OLTP Source Fields and InfoObjects
RSUPDDAT Update rules key figures
RSUPDKEY Update rule: Key per key figure
RSUPDROUT Update rules - ABAP routine - check table
RSRWBINDEXT Titles of binary objects (Excel workbooks) in InfoCatalog
RSRWORKBOOK 'Where-used list' for reports in workbooks
RSDDIME Dimensions directory
RSDDIMEIOBJ InfoObjects per dimension (where-used list)

BI 7.3 New BI Functionality

1. Overview

This document explains detailed process of using the new functionalities available with BI 7.3 SP4 upgrade and also provide insight design consideration for reporting and BI back end objects

2. Major New Features in BI 7.3



2A: Extraction Transformation and Loading in BI 7.3



New Graphical developer interface & new dataflow wizards for faster development. External delta extraction can be done with DB connect, UD connect, flat files & web services. An Init & delta package for SAP extraction is merged.



New data source called "web service pull" is added. Can schedule data loads & extractions using BOBJ data services. The RSPCMhas many new added features for better process chain monitoring. "Dummy" source systems can be created for development purposes. DTP requests can be archived (faster loads). New MDX test editor for debugging of errors is available.



PSA in BI 7.3 will have increased load performance by ability to re-package small data packages within a request to bigger packages. Can use DTPs to load from Multiproviders. Automatic mapping of Data Source to Info Objects. Ability to change data type & length from source. New abilities for deleting PSA/change Log



2B: Data Store Object and Info-cube



Activation is changed from single lookups to package fetch of active table (20-40% faster activation). Support for database partitioning by time characteristics & ability to remodeling for Data Store objects without reloads. Consistency checks for write optimized DSOs can be turned off (faster loads).



Semantic partitioning Objects (SPO) wizards allow for automated partitioning of large objects & thereby increased performance. No need for hints on multi providers.

New Hybrid provider allows link between historical data in Info Cubes & latest updates in DSOs (faster data access). Improved NLS capabilities

BW 7.3, the technical coding has been optimized to improve the loading performance. Load times into DSO objects has been reduced by up to 40% when compared to BI7, plus the loading of Master Data has been accelerated. Improvements in load performance translate into benefits for the business. Data can be made available earlier in the day, reloads are quicker (so cost less) and larger volumes of data can be loaded each and every day.

2C: Automatic semantic partitioning

Semantic Partitioning is used for similar data across several Info Cubes or DSOs, split according to region or year. This allows increased performance and stability, though comes with at a cost of increased development and maintenance.



With BW 7.3, the BW system can automatically “Semantically Partition” Info Providers. Using a wizard interface, multiple partitions that store your data, handles the mappings between partitions and schedules the loading processes. The need for the manual creation of multiple transformations, DTPs has been removed.

A typical scenario would be to Semantically Partition an Info Cube that is used for Global Sales reporting. By keeping to data for each region or year separate, performance is improved, and maintenance and daily loading is easier.

2D: Graphical Data Flow modeler

A graphical way of building a data model has been introduced in BW 7.3. The Graphical Dataflow Modeler is a new function within the Administrator Workbench (transaction RSA1).

This provides:

• Top-down modeling of a new dataflow

• Organization for existing data flows

• Creation of template data flows using best practice

• SAP delivered pre-defined data flows

Using a drag-and-drop interface, a developer can build a dataflow by selecting either existing objects, or by directly creating new objects from within the modeler. The entire dataflow can be modeled in this way, from the Data Source to the MultiProvider, and even as an OpenHub destination.

2E: Analytical indexes

An Analytical Index (AI) is a transient dataset that is stored within the BWA. BEx or Business Objects can be used to report on them directly. They are defined and loaded from within the Analysis Process Designer (APD), and can contain the results of a JOIN between a CSV file and query.

2F: Hybrid providers

A Hybrid Provider takes a DSO, removing substantial development and maintenance effort. Just load your data into a DSO, create a Hybrid Provider and start reporting. You can even build your Hybrid Provider on a Real time Data Acquisition Data Source (RDA), which could potentially provide near real-time reporting from a BWA. To make data held within a DSO available for reporting, in BI7 there are a number of steps you need to do: Create the DSO, Info Cube, Transformation/DTP, MultiProvider, store in a BWA and connect them all up, and then schedule and monitor load jobs.

A typical usage scenario could be to extract your Sales Orders from ECC and make available for reporting. Using a Hybrid Provider, as soon as the data is loaded into a DSO, they then become available for reporting with all the benefits of an Info Cube and BWA



2G: Hierarchies in Open Hub Service Interface

One new feature in SAP BW 7.3 is the improvement of the Open Hub Service interface to allow extraction of hierarchies. The Open Hub Destination mechanism works the same way as with Info Object attributes or texts

A hierarchy was created manually with SAP BW 7.3. It should reflect a kind of org chart where different managers as hierarchy nodes and finally some employees on the leaf level.

When creating an Open Hub Destination BW 7.3 offers the new Sub object Type "Info Object Hierarchies". This is new feature in BI 7.3. So far you could only choose between Attributes or Texts.

The creation of the corresponding "Transformation" and "Data Transfer Process" works the same way as with the other object types.

To create a process chain this triggers the DTP to copy the data to the Open Hub Destination

2H: Security

Ability to do mass changes for authorizations for users, hierarchies & nodes (faster development & maintenance)

2I: Admin Cockpit

New Admin cockpit monitor for DB usage & issues. New wizard based configuration for installs. New real-time data acquisitioning (RDA) monitor. Central performance monitoring of multiple BW systems possible in Solution Manager. Queries are displayed with OLAP cache & BWA status & usage. Improved search feature in RSA1. Ability to delete historical data from internal statistical cubes. New available version management for metadata objects.

2J: Query Designer and Web Application Designer

Queries can be used as an Info provider. Report Designer is discontinued. BEx query designer remains the core query tool. BEx tools are unchanged, but improved features are available in BOBJ Analysis and Web Intelligence.

Web Application Designer remains, but BI Services Connectors (BICS) is a better solution for Xcelsius, WebI and Analysis tools.

BWA 7.2 enabled. Can index DSO, supports exception aggregation, new calculation engine for restrictive and calculated key figures (RKF/CKF). Info Cubes can now be kept in BWA only (i.e. keep only DSOs in BW). Analytical Indexes (AI) can be made with summarized or transformed data. New APD support for BWA objects (can be used for stats & data mining purposes). Can join multiple indexed objects.



3. Reference Documents for BI 7.3 Design Consideration and New Functionality

3i. Design Consideration for BW 7.30 Info Providers
3ii. Optimized DSO Data Activation

3iii. SAP Net Weaver BW 7.3 Road Map and Overview

3iv. what’s new with SAP Net Weaver BW 7.3

3v. Semantically Partitioned Object in BW 7.30

WHY SAP BI/BW ?

Purpose

The reporting, analysis, and interpretation of business data is of central importance to a company in guaranteeing its competitive edge, optimizing processes, and enabling it to react quickly and in line with the market. With Business Intelligence (BI), SAP Net Weaver provides data warehousing functionality, a business intelligence platform, and a suite of business intelligence tools with which an enterprise can attain these goals. Relevant business information from productive SAP applications and all external data sources can be integrated, transformed, and consolidated in BI with the tool set provided. BI provides flexible reporting, analysis, and planning tools to support you in evaluating and interpreting data, as well as facilitating its distribution. Businesses are able to make well-founded decisions and determine target-orientated activities on the basis of this analysis.