Introduction
In addition to the daily-grind of DBA activities, our DBAs also participate in the software production activities of the platform team.
One of their biggest contributions is in the arena of data warehousing and ETL.
Continuous integration patterns
Each new feature or fix can be impacted by, or upon, the data-warehousing systems. Therefore, just like our development teams isolate their code-changes using branches, the same must be true of the data warehousing.Sometimes, the developers and DBAs will collaborate together in the same branch where its mutually beneficial to do so. Sometimes, the DBA's will take their own branches for general improvements not tied a specific feature in the code base.
It is vital that the code and data be packaged together during the build process, to maintain synchronicity and compatibility. Therefore, when I am building the code projects and producing output packages, I also need to include the co-dependent databases (.dbproj) and data-warehouses (.dwproj) at the same time.
Without this synchronicity, precious DBA time is sapped by developers and testers experiencing Cube and Warehousing issues within their respective environments. Deployments to production also become way more complex as the DBAs must attempt to match the correct versions of code and data.
Software prerequisites
The DBA's use similar tooling to the developers. Our DBA's use the Business Intelligence Development Studio, which appears to be Visual Studio with a few extra bits and pieces.
Crucially however, BIDS includes the necessary tooling and command line tools for building the Analysis Services (.dwproj) projects.
You'll find the BIDS installer is part of your SQL server deployment CD or ISO.
You can't use MSBuild
Normally, MSBuild takes care of the work of finding the appropriate compiler, and all we need to do is provide the right set of command line parameters. Not so with Analysis Services, we must make a call directly to Visual Studio via the Command Line.
You may have found this post because you were looking for "How do I build .dwproj with MSBuild". Well, you can't, but there is an entirely satisfactory work around.
Visual Studio (BIDS edition) Command Line
For whatever reason, Analysis Services projects can only be built using the Visual Studio command line. So, here is my Powershell script for invoking this.
Parameters
$vsroot = "$env:ProgramFiles (x86)\Microsoft Visual Studio 9.0"
$devenv = "$vsroot\Common7\IDE\devenv.com"
$devenv = @{
fileParameters = $solutionFile, $projectFile
actionParameters = ,"/rebuild","development"
loggingParameters = ,"/out","$($data.log)"
}
cmd /c $devenv $devenv.fileParameters `
$devenv.actionParameters `
$devenv.loggingParameters | Out-Null.
$devenv = "$vsroot\Common7\IDE\devenv.com"
$devenv = @{
fileParameters = $solutionFile, $projectFile
actionParameters = ,"/rebuild","development"
loggingParameters = ,"/out","$($data.log)"
}
cmd /c $devenv $devenv.fileParameters `
$devenv.actionParameters `
$devenv.loggingParameters | Out-Null.
TFS and Build Agents
All of the above applies to your Build Agents. If you want to build .dwproj, you need to have the appropriate version of BIDS on your build agents too.
Packaging
Unlike MSBuild, this visual studio command line doesn't permit specifying a custom output location. The output is just deposited in the projects default output folder.
Packaging is therefore your responsibility I'm afraid.
Deployment
Deployment is extremely simple, and involves a single command line call to the Analysis Services tooling.
You need to have Analysis Services installed on your SQL server for this to work, but with that in place, generating the XLNA and deploying to your database server is a single command line action.
Here is my powershell for deploying (or patching!) your data warehouse.
{note: this script has to be run on the Analysis Server where the Analysis Services and its tools can be found}
$analysisservices = "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\microsoft.analysisservices.deployment.exe"
$manifestfile = "C:\temp\Project.asDatabase"
cmd /c $analysisservices $manifestfile /s
$manifestfile = "C:\temp\Project.asDatabase"
cmd /c $analysisservices $manifestfile /s
Blissfully simple.
ETL deployment
The ETL deployment is auto-generated from SQL management studio. Right-Click the SQL Agent job and "Script as create to new window".
This SQL script can be stored in a file, and invoked by Powershell (Invoke-SqlCMD) whenever you need it.
This SQL script can be stored in a file, and invoked by Powershell (Invoke-SqlCMD) whenever you need it.
Finito. GL HF.
Comments
Post a Comment