# Miscellaneous > Database Design >  OLAP modeling: one cube versus many cubes?

## josmi

I am still puzzled about the correct way to design a cube vs. several cubes so that reports and queries that compare several different measurements can be made as easily as possible. The problem is that when there are different measurements, these measurements may only share some dimensions or some levels of a hierarchical dimension.
For example (a very artificial example just to illustrate the question) a measurement about working hours may be available on a daily basis, involve the dimension male/female, job category, and also the dimension overtime. Another measurement e.g. production may be available only on a monthly basis, and have the dimension product type. Both measurements share the hierarchies year and month of the time dimension.
These measurements are very different and only share a subset of dimensions, and dimension values, yet we want to create reports and queries where one measurement is compared with the other. For example we want to compare the total female working hours of job category A with the total production of product type X on a monthly basis. Or we want to compare the average daily working hours of all males with total production on a monthly basis.
The usual advice given is not to make the cube to big, avoid "unrelated" measures and avoid too many dimensions, but for some applications, reporting over many measures that are related only by e.g. the time dimension is important. There seems to be no easy, "standard" way to create reports over many cubes in an ad-hoc fashion.
So - how is this kind of problem usually dealt with? Is there some good reading stuff for this?

----------


## skhanal

Your cube design will be driven by reporting requirement. As you mentioned, you should not try to build a cube to satisfy multiple requirements, build specific cube for each type of requirement. This way when you rebuild a cube only subset of users are impacted.

----------


## josmi

Thank you for this answer! But what bugs me is this: what if my reporting requirements do include the necessity to query two or more cubes?  It would be combinatorically forbidding to try to create from each forseeable combination a cube one extra cube - there are simply too many possible queries and reports that should be possible. Also, each cube that contains all the measurements and dimensions that *could* be needed together in a user query would get extremely sparse with many rows where several of the dimensions simply do not apply. On the other hand, factoring the data into several or many cubes will require that the queries combine two or more cubes which is badly or not supported at all by the usual tools or the MDX language.

Most books about OLAP and reporting only really consider a rather small set of fixed reporting tasks which are usually well defined in advance. In those scenarios it is usually possible to identify individual cubes for individual users and their reporting needs. But in our case, users want to combine nearly everything with everything else in their reports. So in order to model this without too many sparse entries and without dimensions that simply do not apply to a measurement, we would need a large number of cubes. But then reports that require two or more cubes get rather hard to do and to implement in a GUI, no? Also MDX cannot be used across several cubes.

The dilemma we are in looks rather basic and principal so I wonder if that is something that is discussed in some whitepaper or book or some web-page -- it is hard to believe that we are the first one who face these kinds of problems.
But most stuff I read about BI, reporting and OLAP either ignores this problem completely or assumes that it is possible to factor the data in a way so that each user group gets their own cube that would satisfy all their query and reporting needs.

----------

