Hi all!

I´m a newbie to OLAP. Currently I have a cube with around 15 dimensions and
some measures. All dimensions are shared dimensions and non-changing. The
aggregations within the cube currently take about 1 GB of storage space.

I have a DTS package that refreshes the cube as well as the dimensions every
30 minutes. Most of the dimensions are subject to frequent changes. For
example, I have a time dimension - about every minute a new entry is created
in my fact table with a new timestamp which means that the time dimension
based on that timestamp must be refreshed. Other dimensions change less
frequently, some maybe 5 - 10 times a day, some just once per week.

The DTS package that refreshes the cube and the dimensions takes about 5
minutes runtime. During this 5 minutes the CPU is permanently at 100% which
leads to very low system performance. So I´m looking for a better way to
keep the data current. If I use changing dimensions instead of the standard
ones, do I still have to refresh the cube? Or is an incremental update
enough? Do I have to refresh the dimensions as well?


Regards

Markus

It souds as if Time is the main issue. What's the lowest level of your time
dim? Can it be pre-populated?

tom @ the domain below
www.tomchester.net


"Markus Stehle" <markus.stehle (AT) web (DOT) de> wrote

Hi Tom!

Yes, indeed time is the main issue (BTW: I have several time dimensions:
business year, calendar year, ...). The lowest level is day. The levels
within each time dimension are year, quarter, month, day.

What exactly do you mean by "can it be pre-populated"?


Markus


"Tom Chester" <publicNOSPAM (AT) tomchester (DOT) net> wrote

I do not exactly unserstand what you mean. How would this time dimension
table look like? Could you please give me an example?

And yes, my fact table has a column of type datetime. My time dimension is
based on that column. So as nearly every minute new items are created in my
fact table, new members are also added to the time dimension.


Thanks

Markus



"Tom Chester" <publicNOSPAM (AT) tomchester (DOT) net> schrieb im Newsbeitrag
news:dNt5b.673$AO6.41254 (AT) news (DOT) uswest.net...

Treat your time dimension as you would any other dimension -- create a
dimension table that stores the members from which you will derive the OLAP
dimension. The time dimension in foodmart is a (cheesy) example. Then the
dimension can be pre-populated with a years worth of dates, avoiding
incremental updates. The fact table will contain a foriegn key joining it to
the time dimension table (as with other dims).

Even aside from the issue you are dealing with, this is an important best
practice.

tom @ the domain below
www.tomchester.net


"Markus Stehle" <markus.stehle (AT) web (DOT) de> wrote

Well, I´m not sure if I understood what you wrote. Does this mean I have to
create a separate date/time table that I join with my fact table?

My fact table currently looks like this (just the two relevant columns):
PK_ID int
Date datetime

Now it will look like this, won't it?
Fact table
======= Date table
PK_ID int =======
FK_DateID int --------------- PK_DateID int
Date
datetime

And the date table would contain the relevant dates, which means I have 365
entries per year in my date table?

So it would look like this?
PK_DateID Date
1 01.01.2003
2 02.01.2003
.....
365 31.12.2003


Thanks

Markus



"Tom Chester" <publicNOSPAM (AT) tomchester (DOT) net> schrieb im Newsbeitrag
news:dCJ5b.23$Qa.9653 (AT) news (DOT) uswest.net...

Yes, yes, and yes.

There are many side benefits: You can specify your own member names in the
dim table, rather than letting AS derive the names. You can have
"non-standard" rollups (i.e. 13 four week accounting periods). You can
assign member properties (i.e. Holiday Y-N). Best of all, it's sharable.

tom @ the domain below
www.tomchester.net


"Markus Stehle" <markus.stehle (AT) web (DOT) de> wrote

Ok, I think I got it.

Please allow me a last question: using the solution you suggested, I`ll
still have to incrementally update the cube, won`t I? But I don`t have to
update/refresh the time dimension any more?

Thanks

Markus


"Tom Chester" <publicNOSPAM (AT) tomchester (DOT) net> schrieb im Newsbeitrag
news:WVJ6b.21$Pw.21068 (AT) news (DOT) uswest.net...

Correct.

tom @ the domain below
www.tomchester.net


"Markus Stehle" <markus.stehle (AT) web (DOT) de> wrote