Analysis Services Security with Richard Lees

Tonight I attended this month's SQL Server user group session in Sydney on the topic of Analysis Services Security. The presenter was Richard Lees, the managing director of EasternMining.

Initially there was a bit of problem with the projector and the presentation got delayed. But they managed to source another projector so that the presentation could start.

Richard started with a few slides followed by plenty of demos. He ran through the standard security options in SSAS. Then he moved on to explaining how you could utilise the security features in interesting ways. Here are some bullet points:

• All SSAS connections use Windows authentication - unless RequireClientAuthentication is set to 0.
• SSAS service account needs to be able to read the data source.
• SSAS security has several levels of granularity: system (OLAP Administrators), database (Administrators), data source, cube, dimension and cell.
  

One of the interesting ways to utilise security was what he called 'Functionality by Security'. This is by giving user the right amount of access to the data in the cube. Then, with the report authored to fit that security model, it could show different results to different people.

For example, a data warehouse may contain all the data from different stores of a franchise chain. You could have a dimension with the different store IDs, and link the store IDs to the store owners' logins in some way. In the cube you can use a filter to restrict the access based on the login/store ID. Then just one version of a report could show the data appropriate just to that store owner.

One interesting demo he showed was that he's been collecting the various data points using PerfMon on his machine. He then used SSAS and ThinSlicer to drill into the various performance characteristics of his machine. We could see SQL Server was the program using up most CPU, with another program call 'Gw' at the second spot. He explained that 'Gw' is a game called Guild Wars, which his son has been playing quite a lot. He showed that he could even drill down into the process's details to see what time his son has been playing.

At the end of the presentation, he said that building cubes without knowing MDX is like creating SQL Server tables without knowing T-SQL. You can certainly do it, but having MDX knowledge will broaden your understanding that much more. He went on to recommended a couple of MDX books:

• Fast Track to MDX by Mark Whitehorn, Robert Zare and Mosha Pasumansky. This is an introductory book to MDX. It is great for people just starting out on the language and explains clearly various concepts for beginners.

• MDX Solutions: With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase by five authors. This is a more advanced book and goes into greater depth on MDX. Some would consider this the MDX bible.