organizing database layer for big project

[ry1633]

Hi all,

Now that I've made it all the way through a Cognos project (with help from this forum which I am very grateful for), I have a chance to relax and look to see if there's anything I could do different or better the next time.

Specifically, I'm wondering if any of you have any good tips for organizing the Database layer for a large system.  The one I just came from had more than 250 tables and it was tough at times.  Is there anything I can do to help organize the db layer in such a setting where you have several hundred tables?

[Kiran P]

Hi,

You should be able to group them into folders by functional area. That way finding tables will be easier. If you have views, stored procedures or user defined functions also in your data layer you can group them by type.

In our case we have nearly 2000 tables in our data layer plus the table shortcuts. We group them by sub modules of our application and within each module folder we group shortcuts into one folder, model QS into one and database QS into yet another.

This is a very open question and there can be multiple ways of organising the objects. All depends on what is easy and good in your scenario.

Thanks
Kiran

[bdbits]

I agree that folders can help a lot in organizing any of your layers. Even in large projects, you often find sets of tables that are closely related, even if relationships extend outside these areas. Beyond that I would try to keep the database layer very simple, just "select *" and relationships as much as possible. If it is complicated, you may want to use folders within folders as kpassumarthi talked about, though I find more use for that in the business layer than the database layer myself.

[ry1633]

thanks for those - some good advice to try.   Had another related question...  in the Metadata wizard when I was setting up the first project.  I could never see the actual tables.   The wizard had a folder called "Tables" in the datasource but it was empty.  So I had my dba create views for each table and I used those instead.  Should I be able to see that actual tables from the Metadata wizard?

[bus_pass_man]

Yes, subject to whatever restrictions in the data base itself given to the identity used in the data source.  FM asks the data base to tell it what it can see and if the data base thinks that FM shouldn't see it then it won't be given to FM.  The problem is with the data base itself or with the connection information given by the DBA to set up the data source connection, not with FM.

I was wondering why you were so keen on using views.   

[ry1633]

I'll have to check with my dba, there may be some security in place that I couldn't get around.   Something to think about for the next project.  I'm gonna leave things as-is in my current project.

[ry1633]

My db says I have the rights that I should have and thinks it's a Cognos issue why I can't see the table.  Can't imagine what else it would.  Supposedly I have the correct grant perms in Oracle.

[bus_pass_man]

Hmmm....

Using the connection information that you used to set up the data source connection, can you see the tables using things like squirrel?

[ry1633]

never used squirrel before.  what is it?   I can see the tables when logged into the datasource from Oracle Toad.

[bus_pass_man]

Along the same general lines, I think.

https://en.wikipedia.org/wiki/SQuirreL_SQL_Client

Are you using JDBC or OCI?

[bdbits]

I don't think it would matter JDBC vs OCI. FM is likely reading system catalogs, and permission is permission once it gets to the database.

Are you using the same credentials in Toad that you are in FM? If so, that should be sufficient from the database side and there is some other issue.

[ry1633]

I'm using Oracle TNS connections, and no I do not use the same credentials to login to FM and Toad.

[bus_pass_man]

I put it to you that you should be open to the possibility that it would be worthwhile to consider as a potentially fruitful avenue of troubleshooting the controlling of that particular variable.

[ry1633]

Yeah, I will look into it.  My dba says all my grants and perms on the Oracle side are as they should be, and so I *should* be seeing the actual tables in Cognos.   So there must be some issue on the Cognos admin side, and I don't control that piece, so I'll have to check that also.

[bus_pass_man]

Did it work?

[ry1633]

nope - not yet.  All of my permissions/grants on the Oracle side are as they should be.  I have all the same rights as the other folks in my section.  And my Cognos admin (different person) still says there's nothing on the Cognos end that would cause me to not see the tables.

[Lynn]

I'm using Oracle TNS connections, and no I do not use the same credentials to login to FM and Toad.

Have you tested access in Toad using the same credentials as you use for FM?