Raymond Busher gave a very comprehensive presentation with a lot of tips where to look and what to do to improve the performance of your ETL. In his presentation he discussed monitoring, data models, ABAP techniques, Shared object memory (SOM) and finally process chains.

It was a very comprehensive session.  But let me start with the points he wanted to take us home :

• Ongoing
• raised and benefit your development team as a whole
• Ensure cubes are compressed regularly
• Ensure indices are active and statistics are collected regularly
• Key to improving overall performance is to minimize database

1. access. Using Shared Object Memory can help you to achieve
2. this.

• Ensure

of your available BATCH processes

• Performance analysis must be ongoing
• Develop standards and guidelines for your development team and

monitor your system by checking metadata guidelines are being

adhered to

I highlighted some words as it is evident that finetuning is not a one off project but a continuing process. In the take away it becomes evident that fine-tuning means constant looking at the BW system and be on guard.  You can have new developments, new people in your team, but you need to rely on BW as a solid system.

In the session we started with looking at monitoring options. First transactions that can be used for SQL monitoring (DB02, ST04, ST05, SE30) and process chain monitoring via transaction ST13.

Then we looked at all the components of extraction. In the extraction time we monitor the DB time to select data and user exits.

Look  at delta vs full updates, start and endroutines.  Check your DTP’s. Semantic grouping for example does deliver a large overhead.

Data Model

Look at the cubes to model them in a performance oriented way. The layout in the multiprovider can be user oriented.  This means using all the dimensions to get the dimensions as small as possible. With DSO’s if they are not used for reporting, turn off SID generation.  If you summarize data to new keyfigures use a 7.x infosource instead of a staging DSO.

There are a lot more tips but the main one

Define clear and accessible guidelines for your development team

Use the Multiprovider hint table (RRKMULTIPROVHINT). Until 7.3 if you do not, the multiprovider will fire a query to all the underlying cubes while perhaps only one cube is relevant.  From 7.3 it is covered with Semantic  Partitioned Objects.

You can check your designs with SM50 for processes accessing the NRIV table, ST13 drilling down to DTP and use the ABAP program SAP_INFOCUBE _DESIGNS. Raymond told us that he and his team use a slightly different version where you can select an infocube instead of the standard where all the cubes are shown. Mind though that the program looks at statistics. When they are not up-to-date the result will not be reliable.

Settings must be monitored. This is an ongoing process especially after transport, modification or redesign.

ABAP techniques

There were a lot of detailed ABAP tips. The main ones were that it is best to use generated / dynamic calls. If there is bad programming in new projects this will ensure that it does not drag the entire system with it. For example if a query variable if faulty it will only affect itself, not the rest of query variables.

Golden rules :

• Keep result sets small (filter)
• Keep data transfers small (no SELECT *, it is especially bad for HANA)
• Array Fetch (Into table)
• Keep DB overhead small (proper indexing and optimizer)
• Avoid database access where possible

Set up a programming standard using best practices and make them available to developers. Check before go-live that these standards are met.

Shared Object Memory (SOM)

From release 6.40 you can store data in SAP memory.  This is shared between users sessions so it can freely be used by other processes. This makes it more flexible than session memory.

You can use these SOMs by loading the masterdata into memory after the masterdata reorganization as it won’t change anymore. Every transaction data process that needs to look up this masterdata can skip the database loading part.

You can use this technique in the user exits in OLTP and the START routines you can read from SOM instead from the database.

You can set up the process chain that the SOM load follows the masterdata reorganization step.

Building better process chains

Keep an eye on performance, use ST13 for this. You can look at one process chain for many days. Use prefixes in the naming of process chains as this makes filtering easy in the tool.

Do process chain maintenance in production. This reduces the turnaround and correction time. Also as development and Quality are different systems, settings in production do not make sense there.

Find bottlenecks with ST13 go to DTP’s where all details are available. There is the DB logs check catch the SQL statement and analyze it. If you make changes watch out for side effects (processes occupied, memory consumption etc.)

Always include the aggregate rollup step. Use the switch “end process successfully if no aggregate exists”. If this is the case in all chains then maintenance of aggregates becomes very easy as you know you don’t have to take additional steps to maintain the aggregate.

Clean up PSA’s, Clean up changelogs. Again well named infoareas make easy selection for changelog deletion.

Finally do a regular check of the SAP market place for performance improvements and notes for your SAP release.

Raymond Busher gave a lot more in depth tips and examples in the session than I described here. Most important is that monitoring and fine-tuning is an ongoing process.

