How to migrate Convergent Charging from Oracle DB to HANA DB.

[gpiekarski95]

Hello SAP Community!

I am coming to you with kind of niche topic about niche product which is a part of the SAP portfolio - Convergent Charging. It was a part of the Hybris solution which evolved into BRIM (SAP Billing and Revenue Innovation Management). There are many descriptions of Convergent Charging on the Internet. Just to make it short: CC is a solution which offer companies a simplified way to charge customers on a pre- and post-paid basis. You may create many different types of subscriptions to offer variety of services for different type of customers and all can be done really quick and on the easy way. 

Some basic information about CC

The first thing we should know, is that CC is officially Netweaver based product. It doesn't mean that it is built like ABAP- or JAVA-stack. It may be, however, closer to JAVA-stack, since it is developed in JAVA. There are common parts with Netweaver stacks, like:

• profiles: has its own specific parameters, but some are taken from ABAP- or JAVA-stacks,
• kernel,
• installer: I mean SWPM here, which is used for installations, updates, upgrades and uninstallations, however for the CC you have to use a special version which is delivered exclusively for it, 
• there may be some more similarities too, but I cannot think of any of them right now.

On the other hand, this product is not that complicated as known NW-stacks. It has a documentation and support above the satisfying level. We never had a big issues with it, I can count on the fingers of one hand major issues and on both hands minor ones. Since we started few years ago with implementation of this product, we have a positive thoughts about it. Updates/upgrades are really fast, administration is simple, support is responding very fast. I may write another blog entry about technical side of CC, but I will try to focus on the main topic - migration from Oracle to HANA. 

Challenges

First of all, required OS or DB versions are well described in the PAM: Convergent Charging PAM. According to it, SAP HANA 1.0 (and higher) is supported until CC 5.0 and from 2020 only SAP HANA 2.0. Very good, we can migrate to 2.0. We have been searching for the generic migration Guides, but could not find any. You may find on the Web products, which will help with Oracle to HANA conversion, but you need additional license and probably some trainings. After discussion with SAP they informed us, that there is no official or even unofficial guide how to do it and such procedure is not supported ☹️. At first were disappointed and didn't know how to proceed, because we wanted to move to HANA without a big problems and this type of steering like for ABAP- or JAVA-stack was missing here. Luckily for us, they pointed some HANA tools used by the other clients like: SDA (Smart Data Access) and SDI (Smart Data Integration). Additionally the CC itself is really small system. It has around 700 tables, so it is not that overwhelming. The next thing they pointed was a guideline Copying an SAP CC system | SAP Help Portal. It describes migration of the CC system to any database, from the higher level, so without specific OS, DB, etc. Just pure, consistent strategy. Not that bad! After some brainstorming and meetings regarding available tools, different approaches, application support, we decided to make it as simple as possible and to use SDA.

Oracle-export approach

Before I go to the substance, let me present our road to the goal. The first, and seems to be a natural, step was to export content of the tables from Oracle in CSV-format and import them to HANA. What we noticed, was the date format. In Oracle with our default environment variables we had DD.MM.YY while in HANA we needed YYYY-MM-DD HH:MM:SS. Change of the NLS did the job and we could extract the tables. From this point we could choose to have the tables in CSV or in the text files as INSERTs.

The first approach seems to be very complicated and time consuming, because we will have to import the files via SAP HANA Studio only one at a time. Imagine doing it for ca. 700 tables. The whole procedure is described here: Import CSV\Flat file to Hana Database. There is no bulk option built-in. OK, there is one, if the files are already stored on the HANA host. IMPORT FROM Statement (Data Import Export) can be used here and in an easy way duplicated for each table. Additionally it may be necessary to modify manually each file, because Oracle export may not be suitable 1:1 for import into HANA.

The second approach, with generated INSERTs from Oracle, sounds better. The text files can be modified in an easy way, but the biggest problem is the number of line which can be inserted into SQL Statement in SAP HANA Studio. We tried with small numbers like 50.000 or even 10.000 and every time JAVA memory exception was raised. 

There might be other approaches, different methods used, some automation and someone may have also other issues. We gave up here and focused on a tool delivered with SAP HANA - SDA

