Hi all!

I'm quite new to OLAP. I'm using MS Analysis Services 2000 to create and
manage cubes. Currently I have 5 cubes, each cube only has one partition. In
order to keep the cubes current I have a DTS package that refreshes all
cubes every 30 minutes. Processing the cubes takes nearly 5 minutes, during
this period the CPU is at 100%. So I'm looking for ways to speed up the
processing.

First of all, currently my DTS job refreshes all cubes - I also tried to use
an incremental update, but I could not see any difference concernng the
processing speed (I expected the incremental cube update to be faster than a
refresh, but it was not). What is the preferable solution?

Secondly, I only have one partition per cube. For example, I have a sales
cube that contains sales data for the years 2000 - 2003. Was it possible to
create separate partitions for each year and to only refresh (or incremental
update) the last/current one?

I also have a lot of dimensions - currently, all dimensions are non-changing
dimensions and most of them are shared (except for those that are only used
in one specific cube so they are private). But some dimensions might change
within the lowest level (most of the dimensions only have 2 levels,
including the 'all' level). These are no frequent changes, maybe three new
entries per month. So currently the DTS packages also incrementally updates
the dimensions associated with the cubes. Is it possible to exclude certain
dimensions from an incremental update? E.g. I have 3 time dimensions (all
have year, quarter, month, day) that use pre-populated values from a
separate date table. These dimensions don't have to be incrementally
updated, but I could not find a way to exclude them in my DTS package - so
currently they are updated and using CPU power although there is no need for
it.

What else might help to speed up cube processing?


Thanks in advance!

You will find lots of good suggestions and practices in the
Analysis Services Performance Guide. You can find it at
this link:
http://www.microsoft.com/technet/treeview/default.asp?
url=/technet/prodtechnol/sql/maintain/optimize/ANSvcsPG.asp

Among other topìcs it discusses partition design,
aggregation desgign and the impact of changing dimensions.

HTH,
Brian

The article suggested by the other individual is
excellent. Has lot of ideas. Briefly speaking
1. Partitions will help (say yearly: 2000, 2001, 2002 and
2003). Incremental processing can be tricky, based on your
data, could lead to duplicate data. Refresh data could be
another option.

2. You might want to Optimize the cubes, if possible. That
will help you a lot during processing.

HTH

Sunil Kadimdiwan

Indeed the article was very useful - although I'm missing personal
experience to decide what might be a better solution for my problems and
what might not be.

Based on the article, I'll try the following now:
- decrease number of aggregations (I designed my aggregations based on "the
more, the better" rule which leads to high disk space usage and longer
processing periods) based on usage-based optimization
- create additional partitions based on MOLAP; currently I'll use partitions
by year, another possibilty was quarters or even month
- refresh only the current partition and leave the old ones untouched (as I
already mentioned, I could not see a big difference between incremental
update and refresh, so as refreshing is less error-prone concerning
duplicate data it might be the better choice?)
- avoid refreshing the dimensions; strictly speaking I only have one
dimension (out of about 15) that needs to be refreshed - I don't know if
it's possible to create a DTS package that only refreshes/incrementally
updates one dimension, anybody knows? All the other dimensions I will
refresh manually if they changed (which might occur some times a month)



"Sunil Kadimdiwan" <sunil_kadimdiwan (AT) hotmail (DOT) com> schrieb im Newsbeitrag
news:b3f801c3814e$90e7fa20$a601280a (AT) phx (DOT) gbl...

Month-based partitions will be a good idea.
I would get started with 10% aggregations or less. Then do
the usage-based optimization.

You can use DTS to incr. update a dimension. (Full process
of any dimension and then you have to refresh/full process
all the relevant cubes anyway, so be mindful of that).

Other ideas seem okay to me.

HTH

Sunil Kadimdiwan

Hi again,

just another question: I actually have two time dimensions in my cube, one
is the business year and the other one is the calendar year. What happens if
I create month-based partitions based/sliced on the calendar year? Is the
business year also included? Or do I have to create two partitions for one
month, one for calendar year and one for business year?

thanks again


"Sunil Kadimdiwan" <sunil_kadimdiwan (AT) hotmail (DOT) com> schrieb im Newsbeitrag
news:09d301c38161$1f973580$a301280a (AT) phx (DOT) gbl...

Hi Jochen,

why do you have two dimensions? I have business year and calendar year in
one dimension like this:

calendar year
cy 2002
jan 2002
feb 2002
mar 2002
apr 2002
01.04.2002
...
...
fiscal year
fy 2002
apr 2002
may 2002
01.05.2002

The element "apr 2002" has two parent elements: cy2002 and fy2002


Snork




"Jochen" <jochen_g (AT) hotmail (DOT) com> schrieb im Newsbeitrag
news:uilhojigDHA.1932 (AT) TK2MSFTNGP11 (DOT) phx.gbl...

That's well and good, but be aware that multiple hierarchies within one
dimension is a non-substanative construct, no different than multiple
dimensions.

tom @ the domain below
www.tomchester.net


"Snork" <cmelk (AT) misag (DOT) com> wrote

Hi,

I do not know how I can use one dimension for both the fiscal and calendar
year - could you please explain? As I already mentioned I'm quite new to
OLAP so what I knew so far is that I have two create two dimensions for my
need :-)

thanks



"Snork" <cmelk (AT) misag (DOT) com> schrieb im Newsbeitrag
news:#PaXcmBhDHA.2192 (AT) TK2MSFTNGP09 (DOT) phx.gbl...