Dimension Security

Hi,
is it possible to secure specific dimension? I'd like to deny selected roles using certain dim. For example, pricing role shouldn't use Customer dim, Basic role should use just Date and Location dim. Could perspectives be used for that (specify dims/measures and grant access just to that perspective) or is there other way? Obviously, I must be overlooking something, this is really basic requirement, isn't it...

TIA,
Radim

Hi! Have a look at roles in the solution explorer. Right click on roles and explore the features.

Perspectives are not a security feature. They are more about easier browsing/navigation.

Regards

Thomas Ivarsson

|||Hi! Thanks, but of course I have tried that :) If you go there, you'll be able to see just two options (at least I do) allowed for dimension security: Read and Read/Write (or to inherit settings). I know that (unfortunatelly) perspectives are more for convenient browsing then for anything else.
Has someone really tried that with success?

Radim|||

On the following tab you have dimension data where you can give access to or deny members in dimensions.

Regards

Thomas Ivarsson

|||That's right. We've implemented dynamic member security system (clr callback function) already, but what I'm asking is how to deny the whole dimension for certain role. I don't want go down to members.

Why this cannot be implemented, is there any internal limitation?

Radim|||

OK. I understand. Can this link be of any help: http://sqljunkies.com/WebLog/mosha/archive/2005/04/10/10599.aspx ?

Regards

Thomas Ivarsson

|||Thomas, I really appreciate your effort :) But yes, I've read all post from Mosha (it's a must for everyone who wants to get deeper into AS :) ). I know that I could solve it by granting access just to Allmember in some dim, but that's not good. My thinking is - why should I unveil dimensions structure if I don't want to? If Customer dim (for example) is not interesting for certain role, why I just can't hide it. Or should I create new cube for that... ridiculous. I'm really missing this functionality and it seems strange to me, that nobody else is asking same question...

Thanks,
Radim|||

Sorry about consumed information. If you are interested in hiding a dimension by code I have not done that. I have implemented dynamic security with a fact table and a virtual cube in AS2000.

What I do not understand is why you cannot use the tab where you allow/deny access to dimensions and point that combinations to separate roles.

I have not tried this by code.

I do not know how big your cubes are but sometimes building a new cube can be the easiest solution, even if it not a good solution. We have done that depending on that we need to deny access to some measures and dependent calculations. Without separate cubes this will mean a large administrative burden. As long as they are in the same project they share dimensions and it is easy to copy scripts.

If you have large cubes I understand that this is impossible.

We have also the problem that ProClarity do not recommend dynamic security with their Analytics Platform.

Regards

Thomas Ivarsson

|||Thomas, in ideal world I'd like to have three choices on Dimensions tab: None, Read and Read/Write. I can see only Read and Read/Write. So that's useless. On Dimension Data tab I can deny all members from certain dim and that does the trick, but dimension is still visible (even though it cannot be used for analysis). Imagine that you have 20 dimensions in a cube (just as an example) and you deny all members for 18 of them. Now, in a browser (Proclarity or anyhing) you would still see all 20 dims, where 18 is completely useless. And that's what I'd like to overcome.

Could you please publish that recommendation about Proclarity? Dynamic security is heart of our cubes and Proclarity is the default browser.

And our solution is still growing, dwh is over 100GB and major cube around 10GB. So don't fancy idea about bulding and processing multiple cubes..

Radim|||

Here is a link to a ProClarity Blog: http://blogs.proclarity.com/blogs/dgustafson/default.aspx, regarding dynamic security.

Edit: I will have to test a little more time but I think you are correct. The dimensions will show but you cannot use them. If you construct perspectives after applying security that can help filtering denied dimensions.

Regards

Thomas Ivarsson

sql

Dim design / MDX help

I have two queries one runs very fast and other very slow. The difference is very little is both. I must be doing some thing wrong is dimension design but not able to solve it.

[Eq up 2sd %] - is a calculated member which I bring to Pivot table to test fast and slow running queries.

