Ok , for those who might be interested (and have got the time to read this lenghty post) this is what I found out in the end regarding this problem
.
After some advice in related link Derived Tables Problem
I ran “Integrity” against the Universe domain in Supervisor and got “The Universe domain “Universe” is no longer valid. (ADM0003)”. Unfortunately there is not a lot of info about what this actually means.
I then spotted seven repository tables that were sitting in my Warehouse database rather than in my Repository database. These seven tables were
UNV_COLUMNS
UNV_COLUMN_DATA
UNV_JOIN_OBJECT
UNV_OBJECT_KEY
UNV_OBJ_COLUMN
UNV_TABLE_DATA
UNV_X_UNIVERSES
Hereafter known as The Magnificent Seven. How did they get there?
(Useful link on these is New Database Tables In 6.5.1)
Well methinks that it all occurred on the migration from 5.1 to 6.5.
I copied the repository by Insert
Select
from the 5 to the 6.5 Repository database. Then ran Supervisor Admin 6.5 which detected I had a 5 data model and migrated it to 6.5 as the documentation says it should. My suspicion is that although it worked out the model was in my Repository DB some magic happened that caused it to load the new tables into the warehouse database instead dont ask me how but Id love to know.
Anyway as far as I was concerned everything was working OK. I played with derived tables OK at my PC and only ran into trouble when I published reports using them and no-one else could use them.
It turns out that one of The Magnificent Seven (UNV_TABLE_DATA) is used to hold the code for derived tables and it was in the wrong database! The other 6 are for features I dont yet use (except for UNV_X_UNIVERSES which is even more special and no-one knows what it is for).
I assume I was OK when using my PC cos I was accessing my local .unv file which held the DT code. It seems odd that when exporting the Universe it did not error saying it could not write to the UNV_TABLE_DATA table?
Anyway I have created The Magnificent Seven in the Repository DB and now when I export the Universe UNV_TABLE_DATA gets populated! Oh Joy!
Also my Universe domain is now valid again! More Joy!
Id be interested in any comments from you gurus out there as to whether I can really trust my Repository now?
Sorry to go on with this post but I spotted several other instances of people either with the same Derived Table problem or with the Invalid Universe ADM0003 so hope this is useful to someone out there.
Bottom line seems to be Ensure your Repository Data Model is complete! Be good if the data model was available somewhere?
Cheers - Jon
jonh (BOB member since 2002-12-18)