Purpose:While creating InfoCube we may use huge number of infoobjects at the same time we may
NOT use all infoobjects in BEx Report .

Disadvantages of holding unused infoobjects in InfoCube:

• Occupy database space
• No room for new dimension’s for enhancing InfoCube
  with new infoobjects (Ex Merging new company data with existing cube)

How to identify such unused infoobjects in InfoCube

Step 1: To get list infobojects used in Infocube goto table RSDCUBEIOBJ and give Infocube name execute

                For Mulitprovider use table RSDICMULTIIOBJ

                For DSO/ODS used table RSDODSOIOBJ

                For Infoset use table RSQFOBJ

Step 2: Download all content in excel sheet

Step 3: Use following ABAP Code, it will generate one excel sheet in given path

Step 4: Filter value 1KYFNM with in column E and delete all records and after delete duplicate infoobjects.

Step 5: Compare both sheets infoobjects with VLOOKUP function in excel sheet. (InfoObjects
which are not there in excel sheet which is generated through ABAP program those are unused infoobjects)

RSZ_X_COMPONENT_GET:Function module pull all infoobjects used in report (like in selections, filters and restricted key figures)

ABAP
CODE Attached to the BLOG.

Note: You may change ABAP code as per your requirement.

I’m a fortunate guy. For the 3rd time in two weeks I’m looking at a program plan for one of my (big) clients and they are asking me (the guy from the wrong side of the tracks I might add) if it makes any sense. And for the third time in a row, it does. Why? It is almost an exact copy of the previous two program plans I saw the weeks before.

Big companies are struggling with the same challenges. They basically went through the same life cycle: introducing ERP as the core for their operational processes, the introduction of a multitude of different data warehouses (often inherited from an acquisition or two), created stove pipes (architecture was not that popular some years ago) and now they have DWHs which need to overcome the challenges of a failing economy, in short: cost need to go down. Cost is one thing these companies worry about. The other components are often performance related, the time to information being to long, the “business” taking matters in there own hand any buying easy-entry BI tools (you know who they are) and reliability of data being poor. Those are quite some challenges. Honestly, I love them, because I often have a pretty good approach on getting the answers for them: optimizing the DWH, allowing “other” BI tools in a controlled way, while showing the advantages of controlled data, getting data lineage and reconciliation in place, adding master data management and governance, … well you get it, basically all those things that make a difference now but were lacking back then due to lack of control, time, architecture and quite frankly, to much money to worry.  Times have changed.

But then after taking a long sip from my morning coffee, I find in program plan xyz, that one element which makes life truly difficult, the one question to rule them all: the need to ask the “business” what information they really need to increase their bottom line, spend efficiency, controls,… well, fill in whatever you like.

And with that ladies and gentlemen, life becomes complicated for me.

That last element in any BI program plan deals with something fundamental. It all boils down to the one question to rule them all: “how can I ask my business what they want, if they don’t know what the future question will be”.

Consulting just turned into an Olympic sport again.

As you might have guessed by now, I don’t have an answer. I truly don’t. What I do have is my top 10 on approaches and their pros and cons. I put them in a neatly formatted table. I dare you to read them, mock them and criticize them, but do that for one reason and one reason only: lets get an answer to that universal question.

Sorry guys and galls, no silver bullet. Looking forward to the comments!

Thank you for reading and take care,

Ronald

Disclaimer

• Any resemblance to existing customers is purely coincidental ;-)

Steps

1. Goto table RSZCOMPDIR and give BEx report technical
   name at COMPID field
2. Execute and take relevant COMPUID as shown in below screenshot

     3.  Go to SE37 Execute function module RSZ_X_QUERY_GET

     4. Pass COMPUID as shown below

    5. Execute or press F8.

    6. Press on table icon as shown in below image.

7. In E_T_MAP structure we can see ELTUID and MAPNAME here ELTUID is UID of variable and MAPNAME is technical name of variable which we see in BEX designer.

8. Take all ELTUID’s and pass to RSZGLOBV table in VARUNIID field and keep VPROCTP value equal to 3 and execute ( 3 means customer exit variables).

9. we can see list of customer exit variables using in a specific BEx report as like below.

So, ZQ_TEST report contain two customer exitvariables

Purpose:

While transporting developed objects from one system to another system, transport request might fail due to following reason (for example)

• Dependent objects missed or inactive in target system

Error details would be more technical format as like below

Routine set to inactive N6LZSYWU4B5BM8TBEHHQX493

Here we don't know what is N6LZSYWU4B5BM8TBEHHQX493 and which routine it is.

