SAP_INFOCUBE_DESIGNS tool

In case dimension tables of star schema data model becomes too large comparing to fact table there can be a performance problems. Thus, it needs to be monitored. SAP provides a tool for that. It is ABAP program called SAP_INFOCUBE_DESIGNS. The tool provides a list of cubes associated with number of rows and density % rate for each if the cube.

The density ratio means a number of rows in the cube fact table divided by the number of rows in each of its dimension table. The ratio is calculated by DB specific FM RSDU_INFOCUBE_TABLE_SIZES e.g. RSDU_INFOCUBE_TABLE_SIZES_HDB in case of HANA DB.

If the ratio is very high, there should be done something with the cube design. Some characteristics can be moved to separate dimension. Similarly, if the characteristics is very large it is be only one in the dimension and the dimension can be market as line item dimension.

In case of BW4/HANA the report is not relevant as source of the information is table RSDCUBE and there are no cubes in BW/4HANA. Basic infoprovider type of objects is aDSO that has flat layout so no star schema anymore. To get to know more information about the aDSO one can use watermarks tool.

 

More information:

Note 1461926 - FAQ: BW report SAP_INFOCUBE_DESIGNS

Error: Numeric overflow for parameter/column

I recently ran into an issue of reading aDSO object from custom code. I used a method READ of ABAP class CL_RSDRI_INFOPROV. Return code of the method call was 8 = inherited_error. When I debugged it; I came to place in FM TREX_DBS_AGGREGATE where following call is performed:

cl_hdb_sql_for_aggr_req_facade=>get_instance_for_1st_chunk

Exception that was triggered I found following error:

AttributeEngine: overflow in numeric calculation;AttributeEngine: overflow in numeric calculation;exception 70006944: AttributeE

overflow in numeric calculation; $message$=aggregation failed $BIC$<KF_name>$sum$ 1 fixed14.3(17) exception 70006944: AttributeE

overflow in numeric calculation; $message$=aggregation failed $BIC$ KF_name $sum$ 1 fixed14.3(17) ,Exception in executor ...

plan408469425@ndhcdb01-int:30085 while executing pop 6: calcEngine search on olapIndex failed.,QueryId: ...

00O2SPBEZ1RC04NPREWOW2QMR:_C10/[Request Info: Object Name = "<db_schema>"."0BW:BIA: <cube/aDSO_name>", FM Name = TREX_DBS_AGGREGATE]

Error 6.944 has occurred in the BWA/SAP HANA server

Error reading the data of InfoProvider <cube/aDSO_name>$X

Seems this is a generic DB error no 10811:

Numeric overflow for parameter/column (<id>) source type <source_type>, target type <target_type>, value '<value>'

Apparently, value of KF mentioned in the error exceeded its threshold. Solution was to delete the data that caused this overflow.

 

More information:

2399990 - How-To: Analyzing ABAP Short Dumps in SAP HANA Environments

2393013 - FAQ: SAP HANA Clients

2352450 - ADBC: Numeric overflow for data type BIGINT

Conversion of basic cube into transactional (real time) one

 Once you create a basic cube in t-code RSA1 there is nothing within that t-code that would enable BW developer to change the cube to transactional (real time) one. However, there is an ABAP report, which supports such a conversion. It is called SAP_CONVERT_NORMAL_TRANS Conversion: Normal <--> Transactional InfoCube. The report also support backward conversion from transactional to basic (normal).

Here is brief overview of the report UI and its output:

Deleting overlapping requests

A process available in process chain called "Delete Overlapping Requests" is very useful feature within the administration of data targets. I blogged about it already here. This time I want to point to few situations that may occur while using the process mentioned.

