Data Warehouse Management in SAP BW

 Data Warehouse Management 

Purpose

The Data Warehousing Workbench is the central point of entry when managing most Data Warehouse Management processes. It provides tools and functions for metadata and process management.

Data Warehouse Management in BI incorporates various tools and functions:

Process Chains, Scheduler, Monitors for Load and Data Transfer Processes

You use process chains, you can automatically control load processes, the further processing of data in BI, and additional central processes in BI. The scheduler is the tool that defines the load process in BI. You can monitor the requests for load and data transfer processes using the respective monitors.

Administration of InfoProviders

In InfoProvider administration, you can display technical information about the content of the InfoProvider. This provides information on the requests that are already in the InfoProvider and allows you to rebuild InfoProviders.

Information Lifecycle Management

Information Lifecycle Management allows you to classify data according to how current it is and archive it as required. This simplifies the administration of the data warehouse and improves performance.

Authorizations

To ensure that the data warehousing solution represents the structure of your company and fulfills its requirements, you have to define who has access to which data.

An authorization allows a user to perform a certain activity on a certain object in the BI system. There are two different concepts for this depending on the role and tasks of the user: standard authorizations and analysis authorizations.

Documents and Texts for BI Objects

Information on BI objects (metadata, master data, InfoProvider data) can be managed in the form of documents. In Reporting, documents can be created, displayed and found using full-text search. In the Documents functional area of the Data Warehousing Workbench, in addition to the functions for creating, importing and exporting documents and searching within documents, there are also functions for the administration of BI document management.

In the Translation functional area of the Data Warehousing Workbench, you can translate short and long texts for BI objects.

Transport of BI Objects and Delivery of Content Objects

Transport system: BI development projects are not usually implemented in a productive system, but in a system landscape with one or more development and test systems. Using the transport connection, you can collect newly created or changed BI objects in the respective development system and then transport it into the required target system (usually a test or productive system) using the Change and Transport Organizer (CTO).

The concept of transporting and using SAP objects in various versions, which is used in the transport system, is also relevant for the delivery and usage of Business Content.

SAP delivers Business Content in the SAP Delivery Version (D version). This means that modified or active objects are not changed when you install or import the delivered Business Content.

During import of customer transports into the target system, the objects are imported either directly into the active version (A version) or into a modified version (M version). Customer-specific changes are also saved in the modified version (M version). In order to be able to use the Business Content that has been delivered or changed according to customer specifications, a customer has to convert the desired objects to an active version (A version).

The Install Business Content functional area of the Data Warehousing Workbench allows you to select (or “collect”) the objects from the Business Content that is supplied by SAP and activate (or “install”) them. This allows customers to determine which objects from Business Content they want to copy, compare with customer-specific modified objects or those that they do not want to install. The system automatically includes all necessary objects as well as the sequence of their activation.

Content that is delivered to business areas by a BI customer or consulting partner is referred to as Customer and Partner Content. The functionality of customer or partner content complements and enhances the options for using the Business Content delivered by SAP.

BW Statistics and Analysis of BI Objects and Data Consistency

In BI, Technical Content is delivered along with Business Content. On the one hand, this includes objects that allow you to analyze the processes in the BI system and to optimize them for performance (BW statistics, BW data slices, BW features characteristics) On the other hand, technical content also includes special DataStore objects that provide data for BEx personalization, for example, or for generating analysis authorizations.

BW Statistics provide data for reporting from the data warehousing (warehouse management) and OLAP areas from several BW statistic InfoCubes using a BW Statistic MultiProvider Within technical content, appropriate BW statistic queries and diagrams are also supplied for the evaluation of data.

An extensive Analysis and Repair Environment is available for performing consistency checks on the data and metadata stored in a BI system.

PROCESS CHAIN STATUS CHANGE PROCEDURE

How to change process chain (PC) status in SAP 

BW?

There are scenarios where you need to change status of a process chain or a particular step in process chain. It is easy to change the status of a step if it is data load, you can just change the status of request in the monitor and it will in turn changes the status in the process chain.
The problem is other types of processes like master data activation, custom ABAP programs etc. In these cases there is no straight forward way to change the status. You might need to change the status in the cases where we need mark these steps successful so that dependent steps get processed. There are other scenarios where one needs to change the status of a single step in the process chain or status of whole process chain.
Step by step instructions on change process chain status
1.    Right click on the failed step in the process chain monitor and go to displaying messages
2.    Go to ‘Chain’ tab and note down variant, instance and start date
 

