Installing the Analysis Services Deployment Utility

The Analysis Services Deployment Utility is a utility that can be used to deploy Analysis Services build outputs (.asdatabase files) to a server, or to generate the XMLA for offline deployment.

I've often used this as part of an automated installation process to push releases out into an integration environment, but on this project I wanted to perform this installation as part of a nightly build. It failed - because the utility (and it's dependencies) weren't installed on the build server.

I wasn't entirely sure what I needed to get it installed (and I was attempting to install the minimum amount of stuff). It turns out this tool is distributed as part of Sql Management Studio, not the SSDT/BIDS projects (as I'd previously assumed). Not sure if the Basic or Complete option is required, because I picked 'complete' and that fixed it.

Also, for 2012 the path has changed, and the utility is now at:

C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Microsoft.AnalysisServices.Deployment.exe

Analysis Services 2008 R2 breaking change when deploying from the command line

As collegues of mine will attest, I will script anything that has to be deployed. Some things are easier than others.

In the case of Analysis Services, the .asdatabase file that comes out of the build needs to be futher transformed to create the XMLA that you need to run on the server to deploy your (updated) cube definition. Rather than attempt to replicate this transformation, I have previously chosen to get the Analysis Services deployment utility to do this for me, since this can supplied with command line arguments:

write-host "Generating XMLA"
$asDeploy = "$programfiles32\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\Microsoft.AnalysisServices.Deployment.exe"
& $asDeploy "$pwd\..\bin\MyCube\MyCube.asdatabase" /d /o:"$pwd\MyCube.xmla"

Which works just nicely. Except when we migrated that project to SQL 2008 R2, when it stopped working.

Well, actually that's not true. We'd been deploying to a 2008 R2 server for ages, it was when we changed the deployment script to use the 2008 version of the deployment tool that it all broke.

Basically the next line in the script kept complaining that 'MyCube.xmla' didn't exist, but I'd look in the folder after the script had run and the file was there. So it seemed like maybe there was a race condition.

Which there was.

If you examine the PE headers for the Sql 2005 version of the deployment util (using a tool like dumpbin) you'll see it's marked as a command line application:

C:\>dumpbin /headers "C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\Microsoft.AnalysisServices.Deployment.exe" | find /i "subsystem"
            4.00 subsystem version
               3 subsystem (Windows CUI)

...but the 2008 R2 version is marked as a gui application:

C:\>dumpbin /headers "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Microsoft.AnalysisServices.Deployment.exe" | find /i "subsystem"
            4.00 subsystem version
               2 subsystem (Windows GUI)

What? Can't see the difference? One is marked CUI with a 'C', the other is GUI with a 'G'. An unfortunately high degree of visual similarity given what a fundamental difference it makes: launch the first from the command line and you wait for it, launch the second and you don't. When scripting it's pretty important to know which one you've got, or you're going to get race conditiions.

In this case the answer was to control the process launching, so we can explicitly decide to wait:

     start-process -FilePath:$asDeploy -ArgumentList:$asdatabase,"/d","/o:$xmla" -Wait;

Maybe I should just do that all the time to be safe, but just being able to use other command line tools within a script without a whole lot of ceremony is one of the really nice bits about powershell, so I tend not to. In this case the launch semantics of an existing utility changing between versions seems like a really nasty thing to be caught out by.
Good reference sources:
Stack Overflow: Can one executable be both a console and GUI app?
MSDN: A Tour of the Win32 Portable Executable File Format

Excel Multiselect Issues with SSAS Calculated Member

Ok, it’s an old one, but I struggled to find the right answer, so I thought it was worth a post. I knew it was an issue, and I thought I knew the answer. I was wrong.

Say you create a calculated member to return some kind of item count against a dimension, a bit like this:

CREATE MEMBER CURRENTCUBE.[Measures].[Days Count] as (
count( descendants(
  [Date].[Year-Month-Date].CurrentMember,
  [Date].[Year-Month-Date].[Date]
))

and it works just fine when you test it

SELECT {
    [Measures].[Days Count]
    ,[Measures].[Minutes Of Day Count]
} ON COLUMNS
FROM [My Cube]
WHERE {
    [Date].[Year-Month-Date].[Year].[2011]
}

…and then you find it falls apart when someone queries the cube in Excel and uses multi-select in the pivot table filter. So you follow Mosha’s advice to fix it (using Existing):

CREATE MEMBER CURRENTCUBE.[Measures].[Days Count] as
count( existing [Date].[Year-Month-Date].[Date] )

…but then someone points out that that doesn’t work either. More recent versions of Excel (2007, 2010) implement multi-select via sub-queries; with just a few days selected it generates a query that (simplified) looks a bit like this:

SELECT {
    [Measures].[Days Count]
    ,[Measures].[Minutes Of Day Count]
} ON COLUMNS
FROM (
    SELECT (
        {[Date].[Year-Month-Date].[Date].&[20110101]
        ,[Date].[Year-Month-Date].[Date].&[20110102]
        })
    ON COLUMNS  FROM [My Cube]
)


and executing that doesn’t give you the right answer (it just counts the number of members in the dimension):

So the old advice, still commonly quoted, is actually wrong. The solution (as of SSAS 2008 and onwards) is to use a dynamic named set:

CREATE DYNAMIC SET [Selected Days] as (
    [Date].[Year-Month-Date].[Date]
)
CREATE MEMBER CURRENTCUBE.[Measures].[Days Count] as (
    count([Selected Days])
)

I found this a bit bizarre at first, but some of the posts I read were themselves a bit confused and left in the ‘existing’. This is not required, and distracts from what’s really going on here: this works by design as this is one of the problems that dynamic sets are intended to fix. A (more recent) Mosha post spells it out:

“Dynamic sets are not calculated once. They are calculated before each query, and, very important, in the context of that's query WHERE clause and subselects”

Ah. The answer, and the explanation. Finally.

Configuring SSAS 2008 HTTP access on IIS 7.5 using PowerShell

…was way too much like hard work / blindly poking about in the dark, and there’s still a nasty hacky bit in the script where I gave up trying to make the IIS snap-in work, and just used appcmd, but it does actually work:

<#
.Synopsis
Configures HTTP access for SSAS on the local box

.SeeAlso
http://technet.microsoft.com/en-sg/library/gg492140(en-us).aspx
http://learn.iis.net/page.aspx/436/powershell-snap-in-changing-simple-settings-in-configuration-sections/
http://www.iis.net/ConfigReference/system.webServer/security/isapiCgiRestriction
#>
$ErrorActionPreference = 'stop'
$scriptDir = split-path $myInvocation.MyCommand.Path;
$isapiPath = "$scriptDir\msmdpump.dll"

import-module WebAdministration
cd IIS:\

# Register msmdpump as a (globally) acceptable ISAPI handler
$isapiRestrictions = Get-WebConfiguration /system.webServer/security/isapiCgiRestriction
$handler = @($isapiRestrictions.Collection | ? { $_.description -eq 'OLAP' } )
if(-not $handler){
    # This way the object always appears locked?
    # but should be fine according to http://forums.iis.net/t/1158906.aspx
   $null = @"
    $restrictions = $isapiRestrictions.GetCollection();
    $handler = $restrictions.CreateElement('add');
    $handler.SetAttributeValue('path', $isapiPath);
    $handler.SetAttributeValue('allowed',$true);
    $handler.SetAttributeValue('description','OLAP');
    $restrictions.Add($handler);
"@

    # instead try
    # http://serverfault.com/questions/134361/programmatically-add-an-isapi-extension-dll-in-iis-7-using-adsi
    & "$env:windir\system32\inetsrv\appcmd.exe" set config /section:isapiCgiRestriction /+"[path='$isapiPath', description='OLAP',allowed='True']"

}

# Create the app pool
cd IIS:\AppPools
if(-not (Test-Path OLAP)){
    $olapAppPool = New-WebAppPool OLAP
}else{
    $olapAppPool = Get-Item OLAP
}
$olapAppPool.managedPipelineMode = 'classic'
$olapAppPool | Set-Item

# Create the website
cd IIS:\Sites
$defaultSite = @(dir)[0]
cd $defaultSite.Name

if(-not (Test-Path OLAP)){
    $olapApp = New-WebApplication OLAP -physicalPath:$scriptDir
}else{
    $olapApp = Get-Item OLAP
}
$olapLocation = '{0}/{1}' -f $defaultSite.Name,$olapApp.Name
cd OLAP

# Setup the web application: first associate the app pool
set-itemproperty $pwd -name applicationPool -value 'OLAP'

# ...then enable anonymous access
$basicAuth = Get-WebConfiguration -Filter /system.webServer/security/authentication/anonymousAuthentication
$basicAuth.Enabled = $true
$basicAuth | Set-WebConfiguration -Filter /system.webServer/security/authentication/anonymousAuthentication -PSPath IIS:\ -Location $olapLocation

# ...and create a mapping for that handler for this web app
$mapping = Get-WebHandler OLAP
if(-not $mapping){
    $mapping = New-WebHandler OLAP -ScriptProcessor:$isapiPath -Path:*.dll -Verb:* -Location:$olapLocation -PSPath:IIS:\ -Modules:IsapiModule
}
$mapping

Wow. IIS administration is just as bizarre and arcane as it always was. Did someone say ‘where is the setup wizard’?

Configuring HTTP Access for Analysis Services 2008 R2 64 bit

It’s basically no different from 2005, so follow these instructions, ensuring that (if needed) you download the OLE-DB provider for AS from the 2008 R2 feature pack site, and not one of the vanilla 2008 releases, and also ensuring that don’t mark your app pool as ‘enable 32 bit’.

You may find that the web role services ‘ISAPI extensions’ or ‘Windows Authentication’ isn’t even enabled on your box, and you will need those. You can get these up by right-clicking the web server node:

A good way to test this is to try and connect using Management Studio. If it fails with a ‘unsupported data format’ error, and you put the pump on a different box to the SSAS instance, it’s probably a double hop issue, and you’re going to have to send yourself insane talking to IT about configuring SSAS to use Kerberos authentication, constrained delegation, SPNs and all the normal fun stuff. I feel for you.

I bit the bullet and installed IIS directly on the SSAS box, and got a bit further. A good way to test at this point is to try and connect to SSAS locally using Management Studio, using the http://<server>/olap/msmdpump.dll path. If that works, at least you know the SSAS/IIS end is ok. But then I still couldn’t connect from Excel on my workstation, in a different domain, which was why I was doing all this in the first place.

I used the old ‘create local user with matching login/password’ trick, and ran Excel as that, but that which didn’t work. Guessing the lack of trust between the domains would be a problem I attempted to disable Kerberos and force NTLM (which seems to have changed a bit from how it used to be done on Windows 2003), but that still didn’t work either (IIS logs helped here). Eventually I just enabled anonymous access to the website, changed the app pool credentials to use Local Service (Network Service would have done), granted IUSR access to the cube (this bit I don’t quite follow, why not Local Service?) and finally it all worked. But note that now I was using anonymous access I didn’t need to install IIS at all, since I could have done this from any box. Ho hum.

Finally go and vote for this to be supported out-of-the-box. With all the work that went into separating out the HTTPSys bits (for WCF et al), this should be a total no-brainer.

Why No ‘Average’ Aggregation Type in SSAS?

Just coming back onto some cube work for the first time in 12 months or so, and managed to get myself confused by the AverageOfChildren semi-additive aggregation type, just like I did the first time round :-/

(Aside: AverageOfChildren is a semi-additive average: it sums over all dimensions except time, and averages over time. This makes sense (for example) if you want to compare average monthly sales over the year for each of your branch offices. It’s useless, however, when you want to know the average sale price per unit)

Whilst creating calculated measures of the type [Measures].[Something Sum] / [Measures].[Something Count] is all very well, I don’t understand why this isn’t just supported out of the box, as a native Average aggregation type. Sure there are always ‘flavours’ of averages that need specific MDX (weighted averages for example), but surely the simple flat average over row count is so common as to warrant ‘out of the box’ support.

You might say having two ‘average’ aggregations would confuse, but AverageOfChildren causes plenty of confusion on it’s own: having two (and documentation on which to use when) would probably make the situation considerably better.

Fool me twice: shame on me.

Update June 2011: Rather than just complain, I raised a Connect Issue: Add AverageOfRows aggregation type for simple averages. Please vote for it

Dang! SSAS deployment configurations are held in the .user file

I really wish Microsoft would make up their mind where deployment target locations should live (ie setting the destinations for Publish / Deploy)

For SSRS this data is stored in the project file (.rptproj) , per configuration, so anyone can get latest on a project, change their solution configuration to (say) 'Test', hit deploy and away they go, happily deploying to your Test environment. This is how it should be.

For SSAS (2005 / 2008) this data is stored in the .dwproj.user file, so for any new developer / PC / workspace that comes along all those deployment configuration settings have to be re-setup (or copied from another workspace), or all their deployments are to localhost. This is just really irritating. As I discovered today.

For an XBAP (or just a WinForms ClickOnce project) a single deployment target is stored in the project file. Other, previously-used deployment locations, are stored per-user. None are correlated with a project configuration in any way. This 'deploy to prod' design mentality drives me nuts.

For an ASP.Net website the (single) deployment location isn't even stored against in the project at all, but squirreled away in %userprofile%\Local Settings\Application Data\Microsoft\WebsiteCache\Websites.xml. Of course! Web application projects put it soley in the .user file, which is at least less obscure, if no more useful.

Now I'd be the first to say that deployment to test environments etc... is best done from a seperate scripted process, but I accept that not everyone has my affinity for long PowerShell deployment scripts. Plus sometimes I just can't be arsed. So am I completely insane to wish that the in-IDE Publish / Deploy mechanisms would work in a manner that could be actually used would be usable within a team, and not scatter the deployment target information quite so liberally?

Remember to enable MARS when using Snapshot Isolation from SSAS

We started getting this error when processing our cube:

OLE DB error: OLE DB or ODBC error: Cannot create new connection because in manual or distributed transaction mode

It went away when:

• We changed to using ReadCommitted isolation, rather than snapshot
• We processed the cube using Maximum Parallel Tasks = 1

Reading knowlege base article PRB: SQLOLEDB Allows Only One Connection in Scope of Transaction lead me to think that SSAS was trying to open multiple connections within a transaction, which isn't allowed.

Which got me thinking about MARS. Not quite sure why it wasn't on to start with, but I enabled it, and then everything was fine again.

Turns out this is actually a RTFM, if you pay attention when reading How to enable the snapshot transaction isolation level in SQL Server 2005 Analysis Services . Of course it's only in Snapshot mode that SSAS attempts to ensure database consistency for the duration of the entire Process operation, which is why it's not a problem using ReadCommitted isolation.

Viewing the MDX cellset with WPF

When executing an MDX query there's various bits of useful metadata that can be returned in the cellset over and above the members and dimensions you've explicitly specified in your query. This can include things like the formatted value (as specified by the cube definition) as well as other attribute values for the dimension member you're explicitly querying against.

This kind of stuff can be invaluable if you're writing your own front-end app to access OLAP data, not least because it saves a whole heap of faffing about with WITH clauses (query scoped calculated measures). Something like:

with Member (
  [date].[date].[date].currentmember.properties("Year")
) as TheYear,
select {
  [measures].TheYear, [measures].[measure]
} on Columns,{
  [date].[date].[date]
} on Rows from Cube

...can just become:

select {
  [measures].[measure]
} on Columns,{
  [date].[date].[date]
} dimension properties member_name, member_value,
 [date].[date].[date].Year on Rows
on Rows from Cube

Trouble is you'd really struggle to work this out since all that metadata is helpfully hidden from view when you execute MDX in BIDS (and MDX studio), which makes it all a bit hit-and-miss. So I wrote a little WPF app just to visualise the actual cellset returned. Pretty basic stuff - load the results into a dataset and bind it to a grid. I could fiddle with my DIMENSION PROPERTIES clause with immediate gratification.

But it took me hours to get the binding working. One of the problems is that the MDX columns have names like '[Measures].[MyThing]' and you can't just set that as the property name of your binding and expect the binding infrastructure to cope:

{Binding Path=[Measures].[SomeMeasure]}

The binding infrastructure sees the dot and tries to walk the path, with predictable results:

System.ArgumentException: Measures is neither a DataColumn nor a DataRelation for table Table

[NB: If you had a column simply named SomeMeasure this would work, due to the magic of ICustomTypeDescriptor, but that's another story]

So instead you have to use the indexer syntax on the DataRowView:

{Binding Path=['[Measures].[SomeMeasure]']}

Or (if that made you wince)

{Binding Path=Row['[Measures].[SomeMeasure]']}

But those don't work either:

System.ArgumentException: Column '"[Measures].[SomeMeasure]"' does not belong to table Table

Even whilst the same binding path works 'just fine thanks' in the debugger. It took me a long, long time to realise there's an extra set of quotes in that error message. The WPF binding syntax doesn't require quotes for string indexers:

{Binding Path=Row[[Measures].[SomeMeasure]]}

It looks so wrong but it works.