Help: How to design large dimensions in Analysis Services

#1

Having an invoice dimension will make your cube VERY
sparse and it will therefore have pretty bad performance.
Have you thought of not having an invoice dimension and
instead letting the users "drill to detail" to see the
line item invoices?

Quote:

-----Original Message-----
I really appreciate it if the group can share their
experiences regarding building an Invoice dimension
(MOLAP) with MSAS. We expect atleast 4000 new invoices
per
day;users want to drill down to invoice details. They
want
to see an Invoice Dimension.

Also, there is a requirement for a Customer dimension
which is very huge, and is a slowly changing dimension
(Parent Customer, or Bill to Customer may change i.e.
Customer-Walmart billing might change from a Walmart
center in Chicago to New York) how can this be handled?

There is a requirement for a Sales Rep-Manager Slowly
changing Dimension (this is a parent child type of a
dimension).

Would you recommend the use of Parent Child dimensions (I
realise they are very slow and degrade performance). How
should I handle slowly changing dimensions with Parent
Child dimensions? Do I use the member properties to
indicate when a particular parent-child relation
starts/ends?

MSAS has limitaions with respect to the number of records
in a Dimension (64,000) How should I design dimensions
for
example an asset dimension, where the company has more
than 64000 assets (unique assetid) and would like to
drill
down by an asset number?

I truely appreciate your help in this regard. Please also
give me some information if SQL Server database and MSAS
is better off installed on the same Server or 2 different
servers? We are looking at an incremental load of about
30MB per day atleast.

Thanks,
GP
.

#2

Thanks David and Tom. I understand that no member in a
Dimension can have over 64K children, as such there can be
64K levels with 64K*64K members overall, but such a big
MOLAP dimension as an Invoice might lead to low
performance issues right as David suggested? Is it
actually adviceable to have such big dimensions? The other
option we were considering was drill through to list
invoice numbers making up the revenue figures? Is there
any other way of handling such dimensions?

Quote:

-----Original Message-----
Having an invoice dimension will make your cube VERY
sparse and it will therefore have pretty bad
performance.
Have you thought of not having an invoice dimension and
instead letting the users "drill to detail" to see the
line item invoices?

-----Original Message-----
I really appreciate it if the group can share their
experiences regarding building an Invoice dimension
(MOLAP) with MSAS. We expect atleast 4000 new invoices
per
day;users want to drill down to invoice details. They
want
to see an Invoice Dimension.

Also, there is a requirement for a Customer dimension
which is very huge, and is a slowly changing dimension
(Parent Customer, or Bill to Customer may change i.e.
Customer-Walmart billing might change from a Walmart
center in Chicago to New York) how can this be handled?

There is a requirement for a Sales Rep-Manager Slowly
changing Dimension (this is a parent child type of a
dimension).

Would you recommend the use of Parent Child dimensions
(I
realise they are very slow and degrade performance). How
should I handle slowly changing dimensions with Parent
Child dimensions? Do I use the member properties to
indicate when a particular parent-child relation
starts/ends?

MSAS has limitaions with respect to the number of
records
in a Dimension (64,000) How should I design dimensions
for
example an asset dimension, where the company has more
than 64000 assets (unique assetid) and would like to
drill
down by an asset number?

I truely appreciate your help in this regard. Please
also
give me some information if SQL Server database and MSAS
is better off installed on the same Server or 2
different
servers? We are looking at an incremental load of about
30MB per day atleast.

Thanks,
GP
.

.

#3

Hi Geeta --

Here are a couple of ideas. In my experience I would definitely have
MSAS, and MOLAP Cubes, on a server separate from my database or data
warehouse server.

With respect to your dimensional member issue, I would recommend using
existing asset classes or creating new classes within which to group
many of the indivual assets, thus creating a hierarchy.

Hope that helps.

Pete

Kirkland, WA, East Side Seattle

================================
Nothing in these comments may be taken as any kind of guarantee,
warranty, or assurance, of any kind, implied or express. These are
just observations. Experience of others in situations other than that
of the respondent may, of course, differ.
================================

"GP" <geeta_pajjuri (AT) yahoo (DOT) com> wrote

Quote:

I really appreciate it if the group can share their
experiences regarding building an Invoice dimension
(MOLAP) with MSAS. We expect atleast 4000 new invoices per
day;users want to drill down to invoice details. They want
to see an Invoice Dimension.

Also, there is a requirement for a Customer dimension
which is very huge, and is a slowly changing dimension
(Parent Customer, or Bill to Customer may change i.e.
Customer-Walmart billing might change from a Walmart
center in Chicago to New York) how can this be handled?

There is a requirement for a Sales Rep-Manager Slowly
changing Dimension (this is a parent child type of a
dimension).

Would you recommend the use of Parent Child dimensions (I
realise they are very slow and degrade performance). How
should I handle slowly changing dimensions with Parent
Child dimensions? Do I use the member properties to
indicate when a particular parent-child relation
starts/ends?

MSAS has limitaions with respect to the number of records
in a Dimension (64,000) How should I design dimensions for
example an asset dimension, where the company has more
than 64000 assets (unique assetid) and would like to drill
down by an asset number?

