pointing universe from Oracle connection to SQL connection

[former_member316829]

Hello Experts,

I have a unx universe which is based on Oracle DB connection (the underlying DB is oracle). The DB is now migrated to SQL. The tables and objects are identical.

But after I change the universe connection from Oracle connection to SQL connection, and refresh the universe structure, it shows that all the tables are missing.

Also in Data foundation layer, when I right click on the table and click on replace to "Database Table", and replace by appropriate table, the dimension and measure in the business layer show [Unknown Reference] in SQL Defination.

So ideally, it is similar to re-creating the universe, is there any other way where I can avoid these issues.

Thanks,

Deepak.

[denis_konovalov]

Not really. You're changing database vendor, so the SQL is different (if you have manual queries - they need to be changed), tables, views, indexes are all different.
there is no way IDT or UDT can do this sort of switch automatically.

Fastest way is to create new Universe vs. chasing all the places where manual adjustment will be needed.

[former_member316829]

Thank you Denis for your reply.

The table names and the objects are all identical. Example I used the tables State, City and Region from the Oracle DB, the tables with the same name are present in SQL , State, City and Region.

and the data inside these tables is same as well.

Would it still cause the issue as there is a DB vendor change..

[former_member316829]

Also i have 9-10 such universe and 100+ reports on it. Recreating it would be a very big task

[former_member316829]

Hi Denis,

I found the way as below, After changing the connection from Oracle to SQL, right click on table and select Change Qualifier/Owner. Check mark Delimit

And then browse the Owner to select DBO

Perform the steps for all the tables in the universe and then refresh structure of universe. Save and validate all the objects and then publish the universe back to repository.

[denis_konovalov]

you still have to check all your sql.

[former_member316829]

Yes you are right, but the advantage in this method is that I do not need to perform change source for all my Webi Reports as the CUID of the universe remains the same.

So there is no effort required at Webi. In case of recreation of universe I would need to perform efforts at Universe as well as Webi.

Thank you,

Deepak

[denis_konovalov]

that is correct. But the answer to original question is still "there is no other way, manual steps are needed" 😉