Cube Build Using Report as a datasource

[twlarsen]

Has anyone started using Cognos Reports as datasources for their cubes?

I think I'm coming into trouble when a report has multiple queries involved in it.  For example, I have a report that takes about 2 mins to run but has multiple queries behind it.  I can add it as a datasource and see all the columns but when I actually try to build the query its taking over 6 hours before I canceled it.  It only has 55k records involved so its not a huge cube.

To get around it, I exported it as a csv and it only took 30 seconds to build the cube.  So altogether it took about 2mins and 30 seconds to build the cube going the csv route, but if I used the Cognos Report as the datasource its taking 6+ hours.

I have used reports that used a single query, the report itself takes about 5 mins to run, but the cube takes 40 mins to read all the records.  So I know that it works.

Anyone have thoughts on this issue?

Thanks,

Todd

[josepherwin]

Todd,

I've build report of cube.

One thing that you have to know that, the syntax that it uses is different to those of normal Report Studio syntax. It actually uses the MDX language.

You can try the following alternative:
1. Try to generate the same report by using Analysis Studio
2. Once you are happy with the value being displayed in Analysis Studio, under File, go to "Open in Report Studio"

In Report Studio, you will be able to see on the query that it uses to build the report.

Next thing is you just need to simplify the query in Report Studio, because when you open an AS report in RS, it will be a little bit complex and displaying unnecessary query, but sorting it out is not rocket science.

Let me know if performance is still an issue when running the report.

[twlarsen]

Hey Joseph,

Thanks for the reply, but what I'm actually doing is building the cube using a cognos report as a datasource.

I ended up having a DBA create a view, and then putting that view into the SQL of a cognos report.  Using that report, my cubes were able to build.  Hopefully that helps someone else if they get in the same pickle.

Todd