Cognos Building Cubes

I am struggling with building a cube. I am trying to understand how cognos calculates the parent/child counts so I can understand why I have too many. Does it have to do with too much data. Would I be better off subselecting my data first by time frame and not have so much historical data in files. I am getting an tr1901 and I see where I am over 65K on my dimension coungs. I wish I could find some doc on how to build a successful cube. Any help would be appreciated.

Popular White Paper On This Topic

Lorraine,
Considering the error you are getting it makes it a bit hard to determine the type of questions to ask. there are a few things you will need to check to narrow down the cause. First thing I would do is a clean house across the dimensions and attempt another build. sometimes in the early stages of design you drage in alot of junk unless you clean that out it hangs around and causes a heap of problems (not to mention a massive model size)

The error basically means you have an unbalanced tree in your dimension or the number of related categories between two levels in you dimension exceeds the 65K number. One way to resolve this is by introducing another level in the dimension.

For example a dimension that holds Customer Name, (a very poor example and should never be used in a cube), anyhoo, you have state at the top level but you have over 65K customers in an individual state. you intrduce another level such as city to refine the number of customers related to the above level. You could also introduce a manual level that separates the customer name by the first letter of their surname

let us know how you go with this there are other things to consider too, but lets get this section resolved first.

my email is email@removed My biggest challenge is knowing what and how much and how many levels to put in a dimension. I ultimately want to build a cube that we can query detail data on. Do not know if this is feasible with cognos.

Thank you for the reply. I did get past building the cube. I put a filter on the data to extract from 2003 an on. Unfortunately now I am struggling with how to create the dimensions and how many levels. Ultimately I want to build a cube to extract detail data. We are an health insurance agency and I built a cube with detail claims and policy data. The reason we are creating a cube is to build it at night so the users will not query against live data and cause resource issues. The cube builds fine but when I run a report and select a few dimensions, then the report either takes forever to run or I get a tuple error. I did change the config tuple error to 30000000 as recommended by IBM. Ultimately I would like to build a cube and give the users a report to select dynamically on various fields such as service dates, state, plan, diagnosis code. Have you built cubes to allow reporting on detail data?

If your users are happy with data that is a day old then consider making a copy of your production database, and allow them to report off the copy. This copy is often known as an 'ODS'. This will allow the users to perform transactional reporting on a database and not affect the production system. You can also load up your ODS with database indexes which speed up reporting.

Cubes are great for showing summarised data and drilling into it but they are not as good at displaying detailed data. You should attempt to put enough levels in the dimension to ensure that there aren't more than 100 or so children of any member.... and preferrably no more than 20. (the number varies, but that is the kind of magnitude we're talking about). Otherwise you end up with crashes or out of memories. Don't try to build a detail report (i.e. 100's of lines with nested dimensions) out of a cube.

If you have 1 million customers. then you are unlikely to be able to build a customer dimension with individual customer numbers in it. You would be able to build a few dimensions around customers that has some attribute of customer, for example one dimension might have customer country, province, city and another might be some kind of claim frequency or claim loss bands with a few levels. Using these dimensions in combination allow you to find out a lot about your customers.

You then end up with a cube that has information valuable to the business (claim bands) so that they can slice and dice and see a summarised picture of what is going on.

Then if you want to go down to detail level (customer numbers etc.), the general idea is that you 'drill through' from your cube to a detail level report which lists customer numbers, claim dates etc. based on whatever you have picked in the cube. The detail level report is not based off a cube, it is based off your ODS. Various versions of Cognos do this in different ways.

Say in your cube you've drilled down to all of the customers in queensland with a claim band of x. Now you drill through to a detail report on that and it shows you all of the individual customers and their claims who satisfy those conditions... and the totals match the cube.

Anyway in summary it sounds like your problem is that you are trying to build detail reports out of cubes. Don't go there. Cubes are greate for a birds eye view of the business, with the ability to drill down to a certain level. Don't try and use them to create detail reports.

Thank you so much for your response. At least now I feel vindicated as to why I could not get the report to run. When the consultants came in to install cognos, we designed the cube star schema on claims detail. I feel like the consultants were knowledgable in the product just not with understanding the business and our goals for cognos. Now it makes total sense to build the cube with summarized data and then to drill through. Just need to learn how to do this. I now feel pretty comfortable in F/M and transformer need to get there in Report Studio.

We are running on an Iseries, I need to analyze if copying the transactional data to ODS will buy us anything. I am assuming that the sql will run in batch and it would not matter if it was querying a prod database or a copy of the prod database, it would just be the system resources we would be the concern.

Understood.... even if you made a copy of the data it would still be on the same iSeries server and therefore still be sharing resources, and therefore there would be no gain.

We have had success in the past copying iSeries data to SQL Server and using SQL Server as an ODS or even a datawarehouse. You might be suprised how well it performs. But its a large undertaking to add another database platform to your portfolio along with all of the associated ETL, maintenance etc.

Anyway if you can move all summarised data reporting into a cube, then that is that component of the load removed from the iSeries. That would mean that only the transactional reporting would hit the database.

Copyright 1998-2015 Ziff Davis, LLC (Toolbox.com). All rights reserved. All product names are trademarks of their respective companies. Toolbox.com is not
affiliated with or endorsed by any company listed at this site.