First of all the "Delete Overlapping Requests" process in case of DTPs with respect type of the DTP. It is a common misunderstanding that only DTPs type of FULL load are supported. That would mean that in case the DTP is type of DELTA the deletion of overlapping request will not work even the deletion conditions are met. However, there is a way how to make it work. Depending on BW version a Notes like 1336410 (70SP22: Enhancements to CL_RSBK_DTP=>GET_ALL_BY_PROPERTY) and 1359397 (P22:PC:REQUDEL:Switching full variant to deleting delta DTPs) need to be implemented. Once it is the case, the "Delete Overlapping Requests" process works with delta enabled DTPs. Only drawback is that the DTPS must be set with indicator specifying that delta data is to be transferred only once. Also another option to try out is to first set the DTP to full mode in that way specify the DTP into the "Delete Overlapping Requests" process and afterward to set the DTP into delta mode.

Another interesting case can be; when "Delete Overlapping Requests" process works with DTP, which has Routines on its filter. Depending on filter’s field (infoobject) one needs to be careful while carrying out ABAP coding. Especially during populating range low/high value by using variables that are of different type as the field/infoobject. Because of ABAP nature while converting between different data types a value can be aligned to LEFT instead to RIGHT. Thus value that is aligned to other side is not recognized as correct value. See Note 2392079 ("Delete overlapping request" variant does not work properly if DTP has a filter routine) for specific example.

More information:

2753683 - "Delete overlapping requests" variant does not delete expected requests

Online docu

Zero elimination for cubes

Zero elimination (sometimes called NULL elimination) is process within BW which does deletion of records in fact table where all Key Figure values are zero. This can be normally seen as BW administration task. It can be performed on Manage screen of cubes in RSA1. On tab called COLLAPSE a check box “With Zero Elimination” is available. 

This task can also be done outside from the RSA1 by executing or scheduling a ABAP report RSCDS_NULLELIM. The report calls FM RSCDS_EXEC_NULLELIM for the task execution. In case the BW system is HANA based there is a report RSHDB_EXECUTE_CLEANUP_NULLELIM for HANA DB.

Parameters of program are very well described in this wiki.

From technical point of view notice there are few limitations of this functionality. Main one is that there is no 100% zero elimination for the Key Figures of type FLTP. It is not possible to precisely evaluate whether the KF type of FLTP is really equal to zero. Value of the KF may be close to zero but not precisely zero. Therefore, as word of advice in scenarios that rely on exact summing never use the KF type of FLTP. Report is using value 1.0E-50 which is pretty close to zero but there might be a values stored in the BW that are even smaller.

Also the NULL elimination is only possible if the KF aggregation is SUM for all of the KF used in the cube. If the cube contains the KF with aggregation types other than SUM (e.g. MIN or MAX) the database cannot eliminate the zero lines during the compression.

More information:

1224631 - Program RSCDS_NULLELIM and Float-Keyfigures (Type FLTP)

1587759 - Duplicate Records in InfoCube Dimensions

1820706 - Report RSCDS_NULLELIM fails on SAP HANA

2206835 - Report RSCDS_NULLELIM now also on SAP HANA

2521338 - Compression with Zero Elimination does not work

Delete Overlapping Requests from cube: SAME OR MORE COMPREHENSIVE vs OVERLAPPING

Within process chains there a particular process that can delete overlapping request from cubes. It is useful to use it case of scenarios where same data is being reloaded and staying same request in the cube would cause double data in reports.

While adding this process into the chain couple of settings need to be specified. What is crucial is to provide selection criteria for the request(s) to be deleted. There are two options on how to setup the request deletion in this process based on selection.

1. SAME OR MORE COMPREHENSIVE

Extract from the documentation:

Only delete if same/comprehensive selection conditions apply.

If you set this indicator, requests are only deleted from the InfoCube if the selection conditions of the new request are the same as or more comprehensive than the selection conditions of the request to be deleted.

 

2. OVERLAPPING

Extract from the documentation:

Also delete if partially overlapping select. conditions apply

If you set this indicator, existing requests are also deleted from the InfoCube if the selection criteria of the new request partially or wholly overlap the selection criteria of the request to be deleted.

