Limitaion of OLAP Physical Cubes

[Prem Mehrotra]

I am new to Cognos world.  I have background in  Databases, Micosoft Analysis Services and SAP BW.
I am using Cognos 10 and looking into some drill own reports and am wondering whether I should use DMR  (virtual cubes) or  Physical Cubes. I had seen some articles a while ago that Cognos Power Play Cubes had a limitation of 2GB. Is it still true in Cognos 10?

I am still not convinced that OLAP Physical Cubes will provide any real benefits. I can model  my relational  database as Star Schema to begin with as well as use Oracle's materialized views for aggregation. I can get very close to performance of physical cubes. If Physical cuebes have limitation of 2GB; what's the point of using them. Even otherwise, they add another layer of complexity. What is the real benefit of them over DMR  designed with performance (start schema and materialized views0. Appreciate your insight.

[cognostechie]

2 GB was a limitation earlier not because of Cognos but because that was the limitation for a single file size on the operating system. The OS like Unix would not allow a single file to grow more than 2 GB but there is a bigfilesize setting in UNIX which allows a single file to grow more than 2 GB. I am not sure about Windows. Maybe somebody else can chip in here.

However, the Powerplay cube need not be a single file. You can have multiple files and you can specify how big each file would grow after which the data would go to the next file. I have seen cubes of 16 GB too.

There are lot of advantages of physical cubes over DMR. One example is Relative Time categories. Since you are more familiar with databases and less familiar with Cognos, you are inclined towards finding solutions outside of Cognos which would defeat the purpose of using Cognos. You will end up not using the power of Cognos and not getting the benefits but get only data extracts which would make the user's life difficult as they would have to dig deep into it and do their own analytics.

I am saying all this to help you realize not to take that direction !

[blom0344]

I spend last month working with SSAS and though no Microsoft fan, I have to admit it seriously outguns both powerplay cubes and DMR. Relative time categories are a strong Powerplay asset, but there are ways around it through MDX in SSAS.

The Microsoft solution is rapidly becoming a defacto standard and can be used with various frontend tools. We plan to replace our DMR build solutions with it.

[MMcBride]

I use the -dMultiFileCubeThreshold flag in the command line for my Transformer cubes that are over 2GB in size. Transformer is a 32 Bit application, so even on a 64Bit AIX install I can't build individual .mdc files over 2GB (if there is a way to do this I simply don't know how)

My largest cube is just under 9GB but this is spread across 20 or so files.

We do have several SSAS cubes in our environment today, but these use Excel as the client and as a result none of these cubes have a proper Time dimension it seems most people for whatever reason like to use SSAS with "time measures"
So using these cubes within Cognos is usually not a good idea.

I realize this is a "coding issue", this is a Cognos forum so I am assuming you plan to use Cognos to front end whatever you are building and unless the SSAS cubes are built correctly they just don't work well within in Cognos.

[cognostechie]

I spend last month working with SSAS and though no Microsoft fan, I have to admit it seriously outguns both powerplay cubes and DMR. Relative time categories are a strong Powerplay asset, but there are ways around it through MDX in SSAS.

The Microsoft solution is rapidly becoming a defacto standard and can be used with various frontend tools. We plan to replace our DMR build solutions with it.

This is very interesting ! For the sake of my knowledge, I would like to know the technical superiority of SSAS over Powercubes and DMR.

[Prem Mehrotra]

I was also reaidng another limitation of Cognos OLAP Cubes (physical) that the attributes of the dimesions are not whon only the "keys". This seems like serious limitation. How does one overcome it.

[blom0344]

With DMR you can show descriptive attributes within an analysis, bit only as a subtext below the member value (within the grid cell) With Powercubes the attributes can not be shown at all.

The strength with SSAS / Excel (I hate to admit) is that it easily lets you display attribute information on the same row in the grid.  This is something our customers find very frustrating with Cognos analysis. Some of our descriptive information is all about dates (not suitable for building a dimension). We always feel we need to defend Cognos for not being able to shown such descriptive info within an analysis

