Entries in Analysis Services
(19)

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.

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

EffectiveUserName=”DOMAIN\UserName”

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:

;EffectiveUserName=DOMAIN\UserName

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.

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.

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:

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:

Conclusion

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.

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.

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.