Basically difference between the two is 1^st case (SAME) the request that shall be deleted must be the same as the one loaded this is also with regards to number of loaded records. Whereas in 2^nd case not all criteria need to match; it is less restrictive than 1^st case.

 

Inserting data into InfoProviders manually

Usually in case of need for manual input of data we speak about planning applications in BW area. There are technologies like BW-SEM (Strategic Enterprise Management), BPS (Business Planning and Simulation) BW-IP (Integrated Planning) where those applications are being developed for specific needs of business users. Of course there are more planning offering from SAP I just mentioned here a few.

Although it is quite rare situation BW developers sometimes face situation where customers are demanding function to enter the data into InfoProviders manually even application is completely not related to planning. In this case it means to have a possibility to insert the data into BW’s InfoProvider w/o any InfoPackage/DTP any transformation/update rule. The requirement like this may sound strange. To name few situations where this can be applicable can be: lack of data in development/test/QA environment and no time to develop regular BW flow to populate the data. Also one could create planning function to the manual entry but again we do not want anything from planning be involved in here.

So given this there are following options available in BW to insert the data manually:

1. TA RSINPUT (Manual Data Entry) – supports cube and DSO objects but cube must be type of real-time and DSO must be type of direct update. There are 2 steps that need to be carried out. First you can configure which fields will be marked as for selection, maintenance, no entry, mandatory and as default value.  This is done on CONFIGURE screen. On next screen which is CHANGE/CREATE screen we can actually enter the data. ALV type layout is displayed for convenient entering of the data.

2. ABAP report CUBE_SAMPLE_CREATE – works with the cube regardless of its type. It can be “Standard InfoCube” or “Real-Time InfoCube” However the report works only for cubes no other infoproviderrs (e.g. DSOs are supported).

Values can be populated into the cube in following modes:

A) Generated Values – all values are randomly generated just to fit the type of infoobjects used.

B) Vales from Master Data Table – values from corresponding Master Data tables are used to fill up the data.

C) Ready-For-Input ALV – Only values that are entered by user are saved in the cube. User gets ALV input screen and can freely add the data as he/she wishes.

3. Custom ABAP report – One can create custom report similar to option no 2. This can be tailored only for specific cube and can be designed to run in background. You can use FM CUBE_SAMPLE_CREATE_NEW to see how data should be processed and FM RSDRI_CUBE_WRITE_PACKAGE to insert the data.

Additional info:

Step by Step Procedure: Creating Sample Data for InfoCube

Unlock InfoObjects option in cube maintenance

I was recently changing structure of one info cube in BW system. I had to remove one infoobject and add other one. For no particular reason I didn’t want to use Remodeling functionality to do that. What I did was just I simply put right click on desired IO to be removed and choose Delete option. However nothing happened and my IO stayed in the cube’s structure. I was sure that cube had no data loaded. I also checked aggregates and indexes. None of them existed. I had suspicion that there are still some data left in the cube. But how come as also dimension tables were deleted.

Moreover I observed that some of my IOs in the cube’s dimension are blue ones and on other hand some of them are black. Again blue ones were those I suspected having the data.

After some examination I found out one option. There is a function called Unlock InfoObjects available in TA RSA1 and RSDCUBE. While I went to cube maintenance screen in menu “Extras” there I found this function. 

What actually this function just did? It released those blue IOs in the cube. Seems it did action kind of deletion cube data also from the IOs which had still same data. After using the function it should be possible to add and/or delete IO from the cube’s structure.

To complete this blog post I need to mention that technically function is implemented in class CL_RSD_STRUCT_MAINT and in method SET_GET_IOBJ_LOCD.

Line Item Dimension Flag

