Hi Roman,
Let me see if I can help clarify this. There are a couple of different
properties you can work with here that will impact what happens when you use
the Storage Design Wizard to define aggregates.
The first property you want to work with is the EstimatedSize Property on
the DatabaseLevel object. This refers to the number of members that will be
in a given level. This is not a partition setting, it is at the database
level. Your code would connect to the server and then select a database and
then enumerate through dimensions and then the levels of those dimensions to
work with that property. For this property, I'd recommend that you set the
value to be equal to the number of dimension members that would exist in any
single partition. For example, if you are doing monthly partitions in your
cubes, I would set this property to 1 on the month level. I'd also set the
property to 1 for the year (because each month belongs to only one year).
If I had a day level in my time dimension, I'd probably set it to 30 or 31
since no month would have more than this number of days. By default
Analysis Services will set this property to the total number of members that
exist in the level when the dimension is processed. For of all of your
non-time dimensions that might be fine, but if you're partitioning by time,
you'll want your dimension counts to be reflective of the number of members
that might exist in any single partition. In some cases, setting this
property on the dimension level can take some significant processing time.
The second property you would be interested in is actually on the partition
object and it is called EstimatedRows. I would set this value to be equal
to the number of records that actually exist in the partition.
An interesting situation might arise when you have for example a dimension
like customer in which not all customers make a purchase every month and you
are doing monthly partitions. In this case, I would follow the rule of
setting the EstimatedSize property on the dimension level to be equal to the
number of members that will typically exist in any partition. So in this
case, I would change my level counts for the customer dimension and for the
time dimension.
I'd also recommend that you download the SQL Server Accelerator for Business
Intelligence. When you download the Accelerator and install it, a "tools"
directory will be created on your disk (in addition to a tools folder via
the startup menu). In the "tools" directory you will find a utility called
the Partition Aggregation Utility. This utility comes with the code for it
and it shows you how to set these properties programmatically. In fact, you
could probably just use the utility as-is for your needs. If you had any
suggestions on improvements to the utility, please just let us know. We are
working on making the tool available on it's own from the download center
and when it's available from there we'll post an announcement in this
newsgroup.
Hope that helps,
Sean
--
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.
The second property of inter
"Roman" <roman.domin (AT) adastra (DOT) cz> wrote
Quote:
Hi,
in Microsoft article "Microsoft SQL Server 2000 Analysis
Services Performance Guide" from june 2003 is mentioned
that since SP3a it is posible to reduce number of
aggregations by decreasing the MemberCount property for
each level in partition. I tried it but DSO said that
MemberCount property is not supported. Has anybody any
experience or advice ?
Roman
|
MemberCount property for dimension levels in partitions -
12-29-2003
, 09:08 AM
Linear Mode
