nearly two hours have passed and no result. I stopped the program in EG 4.2. I got the port number wrong 😞 - no error message to indicate there was a connection problem. ... View more

"Ultimately I want to have those dynamic cascading prompts in a web based user interface. will that be possible with the "custom" approach? " Yes, having a STP as a source for a Web Report is possible. Since I managed to generate some MDX my next question comes up, the security concept for such an "custom approach". I can implement Member-Level Securty on the OLAP cube but that will not restrict the selection users can make in the prompts (since they are based on a relational data source). Does that mean I have to use two seperat security concepts. The first is the OLAP member-level security and the second some SQL Code on the Oracle database or in an STP that uses the SASUID to derive a valid set of selectable prompt choices? ... View more

Hi Paul, Many thanks for your detailed post again. Well spotted, my level naming was wrong - I just tried things out because it was not working how I wanted it. I used the following now (including the part you suggested but extended with the CONDITION statement) {[DIM_PERIOD].defaultmember} {[DIM_KPI].defaultmember} {[DIM_SALES].defaultmember} Ancestors(Head(Filter([DIM_SALES].AllMembers,[DIM_SALES].CurrentMember.Level.Name = 'SALESPERSON_ID' and [DIM_SALES].CurrentMember.Name = '040347788')).Item(0)) Without the CONDITIONS for the other dimension I get the usual error: Formula error - Access to member is not allowed I dont see how it can work for you without those. When I use this MDX filter in WRS (via an Information Map) and I only have the Period and KPI dimension selected as data to display it works fine but as soon as I add the DIM_SALES dimension the page refreshes and I can see a message tellingm me that for this table no values have been returned. I have not spotted a slicer item on my OLAP View in EG 4.2 the only way to create a slice is doing a right-click on the cube and selecting "Create Slice". The following window does not allow a drill down by clicking on a table row. Your guess is right, at some point I am also going to want to have filters for management level users. You said that the EG4.2 generated MDX queries might be of use later. In EG4.2 I have opened the cube, added SALESPERSON_ID to the rows out of the memebr/level selection panel between the left hand process flow view and the right hand OLAP Query result. This resulted in all the bottom level SALESPERSON_ID's being displayed (around 3500). When I then click on one and select add SALESPERSON_ID to filter, then specify that the member caption should ="040347788" and confirm it then takes a long time for EG4.2 to process this step. I leave it running during the night and see tomorrow in hope that this generated MDX query will be usefull. Here is th result, its seems the "Generate" was taking the longest, generating a set of all bottom level members: Filter(Generate({ [DIM_SALES].[SALESPERSON_ID].AllMembers }, Ancestors([DIM_SALES].[DIM_SALES].CurrentMember, [DIM_SALES].[SALESPERSON_ID])), kupcase([DIM_SALES].[DIM_SALES].CurrentMember.Properties("CAPTION")) = kupcase("040347788"))).Count > 0))) Finally, Paul, I tried it with your query again and I must have made a mistake last time because this time it wors perfectly: Ancestors(Head(Filter([DIM_SALES].AllMembers,[DIM_SALES].CurrentMember.Level.Name = 'SALESPERSON_ID' and [DIM_SALES].CurrentMember.Name = 'SUB::SAS.UserId')).Item(0)) Thanks a lot! Regards, Bob Message was edited by: metalray ... View more

The following MDX eleminated the errors but does not result in anything being shown in Web Report Studio. >{[DIM_PERIOD].defaultmember} >{[DIM_KPI].defaultmember} >{[DIM_SALES].defaultmember} >Filter( [DIM_SALES].AllMembers, [DIM_SALES].CurrentMember.Name >= '040347788' and [DIM_SALES].CurrentMember.Level.Name = '040347788' ) Removing [DIM_SALES].defaultmember} leads again to Formula error - Access to member is not allowed - in the "NONEMPTYCROSSJOIN" function ... View more