There are some possibilities of how to improve performance of BW’s infocubes. One of them is to flag particular dimension Line Item Dimension. This can be done for dimension where there is exactly only one characteristics assigned into it. This is so called degenerated dimension. Doing this no dimension table is created. So SID table of that characteristic is acting as dimension table. Then there is fast access to the data as no real dimension table is present. As the model is simplified loading into that dimension is faster as no IDs for dimension table are generated. 

There might an issue arise when you do some changes. The changes can be related to adding or deleting the IOs from such an Line Item Dimension. It is clear that no other IO can be added to such a dimension. However let imagine that I want to deactivate that flag and add other IO. For some reason this may not be possible.  Or I want to get rid of whole dimension but system tells me that I have to remove IO first and it is again not possible.

In such a cases I removal of the flag can be done won database level. The flag as itself is stored in following table. The table is called RSDDIME (used in DB view RSDDIMEV) and field is called LINITFL -> Line Item Dimension. By removing the flag = X particular dimension can be deleted form the cube.

Disclaimer: Notice the blog post discusses activities done in debugger while changing the values of table fields. Such activities are usually not supposed to be executed. Bear in the mind that you may cause serious harm into your system. If you decide to proceed with it do it only after real understanding of all consequences and only on development and/or test system.

InfoCube Utilities

This blog is originally posted on SCN:

InfoCube Utilities

Recently I found infocube related functionalities which are not so obvious within the standard transactions like RSA1. Within this blog post I’d like to share them to others. May it happen that someone benefit from it. One of them is mass copy cube a like function. This function usually every customer tries to do by self-creating some Z* report. And moreover there are other functions…

So how I found it? I needed to change an name space for one of my cubes and I was browsing service.sap.com/notes to find out how to do that. I ran into the SAP Note 1708553 - InfoCube creation enforces BW application selection where I saw transaction RSDCUBEM mentioned. BTW there in the BW systems there are also transactions RSDCUBED and RSDCUBE. As they all point to same ABAP report SAPMRSD0 and screen 1000 I cannot really say what is the difference between them.

As I was exploring TA RSDCUBEM I found interesting items in menu Edit-> InfoCube Utilities->

From the pop-up following function are available:

1.    Make Copies – Allows copying one info cube to new ones with different names. You can specify start and end suffix of 2 characters for new infocubes names. Function is implemented by calling ABAP report RSDG_CUBE_COPY.

2.   Activate Infocubes – Enables mass activation of cubes per chosen cube type or cubes in particular InfoArea or selection. ABAP report RSDG_CUBE_ACTIVATE is behind this function.

3.    Delete Infocubes – This is mass deletion of infocubes per cube type or specified InfoArea or selection. Implemented via call of ABAP  report RSDG_CUBE_DELETE.

 4. Analyze Infocube – This is just a simple call of TA RSRV - Analysis and Repair of BW Objects.

5. Reorganize Texts – Within the call of report RSDG_CUBE_REORG_TEXTS another report is called RSDG_MPRO_REORG_TEXTS. Report is dealing with texts (table RSDCUBET) of cubes/MultiProviders and is trying to insert the text of e.g. business content objects if text is not present in its active version. At least that is my impression based on what I see in the report’s source code. I’m not really sure what this function would be useful for. If someone has experience with this report can you share it in comments?

6. Change Validity Slice – This function is useful while having non-cumulative key figures (like SAP ECC Inventory management) in cube. As per by standard behavior Validity period of stocks is determined by the oldest and the most recent material movement. If you need to display data in your report that lies further in past you can run this function to change the validity period. Function is implemented by calling ABAP report RSDG_CUBE_VALT_MODIFY.

Grouping of InfoObjects in InfoProviders

This post to is to remember one nice functionality available in RSA1. It is related to InfoProviders and grouping of key figures. It comes very handy in case of huge InfoProviders with hundreds of KPIs.

In case of InfoCube/MultiProvider/VirtualProvider/HybridProvider objects - only for Key figures, characteristrics are grupped already through dimensions:

Here how does it look like:

 In case of DSO objects – available for grouing of Data Fields: