MS SQL Server OLAP - Re: Question

Author

Re: Question

XXX

2005-09-21, 11:24 am

Any one?

"XXX" <sa@nomail.com> wrote in message
news:eYRIQOfvFHA.3720@TK2MSFTNGP14.phx.gbl...
>I am building a cube which will needs to count on it's membership by
>various dimenisions like gender, income, education, age, country, etc.
>
> What is a good way to go about it? Build separate dimensions for each or
> Create virtual dimensions by creating a Membership dimension with Member
> properties.
>
> What is the benefit of one over the other?
>
> The membership table has over 50 million rows currently.
>
> Thanks.
>

Deepak Puri

2005-09-22, 3:23 am

With over 50 million members, the Member Property/Virtual Dimension
approach only sounds feasible with the 64-bit version of AS 2000. The
Analysis Services Operations Guide provides guidelines for estimating
dimension memory usage, according to which (below) > 5 GB RAM may be
needed.

However, since AS 2005 doesn't load all dimension data in memory, that
might be feasible even with 32-bit servers. This is the "minidimension"
dilemma, mentioned by Marco Russo in this blog entry, but no guidelines
are given. The only reference point that I can cite is Project REAL,
which mentions a 7 million-member Product dimension table.

http://sqljunkies.com/WebLog/sqlbi/archive/2005/08.aspx[color=darkred]
...
MINIDIMENSION

A minidimension table usually contains some attributes of a larger
dimension tables just to improve accessibility to data in fact table.
Rows are less than rows in large dimension table because only the
existing attribute permutations in the real dimension defines the rows
for the minidimension table. In other words, it's a way to avoid the
large-dimension pitfall of AS2000.

Now, with SSAS 2005 I was tempted to put any attribute of a large
dimension (imagine one table with 2 millions of customers) into the
dimension itself; but the fact that this new release has not anymore the
scalability problem that affected large dimensions in AS2000, it could
be still useful to use minidimensions to improve query performance.

I still have not faithful numbers to define a "best practice" way (or to
define a tradeoff to help choosing between minidimensions and regular
"single" dimensions). Is there someone with more experience in this
area? And, again, how to name a minidimension in a meaningful way?
...[color=darkred]

http://www.microsoft.com/technet/pr...ntain/anservog.
mspx[color=darkred]
Microsoft SQL Server 2000 Analysis Services Operations Guide
...
Memory Consumption By Dimensions

On startup, Analysis Services loads into memory all MOLAP dimension
members for all databases on the Analysis server, along with all of
their member properties, to help increase query responsiveness. By
default, MOLAP, HOLAP, and ROLAP cubes contain MOLAP dimensions. If you
create a ROLAP cube and specify ROLAP dimensions, these dimensions are
not loaded into memory. New dimensions and dimension members are added
to memory as they are created. The amount of memory consumed by an
existing dimension in memory is adjusted only during dimension
processing. This means that large MOLAP dimensions can consume a
significant amount of virtual memory on the Analysis server, reducing
the address space that is left for other tasks. The amount of memory
required by dimension memory tends to increase over time as new
dimensions and dimension members are added to the cubes in the Analysis
Services instance.

For an estimate of the memory space required to hold each dimension, you
can look at the sizes of the files that hold the dimension structure in
the file system. For shared dimensions, this dimension structure
information is stored in four types of files: .dim, .dimcr, .dimprop and
.dimtree. You can find these files in the database folder for the cube,
which is stored in the Analysis Services Data folder. The amount of
memory required for dimension memory is approximately equal to the sum
of the sizes of these files. For more information on these file types,
see Appendix K: "Data Folder Structure."

If you need to estimate the space required before the dimensions have
been defined, such as when planning a hardware purchase, you can use the
following formula as an approximation:

DimSize = CMembers*(61 + 4*CLevels + Size(name) _
+ Size(key)) + 4*CProps + Size(props)

where:

CMembers

The total number of members in the dimension.

CLevels

The number of levels in the dimension, including the All level.

Size(name)

The average size required to hold the member names. For example, a
10-character string stored as Unicode requires 20 bytes.

Size(key)

The size required to hold the member key. Example: an integer key
requires 4 bytes. If the member name is the same as the member key,
Size(key) is zero.

CProps

The number of member property settings in the dimension for all levels.
For example, if a level with 1000 members has two properties on each
member, there are 2000 property settings for that level. The member
property settings are used to identify which member property values are
referred to by the member.

Size(props)

The size required to hold the distinct member property values for all
member levels. Remember that member properties are stored as Unicode
strings, and each unique string is only stored once. For example, a
customer gender property with possible values of Male, Female, and
Unknown requires only 34 bytes of storage (17 characters x 2 for
Unicode) no matter how many times they are referred to.

Note For the 64-bit version of Analysis Services, the formula above
should be 8*CLevels and 8*CProps because an integer is 8 bytes, rather
than 4 bytes as on a 32-bit system.
...[color=darkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Sponsored Links