Any way to programmatically deploy an SSAS solution?

Hi all,

I'm trying to add a nightly build/deploy process for my SSAS solution. Basically - to take my AS solution from source control, deploy it to our dev server, and process just a few partitions.

What's the best way to do this? Looking at the solution folder, the .dsv, .dim, .cube, .ds files are all xml-based, which is good, but they don't seem like XML/A. (Seem very similar to a serialized version of AMO objects, but not quite exactly)

The only way I'd guess to do this is to manually load the individual xml files into an object model, and then map them to the AMO objects (since they're pretty similar..), and deploy those to the server. Is there a more efficient way to directly de-serialize the .dim,.cube, etc files into the AMO structures and deploy?

Take a look at the Deployment Wizard which you can run from Start... Programs... Microsoft SQL Server 2005... Analysis Services... Deployment Wizard.

Also see more info including command line switches here:

http://msdn2.microsoft.com/en-us/library/ms174817.aspx

|||

Furmangg, perfect tip on using the deployment wizard. I think this will suit our needs very well.

I was going to follow up w/ the correct method of compiling - I tried msbuild unsuccessfully, but I found this great blog post by Thomas Kejser -

http://schastar.spaces.live.com/blog/cns!12BCB785A5D8B3D4!148.entry?beid=cns!12BCB785A5D8B3D4!148&d=1&wa=wsignin1.0

Sweet!

any way to prevent SSAS memory from paging?

This morning msmdsrv.exe looked like the following in Task Manager:

Memory Usage: 600,000 K

VM Size: 2,500,000 K

If I understand correctly, that means that a good deal of SSAS memory has been swapped to disk by the OS. Is there any way to prevent this? (I have read about using the Lock Pages In Memory privilege at http://msdn2.microsoft.com/en-us/library/ms179301.aspx which lets SQL prevent the OS from paging sqlservr.exe memory. I don't suppose there's an equivalent SSAS setting.)

In particular, the symptom I'm seeing is that after we finish processing, all that paged memory has to be loaded back into memory before the transaction can be committed. Committing the transaction is usually very quick. But when most of the SSAS process memory is pages, it takes quite a while, during which I can see the Memory Usage number in Task Manager growing. (I don't think it's a blocked transaction commit.)

Looks like maybe there is a way to do this in SP2. Can anyone explain the PreAllocate setting further?

http://www.sdsqlug.org/presentations/November2006/November_2006_64-bit_SQL_Server.pdf (page 36)

Any way to get processing status when executing a large batch process job via AMO?

When making an ExecuteCaptureLog() AMO call, is there any way the client can poll for processing status from the SSAS insance?

I think the only way you could do this is to capture the Trace events

This thread has some samples showing you how to create and use Trace events from code.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=482459&SiteID=1

Any good whitepapers on security/deployment for entire SQL Server BI solutions?

At my current employer we are struggling with the best way to manage security and deployment of a project that contains databases, SSIS, SSAS and SSRS components, using configurations.

Environment (Dev):

3 SQL Server databases, all using mixed-mode security, using SQL Server security credentials.

12 SSIS packages; one master package, eleven child packages, 3 shared data sources

1 SSAS database; one cube, 15 dimensions, three referenced data sources from the SSIS project (in same solution)

6 SSRS reports, one data source to cube (not shared- doesn't appear SSRS can share datasources among other projects in the solution? Why?)

Everything runs fine in development. Now comes the tricky part.

Deploying SSIS and SSAS into production environments:

-Packages use XML config files for connection strings to three relational data sources.

-Deploy to SQL Server storage. Deploy wizard copies package dependencies (including XML config files) to default location set in INI file. When I do this, no config file shows up in remote server (remote server not set up identical to local, so directory does not exist. Need UNC path?) So, being a developer with no "special" permissions on the PROD server, what security permissions is allowing the deployment wizard from copying files to this location on a production server?

-Using a deploy script using dtutil doesn't copy the SSIS dependencies. Is this matter of using COPY or XCOPY to copy the configuration files to the dependency location? Again, in real-world practice, do developers typically change this location in the INI file to another location, or stick with the default. In either case, how does security work that allows files to get copied to the remote folder? (i.e. manual, or SQL Server manages this file folder permission through some other magic)

When using SSMS and running the package after being deployed on the remote server, if the config path is the default (e.g. C:\program files\Microsoft SQL Server\90\DTS\Packages\...) it appears to be read from the local machines directory rather than the remote machines directory path (do I need to use UNC paths? The wizard doesn't give this option it seems)

-When scheduling the job from SQL Agent, does the proxy account need permissions to the folder the config files sit in?

-What about the roles security on the packages themselves? Where does the server roles come into play (dtsltuser, dtsadmin)

-Because the SSAS project uses connection references to the SSIS project in BIDS, and SSIS project uses configurations, will SSAS pick up on these connections?

-What about impersonation levels for SSAS? Leave all data sources set to default, and set the database impersonation level to "UseServiceAccount"? What if the developer is not the same as the OLAP administrator on the production server? In this case, Use Service Account isn't an option, and neither is the current users credentials.

-SSAS database also has security for Full Control, but still doesn't prevent security at the data source level within the database (talking about impersonation level, not source db credentials)

-How can SSRS connections leverage other shared connections?

As you can see, there are a ton of security considerations, none of which are intuitive and can be configured multiple ways and actually work (and a ton of ways that won't work).

I need a simple cheat-sheet about each step to take to configure this so multiple developers can work without interruption, hot-deploying SSIS, SSAS, and SSRS changes into different environments (QA, PROD).

-Kory

It is still on my list to read so I'm not sure it contains the information you need but check http://www.microsoft.com/technet/prodtechnol/sql/2005/sql2005secbestpract.mspx

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

I've already read this- not much on the BI tools side, mostly for the RDBMS.

-Kory

any good conferences or training coming up..

I would like to attend a good sql server training conference here in USA. What would you recommend? My areas of interest are:
OLTP, SSIS, SSAS, Data warehousing etc..

I googled for some excellent training or conference events but could not find one.

There are two big conference providers that I know of -

SQL Server User Group - PASS (http://www.sqlpass.org/)

SQL Server Magazine Connections (http://www.devconnections.com/shows/sql/default.asp?s=0)

There are no doubt loads of training providers out there, but it depends on what you want, public course, private on-site, MOC courses. Discalimer, I work with these people - Solid Quality Learning (http://www.sqlu.com/)

Rather than have lots of commercial links in the forums, as some people get very upset, I've started a Wiki page so feel free to add your favourite provider there as well, I've started with the top search hits-

Training Courses
(http://wiki.sqlis.com/default.aspx/SQLISWiki/TrainingCourses.html)