Hello Paul, You are right, the MDX was altered by the forum engine while posting it. Yes, SalesPerson_ID (040347788) is the user ID and before going into identity drive properties I wanted to see if I can set some kind of filter on a LEVEL basis in the first place. We use SAS OLAP Server on a Windows platform. Your assumption is correct, userid / SalesPerson_ID level has a number of parent levels above it and its a ragged hierarchy as well. Specifying a fixes member name in the MDX will be of no use to me. The userid / SalesPerson_ID appears only onces in a hierarchy and is the bottom level at this tage but there might be a point later in time when this sales person will have a LEVEL below it like sales_contracts. If I add a filter using the EG4.2 add filter function that says only select SALESPERSON_ID where it matches exactly 040347788 then EG4.2 generates a lot of MDX: -----------------MDX CODE------------------- >SELECT > CrossJoin({ [DIM_PERIOD].[All DIM_PERIOD].Children }, { [MEASURES]. > [FACTSUM] }) ON COLUMNS, > Hierarchize > (Filter({ [DIM_SALES].[All].AllMembers }, ([DIM_SALES]. > [DIM_SALES].CurrentMember.Level.Ordinal < [DIM_SALES]. > [SALESPERSON_ID].Ordinal OR Intersect(Ancestors([DIM_SALES]. > [DIM_SALES].CurrentMember, [DIM_SALES].[SALESPERSON_ID]), > Filter(Generate({ [DIM_SALES].[All].AllMembers }, Ancestors([DIM_SALES]. > [DIM_SALES].CurrentMember, [DIM_SALES].[SALESPERSON_ID])), kupcase > ([DIM_SALES].[DIM_SALES].CurrentMember.Properties("CAPTION")) = > kupcase("040347788"))).Count > 0))) ON ROWS > FROM > [DEV_MLS_CUBE_V2] -------------------------------------------- There is not a single WHERE clause generated (I actually never managed that the EG4.2 MDX contains a WHERE clause). What kind of EG do you have that does that for you? Anyway, I have done it manually and the suggested WHERE clause worked as a MDX expression in the management console for the dimension DIM_SALES Filter( [DIM_SALES].AllMembers, [DIM_Vertrieb].CurrentMember.Name = '040347788' and [DIM_SALES].CurrentMember.Level.Name = '040347788' ) Then I added the cube to a info map without applying any filters and opened that info map in web report studio to see if it actually works. I logged in as user 040347788 and it worked. unfortunately it seems to be too restrictive. Do I need to specify that all other Dimensions and members (such as the period) are allowed to be seen by the user? WRS Error: Formula error - Access to member is not allowed - in the "NONEMPTYCROSSJOIN" function or Formula error - Access to member is not allowed (depending which dimension I include in the report) No Query example given and the user does have access to all other dimension (management console) Many thanks for your help. Its a real contribution. What you have written should get into the offical SAS documentation because I think it is an usufull way to write a filter expression rather than the simple ones one finds in the standard documentation provided. ... View more

