SSAS: How Role information is saved

In this post I’d like to explain how role is saved in Analysis services database, so we can learn an important conclusion in permission process design: dimension data can be defined on attribute members that are not existing yet at role update time.

How role is saved in DB? If you’ll look at “Program Files\Microsoft SQL Server\...” folder of your server, you’ll see that roles files are saved as XML files, like all other structure info of SSAS database elements (find explanation how you can see a similar version of the role XML code from Management studio in this post).

Members selection of Dimension Data tab is saved, for each secured attribute, as a set. For example:

{[VERSION].[VERSION].&[650], [VERSION].[VERSION].&[660]}

You can see the set in Advanced tab of role properties screen, under Dimension data:

 

When cube is browsed or queried, the attribute members list is filtered on this set. Like in a regular MDX expression, if this set includes a member that doesn’t exist, it’s ignored and set is applied without it. But member is still saved in role source code! So if later this member will be added to the attribute, permission will be completely applied!

This fact can be important when we schedule our different processes.  For example, if we want to secure “product” attribute, and new product will be added to dimension only after next process, we can already run permission process (or edit it manually in advanced tab) earlier when server is free and not only after process, when server CPU must be released as soon as possible for users.

2 remarks:

·         If you look at Basic tab of your attribute, you will see only existing members. And if you edit the list from this tab, it will overwrite the set by a new one and members that don’t exist will be deleted.

·         Membership tab of the role works differently. If you try to add a user that doesn’t exist (manually, or by AMO code), action won’t be done at all so he won’t appear in role XML code.

SSAS: Administrator role is not really admin!

I discovered this week an interesting bug in SSAS (both 2005 and 2008 versions). A user that was supposed to have full permission complained he can see only few members in a dimension. I checked his role and it really was defined as “Full control (Administrator)” (Checkbox in General tab of role properties). After short investigation I found that this role was in past with those limitations in members, and later the admin checkbox was selected to make this user admin on DB. So in Dimension Data tab settings were still saved,  and in fact they still were applied! I tested it in many cases and in both 2005 and 2008 versions: every time a role is edited to “Full control (administrator)”, it still applies settings of Dimension Data tab.

Someone might explain it by the famous “It’s not a bug, it’s a feature!”. Well, It might be nice to have an option that even admin user can limit access to members for any reason, but in this case when admin checkbox is selected, Dimension data tab becomes gray, and you cannot edit or even read it, for the simple reason that it’s not relevant now. Note that in Cubes tab there is no bug, and even if not all cubes have “read” value in Access column, admin user can browse any cube.

So the solution to my issue was to uncheck admin checkbox, select all members in this dimension attribute, and select checkbox back..

SSAS: How to copy roles between servers or databases

In SSAS there is no direct option or wizard to copy roles between different servers or databases. In this post I want to explain how we can copy one or more specific roles in few steps. It can be useful for example if your roles are created on production database, and you have a security issue with one on more roles, so you want to move them to test environment to check it.

Because this method moves every role separately, if you need to move all roles, it’s better to synchronize (or backup and restore) entire database. If you have different version of your DB on target, you can transfer database from source, and then redeploy the target solution with deployment wizard, using “Retain roles” option. Then you will get your target database but with roles from source.

Here are steps for moving one role:

1.       On source server, right-click on role and select “Duplicate…”.

2.       Enter a new name for role (for example “temp”).

3.       Script it to new window. Click on cancel and not on OK (we only need the script, not to do it!)

4.       Make replacements in script:

a.       <RoleID>Role</RoleID> to <RoleID>Your role Id</RoleID>, for example <RoleID>User1role</RoleID> (one or many occurrences). If you are not sure what is your source role ID, you can get it by scripting the role to create:

b.      <ID>Role</ID> to <ID>Your role Id</ID> (one occurrence)

c.       <Name>temp</Name> to <Name>Your role name</Name> (one occurrence).

d.      If you move roles between databases with different names, replace <DatabaseID>Source DB</DatabaseID> to <DatabaseID>Target DB</DatabaseID> (one or many occurrences).

5.       Run the script on target server.

That’s all for one role, do it for every role you want to transfer.

SSIS: How SSIS Configurations are applied

SSIS configurations are applied on runtime.  Here are 2 details it’s very important to know about how configurations are applied:

Applied once only: Configurations are applied once only, at the beginning of the Package running. All values that are configured are loaded in this time, and not at real-time when they are used in the control flow task. It means that you cannot edit SSIS Configurations sources after package started running (more exactly it won’t apply on current running).

Configurations Order:  When you have multiple SSIS configurations, SSIS applies them is the order they are defined in SSIS configurations window, except “Parent variable” that is always applied at the end, no matter where it’s positioned. Keep it in mind if 2 configs overwrite the same value, to ensure the final value will be the one you want! You can edit the order with the arrows on right side  of the edit window.

If you use dtexec command with configurations in command line, read Microsoft article about changes between SSIS 2005 and 2008.

SSAS: Which change makes the cube/dimension unprocessed?

In many cases, some of OLAP cubes are huge and Full Process takes a lot of time.

Therefore, it’s important to know which change, when the updated solution is deployed, causes the cube to move to “Unprocessed” status and needs new full process. Dimension process status is also critical since if dimension moves to unprocessed, all cubes that have connection to this dimension will be unprocessed too.

Here is the detailed tested list of different actions, and how they apply on cube (tested on SSAS 2008. I don’t remember any difference with  SSAS 2005, but not certified)

We can summarize the results in one sentence: Every action that affects data will move cube or dimension status to unprocessed. Such changes are also called structural changes, meaning any change that apply on cube/dimension structure (new element, edition on aggregation method, etc.) and therefore will require data loading or reloading.