In such a case to see Meta data for one particular TRANSFORMATION use following ABAP program

RSTRAN_DDIS

1 Execute RSTRAN_DDIS
2 Provide Transformation ID and Execute

In next window, it will display the Meta data for given transformation.

Step 1: Execute ABAP program RSAWBN_REQ_TEST_TB

Alternatively you can use

Transaction code RS09

Function module RSAWBN_REQ_SHOW_TB

Step 2: Here provide the transport Request id, which you want to see the details (Transport Request id either it ismodified/released)

Step 3: Press Enter

Step 4: Output will be like this

Step 5: Now you can explore what objects captured under this request id like DTP/Infocube/Transformation etc, if you double click on any object, it will take to you relevant screen

I’ve been working with BW 7.3 for a while. I think it is just around one year I worked for one upgrade project. But it just occurred to me that Attribute tab on IO maintenance screen of Administrator Workbench has (finally!) one very nice and important feature.

This feature is search on IO’s attributes binocular icon – so called Find in Attributes.

Here’s how this screen looks like in BW’s up to 701 (e.g. SP level 10):

Here’s same screen with this nice feature as of BW 730 SP 07:

Popup in particular look like:

I realize that this blog post is not very informative for most of BW guys. They all same as me just simply got used of this feature. However I want to take this as opportunity to thank all developers of BW backed which made this happen. Simply initiatives like SCN’s Idea Place are definitely paid off.

Virtual Analysis Authorization implementation within BW 7.30+ and a zero management approach

Summary

Most flexible Analysis Authorization: The combination of a self-service maintenance of characteristic values and associated key figures to users with an automated and by runtime created "Virtual Analytics Security Object (ASO)" for the Query user allows either to reduce implementation time for authorization projects or especially reduces intensively the creation and maintenance time of user profile assignemts for new and changing security demands. The two aspects include the following:

• An online user interface (UI) for departments key users to maintain their users authorizations rights on characteristic values, key figures and hierarchies;
• an implementation of the BAdI for Virtual Analysis Authorization.

Both aspects will be covered within this BLOG.

Many thanks and appreciation to my current/last customer project - in person the SD/BI Developer Matthias Hommer - who felt confident either with the concept or the development/implementation to this new approach.

Architecture and implementation

Following picture represents the data model which will be explained with possible alternatives:

On the left hand side of the picture the basis to store user authorization values is displayed. On the right hand side you can see the aspects for virtual analysis authorization on query execution runtime.

Self-service maintenance for analysis authorization values, hierarchy values and key figures

Idea:

Following the principles of "data ownership" (by departement), "self-service BI administration szenarios" and "zero administration" the idea is to let a few key users of each department maintain the authorization values for each of their query users directly into the system. In detail they have to maintain following:

• characteristic values assigned to the departemental or organizational view to their users. They have to maintain eg. distribution channel down to customer number values to each user;
• key figures which are allowed to be reported by each user;
• hierarchies and hierarchie nodes which are allowed to be reported by each user.

Possible UI alternatives:

From our perspective there are multiple ways how you can enter and store this data:

• XLS or CSV file as UI which is maintained by the department and uploaded into any table or BI object (characteristic, DSO);
• (Web-)Dynpro as UI to store the data in any DDIC table(s);
• InfoObjects with the BW backend transaction to add/edit master data;
• Transactional InfoCube with planning query - use a key figure value for authorization rights on characteristic value combination: 0 = no right, 1 = data access allowed;
• not a question of UI but "loading" authority values:
  • standard technical content DSO's for authorization object generation;
  • assignment to ERP authorization eg to accounting fields;

To us all of the mentioned possibilities were not appropriate or had some minor / major disadvantages so we decided to use a different approach. Here some arguments we had: by using XLS or CSV you have a break within the media; Dynpro programming seemed to be too complex to us for a hand ful of fields; with InfoObject master data maintenance we did not want to shift authorization to the backend as an other area even for the key users; the concept to use a transactional InfoProvider needs to create (via exit) all possible characteristic value combinations to let the necessary key figure become by default 0 (= not authorized) or via planning query to modify the value combination to 1 (= authorized) by the key user ... also complex.

Implementation:

We have decided to use following - see "I" within the picture above:

