We have a 15 million row fact table being used as the base table for an
Analysis Services cube. The table is indexed according to the MS OLAP
documentation. Still, the cube processing time is several hours (we have
app. 300 aggregations). We do need all the data in the fact table and cannot
reduce the size of the fact table. Can someone recommend how to improve cube
processing performance?

"Brian P" wrote:

Some of the things to check:

- Is the cube schema optimized, using shared dimensions?

- Is the cube partitioned, to reduce processing memory?

- If there is a distinct count measure, it should preferably be in its
own cube, with few aggregations.

- How much of the time is taken in building the aggregations; and is the
processing buffer space enough to avoid the writing of temp disk files
(see paper below).


In case you haven't already studied this MSDN paper:

http://www.microsoft.com/technet/pro...ntain/ansvcspg.
mspx#EKAA

Published: June 1, 2003
By Carl Rabeler, Len Wyatt, Dave Wickert

Summary: This paper describes techniques you can use to optimize query
responsiveness and processing performance in Microsoft® SQL Server™ 2000
Analysis Services.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

The general rule of thumb that I use is that server-class hardware should be
able to process 1M fact table rows per minute. Your 15M rows should be done
in 15-30 min. Several hours can be caused by several items (see the list
that Deepak mentioned and the AS Performance Guide white paper, which he
graciously provided a link to). Another possibility is that you have
distinct count measures in the cube. If this is true then there are lots of
additional processing which needs to be done -- and it could account for the
longer processing runs. If this is the case, make sure you separate your DC
measure into their own cube and use a virtual cube to merge the non-DC
measures cube with the DC measure cube. This reduces the overhead and can
considerable help processing and querying DC measures.
--
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.


"Brian P" <BrianP (AT) discussions (DOT) microsoft.com> wrote

Is there a limit on the # of partitions that can be used for a cube? I have
about 32 partitions (by month) and I notice that the partitions with 2005
data are not included in the cube (I have processed all partitions).

"Brian P" wrote:

I have seen and designed systems with hundreds of partitions. In SQL Server
2000, the real practical limit around partitions is how much administrative
pain you are willing to tolerate. Much above a couple hundred partitions,
Analysis Manager becomes slower and slower to open -- really this is a DSO
issue (the underlying administrative API which Analysis Manager uses to
actually do its work). DSO is written in VB and the instantication of all of
the objects has more and more overhead. On the T3 project we had almost 600
partitions and it took AM close to 10 minutes to startup. We just kept one
running all of the time and used it as needed across multiple people.
--
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.


"Brian P" <BrianP (AT) discussions (DOT) microsoft.com> wrote

Just so I am clear, when you add a partition to a cube and refresh it, the
data for that partition should then show in the cube. Likewise, when you
delete a partition from a cube, the data for that partition will no longer
show in the cube.

"Brian P" wrote:

Yup. You got it.
--
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.


"Brian P" <BrianP (AT) discussions (DOT) microsoft.com> wrote

Dave - thank you for the information. Your input has really helped me better
understand the use of partitions and how they can improve performance with
larger amounts of data.

"Brian P" wrote:

BTW: 300 aggregations might be a bit too much unless this was an extremely
high complexity cube. I would strongly recommend that you look at the AS
Performance Guide
http://www.microsoft.com/technet/pro.../ansvcspg.mspx
and read the section on "number of aggregations".
Also Len Wyatt talks about the tendency to over aggregate in his perf talk,
see here:
http://www.microsoft.com/downloads/d...DisplayLang=en
for a download.
--
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.


"Brian P" <BrianP (AT) discussions (DOT) microsoft.com> wrote