Showing posts with label ssas. Show all posts
Showing posts with label ssas. Show all posts

Friday, February 24, 2012

How to use cell data security with visual totals?

Hi,

We are working on a project with SQL Server 2005 and SSAS.

We created several roles using dimension security and one special role that requires cell data access permissions (i.e. cell security).

This last role is giving us trouble since we do not know how to implement visual totals on cell security.

A problem description follows:

1) We have a budget and expenses cube with a) Account, b) Time and c) Cost Center dimensions
2) The special role was created for a group of users who have the following requirement for data access permission:
- for some cost centers, restrict access to some accounts
- and for the rest of cost centers see all accounts
3) When looking at data at the cost center and account level, the role works fine. Restrictions are applied correctly.
4) However when looking at the data at the cost center level ONLY, the aggregated total shows a sum for all accounts, regardless of restrictions.


We know that if you use regular dimension security, you can enable Visual Totals in order to have aggregated values that reflect
the restrictions applied at the atomic level.

However, we do not know how to apply visual totals on cell data security. If anyone has faced similar situation,
we would appreciate any help on how to do this.


Another way to put it:

1) In a cube with a Product dimension (Family > Category > Product) and a Geography dimension (Country > State > City)
2) You can set dimension security to restrict access to certain products.
3) If you enable Visual Totals when creating the role, the aggregations at the Category level will show total values excluding amounts for those restricted products.
4) If you do not enable Visual Totals, the aggregated totals at the Category level will add up all products, regardless of access restrictions at the product level.

We are trying to achieve the same with Cell Data Access Permissions, but there is no option to Enable Visual Totals. For instance:

1) Same cube as above
3) Your create a role with cell data access permission; for some cities, you restrict acess to some products, and for all other cities allow access to all products.
4) When you look at data at the City level, you want to aggregate totals not for all products, but excluding amounts for those restricted ones.


We would appreciate your help on this enormously!

Cheers!
jbadillo@.streamnova.com

Cell security was not designed to support visual totals as you described.|||

Hi,

Is there a way around this? Instead of using visual totals, is there some other way to get the right totals?

Cheers

|||

One approach, which seems to work in limited testing with Adventure Works, would involve introducing cell security MDX conditions into the cube MDX script. Basically, a scoped calculation sets the secured cell values to NULL, which will cause the aggregation of those values up the hierarchy to adjust accordingly. However, when cube cells are browsed, the secured cells will still show "#N/A". The limitation with this approach, of course, is that user and/or role logic will be needed, so that unrestricted users are unaffected.

The simplified scenario in Adventure Works was as follows:

For user XYZ, secure the data for Bikes in CY 2003, and rollups in the Sales Summary measure group should reflect this.

The Cell Read Permission expression (for a role with user XYZ as member) is:

Not ([Date].[Calendar].CurrentMember is [Date].[Calendar].[CY 2003]
and [Product].[Product Categories].CurrentMember is [Product].[Product Categories].[Bikes])

And the cube calculation MDX script is something like:

Scope(MeasureGroupMeasures("Sales Summary"),

[Date].[Calendar Year].&[2003],

[Product].[Category].[All Products].[Bikes]);

IF UserName = "XYZ"

THEN this = NULL END IF;

End Scope;

How to use already created Dimensions in SSAS project in new Cube

I have an task in which I have to use already created Analysis Services project for building up new cube. now for building up new cube i need to use some of the dimensions that are already in place. and some i have to create.. Till Data Source View I have almost done. But while cube designing from Data Source View I am facing problem. I am not able to use already present dimensions.

I am using wizard to construct CUBE. now what happens when i create the cube using wizard it automatically creates already present dimensions under new name by appending 1 or 2 etc. I tried the other way by unchecking the already present dimensions but this generates other problem after finishing this way i couldn't see relationships presnt in fact and dimension table as expected by me.

I am new to Analysis Services being my first project i am unable to come out of situation. Somebody has told me by constructing CUBE by not using wizard instead doing it manually will sort out the things... Can anybody provide me any link in which there is explanation how to design cube manually OR solution to my problem

Please mail me at mandipjutla@.hotmail.com

Thanks
Mandip

Hi Mandip,

If you have the dimension already defined in the project all you will need to do is add them to the cube by taking the following steps.

1. Edit the cube that you want to add the dimension to. This OK even if you have created the cube via the wizard.

2. Remove the existing dimension created by the wizard from the cube, by right clicking and choosing delete.

3. Add in the existing dimension that you want by right clicking and choose add.

4. Even if it puts a different name, you can always rename it.

I hope this helps,

David Botzenhart

|||

Hi David,

Thanks for the help.

I have applied the same process as explained by you. But still i can't see the relationship between fact and newly added dimension on Cube's Data Source View.

I have also set up the relation betwwen fact and dimension by going into Dimension Usage tab.

Thanks,

Mandip