using SQL in Framework Manager

[ry1633]

Hi all,

Is it possible to use SQL in Framework Manager - for instance to create a Query Subject instead of just using the wizard or dragging-and-dropping items.     The training that I've been through and most of the things I've studied on FM have been mostly silent on this issue, so I figured it might not be possible.   But I wanted to ask the group in case anyone here has tried it and had any success.

-R

[bdbits]

A query subject is a query subject, no matter what layer it is in, so yes you can make a query subject from SQL just like you do in the database layer.

Mind you, there should be a really good reason to do so. It will prevent Cognos being able to optimize SQL generation when the query subject is used. And since it should be the exception to the rule in your data model, someone trying to update for a change in the underlying tables might not think to look in a business layer. I would probably put SQL-based query subjects into a folder, which will highlight it for modelers without affecting presentation naming.

I have on rare occasions done this for particularly difficult reporting, but make sure it is really necessary.

[Lynn]

There is discussion about using SQL for query subjects in chapter 5 of the user guide.

I've only ever done it in the database physical layer and only for the purpose of prototyping something before requesting that a physical table (or view) be created in the database. This helps ensure that the design is going to satisfy the reporting requirements and makes it possible to demonstrate for end users for their approval.

It also helps clearly articulate the request to those responsible for creating it in the database. Once the object exists in the database the hard coded SQL query subject would be removed and replaced with a data source query subject referencing the created object. In an ideal world there would be collaboration with the DBA resources who would simply create a view for this same purpose and tweak if needed before finalizing, but the world isn't always ideal.

As bdbits advised, I wouldn't go there unless it is absolutely necessary.

[Ricardo Julio "Ricky" Villa]

Avoid creating SQL Query Subjects in FM Model as much as possible.

Create a view or table in the database.  Or sweet talk a DBA SQL monkey to do it for you ;)

Ricky - COYS