Hi all,

We'd like to programatically automate post-install configuration and
optimisation of Analysis Services databases, i.e. we'll be installing
the same relational database schema and Analysis Services databases
in a variety of customer sites of widely-varying data characteristics
(specifically, row counts in the fact tables, population complexity
of the dimensions, including Parent-Child dimensions); i.e. no single
aggregation design fits all purposes. So the plan is as follows:

A) Install relational schema and Analysis Services schema;

B) Perform back-record data load into relational schema;

C) Design Storage on the AS cubes;

D) Perform initial full process of AS cubes;

E) Switch to periodic incremental updates of AS cubes.

Ideally, we'd like this to be as hands-off as possible; and one way
would be to programmatically call (presumably through DSO) the
equivalents of the Design Storage Wizard. So, for each cube:

1) Update the dimension counts and fact table row count (for some
cubes, these values would be replaced by parameters from a load file,
where we wish to force design based on specific rowcounts);

2) Design storage, with the storage mode (e.g. MOLAP) and performance
gain (e.g. 40%) drawn from a load file;

3) Process the cube.

Step 2) is the most interesting one; it's not too obvious (to me, at
least!) from the DSO object model precisely which methods should be
called to reproduce the behaviour of the Design Storage wizard. If
anybody has already trod this path and is willing to share some
insights, I'd greatly appreciate it! :-)

Cheers,
--
Use Reply-To address or replace deadspam with hotmail

We have developed some utilities with source code that will show you the
code that is used to perform these types of processes. The application and
the source code is downloadable with the SQL Server Accelerator for Business
Intelligence at www.microsoft.com/solutions/bi

Once you install the Accelerator, there will be a folder on your hard drive
that has tools and utilities. One of them is called the Partition
Aggregation Utility. Again, it includes all the source code and has examples
for updating partition and dimension counts, building aggregations, etc.

Sean


--
Sean

--
Sean Boon
SQL Server BI Product Unit

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

"Koan B" <koangee (AT) deadspam (DOT) com> wrote