Cognos Transformer Performance benchmarks

[bloggerman]

Could someone help with the below and help me understand transformer better.

1) How much  Cube build time do you think is a lot and needs to be handled and reduced.

2) What Cube Size, in terms of number of rows and mb,gb size, is normal and what is huge.

3) Is the size of the source table that is used for cube building a concern? I had a fact table of size 3 million once that would cause the cube build to error out citing time out.

[Arsenal]

You're asking very general questions and IMO, there's no set yes or no answers to your question. For example, you might consider 5 hours for a cube build too long, but it is normal in my case simply because of the data involved i.e. 30 million records. But suppose you have just 100,000 rows then 5 hours may be too long depending upon how many dimensions and levels you have.


Similarly, cube size can be a few hundred MB, 1 GB, 6 GB or 20 GB. It all depends upon how many records your database has.

The size of the source table should not be causing the timeout errors. Did you explore to see the query timeout setting for the DB or for the Cognos layer? Alternatively, did you have a very long winded calculation that would exceed normal timeout settings?

[bloggerman]

Thanks Arsenal. I figured its to generic, but, in part I am trying to gauge size vs time...For, e.g. your note that 5 hrs for 30 million and less than an hour for 100k is good.

Could you also suggest something size for them...like 30 million would be xyzGB or MB....100k would be .....

What would be for 2 million say?

To what extend would number of dimensions impact it all.

Thanks again

[IceTea]

The total number of DB record does affect the build time, but not mainly the resulting size of a cube.

Example:
If you have only two dimensions "gender" and "already_dead_YN" and one fact for the resulting cube size it doesn't matter if your DB has 1000 records or 100.000.000.000...

The size is the outcome of the number of dimensions, hierarchies, elements within the hierarchies and facts. The more of them you have in your model the bigger the cube will be, supposed most of the intersections are populated.

Greetz :o

[bloggerman]

Thanks....

Would it be possible for you guys to list some stats for the below from your previous/current efforts

Cube size
Build time
Number of Categories
Number of Dimensions
Number of Measures

Few examples would be good.

[cognostechie]

3 million in the fact table is pretty small data.

Cognos recommends 50 million as the limit but I have had cubes with 7 dimensions and 62 million rows from fact table (SQL Server) build in 3.5 hrs. I consider that to be good enough. It was taking 5.5 hrs which I managed to reduce to 3.5 hrs by breaking the data sources into seperate.

I recently had a cube reading 2 million from a fact table and it was getting done in about 4 minutes !

[bloggerman]

Thanks

[IceTea]

13 Dimensions with ~30 hierarchies and 37.000.000 Records in Databse. Cubesize = 250 MB, Buildtime ~2-2,5 hours

[bdbits]

Most of my cube builds thus far have had several million facts, 5-10 hierarchies, a few measures, and easily build in less than an hour with a cube file size of a few hundred megs. But this can vary a lot by your source data layout, database servers, and of course the box you are running on, especially RAM and disk speed.

A couple of examples:
38 million rows in fact table, ~15 hierarchies, 7 measures, 4.5 hours, 20G cube files (partitioned)
9 million rows in fact table, ~10 hierarchies, 4 measures (2 calculated), 25 minutes, 200M cube

[jwilliamstd]

Hello All -

We have very different environment with 23 dimensions, 100 measures and about 5 million records. It takes about 3 hrs to calculate that. But when we run the Oracle Table Stats and then create cubes it runs almost in 1.5 hrs. Can anybody explain us, why this change in time ? Thanks.

[bdbits]

Table stats collect information the database engine can use to optimize queries. The more up-to-date the information, the better the optimization and hence reduced run-times. I've not noticed it so much in cube builds, but it makes very large differences in some of our ETL processes and the more complex queries in some of our large reports.