In my last blog, I showed how a Business Objects Data Services Source System can be created in BW 7.30. Today, I will tell you, how you continue accessing a particular table in the remote MySQL database, which we connected via data services.

We stopped after successful creation of the source system. Now let’s doubleclick it and like for any other source system, we jump to the corresponding data source tree. Disappointingly, there is no data source available yet. So let’s create one and go to the extraction tab!

Though in general this DataSource looks similar to any other, there is a new adapter, called “Load using RFC from Data Services”. We can specify a source object, and there are three different value help buttons beside this field. Let’s type “*sales*” (our table is a sales table ;-) and try the first button (search):

Yes, this is the one we are looking for. Anyhow, what will the second button (overview) bring?

A hierarchical overview over all tables. If we expand the node, we find our table again:

Let me skip the third button for the moment and select our table, then go to the next tab of the DataSource maintenance (Proposal).

This action will do two things: First of all, the list of fields is retrieved from data services. And second, the table definition is imported into the data services repository, which is attached to our source system. Because data services does just a similar thing like BW: A metadata upload from the source into the repository. Now we understand the third button on the previous tab: It lists all sources, which are already imported into the repository. This option is useful, because for big source systems, the retrieval of the already imported tables from the repository can be a lot faster than browsing through all tables of the source. And the list is probably much smaller.

We now can go to the fields tab and finalize the maintenance of the DataSource as usual (e.g. make the PRODID a selectable field), then save and activate. This will generate structures, PSA and program, but not do any action in the data services repository.

The next thing to do is create an Infopackage. For loading from data services, as from any BAPI source system, an Infopackage is mandatory, because the data is actively sent to BW, not pulled, and hence the DTP cannot access it.

Entering selections in the infopackage when loading from data services was not possible with prior releases, because the selection condition is part of the query transform in the data services data flow, not a variable when starting the job. However now, saving the infopackage will generate the data flow in the first place. Hence we have the possibility to generate the where-statement into the query transform, reflecting the selection condition entered in the infopackage.

On the extraction tab, the information of the Extraction tab of the DataSource is repeated as usual. Let me go to the 3rd Party Selections.

None of the fields is input enabled. The repository and the JobServer are copied from the source system attributes which you maintained when you created the source system. Also the Jobname is generated. Each InfoPackage will generate a separate data flow and job named infopackage@bw-system. By this, you have no trouble with transports, because even if you use the same repository for connecting to your productive and your development BW, the generated jobs are named different and thus do not interfere. You can just transport the infopackage. The job and flow will be automatically generated when the infopackage is saved or executed the first time. If the infopackage and DataSource definition do not change (i.e. also the selection conditions stay the same), the job and flow are generated only once. Each time something changes (e.g. with dynamic selection conditions), the job and flow are re-generated before the data load request is created.

One remark to the field “Maximum connections”: This is the degree of parallelism which shall be used to load data to BW, comparable with what you can maintain in transaction SMQS for ABAP source systems. There is also the parameter for the package size available via Menu “Scheduler” – “DataS. Default Data Transfer”. Both parameters are transferred into the generated data flow, i.e. the BW target DataSource.

Now you might have one obvious question. What if you want to have a more complex data flow, e.g. containing data quality transform or joins? The answer is: In this case, you must not enter a data store when creating the source system:

Then the Data Services Adapter is not available in the DataSource and you have (mostly) a standard BAPI source system, where you have to enter the fields of the DataSource yourself as usual:

You then can create your own job in data services (and make sure it indeed loads into your DataSource) and enter the Jobname in the InfoPackage manually (resp. via value help):

The repository and JobServer still are copied from the Source System.

legal disclaimer

Don't miss any of the other Information on BW 7.30 which you can find here

I would like to introduce a new method to identify the URL generated by the system to access the Planning Modeler.

