Entries in Analysis Services (19)


Unit Testing Role Security in Analysis Services

Overview: Three methods for unit testing the results being returned from an SSAS data source, using the security context as if you were the end user.

In the real world we usually don’t possess access to user passwords.  Therefore, in order to test “as if” you were an end user, you need to impersonate them in order to test that the SSAS security role is set up properly.  Following are techniques for impersonating a user while unit testing.

Method #1 for Impersonating SSAS Role Security:  EffectiveUserName in SSMS

This technique is appropriate when you have an MDX query (such as an SSRS dataset query) & you want to execute the query while impersonating a specific end user.

First, launch SQL Server Management Studio.  Connect to your Analysis Services instance.



Before clicking the Connect button, choose Options.  Under Additional Connection Parameters, enter the user you’re testing.  The syntax is as follows:



Now that you have a query window open in SSMS with the EffectiveUserName in the connection string, paste the MDX query in & carry on conducting your test.  The query results should (hopefully!) match your expectation based on how the role is set up in SSAS.

Sidenote:  as long as you have that query window open, if you were to fire up SQL Profiler you can see your existing session.  The TextData column shows which SSAS role is applied to the EffectiveUserName, which is quite handy to verify.


The only bad thing about method #1 is SSMS has a hard time letting go of the connection, even after you close the query window & disconnect/reconnect in Object Explorer.  You might need to close & reopen SSMS to get it to behave.

Method #2 for Impersonating SSAS Role Security: EffectiveUserName in Excel

This method is much like what’s discussed above, except we are using the end-user environment (i.e., Excel) rather than SSMS.

First, in a new workbook let’s go to Existing Connections & select one that points to your SSAS database you wish to test:


At the Import Data dialog box, select Properties.   (Don’t worry, if you miss this part you can always update the data connection properties later from the “Data” ribbon.)


Under the Definition tab, within the Connection string, add a semicolon plus the user you’re testing. The syntax is as follows:



Method #3 for Impersonating SSAS Role Security: Browse Cube in SSMS

First, launch SQL Server Management Studio. Connect to your Analysis Services instance.  With this method, we won’t need the Options button like we did above.


Expand the tree to select the cube you are testing.  Right-click the cube & choose Browse.


In the cube browser window, click the toolbar item to “Change User.” 


You’ll be given a Security Context dialog box.  In this situation we want to select “Other user” and type in the DOMAIN\UserName we wish to test.  Another option is to test the Role as a whole.


At this point you can drag & drop fields into the cube browser window.  With any luck, you’ll immediately be able to verify by the dimensional attributes that get displayed that the SSAS role security is working as intended.



Cancelling a Long Running SSAS Query

Overview:  Quick tip for cancelling a long-running SQL Server Analysis Services query.  Screen shots & statements are from SQL Server 2008 R2.

We’ve all been there…waiting in BIDS for a report to render.  You begin to think uh-oh.  After a bit longer you know it’s uh-oh. 


There’s multiple ways to accomplish cancelling the session; here’s one way to cancel a long-running SSAS query.  One caveat: this technique does require elevated privileges.

Step 1:  Use a DMV to find the SPID

First, you need to find the SPID associated to your long-running report query.  We’ll use a Dynamic Management View (DMV) for that.  First let’s launch an MDX window in SQL Server Management Studio:


Obviously you can modify the select statement here to whatever columns you wish to see. 




, session_user_name

, session_last_command

, session_current_database

, session_cpu_time_ms

, session_elapsed_time_ms

, session_start_time

, session_last_command_start_time

, session_last_command_end_time

FROM $system.discover_sessions

WHERE session_status = 1

AND session_user_name = 'DomainName\MCoates'

ORDER BY session_start_time desc



In the above query, I restricted the results to just my user name.  Using the results, find the query associated to your long-running report.  Note the Session_Spid (i.e., the Server Process ID).

Step 2:  Use XMLA Command to Cancel the SPID

Now that we have the SPID, we need to pop over to an XMLA window (rather than the MDX window we were in for Step 1 above).



