SSMS Quick Tip

If you ever need to test Analysis Services roles, you can easily do so either by browsing the cube or by means of a query.

When browsing a cube through SQL Server Management Studio (SSMS) or Bisuness Intelligence Developer Studio (BIDS), you can select one or more roles to be enforced on data, by clicking the little “user” icon in the top left corner of the cube browser:

You will then be able to select on or more of the roles available on the database via a dialog:

This way of browsing roles has the following pros and cons:
Pros: Visual confirmation of PC Hierarchies
Cons: A lot of clicking top verify permission integrity

The other way of testing roles is through SSMS is by creating an MDX query. The trick is to click the options button when the connection dialog is active. The last tab allows you to specify additional connection parameters. The Roles property is described in detail at technet. Basically it’s a comma seperated list of roles (case sensitive). The trick is showed in the screendump below:

Pros: Custom queries
Cons: No visual confirmation of integrity, can be hard to visually confirm permissions on a PC hierarchy as an example

Loading

Renaming the name of the physical database and log file

During one of my current tasks, which was to run the same test on multiple copies of the same database, I needed to setup a flow where I could

  1. deploy the database from VS2010 or setup database w/ vsdbcmd
  2. run the test (import 5.5 mio facts + 250K dimension elements into a dw model)
  3. rename the database including the physical files
  4. goto step 1 w/out overwriting the databases already created

Since both a deploy from VS2010 and a setup run through vsdbcmd will produce the same name on the physical files over and over again, I needed to alter these names. Here’s how the script looks:

USE [master]

DECLARE @Database NVARCHAR(255) = '<name of database>'
DECLARE @BackupDevice NVARCHAR(255) = N'<full path to backupmedia>'
DECLARE @CurrentDataFileName NVARCHAR(255) = 
              N'<name of logical database file>'
DECLARE @TargetDataFileFullPath NVARCHAR(255) = 
              N'<operating system file name for new database file>'
DECLARE @CurrentLogFileName NVARCHAR(255) = 
              N'<name of logical log file>'
DECLARE @TargetLogFileFullPath NVARCHAR(255) = 
              N'<operating system file name for new log file>'

DECLARE @SQL VARCHAR(MAX) = N'
RESTORE DATABASE ' + @Database + N'
FROM  DISK = N''' + @BackupDevice + '''
WITH  FILE = 1,  
MOVE N''' + @CurrentDataFileName + ''' 
TO N''' + @TargetDataFileFullPath + ''',  
MOVE N''' + @CurrentLogFileName + ''' 
TO N''' + @TargetLogFileFullPath + ''',  STATS = 10
'
--PRINT @SQL
EXEC (@SQL)

rename_database_physical_files.sql

Loading

VSDBCMD troubleshooting

Sometimes, just sometimes, I plunge right into using a tool without reading too much documentation. This has some advantages and some drawbacks. Trying to decipher error messages without the right background knowledge can be a hard nut to crack. Just now I found myself in one of these rare occasions, where I had to make the best of seemingly cryptic error messages.

The task at hand was to deploy a new database on a newly installed server using the vsdbcmd tool. The server came with just the rudimentary OS and software installed, hence I was bound for trouble.

My first bump on the head was when I first tried to run this command:

VSDBCMD

I received this error message (rather a critical dialog indicating application crash):

Dialog

This issue was solved by installing the .Net Framework 4.0
I then ran the same command line, and received another error:

VSDBCMD Error

BadImageFormatException is in my book a rather misleading error message, when you look at the actual problem at hand. There needs to be Microsoft SQL Server Compact 3.5 w SP 2 installed, both in 32 bit and 64 bit. Download details: Microsoft SQL Server Compact 3.5 Service Pack 2 for Windows Desktop

All this in a week, where Paul Randal (twitter | blog) has written a nice little piece on where to get your information. A classic on R.T.F.M and I’m glad I didn’t bother anyone but Google, as Google once again served the answer: http://msdn.microsoft.com/en-us/library/dd193258.aspx

Loading

Perculiar Named Calculations

In regards to this article about named calculations in SSAS, I had the perculiar finding today, that NULLs are handled somewhat differently than I would have expected.
In my case I had constructed a named calculation, just as in the article, by concatenating two columns, both of type string and allowing NULLs.
The perculiar part was, that when the second value was NULL, the whole expression would result in a NULL.
So, in order for the concatenation to succeed, the expression should be as follows:

[Marital Status] + CASE WHEN LEN([Has Children]) > 0 THEN  ‘ ‘ + [Has Children] ELSE ” END

 

Loading