Hello all,

I'm having some issues with a new cube we've designed. Let me give you
a little background:

1. We had an old cube working perfectly and our users used Excel's
pivot table to manipulate the data. 2. This cube had roles set up
(database and cube) both enforced on the client side, to restrict the
dimensions a particular user can see. When I go to edit a role's
access to a particular dimension, and I select which level is visible
in a dimension, it says: "The following members and their descendants
on levels between [TOP LEVEL] and [BOTTOM LEVEL] will be visible:
[MEMBERS] New members will not be visible." (where the things in
brackets are what I've selected)

In this set up, the cube works just as expected.

However, the problem is that we've had to design a new cube with a new
fact table, etc. We've done so, and the users are interacting with it
again via Excel's Pivot Table. This cube sits on the same server as
the old cube described above and has the exact same roles defined for
it. The issue is twofold: 1. When I edit a cube role for a user, and
select the same things that I've selected for Top Level, Bottom Level,
and Members the text at the bottom of the "Custom Dimension Security"
window now reads: "The following members and their descendants on and
below level [Top Level] will be visible: [Members] New members will
not be visible."

Aside from this text being different, the blue area in the triangle
diagram is now larger than the old cube. This may not really be an
issue, but I'm trying to be thorough in my description.

Now, the real problem is that when my users load up excel to view the
new cube via a pivot table, they can't access all the data. The roles
are being applied, since a user can only pick the levels of a
dimension I've defined for them, but they can't view data if the "All"
checkbox is checked for one of the restricted dimensions. For example,
if a dimension called "Names" is restricted for you and it had "Jack",
"Jill", and "Edward" in it, if you filtered on "Jack" and "Jill" and
"Edward" by checking each of their boxes but making sure that the "All
Names" box wasn't checked, then it all works. If you instead click on
"All Names" then the user sees #VALUE for any data point he tried to
view. If you drag a restricted dimension into the "Row Fields" area of
the pivot table, and it was restricted to only allow you to see one
level of a dimension, you cannot unclick the "All" box so again, you
don't see values for data points. This is a huge problem and I've no
idea why it is happening.

So, if anyone can please point me in the right direction I would
greatly greatly greatly appreciate it.

Thanks,
Mustafa

"Mustafa" <mustafashabib (AT) hotmail (DOT) com> wrote


Mustafa - let me ask you 2 questions:

1. Does your new cube have Distinct Count measure ?
2. Does your role use visual totals ?

If the answer to both questions is "Yes", then it is by design, and if you
want to change it, you will need to build a new cube with only distinct
count measure in it and turn visual totals off in the role for that cube.

HTH,
Mosha.

--
==================================================
Mosha Pasumansky - www.mosha.com/msolap
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================

"Mustafa Shabib" <mustafashabib (AT) hotmail (DOT) com> wrote

No, visual totals are controlled per dimension/per cube. Therefore I
suggested to put that measure into different cube.

In the cube role editor, in the dimension security dialog last tab allows
you to control visual totals for the cube.

--
==================================================
Mosha Pasumansky - www.mosha.com/msolap
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================

Hi again Mosha,

I can't turn visual totals off, since they are on by default and the
option to change that is disabled. It says that these are always on for
measures with unary operators, custom rollups forumlas or custom
members.

If I remove the measures that use DISTINCT Count (and any calculated
measures that use this measure) from my cube, will my permissions work
properly?

I need a fix as soon as possible. These measures, though important, can
be added later. Which brings me to my next question: if I create a
second cube with the distinct count measure and related caculated
measures in it, should I use the same dimensions from the first cube
(ie. share them), then can I create a virtual cube which uses all these
measures and in which the roles I've set up will work properly? I've
tried doing this but I must've done something wrong since the distinct
count measure and the calc measures that use it appear blank in my new
virtual cube.

Thanks again.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!