This little one isn’t very obvious and can cause a lot of hair pulling, so I decided to write it down here.

Basically in reporting services you can make use of reporting parameters using a combination of dataset query parameters and Member(<<string>>) functions. Hence you can define a parameter that returns a string ie: ‘[Organisation].[Hierarchy].&[1232]‘

Sometimes, like today, I need to send down just the Ids themselves, as these get plucked directly out of a querystring, so I might have say: 1232,144,664  as my key values. I wrote a quick CLR function that I plugged into analysis services which will convert n values into a string of:

{[Organisation].[Hierarchy].&[1232],[Organisation].[Hierarchy].&[144],[Organisation].[Hierarchy].&[664]}, which then gets returned as a set and life goes on.

However, when I was defining the query parameters, @DepartmentID (and all the other parameters) needed a default value. This was annoying as this value was bound to the [Organisation] dimension, and hence it would insist on binding to the values therein.

Little did I know that you’re allowed to define a query parameter that’s NOT bound, although it still needs a default parameter. Unbinding from the dimension will save a headache, and let you pass parameters in as mentioned.

I’ve been doing some R&D work on a new integrated security model for our database/analysis services lately, with one of the main requirements being a system where security changes made to the Sql server database will automatically propogate through to the SSAS environment.

To illustrate this point, I employ the use of a departmental structure. For instance, if in the database, a user gets shifted to a different department, he/she should have those changes automatically replicated when they log into the SSAS server and view cube data. Ie: they should be restricted to view departments and their children that they belong to.

The thing about SSAS is that it sits alongside Sql Server, rather than a subset; and since it’s seen as a seperate entity that can be made up of a number of databases, subsequently don’t inherit any of their rights. Roles can be set up within SSAS, which we can use to mirror the security structure of the Sql Server. As long as windows authentication is used in Sql Server, a flexible model can then be created to manage both servers.

Coming back to the departmental structure, how can a role be created that restricts the user to viewing his departments and it’s children? This can be achieved through by creating a Department dimension within SSAS, and creating a new role that returns an allowable member set via a CLR Stored Procedure that connects to the original SQL Data source.

Creating a CLR Stored proc for SSAS is pretty simple. All you need to do is create a new class library, and within a new class do something like:

Two things to note:

1. you MUST connect to the sql server using SSPI, as when it connects to the database, you want it connecting as the current user who’s on SSAS
2. the vwDepartment view filters all rows based on the current logged in user, and hence only returns departments based on those that the user has access to

Compile that into a dll, and on the SSAS side of things, go to the assemblies folder and:

1. New Assembly
2. File name -> browse to the dll
3. Include debug info if you want to attache the debugger later on
4. Permissions -> external access (as it accesses an external source)
5. Impersonation -> use credentials of the current user

You should now have access to the stored proc within any of your MDX scripts. What is needed is to restrict department rows to the a certain user role. Open up the role (or create a new one) for the one you want to restrict and:

1. Dimension Data
2. Dimension -> Department
3. Attribute -> Hierarchy
4. Allowed Member Set -> <Your Assembly Name>.GetAllowedDepartments()

Save that and try browsing the cube as that role. If you drop the department dimension in, you should see that only the departments the user has access to are visible.

This model also relys on the fact that members of the Department dimension rely on a single ID attribute, named with the department name; as well as kerberos authentication being properly set up.

I’m hoping that this sort of model will deliver the flexibility and centralization needed to cut out the maintenance and effort needed by both developers and administrators of the system. So far, it’s looking pretty good.

Ok,

It’s been a while since I posted this question on forums.microsoft.com with little result.

The issue is that in any SSAS implementation containing employees and departments, you’re likely to get the scenario where you want to drill down by department through to the employee level. Add to that the requirements that

1. there be n number of departments, sub-departments and levels (where n is unknown and can change),
2. that employees can sit in any level of these departments, and
3. that employees can also move to different departments as time goes by (ie promotions, demotions, rotation etc)

IMHO, this is a pretty common scenario, yet couldn’t find a proper solution anyway. Add to that the fact that I don’t consider myself an expert in SSAS, and I’m too stubborn to go out & buy a book on the subject, I came up with this solution which solves my problem perfectly.

This was my original schema as it would appear in the database (albeit simplified for the purposes of the example).

As you can see, I have a fact table – vwCall which holds telephone call information, department which holds a hierarchical view of the departments, an Employee table, and a link table that links an employee to a department given a time period.

The first thing you need to do is to consolidate the department, employee & employeedepartment link table into a single named query. This was done thusly:

