Filter Cube Data

Hi

i am using pivot table 11 to display the data from SSAS Cube. the problem is

1- pivot table fetches all the data and display it according to the design. but what i need is

a- the user could only see the data which was posted by himself.

b. He must be unaware of the data posted by other users.

in database table there is a field named userId which is also which is part of dimenssion in myCube.

i dont want to use filteraxes for this purpose actually i want the user to be unaware of other users of system also

any single helping word will be appriciated

Regards

Salman Bashir

Hi Salman,

You can define custom dimension security rules for the "UserID" attribute - the MDX function: UserName (which returns "domain\user") is typically used for this purpose:

http://msdn2.microsoft.com/en-us/library/ms175366.aspx

>>

SQL Server 2005 Books Online

Granting Custom Access to Dimension Data

...

Understanding the IsAllowed Property

The IsAllowed property determines whether the database role can access attribute members. By default, a database role that has access to a dimension cannot access attribute hierarchies.

Understanding the AllowedSet Property

The AllowedSet property uses a Multidimensional Expressions (MDX) expression to determine which attribute members can be viewed by the database role (the allowed set). The allowed set can include no (default), all, or some attribute members. If you allow access to an attribute and do not define any members of the allowed set, access to all members is granted. If you allow access to an attribute and define a specific set of attribute members, only the specifically allowed members are visible. Specifically defining an allowed set may limit the visibility of attribute members added after the allowed set is defined.

...

Understanding the VisualTotals Property

The VisualTotals property indicates whether the aggregated cell values that are displayed are calculated according to all cell values or only according to the cell values that are visible to the database role.

By default, the VisualTotals property is disabled (set to False). This default setting maximizes performance because Analysis Services can quickly calculate the total of all cell values, instead of having to spend time selecting which cells values to calculate.

However, having the VisualTotals property disabled could create a security issue if a user can use the aggregated cell values to deduce values for attribute members to which the user's database role does not have access. For example, Analysis Services uses the values for three attribute members to calculate an aggregated cell value. The database role has access to view two of these three attribute members. Using the aggregated cell value, a member of this database role would be able to deduce the value for the third attribute member.

If a user can deduce values for attribute members to which the user's database role does not have access, security best practice dictates that you enable (set to True) the VisualTotals property for the attribute. When you enable the VisualTotals property, a database role can only view aggregated totals for dimension members to which the role has permission. For example, enabling the VisualTotals property means that the database role will see an aggregated total that includes only those states (that is, members of the State attribute) that are visible to the role. The aggregated total will not include the values for all states.

Understanding the DefaultMember Property

The DefaultMember property determines the data set that is returned to a client when an attribute is not explicitly included in a query. When the attribute is not explicitly included, Analysis Services uses one of the following default members for the attribute:

If the database role defines a default member for the attribute, Analysis Services uses this default member.
If the database role does not define a default member for the attribute, Analysis Services uses the default member that is defined for the attribute itself. The default member for an attribute, unless you specify otherwise, is the All member (unless the attribute is defined as non-aggregatable).
|||

Dear sir

i am really thankful to you for your such a quick reply. it is no doubt very much helpful but my requirments are a bit different

i have custom role management system and i dont use the roles and user accounts in operating system but there are roles which are defined in my personal database and there are user accounts under those roles.

whenever a user inserts a record in database it leaves a refference tag (His LoginName (which is from a table named users in the same database and have no relation with os or domain users) with that record) which identify that which user is owner of that record.

when user sign in with my application (basically with my own role management system) a global reference to his LoginName (which is from User table in my role management system) is created when his session starts. now from this application he opens a form on which a pivot table is placed which is getting data from the cube (whose datasource is the database i defined in 2nd paragraph) now i want pivot table to send a LoginNameas parameter and cube send back the data with this username and all calculations must be performed on this filter data

|||

Actually, dynamic dimension data security as described above should still work - if you set the SSAS "CustomData" connection property to the custom "LoginName" managed by your app. Then you would access the user "LoginName" via the "CustomData" MDX function:

http://msdn2.microsoft.com/en-us/library/ms145582.aspx

>>

SQL Server 2005 Books Online

CustomData (MDX)

Updated: 17 July 2006

Returns the value of the CustomData connection string property if defined; otherwise, null.

...

For example, this function can be used in a dynamic security expression to select the allowed/denied set members for the string value in the CustomData connection string property.

>>

|||

Hi Salman,

were you able to figure out the solution. if yes could you please post you example details.

Thanks

Milind

|||

I am also having the same problem of filtering the cube data based on users defined in a database. were you able to find a solution for this?

Thanks,

Maximus11