topic OLAP cube MDX authorization in SAS Web Report Studio

OLAP cube MDX authorization

Ravera — Tue, 26 Sep 2017 13:29:02 GMT

Hi,

I'd like to apologise in advance for any weird grammer that may occur - this is not my native language.

I am not sure if what I/we want/need is possible but here we go:

I was tasked with remanufacturing some reports which so far have been sent out manually as Excel-Sheets. We intend to use a WRS-Report based on an OLAP cube instead in their place. However, the data inside is fairly sensitive which means I need to filter by User-ID in regards to who gets to see what report. This is going to happen via User-groups.

Now my problem is following:

There is a 3-tier hierarchy regarding the divisions.

Tier1 could be "A" and "B".

Tier2 could be "AA", "BB" and "CC"

Tier3 could be "AAA", "BBB", "CCC", "DDD", etc all the way to "KKK".

However, the hierarchy doesnt look neat like in Ex.1 but more like Ex.2:

(Ex.1) (Ex.2)

So far, I've tried (and failed) to ONLY give access to the (DDD)-report without giving access to the (A)-report and (A-empty) report as well.

My best-working try so far has been achieved with the following MDX expression in the SAS OLAP Cube Studio, however I'd like (and need) to prevent the User to see anything in the "empty" division.

{
[Abteilung].[Abteilung].[Alle Abteilung],
[Abteilung].[Abteilung].[Alle Abteilung].[A],
[Abteilung].[Abteilung].[Alle Abteilung].[A].[ ],
[Abteilung].[Abteilung].[Alle Abteilung].[A].[ ].[DDD]
}

When leaving out the third line inside the {}, all I see is the A-report but with no way to as well look at the DDD-report.

There are also cases when the recipients of the reports may look at AA and CCC (but not A or AAA/BBB) or B and III (not BB or HHH).

So: Is it at all possible to do what I need and if yes, how?

I already tried to "disable" the middle tier view with a <!--condition, however I might've misunderstood what use those have/bring as my result didn't change.

Re: OLAP cube MDX authorization

FredrikE — Wed, 27 Sep 2017 08:42:57 GMT

Hi!

I dont know if this helps you, it was a while ago since I worked with cubes and MDX....:)

Here is a sample code on how we solved access to different levels of a dimension, I remeber that the  was important to get it to work :). We defined groups in metadata tha correspond to the data in the cube and depending on which group a person belonged to that information was retrieved from the cube :

generate(
Filter([Customer].[Customer Unit ID].[Customer_id].members,IN ([Customer].CurrentMember.name,"SUB::SAS.IdentityGroups"))
,
ascendants([Customer].[Customer Unit ID].currentmember)
)

generate(
Filter({[Customer].[Customer Unit Name].[Customer_id].members},IN ([Customer].[Customer Unit Name].CurrentMember.name,"SUB::SAS.IdentityGroups"))
,
ascendants([Customer].[Customer Unit Name].currentmember)
)

generate(
Filter([Customer].[Region ID].members,IN ([Customer].[Region ID].CurrentMember.name,"RECA"))
,
ascendants([Customer].[Region ID].currentmember)
)

generate(
Filter({[Customer].[Region Name].members},IN ([Customer].[Region Name].CurrentMember.name,"N Europe & Cent Asia"))
,
ascendants([Customer].[Region Name].currentmember)
)

generate(
Filter([Customer].[Customer Unit ID].members,IN ([Customer].[Customer Unit ID].CurrentMember.name,"100017"))
,
ascendants([Customer].[Customer Unit ID].currentmember)
)

generate(
Filter({[Customer].[Customer Unit Name].members},IN ([Customer].[Customer Unit Name].CurrentMember.name,"Verizon"))
,
ascendants([Customer].[Customer Unit Name].currentmember)
)

Re: OLAP cube MDX authorization

Ravera — Mon, 02 Oct 2017 10:00:08 GMT

This was indeed what we were looking for, thank you!