The planning modeler uses SSO (e.g. http://<server>/sap/public/myssocntl?sap-client=000). For that reason, ou cannot access the Planning Modeler URL directly from your default browser.

This procedure is very useful in case of Planning Modeler does not start or, in a urgent case, to access without SSO.

• Go to transaction SE24 and display the class "CL_RSPLS_WDPP"
• Open the method "start_wdapp"

•   Add a breakpoint on the code line:

  l_url = get_url(
      i_dc = i_dc
      i_application = i_app
      i_params = i_params ).

• Go to transaction RSPLAN
• Active the Debugging mode with  /h  on transaction box.
• Click on "Start Modeler" button
• The Debugger will start and will stop on the break-point.
• Press F6 and check the "l_url" content. Its value is the Planning Modeler URL configured for that system.

SAP buzzword glossary released as iPhone App

During one of our technology evaluations we have created a SAP Glossary iPhone application containing several "SAP buzzwords" and corresponding explanations - nothing big but maybe useful for sales guys catching up some buzzwords during lunch and looking these up directly instead of talking about unknown technology...
If you want to provide additional content feel free to contact us, we are willing to extend contents, feedback is taken seriously!

You can download the application within the App Store, it's called "Resource Sales Glossar"

(iTunes Link)

Currently the glossary is base on German language we are planing to provide an English version as as soon as possible...

Cheers, Florian!

When most users launch the SAP NetWeaver Portal, they use a browser to launch it and they get a menu which might look like this:

My aim was to build a portal component that could be used to create a menu system a bit like this:

When the user launches the portal component from Safari, they get a screen similar to this:

The user stores their username and password to access the SAP NetWeaver Portal using the Settings option:

Using the Save option then saves the values and relaunches the URL, appending the user's username and password. The user must have already logged on to the SAP NetWeaver Portal successfully so that they do not have an initial or expired password. Selecting an option leads through the different navigation levels until you get a screen that lets you actually start an iView:

Creating the portal component in NWDS involved creating a new portal project. I called mine Mobile. There is a set of JavaScript code that I need to use, and to make things easy, I put it under the dist/images folder and called it Includeme.js. Here is the project structure:

The main component is called SiteMap. It is of type AbstractPortalComponent, and we need to make it anonymous, so that it can still work even if the user has not yet been authenticated. The source for Includeme.js is here:

<!DOCTYPE html> <html> <head> <title>SAP NetWeaver Portal</title> <meta name="viewport" content="width=default-width; user-scalable=no" /> <meta http-equiv="Content-type" content="text/html; charset=utf-8"/> <link rel="apple-touch-icon-precomposed" href="icon.png" _mce_href="icon.png" /> <link rel="stylesheet" href="http://code.jquery.com/mobile/1.0a2/jquery.mobile-1.0a2.min.css" _mce_href="http://code.jquery.com/mobile/1.0a2/jquery.mobile-1.0a2.min.css"></link> <mce:script _mce_src="http://code.jquery.com/jquery-1.4.4.min.js"></mce:script> <mce:script _mce_src="http://code.jquery.com/mobile/1.0a2/jquery.mobile-1.0a2.min.js"></mce:script> <mce:script type="text/javascript"><!-- var cache = window.applicationCache; function onBodyLoad() { getSettings(); window.location="Mobile.SiteMap?dummy#N0"; // window.location="/irj/servlet/prt/portal/prtroot/Mobile.SiteMap#N0?j_user="+$('#username').val()+"&j_password="+$('#password').val(); } function popAlert(message) { // Populate the message text $('#alert_message').text(message); // Popup the message dialog $.mobile.changePage("#message", "pop"); } function getSettings() { // $('#server').val(localStorage.getItem("server")); // $('#port').val(localStorage.getItem("port")); $('#username').val(localStorage.getItem("username")); $('#password').val(localStorage.getItem("password")); // popAlert('Values returned from local storage'); } // // Save settings to local storage // function setSettings() { if (!connectionParamsExist(false)) { return; } // localStorage.setItem("server", $('#server').val()); // localStorage.setItem("port", $('#port').val()); localStorage.setItem("username", $('#username').val()); localStorage.setItem("password", $('#password').val()); // popAlert('Values saved to local storage'); window.location="/irj/servlet/prt/portal/prtroot/Mobile.SiteMap?j_user="+$('#username').val()+"&j_password="+$('#password').val(); } // // Function returns false if connection parameters are missing // function connectionParamsExist(navigateToSettings) { // var server = $('#server').val(); // var port = $('#port').val(); var username = $('#username').val(); var password = $('#password').val(); // Return false if any parameters not defined if (!username || !password) { popAlert('Connection parameters missing. Please define missing values in Settings.'); if (navigateToSettings) { $.mobile.changePage("#settingsConnection", "pop"); } return false; } return true; } // --></mce:script></head>

The code for the component is here:

package com.sap.anz; import java.io.BufferedReader; import java.io.File; import java.io.FileInputStream; import java.io.InputStreamReader; import java.util.Hashtable; import java.util.Iterator; import java.util.Locale; import java.util.StringTokenizer; import java.util.Vector; import javax.servlet.http.HttpServletResponse; import com.sap.security.api.IUser; import com.sapportals.portal.navigation.INavigationNode; import com.sapportals.portal.navigation.INavigationService; import com.sapportals.portal.navigation.NavigationNodes; import com.sapportals.portal.prt.component.AbstractPortalComponent; import com.sapportals.portal.prt.component.IPortalComponentProfile; import com.sapportals.portal.prt.component.IPortalComponentRequest; import com.sapportals.portal.prt.component.IPortalComponentResponse; import com.sapportals.portal.prt.pom.IEvent; import com.sapportals.portal.prt.runtime.PortalRuntime; public class SiteMap extends AbstractPortalComponent { IUser user; int counter; String heading; // BufferedWriter out = null; java.io.PrintWriter out = null; Vector parents; Vector folders; Vector menus; String myURL; int level; protected void doOnNodeReady( IPortalComponentRequest request, IEvent event) { IPortalComponentProfile profile = request.getComponentContext().getProfile(); HttpServletResponse response = request.getServletResponse(true); counter = 0; level = 1; parents = new Vector(); folders = new Vector(); menus = new Vector(); parents.add(new Integer(0)); folders.add("0,SAP NetWeaver Portal"); try { out = response.getWriter(); response.setContentType("text/html; charset=UTF-8"); com.sapportals.portal.prt.session.IUserContext userContext = request.getUser(); boolean anonymous = userContext.isMemberOfGroup("GRUP.SUPER_GROUPS_DATASOURCE.Anonymous Users",false); // read the contents of Includeme.js from the dist\images folder in the NWDS project String pubPath =request.getPublicResourcePath(); File jsfile = new File(pubPath+"\\images\\Includeme.js"); BufferedReader js = new BufferedReader( new InputStreamReader(new FileInputStream(jsfile))); String line; while ((line = js.readLine())!=null) { write(line); } js.close(); // write("</script></head>"); write("<body onLoad=\"onBodyLoad()\">"); myURL = "http://"+request.getServletRequest().getServerName()+ ":"+request.getServletRequest().getServerPort()+ "/irj/servlet/prt/portal/prtroot/com.sap.portal.pagebuilder.IviewModeProxy?iview_id=pcd%3A"; getNavigation(request,out); for (int i=0; i<folders.size();i++) { String s = folders.elementAt(i).toString(); StringTokenizer st = new StringTokenizer(s,","); int thisint = (new Integer(st.nextToken())).intValue(); // write("N"+thisint+","+st.nextToken()); write("<div data-role=\"page\" data-theme=\"b\" id=\"N"+thisint+"\">"); write("<div data-role=\"header\" data-theme=\"a\""); boolean writeSettings = false; if (i==0) { write("data-nobackbtn=\"true\">"); write("<span class=\"ui-btn-right\"><a href="\"#settingsConnection\"" _mce_href="\"#settingsConnection\"">Settings</a></span>"); writeSettings = true; } else { write("><a href=\"#N0\" data-icon=\"arrow-l\" data-transition=\"flip\">Home</a>"); } write("<h1>"+st.nextToken()+"</h1></div>"); if (anonymous) { write("<div data-role=\"content\" <center>Use the Settings option to setup your username and password</center></div>"); } write("<div data-role=\"content\">"); write("<ul data-role=\"listview\" data-inset=\"true\" data-theme=\"c\" data-dividertheme=\"a\">"); for (int j=0;j<menus.size();j++) { String thisone = menus.elementAt(j).toString(); st = new StringTokenizer(thisone,","); int thisfolder = (new Integer(st.nextToken())).intValue(); int thismenu = (new Integer(st.nextToken())).intValue(); String thisoption = st.nextToken(); if (thisfolder==thisint) { write("<li><a href=\"#N"+thismenu+"\" data-transition=\"flip\">"+thisoption+"</a></li>"); // write("option:"+thisoption+" "+thismenu); } } // if (writeSettings) { // write("<li><a href=\"#settingsConnection\" data-transition=\"flip\">Mobile connection settings</a></li>"); // } write("</ul>"); write("</div>"); write("<div data-role=\"footer\"></div></div>"); } write("<div data-role=\"page\" data-theme=\"b\" id=\"settingsConnection\">"); write("<div data-role=\"header\" data-theme=\"a\">"); write("<a href=\"#N0\" data-icon=\"arrow-l\">Home</a>"); write("<h1>Mobile connection settings</h1>"); write("<span onclick=\"setSettings();\" data-role=\"button\" data-icon=\"check\" class=\"ui-btn-right\">Save</span>"); write("</div>"); write("<div data-role=\"content\">"); write("<form action=\"#\" method=\"get\">"); // write("<label for=\"server\">Server:</label>"); // write("<input type=\"text\" id=\"server\" name=\"server\" value=\"\" placeholder=\"Please specify. eg. local.nsp.system\"/>"); // write("<label for=\"port\">Port:</label>"); // write("<input type=\"text\" id=\"port\" name=\"port\" value=\"\" placeholder=\"Please specify. eg. 50000\">"); write("<label for=\"username\">Username:</label>"); write("<input type=\"text\" id=\"username\" name=\"username\" value=\"Please specify username.\">"); write("<label for=\"password\">Password:</label>"); write("<input type=\"password\" id=\"password\" name=\"password\" value=\"Please specify password.\">"); write("</form>"); write("</div></div>"); write("<div data-role=\"dialog\" data-theme=\"b\" id=\"message\">"); write("<div data-role=\"header\" data-theme=\"a\">"); write("<h1> </h1>"); write("</div>"); write("<div data-role=\"content\">"); write("<p id=\"alert_message\"> </p>"); write("<a href=\"#N0\" data-role=\"button\" data-theme=\"c\"><center>OK</center></a>"); write("</div>"); write("<div data-role=\"footer\" data-theme=\"a\">"); write("</div>"); write("</div>"); write("</body></html>"); out.close(); } catch (Exception e) { } } public void getNavigation(IPortalComponentRequest request, java.io.PrintWriter response) throws Exception { try { NavigationNodes rootNodes = getrootnodes(request); int i = 1; for (Iterator it = rootNodes.iterator(); it.hasNext();) { INavigationNode rootNode = (INavigationNode) it.next(); String title = rootNode.getTitle(Locale.ENGLISH); addFolder(title, response); getRestofNodes(rootNode,response); i = i + 1; } } catch (Exception e) { response.write("<br>Error in getNavigation() - "+e.getLocalizedMessage()); } } public void addName(String text, String URL, java.io.PrintWriter response) { try { counter++; int parent = ((Integer) parents.elementAt(level-2)).intValue(); String miniURL = URL.substring(8); write("<div data-role=\"page\" data-theme=\"b\" id=\"N"+counter+"\">"); write("<div data-role=\"header\" data-theme=\"a\">"); write("<a href=\"#N"+parent+"\" data-icon=\"arrow-l\">Back</a>"); write("<h1>"+text+"</h1></div>"); write("<div data-role=\"content\">"); write("<a target=\"_new\" href="\""+myURL+miniURL+"\"" _mce_href="\""+myURL+miniURL+"\"">Launch the content</a>"); write("</div><div data-role=\"footer\"></div></div>"); //write("N"+counter+","+text+","+text+","+myURL+miniURL // +parent+",3"); menus.add(parent+","+counter+","+text+","); } catch (Exception e) { response.write("<br>Error in addName() - "+text+" - "+e.getLocalizedMessage()); } } public void addFolder(String foldername,java.io.PrintWriter response) { counter++; if (level > parents.size()) { parents.setSize(level); }; parents.set(level-1,new Integer(counter)); int parent = 0; if (level > 1) parent = ((Integer) parents.elementAt(level-2)).intValue(); folders.add(counter+","+foldername); menus.add(parent+","+counter+","+foldername+","); // write("N"+counter+","+foldername+","+parent); } private void write(String s) { if (out!=null) { try { out.write(s); out.write("\n"); } catch (Exception e) {} } } private void getRestofNodes(INavigationNode rootnode, java.io.PrintWriter response) throws Exception { level++; NavigationNodes childrenNodes = rootnode.getChildren(); for (Iterator it = childrenNodes.iterator(); it.hasNext();) { INavigationNode newNode = (INavigationNode) it.next(); String text = "" + newNode.getTitle(Locale.ENGLISH) +""; if (newNode.getChildren().size() > 0) { // response.write("<br>folder"+level+" "+text); addFolder(text, response); getRestofNodes(newNode,response); } else { addName(text,newNode.getName(),response); //response.write("<br>end"+level+" "+text+newNode.getName()); }; } level--; } public Hashtable getEnvironment(IPortalComponentRequest request) throws Exception { Hashtable environment = new Hashtable(); com.sapportals.portal.prt.session.IUserContext userContext = request.getUser(); String user = userContext.getUniqueName(); environment.put("NavigationPrincipal", userContext); environment.put("User", user); return environment; } private NavigationNodes getrootnodes(IPortalComponentRequest request) throws Exception { INavigationService navigationservice = (INavigationService) PortalRuntime .getRuntimeResources() .getService( "com.sap.portal.navigation.service.navigation"); return navigationservice.getInitialNodes(getEnvironment(request)); } public void doContent( IPortalComponentRequest request, IPortalComponentResponse response) { }; }

A copy of the PAR file can be found here

Ok, once again reproducing a problem takes a lot of (waiting-)time for the query to finish.

In the meantime I can also just write a super-short-blog post about a pretty common BW-on-Oracle performance hitch:
Inappropriate database optimizer statistics.
(yes, 'inapropriate' not 'outdated'!)

Usually, that means: if everything runs as it is supposed to, BW will take care of collecting the CBO statistics whenever large changes to the data are performed (e.g. data had been loaded or condensed, etc.).
Of course, the BW admin has to set this up in process chains or as attributes to infoproviders but usually, this works well.

If these BW-measures somehow "forget" to update the statistics when new statistics would be required, there is still the daily "Update Optimizer Statistics" (brconnect -f stats) run that should cover this.

The big exception

Unfortunately there is one setting available that prevents the update of new statistics:
the exception table DBSTATC.

In this table you can setup statistic gathering parameters that deviate from the default build-in behavior of brconnect. (This behavior is not too complex, but really does cover most SAP specifics. See the documentation for details here).

One of the most used exceptions here is to avoid gathering new statistics at all.
This can be useful, e.g. when a table is very volatile and you don't want to have your access plans changed all the time because the statistics sometimes report a small/emtpy and sometimes a large table.
(For such case SAP even provides pre-defined table statistics, check sap note 756335 on that)

But what does this have to do with BW?

Well, before brconnect there had been a different tool called SAPDBA and this tool wasn't able to cope with BW objects.
Therefore BW development decided to build its own statistics update coding and to prevent SAPDBA from messing around with the BW tables. And for that they created entries in DBSTATC with ACTIVE=I automatically.

A comfortable solution

This is of course all long past now, but these entries seem to have survived many system upgrades.
In order to get rid of them, there's a report available: SAP_DBSTATC_CLEANUP.
With it you can display and delete the DBSTATC entries for single infocubes or all of them.
Looking at the sap note that introduces this report (SAP note 129252) you'll find that this had been around for quite a while now :-)

The recommendation is of course to remove all the DBSTATC entries for BW objects, so that the standard statistic gathering can be performed.
SAP note 1013912 - "FAQ: Oracle BW Performance" already contained this recommendation and from today on, it also contains the report name ...

And yes, the solution is to remove the entries and not to set ACTIVE=A or something like that. DBSTATC really is all about defining an exception. If the tables should be handled the standard way, then DBSTATC should contain any entry for them!

Ok, the customer report is through... back to the real work!

Best regards,
Lars

There are several places across BW tools from where clearing of different types of cache can be done. 

BEx Web Application Designer metadata cache: stores metadata information about objects related to web templates. You can also delete the cache in which metadata information belonging to current opened template within the session to BW backend. To delete this cache choose in BW Web Application Designer: Menu Tools -> Setting -> Delete cache -> Delete cache. Documentation for NW2004 can be found here. This is not valid for BW based on version 7.x.

OLAP Cache: so called Query cache is type of cache is used to buffer BW queries data sets to be available for next query run. In this case OLAP processor first checks cache and do not need to read the data out of BW infoproviders. To clear OLAP cache run TA RSRT -> Cache monitor -> Delete or you can go via TA RSRCACHE. Online docu here. If you want to do it from your custom ABAP program you may use function module: RSR_CACHE_CLEAR_ALL_CACHES. 

HTTP server cache: this is related to HTTP server build in SAP AS server. You can clear it via TA SMICM -> Goto -> HTTP server cache -> Invalidate cache -> Global in system. Online docu here.

A lot of time and effort has been invested in SAP BW 7.30 to improve the performance of operations on DataStore Objects (DSOs), such as request activation. The most important improvements are:

1. database partitioning for DSOs
2. mass lookups in DSO activation
3. dynamic flag “unique data records”
4. faster DSO activation on databases with massively parallel processing architecture
5. DSO lookup in transformation

The following sections describe these points in more detail.

(1) Database Partitioning

The E fact tables of InfoCubes can be partitioned, by month or fiscal period, on databases that support range partitioning (for more details, see here). In SAP BW 7.30, this is now also possible for the active tables of standard DSOs. More precisely, if a standard DSO has at least one key field referring to InfoObject 0CALMONTH or InfoObject 0FISCPER, then the active table of this DSO can be partitioned by one of these key fields. The advantage of this partitioning is faster access to data due to partition pruning, provided that the partition criterion is part of the selection criterion (‘where’ clause).

To activate partitioning, go to the menu and choose “Extras → DB Performance → DB Partitioning (in edit mode of a standard DSO).

A popup appears where you can select a key field that refers to the InfoObject 0CALMONTH or InfoObject 0FISCPER (you cannot select anything if there is no field referring to one of these InfoObjects).

Any changes made here will become effective after activating the DSO.

Note that you can only change the partitioning settings if the DSO is empty. This means that you should decide whether you want a DSO to be partitioned before you load any data into it.

(2) Mass Lookups

Requests are activated in a standard DSO by splitting the data in the activation queue into packages. These packages are then activated independently of each other (and usually simultaneously).

For each data record of the activation queue, the system performs a lookup into the active table to find out whether a record already exists in the active table with the same key (i.e. whether the activation will cause an insert or an update in the active table).

In SAP BW 7.30, the lookup is no longer performed record by record, but for all records in a package in one go. This decreases the activation runtime by 10-30%, depending on the database.

(3) “Unique Data Records” Can be Set Dynamically

The last section was about lookups into the active table as part of the activation. This lookup is performed to find out whether the active table already contains records with the same key as a record that you want to activate. However, if the active table does not contain any records with the same key as one of the records that you want to activate (for example, if data for a new year or a new region is activated), then these lookups can be omitted. This reduces the runtime of the activation, particularly if the active table contains a lot of records.

You can set the flag “Unique Data Records” in the settings of a standard DSO to guarantee that no record in the activation queue will ever have the same key as any record in the active table during activation. The system will then omit the lookup into the active table. Since this setting is very restrictive, there will probably not be many occasions where you can risk using this flag.

In SAP BW 7.30, you can set this option for one single activation request only. In other words, you can specify that for the current activation request, none of the keys in the activation queue occur in the active table, without making any statement on other activation requests. The system then will omit the lookup for this activation request only.

To select this option, click “Change...” next to the text “Use DataStore Setting (Unique and Changed Data Records)” in the popup where you choose the requests to be activated.

Another popup appears. Choose “New, unique data records only”.

Confirm the dialog. The system will now run the activation without lookup.

For activation processes scheduled as part of a process chain, you also have the option to force the system to omit the lookup for init requests. Choose “Change...” next to the text “Uniqueness of Data: Use DataStore Settings” on the maintenance screen of your variant for a DSO activation.

A popup appears where you can choose “Init requests always return unique data records”.

Confirm the dialog. The system will now omit the lookup for init requests.

(4) New Activation Method for Databases with Massively Parallel Processing Architecture

The traditional activation of requests in a standard DSO is done by splitting the data of the activation queue into packages that are activated independently of each other (and usually simultaneously). The system ensures that records with identical keys are placed into the same package and stay in their original sequence. It is very important that records with identical keys are activated in the correct sequence to guarantee correct results. Therefore a package is activated sequentially - record by record.

Usually there are not many records with identical keys in an activation run. SAP BW 7.30 offers you a new activation method that can be used on databases with massively parallel processing architecture (these are currently (2010) “IBM DB2 for Linux, UNIX, and Windows” and “Teradata Foundation for SAP NetWeaver BW”).

This new activation method finds all the records in the activation queue that do not have unique keys and activates them in the traditional way. All the other records have unique keys in the activation queue and can be activated regardless of the sequence. These records are activated simultaneously using a few SQL statements. This means that the majority of records are no longer read from the database to the application server, processed and written back to the database. Instead they are processed directly in the database. This results in a considerable improvement of the activation runtime, in databases with a massively-parallel-processing architecture.

This new activation method is used automatically by the system (i.e. no user action is needed), provided that certain conditions are met (list not complete):

• this new activation method is supported for the database used (currently only “IBM DB2 for Linux, UNIX, and Windows” and “Teradata Foundation for SAP NetWeaver BW”, as mentioned above)
• SID generation during activation is not requested
• the option “unique data records” is not set
• the aggregation behaviors of all (load) requests that are activated in one go are identical
• the option “do not condense requests” is not set

The performance gains depend on the data as well as on the hardware. In scenarios with very suitable conditions, performance can improve 2 to 3 times.

(5) DSO Lookup in Transformation

In SAP BW 7.30, you can define a transformation rule to fill a field in the transformation target by reading the value from a DSO (similar to reading from master data). More information on this can be found in Documentation in section “Read from DataStore Object”.

This new method of reading from DSOs in a transformation is intended to replace the routines written by users as transformation rules to read values from DSOs. Besides making it more convenient to read from DSOs, this replacement will result in performance gains in many cases (depending on the implementation of the replaced user routine). This is because data is read from the DSO once per data package instead of record by record.

Disclaimer: https://www.sdn.sap.com/irj/sdn/index?rid=/webcontent/uuid/b0b72642-0fd9-2d10-38a9-c57db30b522e

In the second week working in BW support I noticed that there was a problem reported by customers again and again.
The customers complained about "database statistics update fails in process chains" or "brconnect run returns errors".
When checking the relevant log file entries for the actions there was always something like

ERROR at line 1:
ORA-20000: index "SAPR3"."/BIC/F100069~900"  or partition of such index is in unusable state
ORA-06512: at "SYS.DBMS_STATS", line 15181
ORA-06512: at "SYS.DBMS_STATS", line 15203
ORA-06512: at line 1

The more database savvy customers or DBAs sometimes had a quick resolution at hand: to rebuild the index.

And, indeed, after rebuilding the affected index (as I realized it was nearly always a 900 index) the statistics could be correctly gathered.
Problem solved, or not?

Well, only until the next compression run for the infocube happened.

Infocube compression, wait, like infocube.zip or what?

The term 'compression' is a bit misleading here and 'to condense' seems more appropriate.
To understand this action, we've to take a look into how fact table data is managed in SAP BW.
If you're yet unfamiliar with the BW-star schema (extended star schema) it might be a good idea to check the documentation or the BW related blogs and WIKIs here at SDN.

The fact tables are used to store the actual business data that should be reported on to. In the simplest case, this data is loaded from the source system (e.g. the ERP system) on a regular basis, say daily.
So, we get new data in more or less large chunks or packets.
The BW term for this is 'request'.
A data request is a specific chunk of data that only has one thing in common: it has been loaded into the fact table together. [more on this]

Requests and what they are good for

This request wise processing provides several options that are otherwise quite hard to achieve in datawarehouse systems:

• we can load just the data that had been changed since the last load (delta loading)
• we can check the data after each load for technical and quality issues and decide wether or not it should occur in the reporting
• in case some of the data was not imported correctly, we can easily delete it again, without impairing the reports that use the already available data.

If you think about it, this means, that it's totally possible to load information about the same business objects (let's say: direct sales to high level customers in southern germany) can be loaded several times.´

If you load the sales transaction data every day to later on report it on a weekly base then you'll get the the sum of all the sales aggregated over a week - and use data of 7 requests work of data (1 request per day, 7 days a week).

But as we see, for our reporting requirement (sales on weekly base) it's actually not necessary to keep all the data load pakets (requests).
Once we are sure about that the data is technically and quality wise OK, then we might just as well sum up the data to the weekly level, store this and throw away the 7 requests.
This is what the compression of Infocubes pretty much is about.
In SAP BW this is implemented based on two tables:
the F-facttable to which all data load requests go and the E-facttable, which contains the information for the pre-aggregated (condensed) data. [more on this]

Getting closer...

On reporting time, the SAP BW OLAP engine knows about the fact that our data is stored in two tables.
So for every BW query against an Infocube, we usually see TWO nearly identical SQL statements, that only differ in the facttable that is actually used.

Now we have:

• two tables,
• we've data that needs to be read and aggregated request-wise from the F-facttable,
• we've the aggregation result that has to be saved in the E-facttable
• we've data that afterwards needs to be deleted from the F-facttable - otherwise some numbers would be included twice in our report!

Looking at this database requirements, there's an Oracle feature available, that really is made for it (it really is ;-)): PARTITIONING!

Without partitioning the final deletion of already condensed requests would require to

• scan at least one full index in order to find all rows matching the requests to be deleted,
• remove them
• and update the indexes afterwards.

With partitioning all we've to do is to drop the partitions that contain our request.
That's the reason why the F-facttables are always partitioned based on the request dimension (on DBMSes that support partitioning, of course)

So, we can easily get rid of data, when the table is partitioned the right way.

But what about the indexes?

There's a treat in Oracle available for that as well: local partitioning of indexes.
Simply put this means: for every table partition a corresponding partition is created for the partitioned index.

With this, we don't even have to rebuild the indexes after dropping a table partition.
All we've to do is to also drop the corresponding index partition together with the table partition.
The remaining index will still be completely correct and will still cover all data in the table.

Ok, now we arrived at the start of the problem ;-)

All this sounds quite good.
In fact, it's great!
And (of course) here begins the problem.

This great combination of clever data design, implementation and database feature exploitation only works properly if the indexes really are partitioned exactly as the table is.
So BW has to take care of this since Oracle allows to create indexes with a different partitioning scheme or without partitioning as well.
If this is the case and we drop table partitions, then Oracle would have to read every row of the dropped partition to know every deleted row and take this information to maintain the index data.
Obviously this would render the partition advantage null and void.
So, Oracle simply flags all indexes for which the same drop partition cannot be performed as UNUSABLE.

Such UNUSABLE indexes can be repaired simply be rebuilding them.
The Oracle cost-based optimizer is clever enough to ignore those indexes (also see oracle parameter "skip_unusable_indexes"), so queries will not fail because of this.

Except... except we force Oracle to use the broken index by using a hint.

"Where the heck to we do such stuff?" - is that the question you've got in mind right now?

Well, we do it everytime you run the update statistics job.
And collecting CBO statistics after deleting lots of data from a central fact table in BW is usually done as part of the data loading process (chain).

In Oracle update statistics means calling a PL/SQL stored procedure in the DBMS_STATS package. And in there, Oracle will run SQL statements like this:

select /*+ no_parallel_index(t,"/BIC/F100069~900") dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_expand index(t,"/BIC/F100069~900") */ count(distinct sys_op_lbid(15948,'R',t.rowid)) as nrw ,count(distinct sys_op_lbid(15948,'L',t.rowid)) as nlb ,count(distinct "KEY_IC_0025P") as ndk ,null as clf from "SAPR3"."/BIC/F100069" t where TBL$OR$IDX$PART$NUM("SAPR3"."/BIC/F100069",0,4,0,"ROWID") = :objn

p>No reason to understand the whole statement for now, but the important part here is the INDEX hint.
With this hint Oracle is forced to use the index (for which statistics should be collected).
If our index is UNUSABLE at this point in time, then Oracle has no other choice as to report:

ERROR at line 1:
ORA-20000: index "SAPR3"."/BIC/F100069~900"  or partition of such index is in unusable state
ORA-06512: at "SYS.DBMS_STATS", line 15181
ORA-06512: at "SYS.DBMS_STATS", line 15203
ORA-06512: at line 1

LOOOONG story short.

Mashing all this together:

WHEN

        we've a partitioned table with a non-partitioned index

AND

       we drop a partition of the table, so that the non-partitioned index is flagged UNUSABLE

AND

       when we finally run an update statistics on this table,

THEN

       we end up with our ORA-20000 error message.

Why do I tell you this, when BW does take care of the database objects so well?

Because sometimes it fails to do so.
One reason for such a failure is a bug that has been introduced with a SAP Basis SP.
This bug is already fixed with sapnote 1479683 but there are many customers around who haven't yet installed the note and who are just facing miraculous errors in their info cube compression process chains.

As you can see the connection between the ORA-20000 error message about the unusable index and the real cause is rather a long distance one, although straight forward once you undertand it.

The solution (ahem... TATAAA!)

To finally get rid of this problem, I wrote note #1513510.

In there you'll find that you have to
a) import the fix for the SAP Basis bug (note #1479683)
b) recreate (NOT just rebuild) the indexes

