framework manager tables

[priyanka]

Our cognos project has oracle as a backend. I created reports using package in FM .But when I asked to DBA she is telling that table is not present in their database.Then what will be the problem?

[Lynn]

Our cognos project has oracle as a backend. I created reports using package in FM .But when I asked to DBA she is telling that table is not present in their database.Then what will be the problem?

What did you ask the DBA about? Are you getting an error? Unpredictable results? Some other problem?

Without really knowing what your problem is the only thing I can suggest is that you generate the native SQL from your report to show your DBA and also provide the login credentials that Cognos is using to connect to the database so that she might have better information for troubleshooting purposes. You should also know that modeling can be done against physical tables as well as using database views and stored procedures.

[priyanka]

Thanks for your help.I want to know this because I have to move cognos code from one region to another.I am not understanding as this table is present in FM should I ask to oracle DBA to create table which I want by giving required fields?

[priyanka]

And one more thing to add I am not getting errors while generating cognos reports.

[priyanka]

Can you please tell me how to generate native sql?

[Lynn]

Can you please tell me how to generate native sql?

There is an option in the Tools menu of report studio to generate SQL/MDX.

Thanks for your help.I want to know this because I have to move cognos code from one region to another.I am not understanding as this table is present in FM should I ask to oracle DBA to create table which I want by giving required fields?

Sorry, I still don't understand the problem. What does it mean to "move cognos code from one region to another"?

[priyanka]

Actually I our project, there are 4 regions Dev,UAT,stage and prod.First code is created in DEVi.e development environment and then it is moved to UAT i.e testing region ,after that it is moved to stage which is exactly similar to prod and then it is moved to production.Here my doubt is I developed 4 reports using one table in one Framework manager package.For our project ,backend is oracle database.All other tables in that package are found in that oracle database but table which I used for reports is not in oracle database as per DBA told.So should I ask to DBA to create that table?

[MFGF]

Actually I our project, there are 4 regions Dev,UAT,stage and prod.First code is created in DEVi.e development environment and then it is moved to UAT i.e testing region ,after that it is moved to stage which is exactly similar to prod and then it is moved to production.Here my doubt is I developed 4 reports using one table in one Framework manager package.For our project ,backend is oracle database.All other tables in that package are found in that oracle database but table which I used for reports is not in oracle database as per DBA told.So should I ask to DBA to create that table?

Hi,

That doesn't seem to make sense. If the table is not in your Oracle database, how were you able to model it in Framework Manager in the first instance? Are there different schemas in Oracle for Dev, UAT, Stage and Prod? Do the data source connections in these four Cognos instances point to different places/schemas?

When you take a moment to consider that Cognos will only report on data that exists already, it makes sense that

a) Either the table already exists and you are using it, or
b) The table exists in the schema you are pointing to currently but not in the schema you will end up pointing to after deploying to a different instance.

If you try to deploy to an instance where the table does not exist in your Oracle data source, your report will not work.

Cheers!

MF.

[Lynn]

Actually I our project, there are 4 regions Dev,UAT,stage and prod.First code is created in DEVi.e development environment and then it is moved to UAT i.e testing region ,after that it is moved to stage which is exactly similar to prod and then it is moved to production.Here my doubt is I developed 4 reports using one table in one Framework manager package.For our project ,backend is oracle database.All other tables in that package are found in that oracle database but table which I used for reports is not in oracle database as per DBA told.So should I ask to DBA to create that table?

In addition to the Muppet's comments:

You can't report on a table that doesn't exist. The database elements as well as the Framework Manager package needs to be in place in your target environment in order for your reports to run.

Typically a Cognos deployment archive would be created to migrate any packages (if they are new or changed) plus any new or changed reports. Before doing that, however, you would need to ensure that the database contains all the necessary views, tables, indexes, etc.

You said you aren't getting an error which is confusing -- your reports will absolutely fail if the database table isn't in the database. I suppose you mean they don't fail in your source environment.

[priyanka]

Thanks for all the help.I have generated the reports without failure.That means that table is in that database but may be in a view form.Is it possible?

[priyanka]

My doubt is what should I ask to DBA regarding that table because she is telling that table is not present but reports are generated.

[Lynn]

Thanks for all the help.I have generated the reports without failure.That means that table is in that database but may be in a view form.Is it possible?

My doubt is what should I ask to DBA regarding that table because she is telling that table is not present but reports are generated.

It could be a view or you could be that you are each talking about different databases. If you give her the native SQL and the connection information for the data source defined in the package your successful report is using she ought to be able to figure it out. The connection information would be found on the administration console.

[bus_pass_man]

You don't say where your report is running without errors.  In your dev environment?  In the production environment? Others?  All?  I'm assuming that you've just run it in your dev and, possibly, test environments but not your production environment.  Is that a correct understanding?

So the problem seems to be you've created a report using an object which you're not sure exists in your production environment.  Is that a correct understanding?

One thing that might be helpful to you would be to try to track down the location of the table in question.  You would then use the content manager data source information to determine the actual data base schema where the table exists.  Once you know that you can determine if the table (and I am using that as shorthand for not just tables but views and synonyms as well) exists in all of your environments, most importantly the production environment.

You have several ways of going about that. 

One would be to, in report studio, select an object which you believe is using an object from the table and choose lineage from the context menu.  Then go to the lineage diagram and trace the object.  When you select something in the lineage diagram you can see its expression in the properties.   By being able to trace the lineage of the object you can track down the original data base query subject.  This technique would be useful as it would enable you, by having performed the action in all your environments, to identify if the object reference differs.

Another would be to do something similar in FM, but it is a bit more time consuming.  You would open the object which you have used to create the report, identify its object references and open those objects and trace their object references until you get to the base data base objects.

Your dev environment does not need to have the entire set of objects in it but it would be helpful that models developed in it do not make use of objects which don't exist in upstream environments.

If it turns out that your dev environment has a different database object you can use remapping to remap your business layer object to the other database object but that would mean that you would need to actually publish the package in your production environment.  This would be something would do in extremis.