Any Alternatives to a Union Query in Report Studio

[damzI]

Hello Experts,
So I am creating a report in Report Studio using one table (with about 100 rows) from the database.
The report is an output of a union of 2 queries. I need to use one query to insert a dummy record. The report is a data source for a cube in transformer. The performance is really bad - it takes 30 minutes to read the data in Transformer ! Is there any alternative to a union in Report Studio. Or any other way to go about this ? Or maybe even to tune ithe existing query.
Thanks - d

[Michael75]

Hello,

<disclaimer> In this reply, I'm speaking from personal experience, and not trying to state general truths </disclaimer>

In the past, when feeding new Transformer cubes, I initially made extensive use of reports. I was aware that this was not too easy when it came to doing promotions i.e. Dev -> UAT -> Prod. Also, its use limited its understanding to those who could read a Cognos report. Long story short, after one inexplicable error which was posted here http://www.cognoise.com/index.php?topic=21845.0 but never resolved directly, I moved towards the solution of dropping reports as data sources, and creating new DB views, based on a simplified version of the generated SQL of the previously-used Cognos report, importing and modelling these in FM as if they were tables, and making a fully package-based TR cube.

Taking this approach happened to resolve the full outer join problem (see link above), and also made promotion of new versions through the different layers accessible to a much wider population. Personally, I wouldn't go back to using reports as a Transformer data source.

As to why your union report is taking so long . . . many factors could come into play. I suggest you enlist the help of your DBA to help to debug this.

There's one little-used Cognos FM function which you could try, and which avoids using a custom report - see Creating Query Sets (Ch. 5 of FM guide). I believe that this induces local processing, but on the data volumes you're talking about the effect should be imperceptible.

Alternatively, you could renounce minimised SQL and try a hard-coded union in your FM as suggested here: http://www-01.ibm.com/support/docview.wss?uid=swg21453737

I'm not sure that either of these solutions will directly solve your appalling response time, but trying them could help you find the correct solution.

HTH
Michael

[damzI]

Thanks Michael,
This is really good information. You have more than answered the questions I had.
- d