Derived Tables - Empty of code

[system]

I think I may have seen some other posts of this sort but with no fixes thus far.

I am using 6.5, full client, using Teradata.

I create a derived table and build a report that uses it successfully. If that report is run elsewhere then the SQL excludes the code of the derived table.

eg in simple form I get

SELECT
Table__13.custs
FROM
( ) Table__13

ie nothing in the brackets.

If I try to build a report on the second PC I can see the Derived Table object and can add them to a report but get the same code (or lack thereof!!)

Any idea as to where to look or are derived tables only available to the guy who derived them

Thanks - Jon

---

jonh (BOB member since 2002-12-18)

[system]

Wow!

Are you using a generic connection string for the universe?

---

Steve Krandel (BOB member since 2002-06-25)

[system]

Steve, Thanks for the response.

I’m not too sure what you mean by a ‘generic connection string’. All users connect through the same connection (odbc) if that’s what you mean. I had wondered if it might be some kind of access rights but the SQL in the DT is accessing a standard database that all the users have access to.

Thanks - Jon

---

jonh (BOB member since 2002-12-18)

[system]

Any idea as to where to look or are derived tables only available to the guy who derived them  

Surely not

1. What is the exact SQL code of your derived table?

2. Try shortening all referenced table names in your derived table and the name of the derived table itself.

3. Create a small derived table for testing. Does this one work for other users?

4. What happens if you loggin at another workstation using your Business Objects user ID? Does the derived table work then?

5. Double-check that the other PCs have exactly the same database middleware installation and configuration compared to your workstation.

---

Andreas (BOB member since 2002-06-20)

[system]

Have you posted your universe to the repository?

Cheers
Scruffy

---

scruffy (BOB member since 2005-01-19)

[system]

Thanks for the input everybody.

a) Created a simple DT called Months with SQL

sel month_name
from month_names

b) Saved Universe and on the same PC built a report displaying the month_name. SQL created was

SELECT
months.month_name
FROM
( sel month_name
from month_names) months

c) Export Universe. Go to other PC, log in with same user as originally and create same report ( I can see the new DT and the month_name object within) . This time the code is

SELECT
months.month_name
FROM
( ) months

d) PCs are using the same odbc connection with the same Teradata driver, same BO V 6.5.1

e) Looks like I am not alone as this link Derived Tables Problem
shows a similar problem reported recently.

f) This one has got me flumoxed - roll on the weekend!

Thanks for any and all assistance.

Jon

---

jonh (BOB member since 2002-12-18)

[system]

Just also noticed that if I look at the Universe on a second PC I can see the object and class but if I try to edit ithe DT code it is blank. Its as if the code is only available on the originating PC

---

jonh (BOB member since 2002-12-18)

[system]

Not sure if this applies to Teradata, but are the table(s) in the DT fully qualified with the owner or schema name such as owner.table.column.

Would this make a difference in your case?

---

Irwin Miller (BOB member since 2002-08-16)

[system]

Thanks Irwin,

But no, fully qualifying the query doesn’t make any difference.

Still stuck on this one.

Jon

---

jonh (BOB member since 2002-12-18)

[system]

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 – don’t ask me how but I’d 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 don’t 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!

I’d 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)

[system]

Hi,

just one extra note: we had this same problem with the integrity and never noticed it until we created derived tables…and some users had problems using them…
I looked in the database, but these magic tables were in…then I looked again several days later and noticed that they were created using a different user account which meant that some users could access them…and some not…
So even when the tables exist, look at the permissions.

But this topic gave me the hint I needed!

Regards,

Gerard

---

highandstoned (BOB member since 2005-08-01)