For step b) the easiest and quickest way is to use the same function modules that SAP BW does use for the this task.

Excurse... Aggregates are just little infocubes...

In note #1513510 I included an SQL statement to find all partitioned tables for which there are non-partitioned indexes, so that the DBA or BW-Poweruser can look up the problematic ones without having to wait for process chains to fail.
The statement looks like this:

select 
    /*+ 
    no_parallel_index(t,"/BIC/F100069~900")
    dbms_stats 
    cursor_sharing_exact
    use_weak_name_resl 
    dynamic_sampling(0) 
    no_monitoring 
    no_expand 
    index(t,"/BIC/F100069~900") 
    */ 
 count(distinct sys_op_lbid(15948,'R',t.rowid)) as nrw
,count(distinct sys_op_lbid(15948,'L',t.rowid)) as nlb
,count(distinct "KEY_IC_0025P") as ndk
,null as clf 
from 
    "SAPR3"."/BIC/F100069" t 
where 
    TBL$OR$IDX$PART$NUM("SAPR3"."/BIC/F100069",0,4,0,"ROWID") = :objn

If you run this statement you may come across tables like /BIC/F100234.
But you don't have any Infocube named "100234" - so what are those tables about?
They belong to aggregate infocubes. [more on this here]
These are (small) subsets of data that the OLAP processor can choose to deliver the reporting result much quicker. In this respect aggregates are very much like database indexes.
Since the aggregates really are subsets of the actual large infocube they have an F- and E-facttable as well and the same problem can occur wich them as well.

