Friday 30 October 2009

Exclude query to use BW Accelerator (BWA)

What's in a name :
High Performance Analytics(HPA) became Business Intelligence Accelerator(BIA) which became Business Warehouse Accelerator(BWA) which will, eventually, become Open Database Analytics(ODA).

Anyway: After you've enabled a cube to be indexed/loaded to the BWA, all queries belonging to that cube will be executed via the BWA. You're able to exclude a particular query, belonging to a "BWA-cube", to be ran via the BWA.
Find the query which shouldn't use the BWA via SE16 and table RSRREPDIR.
Within table RSRREPDIR, set field NOHPA (=Do Not Use HPA Index) to 'X' and save the change. From now onwards, the particular query will not use the BWA eventhough the cube it "runs" on is BWA enabled.

Friday 16 October 2009

Debug startroutine in BI 7.0

Within BI 3.x it was quite easy to debug your startroutine. Pressing the sequence F7 -> F6 -> F5 brought you directly to the startroutine (code). Within BI 7.0 the "F7->F6->F5" trick doesn't work anymore.
To debug a startroutine in BI 7.0, the following steps need to be executed:

1) Go to Display Generated Program within the transformation

2) Jump all the way down to find some startroutine coding you recognize :-) and place a break-point.

3) Find the load you want to debug within the DTP monitor

4) Press the debugging button

5) Execute the debugging request and the execution will stop at the break-point which has been created/set at step 2.


Big thanks go out to Freek Geldof who provided the necessary information!

Monday 5 October 2009

ADAPT: Application Design for Analytical Processing Technologie

I had the pleasure to attend a training at SAP Netherlands in which the ADAPT modeling methodology was explained. As I've become very enthusiastic about this modeling "tool", I'd recommend you all to read the following white paper, written by the Symmetry Corporation.

Monday 28 September 2009

Hierarchy on navigational attribute decreased query performance

A multiprovider, based upon 5 different infoproviders and 2 infoobjects, was causing a performance drain whenever a query was executed.
Every query, based upon this multiprovider, contained a hierarchy on a navigational attribute of an infoobject.
Eg. Infoobject A --> navigational attribute B --> Hierarchy C.
To make sure that the hierarchy was displayed correctly, also infoobject B was added to the multiprovider, eventhough this had no added value (as no "fact" data was to be displayed which "belongs" to infoobject B)
Infoobject B (IOB in the picture below) is a navigational attribute of 0MATERIAL

Infoobject B (IOB) has been added to the multiprovider and was identified (assigned) via the checkbox.
After the checkbox was "un-ticked" (as seen in the picture above; red arrow) and thus infoobject B was no longer part of the identification, the query performance increased.
Conclusion: Know what you're doing when inserting infoobjects in a multiprovider as mis-usage can drastically decrease performance!

Wednesday 23 September 2009

Making an added function module (to a function group) visible in SE80

After creating an (additional) function module within a function group, it wasn't visible within SE80 after it was transported to another BW system.
When displaying the function group, via transaction SE80 --> Function Group, the newly created function module WAS NOT visible, even though the import/transport ended without errors.
The trick, to make all function modules visible within the function group, is to update the navigation index as shown in the screen-grab below:

Friday 18 September 2009

Create or maintain currency translation types

A currency translation type is a combination of different parameters that establish how the exchange rate for the translation is determined. Currency translation types can be created or maintained via transaction RSCUR
For more info regarding currency translation types, click here

Wednesday 9 September 2009

Find out if R/3 field is already transferred to BW

When executing a fit/gap analysis (are all necessary R/3 fields already extracted and transferred to BW), table RSOLTPSOURCEFIE can be used.
Within the selection screen you can enter, for example, R/3 field MATNR if you want to know in which datasources MATNR is used.

The screenshot below shows the overview of all datasources which use R/3 field MATNR:

To see, per datasource/transfer structure, to which infoobject the R/3 field is mapped, table RSTSFIELD can be viewed.
In the screengrab below you can see that R/3 field MATNR is mapped to infoobject 0MATERIAL within several transferstructures/datasources.

Prevent automatic migration of 3.x queries to version 7.0

