We are currently evalutating MSAS to be an alternate reporting structure for
our data warehouse. We have 2-3 billion rows in our fact tables and we're
trying to move that into MSAS. My question is: has anyone else done this
and if so, what methods have you found to be best.

Couple things about our project:
1) Our data is 2 years of POS data stored, so I've been working with
partitions broken out by week.
2) We're open to using a cluster of servers and would love to hear someone's
experience with this.
3) We'd need to have shared dimensions that can be updated without having to
update the entire cube. (Stores, Items, etc)

Thank you,
Malcolm

We have 2-3 billion rows in our fact tables
of thumb" your mileage may vary -- is that on server quality hardware you
should be able to process 1 million rows per minute so long as you haven't
over aggregated your cube and you've ran the optimize schema tool. These
topics are discussed in these two white papers
http://www.microsoft.com/technet/pro.../ansvcspg.mspx
http://www.microsoft.com/technet/pro.../anservog.mspx

1) Our data is 2 years of POS data stored, so I've been working with
partitions broken out by week.
2) We're open to using a cluster of servers and would love to hear someone's
experience with this.
It's an oldie but goodie.

3) We'd need to have shared dimensions that can be updated without having to
update the entire cube. (Stores, Items, etc)
information. However, there isn't a free lunch here. If you do an
incremental process on a changing dimension, then you will kick off the lazy
aggregator and it does a sequential rebuild of the flex aggs. Which isn't
good. My general advice to customers is to minimize the use of changing
dimensions -- but they will do what you want, i.e. allow the changing of the
hierarchy without full reprocessing everything.

However, are you making real changes; or just adding new members? One of the
facts of AS that aren't well known is that you can always *ADD* new members
with an incremental process of a non-changing dimension. Changes are what
causes problems.

Good luck with your new system. Enjoy.
--
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.

"Malcolm Toon" <mtoon@ no-spam.retailsolutions.com> wrote