Qualifying Database Views

[goofaholix]

We have a number of reports based on Database Views.

As I was creating the Views the database administrator didn't want me to have dbo access so created a database user for me who had rights to create or modify database views in sql server.

We found that the Impromptu Catalog couldn't find these Views unless we qualified them with the owner name when they were added to the Catalog. This was ok, we could live with that.

Now we've realised that we probably shouldn't have used my UserID as the owner of the Views, but should have used a generic reportwriter UserId, as when I leave my UserID will cease to exist.

We've created a new UserId and changed the owenership of the Views to the new one. Trouble is that when we remove the Views from the Catalog and re-add them with the new owner the reports can't find them.

Looking in the report SQL I see that the View owner is hard coded everywhere in the reports, I assumed that the Catalog would manage the pathing of these kinds of things.

Any ideas how we can prompt the reports to recognise the Views with the new owner? or better still recognise the Views regardless of owner like they do for tables belonging to dbo?

Thanks.

Bruce

 

[griffindm]

goof,

You should be able to go into the catalog and rename the schema owner object to the new owner (after you recreate the views in a different, "generic" user id). I've done this with changing table ownerships in Oracle and it's worked fine.

Let me know if you need more info.

Dave G.


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[goofaholix]

You mean uise Schema Leveling mode?

I've given it a try for one report and it appears to hjave worked, so I'll go ahead and do the others.

Thanks.

 

[griffindm]

goof,

Actually I did not mean Schema Leveling mode. It may work though. What I meant was to go into the catalog, without a report open, and go to the Tables catalog menu option. Then select the schema ownership level only and click on Rename via the button at the bottom of the right hand table view screen.

Dave G.


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com