3.    Go to SE16 and the table RSPCPROCESSLOG and enter  variant, instance and start dates from step 2 and note down log_id, type, variant, instance.
4.    Go to SE37 transaction and execute the function module RSPC_PROCESS_FINISH and enter the values from step 3 and enter the new status ‘G’ in status field and execute the FM
5.    This sets the status of the process chain (PC)

After you set the status using the FM, go to the monitor screen of the process chain, you will notice the changed status. Now dependent steps in the process chain will start running.

SAP BW REALTIME INTERVIEW QUESTIONS

1) Why we delete the setup tables (LBWG) & fill them (OLI*BW)?

A) Initially we don't delete the setup tables but when we do change in extract structure we go for it. We r changing the extract structure right, that means there are some newly added fields in that which r not before. So to get the required data ( i.e.; the data which is required is taken and to avoid redundancy) we delete n then fill the setup tables.  


To refresh the statistical data. The extraction set up reads the dataset that you want to process such as, customers orders with the tables like VBAK, VBAP) & fills the relevant communication structure with the data. The data is stored in cluster tables from where it is read when the initialization is run. It is important that during initialization phase, no one generates or modifies application data, at least until the tables can be set up. 


2) SIGNIFICANCE of ODS? 
It holds granular data (detailed level). 


3) WHERE THE PSA DATA IS STORED? 
In PSA table. 


3) WHAT IS DATA SIZE? 
The volume of data one data target holds (in no. of records) 


4) Different types of INFOCUBES. 
Basic, Virtual (remote, sap remote and multi) 


Virtual Cube is used for example, if you consider railways reservation all the information has to be updated online. For designing the Virtual cube you have to write the function module that is linking to table, Virtual cube it is like a the structure, when ever the table is updated the virtual cube will fetch the data from table and display report Online... FYI.. you will get the information : https://www.sdn.sap.com/sdn/index.sdn and search for Designing Virtual Cube and you will get a good material designing the Function Module 


5) INFOSET QUERY. 
Can be made of ODS's and Characteristic InfoObjects with masterdata. 


6) IF THERE ARE 2 DATASOURCES HOW MANY TRANSFER STRUCTURES ARE THERE. 
In R/3 or in BW? 2 in R/3 and 2 in BW 


7) ROUTINES? 
Exist in the InfoObject, transfer routines, update routines and start routine 


8) BRIEF SOME STRUCTURES USED IN BEX. 
Rows and Columns, you can create structures. 


9) WHAT ARE THE DIFFERENT VARIABLES USED IN BEX? 
Different Variable's are Texts, Formulas, Hierarchies, Hierarchy nodes & Characteristic values.  


Variable Types are 

Manual entry /default value 
Replacement path 
SAP exit 
Customer exit 
Authorization 


10) HOW MANY LEVELS YOU CAN GO IN REPORTING? 
You can drill down to any level by using Navigational attributes and jump targets. 


11) WHAT ARE INDEXES? 
Indexes are data base indexes, which help in retrieving data fastly. 


12) DIFFERENCE BETWEEN 2.1 AND 3.X VERSIONS. 
Help! Refer documentation 


13) IS IT NESSESARY TO INITIALIZE EACH TIME THE DELTA UPDATE IS USED? 
No. 


14) WHAT IS THE SIGNIFICANCE OF KPI'S? 
KPI's indicate the performance of a company. These are key figures 


15) AFTER THE DATA EXTRACTION WHAT IS THE IMAGE POSITION. 
After image (correct me if I am wrong) 


16) REPORTING AND RESTRICTIONS. 
Help! Refer documentation. 


17) TOOLS USED FOR PERFORMANCE TUNING. 
ST22, Number ranges, delete indexes before load. Etc 


18) PROCESS CHAINS: IF U has USED IT THEN HOW WILL U SCHEDULING DATA DAILY. 
There should be some tool to run the job daily (SM37 jobs) 


19) AUTHORIZATIONS. 
Profile generator 


20) WEB REPORTING. 
What are you expecting?? 


