SQL Server 2005 Analysis Service (SSAS) Partial Synchronization

I'm new to SSAS (this is my first project that involves ssas).

I have a regular a SQL Server 2005 server (say Blah) that run database instance and sql server analysis service. I want to synchronize some of the data in Blah (based on some condition) to another server, Blah2. Partial data sync is quite straightforward with the help of replication server. However, I'm not sure how to do a partial data synchronization for the analysis service.

I have a table in Blah database that list all of the cubes in its analysis service. I then need to filter this table to list all the necessary cube and this is ok. But I'm sure how to continue from there.

I've looked into the SSAS Database Sync Wizard but I couldnt find any command line tools for this or a way to run this as procedure in SQL Script (I will need to do this as a regular sql server job, so it's necessary not to rely on gui). Even if I want to use the gui, there doesnt seems to be a way to filter the cube/measurement from the gui.

I'm thinking of getting the cube, measurement, data source view, etc dynamically but I cant find a way to get these definitions dynamically from sql script. I'm trying to do simple openquery to get list of cube in sql server screen with this (olap_server is a linked server to the ssas):

select *
from openquery(olap_server, 'select [CATALOG_NAME]
from $system.dbschema_catalogs')

with no luck. I got the "An error occurred while preparing the query "select [CATALOG_NAME] from $system.dbschema_catalogs" for execution against OLE DB provider "MSOLAP" for linked server "olap_server"." error instead.

Is th开发者_运维百科ere any straightforward way to do this task?

I am looking at doing something similar...

You can synchronize without using the Database Sync Wizard gui by issuing an XMLA Synchronize command against the Target database as described here http://msdn.microsoft.com/en-us/library/ms187156.aspx or for 2005 here: http://msdn.microsoft.com/en-us/library/ms187156(SQL.90).aspx

Your SQL Server job will need to have a step of type "SQL Server Analyis Services Command"

An example and some more background info is here: http://dwbijourney.blogspot.com/2008/01/ssas-database-synchronization-for.html