SAP on IBM i: Mass Conversion of Standard InfoCubes and Semantically Partitioned Objects to Flat Format and Back
Since the program RSDU_IC_STARFLAT_MASSCONV was introduced in blog post Mass Conversion of Standard InfoCubes to Flat InfoCubes, it has received extensive enhancements.
To enable the newest functions, install the support package levels or correction instructions that are attached to SAP Note 2750965 – Conversion of flat SPOs to star schema. The documentation for program RSDU_IC_STARFLAT_MASSCONV, which is attached to SAP Note 2446889 – Mass Conversion of Star InfoCubes and SPOs to Flat Format, has also been updated accordingly.
If you need a reminder of why converting to Flat InfoCubes may be interesting to you, check out this blog post: Faster Analytics with Flat InfoCubes for SAP NetWeaver Business Warehouse on DB2 for IBM i.
If you wonder which new functions were added, check out the remainder of this blog post for an overview over the most important updates:
Conversion of Semantically Partitioned Objects
The program RSDU_IC_STARFLAT_MASSCONV supports not only the conversion of single InfoCubes, but also the conversion of InfoCubes that are part of semantically partitioned objects (SPO).
In order to keep an SPO consistent, it is not possible to convert the InfoCubes that form an SPO separately. With RSDU_IC_STARFLAT_MASSCONV, you can schedule SPOs for conversion and the program takes care of converting all InfoCubes that are part of the SPO and adapting the metadata of the SPO accordingly.
Besides the “parent” conversion of the semantically partitioned object itself, all “child” conversions of the InfoCubes are also listed on the Conversion Jobs tab page so you can see the status of each conversion and track its progress individually.
Conversions in Both Directions
In addition to conversions of InfoProviders from the standard (“Star Schema”) format to flat, the program now also supports conversions from the flat format back to the standard format.
After the completion of a conversion to the flat format, the respective InfoProvider used to be moved to the tab page Flat InfoProviders. This tab page doesn’t exist anymore. Now, after a successful conversion, the InfoProvider could be converted back to the former format again. Therefore, it stays on the Convertible InfoProviders tab page. During a conversion, the InfoProvider is highlighted and the InfoCube subtype is replaced by the conversion direction. Once the conversion is finished, the subtype and metadata of the InfoProvider are updated accordingly on the Convertible InfoProviders tab page.
Partitioning of InfoCubes
If you have the product IBM Db2 Multisystem installed on your database server, you can choose to partition standard InfoCubes. In this case, if you start a conversion from flat to standard, and if the InfoProvider contains time characteristics that are eligible as partitioning key, a pop-up prompts you to define the partitioning conditions for the InfoCube.
If database partitioning is available on your SAP system, but you do not want to partition any standard InfoCubes, select the option For New Conversions to Standard Format: Don’t Ask for Partitioning and Create InfoProviders Unpartitioned to skip the pop-ups for determining partition conditions.
The Convertible InfoProviders tab page displays information about each InfoProvider’s size and structure. If database partitioning is supported on your system, the table also displays partitioning-relevant metadata, for instance whether the InfoCube is partitioned and how, and which time characteristics are contained that are eligible as partitioning key.
If you are interested in more detailed information about using database partitioning with SAP Business Warehouse systems on IBM Db2 for i, have a look at Database Partitioning and Semantic Partitioning for SAP Business Warehouse on IBM i. The blog post researches the prerequisites for database partitioning, capabilities and limits, and the differences to semantic partitioning.
To convert InfoProviders, you need authorizations for the following:
- Execute the program RSDU_IC_STARFLAT_MASSCONV
- Create variants for conversion programs
- Submit and release batch jobs
- Maintain the definition of the respective InfoProviders
If an authorization is missing, an error message is shown. For more information about which authorization objects are required, see SAP Note 2804993 – Repartitioning / Flatcube Conversion / Reclustering: Authority Checks.
Distinction Between Job Status and Conversion Status
The conversion of an InfoProvider is not necessarily finished when the corresponding conversion job is finished. The conversion job might end prematurely with an error. And the conversion of SPOs consists of several conversion jobs that must finish successfully in order to complete the conversion of the SPO. Therefore, both the status of the conversion job and the status of the conversion itself, are displayed on the Conversion Jobs tab page.
Only entries with conversion status Complete are removed from the Conversion Jobs tab page when you select button Clear Finished Conv. From List or when you regenerate the worklist.
It might happen that an unfinished conversion has no corresponding background job for the following reasons:
- The job was manually deleted by using transaction SM37.
- The background job logs were cleaned up by program RSBTCDEL.
- A conversion was initialized by using program RSDU_REPART_UI, but the scheduling of the conversion job was canceled.
In these cases, the conversion is still displayed in the list of conversion jobs with the job status Does Not Exist. This enables you to select the conversion and schedule a new conversion job by choosing Reschedule Aborted Conversions.
If several conversion jobs were scheduled, the scheduler used to release them in alphabetical order of their name. Now, you can influence the order by which the scheduled conversion jobs are released (and then executed) with the new parameter Release Conversion Jobs Ordered by. Select one of the available sorting criteria (job name, InfoProvider name, job creation time, conversion initialization time, maximum request size) from the drop-down list and choose whether the sort order should be ascending or descending.
If you change this parameter (or parameters Max. Number of Parallel Conversions or Max. Package Size for Parallel Conversion Jobs) while the scheduler is active, you don’t have to stop and restart the scheduler anymore for it to become aware of the change. The scheduler job automatically uses the new values at its next scheduled run.
Issues with Non-Convertible InfoProviders
InfoProviders that do not meet the requirements for a conversion are listed on the Non-Convertible InfoProviders tab page. After you check and resolve the reason why an InfoProvider cannot be converted, instead of generating the entire worklist anew, select the InfoProvider and choose Re-Collect InfoProvider Data to have the InfoProvider checked again.
Reasons why an InfoProvider cannot be converted are now displayed in the user’s sign-on language. Clicking on a reason opens a pop-up from where you can navigate to additional information like the message class and number, and if it has one, the long text of the message.
If multiple issues prevent a conversion (for example, multiple InfoCubes of a semantically partitioned object cannot be converted), the first reason found is shown and an Expand icon indicates that there are more. Click on it to call up a pop-up with the entire list of issues that must be resolved.
Manually Unlocking InfoProviders
When a conversion is executed, the InfoProvider is locked against changes from other processes, for example, loading data, or changing the InfoCube structure in the Data Warehousing Workbench.
During a conversion, issues might occur that require the deletion of these locks to be able to resolve them. Or, if you delete a conversion request in the conversion monitor, existing conversion locks on the InfoProvider are not automatically deleted. In such cases, you can manually remove these locks on the Convertible InfoProviders tab page, or on the Conversion Jobs tab page.
Select the InfoProvider, then select button Manually Unlock InfoProvider, and select Unlock on the confirmation prompt.
If the selected InfoProvider is a semantically partitioned object, a pop-up is shown where you can choose, which of its locked InfoCubes should be unlocked.
Other Small but Helpful Additions
More messages are saved in the background job logs. For example, when generating the worklist, the job log of job RSDU_IC_FLAT_MASSCONV_BTC gets updated continuously with the names of the analyzed InfoProviders, so you can better trace the progress of the job.
Select (Re-)Generate Worklist to start the generation. Then, select Worklist Generation: Job Overview to navigate to the job log of the job that generates the worklist. In the job log view, select Refresh to see the analysis of InfoProviders progress.
The scheduler job always stays at the first line of the Conversion Jobs table, no matter how you sort the other jobs.
The Convertible InfoProviders tab page offers more information about the InfoProviders. Some of the new columns are initially hidden. Choose button Change Layout… and move the columns to the list of displayed columns to make them visible.
(Click on images to enlarge them)