21) CAN CHARECTERSTIC INFOOBJECT CAN BE INFOPROVIDER. 
Of course 


22) PROCEDURES OF REPORTING ON MULTICUBES 
Refer help. What are you expecting? MultiCube works on Union condition 


23) EXPLAIN TRANPSORTATION OF OBJECTS? 
Dev---àQ and Dev-------àP 


24) What types of partitioning are there for BW?

There are two Partitioning Performance aspects for BW (Cube & PSA) 
Query Data Retrieval Performance Improvement: 
Partitioning by (say) DateRange improves data retrieval by making best use of database [data range] execution plans and indexes (of say Oracle database engine). 
B) Transactional Load Partitioning Improvement: 
Partitioning based on expected load volumes and data element sizes. Improves data loading into PSA and Cubes by infopackages (Eg. without timeouts).  

25) How can I compare data in R/3 with data in a BW Cube after the daily delta loads? Are there any standard procedures for checking them or matching the number of records? 

A) You can go to R/3 TCode RSA3 and run the extractor. It will give you the number of records extracted. Then go to BW Monitor to check the number of records in the PSA and check to see if it is the same & also in the monitor header tab. 
A) RSA3 is a simple extractor checker program that allows you to rule out extracts problems in R/3. It is simple to use, but only really tells you if the extractor works. Since records that get updated into Cubes/ODS structures are controlled by Update Rules, you will not be able to determine what is in the Cube compared to what is in the R/3 environment. You will need to compare records on a 1:1 basis against records in R/3 transactions for the functional area in question. I would recommend enlisting the help of the end user community to assist since they presumably know the data. 

To use RSA3, go to it and enter the extractor ex: 2LIS_02_HDR. Click execute and you will see the record count, you can also go to display that data. You are not modifying anything so what you do in RSA3 has no effect on data quality afterwards. However, it will not tell you how many records should be expected in BW for a given load. You have that information in the monitor RSMO during and after data loads. From RSMO for a given load you can determine how many records were passed through the transfer rules from R/3, how many targets were updated, and how many records passed through the Update Rules. It also gives you error messages from the PSA. 



26) Types of Transfer Rules?

A) Field to Field mapping, Constant, Variable & routine. 


27) Types of Update Rules?

A) (Check box), Return table 


28) Transfer Routine?

A) Routines, which we write in, transfer rules. 


29) Update Routine?

A) Routines, which we write in Update rules 


30) What is the difference between writing a routine in transfer rules and writing a routine in update rules? 

A) If you are using the same InfoSource to update data in more than one data target its better u write in transfer rules because u can assign one InfoSource to more than one data target & and what ever logic u write in update rules it is specific to particular one data target. 


31) Routine with Return Table.

A) Update rules generally only have one return value. However, you can create a routine in the tab strip key figure calculation, by choosing checkbox Return table. The corresponding key figure routine then no longer has a return value, but a return table. You can then generate as many key figure values, as you like from one data record. 



32) Start routines?

A) Start routines u can write in both updates rules and transfer rules, suppose you want to restrict (delete) some records based on conditions before getting loaded into data targets, then you can specify this in update rules-start routine. 

Ex: - Delete Data_Package ani ante it will delete a record based on the condition 



33) X & Y Tables?

X-table = A table to link material SIDs with SIDs for time-independent navigation attributes.

Y-table = A table to link material SIDs with SIDS for time-dependent navigation attributes.

There are four types of sid tables 

X time independent navigational attributes sid tables

Y time dependent navigational attributes sid tables 

H hierarchy sid tables

I hierarchy structure sid tables 




34) Filters & Restricted Key figures (real time example)

Restricted KF's u can have for an SD cube: billed quantity, billing value, no: of billing documents as RKF's. 


35) Line-Item Dimension (give me an real time example)

Line-Item Dimension: Invoice no: or Doc no: is a real time example 



36) What does the number in the 'Total' column in Transaction RSA7 mean?  

A) The 'Total' column displays the number of LUWs that were written in the delta queue and that have not yet been confirmed. The number includes the LUWs of the last delta request (for repetition of a delta request) and the LUWs for the next delta request. A LUW only disappears from the RSA7 display when it has been transferred to the BW System and a new delta request has been received from the BW System. 


