SQL Tech


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


Re: SQL Agent Job to refresh Analysis Services Cubes

Phil Brammer


You might want to ask this in the SSAS forum. This forum is for Integration Services.

http://forums.microsoft.com/MSDN/ShowForum.aspx ForumID=83&SiteID=1






Re: SQL Agent Job to refresh Analysis Services Cubes

Phil Brammer

I can move it for you if you'd like.






Re: SQL Agent Job to refresh Analysis Services Cubes

SQL Tech

That would be great! Thank you for your help!



Re: SQL Agent Job to refresh Analysis Services Cubes

Edward Melomed

You can definitely use SQL Agent job to process your cubes.
For that you go into SQL Management studio run processing dialog and then use "Script" button to generate XMLA command for processing your cubes or dimensions.

Then you go and create new SQL Agent job and create Analysis Services execution step where paste your processing command. Here an article that talks about backup but at the end show how would you do that. http://www.microsoft.com/technet/prodtechnol/sql/2005/bkupssas.mspx

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.







Re: SQL Agent Job to refresh Analysis Services Cubes

SQL Tech

Thank you Edward for your response. It was helpful in getting me to the information I needed.

Now...I have another question. How can I included all of the dimensions for a particular AS database in the script I am not very familiar with XML, and have tried several ways to get this to work, but to no avail.

Here is the script that I created, with the error that appears when I try to execute it:

<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>

Error:

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.

How can I change the script so that it will execute properly (by the way, I've already tried taking out <Dimensions> and using <Dimension> with <ID> for the child objects, and it didn't work)





Re: SQL Agent Job to refresh Analysis Services Cubes

Edward Melomed

Here is simple way to generate a script containing several dimensions.

Go into SQL Management studio and navigate to the "Dimensions" node in your database.

In the middle pane of SSMS you'd see list of dimensions. Multi-select dimensions you need and then use right-click menu to invoke processing dialog.
Click on the Script button on top of the dialog and you'll get a processing script containing serveral dimensions.

You can do the same with partitions.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.






Re: SQL Agent Job to refresh Analysis Services Cubes

SQL Tech

Edward,

You are awesome!!

That worked perfectly!!

Thank you very much for your help!

Alan