The other day somebody asked me how I could implement Slowly Changing Dimension Type 2 (SCD Type 2) in SSAS. I wasn’t able to give the answer right there and then, so I decided to create a demo video to answer his question and as a note to myself for future uses.
This is my first ever demo video by the way and I agree that it could have been better in many parts. I do hope that despite my amateur recording and editing skills, I am still able to send the message across. 🙂
Step 1: Build SQL Server Analysis Services solution using devenv /build, for example
“<path>\devenv.com” AdventureWorksSSAS.sln /build Development
In the bin folder of your solution, you should find <projName>.asdatabase, <projName>.deploymentoptions, <projName>.deploymenttargets and <projName>.configsettings XML files
Step 2: Generate XMLA script using Microsoft.AnalysisServices.Deployment.exe
Microsoft.AnalysisServices.Deployment.exe <path>\AdventureWorksSSAS.asdatabase /s:GenerateXmla.log /o:SSASDatabase.xmla
Step 3. Execute XMLA on target server using PowerShell script.
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices.AdomdClient”)
# Read the target server from .deploymenttargets file generated in Step 1
$DeploymentTargetsFile = <path to <projName>.deploymenttargets>
$deploymentTargets = [xml](Get-Content $DeploymentTargetsFile)
# Connect to the server
$serverName = “Data Source=” + $deploymentTargets.DeploymentTarget.Server
$conn = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdConnection $serverName
$conn.Open()
# Read XMLA
$xmla = Get-Content $XmlaPath
# Execute XMLA
$cmd = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdCommand $xmla, $conn
return $cmd.ExecuteNonQuery();
And that’s it 🙂
I have this rare opportunity to provide basic SSAS training to developers from another team. Being busy with my own projects, my manager and their manager agreed to have the training for only 3 hours. I met the would be trainees and their manager to understand their expectations, the developer profiles and the modeling technique their project use.
The developers have intermediate to advanced T-SQL skills and they expect that at the end of the 3-hour training, they would be able to comfortably navigate around the existing SSAS solution, understand basic concepts of dimensional modeling, create new dimensions and measures, modify the existing cubes and have the basic knowledge they need to dig deeper into SSAS themselves. Whew! Investigating further, I found their current solution uses multidimensional approach with SQL Server 2012. They have calculations and perspectives but do not (or maybe not yet) define partitions, aggregations and translations.
With those constraints in mind, I prepared the outline of my training using XMind.
Continue reading “All You Need To Know To Start Learning SQL Server Analysis Services”
Vanie's View 