• We wanted to use BW objects like InfoObjects or DSO's because of being able to use the already loaded InfoObject master data values. Also we wanted to be able to combine the objects by a MultiProvider in order to read user authorization values during runtime with a standard interface (please see later).
• missing asterics masks master data values like "abc*" or "1*" could we enhance manually within the InfoObject master data maintenance (text description eg: "for analysis authorization")
• We wanted to allow the maintenance of excluding values which is not possible for ASO's and standard analysis authorizaton - excluded values like "all customers beside 4711 and 4712" can be transformed during runtime into including values.
• We created DSO's which represent hierarchies, key figures and the department view to each reporting areas by the authorization relevant InfoObjects.
• The maintenance will be done by generated DDIC-maintenance-views on the active table of the DSO; to each maintenance view we can address a transaction code for authorization easily.
• we followed the template DSO's which are delivered by the technical content for analysis authorizations (0TCT*);
• all DSO's have some fields in common which will be filled automatically by maintenance view exit: BW Sys-ID, last changing user, last changing date;
• all DSO's have some fields in common which need to be maintained manually: User, InfoProvider, validity date and activity;
• The DSO structures for hierarchy includes the fields corresponding the technical content eg. InfoObject (to the hierarchy), hierarchy name, hierarchy version, hierarchy node, etc.;
• The DSO structures for key figures include the fields corresponding the technical content eg. key figure name;
• The DSO structures for reporting areas eg. 'Sales' includes the (authorization relevant flagged) InfoObjects as fields like distribution channel, customer and/or material class.

Virtual Analysis Authorization in combination with exit variables and authorized F4-Values in a class library

Idea:

Comparing

• to the "old" (and still common) idea - transaction RSECADMIN - of generation in fore front of Analysis Authorization Objects to all possible value combinations in addition to key figures and hierarchies and an assignment to user (roles) accordingly
• with the new concept of Virtual Analysis Authorization by (Query-)user the ASO is created and checked during runtime

--> only the authorization values for one single user at one processing time of a Query execution will be processed.

Possible alternatives:

Due to the fact of having a kind of "performance pressure" the alternatives tend to reduce processing time of creating and checking the ASO to each Query execution of a user. Possible is to read the authorization values faster by indexing the DSO's into the BWA (possible with BWA7.20). Also to consider is having instanced the reading of the authority values by the BAdI enhancement implementation already.

In our implementation and system environment we have not many (concurrent) users. So it would be very interesting how virtual ASO's will perform with hundred's or even thousands of Query execution users.

Implementation:

Our implementation is based on a customer class library - please see representing "A" within the picture. Here we combine as well as transformation coding encapsulation like start-, end- or field-routines or Query variable exit coding or other pieces of helping coding within the overall BW environment.

• One helping function is a class method to read all authorization values for one single user - please see the representing number "2" within the picture. With the standard function module RSDRI_INFOPROV_READ_RFC we are able to read via the MultiProvider all necessary authorization values to one user and to the exact list of requested query characteristics (which is provided during runtime by the BAdI interface). Also the coding checks the maintained authorization values for correctness:
  • are the characteristics authorization relevant (with function module RSEC_GET_AUTHREL_INFOOBJECTS);
  • include the key figure field names which are authorization relevant;
  • include hierarchy authorization values (InfoObject of hierarchy, node, node InfoObject, level, etc.);
  • turn maintained exclude filters into include filters;
  • check asterics masks signs like "*" and change into "contains pattern" accordingly.
• When prompting for a variable we want to restrict to those F4 values for which the user is allowed. So when having a Query variable (see number "1" within the picture) we process the variable exit within the correct i_step within a class-method (see number "3" within the picture) which calls the class method just mentioned before (numer "2" within the picture).
• And before all of that will be called the exit for the new BAdI enhancement implementation method - see number "4" within the picture above).
  • For that you can refer to the help-side however this is very spartanic.
  • The reference within the system does help more by far - I have copied it into the Appendix so you can read through it.
  • Also an example method helps to understand and even you can test it easily by un-commenting the delivered class-method ABAP code.
  • The over-ruled method GET_AUTHS needs to be delivered in it's interface tables C_T_HIE and C_T_VAL; in our implementation the interface to the helping method to read the authorization values by user - see number "2" delivers the values within this type of format already.
  • When you have understood the manual maintenance of security analysis objects within transaction RSECADMIN then it is easy to understand how to fill the interface tables and how the BAdI method is working.
• Here you can see our implementation:
  • Transaction SE18:
    
  • Enhancement implementation:
    
  • Enhancement implementation element:
    
  • Overruled class method "GET_AUTHS" implementation:
    

Final thoughts

From my personal point of view this new BAdI interface is exciting - it is just great when thinking of all these authorization projects out there and also how much work it is to maintain permanent shifting authorization rights for Query users out there. The combined approach seems to me a tremendous work load rejection of any BI- or system-administration-security team. Please feel free to comment or enhance this idea and/or implementation or give suggestions to similar ideas!

