What form of a Datasource do you Use?

[biresg]

Just taking a survey to see what kind of datasources people are using for their transformer models?

I work for a very large company, and we used to use Impromptu (IQDs).
Since moving into Cognos 8, and now Cognos 10, we no longer use IQDs nor do anything with Impromptu.

Currently working to "re-write" all impromptu queries to report studio to use as our transformer datasources, but I wanted to check best practices, better tools to use in the re-writing efforts etc...

Any info would help

[MMcBride]

We continue to use IQD generated from Framework manager

We found Package and report based sources in transformer to be excessively slow compared to using the IQD's

For example: We took a Framework Manager Package published it to Cognos Connections and in Transformer using that Package as a datasource to build a cube.

Time to build cube 5 hours

Same Package - ZERO changes simply externalized the query as an IQD - used that IQD in an identical cube
Time to build 1 hour

Everything between the cubes is identical but the SQL when we externalize compared to publishing the package is off just enough to cause us a alot of problems.

We are running IBM DB2 9.7 UDB on AIX
Same tables for both.

After trying to convert half a dozen or so IQD's to package based we decided it simply wasn't worth the trouble...

These results were created when we went to 8.4 we have not tested them since moving to Cognos 10.1.1 it may be better now but with over 70 Cubes using IQD's we don't have the time to test them all...

[biresg]

Right now we have about half still IQD, and coverted some over in report studio.
We now pull report studio as the datasource into transformer for those.


I know drill thru reports from the cube to report studio is coming up soon, i just wanted to make sure we using best practices for datasources, so we dont screw anything up when trying to get accurate numbers in our drill thru reports.

[gonbravo]

hi ,

Very interesting topic.

i  have joined a small company..first they were doing simple reports using the package from frame work manager .

NOw the trend is DMR ..we are using impromptu to create iqd as the data source for transformer and use that cubes as a package and thn use them as datasource package for analysis studio or report studio.

i think this the practice going on every where...

@ Mr.biresg   your company's  practice  is different.we are about to change the version to transformer 10.

can u say me why u r using iqd to create report which ll b used as data source for transformer.( seems long process) .and y not u directly use the iqd as data source for the transformer..and wat u ll do with the generated cubes .(what are all the further process involves after the cube is generated )

[cognostechie]

It is suggested to use a Package but all Query Subjects should be seperate from each other and should be used as seperate queries and association should be made in Transfomrer with common field names. This also ensures proper rollup. The package that is used for reporting should not be used because that will already have lot of joins which will link the queries together resulting in a massive SQL which will cause more time taken to build the cube. This is probably what is causing the problem for MMcBride.

[MMcBride]

Actually Cognostechie we build "IQD Query Sibjects" and publish those to the repository - then use those as individual data sources within Transformer. To use these as an IQD we simply externalize them and call them via IQD instead of directly.

So while you would think the SQL is exactly the same - since both IQD and Package are using the exact same Query Subject the reality is they are not.

We opened a ticket with IBM and the issue is that Cognos delviers different SQL code when externilizing vs publishing the package.
The solution as per IBM is to externalize your queries - then copy the SQL found in the IQD file.
Create a new Query Subject and Paste the SQL from the IQD in, then publish this to Cognos Connections and use it to build your Transformer cubes.

This is not my solution mind you this is IBM's answer to why is the SQL different and one builds faster SQL.

My solution to this issue - is to just keep using IQD Files.

Since the push is to go DMR when possible and in AIX I am forced to use JDBC connections for everything I may revisit this and see if DMR and JDBC help resolve this issue, but if the SQL is slightly different when externalizing and is more efficient - then I will stay IQD's - even though it is a pain in the back side using IQD's on AIX with Transformer

[cognostechie]

I don't know why the SQL is different when it is externalised but in 8.4, I had a problem and I found the reason.

Transformer ws adding the word 'distinct' to the SQL after the query was externalised. So the SQL in FM was ok and the same SQL was seen in the package but it was Transformer that was adding the word 'distinct' on the top of the SQL. This was because in 8.4, there is a new property called 'auto-summarize' which gets turned on by default. This is the same property which we have in Report Studio. That property was adding the word distinct which was eleminating the duplicates and we needed to count duplicates in our case. By checking off that property, the problem was solved.