We have built reports in Report Studio and use Tabular SQL for writing our queries. Our organization is having trouble with the COGNOS environment, and they are blaming that instability on our use of Tabular SQL. They have told us that COGNOS does NOT support Tabular SQL. We are having trouble believing this, wondering why they would include it in Report Studio if they didn't support it. I'm only looking for a yes or no ( hopefully someone out there knows ) so I can start somewhere. THANKS Katie!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Is Tabular SQL supported by COGNOS
- Thread starter rigged
- Start date
Hi Katie,
The ability to add Tabular SQL is in itself supported (so for example if you were trying to add Tabular SQL and were unable to, they would probably look into it), but the issue is that you are potentially overriding the SQL that would be generated by the Cognos Query Engine with something that may provide inaccurate or inconsistent results. Cognos support will probably not be able to accept a testcase from you if you are not retrieving the correct results from your reports as it's really down to how the SQL is coded rather than what the Cognos software is doing to generate it for you. The end result is that you are much less likely to get useful support for reports that have hard-coded SQL.
Is there a reason why you are overriding the SQL routinely like this? It severely limits the ability to easily modify reports later on and potentially prevents some of the neat inherent SQL functionality being used (such as stitch queries).
Best regards,
MF.
The ability to add Tabular SQL is in itself supported (so for example if you were trying to add Tabular SQL and were unable to, they would probably look into it), but the issue is that you are potentially overriding the SQL that would be generated by the Cognos Query Engine with something that may provide inaccurate or inconsistent results. Cognos support will probably not be able to accept a testcase from you if you are not retrieving the correct results from your reports as it's really down to how the SQL is coded rather than what the Cognos software is doing to generate it for you. The end result is that you are much less likely to get useful support for reports that have hard-coded SQL.
Is there a reason why you are overriding the SQL routinely like this? It severely limits the ability to easily modify reports later on and potentially prevents some of the neat inherent SQL functionality being used (such as stitch queries).
Best regards,
MF.
- Thread starter
- #3
Thank you for your response, first and foremost!
The reason we are using the Tabular SQL is because we were basically in a time crunch. We didn't have a 'proper' data warehouse, with de-normalized data, but we had to have the reports done anyway, so we wrote the SQL ourselves, hitting our production database of over 750 tables.
We aren't 'overriding' the SQL. We are building our own SQL and adding it to the report by dropping in a tabular sql object and pasting our SQL there. The reports ( 149 of them, and counting ) retrieve accurate and consistent results.
From a tool perspective, from an environment perspective, from a reporting perspective, is there a problem/support issue with us using the Tabular SQL object?
Thanks Again!
Katie.
The reason we are using the Tabular SQL is because we were basically in a time crunch. We didn't have a 'proper' data warehouse, with de-normalized data, but we had to have the reports done anyway, so we wrote the SQL ourselves, hitting our production database of over 750 tables.
We aren't 'overriding' the SQL. We are building our own SQL and adding it to the report by dropping in a tabular sql object and pasting our SQL there. The reports ( 149 of them, and counting ) retrieve accurate and consistent results.
From a tool perspective, from an environment perspective, from a reporting perspective, is there a problem/support issue with us using the Tabular SQL object?
Thanks Again!
Katie.
From a modelling perspective , wouldn't it be better to use database views and use them within the Cognos products?
I am perhaps missing the exact definition of 'tabular SQL', but I assume it is comparable to 'free-hand SQL' as known with BO. (bypassing the semantic layer of catalog/framework/universe and sending 'straight' SQL to the database)
Ties Blom
I am perhaps missing the exact definition of 'tabular SQL', but I assume it is comparable to 'free-hand SQL' as known with BO. (bypassing the semantic layer of catalog/framework/universe and sending 'straight' SQL to the database)
Ties Blom
Hi,
I understand what you are saying about time pressures, but I honestly believe this approach is storing up lots of trouble for you later on. You do not need to have data in a conformed-dimension star-schema presentation layer to get accurate results from ReportNet. You can model your normalized data in Framework Manager to make it appear as though it is structured in star schemas, then write your reports from packages based on this model and get accurate, consistent results without having to resort to coding your own SQL. When I refer to "overriding" the SQL, what I mean is that you are coding your own SQL query rather than allowing the Cognos Query Engine to code the query based on your report specification. The Query Engine has lots of clever inbuilt functionality that will allow it to automatically group and summarize data, code queries to retrieve data held at different levels of granularity without double-counting, retrieve data from across multiple data sources concurrently etc etc. If you code your own queries you are bypassing all of this and losing many of the neat features built into the product. You are also making it extremely tricky to code your reports in the first place and to modify reports later on, as you cannot just drag in new data items onto your report page, you must first go and modify your tabular SQL code, then bring the items up into the query, then finally bring them onto the page.
As I said earlier, you can code tabular SQL, but Cognos support will be far less likely to assist you if you believe your reports are returning wrong/inconsistent results. What may seem like a quick-win now may come back to haunt you later!
Best regards,
MF.
I understand what you are saying about time pressures, but I honestly believe this approach is storing up lots of trouble for you later on. You do not need to have data in a conformed-dimension star-schema presentation layer to get accurate results from ReportNet. You can model your normalized data in Framework Manager to make it appear as though it is structured in star schemas, then write your reports from packages based on this model and get accurate, consistent results without having to resort to coding your own SQL. When I refer to "overriding" the SQL, what I mean is that you are coding your own SQL query rather than allowing the Cognos Query Engine to code the query based on your report specification. The Query Engine has lots of clever inbuilt functionality that will allow it to automatically group and summarize data, code queries to retrieve data held at different levels of granularity without double-counting, retrieve data from across multiple data sources concurrently etc etc. If you code your own queries you are bypassing all of this and losing many of the neat features built into the product. You are also making it extremely tricky to code your reports in the first place and to modify reports later on, as you cannot just drag in new data items onto your report page, you must first go and modify your tabular SQL code, then bring the items up into the query, then finally bring them onto the page.
As I said earlier, you can code tabular SQL, but Cognos support will be far less likely to assist you if you believe your reports are returning wrong/inconsistent results. What may seem like a quick-win now may come back to haunt you later!
Best regards,
MF.
Hi,
Although I am new to the scene, we have a similiar situation. Not all our medical databases are within in COGNOS. We have many hospitals with their own SQL server & Oracle datbases tied to various applications. As a result, we have created stored procedures in those databases then we just 'pass-through' Framework Manager to make the result sets available for writing COGNOS reports.
We were told by a COGNOS instructor that if your database does NOT reside within the COGNOS environment that the most efficient solution in returning data was to create stored procs in your outside databases and then import them into COGNOS as query subjects rather than adding another layer and building the relationships within Framework. If you don't, it will slow the building of the result set before the data becomes available to the report engine. The instructor had been a DBA for many years prior to joining the COGNOS fold.
Rich
Although I am new to the scene, we have a similiar situation. Not all our medical databases are within in COGNOS. We have many hospitals with their own SQL server & Oracle datbases tied to various applications. As a result, we have created stored procedures in those databases then we just 'pass-through' Framework Manager to make the result sets available for writing COGNOS reports.
We were told by a COGNOS instructor that if your database does NOT reside within the COGNOS environment that the most efficient solution in returning data was to create stored procs in your outside databases and then import them into COGNOS as query subjects rather than adding another layer and building the relationships within Framework. If you don't, it will slow the building of the result set before the data becomes available to the report engine. The instructor had been a DBA for many years prior to joining the COGNOS fold.
Rich
misscognos
Programmer
Katie,
As pointed out, when cognos support say it is not supported, they mean they will not look into it for you (for example, debug it or look for inefficiences etc) or be held responsible for the consequences of it. Once you decide to write your own sql, you are on your own. Cognos is not responsible for it. Cognos provides the functionality to write your own sql and therefore it is supported by the software. Do you see the distinction? The former point here is what cognos probably meant when they said that it is not supported.
It would be well worth your time to invest a little in a proper model.
when you say instability, can you describe the type of behaviour you are seeing? if a report never runs, then boom.. the sql is not written correctly. if there is something else, please describe..
As pointed out, when cognos support say it is not supported, they mean they will not look into it for you (for example, debug it or look for inefficiences etc) or be held responsible for the consequences of it. Once you decide to write your own sql, you are on your own. Cognos is not responsible for it. Cognos provides the functionality to write your own sql and therefore it is supported by the software. Do you see the distinction? The former point here is what cognos probably meant when they said that it is not supported.
It would be well worth your time to invest a little in a proper model.
when you say instability, can you describe the type of behaviour you are seeing? if a report never runs, then boom.. the sql is not written correctly. if there is something else, please describe..
- Thread starter
- #8
This is the real issue..
We are experiencing an issue that is preventing us from building or updating a report in Report Studio that uses tabular SQL.
The issue:
If a developer is developing a report in Report Studio using tabular SQL, and that developer has only one datasource available, everything is fine. The developer can enter/update SQL statements and the report runs without error. Initially, access to one datasource was how most developers were set up.
However, if a developer is developing a report in Report Studio using tabular SQL and that developer has access to multiple datasources, problems arise. SQL statements can be entered or updated, but the data items will not be populated in the query view and therefore not available to be used in the report. This prevents the report from being validated, and prevents the developer from fixing an existing report or building a new report. Currently, most developers have more than one datasource connection and are unable to fix existing reports.
This is a major problem.
I apologize, I should have just said all this in the beginning. The SQL as written and dropped in the Tabular SQL object is valid, it runs in any database tool ( ie:TOAD ) and returns correct results. However, now some tables have been changed, fields have been moved, and I need to change the SQL in the report to reflect that. If I only have one datasource defined ( as we did in the beginning of the project ) I can and did change things. Since more datasources have been established, I can no longer make changes to any reports, no matter what datasource I choose to connect to. Our support says that it's the Tabular SQL, and since Cognos doesn't support Tabular SQL, they won't look into it, or log an issue with them. Hence, my frustration.
We are experiencing an issue that is preventing us from building or updating a report in Report Studio that uses tabular SQL.
The issue:
If a developer is developing a report in Report Studio using tabular SQL, and that developer has only one datasource available, everything is fine. The developer can enter/update SQL statements and the report runs without error. Initially, access to one datasource was how most developers were set up.
However, if a developer is developing a report in Report Studio using tabular SQL and that developer has access to multiple datasources, problems arise. SQL statements can be entered or updated, but the data items will not be populated in the query view and therefore not available to be used in the report. This prevents the report from being validated, and prevents the developer from fixing an existing report or building a new report. Currently, most developers have more than one datasource connection and are unable to fix existing reports.
This is a major problem.
I apologize, I should have just said all this in the beginning. The SQL as written and dropped in the Tabular SQL object is valid, it runs in any database tool ( ie:TOAD ) and returns correct results. However, now some tables have been changed, fields have been moved, and I need to change the SQL in the report to reflect that. If I only have one datasource defined ( as we did in the beginning of the project ) I can and did change things. Since more datasources have been established, I can no longer make changes to any reports, no matter what datasource I choose to connect to. Our support says that it's the Tabular SQL, and since Cognos doesn't support Tabular SQL, they won't look into it, or log an issue with them. Hence, my frustration.
Hi,
Cognos will support the mechanism of using Tabular SQL objects in a report if it's broken. If the mechanism is not working as expected, so there's definitely a case to log this with them.
First question (just to make sure - please don't be offended!) have you definitely selected the appropriate data source connection in the properties of the Tabular SQL object? Look at the 'Connection' property - is it set?
Just to clarify - a Tabular SQL object can only be written against one data source, so if your data is coming from more that one source you will need to code multiple Tabular SQL objects (one for each data source, with the relevant source defined in the 'Connection' property) as children of a Tabular Model and join the Tabular SQL objects by coding a filter in the Tabular Model that links the relevant Tabular SQL items together.
From what you're describing, I don't think this is your situation, though - you are saying that each report has a Tabular SQL object retrieving data from a single data source, but if a developer has been granted access to more than one data source, they cannot verify or obtain a result set from the Tabular SQL (even with the appropriate connection defined in the properties of the Tabular SQL object)? If this is the case, then it sounds like this needs to be logged with Cognos.
In the meantime, here's something else you could try as an experiment:
Build a new report with a Tabular Model as the child of the query, and a Tabular SQL object as the child of the Tabular Model. Code your Tabular SQL and see if the result set is visible from within the Tabular Model. If it is, then bring the items from the result set into the Tabular Model and see if the result set of the Tabular Model can is visible from within the query. If it is, then bring the items into the query. This should help determine whether the problem is with Tabular SQL objects generally, or whether it's to do with Tabular SQL objects defined directly as children of queries.
Let us know what you find.
Best regards,
MF.
Cognos will support the mechanism of using Tabular SQL objects in a report if it's broken. If the mechanism is not working as expected, so there's definitely a case to log this with them.
First question (just to make sure - please don't be offended!) have you definitely selected the appropriate data source connection in the properties of the Tabular SQL object? Look at the 'Connection' property - is it set?
Just to clarify - a Tabular SQL object can only be written against one data source, so if your data is coming from more that one source you will need to code multiple Tabular SQL objects (one for each data source, with the relevant source defined in the 'Connection' property) as children of a Tabular Model and join the Tabular SQL objects by coding a filter in the Tabular Model that links the relevant Tabular SQL items together.
From what you're describing, I don't think this is your situation, though - you are saying that each report has a Tabular SQL object retrieving data from a single data source, but if a developer has been granted access to more than one data source, they cannot verify or obtain a result set from the Tabular SQL (even with the appropriate connection defined in the properties of the Tabular SQL object)? If this is the case, then it sounds like this needs to be logged with Cognos.
In the meantime, here's something else you could try as an experiment:
Build a new report with a Tabular Model as the child of the query, and a Tabular SQL object as the child of the Tabular Model. Code your Tabular SQL and see if the result set is visible from within the Tabular Model. If it is, then bring the items from the result set into the Tabular Model and see if the result set of the Tabular Model can is visible from within the query. If it is, then bring the items into the query. This should help determine whether the problem is with Tabular SQL objects generally, or whether it's to do with Tabular SQL objects defined directly as children of queries.
Let us know what you find.
Best regards,
MF.
- Status
Similar threads
- Locked
- Question
- Locked
- Question