Member based security & aggregations

Performance question:

We are using member based security, which restricts at the lowest level of our hierarchies. When a user connects to AS we are using the CustomData attribute of the connection string, which passes in a user ID which is resolved to a keyset. If we are quering a few levels higher than the lowest level, and an aggregation has been created at the level being queried, does the engine "crawl" the hierarchy to determine if it can use the aggregation (provided the user has access to all lowest level members), or will abandon the aggregation and sum them up since it is already touching the lowest level?

If anyone has any insight, it would be much appreciated.

Thank you in advance,

John Hennesey

Depends on how you set the Visual Totals attribute on the dimension attribute permission. If it is set to False, which is the default, your totals will reflect all child values whether or not the child is available per the security setting. This helps to keep performance high in the cube and in most situations does not cause problems so long as end-users are aware of why their totals do not match the values for the members visible to them.

If you set the property to true, totals will reflect just those members available to the end user.

B.

|||

And to continue on from Bryan, visual totals is known to reduce performance. I don't know if it actually checks if a given user has access to all the children of a given member and then uses the higher level member, or if it will always add up the applicable members of the allowed set. I think it might be intersecting the allowed set and adding up the results, but I don't know for sure, this is just a guess.

Either way you are better to set your security at the highest level that you can. If you want a person to see all the cities in a given state, given them access to the state member rather than to an explicit list of cities.

|||

Bryan / Darren -

Thank you both for your quick responses - certainly not what I was expecting (or hoping for); it is a bummer because our security is set at the lowest level, but I completely understand why it works this way. Oh well.

Thanks

John Hennesey

MDX script to scope on all but the leaf levels

Hi,

I am trying to specify a scope statement on all non-leaves members of all hierarchies of a dimension (time dimension basically) so I would need to say something like this:

scope (MyMeasure, not leaves(TimeByDay));
this = (MyMeasure,timeByDay.currentHierarchy.currentmember.firstChild);
end scope;

Does anybody see a way of doing this without explicitly repeating a scope statement for each implemented hierarchy.level-above-leaf like the one below?

scope (
{[Measures].[RG Queue State],[Measures].[RQ Distinct Queue State]},
[Processed Statistics Period].[YMD].[Month of year],
[Processed Statistic Type].[Processed Statistic].[Processed Statistic Action].&[1.]);

this = ([Processed Statistics Period].[YMD].currentmember.FirstChild);

end scope;

Thanks

I'm not sure of your particular implementation, but have you considered inverting the problem. Something like:

a) Set the aggregation method for your measure to FirstChild
b) If necessary, redefine the aggregation at the leaves level:

Scope (myMeasure, leaves(TimeByDay));
this = custom aggregation;
end scope;|||using leaves in the scope statement makes the performance go waaaay down...|||Hi Zoran,

What you should be able to do is to scope your calculation on the All Member of the granuarity attribute of your dimension. So if the granularity attribute of your TimeByDay dimension is Month, then

SCOPE([MEASURES].[MYMEASURE], [TIMEBYDAY].[MONTH].[ALL]);
THIS={EXISTING([TIMEBYDAY].[MONTH].[MONTH].MEMBERS)}.ITEM(0).ITEM(0);
END SCOPE;

should do the job of returning the first month that exists with every member on every attribute of the dimension, which is what you want to do I think. Does this work for you? Is the performance ok?

Chris

MDX question about Parameters and two different types of hierarchies....

I was looking at creating cascading parameters within a report I am developing in BIDS.

The parameters I am creating are for an organization structure that looks something like this.

Organization Hierarchy

===========

-Division

-Community

The problem is Division and City are part of a parent child hierarchy inside of a separate DIM named Organization.

Community is part of an attribute hiearachy defined in another DIM named Lot.

I want to be able to filter down the Communitys based upon which Division is selected. The code below returns all Communitys. I try to use a CrossJoin but I cant get it to filter the Communitys down correctly.

Code Snippet

WITH MEMBER [Measures].[ParameterCaption] AS '[Lot].[Community Rollup].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue] AS '[Lot].[Community Rollup].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Lot].[Community Rollup].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Lot].[Community Rollup].Levels(1) ON ROWS FROM [Cube]

Since Division and Community are in different dimensions, I assume that you want to select Communities with cube data for the selected Division(s). You could try NonEmpty(), like:

WITH MEMBER [Measures].[ParameterCaption] AS '[Lot].[Community Rollup].CURRENTMEMBER.MEMBER_CAPTION'

MEMBER [Measures].[ParameterValue] AS '[Lot].[Community Rollup].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Lot].[Community Rollup].CURRENTMEMBER.LEVEL.ORDINAL'

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,

NonEmpty([Lot].[Community Rollup].Levels(1), CrossJoin(StrToSet(@.Community, CONSTRAINED), {[Measures].[FilterMeasure]})) ON ROWS

FROM [Cube]

where [Measures].[FilterMeasure] is a cube measure from the measure group which is used to determine whether a Community has data.

|||Thank you, I will give this a try and let you know how it turns out.

MDX Hierarchy help

I have two calendar hierarchies in a 2005 SSAS cube. I have to make some calculations based on the date level, but of course, my calculated measures dont work when the wrong hierarchy is chosen.

I have tried to look at the .hierarchy and hierarchy() function but am unable to really understand how they work and I haven't been terribly successful looking online for info on them.

Has anyone had a similar issue? How did you test for the current hierarchy?

Here's an Adventure Works example, when either the Calendar or Fiscal hierarchy is selected on rows:

>>

With Member [Measures].[DateHierarchy] as

Axis(1).Item(0).Item(0).Hierarchy.Name

select {[Measures].[Order Quantity],

[Measures].[DateHierarchy]} on 0,

Non Empty [Date].[Calendar].[Calendar Year] on 1

from [Adventure Works]

-

Order Quantity DateHierarchy
CY 2001 11,848 Calendar
CY 2002 60,918 Calendar
CY 2003 124,615 Calendar
CY 2004 77,395 Calendar

>>

Another approach is to test whether the current member of each hierarchy is the default member (assuming that only 1 hierarchy has been navigated).