I have 12 cubes, one for each month of the year, and a Virtual Cube of all 12
making a "Year Cube". The problem I'm having is that certain queries of the
Virtual Cube return completely incorrect results when compared to running the
same query on the individual monthly cubes.

These cubes have fact table around 6,000,000 rows, I'm wondering if 12 x
6,000,000 is too much data for OLAP 2000 to handle?

My example is if I select the Destination Dimension for June in the June
monthly cube I get 1,300,000 calls to Vodafone mobiles. However if I select
the same in the Year Cube I get 720 calls in June. This is obviously wrong,
and there's nothing missing from the Calculated Member as far as I can see
that would cause this.

Is this a known issue? I have the most up-to-date service pack installed
AFAIK.

Hi, i'm not really sure what your issue is, however why are you using
12 cubes and joining using a virtual cube, as opposed to 1 cube with 12
partitions. Each partition can point to a different fact table, so
long as they are the same structure. This would probably reduce your
complexity of summing measures from multiple base cubes in the virtual
cube.

I'm not sure that would be viable. The reason we have separate cubes for
each month is because the processing time for using the Year cube is
extremely slow, and most users only need to look at one month at a time.
Occasionally people want to look at longer periods of data so we have the
Year virtual cube for that. I'm not sure if OLAP is smart enough to know
which partition to look in for the relevant month's data.

http://www.mosha.com/msolap/tech.htm
http://www.microsoft.com/sql/solutio...ojectreal.mspx

sounds like a typical partitioning scenario - however - heard that you can
actually partition the relational environment without partitioning in AS -
never tried it though...

"commuting is hell" <commutingishell (AT) discussions (DOT) microsoft.com> wrote in
message news:6171AF89-99F6-48E0-8870-CA9480BA421D (AT) microsoft (DOT) com...

In order for AS200 be able to retrieve data only from the correct partition
(e.g., the June partition if user only uses June) you must create the
partitions with data slices. The data slice must match the correct month in
the time dimension.

Be aware that using data slices the query lanch to the relational server
will have a join to time dimension's fact table in order to ensure that only
data from the related month is selected.
There is server level parameter to avoid this additional join. I think how
to do it is detailed on "Microsoft SQL Server 2000 Analysis Services
Performance Guide"
http://www.microsoft.com/technet/pro.../ansvcspg.mspx.
If not then it is somewhere in Microsoft's on-line documentation.

"commuting is hell" wrote:

i forgot to mention that we have many cubes that reference more than
80,000,000 rows across 4 parititions. I use data slices to make sure
AS2000 uses the correct partition for user queries, but i also put an
SQL 'WHERE' clause in the advanced box (in the parition wizard) that
repeats the dataslice, but using my clustered index on the fact table,
rather than the default behaviour. I found that this had a big
improvement in data processing performance.