Well, from SAP BW side not that easy J however everything is possible since there is a class CL_RSDDTREX_SERVICE.

For very simple information about BWA release and its revision you can call GET_BIA_RELEASE method:

For little bit more comprehensive information about SP of BW as itself you can call method COMP_BI_SP_BIA_REV.

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.

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

SAP NetWeaver BW 7.30 provides you with a new metadata and transport object for BW data flows. This functionality enables you to persist the data flow views that you created with the ‘Display Data Flow’ function. Data flows are integrated as trees into the Data Warehousing Workbench. They are created and maintained in the same graphical design environment that you will be familiar with from transformation and process chain maintenance screens, for example.

A data flow provides a constant view of objects in the system. You can integrate existing objects and create new objects. The data flow maintenance screen can be used as a single point of entry for future development of your Data Warehousing objects and you can use the dataflow functionality to organize your projects.

Next I want to give you a short demonstration of some of the user-friendly modeling capabilities of data flow maintenance. I will also show you how  to model a data flow from a source to a target, based on existing objects or by creating new objects. I have modeled the following sales scenario: The USA data is sent from a DataSource via a DataStore object to the USA Sales InfoCube. The same setup applies to the Europe data, but the data comes from three DataSources. The USA InfoCube and Europe InfoCube are combined using a MultiProvider for reporting.  Let’s assume that some of the objects are already available and others have to be created from scratch.

Let's start in the data flow tree of the Data Warehousing Workbench. We create a new data flow using the InfoArea context menu and enter the name and description of the data flow. We save the empty data flow and the object appears in the tree. Then we drag and drop the icon of the object that we want to create from the toolbar to the data flow maintenance area. We start with the DataStore object, the InfoCube for Sales Europe and the MultiProvider.

We have now created three non-persistent objects. This means that the objects only exist locally in this data flow and not in the database. Now we draw two lines from the DataStore object to the InfoCube and one line from the InfoCube to the MultiProvider.

What happened? When you drew the line from the DataStore object to the InfoCube, the system realized that the line was a transformation and created a non-persistent transformation. When you drew the second line, the system created a data transfer process (DTP) because a transformation already existed. Further lines would result in further DTPs. When you drew a line from the InfoCube to the MultiProvider, the system created a simple line. This indicates a contained-in relationship, which is the only valid possibility in this case.

Next we used the undo and redo buttons that allow you to undo and redo your previous actions. The screenshots below show the result of the last four actions being undone. In this case, the three connections as well as dragging and dropping the MultiProvider are reversed.

To redo your actions, simply open the dropdown menu of the redo button and select the required action or press the redo button four times, for example.

Before we move on, we’ll assign a technical name and description to the InfoCube by choosing the change option in the context menu. Note that this option does not physically create the InfoCube in the system. The InfoCube is still a non-persistent object that is saved locally in the data flow.

The Sales Europe DataStore object exists already in the system. We add it to the data flow by choosing a command from the context menu of the non-persistent object:

Choose the DataStore object ‘Sales Europe’ in the value help. The system adds the object to the data flow maintenance screen and changes the background color of the node to blue. This indicates that the node represents an existing persistent BW object.

Next we create the Sales Europe InfoCube by double-clicking on the non-persistent object. The dialog box for creating InfoCubes opens. The DataStore object is selected as the InfoCube template because it is connected to the InfoCube. The description and technical name of the non-persistent object are applied to the InfoCube.

Activate the InfoCube and create the transformations and DTPs by double-clicking on the appropriate connections. The source and target are already specified. You simply need to maintain the details. Activate the objects.

Now we need to integrate Sales USA (already exists in the system) into our data flow. We start by dragging the existing Sales USA InfoCube from the InfoProvider tree and dropping it onto the MultiProvider. The existing InfoCube is added. Since the target is the MultiProvider, the system creates a connection between the InfoCube and the MultiProvider, based on the algorithm explained above.

To integrate the entire data flow of the Sales USA InfoCube, expand the existing data flow of the  InfoCube by choosing the menu entry ‘Use data flow of object’. A dialog box opens. Specify whether you want to use the data flow upwards, downwards or in both directions. The data flow is shown in a separate window and can be added to the data flow maintainance by pressing the confirm button.

The objects of the data flow are now integrated in our data flow. Double click on the MultiProvider to open the MultiProvider maintenance screen. Here the source objects are listed due to the connections and are already selected in the MultiProvider maintenance. Simply complete your MultiProvider and activate it.

Connect three European Sales DataSources to the DataStore object by selecting multiple DataSources in a Data Warehousing Workbench object tree and dropping them onto the DataStore object. The transformations are automatically created (as mentioned above).

The Sales data flow scenario can be completed by drawing links for the DTPs and maintaining the details for transformation and DTPs.

This document gives you a first impression of what is possible with the new data flow maintenance. Note that this is only part of the functionality. Some additional features are listed below. A very important function is described in a separate document: Creating and Using Data Flow Templates.

