Topic
  • 9 replies
  • Latest Post - ‏2010-07-16T23:08:09Z by SystemAdmin
AnkurG1981
AnkurG1981
4 Posts

Pinned topic Cognos cubes data capacity

‏2010-07-16T10:59:37Z |
Hi,

We are process of evaluating different BI tools. Can someone please tell me what is the maximum data load that has been tested on cognos cubes ?
We want to build cubes using at least ~50 mn records/day i.e. approx 20 GB of data per day
Can someone please tell approx how much time a simple 3D cube will take for that data volume

Thanks and regards,
Ankur
Updated on 2010-07-16T23:08:09Z at 2010-07-16T23:08:09Z by SystemAdmin
  • Rob Walker
    Rob Walker
    134 Posts

    Re: Cognos cubes data capacity

    ‏2010-07-16T11:16:19Z  
    Hi,

    It's going to depend on how much historical information you will be holding, how many dimensions, how many categories etc but really from what you have said with 50 mn records/20gb per day you will be really struggling on Transformer and in fact on any disk integrator and your build and access times will become unmanagble pretty quickly

    If you are looking at holding this volume of data and making it usuable it may be better to look at TM1 instead. Perhaps drop a post to the TM1 forum with the same question and see what they think.
    Rgds

    Rob
  • AnkurG1981
    AnkurG1981
    4 Posts

    Re: Cognos cubes data capacity

    ‏2010-07-16T12:38:05Z  
    Hi,

    It's going to depend on how much historical information you will be holding, how many dimensions, how many categories etc but really from what you have said with 50 mn records/20gb per day you will be really struggling on Transformer and in fact on any disk integrator and your build and access times will become unmanagble pretty quickly

    If you are looking at holding this volume of data and making it usuable it may be better to look at TM1 instead. Perhaps drop a post to the TM1 forum with the same question and see what they think.
    Rgds

    Rob
    Thanks Rob for a quick reply

    Any inputs on the build time for say 3 Dimension cube for such a volume?

    Thanks,
    Ankur
  • Rob Walker
    Rob Walker
    134 Posts

    Re: Cognos cubes data capacity

    ‏2010-07-16T13:18:36Z  
    Thanks Rob for a quick reply

    Any inputs on the build time for say 3 Dimension cube for such a volume?

    Thanks,
    Ankur
    Hi Ankur,

    To be honest I wouldn't even hazard a guess. If you are adding 1.4 billion records a month,17 billion a year even using an incremental update technique and a limited number of dimensions the processing time is going to be extensive as is the reading.

    Transformer has a 2gb limit per mdc file although it will create new files once you hit that limit but can you imagine the amount of physical files it will create if it receives 20gb per day.

    Out of interest what would be the purpose of the cube as containing just 3 dimensions, one of which I guess would be time, does seem a bit limiting in terms of useful analysis? Also I'm presuming the 50 mn records per day are transactional items but can these be summarised first and therefore bring the numbers down to a more manageable level?

    Rgds Rob
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Cognos cubes data capacity

    ‏2010-07-16T13:26:43Z  
    Thanks Rob for a quick reply

    Any inputs on the build time for say 3 Dimension cube for such a volume?

    Thanks,
    Ankur
    There are many factors which will influence the cube build time. It will not be possible to give you an estimate on the time it will take to build the cube.

    It will be useful for you to evaluate the number of consolidated records that you intend to build into the cube. For example, you might have transactional data which produces millions of records per day (reported at minutes and seconds). However, a cube built at the day level may aggregate this set of records to the day level and you would have considerably fewer consolidated (aggregated) records which would need to be processed by Transformer to build the cube.

    If you are trying to build the lowest level transactional data into a PowerCube then I would suggest that you review your application design. There are companies building PowerCubes which reach 40 Gb in size but these are not following the best practices for using the product and there are corresponding performance issues that come with cubes this large. To get performance out of such large PowerCubes it is necessary to do a lot of work in designing the cube with appropriate partitioning strategies. TM1 or Cubing Services will probably be better applications to handle the data volume if you need OLAP reporting at transactional levels.

    Honestly though, it sounds like there is a gap in the application design that needs to be addressed if you believe that you need to perform OLAP analysis on data at the transactional level.
  • AnkurG1981
    AnkurG1981
    4 Posts

    Re: Cognos cubes data capacity

    ‏2010-07-16T13:40:10Z  
    Hi Ankur,

    To be honest I wouldn't even hazard a guess. If you are adding 1.4 billion records a month,17 billion a year even using an incremental update technique and a limited number of dimensions the processing time is going to be extensive as is the reading.

    Transformer has a 2gb limit per mdc file although it will create new files once you hit that limit but can you imagine the amount of physical files it will create if it receives 20gb per day.

    Out of interest what would be the purpose of the cube as containing just 3 dimensions, one of which I guess would be time, does seem a bit limiting in terms of useful analysis? Also I'm presuming the 50 mn records per day are transactional items but can these be summarised first and therefore bring the numbers down to a more manageable level?

    Rgds Rob
    Thanks Rob,

    I just wanted to get minimum build time that's why mentioned simplest possible cube dimensions. The actual implementation that we plan to do will be far more complex

    You are right this is transactional data and 50mn limit is after summarizing the data. Actual volume is 200mn+ (upto 1bn in future) records per day

    Can you give me a rough idea about max volume you have seen on a cognos cube and how much build time such cognos cubes was taking

    Thanks,
    Ankur
  • Rob Walker
    Rob Walker
    134 Posts

    Re: Cognos cubes data capacity

    ‏2010-07-16T13:57:50Z  
    Hi Ankur,

    For Transformer a few hundred million records and build time will be taking a number of hours certainly not minutes. I would very much concur with Phil on this and take his lead on the sizing limits especially if your design is much more complex than 3 dimensions.

    With the current and potential volumes you are talking about it sounds like you need an in-memory solution instead such as those already mentioned.

    Kind Regards

    Rob
  • Rob Walker
    Rob Walker
    134 Posts

    Re: Cognos cubes data capacity

    ‏2010-07-16T14:13:41Z  
    Hi Ankur,

    Perhaps put it this way, if you were a client and came to me with your requirements then Transformer would not be the first tool I would be reaching into my toolbox to pull out.

    Rgds

    Rob
  • AnkurG1981
    AnkurG1981
    4 Posts

    Re: Cognos cubes data capacity

    ‏2010-07-16T15:25:00Z  
    Hi Ankur,

    Perhaps put it this way, if you were a client and came to me with your requirements then Transformer would not be the first tool I would be reaching into my toolbox to pull out.

    Rgds

    Rob
    Phil/Rob,

    I probably didn't put the question properly. We will first summarize the data to ~50 mn records and then feed it to the cube. However after aggregation in the cube the actual count will be less than 10 mn with size < 10GB

    Regarding in-memory, yes we are using an in-memory DB for transactional data. We plan to summarize transactional data in-memory and then feed that data into Cognos cube for aggregation

    I just need a rough estimate of minimum possible time that any such cube build can take

    Thanks,
    Ankur
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Cognos cubes data capacity

    ‏2010-07-16T23:08:09Z  
    Phil/Rob,

    I probably didn't put the question properly. We will first summarize the data to ~50 mn records and then feed it to the cube. However after aggregation in the cube the actual count will be less than 10 mn with size < 10GB

    Regarding in-memory, yes we are using an in-memory DB for transactional data. We plan to summarize transactional data in-memory and then feed that data into Cognos cube for aggregation

    I just need a rough estimate of minimum possible time that any such cube build can take

    Thanks,
    Ankur
    10 Gb is still a very very large cube. There really are too many factors to give you a time estimate for the cube build.

    One thing you should be aware of is that there is a product limit which may impact your design choice. PowerCubes have a limit of 2 million categories (distinct members in all the dimension hierarchies). If you are going to have more than 2 million categories in your entire cube then you are not going to be able to put all of this information into a PowerCube.

    If you absolutely insist that PowerCubes need to be used here then you should consider building only higher-level summary information into the cube and then combining the cube with a drill-through mechanism to a relational model for the transaction-level details. This will reduce build time, give you faster query response, and also give you access to the full data set.

    I would not recommend your current design approach. These are the design approaches I would recommend you look at:
    1. Summary PowerCube with drill through to relational data
    2. TM1
    3. Cubing Services