Smart Data Access

We have started with the basic questions:

1. What about the license? "Because smart data access is part of the SAP HANA core system, no additional licensing is required to use it." ✔️
2. Is our database version supported? 2600176 - SAP HANA Smart Data Access Supported Remote Sources ✔️
3. Is it out-of-the-box tool? Unfortunately no, but preparation steps are quite simple and have already been known to our HANA Team. Just for the record - here they are: Oracle Database ODBC Driver  ✔️

After all was set we could finally start. We will follow here the general guideline for copying CC, which I mentioned earlier. 

Preparing the destination system

I assume that the installation has already been done on your side and we can move on. If not, please refer to SAP's Installation Guides. Additionally, user created in HANA for CC installation, should have one more Role under System Privileges: "CREATE REMOTE SOURCE". 

Backing-up the tables of the destination Core Database and Session Database, according to the copy scenario

I suggest now to perform full backup of the destination system, just to be sure, that we can revert our CC to the first working version. Below are the steps we performed to create backup.

Mark all tables under relevant tenant and CC HANA Admin user, click right mouse button and choose export:

In the next step, all irrelevant tables can be selected and removed from being backed-up as on the screenshot below. Just for the information: the small icon  indicates, that they are virtual tables. We do not want to back them up, because it does not make any sense.

Following options must be used from “Export Selection” screen. Export catalog will be stored on a local storage. Number of Parallel Threads can be i.e. 5. Click on Finish will start exporting the tables:

Backing-up the source Core Database and Session Database

This step is not necessary here, because from SAP HANA Studio we are unable to change anything on the source Oracle database. At least I have not found any option to do this. 

Transferring the adequate data from the source Core Database and Session Database to the destination ones
Restoring the adequate data within the destination Core Database and Session Database

Congratulations, you have finally reached your destination. Thank you for being patient 😊

At first, we have to create External Source which allows us to connect to the Oracle source database:

Then change Adapter to ORACLE, fill in the Data Source Name from tnsnames.ora, enter username and password for access to Oracle DB:

Now let's create virtual tables with links to the content of the Oracle source system. Expand previously created External Source until you reach username provided above. Mark all tables (I assume there are only CC tables there) and click on "Add as Virtual Table". Provide prefix for the tables and wait. Progress can be monitored in the "Progress" tab:

For the further processing, we have to collect the table names from the target system. To do this, we have to execute a SQL command on the database/tenant where the CC has been installed. As a schema user, insert the CC HANA Admin user used for installation:

select TABLE_NAME from SYS.M_TABLES where SCHEMA_NAME ='<cc_hana_admin_user>';

 If you want to list the virtual tables, other statement must be used:

select TABLE_NAME from SYS.VIRTUAL_TABLES where SCHEMA_NAME ='<cc_hana_admin_user>';

The table names are required to build INSERT and SELECT statements, which we used to migrate the tables' content from the Oracle to HANA. The fastest way, for me, was to generate everything in the Excel sheet. So I paste already listed tables into new Excel file.

First step was to delete (actually to truncate) content of the tables in target system. Under documentation Restoring the Destination Core Database SAP describes which tables should be restored with which action. My approach is, that the ones with "Overwrite only", must not be deleted as they already have required content. The other tables can be dropped. Here following statement was used:

TRUNCATE TABLE <cc_hana_admin_user>.<table_name>;

So basically if there is a list of the tables in Excel, the statement for each table can be created easily.

The next and last step is to insert data from the source into target database. It is done by using following statement:

INSERT INTO <cc_hana_admin_user>.<table_name> SELECT * FROM <cc_hana_admin_user>.<virtual_table_name>;

Last word for the end

It is actually all. Of course you may struggle with performance issues as we did. Just to avoid any, the SQL statements for small tables can be executed in one SQL Console and INSERTs for the big tables in other, running parallel. Additional thing, which may speed up the process, is to perform Housekeeping according to the original documentation: Purging and Archiving | SAP Help Portal. We could delete a pretty number of outdated logs. 

Hope you find this blog entry interesting. Any comments or questions are always welcome.