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
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 (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 (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
Thanks Michael,
This is really good information. You have more than answered the questions I had.
- d