37) How to know in which table (SAP BW) contains Technical Name / Description and creation data of a particular Reports. Reports that are created using BEx Analyzer.

A) There is no such table in BW if you want to know such details while you are opening a particular query press properties button you will come to know all the details that you wanted. 

You will find your information about technical names and description about queries in the following tables. Directory of all reports (Table RSRREPDIR) and Directory of the reporting component elements (Table RSZELTDIR) for workbooks and the connections to queries check Where- used list for reports in workbooks (Table RSRWORKBOOK) Titles of Excel Workbooks in InfoCatalog (Table RSRWBINDEXT) 


38) What is a LUW in the delta queue?

A) A LUW from the point of view of the delta queue can be an individual document, a group of documents from a collective run or a whole data packet of an application extractor. 


39) Why does the number in the 'Total' column in the overview screen of Transaction RSA7 differ from the number of data records that is displayed when you call the detail view? 

A) The number on the overview screen corresponds to the total of LUWs (see also first question) that were written to the qRFC queue and that have not yet been confirmed. The detail screen displays the records contained in the LUWs. Both, the records belonging to the previous delta request and the records that do not meet the selection conditions of the preceding delta init requests are filtered out. Thus, only the records that are ready for the next delta request are displayed on the detail screen. In the detail screen of Transaction RSA7, a possibly existing customer exit is not taken into account.  


40) Why does Transaction RSA7 still display LUWs on the overview screen after successful delta loading? 

A) Only when a new delta has been requested does the source system learn that the previous delta was successfully loaded to the BW System. Then, the LUWs of the previous delta may be confirmed (and also deleted). In the meantime, the LUWs must be kept for a possible delta request repetition. In particular, the number on the overview screen does not change when the first delta was loaded to the BW System. 


41) Why are selections not taken into account when the delta queue is filled? 

A) Filtering according to selections takes place when the system reads from the delta queue. This is necessary for reasons of performance. 


42) Why is there a DataSource with '0' records in RSA7 if delta exists and has also been loaded successfully? 

It is most likely that this is a DataSource that does not send delta data to the BW System via the delta queue but directly via the extractor (delta for master data using ALE change pointers). Such a DataSource should not be displayed in RSA7. This error is corrected with BW 2.0B Support Package 11. 


43) Do the entries in table ROIDOCPRMS have an impact on the performance of the loading procedure from the delta queue? 

A) The impact is limited. If performance problems are related to the loading process from the delta queue, then refer to the application-specific notes (for example in the CO-PA area, in the logistics cockpit area and so on). 

Caution: As of Plug In 2000.2 patch 3 the entries in table ROIDOCPRMS are as effective for the delta queue as for a full update. Please note, however, that LUWs are not split during data loading for consistency reasons. This means that when very large LUWs are written to the DeltaQueue, the actual package size may differ considerably from the MAXSIZE and MAXLINES parameters. 


44) Why does it take so long to display the data in the delta queue (for example approximately 2 hours)?  

A) With Plug In 2001.1 the display was changed: the user has the option of defining the amount of data to be displayed, to restrict it, to selectively choose the number of a data record, to make a distinction between the 'actual' delta data and the data intended for repetition and so on. 


45) What is the purpose of function 'Delete data and meta data in a queue' in RSA7? What exactly is deleted? 

A) You should act with extreme caution when you use the deletion function in the delta queue. It is comparable to deleting an InitDelta in the BW System and should preferably be executed there. You do not only delete all data of this DataSource for the affected BW System, but also lose the entire information concerning the delta initialization. Then you can only request new deltas after another delta initialization. 

When you delete the data, the LUWs kept in the qRFC queue for the corresponding target system are confirmed. Physical deletion only takes place in the qRFC outbound queue if there are no more references to the LUWs. 

The deletion function is for example intended for a case where the BW System, from which the delta initialization was originally executed, no longer exists or can no longer be accessed. 

SAP BI/BW DESIGNING TIPS

Requirement Gathering

I prefer the below audience to be always present in Business user report requirements.

1. One Functional consultant, this person is helpful for a BW person in understanding business into SAP terms (Mandatory)

2. One BW Sr. Consultant/Solution Architect, he is a required attendee to make aware of users about the BW/BI/BOBJ (Mandatory)

3. One Project manager (optional), to make us understand what is promised to business and what not.

