x

Restricting Access to Measure in SSAS

Hi,

I have a measure "ThirdPayment" , users of the role shouldnt see the value in detail...like for example..

ChannelName.Level3 Time.Level3 ThirdPayment

So if the users drills to Level 4 of Channel Name and Level 4 of Time no value should be seen for the ThirdPayment.

This scenarion only exist for ThirdPayment Measure.

Request you to please help..
more ▼

asked Aug 27, 2012 at 04:34 PM in Default

Kirman50 gravatar image

Kirman50
0 2 2 3

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

You can define a Cell Security for particular role in the SSAS Database.

See Granting Custom Access to Cell Data on MSDN.

more ▼

answered Aug 28, 2012 at 06:00 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

Pavel..

I want to disable the Drill To Detail Option available in ProClarity for the Measure Third Payment.

In the Cube Role I have added the following MDX in the Denied Member set of Dimension Data Tab

DRILLTHROUGH MAXROWS 1000

SELECT {[Measures].[Third Payment]} ON 0 FROM [BARS]

RETURN

[$Trade Date].[Date],[$Value Date].[Date], [$Customer].[Customer ID], [$Customer].[Customer Name], [$Customer].[Fund Manager Name], [Fact Trade TPFX].[TPFX - Buy Amt], [Fact Trade TPFX].[TPFX - Sell Amt]

But it is giving syntax error..is this the right approach.

Please help
Aug 28, 2012 at 01:31 PM Kirman50
So you want only to disallow access to the details when executing the drill through action? Or also prohibit access to the aggregated value of that particular measure?
Aug 28, 2012 at 02:10 PM Pavel Pawlowski

Pavel..

I want to prohibit access to the details BUT show the aggregated value of that particular measure.

Please help..
Aug 28, 2012 at 04:51 PM Kirman50
(comments are locked)
10|1200 characters needed characters left

Hmss.. Basicaly thre will be two possibilities.

  1. Disable the drill through completely for particular role
  2. Use the dynamic security approach in the condition of the Drill through action (Dynamic Security in SSAS Cube). In that case the drill through action will not show to the user. If you wnt the drill through to be available, only hide that concrete field for particular users, than define two different actions and depending on the user allow one or the other.
more ▼

answered Aug 29, 2012 at 05:39 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

link textPavel...

As per your solution to disable the DrillThrough for a particular role.

We have set option as "None" for "Local Cube/Drillthrough Access" in SSAS Role.

This option will disable both Drill To Detail & Actions in ProCalrity.

Regarding SSAS Dynamic Security , yes we can put that in Actions BUT "Drill To Detail" option will still be available in ProCalrity.

Attaching a short document which expalin the steps which have been performed.

Aug 29, 2012 at 09:57 AM Kirman50
(comments are locked)
10|1200 characters needed characters left

As per your solution to disable the DrillThrough for a particular role.

We have set option as "None" for "Local Cube/Drillthrough Access" in SSAS Role.

This option will disable both Drill To Detail & Actions in ProCalrity.

Regarding SSAS Dynamic Security , yes we can put that in Actions BUT "Drill To Detail" option will still be available in ProCalrity.

Attaching a short document which expalin the steps which have been performed link text

more ▼

answered Aug 29, 2012 at 10:02 AM

Kirman50 gravatar image

Kirman50
0 2 2 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x172

asked: Aug 27, 2012 at 04:34 PM

Seen: 1591 times

Last Updated: Aug 29, 2012 at 10:02 AM