Migrate OLAP cubes

[umeman]

Is their a way to migrate OLAP cubes between environments
such as from development to production.

This is possible if you can attach both OLAP servers
and the perform a copy paste. However if these environments
are totally detatched is there some way to copy the cube

Thanks
-u-

 

[MDXer]

There are a few diffrent ways you can move OLAP databases between servers.

1) Archive & Restore. This method will archive all cubes roles and dimensions within a DB. THis will create a cab file with all the necessary files needed. You can then restore this cab file to another server. The benifit of this method is you generally do not have to reprocess the cube after it is restored (I have seen some bizarre instances where reprocessing was needed bt I believe it was related to servicepak issues). To archive right click on the database you want to archive then select archive database. After the archive is done you simply move the cab file and restore. A restore is done by right clicking the server you want to restore a DB to and selecting the restore menu item.

2) You can copy and paste the cube structures. Unlike the Archive and restore where everything within the DB is moved, this method will allow you to select which items are moved. This method can be slower and requires that the machines exist within the same domain for permission reasons. Right click the DB or Cube you want to move and select Copy. In Analysis Mangler go to the Server you want the cube moved to and Select paste, if done on the DB node all items will be moved if done at the Cube level only the CUBE (related cubes if it is a virtual) and it's required dimensions will be moved. the WILL NOT BE PROCESSED after the transfer is complete.


The only issue to the Archive and restore method is that if a file within the cube exceeds 2GB the db will not archive. You will rarely get a file in excess of 2GB unless you have used a distinct count measure and have not properly isolated it from other measures. This issue can be fixed with basic redesign of the cube and the manner in which the distinct count measure is used.

HTH


"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[umeman]

Hi thanks for your reply,

I cannot use the copy/paste method as the servers are not visible to each other.

I archive and restore sounds good but we want to move just one cube at a time not the entire repository. I would still like to know how to create and restore an archive as I don't know how to do this either

Thank you
-u-

 

[hudi]

Hi,

easiest way to do it is to use Meta Data Scripter.
This is an add-in for Analysis services and it makes .vbs file from any OLAP object you select.
That is, if you select a dimension, cube or whole OLAP database. Then you just run the VBS file and it will create you the object you have scripted.

And if you want to move the OLAP database, all you need is to change server name in the script.

You can find the Meta Data Scripter in the SQL Resource Kit. You have vb source for it. Just make a .dll and register it.
I've done it the same and can't imagine how I did make without it.

Bye,
Andrej