Hi Paul, I agree on your permission table analysis. Regarding the Member-Level Permission I am still not where I want to be: If I drill down to the Salesperson_ID, using a filter on the sales person ID, the MDX Query generated looks like this: SELECT CrossJoin({ [DIM_PERIOD].[All DIM_PERIOD].Children }, { [MEASURES].[FACTSUM] }) ON COLUMNS, Hierarchize(Filter({ [DIM_Sales].[All DIM_Sales].[HeadOffice].[Specialoffice].[212302].[A3213502].[3213S202].[ ].Children }, ([DIM_Sales].[DIM_Sales].CurrentMember.Level.Ordinal < [DIM_Sales].[SalesPerson_ID].Ordinal OR Intersect(Ancestors([DIM_Sales].[DIM_Sales].CurrentMember, [DIM_Sales].[SalesPerson_ID]), Filter(Generate({ [DIM_Sales].[All DIM_Sales].[HeadOffice].[Specialoffice].[212302].[A3213502].[3213S202].[ ].Children }, Ancestors([DIM_Sales].[DIM_Sales].CurrentMember, [DIM_Sales].[SalesPerson_ID])), kindex(kupcase([DIM_Sales].[DIM_Sales].CurrentMember.Properties("CAPTION")), kupcase("040347788")) <> 0)).Count > 0))) ON ROWS FROM [DEV_MLS_CUBE_V2] If I dont use a filter and do a right click "Isolate 040347788", then the query looks like this: SELECT CrossJoin({ [DIM_PERIOD].[All DIM_PERIOD].Children }, { [MEASURES].[FACTSUM] }) ON COLUMNS, { [DIM_Sales.[All DIM_Sales].[HeadOffice].[Specialoffice].[212302].[A3213502].[3213S202].[ ].[040347788] } ON ROWS FROM [DEV_MLS_CUBE_V2] In both cases its a valid query but no WHERE clause I can use to specify an Member-Level Permission MDX expression. --error , invalid member name at SALESPERSON_ID SELECT CrossJoin({ [DIM_PERIOD].[All DIM_PERIOD].Children }, { [MEASURES].[FACTSUM] }) ON COLUMNS, { [DIM_SALES].[All DIM_SALES].Children } ON ROWS FROM [DEV_MLS_CUBE_V2] WHERE [DIM_SALES].[SALESTEAM].[SALESPERSON_ID].[All Members].['johnsmith'] --error , invalid dimension name at SALESPERSON_ID SELECT CrossJoin({ [DIM_PERIOD].[All DIM_PERIOD].Children }, { [MEASURES].[FACTSUM] }) ON COLUMNS, { [DIM_SALES].[All DIM_SALES].Children } ON ROWS FROM [DEV_MLS_CUBE_V2] WHERE [SALESPERSON_ID].[All Members].['johnsmith'] Thanks for your help, Bob ... View more

Hello Paul, I used the example you described but this did not work well: {[SalesPersonID].[All Members].[SUB::SAS.Userid]} I got the syntax error but additionally the web report could not display anything in the cube (so I think the invalid syntax broke it). n error occurred in processing the report element. Please refresh the page to try again. Contact your administrator if this problem persists. [a12962322664760.05642803333271551_0_0] Error Rendering the Report Unable to access the OLAP server for cube DEV_MLS_CUBE_V2. Returning to fix my syntax and using EG 4.2 as a inspiration I was unable to make out how to apply a filter to a bottom level or any level that is not the top one (whole dimension). WHERE ([SALESPERSON_ID].[All Members]) WHERE ([DIM_SALES].[SALESPERSON_ID].[All Members]) Both cause the error: "Invalid Dimension Name was encountered in the MDX statement" Back to permission tables: I tried what you did (also using D06001) but failed creating a permission table with the error 76: LINE and COLUMN cannot be determined. Assuming that my SAS/ACCESS Library has the permission to create an Oracle table in a Oracle Schema for developers (which is already very unlikely and I dont even know how to grant such a right to create tables in other schemas) I could still not imagine how this permission table would be represented on the SAS side. Would it be a SAS VIEW that SAS created by itself to show me in SAS the Oracle talbe it created seconds before? Having an empty permission table seems like a lot of work to fill manually. I dont see that it has a benefit over the approach via member level permission in that respect. I see howerver, the high potential of reusability. is that correct? Thanks for your reply and your brilliant blog! What you wrote should go in the SAS documentation. Bob ... View more

Hi Paul, Thanks for that. I actually did not go any further after I got the "invalid syntax" error. When I try to add a permission table with a library that uses the SAS ACCESS engine and press Next then nothing seems to happen. Do permisson tables work with a libraray that uses the SAS Access (Oracle) engine? Creating a new library did not help either. I use sas olap studio 4.2 M2. - I think there is a hotfix for that Why do you think permission tables make it more flexible? I am worried that the format of the table and providing the data in that format will be a big job. Thanks, Bob Message was edited by: metalray ... View more

hi Vince, thanks for that. I will keep it as an option. before I move to it I want to see if the MDX idea work but I just noticed that clicking on tools->manage permission tables and providing the information results in OLAP Cube Studio not doing anything when clicking on "next". back to the query, I can not restrict the query by any selection of the other dimensiosn apart from the sales channel so I hoped that someting like: [SalesPersonID].[All Members].where the salesid =&SAS.Userid; would be sufficient but that just gives me an error. I found this great tutorial but it is not LEVEL specific rather member specific and it does not show how the final query with the member definition and identity driven property looks, it almost seems the author had not clue how the final query would look either 🙂 http://support.sas.com/documentation/cdl/en/olapug/59574/HTML/default/viewer.htm#a003212399.htm ... View more

Hello Paul, I read on the website provided that I can implement it with SAS OLAP Server using MDX expressions/query. the link to the document "How to Assign an OLAP Permission Condition" shows me where to click in the management console as well but I dont know how to write the MDX query that effects all bottom level members and only gets those where the salesid = =&SAS.Userid; SalesPersonID is the bottom level member. [SalesPersonID].[All Members].where the salesid = =&SAS.Userid; Do I have to write an MDX query with a SELECT clause or is an MDX "expression" sufficient? I think an expression is without a select. ... View more

Hi Paul, that sounds very promising! I give it ago. Thanks a lot. Cheers, Bob ... View more

Hi Vince, yes, its imported from the LDAP. so they can login but how do I do member level security for 3000+users on 3000+ OLAP dimension members. maintaining that manually will take forever so I have this plan: 1. dont show them anythink in the selection prompts that they are not allowed to select (the selection is retrieved with a sql pass through in a stored process that has somehow taken the user rolee or ID as an input) ... View more