SQL usage in Report Studio beyond packages

[PolzovatelCognos]

Has anyone encountered the following scenario, where the report authors are utilizing the published/available packages to report beyond the provided package data elements via the usage of SQL (i.e., pass-through SQL) in Report Studio? To elaborate, the report authors are even reporting beyond the package's data source, by choosing from the list of published data sources (based on their role/permissions) and then slinging straight SQL against those sources. This is a concern on a couple of levels. On one level, as an administrator, there is a maintenance concern. Report Authors have discovered this ability in our environment and are no longer willing to use the published packages. Hence, although reports may appear to be reporting against (pointing to), let's say, a published Operational Data Store Financial package, in reality, the report goes against a completely different data source (i.e., a production Student data source). This type of unconventional usage of the published Cognos packages has also caused us to re-look our Cognos/data source security model. Can anyone share their experiences/thoughts in regards to such a scenario?  Or perhaps of a way to globally restrict the mechanism of choosing other data sources beyond the FM package. According to IBM Cognos support, this is a provided feature and cannot be disabled?

[Lynn]

Sounds to me like you've got a group of naughty monkeys running loose! I suppose simply telling people this isn't allowed would be ineffective  :o

I don't know if you'll find a way to prevent this since they would have access to any sources their permissions allow even if their common sense appears to be out of reach. I don't do much in the way of administration so hopefully someone will correct me if I'm wrong on this.

What comes to my mind first is that using SQL is MUCH more difficult than building reports from a package .... unless your database and Framework Manager models are not very good. In fact I'd almost suggest they must be really bad if authors are bypassing them altogether. Maybe reevaluating the suitability of these very important foundation elements would help move things in a better direction?

For a brute force suggestion I would ask how reports get promoted to production. At many of my clients there are documentation requirements and code review before ANYTHING gets the green light to migrate. In some cases the ability to do RS authoring in production is locked down.

If the powers that be agree the SQL approach is fraught with peril then lock down production and implement standards and guidelines required for migration. Then they can write as many stupid reports as they like but they'll never pass muster to get past the dev environment.

[sthornburg]

Lynn raises some excellent points about possible root-causes for users constructing their own SQL.  I also agree with her that simply telling users "don't do this" when permissions allow them to do so is probably not going to work.

So while the underlying issue is analyzed, here's a technical solution that should at least prevent the users from writing their own SQL in Report Studio.

In Cognos Connection Administration, Security tab, change the Capabilities of the relevent security group(s) for the following:

   Report Studio --> User-defined SQL

Remove the following permissions:   Execute, Traverse
Make sure that the Everyone group does not have permissions, of if you do need this for some reason (not best practice!), then specifically deny the authors' group these permissions.

This should solve both problems -- hand-written SQL and use of alternative data sources (in Report Studio, at least).


Hope this helps,

Scott

[Lynn]

Oh it's a capability!! Wonderful and simple!!! Thank you Scott since it didn't even occur to me to check that  :o

[PRIT AMRIT]

Nice one Scott.

If anyone is interested, have attached the screenshot of the result.

Thanks
Prit

[PolzovatelCognos]

Thank you all for your responses. Scott, we actually found this ability to restrict/disable the "User-Defined SQL" in Report Studio a couple of weeks ago by just "rummaging" through the complete Administration UI. :) When we learned of this, we reported it back to IBM Cognos, since we were told that there is "no" ability to disable this (and we learned there actually is). However, those users that had the opportunity to delve into this functionality are obviously very much in favor of it, since they are no longer dependent on the published models. Lynn, your comment is actually very true:
"What comes to my mind first is that using SQL is MUCH more difficult than building reports from a package .... unless your database and Framework Manager models are not very good. In fact I'd almost suggest they must be really bad if authors are bypassing them altogether."   

You are right on target there. :) Our models are truly out-dated (especially since we have had a long history of Cognos at our organization) and are not meeting everyone's reporting needs (reporting needs have changed and grown). This is exactly why we were pushing for an endeavor to begin meeting with each functional group so as to gather their reporting requirements/specifications and begin the endeavor of creating new models that would also follow Reporting best practices. However, with this new discovery, users are now commenting: "well, we can save time now and not worry about re-creating the models any more." "Why create models, if we can just query straight against the data source and get access to anything the database account gives us access to." As an administrator, however, this is obviously concerning. First of all, all of this has to be supported. Data lineage and data impact analysis becomes more difficult. Maintenance becomes more difficult. When new Report Authors (we have a reporting group, with authors defined for each functional area) come into the picture, this methodology will require for them to "know" the data/database schema. I can keep on commenting and commenting as to why I am personally not completely thrilled in moving into this direction...  :)

[Lynn]

Oh yikes!  :o

Volumes could be written on why that is a dangerous road to travel.

If you end up getting stuck with that approach you might try to at least fight for some VERY stringent documentation requirements along with peer review and enforced redundancy of author resources (have a primary and backup author familiar with the report who can be responsible for fixes/changes).

Still a bad idea, but remember the old saying:

When life gives you lemons.....


....throw them at somebody :D

[guthikv]

Hi all,

I was actually wondering if all the users can run a report based of the SQL, if the changes are made.

Please confirm so I can make the changes accordingly.

Thanks