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

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

Filling out missing data in subsequent records?

Hi

I receive several datafiles from another system that are more or less in a Excel pivot table like format.
That is the first row representing the current data is fully filled, while subsequent rows, representing historic data are left partly empty.
Current rows and historic rows have different identifiers, e.g. rectype=0 or 1
Filling out the missing data on the historic record should be simple, if only all current rows would be filled.
Some current rows aren't filled, so the stuff like the following doesn't work:
update t1
set t1.colA =
(
select top 1 t2.colA
from mytable AS t2
where t2.rowid <= t1.rowid
and t2.cola <> 0
order by t2.rowid desc
)
from mytable AS t1

Somehow I need to check for the rectype, so I don't fill out rows with data from a previous entity

Any suggestions before I revert to using a cursor?

And while we are at it: I am in for an easy way to do this for all (about 60) colums in one move?

Before you ask:
After filling everything out we process the file to arrive at a few handy fromto tables, so we can use the correct data about the entity's status at a particular point in time elsewhere

I am using MS SQL Server 2005, and solutions are allowd to use any specific trickery that MSSQL allows.

Many thanks for any constructive thoughts

Cheers

DrioWould this do the job? My changes are highlighted

UPDATE t1
SET t1.colA =
(
SELECT TOP 1 t2.colA
FROM MyTable AS t2
WHERE t2.rowid < t1.rowid
AND t2.colA <> 0
ORDER BY t2.rowid DESC
)
FROM mytable AS t1
WHERE rectype = 0

I havn't tested this code - it's only in my head (/on the screen) so don't use it on your live data ;)|||Thnak you georgev:
for the tagline (I won't do it again; a real eye-opener)
for the small correction in my code and for the direction.

It goes wrong where there are history record after the current reccord with no data.Obvioulsy they get filled from the previous current record that hadd data.

My interim solution
1. update all current records wh data with a dummy value
2. use the fill out query
We then have to check the marked records and see if we can find a pattern that allows us the handle them in code (otherwise someone has to go through them manually; only .25% of total)

Thanks for you swift response

Cheers

Drio