Constant selection in query (Apples and Oranges)

The great thing about an infoset is that it can combine data from different sources via a "JOIN" (inner/outer) mechanism. This may come in very handy if you want to report on two different sources (apples & oranges) without having to show the annoying '#'.
But what to do if you want to show data from the Apples bucket, even though this bucket is empty for the corresponding Oranges bucket? (and vice versa)

Eg.
Apples: 10 pieces in January 2010 for customer A
Oranges: 5 pieces in January 2010 for customer B

With an infoset (JOIN) on Apples & Oranges (where Apples is 'leading') it is impossible to show the Oranges of January for customer B because Apples has no customer B entry.

To be able to show the Oranges data, you have to use a multiprovider which uses the UNION mechanism. The downside of the multiprovider is that it will show a # for January Oranges for Customer A and a # for January Apples for Customer B.

This unwanted # behaviour can be overruled by using constant selection
Step 1: Create restricted keyfigure


Step 2: Tick on constant selection



Without constant selection:
January 2010;Customer A; Apples 10; Oranges #
January 2010;Customer B; Apples #; Oranges 5

With constant selection:
January 2010;Customer A; Apples 10; Oranges 5
January 2010;Customer B; Apples 10; Oranges 5

DB02: table diagnostics and 'native sql' commands

Via DB02 you can find out what 'issues' you might have on database level.

In the screengrab above it is shown that 4 DB tables no longer exist in the database. You can use 'native SQL' commands to physically delete these 4 tables from the database. This native SQL can be executed via a function module (SE37) call of DB_EXECUTE_SQL

SAPconnect: Set BW mail/fax/internet settings

Use transaction SCOT to setup, for example, an SMTP connection on your BW server.

Currency conversion in query

Looking at the properties of a keyfigure,

you sometimes find a currency conversion being executed.
If you're wondering where this conversion is being maintained, take a look at transaction RSCUR

Within transaction RSCUR you can customize/maintain the conversion which should be executed.

Activation of a cube/iobj/mpro/odso in a (closed) production system

Within SAP BI 3.x and also within SAP BI 7.x you have the possibility to activate infocube's, DSO's, etc. in a productive (closed) system, without having to transport them (again) from development.
To achieve this activation, the following ABAP programs can be used:

RSDG_ODSO_ACTIVATE: Activation of all ODS Objects
RSDG_IOBJ_ACTIVATE: Activation of all InfoObjects
RSDG_MPRO_ACTIVATE: Activating Multiproviders
RSDG_CUBE_ACTIVATE: Activation of InfoCubes

ABAP maximum for compiled code (within user exit) reached

Last week we've encountered some strange behavior (read: short dumps) in our BW system. For some strange reason the user exit for variables (function module EXIT_SAPLRRS0_001; include zxrsru01) was generating shortdumps. Analysis revealed that there's a maximum of 32kb of compiled code which the abap runtime environment can handle. (This limitation is not regarding the number of coded lines, but the amount of byte code generated)
This limitation was breached as we've placed all our user-exit code in one big CASE/ENDCASE statement.
When we replaced this big CASE/ENDCASE statement by several smaller CASE/ENDCASE chunks, everything worked fine again.



Thanks to Crispian for the analysis

Easily find the request ID you're looking for

Two easy ways to find Request ID's in a BW 7.0 system:

1) Use transaction code RSRQ and enter the request ID you're looking for


2) When 'monitoring' a request ID, you can press SHIFT-F5 or go to the menu-bar and press Request -> Other Request. Within the pop-up which appears, another request ID (the one you're looking for) can be entered.

Adding a filter value within a DTP

It's been a while (in fact: it's been to long) since I've last posted a blog entry. The reason for this was that I've been working as a team-lead the last couple of months. Therefore I was more 'occupied' with leading a team, then I was with inventing/discovering great BI 7.0 solutions....
But as I'm back in the saddle, I'm proudly presenting my first 2010 blog entry:

Within the DTP-extraction tab, you're able to set a filter to control the data you're going to extract:


The standard coding which will be displayed will be something like this:

read table l_t_range with key fieldname = ' '.
l_idx = sy-tabix.
if l_idx <> 0.
   modify l_t_range index l_idx.
else.
   append l_t_range.
endif.

Please be aware that, if l_idx = 0 and thus no entry is found in table l_t_range, you also have to fill in the fieldname

Eg. Usually, for filling a range, the following code is entered:
   l_t_range-sign = 'I'.
   l_t_range-option = 'EQ'.
   l_t_range-low = 'something'
This coding is sufficient when the read statement (read table l_t_range with key fieldname = 'whatever') finds an entry in table l_t_range...and thus l_idx <> 0.

When no entry is found (and thus l_idx = 0) you also have to add the following line to the code
   l_t_range-fieldname = 'FIELDNAME'.
otherwise filling the range has no added value...as the system does not know for which field the range should be filled....

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.

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!

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.

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!

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:

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

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.

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)

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).

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)

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:

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....

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.

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