When I look into profiler fast query has some events "Query Subcube" and then end query but for slow one I get "Query Subcube" then there is pause with Notification - Flight Recorder Snapshot begin then Server state discover begin/end and same group again and again.

My Instrument Sector Dim has attributes (attributes -> <relation>)

Instrument Desc -> Issuer Desc, Issuer Desc -> Sub Ind cd, Sub Ind cd -> Ind Cd, Ind Cd -> Instrument Sector.

Instrument Desc is Key with Instrument ID as Key field

Query is not only slow it max out cpu 100% too.

Fast Query

SELECT {[Measures].[Gghm Hldng],

[Measures].[Eq dn 50p],

[Measures].[Eq up 2sd %]}

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,

NON EMPTY CROSSJOIN(HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Sector Cd 1].[All]})})), HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Issuer Desc 1].[All]})})))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS

FROM [Position Scenario P and L]

WHERE ([Instrument].[Asset Type Cd].[All], [Investment].[Investment].[Investment Cd].&[228], [Date].[Date].[Year].&[2007].[Quarter 1].[February].[02/21/2007])

Slow Query

SELECT {[Measures].[Gghm Hldng],

[Measures].[Eq dn 50p],

[Measures].[Eq dn 2sd %]}

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,

NON EMPTY CROSSJOIN(CROSSJOIN(HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Sector Cd 1].[All]})})),

HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Issuer Desc 1].[All]})}))),

HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Instrument Desc 1].[All]})})))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS

FROM [Position Scenario P and L]

WHERE ([Instrument].[Asset Type Cd].[All], [Investment].[Investment].[Investment Cd].&[228], [Date].[Date].[Year].&[2007].[Quarter 1].[February].[02/21/2007])

Thanks guys - Ashok

--

Just to add more in my question, when l look into new performance guide there is example of dim like following

Product Key (PK) -> SubCategory -> Category
-> Size -> Size Range
-> Color
-> Description

In my case I am selecting in my query Category, Product Name (Name of key field), and Size Range. This is creating CROSSJOIN(CROSSJOIN 2 of them in MDX for Category and Size Range.

In my cube I have Issuer Desc 1, Instrument Desc 1 in place of Category and Size Range

Hi Ashok,

Could you clarify the following points, to help better understand your scenario?

What's the MDX expression for [Measures].[Eq dn 2sd %] - this could be crucial?|||

Thanks Deepak.

I think the problem was I didn't set "Non-empty behavior" in calculated member.

"Eq dn 2sd %" is a calculated member. I did notice some issues in my Dim design. I am in the middle of final testing I will confirm it has fixed or not today.

Based in my experience the use of "Non-empty behavior" in calculated member should be highlighted in Performance document for OLAP. It worked like magic in query difference but again allow me to confirm that by end of today.

-Ashok

confirmed ! it's "Non-empty behavior" in calculated member

Dim design / MDX help

I have two queries one runs very fast and other very slow. The difference is very little is both. I must be doing some thing wrong is dimension design but not able to solve it.

[Eq up 2sd %] - is a calculated member which I bring to Pivot table to test fast and slow running queries.

When I look into profiler fast query has some events "Query Subcube" and then end query but for slow one I get "Query Subcube" then there is pause with Notification - Flight Recorder Snapshot begin then Server state discover begin/end and same group again and again.

My Instrument Sector Dim has attributes (attributes -> <relation>)

Instrument Desc -> Issuer Desc, Issuer Desc -> Sub Ind cd, Sub Ind cd -> Ind Cd, Ind Cd -> Instrument Sector.

Instrument Desc is Key with Instrument ID as Key field

Query is not only slow it max out cpu 100% too.

Fast Query

SELECT {[Measures].[Gghm Hldng],

[Measures].[Eq dn 50p],

[Measures].[Eq up 2sd %]}

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,

NON EMPTY CROSSJOIN(HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Sector Cd 1].[All]})})), HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Issuer Desc 1].[All]})})))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS

FROM [Position Scenario P and L]

