Steps Validations points for Changing DB vendor from Oracle to SQL in IDT Universe

[former_member316829]

Suppose you have a Unx universe build on Oracle Database connection, and have a requirement to change the connection from Oracle to SQL. In this case, you need to manually perform steps on the universe and validate the objects, below blog describes these steps and impact of it on the universe.

 

Step 1:

Export the Unx universe locally in a IDT project.

There are two connection Oracle_conn based on Oracle Database, SQL_conn based on SQL Database, in the Data Foundation layer, change the connection from Oracle_conn to SQL_conn

Save the project. Now, if you Refresh Structure of the universe as below



All the tables in the universe will be shown missing:

Step 2:

Right click on a table, select “Change Qualifier/Owner”

Currently Owner is HR:



Check mark Delimit and then browse in Owner option.



Select "dbo" and click on OK.



Repeat this steps for all the tables in the universe, then Refresh Structure, as below it shows that there are no missing tables and all tables have been mapped to the new SQL database.



Now, click on Next

Step 3:

If there are identical columns in both Oracle and SQL database, no error would be prompted but if there is any column in Oracle which does not match SQL table then you will be prompted for deleting the table, similarly if there is an additional column in SQL table, you will prompted to add the new column.

Example, in the Employees Table for Oracle the EMAIL column is present, but not present in SQL



Then you might need to delete the EMAIL column by checking it.

Finally, the refresh structure prompt will show a summary of columns added and deleted respectively. Save the project.

Step 4:

Go to Business Layer and Check Integrity for all the objects, joins and tables.

Summary:

From my observations, I have found that, there is Impact on Derived Tables, the definition of the Derived tales needs to be updated.

There is no impact on Alias tables, Joins, Loops, Traps, Dimensions, Measures, Filter definition after the change.

Advantage:

These steps would be helpful if you do not want to change the CUID of the universe. As the CUID of the universe is not changed, the Webi Reports or Dashboards in the universe will not be impacted.

 

Thank you,

Deepak