File size limitation on .CUB files?

[MarioRubbo]

Is there a 2GB limit on the size of a .CUB file? I have an extremely large database in Oracle that I would like to make OLAP-capable. I am limited by budget to Cognos OLAP tools (PowerPlay and TM1) and I have have already reached size limitations in Cognos PowerPlay. I don't think TM1 can handle the volume either but I wanted to do some fact finding first.

[sachin]

In case you were not aware, to circumvent 2GB size limit you can create Cognos PowerPlay cube with multiple files as mentioned in this article.

[Martin Ryan]

With TM1 the limit is imposed by your hardware, not TM1. If you have a 32 bit system then your model is limited to 2GB, or 3GB if you do some fiddling. But if you have 64 bit TM1 then you are only limited by the amount of RAM on your machine.

Also, I don't know about Powerplay but Cognos Planning stores zeroes, whereas TM1 doesn't. This may make your cube much smaller in TM1 than it would be in the other technologies. You can also do a lot of fiddling with the order of dimensions in the cube, which makes a big difference to its sizing (again due to TM1's handling of sparsity).

Martin

[Mike Cowie]

Also, if you're talking about physical *file* size limitations, I believe any limitations there have to do with your choice of disk/partition format and, possibly, operating system.

For example, if your TM1 data directory is on a FAT32 partition (I can't imagine too many are these days) I think you are limited to files no greater than 4GB. On an NTFS partition those file size limitations I believe are determined by the size of your partition (i.e., if you have a 300GB partition you could theoretically create one 300GB file). TM1 generally does a pretty good job of keeping .CUB files pretty small, but as alluded to above things like dimension order can have a direct impact on the cube size in memory as well as the .CUB file size. Unless your disk is nearly full, I'd be more concerned more with your available RAM, though, since that's what ultimately matters to TM1 most.

Hope that helps.

Regards,
Mike

[Alan Kirk]

Also, if you're talking about physical *file* size limitations, I believe any limitations there have to do with your choice of disk/partition format and, possibly, operating system.

For example, if your TM1 data directory is on a FAT32 partition (I can't imagine too many are these days) I think you are limited to files no greater than 4GB. On an NTFS partition those file size limitations I believe are determined by the size of your partition (i.e., if you have a 300GB partition you could theoretically create one 300GB file). TM1 generally does a pretty good job of keeping .CUB files pretty small, but as alluded to above things like dimension order can have a direct impact on the cube size in memory as well as the .CUB file size. Unless your disk is nearly full, I'd be more concerned more with your available RAM, though, since that's what ultimately matters to TM1 most.

And just to add to what Mike said, an often overlooked issue until it causes a problem is that the larger the .cub file size, the longer a data save is going to take. Our largest .cub is around 600 meg, and even with that save times can be painful (several minutes, though it's rarely that cube alone) which is why we try to restrict it to out of hours. But unfortunately there's at least one time each week when it has to be done in working hours, and it's not fun for users. When you're talking about .cub sizes into gigabytes, then blindingly fast hard disks would be more of a necessity than a luxury. (Also I hate to think about load times with a file of that size, particularly if in addition to all of that data it's punching in feeders all around the place.)

[lotsaram]

And just to add to what Mike said, an often overlooked issue until it causes a problem is that the larger the .cub file size, the longer a data save is going to take. Our largest .cub is around 600 meg, and even with that save times can be painful (several minutes, though it's rarely that cube alone) which is why we try to restrict it to out of hours. But unfortunately there's at least one time each week when it has to be done in working hours, and it's not fun for users. When you're talking about .cub sizes into gigabytes, then blindingly fast hard disks would be more of a necessity than a luxury. (Also I hate to think about load times with a file of that size, particularly if in addition to all of that data it's punching in feeders all around the place.)

From painful experience with a model stretching into 100GB+ the single threaded save is the real bottle-neck (and the idiocy of "change a single cell, save the entire cube" design which was of course not so bad in the 32 bit world with much smaller cubes but now needs some serious rethinking). As long as feeders are kept to a minimum the load time is pretty good even for very large cubes, especially if you have lots of cores at your disposal.

[MarioRubbo]

Thanks, guys. Ideally, I would be using an OLAP solution for this reporting project and I am trying to determine if I should even attempt TM1 as a possibility. Ultimately, I think I will need to stick with more of a ROLAP solution - rather than a MOLAP approach. My fact table is absolutely huge and I don't think MOLAP will be practical. One of my dimensions has approximately 9million members in it and the resulting cube would be very dense. Therefore, I'm not sure how sparse the cube will actually be - which translates into a larger .CUB size.

[jim wood]

Most BI tools (OBIEE and Cognos) use a database for the detail and an olap product for the summary level. You might want to consider a similar model. We are currently rolling out a MI object across our business. Originally we opted to use a database only. This has proved to cause all kinds of problems. We are now looking at an OLAP tool to replace some of the summary level tables that we have,

Jim.

[MarioRubbo]

Hi Jim:
I would normally implement a solution with the same components described but this project presents problems because of the shear volume.

As far as the problem you experienced, my guess is that you had an ever exploding number of summary tables/materialized views and the process of keeping them refreshed eventually became unsupportable? I suspect we will have the same problem on this project if I go with a purely ROLAP solution in Oracle --> i.e. Materialized Views with Query Rewrite enabled on top of a large, detailed fact table. The problem I have is that I am currently limited in my choice of OLAP tools to PowerPlay and TM1 and neither can handle the volume I need to calculate the required measures. I am researching approaches before I have to scale back the deliverable.

[jim wood]

We have kept the number of views quite small. The problem we have had is 2 fold:

1) The performance we have experienced has not been good enough. Unless one of reports is run solely on the top level summary tables it can take over 5 minutes to come back. (Our project aim was for quicker than this, granted the amount of data is huge. Bare in mind we are using Teradata which is supposed to be one of the more faster data retrievers around.)

2) Databases do not lend themselves to reporting variances either against time or version very well. Especially when considering when a variance may differ depending if it is an expense or income.

3) We have also struggled to calculate participation %'s easily.

Points 2 & 3 are eaten by all OLAP tools very easily.