Hello everybody,

My question is as follows:
Is it possible to delete members from dimensions without reprocessing the
entire cube?

I have built a datawarehouse and am loading the data from the datawarehouse
into the MSAS cubes. The cubes are partitioned by month.
However I only want to have the past 2 years (24 months) in my datawarehouse
and cubes. My datawarehouse fact tables are also partitioned by month and all
of my dimensions have history (valid from / valid to). So deleting any data
prior to the 24 month boundry is simple in my datawarehouse.
In my cubes it is also simple to delete the obsolete partitions, but here
comes my problem.
The dimensions in my cube have all the history that was in my dimension
tables.
Including those which have become obsolete.
And my cubes are way to big (10GB) to do a full process every month.

Can anyone help me?

Sorry. You can't. To delete members requires a full process of the
dimension.
Doing a full process on any dimension (regardless of its properties) will
force the reprocessing of any partition that uses that dimension.

10GB is really not that big. It should not take a significant amount of time
to do a full reprocess.
Have you ran the optimize schema wizard in the cube editor and reduced the
join complexity of the SQL statement to make it as close to a table scan as
possible? My general rule-of-thumb is that with server-grade hardware with a
reasonable I/O subsystem, on a well-optimized cube which hasn't been
over-aggregated, pulling from SQL Server either locally or on a good-quality
LAN, should process about 1 million rows per minute. If you aren't somewhere
around that number, then you need to look at how to better tune your
processing. This is talked about extensively in the AS Performance Guide
available here:
http://www.microsoft.com/technet/pro.../ansvcspg.mspx

Hope that helps.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Mike van der Niet" <MikevanderNiet (AT) discussions (DOT) microsoft.com> wrote in
message news:E898DB74-CD6F-456F-97BD-8CA6D5042C2B (AT) microsoft (DOT) com...

Thanks Dave, I will read the performance guide and see if there's room for
improvement. But I have several distinct count cubes which greatly slow the
processing and most probably won't reach the 1 mln rows per minute.
I have several databases. One of them is about 10 GB.
This database consists of 13 dimensions (ranging from 2 to 10000 rows) and 6
cubes with 24 partition. Each partition has an average of 7 to 8 mln rows.
3 of these cubes are a distinct count cube (1 measure).

So you can see that processing this isn't something i want to do on a
regular basis.

But again thanx for the pointer and I will look into it.

Kind regards,

Mike van der Niet
Business Intelligence Consultant
Capgemini

"Dave Wickert [MSFT]" wrote:

Sorry. I didn't see distinct count in your earlier post. Yes, it places a
considerably heavy load in both the processing time, disk usage, and
complexity of SQL generated. The 1M rows per minute was definitely not with
DC.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Mike van der Niet" <MikevanderNiet (AT) discussions (DOT) microsoft.com> wrote in
message news:CBE38DA1-B408-4971-A3C2-47D4C0210ACC (AT) microsoft (DOT) com...