One of the last pieces of our complete deployment we have left to automate is Analysis Services. This turns out to be pretty simple.
Microsoft has created a tool called Analysis Services Deployment Wizard. It can be found in your start menu under SQL 2005, Analysis Services, Deployment Wizard. This takes the asdatabase file in your bin directory and creates an XMLA script that creates the SSAS database. It can be found at:
[Install Drive]\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\Microsoft.AnalysisServices.Deployment.exe.
It can be called from the command line without showing the GUI wizard piece.
Now the only thing is that we need to play that script against our server. Microsoft has also written this tool for us already. There is a sample app that comes with SQL server and it can be found in [Install Drive]\Program Files\Microsoft SQL Server\90\Samples\Analysis Services\Administrator\ascmd\CS. Open the solution and compile and you’ll have a tool that will play XMLA scripts against the server. It has a simple command line interface too.
Next we’ll have to create some XMLA scripts that we can play that will process our dimensions and cubes.
Is it possible to automate the deletion of the ssas database in the similar manner??
Santhosh
Yes – I would think so. Go to the cube and right click on it and script it out as a DELETE. This would be the XMLA that you need to run against the server. Use the same method described above to play the script.
Good luck.
Come back and ask more questions if you need.