how to copy/replicate/backup OLAP cubes ?

Question

how to copy/replicate/backup OLAP cubes ?

Kicking_Fish0713

SSC Eights!

Points: 896
More actions
April 21, 2008 at 9:17 pm

#121819

Hi
We got production server with a SQL server and OLAP server/service on it. I would like to set up the same things on a test server for testing. No problem with the SQL server. Anyone knows how to copy/replicate/backup OLAP cubes ?
Thanks in advance.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

happycat59 One Orange Chip Points: 29389 More actions · Answer 1

The simplest way is to use the backup command from within Management Studio. Just right click on the database and select backup. You also have the option to save the script - at the top of the screen that appears is a script option. This XLMA script can be run from SQL Server Agent or Management Studio

You could also create an Analysis Services project within Visual Studio based on the cube (it is one of the project types you can select). Once you have the project in Visual Studio, you can deploy it to a location of your choosing - just set the server name etc in the project properties and away you go.

Kicking_Fish0713 SSC Eights! Points: 896 More actions · Answer 2

I'm using SQL Server 2000.I don't seem to have a shortcut to the SQL Server 2000 Management Studio anywhere.

happycat59 One Orange Chip Points: 29389 More actions · Answer 3

Sorry - I assumed you were using AS 2005.

For AS 2000, everything is done using Analysis Manager. You should be able to find this in the SQL Server program group.

Connect to the server, right click on the database and select archive (this is the equivalent of a backup). To restore, connect to the desired server, right click on the server name (I think, or it might be the "database" node - I don't have AS 2000 installed anymore, this is from memory).

During the restore, if the database already exists, you will be prompted/warned about this and also given a chance to restore as another name.

Another option you can use is to copy/paste the database. Provided you use the same instance of analysis manager, you can copy and paste between servers. If you are copying to the same server, you will be warned and given the option to change the name.

Kicking_Fish0713 SSC Eights! Points: 896 More actions · Answer 4

Kicking_Fish0713

SSC Eights!

Points: 896

April 23, 2008 at 1:42 am

#805900

Thanks a lot!Good day and God Bless!:)

Kicking_Fish0713 SSC Eights! Points: 896 More actions · Answer 5

Hi again.I already copied the cubes from production to test server.My question is that since the database has changed,do I need to create a new cube?

Dave Balsillie SSCertifiable Points: 5589 More actions · Answer 6

Are you talking about an MSAS database or the SQL database (the data source).

Assuming you're talking about the SQL database, no, just change / update your SQL connection string. This assumes of course, that there's trhe same tables in the new database.

Kicking_Fish0713 SSC Eights! Points: 896 More actions · Answer 7

Thanks Dave.Aside from database,I also changed the source table that's why i need to recreate my cube.The problem is when i try to create new dimension i encountered this error:

"Unable to count the members of the xxxx level".Pls help.

Dave Balsillie SSCertifiable Points: 5589 More actions · Answer 8

Ok, I'd need more information.

Check the SQL Statement (during the process) that is generating the error. Copy / paste it into Management Studio, execute it there and debug your issue.

Don't use different table names between environments. For a different delivery, that's fine, but you shouldn't need to update the cube info, as you move. (Way too much work.) Either use a different database, or if you really want it in the same databasse, use a different schema (user ID). You can set the datasource view to filter on schema.

For example, we support dozens (soon to grow to hundreds) of different deliveries, all drawing from a common set of base tables. Each of the deliveries have different tweaks and filters, to support different requirements and objectives. (Yes, we want them separate.) To accomplish this, a series of views is used to access the base tables, where each "view set" is saved under a different schema name. This allows the primary data filters / customizations to be managed centrally in the database, with very little overhead. You can do the same thing for prod, qa, and development environments very easily. When creating a new delivery, copy one of the view sets, perform you tweaks (data & cube) and you've got a whole new implementation.

Kicking_Fish0713 SSC Eights! Points: 896 More actions · Answer 9

Thanks to happycat59! and Dave Balsillie! Good day and God Bless!