If you now want to know to which infocube a specific aggregate belongs to, you can easily look it up in the RSAGGRDIR table.
For every aggregate you'll find an entry with in the table that maps the aggregate to the infocube.

Checking this table in SE16 delivers an output similar to this:

Table:          RSDDAGGRDIR

   AGGRUID                   OBJVERS INFOCUBE   AGGRCUBE ...
                                                         ...
   03T89W5IUUEFPARPLPZ29YZF3 A       0BWVC_AGV  100108   ...
   200H0IWIR23WASLOESNT8BKJZ A       0BWVC_AGV  100099   ...
   3VILFVABC6MYNF9R10M0WYVHR A       ICD05      100772   ...
   3VIVFYBTHUCH8HF58HQCRGJXS A       ICD03      100778   ...
   3VIVG4AW8R8BQ0JPRVJWKZJZK A       ICD03      100779   ...
   3VIVG8ZVTWHX3SFLC8ZEQ6RQO A       ICD03      100780   ...
   3VP09ETI53LHVKWQHLL79RK5X A       RSDRICUBE  100032   ...
   40VXFTXRAJ6NNT88CWOEA3LYN A       0BWVC09CP  100071   ...
   40VXFU60ZFEEYGCAFPLSI952N A       0BWVC09CP  100072   ...
   40VXK7M8IUTH0IH052QGOF94F A       0BWVC09CP  100073   ...
   [...]
   

Ok, I hope some of you really made it to the end of this rather lengthly first BW blog post. I'll try to keep them shorter in the future ;-)

regards,

Lars

Watch this video on the future of Business Intelligence at SAP:

Stay connected with SAP BusinessObjects 4.0

www.sap.com/analytics 

www.facebook.com/sapanalytics 

www.twitter.com/businessobjects 

more on the SAP Business Analytics blog: http://blogs.sap.com/analytics