Questions on ROLUP - dimensionaly modeled relational data/AS v8

[seweryng]

Hi,

I would like to share some experiences and hear some opinions about ROLAP by Cognos v8.

Just to give a little bit background our recent client is big telco with max 60mlns usage rows/day. Client has strict policy restricting any physicalization of data outside data warehouse unless there is no other choice. Cognos cubes are also treated as physicalisation. Therefore preferred/enforced option is to develop virtual cubes to provide OLAP capability. Our database is taradata.

Couple of challenges we have met so far:

1. The only way to provide OLAP through Cognos connection in v8.3 is Analysis Studio. Upgrade to 8.4 is out of question in our timelines due to need to regression test everything we have so far in.

The problem is that the user community got used to Power Play and they simply rejecting Analysis Studio. Has anybody got similar issue? How to deal with that?

2. ROLAP looks messy due to the problems with sorting of categories in dimensions.

In order to sort in version 8.3 we had to change query subjects sql to pass-through. This is the only way we know to include "order by" clause in query subjects sql. This has however direct impact to performance.
We captured and analyzed sql going to database for many different scenarios. Basically, "pass-through" automatically splits sql into separate islands. Dimensions are retrieved separately and fact separately at lowest possible level of granularity. Such data is then aggregated at Cognos server. With relatively small subset of data (couple of millions of rows) this extends response time unacceptably.
We checked also version 8.4 and sorting embedded there seems to work without problems. All processing happens in database.

3. Count distinct type of measures work ok only until certain level of report complication.

Up to 3 columns of nesting it sends 1 query (full outer joined for every level of summary but that's ok). With 4th column it tries again to import fact data at ID level and aggregate at Cognos application. Means main query lacks of “group by” clause.
Applying of determinants for dimensions is just putting “group by” only in little pieces of sql related to those dimensions. Such pieces are sent separately anyway.

This, as far as we know does not seem to be addressed in v8.4 (maybe we are doing something wrong here? Anybody had similar problems)

4. Custom aggregation for one dimension (like first/last, which would correspond to time rollup in transformer) affects performance.

Again, the same situation. Application of such custom aggregation changes processing. Queries are not grouped in database level. We tried all this with all possible database and rollup processing options.

5. Assuming we are careful and avoid to do anything that would move aggregation to application server the performance looks relatively ok.
Means for relatively small area with just 100k of transactions response time for 1-2 dimensions is less then 15secs. Up to 1 minute for more complex . This may still be showstopper for some users. In majority of cases I reckon aggregation tables in database would handle the problem (does not apply to count distinct).

6. Relative time categories are not out of the box functionality. But there are some good options to develop them anyway, it is just not that easy.

I would be interested to know any opinions on stability of ROLUP in v8.4.
Also, is Power Play studio going to return for good? What are the plans for that?