[cognostechie]

Yeah, that is true when you use the cube in Analysis Studio. The cube does allow three attributes in addition to the code/key (Short Name, Long Name and Member Description) but that can be used in Report Studio).

If that is the only show stopper, you can create the cube with a column which will be ID + Description and it will show up like that in Analysis Studio.

[cognostechie]

I am open to other technologies which are better but I find Cognos cubes useful because of their slicing/dicing ability and the power to open the extreme detail data by drilling thru from a Cube to a relational package. Analysis Studio does not have too much power anyway but when you use Report Studio, you can make really powerful analytics and also drill thru from one cube to another.

For analytics, time based analysis (trending) is extremely important and that is another area where Powercubes score !

[Prem Mehrotra]

Can't you do slicing and dicing using DMR as well. Also, are you saying time based trending can only be done using Pwoer Cubes, there is no way to do them using DMR. As I said, I am new to Cognos, so my questions ae new bie quetsion.

[Prem Mehrotra]

Last month I took training on Reports Studio. They use samples folder inside that everything is modeled usin DMR. I can do drill thorugh, drill across etc. So I am not clear; what dditional things Power Play cubes will do for ,.

[norkos]

Issues with the descriptive attributes, and cube size are existing problems, but there are workarounds to deal with them.

In my opinion the most important difference between DMR and OLAP is performance. Most OLAP applications - like Cognos Transformers - summarize the data in cube building time, and store the summarized and detailed data in the cube (in files) for every cells. If you run an OLAP based report, the reporting application just need to read out this data and render the report quickly.
If you run a DMR based report than Cognos first has to run a query on your DB, then from the resultset it has to create a virtual cube in real-time.  So Cognos can render the report only after the cube is finished, which can be quite long time.
In Cognos 10 you can speed up this process with DQM, but I think it can't be as fast as OLAP. And I'm affraid that DMQ is optimized mainly for TM1 which is already an OLAP solution.

So I would use DMR only in two cases:
  - you have a very robust DB provider which can process also complex queries in seconds - like a robust Teradata solution
  - you need real-time data and cube building can be a problem or a performance bottleneck

[blom0344]

I only partially agree. We tested DMR and existing OLAP cubes side by side and - for our limited amounts of data - found that the main performance bottleneck is in the webserver having to come up with the results, not the fact that having to run queries first may be that much slower. Of course this was against well formed starschema type data.
Because DMR creates the query on the fly it will usually draw a small part of the data stored and with proper indexing queries can be VERY fast and effective.

Another benefit is, that DMR analysis is always fully synchronized with the datamart it extracts data from.  Basically, the DMR concept is more or less the same one as used by Business Objects for its full client desktop reporter (which I developed in for about 10 years)

Yes, the OLAP version will always be inherently faster, but we sure were glad to introduce DMR with clients. Just setting up all the server components is really a throwback to days of future past.

And as for the descriptive attributes.. Never found the workaround you mentioned. So sure would like to know how

[cognostechie]

Not that I want to pursue this topic anymore but just stumbled across something..

I opened a Transformer cube in Business Insight Advanced and saw all 3 descriptive attributes for every level of every dimension. I guess Analysis Studio is not really required anymore so the problem of attributes goes away. It was never really a Cube limitation but only an Analysis Studio limitation.

That being said, it is not neccecary to build a Powercube if you can do something equivalent using some other tool.

[norkos]

Blom I thought something similar as Cognostechie wrote in his post. The attribute issue is just an Analysis Studio problem, you can deal with it in Report Studio and also in Business Insight Advanced as Cognostechie stated.

Anyhow I have to agree with you. Both DMR and OLAP have their pros and cons, so you have to analyze the actual situation first to be able to tell which one can fit the needs better.
And thanks for the useful infos about the performace bottleneck, these were new for me.