dinsdag 20 september 2011

SSRS: 5 Attempts and 2 solutions with reportfilter and multiple conditions (grmph)

This is annoying me for a long time now: filter records based on multiple conditions in Reporting Services (SSRS). I'm working with power users and they are using reportbuilder 2.0 and they want to select multiple conditions for rows in a tablix. Simple? Intuitive? Easy for endusers? Not quite (in my opinion).

The problem

Attempt 1
Suppose i've  a simple fact with a department dimension and i've 5 factrecords. I know that department A and E has a factrecord. Therefore i'm using them to test this filterbehaviour. Now i want to select two departments with the report filter. On Technet i've found the following example :

The first thing (as most of us will do and as my poweruser were doing) is trying Department_Description = Department A, Department E

and no results appear. Okay, well i know something about SQL and there is an IN operator available for this kind of filtering. In SQL you willl type something like this :

WHERE Department IN ('Department A', 'Department B')

Attempt 2
So the next thing i tried was the IN operator

But no luck here too.

Attempt 3
The next step is using quotations

This gives me some errors. Now what? Coming back to the Filter window shows this:

Reporting Services has changed the constraint to this :  """"Department A, Department B"""". That seems a bit awkward. I think that three " are needed for escaping other " characters. When i'm building dynamic sql i need to use these too.

Attempt 4
Perhaps single quotes?


Attempt 5
Round Brackets perhaps?

