Automatically process a cube which is on another server

Hello

I want to create a DTS package on my server #1 which will process a cube on my server #2. The problem is that when I create the DTS package, I select the "Analysis Services Processing Task" and then, the only choice I have in the left box "Select the object to process" is the local server (server#1). How could it be possible to select my server#2 in that box ?

I know I can create connexions ... could that be part of the solution ?

I'm using SQL SERVER 2000 on server#1 and SSAS2000 on server #2.

Mike

When you're editing a DTS package, I believe the Analysis Services Processing Task will display servers that you've registered in Analysis Manager with your current login profile.

Open Analysis Manager and register the server that you intend to process cubes on, then try the package design again.

Are you logged directly into server #1 or term-served into it? If not, just be aware of the classic problem of DTS package design & deployment. Since Analysis services and DTS package design use live connections, what may work during DTS design time may not work once you, for instance, schedule the package to run as a job step, due to differences in permissions between the developer account credentials versus the account credentials of the service account which SQL Server Agent starts up as.

I hope this helps. I remember suffering through my first DTS package design sessions all too well.

CJB

|||

Enterprise Manager only allows me to register a SQL Server , but the database is on my server#1. My server #2 just has Analysis Services installed, so it is not a SQL Server.

I tried to register my server#1 with the Analysis Manager, and it worked ( I think it's because I have a sample cube on my srever#1), but that didn't change anything.

I still can't do what i need to.

Mike

|||

I think you're looking for the TreeKey setting. It's been so long since I've done DTS and AS2000 that I'm a little rusty. But have a look at:

http://msdn2.microsoft.com/en-us/library/aa902667(sql.80).aspx

Search for "TreeKey" then look at the image above that section. I believe if you set it to "YourServerName\YourCubeName" you should be able to process a cube on another server. I think you'll need a dynamic properties task to accomplish that.

And you might look at:

http://blogs.msdn.com/bi_systems/articles/141632.aspx

|||

thanks for your help, furmangg, but I am not familiar with dynamic properties tasks... What are they ?

And I never used ActiveX scripts, like it is suggested there http://msdn2.microsoft.com/en-us/library/aa902667(sql.80).aspx

Could you give me more detailed explanations, please ?

|||

You need a dynamic properties task to set the TreeKey property of your Analysis Services Processing Task. Here's more on dynamic properties tasks:

http://msdn2.microsoft.com/en-us/library/aa933528(sql.80).aspx

(If there's a UI way to hardcode the TreeKey for the AS Processing Task without a dynamic properties task, then you won't need one.)

I think you'll only need ActiveX script if you don't want to hardcode your TreeKey and want it to be more dynamic like that article suggests.

|||

Ok, I think I understand the problem now.

You can change the treekey setting by entering "Disconnected Edit" mode in the DTS designer. The drawback here is that you won't be able to double click your Analysis Services Processing Task to edit it. But that's okay, since it's not working anyway.

Right click anywhere in the background while designing the DTS package.

A dialog should appear.

Select "Disconnected Edit..."

Expand "Tasks"

Select the Task which is processing the cubes.

A collection of Task properties will show up in the right hand panel. The bottom one is "TreeKey", and its syntax is

servername\databasename\CubeFolder\cubename

So you might edit this to read (note that spaces here are fine, the package will "wrap" them properly at runtime)

mike8srv\FoodMart 2000\CubeFolder\Sales

Inspect the other property values, particularly DataSource (should match the name in Analysis Manager "Data Sources"), Fact table, and ProcessingOption (enumerated list: 0 = full, 1 = refresh, 2 = incremental).

|||

Maybe I was getting close of a solution with furmangg, but with your last post, you really solved my problem John !

I wouldn't have imagined a simpler solution !

thanks for your help to both of you - i really appreciate