I have lost a blog entry about multiple measure group vs linked cubes...

I have lost a blog entry about multiple measure group vs linked cubes...

Post by » Fri, 04 Aug 2006 01:54:21


Hi,

Does anyone has the link to a blog entry which discuss about multiple
measure groups versus linked cubes
and the impact on query the performance.

I want to know more when the first option is better and when the second
option is better.

I have a cube with 19 measure group and 25 dimensions.
by measure group I have between 1 and 10 dimensions associated, so there is
a lot of unrelated links between measure groups and dimensions.
all measure groups are setup with the ignore unrelated dimensions to true.
each measure group contains between 10 000 and 400 000 of rows in a dev
environment, the production will have more rows (20 000 to 4 millions).
I don't have enough calculations, and most of them use measures which use
the same dimensions.

the users are able to create reports based on multiple measures easely.
So they are happy with this.

now, I have to anticipate the large number of rows in both fact and
dimensions.
how a single cube will perform compared to a multiple cube + linked objects?
have you done some tests to compare the 2 options?

Thanks.

Jerome.
 
 
 

I have lost a blog entry about multiple measure group vs linked cubes...

Post by Deepak Pur » Fri, 04 Aug 2006 04:07:40

Hi Jerome,

Are you alluding to this entry in Teo Lachev's blog:

http://www.yqcomputer.com/
UDM Data Islands

As I mentioned in my TechEd Memoirs post, Microsoft is scaling back from
the upercube approach in favor of more granular data slands
Since this took me by suprise, I approached the SSAS team for
clarification. Here are the answers I managed to get:


1. What performance benefits could be realized by splitting UDM in data
islands?

At this point, we don't have detailed performance numbers we can share.
But to give an indication - a cube that had around 15 measure groups,
and a very large number of attributes (over 100 cube dimensions, many
with more than 100 attributes) showed a performance improvement of about
6 fold when split into separate cubes (one per measure group), for
queries that only referenced a single measure group. The main benefit
came from the reduced cube space - the individual cubes only needed to
include dimensions related to the measure group, and therefore paid no
overhead for unrelated dimensions. There was also some benefit from the
MDX script being simpler for each of the individual cubes.


2. What guidelines should be followed when partitioning UDM?

The general principle is to have multiple cubes, maybe even one per fact
table. Then have other cubes, using linked measure groups that can
combine into the complete view. Use the small cubes when they meet the
needs, and query the full cube when need to use measures fro> >1 measure
group.

Some general guidelines are:

- If the dimensionality of all the measure groups is (much) the same, it
would limit the benefit of splitting up (though there is some benefit of
the simpler MDX script per cube)

- If queries very commonly reference measures fro> >1 measure group,
then there would be no value in splitting those measure groups into
separate cubes

- The perf benefit must be balanced against the negative impact on the
end user view, if frequently a question using measures from just one
measure group becomes a question where a measure from another measure
group is added. For the user to switch cubes in the middle would be
tiresome

- A single large cube is easier to manage, as there is no need to
duplicate scripts

For calculations that span cross measure groups e.g. where many measure
groups have calculations using exchange rate, certainly would not want
to use LookupCube. Could either keep all those measure groups in the
same cube, or split up and again use linked measure groups to utilize
exchange rates in multiple cubes


3. By a ata islanddo you mean leveraging the linked dimensions and
measure group features? If so, any known limitations and restrictions?

Yes, uses linked measure groups, as described above. The dimensions are
simply shared, and need not be linked. As far as limitations, there is
overhead of having to define calculations etc. on the cube that combines
the smaller cubes. Also, Report Builder does not handle such databases
that well, as the linked measure groups appear as separate entities in
the report model (there is nothing saying the Sales in one cube is
exactly the same as Sales in another)


4. When can we expect a UDM performance whitepaper?

One is being worked on>> >>


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.yqcomputer.com/ ***

 
 
 

I have lost a blog entry about multiple measure group vs linked cubes...

Post by » Fri, 04 Aug 2006 05:21:42

hanks for the link.

what are the results in case of creating multiple cubes
and then 1 "super cube" with all the littles cube linked into.
in this case, does the "unrelated dimensions problem" appear again?
or the physical split but logical single view will improve the performance?



"Deepak Puri" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...