Nothing again... grmph...Getting desperate here! When i return back to the window every time """" are added to the condition. 

The solutions (?)

Solution 1
On MSDN i've found an example. I  need to set the expression to =(Fields!Department_Description.Value = "Department A" OR Fields!Department_Description.Value = "Department E")

and set the Filter to boolean.

Solution 2
Another solution i've found on a site of data inspirations :

And the expression is : =SPLIT("Department A" + "," + "Department E", ",")

The solution proposed on the MSDN forums is not very intuitive. The other example from the site data inspirations is a bit more intuitive but you have to use the SPLIT function.

Reportbuilder is used by end users for building Self Service BI reports, but this filter functionalitity is hard to understand. I hope Microsoft do a better job building the successor of Reportbuilder.


zondag 18 september 2011

SSAS : Field (Y/N) 'a' in Fact A impacts on rows Fact B

I've a requirement from a customer about combining facts and selecting them with a Y/N field. This blogpost descibes this requirement and the translation in a solution. This blogpost is simplified version of the 'real world' situation. Suppose you have two facts : 'A' and 'B'. Fact A has a field 'a' and this field can be Yes or No. The user wants to use this field to limit the rows in Fact B, somehow.

When i started to investigate this problem, i came to three options:
  1. Put the Yes/No field a in a separate dimension and use the dimension in both facts
  2. Load the Yes/No field during the ETL in both facts.
  3. Create an view where the two facts and the field are combined.
In my opinion option 1 is more rigid option than option 2. Option 2 is more flexible because i've to ability to set field a in Fact A and B independant. I've not investigated option 3, furthermore.

In this post i'll explain option 1.

The problem

First i'll describe the initial situation i've created. Below you can see my cube with the two facts A and B, two dimensions DimDepartment and Dimdate and the involved field ShowAllYN that should impact on both facts

When i browse the cube in SSMS, select field Fact A Count and Fact B Count and watch the result per department :

The results of Fact A are limited, but the results in Fact B aren't (off course). So, how can i achieve that field "Show All YN"impacts both on FactA and FactB?

Solution (option 1)
The solution that is described in this blogpost is option 1. In option 1 a separate dimension is created, named DimFactA_DD dimension. The ShowAllYN field is not available anymore in fact A, but moved to this DD dimension. Normally you would create a Fact cubedimension for this kind of degenerated dimension fields. But in this case we need to create a separate (regular) dimension.

So the cube structure is now changed to the following structure:

And how does this look like in the cube browser? First let's look at the whether everything works ok when i don't use the DD dimension:

The next step is using the DD dimension for both facts:

As you can see selecting the Y/N field has an impact on the measures now, just as we wanted.

In this post I've described one option that can be used for selecting multiple facts based on one field. There are many variations on this topic. One variation could be that you leave the Y/N field in the FactA, add one in Fact B and use the combined DD dimension. This way a very flexible solution can exist, but you have some redundancy.

I've implemented option 2 at the customer and this gives me more flexibility than option 1 but the solution descibed in this blogpost is a much nicer solution in my opinion.


maandag 12 september 2011

SSAS : Setup dynamic dimension security in Analysis Services

In SSAS implementations there is always a demand for implementing security. There are multiple possible security options and this blogpost focuses on data level security or row level security : security that depends on the useraccount and connects users to certain rows in a table. Users needs to be managed as new users appear, some will disappear and from some users may change their role.

In this post i'll descibe the dynamic dimension security in SSAS i've built at a customer. It's also called 'attribute hierarchy security'. Dynamic dimension security is about allowing restrictive number of users to a certain set of information in your cube. So you need to create an allowed set of members that users in a role are permitted to access. In this blogpost i'll be defining a set by building a MDX query for showing departments for certain users.

In this post we will build a security based on a department dimension. The cube is secured by the department dimension, a bridge tabel and a user table. In addition, i have leveraged two local user accounts: SSASUser and SSASUser2.

Here are the steps that needs to be taken for a successful implementation of dynamic dimension security:
  • Create a table users with username field that should store the Windows accounts.
  • Create a bridge table that models the M2M relationship between users and department.
  • Fill the tables with appropriate values.
  • Add the tables to the datasourcce view of the SSAS project.
  • Create a user dimension.
  • Create a measure group for bridging the users and department.
  • Create regular relationships between users and the bridge userdepartment.
  • Create a new role in BIDS/SSMS.
  • Add MDX statement to the Allowed member set and set the Enable visual totals checkbox.
  • Deploy and test. 

Below are the steps described that i've taken to build a dynamic dimension security.

1) Create two SSAS users. I'll be using these users for testing the dynamic dimension security. I've added these to the SSASusers and the SSASUsers2 group.

2) Create some tables in SQL and build a cube on the tables with the following structure:

There a couple of sections involved in this diagram: a fact called, FactTest, a M2M dimension and a bridge for the security. The FactTest is a small example with two dimenions Date and Location. The M2M dimension setup is a bridge (BridgeLocationDepartment) and a Department dimension. The requirements of my customer tells me that there is a n:m relation between location and department. For this blogpost i've added two tables : BridgeUserDepartment and user. Most of these tables are described in more detail in other blogposts about SSAS M2M dimensions. You can find them here:
3) So there is n:m relation between location and department, a fact table, a date dimension and especially for this blogpost i've added two extra tables and these controls the security on the cube: BridgeUserDepartment and Users. The BridgeUserDepartment table handles the n:m relation between department and users. The first thing to check is the relation between User and Department:


User_Dim_Key 1 is the SSASUser and 2 is the SSASUser2.

Querying with MDX the Bridge User Department, SSASUser and SSAUser2 shows the following results:

4) In this step w'll setup the dynamic security for this cube. We will create a new role, Dynamic Security, with read access to the ResearchAverage cube.

Add the SSASUser and SSASUser2 users to the membership tab.This step is a pitty one, in my opinion. Now i've to set something at two places: in the database and in the membership tab in Analysis.

Give read access to the cube:

The next steps are done in BIDS (denali) but it makes no difference where you setup the security (in SSMS or BIDS). Only when you setup the security in SSMS, it will be overwritten when the cube is deployed from BIDS. The next step took me a while to understand when i was working at the customer. In this customer situation there are about 50 dimensions and a lot of role playing dimensions. One error i keep on getting was

"An error occurred in the MDX script for the dimension attribute permission's allowed member set: The dimension '[User]' was not found in the cube when the string, [User].[UserName].[UserName].&[<user>], was parsed."

Because i had so many dimensions i didn't see the cube dimensions, at first. So i implemented the security at databasedimension level and not at cube dimensionlevel. The combobox "dimension" stores both database dimensions and cube dimensions. This is very confusing. I'm not completely sure that this also needed in this every situation. Anyway i've implemented the security at cubedimension level.

The following window does the trick. It limits the allowed set because of the usage of the UserName() function. This function returns a windows useraccount like Laptop\Hennie or Laptop\SSASUser.

I've left open the "enable visual totals" (lower left) intentionally and pressed on check (not shown in the window) and we have a....successful check!

The next step is running SSMS with the SSASUser account

And we can only see the departments that are authorized for the SSASUser:

The next step is running SSMS with the SSASUser2 account

And we can only see the departments that are granted by SSASUser2:

Okay, let's try another dimension like date and ...Now we can see all the information. That was not supposed to happen.

You can solve this with "enable visual totals" option.

And now only the facts are shown that belongs to a department:

Other things to do are making things invisible. The Bridge User Department measure and the User dimension provides a base for the security of the dynamic dimension security. These tables are only needed for the security and not for end user querying. So these two should be set to invisible. Important note: these are only hidden and when the end user is familiar with the presence of them, still they can query them.

There some things that are a bit annoying in my opinion:
  • It's not fully dynamic because i've to add the user to the membership tab also. To grant a user i've to add the user to the database and to the memership tab. 
  • The distinction between regular and cube dimension did cost me a lot of time to find out, because i didn't see the cube dimensions at first sight (because the customer had about 50 dimensions).
  • Another problem i've encountered now is that i've a benchmark graph with all departments and the user can compare his department with all of the other departments. Now with this security only the departments which are granted to the user are visible. I'm still working on a solution for this. When i've a proper solution i'll blog about this.
So for securing the cube, dynamic security is a great solution for letting the users play with the cubes with Excel.


woensdag 7 september 2011

SSAS : Set up simple security in Analysis services

In this post i will describe the basics of the security of a cube. For this purpose i'll be using the ResearchAverage cube i've worked before and is described in the following posts:
    Security in SSAS is controlled by roles which allows you to group windows users according to their needs.  (windows) Users are members in roles and that determines whether a user can access a cube (and how).

    1) First set up a environment with the scripts i've used before (see former posts)
    2) The next thing we need to do is creating a user with Computermanagement (Right click computer and manage):

    3) Create a group SSASUsers and add a user to the group:

    4)  Create a role in Analysis Services

    5) Add the user to the role

    6) Select "Read" in the Access field:

    7) Run as SSASuser a SQL Server Management Studio session:

    8) Connect with the Analysis server with the SSAS user:

    9) let's look at the databases :

    No databases?

    10) Let's try a query to check whether we can query the database. The first thing that happens when i press new query is that Database is automatically selected:

    And let's try a query:

    It seems that we can query the database without viewing the definition of the database.

    11) let's try whether we can connect to the cube with Excel

    12) Excel

    Why can't we see the cube in SSMS? On the General tab there is a Read defintion checkbox

    Can we see the cube now in SSMS?

    This blogpost is about a simple security model. This way you can setup quite easily a basic security in a early stage of a project or in case of a small project.


    vrijdag 2 september 2011

    SSAS : Selecting a dynamic period in a MDX query

    In many customer wishes i've encountered so far, they want a dynamic report that shows the data until now. In SQL there is a GETDATE() function avaliable to build dynamic queries with dates. IN MDX there is a NOW() function available. In this small post i'll show you an example of a MDX query with a sliding window principle.

    How could you solve this with MDX? For this post i've used a cube from a former post.

    The most important part is the following snippet:

    STRTOMEMBER('[DimDate].[Date Year].&[' + CSTR(YEAR(now())-3) + ']'):STRTOMEMBER('[DimDate].[Date Year].&[' + CSTR(YEAR(now())) + ']')

    The NOW() function calculates the current date and the CSTR() funtion turns it into a string which in turn can be concatenated with the other string. The : is a range operator that returns a window from 2008 (2011 -3) until 2011 (now).

    Very handy for building dynamic period reports with MDX.