Quickly find queries which use specific InfoObjects

When it comes to deleting InfoObjects from SAP BW it is necessary that these are not used anymore by other objects in the system. The standard where-used functionality could supply sufficient insight. However the standard where-used functionality is not always accurate when a person wants to know in which SAP BW queries an InfoObject is used. For this specific case you have the ability to use one of the following two function modules.

Technical name Type
RSZ_I_BASIC_CHA_WHERE_USED Characteristics
RSZ_I_BASIC_KEYFIG_WHERE_USED Key figures

By filling in the right parameters the function module delivers a table of queries using the specified characteristic or key figure.
RSZ_I_BASIC_CHA_WHERE_USED

SAP tables related to transports

In some cases it might be interesting to know which SAP BW objects are attached to a specific transport for e.g. release management. Instead of copy and pasting object values mentioned in the SE10 transaction SAP tables can be used.

All information related to SAP transports is stored in E07* tables. An overview of these tables is shown below. Along with this information and this table overview you have the ability to compose an overview of objects (including their functional description) belonging to specific transport.

Table Description
E070 Change & Transport System: Header of Requests/Tasks
E070A Change & Transport System: Attributes of a Request
E070C CTS: Source/Target Client of Requests/Tasks
E070CREATE Change & Transport System: Creation Date of Request
E070CTV Generated Table for View E070CTV
E070DEP Change & Transport System: Dependencies of Requests
E070L CTS: Index for Assigning Numbers to Requests/Tasks
E070M CTS: Target Package/Layer for Requests
E070TC Help Table for E070 for Client-Specific Imports
E070USE Use of Current Requests by Users
E070V Generated Table for View E070V
E071 Change & Transport System: Object Entries of Requests/Tasks
E071C Change & Transport System: Client-Specific Lock Flag
E071E Lang. Transport: Positive List for Generic Object Selection
E071K Change & Transport System: Key Entries of Requests/Tasks
E071KC Change & Transport System: Key Entries of Requests/Tasks
E071KF Change & Transport System: Nametab Info. on (CHAR)Key Fields
E071KFINI Change & Transport System: Nametab Info. on (CHAR)Key Fields
E071K_30 Change & Transport System: Key Entries of Requests/Tasks
E071K_KEY E071K Key Fields
E071S System-Specific Import Status of Objects
E071V Generated Table for View E071V
E07T Change & Transport System: Short Texts for Requests/Tasks
E07T_OLD E07T Before TRKORR Extension

Thanks to http://sapbasisanswers.blogspot.nl/ for supplying this helpful overview.

The power of navigational attributes

Remember the time when all additional functionality had to be programmed manually in SAP BW? With SAP Business Warehouse 7.3 (and above) many of custom ABAP code could be replaced with standard functionality. One real-life example is the retrieval of a navigational attribute from a navigational attribute.

In the good old days this could be done by using custom ABAP code. With SAP BW 7.3 you can now easily achieve the same result by using navigational attributes. By activating a navigational attribute in the source InfoProvider (like a DataStore Object) you will get the possibility to use the attribute as a source in a transformation.
Using navigational attributes instead of ABAP
With this in mind you now have the capability to use the default “read from master data”-option when modelling a transformation. This makes it possible to read a navigational attribute from a navigational attribute.

The activation of navigational attributes also enables you to use these in a DTP or start routine. This might come in handy when filtering out invalid records.
Using navigational attributes as DTP filter option

RSRQ: a useful transaction for tracing back records

It might happen that when you are unit testing reports or data models you will probably encounter various issues. Tracing back the data is one of the steps you can execute to find the cause of an issue. In order to trace back the data you will have to know from which source it is coming from, at least when a data object is loaded from multiple sources.

Add request ID as characteristic
By selecting the request ID as a characteristic you will be able to see which specific data load is responsible for a record. With the use of the SAP BW transaction RSRQ along with the request ID, you will be able to go straight to the monitor of the data load and determine that source of a record. Hopefully this information will help you in your journey of solving SAP BW data issues!

RSRQ data load monitor for single request

Multi-dimensional clustering in SAP NetWeaver Business Intelligence

Getting the best possible query performance out of a SAP Business Intelligence system is one of the main responsibilities of a BI consultant. Optimizing data models (e.g. remodeling and creating indexes) is one of the activities you could perform in order to achieve this. In some cases optimizing is not sufficient. Partioning data objects is sometimes forgotten, but might do the trick.

Partitioning data objects is a piece of cake from SAP BW 7.3 onwards (see attachment). However, when running an old version of SAP BW with a DB2 database, this could only be achieved by using multi-dimensional clustering (MDC). This feature, which is only applicable for DB2 databases, allows you to physically cluster data in a table along multiple dimensions. It is especially beneficial for query performance in business intelligence applications that use star and snowflake schemas and process large amounts of data with restrictions on several dimensions.
Multi-dimensional clustering option for data objects in SAP Business Warehouse
If you have tried almost everything to optimize your query performance then MDC could be your last option. In the end upgrading SAP BW to a higher version (which makes is possible to create semantic partitioned objects) and using an accelerator (like the BWA or HANA) will be more beneficial for the long term.