WHERE ([Instrument].[Asset Type Cd].[All], [Investment].[Investment].[Investment Cd].&[228], [Date].[Date].[Year].&[2007].[Quarter 1].[February].[02/21/2007])

Slow Query

SELECT {[Measures].[Gghm Hldng],

[Measures].[Eq dn 50p],

[Measures].[Eq dn 2sd %]}

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,

NON EMPTY CROSSJOIN(CROSSJOIN(HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Sector Cd 1].[All]})})),

HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Issuer Desc 1].[All]})}))),

HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Instrument Desc 1].[All]})})))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS

FROM [Position Scenario P and L]

WHERE ([Instrument].[Asset Type Cd].[All], [Investment].[Investment].[Investment Cd].&[228], [Date].[Date].[Year].&[2007].[Quarter 1].[February].[02/21/2007])

Thanks guys - Ashok

--

Just to add more in my question, when l look into new performance guide there is example of dim like following

Product Key (PK) -> SubCategory -> Category
-> Size -> Size Range
-> Color
-> Description

In my case I am selecting in my query Category, Product Name (Name of key field), and Size Range. This is creating CROSSJOIN(CROSSJOIN 2 of them in MDX for Category and Size Range.

In my cube I have Issuer Desc 1, Instrument Desc 1 in place of Category and Size Range

Hi Ashok,

Could you clarify the following points, to help better understand your scenario?

What's the MDX expression for [Measures].[Eq dn 2sd %] - this could be crucial?|||

Thanks Deepak.

I think the problem was I didn't set "Non-empty behavior" in calculated member.

"Eq dn 2sd %" is a calculated member. I did notice some issues in my Dim design. I am in the middle of final testing I will confirm it has fixed or not today.

Based in my experience the use of "Non-empty behavior" in calculated member should be highlighted in Performance document for OLAP. It worked like magic in query difference but again allow me to confirm that by end of today.

-Ashok

confirmed ! it's "Non-empty behavior" in calculated member

Dim design / MDX help

I have two queries one runs very fast and other very slow. The difference is very little is both. I must be doing some thing wrong is dimension design but not able to solve it.

[Eq up 2sd %] - is a calculated member which I bring to Pivot table to test fast and slow running queries.

When I look into profiler fast query has some events "Query Subcube" and then end query but for slow one I get "Query Subcube" then there is pause with Notification - Flight Recorder Snapshot begin then Server state discover begin/end and same group again and again.

My Instrument Sector Dim has attributes (attributes -> <relation>)

Instrument Desc -> Issuer Desc, Issuer Desc -> Sub Ind cd, Sub Ind cd -> Ind Cd, Ind Cd -> Instrument Sector.

Instrument Desc is Key with Instrument ID as Key field

Query is not only slow it max out cpu 100% too.

Fast Query

SELECT {[Measures].[Gghm Hldng],

[Measures].[Eq dn 50p],

[Measures].[Eq up 2sd %]}

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,

NON EMPTY CROSSJOIN(HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Sector Cd 1].[All]})})), HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Issuer Desc 1].[All]})})))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS

FROM [Position Scenario P and L]

WHERE ([Instrument].[Asset Type Cd].[All], [Investment].[Investment].[Investment Cd].&[228], [Date].[Date].[Year].&[2007].[Quarter 1].[February].[02/21/2007])

Slow Query

SELECT {[Measures].[Gghm Hldng],

[Measures].[Eq dn 50p],

[Measures].[Eq dn 2sd %]}

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,

NON EMPTY CROSSJOIN(CROSSJOIN(HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Sector Cd 1].[All]})})),

HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Issuer Desc 1].[All]})}))),

HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Instrument Sector].[Instrument Desc 1].[All]})})))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS

FROM [Position Scenario P and L]

WHERE ([Instrument].[Asset Type Cd].[All], [Investment].[Investment].[Investment Cd].&[228], [Date].[Date].[Year].&[2007].[Quarter 1].[February].[02/21/2007])

Thanks guys - Ashok

--

Just to add more in my question, when l look into new performance guide there is example of dim like following

Product Key (PK) -> SubCategory -> Category
-> Size -> Size Range
-> Color
-> Description