Let me explain. I’ve negated the DepartmentID and unioned it with the EmployeeID (from the vwCall view) to form a non-conflicting KeyID field. This is a unique key regardless if it’s describing a department or employee. Next, I’ve queried in the unaltered DepartmentID and EmployeeID, to enable the named query to eventually be linked up with the fact table (vwCall). Finally I added the name, which is going to return either the Employee Name or Department Name.

To paint a picture, here is a sample set of the output data:

As you can see, employee records also have a ParentID, which links them back to their department. I’m using the fact table to build a hierarchy of the employees, as it adds in the date dimension and will handle cases where at one point in time, an employee might make a call from “Software”, get rotated into the “Managment” department & make a call there. This will obviously show up as the employee appearing in both “Software” and “Management” departments, which will make sense to the OLAP user when they add a time dimension to the mix.

When that’s done, the new schema in the data source view becomes:

Nice huh? That’s the fact table linked on DepartmentID and EmployeeID. ParentID links back to KeyID to perform the Department-Department and Employee-Department link.

The final step in the whole process is to create the dimension.

Create a blank dimension and add the EmployeeHierarchy named query (above) to it. Drop in KeyID, set it’s usage as “Key”, then set the NameColumn to EmployeeHierarchy.Name.

Next, add in the ParentID attribute and set it’s usage as Parent, and add it as an attribute relationship under KeyID if it’s not done automatically.

Finally, add the dimension to the cube you want to use it with. Under the “Dimension Usage” for that cube, define the relationship type as “Fact”, linking the fact table’s EmployeeID field on KeyID of the dimension we just created.

It’s then just a case of deploying and processing your cube.

I’ve heard people speaking about slowly changing dimensions as a solution to this, but as far as my requirements went, that solution wouldn’t suffice. I feel that although this is slightly more complex on the data source view side of things, it gains through not having to redesign the underlying database schema and actually strips away complexity on the dimension designer side of things.

SSAS - it’s a steep learning curve.

One of the problems I faced recently was trying to write a click-through deployment for a web application which not only has a SQL backend, it also needs to deploy & build an Analysis Services project.

The issue is that when you create a web setup project, there’s no intuitive interface to allow you to do such a deployment as part of your installation.

Solution? Deploy the analysis services project dynamically.

The first thing you need to do, if you haven’t done it already, is to create a new class library to handle all these sorts of custom tasks during your installation.

1. Create new class library
2. Create a new folder within it called Olap
3. In the Build Events section of this project, add the following Pre-build event command line:
   • copy /Y $(SolutionDir)Olap*  $(ProjectDir)Olap 
   • where $(SolutionDir)Olap is the project directory of your analysis services project
   • This will copy over all the project files for your analysis project to your installer class before each build
4. Build the project to copy over all the files from your analysis services project
5. Within your class library, add the new files to your project, and change their build action to Embedded Resource
6. In the root of your class library, add a new Installer Class called Actions

What we have now are all the resources needed in order to deploy our cube on install. I recommend that you have a look inside your class library to see where your olap files are stored. Many people have a problem with not being able to find their embedded resources within a given assembly due to leaving out namespaces or what have you. A great tool to peek inside your .net assemblies is Lutz Roeder’s Reflector for .NET http://www.aisto.com/roeder/dotnet/ or you can always use the [assembly].GetManifestResourceNames() if you feel so inclined.

The other thing you need to do is to ensure that your class library gets called when your MSI is installing. Add the primary output from your class library to the setup project, and then in Custom Actions, add ‘Primary output from <your class library>’ in the install directory.

Jump into your class library’s installer class (Actions.cs) and override the Install method. The general process is that you read through each of the analysis services project files in order of dependency, building an XMLA script. XMLA (XML for Analysis) is essentially a macro language that instructs analysis services to perform a whole heap of actions. We can leverage this to build & then process our olap project.

First off, we need to put all of our operations in a batch, as we want to execute the XMLA at the end as a single script. Create a new XmlDocument that’ll hold your XMLA script. It should have a root node of:

Next you need to add to this an Xml Element to create a new analysis services database on the server:

Which should produce something like:

What you need to do next is simply read through your embedded resources to add the rest of the <Create> elements for your project. You’ll need to create them in the following order: 

You need to remember that you’ll probably have a whole heap of connection strings that you’ll need to set if it’s a data source, so remember to edit the XML as needed. Once this is done, you need to add a process command to build the cube for the first time.

And then you’re done. All you need to do now is create a connection to the analysis services server and execute the generated XMLA as a AdomdbCommand as such:

Remember, you can also save the XMLA and run it directly through Sql’s management studio to see what bugs you’re turning up.

Hopefully this’ll give you a good introduction into XMLA and how you can use it not only for deployment, but also to administer and manage your analysis services projects from your applications.