RE: Create calculated member and dimension security fails

#1

Hi Andreas,

I've followed your steps, and to be honest the surprise for me is that you
say that your first SELECT query works - it shouldn't, and it doesn't on my
machine (which has all the latest service packs installed). The reason I say
this is that your CREATE MEMBER statement is creating a calculated member at
the All level of the Store dimension - and you have specified in your role
that Store Country should be the top level visible. Therefore you shouldn't
be able to see your calculated member at all.

What I think you want to do is create a new calculated member on one of the
visible levels in the Store dimension, and to do this you need to specify a
parent member for it. Here's an example that will work in your Foodmart
scenario:
CREATE MEMBER [Sales].[Store].[Mexico].[MyStore] AS '42'

HTH,

Chris

"Andreas Doyé" wrote:

Quote:

Hi all!

Recently we found the following problem in our productive system. We use
dimension security including top level restrictions for some of the roles.
Our users have to create calculated session members.

You can reproduce the error in the Foodmart 2000 database. Therefore create
a role with the following restrictions:

Cubes: Sales
Restricted Dimensions: Store
Top Level: [Store].[Store Country]
Bottom Level:[Store].[Store Name]
Allowed Members:{[Store].[Store Country].&[Mexico]}

When you connect as a role user execute the following statement using MDX
Sample application:

CREATE MEMBER [Sales].[STORE].[MyStore] AS '42'

This statement will be executed without any errors. To check whether the
member was actually created use the following statement:

SELECT
AddCalculatedMembers({[Store].[MyStore]}) ON Rows,
{[Measures].Defaultmember} ON Columns
FROM [Sales]

As expected the result contains the member [MyStore]. The problem occurs
when you try to use the calculated member on the axes or in the WHERE clause:

SELECT
{[Store].[MyStore]} ON Rows,
{[Measures].Defaultmember} ON Columns
FROM [Sales]

The sample application raises the following error: <<Formula error - cannot
bind: unknown member: "[Store].[MyStore]">>. Of course that error occurs when
using other OLAP clients as well.

Microsoft Support Germany told me that this behaviour is “by design” and we
have obviously no chance to get a fix for that problem.

In the meantime we tried several workarounds but we still have no idea how
to solve that problem. Any ideas are welcome.

Thanks in advance,
Andreas

#2

OK, I see what the problem is now. You're right, if you only have one visible
level there isn't a parent member to hang a calculated member from! Also it
does seem like this behaviour is inconsistent over the two queries.

However, the fact that the query below works shows that there is a
workaround you can use to just return the member you want:
SELECT TAIL(AddCalculatedMembers({[Store].Members}),1) ON Rows,
{[Measures].Defaultmember} ON Columns FROM [Sales]

The general idea here is that you take the set returned by
AddCalculatedMembers({[Store].Members}) and then use one of the set functions
to pick out the calculated member you want from it. A bit of a pain, yes, but
it seems to work...

HTH,

Chris

"Andreas Doyé" wrote:

Quote:

Hi Chris,

thank you very much for your reply. You're right the first SELECT doesn't
work. It was just a "copy and paste" error. I'm very sorry for that. But the
following statement works on my machine:

SELECT
AddCalculatedMembers({[Store].Members}) ON Rows,
{[Measures].Defaultmember} ON Columns
FROM [Sales]

I'm using AS 2k Developer edition (8.0.760.0) on my client that's SP3,
right? But this statement is not so interesting for me. It's more important
for me that the 3'rd statement doesn't work and I think you would agree to.

Your suggestion to create the calculated on a visible level is interesting.
But it doesn't help much when the only allowed level is the bottom level of
the dimension. Then I would have to put the calculated member on a level
below the bottom level, right? That doesnt work: "Formula error - formula
level is not valid (too deep)". Unfortunately in this case I don't have a
"suitable" parent for the calculated member.

Thank you very much for your ideas, again.

Regards,
Andreas

"Chris Webb" wrote:

Hi Andreas,

I've followed your steps, and to be honest the surprise for me is that you
say that your first SELECT query works - it shouldn't, and it doesn't on my
machine (which has all the latest service packs installed). The reason I say
this is that your CREATE MEMBER statement is creating a calculated member at
the All level of the Store dimension - and you have specified in your role
that Store Country should be the top level visible. Therefore you shouldn't
be able to see your calculated member at all.

What I think you want to do is create a new calculated member on one of the
visible levels in the Store dimension, and to do this you need to specify a
parent member for it. Here's an example that will work in your Foodmart
scenario:
CREATE MEMBER [Sales].[Store].[Mexico].[MyStore] AS '42'

HTH,

Chris

"Andreas Doyé" wrote:

Hi all!

Recently we found the following problem in our productive system. We use
dimension security including top level restrictions for some of the roles.
Our users have to create calculated session members.

You can reproduce the error in the Foodmart 2000 database. Therefore create
a role with the following restrictions:

Cubes: Sales
Restricted Dimensions: Store
Top Level: [Store].[Store Country]
Bottom Level:[Store].[Store Name]
Allowed Members:{[Store].[Store Country].&[Mexico]}

When you connect as a role user execute the following statement using MDX
Sample application:

CREATE MEMBER [Sales].[STORE].[MyStore] AS '42'

This statement will be executed without any errors. To check whether the
member was actually created use the following statement:

SELECT
AddCalculatedMembers({[Store].[MyStore]}) ON Rows,
{[Measures].Defaultmember} ON Columns
FROM [Sales]

As expected the result contains the member [MyStore]. The problem occurs
when you try to use the calculated member on the axes or in the WHERE clause:

SELECT
{[Store].[MyStore]} ON Rows,
{[Measures].Defaultmember} ON Columns
FROM [Sales]

