The last view months I tried to develop an universe on DB2 tables. This is a development environment and during the project I asked the DWH team to add some records manually (DWH is also new) to test the universe and reports. In some cases this is enough and the business can give their approval.
For other cases I need more data to receive an approval. So, I want to migrate the universe to the acceptance environment (also DB2) where we have lots of data.
This is very easy to do when I migrate a ‘normal’ table. Just select all tables and change the owner.
Unfortunately I have to change the query manually for all derived tables.
And I did not succeed in migrating alias tables.
Another problem is that I want to ‘switch’ the connection to go to development or acceptance environment. Because for some reports I still want to use the development environment for the manual inserted rows in DWH.
So, here are my questions:
Is the only way to migrate derived tables to change it manually in the query?
How can I migrate alias tables to the new environment?
Is ‘switching’ between the connections and using 1 universe the right way? Or is it wise to develop 2 universes; 1 for development, 1 for acceptance?
Yes, which is why they are not a good solution…:).
You should be able to change the base tables, did the aliases work then?
How many environments have you got or are you just switching the universe connections between them from one BO environment?
I can see this being a pain, but if you have two universe versions you may have problems when you migrate them between environments, human error for example.
You are better off having exact DB mirrors between environments, with the same owners for the reasons you have described…
Hmmm, I did not find another solution, that’s why I am using a derived table. I have a fact file with activities of employees. The activity codes can change during a day. I would like to see all those changes, but I need an extra table where I can only find the max activities (last) of an employee for each day (so only 3 fields in this table). The solution I made by using a derived table works perfect! But if there are other options, I would like to know them. I can learn a lot!
No, maybe I did something wrong, but I changes all the owners of the base tables. And the funny thing is, that i did not get an error message of the base tables.
I only have 1 BO environment for dev and acc. But we have 2 separate DB2 environments. So I have to use 1 environment to work on 2 databases. Indeed I have to switch the universe connection.
For production it is another story. We do have a separate BO environment and also a separate database. But that is not my responsibility; we have an Operational department.
Yes, I can see that. I already had a corrupt universe
Unfortunately, it is not possible to have same owners. Our DBA’s have guidelines and I already had this discussion. I had exactly the same idea…
OK, well how about dealing with this another way, to get rid of the derived table. You could create a universe filter, that aliases your employee table, for example
Employee.ActivityDate = Select(Max(a.ActivityDate) from Employee a Where Employee.EmployeeID = a.EmployeeID)
What was the error, did the aliases lose their linkage?
Maybe, but, however, they should be looking at making the whole system work for everyone, not just for their own convienience…;).
Are these DB instances on the same physical machine, if not, I would argue your case for having them named the same.
Yesterday there was something wrong with the acceptance environment, so that is the reason of my late response
Thank you for the hint to avoid derived tables by using alias and a filter. I will try to solve this, together with a lot of other things to improve my universe (I am kind of new)
The good news: I succeeded in migrating my universe to the acceptance environment, including the alias tables. I think I forgot to change the owner of an original table the first time, because I did not have a problem with that! Now I have to wait for correct data and run the reports.
Having the same owners is not an option…you can see the reaction of the other 2 BO users. For my company it is the same story. I work for a company with thousands of employees. At our department we have 3 DBA’s and we have lots of them all around the company. They agreed standard guidelines together. No chance that a simple BO developer like me can change those rules…
But if I select all tables together (and remove the derived tables), and change the owner every time I want to switch to another environment, I can live with that.
I wrote a script to do this sort of migration. It basically does a global “search and replace” on table names (for owners) and derived tables. When I need to “repoint” a universe from DEV.table_name to ACC.table_name this script does everything for me.
And it’s available to download for testing if you want.
(BOB member since 2009-07-08)
(BOB member since 2005-01-06)
(BOB member since 2009-07-08)
(BOB member since 2006-10-05)
.
(BOB member since 2002-06-06)