I truely appreciate your help in this regard. Please also
give me some information if SQL Server database and MSAS
is better off installed on the same Server or 2 different
servers? We are looking at an incremental load of about
30MB per day atleast.

Thanks,
GP

#4

GP,

It will probably depend on how much data you want to store. Based on your
example, if you consider that there are 365 days in a year, that will
require 1,460,000 members per year. That is definitely a large dimension
for MOLAP storage. If you can't design around it by using drill-through or
actions for example, you may need to consider using the 64-bit edition of
Analysis Services and using a server that has more than 3GB of RAM installed
on it. What I would do is process the dimension and see how much memory it
consumes, and double that value to take into account shadow dimensions that
are needed for processing. If you're well under 3GB at that point, you
should be fine pending your other dimensions. I would also recommend that
you keep member properties limited at the Invoice level. If you find that
yourself needing virtual dimensions based on member properties, I'd
recommend that you just build regular dimensions instead and not create the
member properties.

Sean

--
Sean Boon
SQL Server BI Product Unit

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

"Pete Hohenhaus" <cam_pete (AT) hotmail (DOT) com> wrote

Quote:

Hi Geeta --

Here are a couple of ideas. In my experience I would definitely have
MSAS, and MOLAP Cubes, on a server separate from my database or data
warehouse server.

With respect to your dimensional member issue, I would recommend using
existing asset classes or creating new classes within which to group
many of the indivual assets, thus creating a hierarchy.

Hope that helps.

Pete

Kirkland, WA, East Side Seattle

================================
Nothing in these comments may be taken as any kind of guarantee,
warranty, or assurance, of any kind, implied or express. These are
just observations. Experience of others in situations other than that
of the respondent may, of course, differ.
================================

"GP" <geeta_pajjuri (AT) yahoo (DOT) com> wrote

I really appreciate it if the group can share their
experiences regarding building an Invoice dimension
(MOLAP) with MSAS. We expect atleast 4000 new invoices per
day;users want to drill down to invoice details. They want
to see an Invoice Dimension.

Also, there is a requirement for a Customer dimension
which is very huge, and is a slowly changing dimension
(Parent Customer, or Bill to Customer may change i.e.
Customer-Walmart billing might change from a Walmart
center in Chicago to New York) how can this be handled?

There is a requirement for a Sales Rep-Manager Slowly
changing Dimension (this is a parent child type of a
dimension).

Would you recommend the use of Parent Child dimensions (I
realise they are very slow and degrade performance). How
should I handle slowly changing dimensions with Parent
Child dimensions? Do I use the member properties to
indicate when a particular parent-child relation
starts/ends?

MSAS has limitaions with respect to the number of records
in a Dimension (64,000) How should I design dimensions for
example an asset dimension, where the company has more
than 64000 assets (unique assetid) and would like to drill
down by an asset number?

I truely appreciate your help in this regard. Please also
give me some information if SQL Server database and MSAS
is better off installed on the same Server or 2 different
servers? We are looking at an incremental load of about
30MB per day atleast.

Thanks,
GP

#5

Depending on whether new hardware/OS software is an option, the 64-bit
version of Analysis Services supposedly overcomes a lot of the
limitations on dimension size:

http://groups.google.com/groups?q=64...ublic.sqlserve
r.olap&hl=en&lr=&ie=UTF-8&oe=UTF-8&group=microsoft.public.sqlserver.olap
&selm=OhfnR2eXDHA.2568%40tk2msftngp13.phx.gbl&rnum =9

Quote:

From: Sean Boon [MS] (seanboon (AT) online (DOT) microsoft.com)

Subject: Re: Very Large Dimension
View: Complete Thread (7 articles)
Original Format
Newsgroups: microsoft.public.sqlserver.olap
Date: 2003-08-08 13:43:20 PST

4 million members definitely qualifies as being a large dimension. As
Jon
indicates you can use the VLDM to manage this dimension. While VLDM
does
help, it also out of process from the Analysis Service itself. This
means
that there will be a performance impact associated with it. That may or
may
not be of a concern for you. All you have to do take advantage of VLDM
is
set the VDLMThreshold key to a value that will force the VLDM to be
used.

However, I'd strongly recommend that you look at the 64-bit edition of
Analysis Services. The 64-bit edition does not use VLDM and can load
this
dimension into the main server process becuase the 64-bit edition can
support much larger amounts of virtual memory.

Sean

--
Sean Boon
SQL Server BI Product Unit

Quote:

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Help: How to design large dimensions in Analysis Services

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

Help: How to design large dimensions in Analysis Services - 09-08-2003 , 11:12 AM

Help: How to design large dimensions in Analysis Services - 09-08-2003 , 12:11 PM

Re: Help: How to design large dimensions in Analysis Services - 09-08-2003 , 01:22 PM

Re: Help: How to design large dimensions in Analysis Services - 09-08-2003 , 04:13 PM

Re: Help: How to design large dimensions in Analysis Services - 09-08-2003 , 04:25 PM