MS SQL Server OLAP - Structure Advice Please

Author

Structure Advice Please

Patricia

2005-09-19, 11:23 am

Hello,
I do not know much about Analyst Services, however I have been asked to look
into Storeage and update for a Analyst Services System.

The Database itself is 189gb, and each day we get around 200k rows of
information that is loaded between 7.30 and 8.30 am.

The main issue is that we have only 30 minutes to update the cubes, and the
last time we did a full process of the cube, it took 22 hours.

Could someone please give me advice on how to handle this volume, and a way
of cutting down on the processing time.

Thanks
P

Darren Gosbell

2005-09-20, 7:23 am

Sounds like you need to look into incremental processing and possibly
partitioning.

This could be a bit tricky to try and explain in a newsgroup post, start
out by having a look at "Incrementally Updating cubes" in BOL.

--
Regards
Darren Gosbell [MCSD]
< dgosbell_at_yahoo_do
t_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <B7270D9F-B660-4D1C-9FAD- 457FD237DD91@microso
ft.com>,
Patricia@discussions
.microsoft.com says...
> Hello,
> I do not know much about Analyst Services, however I have been asked to look
> into Storeage and update for a Analyst Services System.
>
> The Database itself is 189gb, and each day we get around 200k rows of
> information that is loaded between 7.30 and 8.30 am.
>
> The main issue is that we have only 30 minutes to update the cubes, and the
> last time we did a full process of the cube, it took 22 hours.
>
> Could someone please give me advice on how to handle this volume, and a way
> of cutting down on the processing time.
>
> Thanks
> P
>
>
>

Dave Wickert [MSFT]

2005-09-21, 7:23 am

There is an entire section on how to optimize processing in the AS
Performance Guide
http://www.microsoft.com/technet/pr...n/ansvcspg.mspx
My general rule-of-thumb is that most server-quality systems using normal
techniques as outlined in the Guide should be able to do about 1 million
rows per minute.

So clearly even if you can get your processing down, you aren't going to be
able to get a full process in the time window you are asking for. Thus,
incremental processing is your only real option. BOL has a good description
of it.
--
Dave Wickert [MSFT]
dwickert@online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Patricia" < Patricia@discussions
.microsoft.com> wrote in message
news:B7270D9F-B660-4D1C-9FAD- 457FD237DD91@microso
ft.com...
> Hello,
> I do not know much about Analyst Services, however I have been asked to
> look
> into Storeage and update for a Analyst Services System.
>
> The Database itself is 189gb, and each day we get around 200k rows of
> information that is loaded between 7.30 and 8.30 am.
>
> The main issue is that we have only 30 minutes to update the cubes, and
> the
> last time we did a full process of the cube, it took 22 hours.
>
> Could someone please give me advice on how to handle this volume, and a
> way
> of cutting down on the processing time.
>
> Thanks
> P
>
>

Patricia

2005-09-21, 7:23 am

Thanks Guys

"Patricia" wrote:

> Hello,
> I do not know much about Analyst Services, however I have been asked to look
> into Storeage and update for a Analyst Services System.
>
> The Database itself is 189gb, and each day we get around 200k rows of
> information that is loaded between 7.30 and 8.30 am.
>
> The main issue is that we have only 30 minutes to update the cubes, and the
> last time we did a full process of the cube, it took 22 hours.
>
> Could someone please give me advice on how to handle this volume, and a way
> of cutting down on the processing time.
>
> Thanks
> P
>
>

Sponsored Links