Appendix

RSEC_VIRTUAL_AUTHS

Description

This BAdI interface enables users to generate virtual analytics security objects (ASOs). These ASOs are generated at runtime and are checked as analysis authorizations.

It is therefore only possible to determine which authorizations the user has, at runtime. You can also enhance existing authorizations. Furthermore, you can use variables in virtual authorizations; these variables are then processed as normal customer exit variables as usual at runtime. For reasons of clarity, however, we do not recommend this since the required runtime operations could be executed during the implementation for the virtual ASOs.
  Virtual authorizations (analytics security objects) can often actually replace variable processing in authorizations and can therefore make the process flow clearer.

Use

Method GET_AUTHS requests for the content as value authorizations and hierarchy authorizations in parameters C_T_HIE and C_T_VAL. These tables contain the ASO or authorization names and the relevant InfoObjects along with an authorization definition in the form of intervals (C_T_VAL) or hierarchy nodes with the authorization definition (C_T_HIE). One row is expected for each interval and each hierarchy node. The various authorization combinations are grouped together as rows for each authorization (TCTAUTH field). (See example.)

The method can be called in two ways; with or without InfoProviders:

If the InfoProvider is specified in parameter I_INFOPROV, only the authorizations for this InfoProvider are required. If the InfoProvider is not specified, InfoObject I_IOBJNM should generally be used if the module is called in the standard environment for a query. Both parameters can only be initial in the case of documents that are protected with authorizations. InfoProvider-independent, that is, cross-InfoProvider authorizations are then required. Since authorization-relevant attributes are generally specified for a particular InfoObject, these attributes are automatically entered in the input parameter I_T_ATR for the sake of simplicity and to accelerate processing. They can then, for example, be authorized explicitly (I CP * in C_T_VAL), or not authorized, if required.

The user name is also provided when a transaction is started with the function Execute as Other User (RSUDO); the user name does not have to match the value for sy-uname. This means the analysis authorization check is made with a user that differs from sy-uname. You should therefore always use parameter i_uname instead of sy-uname.

Example

Two virtual ASOs, 'ONE' and 'TWO', are to be created. In the first ASO, the interval [A, D] is to be authorized for characteristic 0EMPLOYEE, which is authorized for a specific cost center node. In addition, the combination [D, G] is to be authorized for another cost center.

This is to be done as follows:

Field in C_T_VAL:

TCTAUTH ONE TWO

TCTIOBJNM 0EMPLOYEE 0EMPLOYEE

TCTSIGN I I

TCTOPTION BT BT

TCTLOW A D

TCTHIGH D G

Field in C_T_HIE:

TCTAUTH ONE TWO (Name of authorization or ASO)

TCTIOBJNM 0COSTCENTER 0COSTCENTER

HIESID 123 123 (Field can be left empty)

HIEID AXYDGFHE... AXYDGFHE... (Field can be left empty)

TCTHIENM MYHIERARCHY MYHIERARCHY

TCTHIEVERS

TCTHIEDATE 99991231 99991231

TCTNIOBJNM 0HIER_NODE - (Empty means end node or leaf)

TCTNODE 100_all 100123

TCTATYPE 2 1 (Authtype; Authtype = 0 never permitted for leaves (only nodes))

TCTACOMPM 0 0

TCTTLEVEL 2 0

TCTHDATE 00000000 00000000

Notes

To find out what is used for interval and hierarchy authorization definitions, see database tables RSECVAL and RSECHIE.

Function module RSEC_GET_AUTHREL_INFOOBJECTS can be used to identify which InfoObjects are relevant (see the documentation for the module).

RSEC_VIRTUAL_EXEC_AUTH_BADI

Use

You can use this enhancement to check the execution authorization by implementing your own method instead of using authorization object S_RS_COMP (method get_execution_auth).If an implementation is active, the method of this implementation is checked instead of authorization object S_RS_COMP.If either of the two mechanisms permits execution, the query can be executed.See the technical documentation on implementation.The return value of the method is a boolean value with two possible values:True (=X): Execution authorization exists: A standard check is not performed.False (=empty/initial): No execution authorization by BAdI. -> There is also a standard check for S_RS_COMP.Execution authorization for one of the two mechanisms is therefore sufficient to execute the query (OR logic).

This also guarantees that a user with a SAP_ALL profile can always execute the query, even if the BAdI is not active or returns "no authorization".