One exception: Adding or deleting translation to dimension unprocesses it when deployed. I don’t know why..

And now to the detailed list:

Type	Action	Unprocess cube/dimension?
Cube	New measure group	Yes
Cube	New measure	Yes
Cube	Edit measure aggregation method	Yes
Cube	Measure format	No
Cube	Measure name	No
Cube	Measure display folder	No
Cube	ErrorConfiguration edition	No
Cube	Edit dimension usage	Yes
Cube	Calculations	No
Cube	Add, edit or delete kpi	No
Cube	Add, edit or delete action	No
Cube	Edit partition query	No, not applied till next process.
Cube	Add new partition	No, new partition unprocessed.
Cube	Edit partition storage mode	No, but data is empty.
Cube	Create,edit and assign aggregations	No, not applied till next process.
Cube	Add, edit or delete perspective	No
Cube	Add, edit or delete translation	No
Dim	Add attribute to dim	Yes
Dim	Edit attribute name	No
Dim	Order by property of attribute	Yes
Dim	AttributeHierarchyVisible property	No
Dim	Edit attribute relationship	Yes
Dim	Add or delete translation to dim	Yes
Dim	Edit translation	No
Other	Add, Edit, Delete Role	No
Other	Edit data source	No, not applied till next process.
Other	Edit DSV	No

Finally, 3 remarks:

·         BIDS Helper add-in gives you possibility to deploy MDX script or aggregations only, so you can be sure nothing else is affected.

·         Use Deployment Wizard tool when you want to deploy without overwriting Partitions or roles, in cases they were edited in the OLAP DB.

·         If your cube is down and you need to have one measure group/partition available without processing the entire cube (because of process long time), you can first process the cube with “Process structure” Process mode. It will move cube status to Processed but without any data. Then you can process the specific part you want and it will be available.

SSIS: Issue when waiting for 2 tasks completion

Here is a case in SSIS I got. We have a package that performs a first task, then do 2 parallel actions (in screenshot there are data flows, but it doesn’t matter), and then a 3rd step that must wait for 2 parallel actions ending.

The critical fact is that one of 2 parallel actions is done only if condition is true.

Apparently it must be built like that:

The issue: In case the condition is not true, the package will never continue to “Next task”!!

In fact it’s very logic: “Next task” is waiting for 2 DFs to be done, and the first one doesn’t run because condition is false. So it will never send “completed” answer!

The solution is simple:

A new conditioned path was added with the contrary of first DF condition. It’s connected to dummy task (drug a script task without editing it at all).

Data flow is also connected to dummy task with OR logical connection between 2 sources. Then this task and DF2 are connected with AND connection.

This solution is equal to this logical expression:

(

([Condition] AND [Data Flow 1])

OR

(NOT [Condition])

)

AND

[Data Flow 2]

BIDS: “Insufficient Memory” error on running SSIS

On one of our servers, I got this error in Visual Studio BIDS 2005 when trying to run a SSIS package I built:

“Insufficient Memory to continue the execution of the program” in a message box.

It was solved by installing Visual Studio 2005 Service Pack 1.

Link: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=5553

MDX: Rewrite query with CurrentMember for multiple selection

One of famous MDX issues is that you cannot use “CurrentMember” keyword when you have multiple selection on an attribute. Read Mosha’s article to understand the full picture.

In a few words, the solution is based on using EXISTING and EXISTS keywords.

EXISTING filters  a set of members to selected members only, it’s in fact the “currentmembers” missing keyword.

EXISTS is a function that returns members of first set that are included in the second set.

I’d like to show 2 concrete samples how to rewrite your query. You can run them on Adventure Works DW OLAP DB. Anyway it's important to notice that not every query/measure can be rewritten in this way, and sometimes the best way is to change the approach to the solution.

1)      Count days in selection

We want to count how many days are included in selected period. If 2 months are in selection, we expect to get the sum of each month values.

The “Single” version takes all descendants of current member in Date (day) level and counts them.

The “Multiple” version calls to an internal measure, similar as single version, for everyone of all selected members.

WITH

MEMBER Measures.[CountDays_Single] AS

COUNT(DESCENDANTS([Date].[Calendar].CURRENTMEMBER,[Date].[Calendar].[Date]))

MEMBER Measures.[CountDays_Multiple] AS

sum(existing [Date].[Calendar].[Month].MEMBERS,Measures.[CountDaysM])

MEMBER Measures.[CountDaysM] AS

COUNT(DESCENDANTS([Date].[Calendar].CURRENTMEMBER,[Date].[Calendar].[Date]))

SELECT {Measures.[CountDays_Single],Measures.[CountDays_Multiple]} ON COLUMNS

FROM [Adventure Works]

where ({[Date].[Calendar].[Month].&[2003]&[10],[Date].[Calendar].[Month].&[2003]&[11]})

2)      Check member is selected

In this case we want to check if a specific member is selected. In multiple version it means that we want to check if it’s one of selected members.

The solution is to check if the specific member EXISTS in the set of EXISTING members.

WITH

MEMBER Measures.[RightMemberIsSelected_Single] AS

iif( [Account].[Account].currentmember is [Account].[Account].&[48],'Yes','No')

MEMBER Measures.[RightMemberIsSelected_Multiple] AS

iif( COUNT(EXISTS ([Account].[Account].&[48],{EXISTING [Account].[Account].[All Accounts].children}))>0,'Yes','No')

SELECT {Measures.[RightMemberIsSelected_Single],Measures.[RightMemberIsSelected_Multiple]} ON COLUMNS

FROM [Adventure Works]

WHERE ({[Account].[Account].&[48],[Account].[Account].&[2]})