Sistea

|Seattle Office: | |Intercepting and Altering SSAS HTTP Connections |
|2211 Elliott Avenue | | |
|Suite 200 ||Business Intelligence being one of the fastest growing areas irrespective of the |
|Seattle, Washington, 98121 seattle@avanade.com | |domain industry types. Security of Customer’s solutions is always the high |
|www.avanade.com | |priority.|
| | |This Whitepaper will help different Project Teams to understand how incoming HTTP|
|Avanade is the leading technology integrator specializing in the | |connections can be altered or restricted to cover the security risk involved with|
|Microsoft enterprise platform. Our people help customers around ||BI solutions. |
|the world maximize their IT investment and create comprehensive | |This Whitepaper is based on an implementation done in large MS-BI engagement with|
|solutions that drive business results. Additional information can| |a major CG&S client at Accenture. This was implemented on Microsoft SQLServer |
|be found at www.avanade.com. | |Analysis Services 2008. The solution was designed to cover one of most critical |
| | |security requirement/scenario from the business perspective. This document |
| ||contains details of the solution and environment used for the same. |
| | | |
| | ||
| | | |




Introduction

The reports of a normal SSAS solution can be used by a single end user as different ROLES e.g. a single user can be Key Account Manager (KAM), he can also be a Sales Manager (SM). The client may askfor a solution where a single user can do the reporting using only one role at a time in Excel. Let’s take an example to explain the scenario

If the SSAS solution has 2 dimensions Product & Company. The Sales amount for the products & company is as below

[pic]
Figure 1


Client says, dimension security should be applied for KAM as below (allow Product A and Company C)

[pic]
Figure 2For SM as below (allow Product A & Company A, B)

[pic]
Figure 3

Now the client says that Product A with all 3 companies (Company A, B, C) is a critical combination and no user should be permitted to see them together. Now if the user doesn’t have any option to choose a single profile then by virtue of SSAS default behavior, he will see the below content and by this he is permitted tosee critical combination.

[pic]
Figure 4

If the client is using SSRS then it is possible to force the user to select at least one role (KAM or SM) as a report parameter. If the reporting is done using EXCEL 2007 then the user can use the ROLES property of its connection string and work with only one role at a time (User will have to pass the physical SSAS role name denoting either KAM or...