<Cancel xmlns="">



After executing the Cancel Command, you should immediately see the message in BIDS that the operation has been cancelled.  Mission accomplished.


A Final Word on Permissions

I’m lucky enough in the current environment to be able to do this on my own.

DMV Select Permissions:  Requires VIEW SERVER STATE or VIEW DATABASE STATE permissions.

XMLA Cancel Command Permissions:  Requires administrative permissions.

Finding More Information

MSDN – Use Dynamic Management Views (DMVs) to Monitor Analysis Services

MSDN – Discover Sessions

MSDN – Cancel Element (XMLA)



Why Would You Want to Edit an SSAS Database in Online Mode?

I recently had my first “legitimate” purpose for opening and modifying a production SQL Server Analysis Services database in online mode.  I needed to add a new cube calculation to one of the existing production cubes.  As you’d expect, I opened up the project, added the calculation, deployed it to the test environment, and was happy with what I saw so I proceeded to check in my changes to the project.  The project is integrated with TFS source control.  Now it’s time to get the new cube calculation migrated to production. 

Here’s where things got interesting.  Also checked into TFS were other changes to the SSAS database, made by another BI developer, which weren’t scheduled to be deployed for another couple of weeks yet.  Since you must deploy an SSAS database in its entirety, deploying in its current state wasn’t an option.

One choice would be to temporarily roll back the other developer’s changes from source control and deploy from the project – that would be appealing if I had quite a few changes to deploy & if the sequence of events in a multi-developer environment made reverting to a prior changeset possible.  However, since my change was one very small calculation, I chose to open up the “online” version of the SSAS database and add the calculation directly.  The change was also checked into the project file, so next time it’s deployed my change won’t be lost.

Let me just be clear this is absolutely not the first choice – there’s a much bigger risk of error when you make the change twice (i.e., once in the online version & once in the project).  And working in the online version just shouldn’t be a habit.  However, in a pinch it’s nice functionality to have in the ‘ol tool belt.

Opening an Analysis Services Database in Online Mode

Opening an SSAS Database in SSDT (SQL Server 2012) or BIDS (SQL Server 2008 R2 and prior):


If you’ve connected in online mode previously, the server(s) and database(s) will be listed in the middle box.  If not, enter them at the top to connect:


When you are working in online mode, it will tell you so within the name of each tab across the top:


As soon as you make your changes and save, you're done.  Saving changes while in online mode can be a bit slow.

Finding More Information

Technet – Working with Analysis Services Projects and Databases During the Development Phase



Improving the Performance of a Calculated Field in SSRS When Reporting from SSAS

Overview:  A quick tip re: using SQL Server Reporting Services (SSRS) Dataset Properties to derive a concatenated field.

Level:  Familiarity with SSRS parameters & datasets is presumed.

Recently I was working on an SSRS report.  One parameter required its label to be formatted Number – Name (for example:  80 – Revenue).  In my SSAS data source, I had the Number field and the Name field available, but I did not have them concatenated together.  That I would need to derive in the report.

A quick reminder about parameters in SSRS:  a parameter looks for 2 fields:  a value, and a label.  This blog entry is focusing on the label that the user sees when he interacts with the parameter.

Option 1 – Derive in the MDX source query

Deriving within the source query is what I did initially.  However, in my situation, I had about 4,000 possible options that could be returned by the parameter query (if the user had full security to the data, like I do).  After adding the WITH MEMBER, this query ran extremely slowly.

To translate my situation into an Adventure Works example, here’s what I did first:

MEMBER [Measures].[AccountParameter_Label] AS
[Account].[Account Number].CURRENTMEMBER.NAME + " - " + [Account].[Account Type].CURRENTMEMBER.NAME

[Account].[Account Number].[Account Number].MEMBERS
,[Account].[Account Type].[Account Type].MEMBERS

FROM [Adventure Works]

Since the performance of the above query was not acceptable, I looked for an alternative.

Option 2 – Derive in the SSRS Dataset