In my case I am selecting in my query Category, Product Name (Name of key field), and Size Range. This is creating CROSSJOIN(CROSSJOIN 2 of them in MDX for Category and Size Range.

In my cube I have Issuer Desc 1, Instrument Desc 1 in place of Category and Size Range

Hi Ashok,

Could you clarify the following points, to help better understand your scenario?

What's the MDX expression for [Measures].[Eq dn 2sd %] - this could be crucial?|||

Thanks Deepak.

I think the problem was I didn't set "Non-empty behavior" in calculated member.

"Eq dn 2sd %" is a calculated member. I did notice some issues in my Dim design. I am in the middle of final testing I will confirm it has fixed or not today.

Based in my experience the use of "Non-empty behavior" in calculated member should be highlighted in Performance document for OLAP. It worked like magic in query difference but again allow me to confirm that by end of today.

-Ashok

confirmed ! it's "Non-empty behavior" in calculated member

Different calculation based on dimension attribute?

Hello

I would like to do this in pseudo code for a calculated member:

if(dim.value == 1) then measure.val

else -measure.val

Anyone have a suggestion?

For AS2005, you should include the following in the MDX script for your cube (your will have to adjust for naming):

CREATE MEMBER CURRENTCUBE.[Measures].[MyCalculatedMeasure] AS

[Measures].[MyMeasure] * -1,

NON_EMPTY_BEHAVIOR = [Measures].[MyMeasure];

SCOPE ([MyDimension].[MyAttribute].[1], [Measures].[MyCalculatedMeasure];

This = [Measures].[MyMeasure];

END SCOPE;

... and if it's a calculated member defined at run-time.

WITH MEMBER [Measures].[MyCalculatedMeasure] AS

IIF([MyDimension].[MyAttribute].currentmember IS [MyDimension].[MyAttribute].[1], [Measures].[MyMeasure], -1 * [Measures].[MyMeasure]), NON_EMPTY_BEHAVIOR = [Measures].[MyMeasure]

SELECT .....

|||

Thanks for the quick response, I did solve my first problem.. sorta, with SQL in the integration layer. Now I have almost the same problem though. Now I am trying to do this instead:

If([Dim Account].[Dim Account Type] = 1) then 0 else [Measures].[Fact Result AMOUNT]

I modified the query like so, but it only returns 0, never the Fact Result AMOUNT measure.

CREATE MEMBER CURRENTCUBE.[Measures].[Cost] AS

0,

NON_EMPTY_BEHAVIOR = [Measures].[Fact Result AMOUNT];

SCOPE ([Dim Account].[Dim Account Type].[1], [Measures].[Cost]);

This = [Measures].[Fact Result AMOUNT];

END SCOPE;

|||

Please remove the NON_EMPTY_BEHAVIOR = [Measures].[Fact Result AMOUNT] part.

If you don't remove it - you are risking to get wrong results.

|||

It looks like your logic is reversed. If you want to achieve the following

If([Dim Account].[Dim Account Type] = 1) then 0 else [Measures].[Fact Result AMOUNT]

Then the script should be.

CREATE MEMBER CURRENTCUBE.[Measures].[Cost] AS

[Measures].[Fact Result AMOUNT],

NON_EMPTY_BEHAVIOR = [Measures].[Fact Result AMOUNT];

SCOPE ([Dim Account].[Dim Account Type].[1], [Measures].[Cost]);

This = 0;

END SCOPE;

Mosha - does this address your issue about the NON_EMPTY_BEHAVIOR returning incorrect results? If not would you be able to explain where the issue is?

|||

Mosha - does this address your issue about the NON_EMPTY_BEHAVIOR returning incorrect results? If not would you be able to explain where the issue is?

No, it is still wrong in this example, because when Fact Result AMOUNT can be NULL, the Cost won't be NULL (it will be 0). Note that in your first example, it was correct, since MyCalculatedMeasure and MyMeasure are always either together NULLs or together not NULLs (assuming that there are no other calculations in the cube).