The sample application raises the following error: <<Formula error - cannot
bind: unknown member: "[Store].[MyStore]">>. Of course that error occurs when
using other OLAP clients as well.

Microsoft Support Germany told me that this behaviour is “by design” and we
have obviously no chance to get a fix for that problem.

In the meantime we tried several workarounds but we still have no idea how
to solve that problem. Any ideas are welcome.

Thanks in advance,
Andreas

#3

Well, to get a member from a set is fairly straightforward - you need to do
something like:
TAIL(AddCalculatedMembers({[Store].Members}),1).ITEM(0).ITEM(0)
if you can supply a specific example of the MDX used in the FILTER
statements it might be possible to get them to work.

But I'm beginning to think that I might be better off trying to understand
what you're trying to do overall with your cube. There might be a completely
different approach that you could take that doesn't run into these
restrictions. What are these calculated members for and why are you filtering
by them?

Regards,

Chris

"Andreas Doyé" wrote:

Quote:

Hi Chris,

thanks for your idea. It's a usefull workaround for using the calculated
member on the axes. But I have two problems with that, which you couldn't now.

First we are using commercial OLAP clients and thatswhy we have almost no
influence on the MDX the clients generate :-((
The second problem is the way these clients using the calculated members.
The members are used for filtering purposes. It looks like that:

filter ({something},(MyCalculatedMember,some_other_elemen ts,...) <> 0)

I can manipulate the string "MyCalculatedMember", so I could insert a magic
MDX expression that that generates the member. But I don't know how to covert
a set with one element to a "member" that can be used in a tuple or numeric
expression. Is something like that possible or am I on the wrong way?

Andreas

"Chris Webb" wrote:

OK, I see what the problem is now. You're right, if you only have one visible
level there isn't a parent member to hang a calculated member from! Also it
does seem like this behaviour is inconsistent over the two queries.

However, the fact that the query below works shows that there is a
workaround you can use to just return the member you want:
SELECT TAIL(AddCalculatedMembers({[Store].Members}),1) ON Rows,
{[Measures].Defaultmember} ON Columns FROM [Sales]

The general idea here is that you take the set returned by
AddCalculatedMembers({[Store].Members}) and then use one of the set functions
to pick out the calculated member you want from it. A bit of a pain, yes, but
it seems to work...

HTH,

Chris

"Andreas Doyé" wrote:

Hi Chris,

thank you very much for your reply. You're right the first SELECT doesn't
work. It was just a "copy and paste" error. I'm very sorry for that. But the
following statement works on my machine:

SELECT
AddCalculatedMembers({[Store].Members}) ON Rows,
{[Measures].Defaultmember} ON Columns
FROM [Sales]

I'm using AS 2k Developer edition (8.0.760.0) on my client that's SP3,
right? But this statement is not so interesting for me. It's more important
for me that the 3'rd statement doesn't work and I think you would agree to.

Your suggestion to create the calculated on a visible level is interesting.
But it doesn't help much when the only allowed level is the bottom level of
the dimension. Then I would have to put the calculated member on a level
below the bottom level, right? That doesnt work: "Formula error - formula
level is not valid (too deep)". Unfortunately in this case I don't have a
"suitable" parent for the calculated member.

Thank you very much for your ideas, again.

Regards,
Andreas

"Chris Webb" wrote:

Hi Andreas,

I've followed your steps, and to be honest the surprise for me is that you
say that your first SELECT query works - it shouldn't, and it doesn't on my
machine (which has all the latest service packs installed). The reason I say
this is that your CREATE MEMBER statement is creating a calculated member at
the All level of the Store dimension - and you have specified in your role
that Store Country should be the top level visible. Therefore you shouldn't
be able to see your calculated member at all.

What I think you want to do is create a new calculated member on one of the
visible levels in the Store dimension, and to do this you need to specify a
parent member for it. Here's an example that will work in your Foodmart
scenario:
CREATE MEMBER [Sales].[Store].[Mexico].[MyStore] AS '42'

HTH,

Chris

"Andreas Doyé" wrote:

Hi all!

Recently we found the following problem in our productive system. We use
dimension security including top level restrictions for some of the roles.
Our users have to create calculated session members.

You can reproduce the error in the Foodmart 2000 database. Therefore create
a role with the following restrictions:

Cubes: Sales
Restricted Dimensions: Store
Top Level: [Store].[Store Country]
Bottom Level:[Store].[Store Name]
Allowed Members:{[Store].[Store Country].&[Mexico]}

When you connect as a role user execute the following statement using MDX
Sample application:

CREATE MEMBER [Sales].[STORE].[MyStore] AS '42'

This statement will be executed without any errors. To check whether the
member was actually created use the following statement:

SELECT
AddCalculatedMembers({[Store].[MyStore]}) ON Rows,
{[Measures].Defaultmember} ON Columns
FROM [Sales]

As expected the result contains the member [MyStore]. The problem occurs
when you try to use the calculated member on the axes or in the WHERE clause:

SELECT
{[Store].[MyStore]} ON Rows,
{[Measures].Defaultmember} ON Columns
FROM [Sales]

The sample application raises the following error: <<Formula error - cannot
bind: unknown member: "[Store].[MyStore]">>. Of course that error occurs when
using other OLAP clients as well.

Microsoft Support Germany told me that this behaviour is “by design” and we
have obviously no chance to get a fix for that problem.

In the meantime we tried several workarounds but we still have no idea how
to solve that problem. Any ideas are welcome.

Thanks in advance,
Andreas

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

RE: Create calculated member and dimension security fails

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

RE: Create calculated member and dimension security fails - 12-01-2004 , 10:15 AM

RE: Create calculated member and dimension security fails - 12-01-2004 , 11:19 AM

RE: Create calculated member and dimension security fails - 12-02-2004 , 09:43 AM