Once you've migrated a 3.x query to version 7.0, you can't alter it anymore within a 3.x query designer, as a 7.0 query is not downward compatible.
(Even though you can still execute a migrated 7.0 query with a 3.x BEx Analyzer/Query designer, you aren't able to alter it anymore)
OSS Note 962530 (NW04s: How to restrict access to Query Designer 2004s) explains the necessary steps to take to prevent unwanted migration of queries.

Thursday 27 August 2009

Display joblog (SM37) in source system

After having upgraded a customers BI system to version 7.0 last weekend (and worked lots of hours), the last step was to extract the missing delta data from the R/3 source system. As a pre-caution all collective runs were stopped in both clients, prior to the upgrade, so they needed to be restarted (by CONTROL-M).
When I logged on to client A of the R/3 system and watched the joblog of the collective run, I found out that data was extracted from the Qrfc and placed within the delta queue. Unfortunately the delta queue (RSA7) showed no delta data, so I was really puzzled.
Luckely a not so tired colleague pointed out that SM37 is CLIENT INDEPENDENT and thus I was looking at the joblog of the collective run which has ran in client B (Even though I was logged on to client A)!!


To make sure that you're looking at the right joblog, it's advisable to always display the client within the joblog overview.
Settings --> Layout --> Change layout (drag CLIENT from right to left)

Tuesday 18 August 2009

Shortdump when accessing a (role) menu

When logging on to a BW system via the SAPGui, you can access your user (role) menu. Within this user menu/SAP easy access menu, a hierarchy of roles/menu entries will be displayed upon wich can be clicked. Clicking such an entry generated a shortdump (MESSAGE_TYPE_X) which was not self explanatory. (Program CL_GUI_CFW====================CP dumps)
Searching OSS led to note 362883 which advised to enter 3 values in table SSM_COL.

The values displayed in the picture above are max. values. Exceeding these max. values has no purpose as the default value will be taken (in case of exceeding).

Tuesday 4 August 2009

Inconsistent Multiprovider after upgrade to version 7.0

After having upgraded to SAP BI version 7.0, several multiproviders became inconsistent. (Starting a query, based upon these multiproviders, ended with a BRAIN 152 error)
Inserting a "NO_A135_A152" entry to table RSADMIN solves the problem. You can use program SAP_RSADMIN_MAINTAIN to add an entry to the RSADMIN table
(For more info please check OSS note 1086744)

Wednesday 29 July 2009

TEMSE errors within system log (SM21)

When browsing through the system log (as many background jobs failed with no specific reason), I found several "TEMSE" related log entries.

Executing transaction SP12 starts the TEMSE cockpit in which you can execute a consistency check. (Temse Data Storage -> Consistency check).

After several minutes the screen above, regarding the results of the consistency check, were shown. By deleting the complete TEMSE database (button DELETE ALL), no more TEMSE system log entries were generated and the background jobs (which failed in the beginning) ended successfully again.

Which authorization objects are checked while executing a transaction

If you wonder which authorization objects are being checked while executing a transaction, transaction code SU24 is very usefull.
Start transaction SU24 and enter the transaction code you want to check (eg. RSA1)

A list will be displayed containing all authorization objects which can be checked. Column Check Ind. triggers the actual checking. If this column contains a "C/Check" entry, the authorization object will be checked, otherwise the authorization check will be ignored.

Column Proposal can be clicked whenever it contains an YS entry. The following data will be displayed when clicking the YS entry:

Saturday 25 July 2009

EEDO516X error while upgrading from BI 3.5 to BI 7.0

While upgrading a BI 3.5 system, which includes QlikView, from version 3.5 to version 7.0, the following error occurred in the ACT_700 phase:

1EEDO516X"Index" "/QTQVC/TABLECONT" "Z01" could not be activated
1EEDO516 "Index" "/QTQVC/USERCONT" "Z01" could not be activated

For me it was very strange that a SAP upgrade could not continue because a non SAP component (read: An index on a QlikView table) could not be activated.
(You can drop and recreated indexes "easily" via SE14, so what's the big deal??)

Luckely SAP agreed as contacting them via a customer message taught us we could IGNORE the error as displayed above and continue our upgrade....

Thursday 23 July 2009

How to check if JAVAstack has been installed and configured

After the JAVAstack has been installed, it also needs to be configured. When you do not have sufficient authorization the check if the configuration has been executed, table RSPOR_T_PORTAL may come in handy.
When no entries exist within this table, JAVAstack has not been configured.

Wednesday 22 July 2009

Transport a released local ($tmp) transport

When you've released a local ($tmp) transport and still want to transport it across the system landscape, a transport of copies needs to be created.
Create, via SE01, a transport of copies as shown in the screengrab below

Select the transport of copies number and, via the menu, go to Request/Task --> Object List --> Include Objects (CTRL + F11)

A popup will appear in which you enter/select the local ($tmp) transport which has already been released (as shown in the screengrab below)

All objects contained in the released transport will be embedded in the transport of copies-transport which can be transported across the system landscape.

Set User ID for Source System Communication

RSCUSTA

Tuesday 21 July 2009

Function module to find out if the Attribute Change Run is active

You can find out if the ATTRIBUTE CHANGE RUN is active by calling function module RSDDS_CHANGERUN_MONITOR
If import parameter e_ts_state_cr contains an entry, the ATTRIBUTE CHANGE RUN is active.

Monday 20 July 2009

Find out in which MASTERchain a LOCAL (process)chain is embedded

If you have the technical name of a process chain (and you want to execute it via transaction RSPC1), you might need to know if you're going to execute the MASTER process chain or (just) a LOCAL process chain.
You can find out the "link" between a local process chain and it's MASTER process chain via the content of table RSPCCHAIN.
(SE16, table RSPCCHAIN, OBJVERS=A, VARIANTE=local process chain name)

The result of the above SE16 selection will show the following result:

The local process chain name can be found in the column VARIANTE as where the MASTER process chain name is to be found within column CHAIN_ID

Friday 17 July 2009

Usefull (std. SAP) ABAP programs within a SAP BI environment

Listed below several "may come in handy" ABAP Programs (within a SAP BI environment)

RSCDS_NULLELIM: Delete fact table rows where all Key Figure values are zero. See Note 619826.
RSDG_CUBE_ACTIVATE: Activation of InfoCubes
RSDG_CUBE_COPY: Make InfoCube Copies
RSDG_CUBE_DELETE: Delete InfoCubes
RSDG_DODS_REPAIR: Activation of all ODS Objects with Navigation Attributes
RSDG_ODSO_ACTIVATE: Activation of all ODS Objects
RSDG_IOBJ_ACTIVATE: Activation of all InfoObjects
RSDG_IOBJ_DELETE: Deletion of InfoObjects
RSDG_IOBJ_REORG: Repair InfoObjects
RSDG_IOBJ_REORG_TEXTS: Reorganization of Texts for InfoObjects
RSDG_MPRO_ACTIVATE: Activating Multiproviders
RSDG_MPRO_COPY: Make Multiprovider Copies
RSDG_MPRO_DELETE: Deleting Multiproviders
RS_COMSTRU_ACTIVATE_ALL: Activate all inactive Communication Structures
RS_TRANSTRU_ACTIVATE_ALL: Activate Transfer Structure
RS_PERS_ACTIVATE: Activating Personalization in Bex(Inactive are highlighted)
RSAU_UPDR_REACTIVATE_ALL: Activate Update Rules
RRHI_HIERARCHY_ACTIVATE: Activate Hierarchies
RSSM_SET_REPAIR_FULL_FLAG: Convert Full Requests to Repair Full Requests
SAP_AGGREGATES_ACTIVATE_FILL: Activating and Filling the Aggregates of an InfoCube
SAP_AGGREGATES_DEACTIVATE: Deactivating the Aggregates of an InfoCube
SAP_INFOCUBE_DESIGNS: Print a List of Cubes in The System and Their Layouts
SAP_ANALYZE_ALL_INFOCUBES: Create DB Statstics for all InfoCubes
SAP_CREATE_E_FACTTABLES: Create Missing E-Fact Tables for InfoCubes and Aggregates
SAP_DROP_EMPTY_FPARTITIONS: Locate/Remove Unused or Empty partitions of F-Fact Table
SAP_DROP_TMPTABLES: Remove Temperory Database Objects
SAP_RSADMIN_MAINTAIN: Add, change, delete RSADMIN table entries
SAP_CONVERT_NORMAL_TRANS: Convert BasisCube to Transactional Cube and the other way around.
CUBE_SAMPLE_CREATE: A fast way to put some "sample" records in a InfoCube. No need to use flatfiles, just enter the value in a ALV-Grid or let the Cube be filled with random value.

Thursday 16 July 2009

Find out which queries are created for a Infocube/Multiprovider

It's 'easy' to find out which queries have been created "on top of" an infocube or multiprovider.
--> Execute transaction RSA1 and press the "Metadata Repository" button (left bottom of the screen)
--> Within the overview of all repository objects, click on multiprovider or infocube. See screengrab below

--> A list of all multiproviders/infocubes will be displayed and from within this list, select the desired infocube or multiprovider.
-->A new screen will be displayed, containing all information regarding the selected infocube.
--> If you scroll all the way down, you'll find an overview of all queries created for this infocube or multiprovider.See screengrab below

Wednesday 15 July 2009

Boosting performance of START routine

There are several 'places' where custom made ABAP code can be added within a SAP BI system. One of those places is the START ROUTINE (of an update rule). Within such a start routine, the (incomming) data package can be altered/extended.
This alteration is usually done via 'looping' over the datapackage:

(AS IS)
loop at datapackage (into wa_datapackage).
   datapackage-field = 'new value'.
   modify datapackage.
endloop

The correct way of altering a data package, from a performance point of view, should be by using field symbols.

(TO BE)
field-symbols: < fs_dp > type data_package_structure.

loop at datapackage assigning < fs_dp >.
   < fs_dp >-field = 'new value'.
endloop

Within the TO BE situation, the modify datapackage statement is no longer required!

Tuesday 14 July 2009

Check version of BW frontend applications

If you need to find out which version of the SAP Gui or SAP BEx analyzer is being used (so you get an idea of how many PC's needed to be upgraded/patched),
the content of table RSFEC (BW frontend check) can be very helpfull.

In the example above you can see that SAP Gui version 3.0(20), version 3.5(00) and version 3.5(71) are being used.

Monday 13 July 2009

Exception handling when calling a FUNCTION MODULE

Sometimes coding (in transfer rules, transfer routines (on infoobjects) or update rules) can cause problems due to invalid exception handling of function module calls. When calling a function module (eg. READ_TEXT) always enable the EXCEPTIONS part of the function call, otherwise a shortdump will occur.
(When, in the coding of the function module, an exception is raised)

In the coding of function module 'READ_TEXT', an exception is raised, for example, when a text can not be found (In this case exception NOT_FOUND will be raised)

This exception (NOT_FOUND) which is 'raised' by the function module (READ_TEXT), will be passed back to the 'caller'.
('caller': the abap which 'calls' the function module. Eg. a transfer rule or an update rule)
As the caller cannot handle the exception which is 'passed back' by the function module call, a shortdump occurs!

Conclusion:
Always enable the EXCEPTIONS PART when calling a FUNCTION MODULE !

Friday 10 July 2009

Display cube dimension percentage used

To display, per dimension, the percentage used with regard to the number of entries in the fact table(s), function module RSDEW_INFOCUBE_DESIGNS can be used.
Enter the name of the cube for input parameter I_INFOCUBE and execute the function module.
Export parameter E_T_TABLSIZE will provide you the desired result as shown in the attached picture.

Thursday 9 July 2009

(Re)set a FULL LOAD to FULL REPAIR LOAD mode

When having executed a full load to a delta enabled target, the next delta request cannot be activated anymore. (The full load is hampering this)
Solving this issue by deleting this full load can be a solution, but it's easier to reset the full load to a full repair load.
The latter can be accomplished by executing report RSSM_SET_REPAIR_FULL_FLAG.
(Tcode: SA38 -> RSSM_SET_REPAIR_FULL_FLAG)

Wednesday 8 July 2009

How To: Delete an Update Rule to an already deleted target

When you've deleted an ODS/Infocube/Infoobject (aka target), without prior having deleted the update rule which was linked to that particular target, the update rule still 'wanders around' your SAP BI system. (Eventhough you can not see it via transaction RSA1)
You can deleted this (orphan) update rule (call it some kind of "garbage collection") via function module 'RSAU_UPDR_DELETE'. (tcode SE37 --> 'RSAU_UPDR_DELETE' )