• If the data flow is collected for the transport, all relevant objects are also collected.
• You can copy data flows with the data flow copy wizard by performing a simple copy or a multiple deep copy.
• You can use the create data flow wizard to generate parts of your data flow.
• Semantically partitioned objects are supported in the data flow maintenance
• You can collect and integrate all DTPs and/or transformations that belong to objects in the data flow by selecting a single option. For example, if you start your data flow with a list of InfoProviders and you want to display all transformations that connect objects to other objects of this object set.
• …and many more features.  Try it out!

Scenario:

• DSO/InfoCube having very large number of entries > 10 million entries
• Database is Oracle
• Customer is adding new fields to the DSO/InfoCube and transporting the changes to the Production system.

Background:

Whenever the changes like
a) Adding new key figures
b) Adding new characteristics and dimensions.

are performed on an object (DSO or InfoCube) which already contains data, the activation process has to adjust the table(s) structure. This adjustments process has to alter the table by adding new column and initialize the column to "NOT NULL" and with initial default value. This should work without any problems given the fact that there are enough database resources to perform this change on the database.

Adding not null fields requires checking of the entire "existing" data for null values which requires large amount of space in the transaction log and in the database and comprise many I/O operation. Depending on I/O throughput of your storage it takes hours.

Reason for initializing the newly added column is that from BW reporting and extraction point the column has to be not null and initialized with default value, as BW can not handle null values. General recommendation in such cases where it is required to remodel the object by adding new fields where there is already much data is to follow the below workaround.

The general workaround involves making a copy of the existing data in the cube using datamart interface or Open Hub services. For example
1. Make copy of the existing Cube (Cube1) to Cube2
2. Add new fields to Cube2
3. Load the data from Cube1 to Cube2 using datamart (use Cube2 for further purposes.

If you have some reporting queries defined on Cube1 then need to do the following

1. Make copy of the existing Cube (Cube1) to Cube2
2. Load data from Cube1 to Cube2 using datamart
3. Delete Data from Cube1
4. Add new fields to Cube1
5. Reload the data from Cube2 to Cube1 using datamart

Alternative Solution:

An alternative to this solution is SAP Note 1287382 which observed to be much faster than the normal process.

1287382 Extending BW objects on Oracle with large tables

We have observed a significant performance improvement using this SAP Note although the implementation of this SAP Note, require good application knowledge.

Here are the steps for your reference:

1. Have a list of the large DSOs (identify the size of active table, change log table) and InfoCubes (identify the size of E, F table).
2. Archive or clear change log tables, compress the InfoCubes, minimize the number of records in the tables.
3. Have a list of objects to be transported, identify the dependency of the objects, create the change requests (try to group large DSOs and InfoCubes into separate change request), identify the sequence of the transport.
4. Check the current SP level and implement SAP Note 1287382 and correction mentioned in the SAP Note 1340922.
5. Run program SAP_RSADMIN_MAINTAIN, maintain the RSADMIN parameters as mentioned in the SAP Note 1287382.
6. Schedule background job to run program SAP_NOT_NULL_REPAIR_ORACLE with suitable parameters (table name, parallelism). More parallel processing could be scheduled depending on your hardware capacity. For InfoCubes, the job has to run separately for all the DIM tables and Fact tables.

Job SAP_NOT_NULL_REPAIR_ORACLE
- Renames the table name (/BIC/AMYODS00) to (/BIC/AMYODS00_OLD)
- New table /BIC/AMYODS00 is created in the system and new fields are added to this new table
- Data from the /BIC/AMYODS00_OLD is copied to the newly created table /BIC/AMYODS00

7. Ensure that Job in SM37 is successfully executed.
8. If any errors occur observed in SM37:

The program fails before copying :- Check whether "/BIC/AMYODS00_OLD" exists. Normally, however, this should not be the case because, if errors occur, "/BIC/AMYODS00_OLD" is renamed "/BIC/AMYODS00". If the table exists, before the program is restarted, the table "/BIC/AMYODS00" must be dropped (!!! MUST NOT CONTAIN DATA) and "/BIC/AMYODS00_OLD" becomes "/BIC/AMYODS00".
1.) Drop the new empty table "/BIC/AMYODS00" (using SE14 or SQL: DROP TABLE "/BIC/AMYODS00")
2.) Rename "/BIC/AMYODS00_OLD" to "/BIC/AMYODS00" (must be done with native SQL )
3.) Update in DD03L at least one column to nullable

The program fails after copying: - If the number of records in "/BIC/AMYODS00_OLD" and "/BIC/AMYODS00" differ.

- ANALYSIS and repetition in accordance with the previous step (The program fails before copying). /BIC/AMYODS00_OLD should have complete data in the table.
The program should not be restarted. Instead, the remaining steps (structuring an index and structuring statistics) are carried out manually after the error analysis.

9. Drop *_OLD tables if the conversion is successful.

If the DSO/InfoProvider is contain more than 10 million entries, the above alternative solution should help to reduce the amount of time significantly.