Hi Mark

Since LINKMEMBER works by matching keys, I take it you're sure that all of the members on FlatAcct.[Rep Nbr] (and ABCD in particular) have equivalent members with the same key on Acct? What happens when you run a similar query in MDX Sample, ie something like

WITH MEMBER MEASURES.TEST AS 'MEMBERTOSTR(LINKMEMBER(FlatAcct.CURRENTMEMBER, Acct))
SELECT {MEASURES.TEST} ON 0
FlatAcct.[Rep Nbr].MEMBERS ON
FROM MYCUB

If you see errors in any cells, click on the cell and tell me what the message is. Also, what have you set for the key uniqueness properties on the various dimensions/levels?

Regards

Chri

----- Mark wrote: ----

Hi, I think I need some help with an MDX equation, but I think some background may also help. (SP3 is installed on Analysis Services, FYI

1. This is the MDX
StrToSet(iif(Username = "", "{}",SetToStr(Generate(NonEmptyCrossJoin([FlatAcct].[Rep Nbr].Members, {StrToMember("[Users].[All Users].[" + UserName + "]")},1), {LinkMember([FlatAcct].Currentmember, [Acct])})))

2. I'm using this to do dimension security on the Acct dimension. When I built it in a sample data set it worked just great. When I let lose the entire dataset, it choked. I've rebuilt all the objects, thinking I'd done something different. I even used my sample set and just changed the data source to look at the big data source... but still I get the same error

Dimension Security Syntax
Formula error- cannot find linked dimension member for "ABCD" - in the LinkMember(<object>,<object>) functio
Do you still want to use this expression for dimension security

... So i've tried several things - originally the Rep Level in the Acct dimension had a long name and key field (which was the same key from the flatacct; and the user rights table). Thinking that might be the problem, I changed the Rep level to have only the RepNbr as both name and key
... After I did that I can run this equation in the MDX editor and not get the error, but it still appears in the dimension security screen.
(I had that problem in the past, but when I applied SP3, it worked great... SP3 is INSTALLED on this server)
I have two users in the permissions cube - me and one other person. The other person has two reps and I have just one

So what I think I need help with - how can I deconstruct the MDX to help me identify why the Acct dimension doesn't recognize the ABCD rep - I know it's there, I can see it in the viewer, and I can query the underlying table directly and it's there too.

I would really, really appreciate any help. I've already missed one deadline on demonstrating the security solution for this project and it would be great to be able to explain a solution

Thanks
Mark

Chris - thanks for responding
I tried the MDX you gave me and it returned the ABCD in the first column (in grey) and the fully defined Acct entry, including the entire hierarchy in the second (in white) for example: [Acct].[All Acct].[Level1].[BranchName] for the items which are branch levels and [Acct].[All Acct].[Level1].[BranchName].[RepNbr] for the items which are reps

(I'm trying to apply security where a user may be limited to see only a couple of rep numbers or may be allowed to see an entire branch - so that's why I'm using leaf level.

As for uniqueness - originally, in my sample, I just use the defaults that were created when I used the wizard to build the dimension , so Member Key Unique and Member Name Unique were false ( this was the sample that worked). But thinking that might be a factor in the fully blown Acct dimension, I changed the Member Key Unique to TRUE and the Member Name Unique to TRUE on the BranchName and the RepNbr level. The bottom level, Acct is unique in both. But it didn't seem to have any impact

There are about 163 branches, and 2,236 Reps (and 736,000 Accounts) in the Acct dimension. I don't know if this has any impact.

Thanks
Mark

The max on set to str -I'm not exactly sure about that. I know that I have more than 1,000 individual accounts in the acct dimension for just about any dimension limit. But it is unlikely that I would have more than 1,000 individual reps for any individual. So would I expect the setToStr to generate a list down to the bottom level of the Acct dimension? Or only to the Branch or Rep level.

SAMPLE MDX results:
--(permission table
username rep
abc\mark01 ABCE
abc\test01 XYZE )

results from the sample mdx:
abc\mark01 [Acct].[All Acct].[BranchName].[ABCE]
abc\test01 [Acct].[All Acct].[BranchName].[ABCE]

if I just delete the "+ USERNAME + " from the equation, so I can get it to look at both username (mine is Mark01)
abc\mark01 [Acct].[All Acct].[BranchName].[ABCE], [Acct].[All Acct].[BranchName].[XYZE]
abc\test01 [Acct].[All Acct].[BranchName].[ABCE], [Acct].[All Acct].[BranchName].[XYZE]

so it didn't work exactly as I expected... but I think it does seem to be getting me the right data.

SP3 thing:
Don't assume too quickly that I know what I'm talking about (but thanks for the vote of confidence!).... I think the first time I tried this solution in my environment I didn't have SP3. So I've asked the DBA about a 100 times if SP3 were really installed. Or if there could have been a glitch. I have had her install it twice, but now I will ask her to uninstall and reinstall one more time. The behaviour seemed weird enough to think it might be SP3. Actually, I think you were the one that told me to validate that on my first try and I had done exactly that thing thinking SP3 on SQL was the same.

Thanks again for taking the time - I don't know what I'd do without the help i get from this web site!
Mark

Well, that is strange. The only other thing I can think of is the key uniqueness thing again - you mentioned you changed the properties on some of the levels, but what about the dimensions as a whole? Acct should have unique keys across all levels

Contact me offline on cwebb (gap to avoid getting more spam) @uk.imshealth.com and we can chat about this some more. I'm sure I must be missing something obvious..

Regards

Chri

----- Mark wrote: ----

No - when I used your MDX (see don't assume I know how to do anything...) I get the correct ABCE for mark0
and the correct ZYXE for test01. So that is working

Mark

I would imagine it was the first two setting that did the trick here, and that there was some kind of problem with shadow dimension creation. Interesting that it manifested as an error for LINKMEMBER though - it would be nice to have had a relevant error message..

----- Mark wrote: ----

FYI - for anyone that might have followed this thread... We found (with some additional help from Chris) that adjusting the memory settings (minimum allocated memory to 512, memory conservation thresdhold to 2870, and the readahead buffer to 64 and the process buff size to 1024) that the issue with my MDX error went away.

I can't tell you exactly which memory setting made the difference - I had someone else making the adjustments... but one of them made the difference

Thanks to Chris again for all your help!