Using SQL Agent to automatically refresh cubes

[SQLHacker]

I am using SQL Server 2005, specifically Analysis Services.

The cubes are already created, but I need to update (refresh) them regularly using the SQL Server Agent to schedule a job.

This was a very easy task in SQL 2000, but I can't figure out how to create a job that will process AS cubes in 2005. Does anyone have a reference for how to set this sort of thing up in SQL 2005?

 

[SQLHacker]

Okay, I found a way to accomplish the task, but now I can't figure out the correct XML(A) text to get all of the Dimensions into one batch script. When I try to execute the following script, I get an error stating:
"The Dimensions element at line 11, column 18 (namespace

http://schemas.microsoft.com/analysisservices/2003/engine)

cannot appear under Envelope/Body/Execute/Command/Batch/Process/Object."

Here is the script:
<Batch xmlns="

http://schemas.microsoft.com/analysisservices/2003/engine">

<Process xmlns:xsd="

http://www.w3.org/2001/XMLSchema"

xmlns:xsi="

http://www.w3.org/2001/XMLSchema-instance">

<Object>
<DatabaseID>...</DatabaseID>
<Dimensions>
<DimensionID>Person Basic</DimensionID>
<DimensionID>t Person Training</DimensionID>
<DimensionID>t Person Training Expense</DimensionID>
<DimensionID>t Person Property Hist</DimensionID>
<DimensionID>Dim Person Property Card</DimensionID>
<DimensionID>Dim Property</DimensionID>
<DimensionID>Dim Person Absence</DimensionID>
<DimensionID>Volap Person Benefit Fact Table</DimensionID>
<DimensionID>Volap Person Illness Injury Fact Table</DimensionID>
<DimensionID>Volap Person Termination Fact Table</DimensionID>
<DimensionID>Volap Person Nomination Fact Table</DimensionID>
<DimensionID>Volap Person Goals Fact Table</DimensionID>
<DimensionID>Volap Person Previous Employment</DimensionID>
<DimensionID>Volap Person Application Fact Table</DimensionID>
<DimensionID>t Person Application Evaluation</DimensionID>
<DimensionID>Volap Person Application Fact Table 1</DimensionID>
<DimensionID>Dim Requisition</DimensionID>
<DimensionID>Recruiting Expense Fact Table</DimensionID>
<DimensionID>Volap Person Certification Fact Table</DimensionID>
<DimensionID>Volap Person Competency Fact Table</DimensionID>
<DimensionID>Volap Person Education Fact Table</DimensionID>
<DimensionID>Volap Person Base Pay Fact Table</DimensionID>
<DimensionID>Volap Person Other Pay Fact Table</DimensionID>
<DimensionID>Volap Person Absence Plan Fact Table</DimensionID>
<DimensionID>t Recruiting Expense Allocation</DimensionID>
<DimensionID>Dim Requisition Evaluation</DimensionID>
</Dimensions>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Batch>

Can anyone suggest a different way to write the XML so that it will accept all the Dimensions?

 

[MDXer]

if you go into SSMS and select your dimension in the summary tab and then Select Process it will open up the process dialog box. At the very top of this box is a button to script the process. THis should give you what your after.

The better way may be to build a SSIS package that detects when new dimension members exist and then determines what type of dim process is required.

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

 

[SQLHacker]

MDXer,

Thanks for the response.

The cubes (dimensions) are static, and wouldn't change on a regular basis.

What I'm really looking for is help with the syntax that isn't working (shown above).

I followed what you describe for "single" dimensions, and each time I scripted one, I would copy it and place it in the above script. I want to create the script to process all of the dimensions in one job (instead of having several individual jobs processing one dimension at a time).

Thanks!