Categories
Move Tables from One Schema to Another in Physical Layer OBIEE
I have one database with two schemas (Siebel and Financial). The User analyst has access to both schemas.
For various reasons we had defined two databases: BI Datawarehouse and BI Realtime in the Physical Layer.
The BI Datawarehouse database had two schemas: (1) Siebel and Financial defined. Because the analysis has access to both schemas the OBIEE reporting allowed us to write analysis that spanned both schemas with no issues. We had unchecked "Require fully qualified table names" in the connection pool so that the queries would pick up the schema name from the physical layer. The schemas are are directly beneath the database definitiion (we did not create a physical catalog).
The BI Realtime database has one schema (siebel) which contains tables from the same dataware house as defined in the BI Datawarehouse database. It uses the exact same access information.
Within the schema I have the physical tables defined and then aliases to each physical table that I use to create my physical model (joins).
We heave decided we want to merge the BI Realtime Siebel schema tables with the BI Datawarehouse schema tables. I select all of the tables and aliases in the BI Realtime Siebel Schema and drag and drop them into the BI Datawarehouse Siebel schema. However, I am not allowed to drop them (or paste if I cut). OBIEE will allow me to drag and drop the phsycial tables but will not allow me to drag and drop the aliases.
Can you please advise me on how I can drag the aliases from the BI Realtime Siebel schema to the BI Datawarehouse schema?
Thanks...
Answers
-
Ok Had to read that twice. So you want to move Aliases to another schema and not tables. Yes the Admin Tool will not allow you to do that for a very simple reason. Take one of your aliases with Ctrl+C and paste it in Notepad:
DECLARE TABLE "01 - Sample App Data (ORCL)".."BIFOD"."ADDRESSES (Warehouses)" AS "ADDRESSES (Warehouses)" POLL FREQUENCY 0 NO INTERSECTION DIAGRAM POSITION (1529, 1343) SOURCE "01 - Sample App Data (ORCL)".."BIFOD"."ADDRESSES" PRIVILEGES ( READ);
The alias references the fully qualified object name of its originating table.
So if you really want to copy things over, then well you got two possibilities:
a) Use the "Duplicate" function on the whole schema and its child object and afterwards just rename the schema or
b) Be clever with Notepad. Copy over the object definitions into notepad, change the reference to the schema (like "MyDB".."Siebel". to "MyDB".."Financial") and then copy back into the RPD into the schema you want (i.e. "Financial" in the before case).
0 -
To clarify:
I want to copy both the physical table definitions and the aliases from the one schema to the second schema.
Regarding # 1 - I'm not sure how duplicating the schema will solve the problem sense I have to actually merge the two schemas. It is not allowing me copy/move table and alias definitions from one schema into another. Duplicating the schema is not solving this problem. I can move the schema from one database definition to another but not merge the two schemas (by consolidating tables and alias defintions).
Regarding # 2 - (this is also appropriate if I did a duplicate). I really want to retain the physical joins and the relationships between the physical layer and the business model. If I use the notepad solution (which I've done before) I lose all of the relationships and physical joins. If there were a much smaller group of tables then it would not be an issue but it is close to 50 tables/aliases that need to be moved.
I hope this helps and thank you for the suggestions. If I misunderstood anything please let me know and/or if you have a different suggestions please let me know.
Thanks...
0 -
No you didn't but that's how it works. You can also clone the whole database object and then notepad-mangle it and copy it back in - THEN you'd have the relationships as well. Otherwise...nope.
0 -
Thanks for the confirming what we have been seeing. The odd thing about this is that:
1) I can copy the physical table definitions from one schema to the other it is only the aliases that I cannot copy. At first I thought it would be a dependency issue, so I copied the physical definitions and then tried to copy the aliases but it didn't work.
2) You can only copy the table definitions from one schema to another if you un-check the "Require fully qualified table names" box. If the box is checked it will not allow you to copy the table definitions either.
I thought this was an interesting note to what we have discovered. Not sure why Oracle allows the table definitions but not the aliases to be copied and the restraint based upon the qualified names check box.
0 -
1) I can copy the physical table definitions from one schema to the other it is only the aliases that I cannot copy. At first I thought it would be a dependency issue, so I copied the physical definitions and then tried to copy the aliases but it didn't work.
As I said that's doe to the object referencing the fully qualified name of the originating table
2) You can only copy the table definitions from one schema to another if you un-check the "Require fully qualified table names" box. If the box is checked it will not allow you to copy the table definitions either.
Yes same thing but there's no way that aliases can work without that fully qualified reference
0 -
Here is how I got around this (combination of Christian's recommendation) and other efforts:
- Copied the XML of the old schema to Notepad (this included schema name, physica and alias table definitions)
- In notepad renamed the reference of the one schema to the the new schema
- Copied the XML from Notepad and pasted into the target schema (this added all of the tables as well as the physical joins). This was good because my greatest fear was having to recreate all of the physical joins.
- Using the Utility tool "Replace Column or Table in Logical Table Sources" I switched all of the logical table references from the old schema aliases to the new schema aliases.
- Deleted the old schema, saved and ran consistency check
0