This second option, deriving the field within the Dataset Properties, performed significantly faster.  To set it up:

Step 1:  Create the basic Parameter results query

MEMBER [Measures].[AccountParameter_Value] AS [Account].[Account Number].CURRENTMEMBER.UNIQUENAME

[Account].[Account Number].[Account Number].MEMBERS
,[Account].[Account Type].[Account Type].MEMBERS

FROM [Adventure Works]


Step 2:  Add a Calculated Field in the “Fields” pane of the Dataset Properties

First, create a Calculated Field:


Then, give it a Field Name and a Field Source.  The expression for the Field Source is:

=Fields!Account_Number.Value & " - " & Fields!Account_Type.Value


Step 3:  Create (or modify) the parameter



What the user sees is the concatenated label we created:



Usually I prefer to derive fields within my dataset, just to keep the logic consolidated in one place.  However, in this particular situation having the Report Server perform the concatenation performed much, much faster for me – sub 1 second as opposed to 30 seconds execution time.  Doing it this way won’t always be the best answer – but it’s a possibility to keep in the ‘ol toolbox.



Why is my SSAS Query Log Table Empty?

Overview: Just a quick tip re: resolving an issue with getting SSAS the query logging table populated.

Enabling the SSAS Query Log

In Management Studio, if you right-click the SSAS Server instance, you are presented with Analysis Server properties.  Here is where you tell SSAS to log queries.  The default table name is “OlapQueryLog” which will reside in the database you specify in the connection string.

In the screen shot below, you’ll see I specified a sampling of 1.  This is because I wanted to capture all queries within this test database.  I have a dashboard getting close to being rolled out.  Before migrating to Production I wanted to run the Usage-Based Optimizer for the purpose of creating a few aggregations that’ll help query performance of the new dashboard (particularly a many-to-many bridge table that I know is a bit of a bottleneck).



So, I enabled the QueryLog properties as shown above.  Then I went and ran my dashboard to force some records into the OlapQueryLog table. 

Next, a quick select statement on the OlapQueryLog table – no records.  It was empty.  Huh?

SSAS Service Account Permissions

Why was my OlapQueryLog table empty?  Well, in the connection string I didn’t specify certain credentials.  Which means the SSAS Service Account was responsible for running it.  And … that SSAS Service Account didn’t have write permissions to the database specified in the connection string.

So, since this was a quick exercise, I chose to grant the write permissions needed by the SSAS Service Account & then run my queries; after verifying I had the records I expected, I then set the QueryLogSampling property back to 0 (to turn SSAS query logging back off), and revoked the write privileges. 

If you intend to keep logging enabled for more than a quick test, it would be a better practice to define a specific user ID and password that has few privileges overall, but with the write permissions it needs, and embed it in the QueryLogConnectionString property.

Finding More Information

Technet – Configuring the Analysis Services Query Log

SQL CAT - Reintroducing Usage-Based Optimization in SQL Server 2008 Analysis Services



Resolution for the SSAS FileStore Error When Processing Dimensions

This week I deployed an updated SQL Server Analysis Services (SSAS) database from Dev to Test.  When processing in Test, I got an error which we had not experienced in the Development environment.

The dreaded File system error “A FileStore error from WriteFile occurred.”

File system error: A FileStore error from WriteFile occurred. Physical file: \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Temp\MSMDCacheRowset_1136_e48_dnogks.tmp. Logical file: . : The parameter is incorrect. .


After an audible groan, I did what any good BI developer does.  Opened my web browser.  I won’t repeat the common resolutions I found since none of them applied to my situation.  (Sidenote:  it was not the string limitation issue which, incidentally, is fixed in SQL Server 2012.)

What’s The Fix?

One of my teammates noticed the versions were different between Dev and Test.  As it turns out, the Development SQL Server box had Service Pack 1 for SQL Server 2008 R2 installed whereas the Test box did not.  So I promptly gave the client DBA an opportunity to be a hero and get SP1 installed.  Next time I processed the cube, the issue was gone.

To download SP1:  Download Center