Without the Functional consultant, BW Solution Architect (Sr. Consultant) it is not good enough to proceed in collection of requirements.

For example:

Case 1:

In my experience, I've seen a scenario where in some of the companies they would allow any fresh graduates/low experienced people for requirements which may lead to a disaster situation in designing. So, it has to be avoided always.

In the above case, if a fresh graduate is present as BW report requirement gatherer they may accept for some of those avoidable requirements like invoice number or document number in prompts; very detailed level of reports in to BI/BW/BOBJ; posting date, fiscal year, period in prompts and many more....

Case 2:

There are cases where the requirements made without functional consultants, this is also an avoidable situation, because functional consultants are the person who assist us on the feasibility of the requirements.

Like if there is any field which is not exist in source system as standard and which is business insisting as requirement, here functional consultant can play a role to guide us what is the labour work or logic for extraction of that field in to BW.

Based on the above examples, you might have got an understanding that what are the major concerns on requirement gathering.

After a completion of milestone of report requirement gathering, the actual part of designing involves from BW side.

Designing

Data mapping

This hurdle is not an easy task, we need to get precise data mapping as these are kind of building blocks for our designing. It's like bricks to a building. If we don't have precise data then we may end up in wrong design.

For example: The field name may say Invoice amount but the data mapping for this field end up from postings table then this would lead us in wrong prospect where we cannot end up in appropriate design.

Data modelling

Activity 1 - Defining an approach

In this activity we need to invest much time to come up an appropriate design for the reports for this case I would like to share my knowledge.

     There are 2 types of approaches which I would like to highlight here

a. Top-Down approach

b. Bottom-Up approach

At first I would like to let the pros and cons of each approach and which would be an opt for any case.

a. Top-Down approach (from report level to source table level)

PROS:

             1.  Easy & efficient when we opt if most of the business content satisfies our requirement.

             2. Business content is easy available and pre-built.

CONS:

          1. Field descriptions in the report requirement may lead to wrong perceptions of grouping of reports. For Ex: field                                   description saying as "Invoice amount" but data mapping is from Contract tables for some reports and for other from invoice                tables.

          2. Business content is available but which is not satisfying in our requirements even 50%.

          3. Requires most of business knowledge to derive the grouping.

      4. There may be chances of Duplicate of data across the cubes/data model for ex: Invoice amount or Invoice related info                is used in most of the reports.

          5. Need to create Custom datasources as standard may not satisfy, 100%.

          6. More time taking process as understanding of business required.

          7. No re-usability (as it is specific to group of reports)

b. Bottom-Up approach(from datasource/table level to report level)

PROS:

          1. Use of Standard Datasources.

          2. No redundancy

          3. Mainly used for SAP source system.

          4. Can overcome the problem with difference in field descriptions (between source and reports).

          5. Not much business knowledge required.

          6. Less time taking process.

          7. can easily satisfy reports pulling data from multiple sources.

          8. Re-usability.

CONS:

          1. combination of cubes (creation of multi-cubes) is challenging in our case.

Activity 2 - Identification of standard datasources (if source system is ECC)

After defining your approach then the next task would be to  identify standard content datasources. Based on your report requirement and data mapping you can able to identify any standard datasources with the source tables mapped.

For examples: for invoice extraction (FI-CA), we can use 0FC_INVDOC_00; for postings we can use 0FC_BP_ITEMS; for payments extraction we can use 0FC_PAY, etc.

Activity 3 - Validation of datasources

once the datasources are identified then the next task would be validation of the datasource data with report requirement. If 80 to 90% of our report requirement satisfies with the datasource then we can use that datasource.

Activity 4 - Master data Identification

Master data has to be identified with the like attribute, text and hierarchy based on our requirement. for example: Company code, company code country, company code address, company code telephone number as master data attribute and Company code name as text data.

For Hierarchy, organization structure will be an opt example.

Activity 5 - Designing of cubes/Multicubes

Need to come up with a cube design like dimensions, key figures and multicubes based on our cube design.

Activity 6 - Prototype (Proof Of Concept)

In order to support our design prototyping of the design would be very advantageous which can give us 100% confident on our design and also can support ourselves in each and every scenario of the reporting requirement.

Conclusion:

Based on the above considerations we would be able to provide a perfect BW design or data model