RESOLVED Prompt macros used in Cube Designer, to "secure" (filter) a dimension

[rockytopmark]

We are looking to pass a parameter into the Dynamic Cube at run-time so only appropriate/permissioned rows are returned.

This is a rather simple implementation in DMR using Prompt Macros, but when we try to use the same strategy in Cube Designer... we get an "XQE-QTE-2008 there are unresolved prompts" error.  Our prompt macros are set to default, so we assumed that would satisfy the prompt, but that is not the case.

I haven't found much on IBM or anywhere else relating to prompt macros within a Dynamic Cube.  Does anyone know if this is indeed not allowed/unsupported?  Or... even better, experience with successfully using prompt macros in a Dynamic Cube model?

TIA... Mark

[navissar]

Hi,
I'm not sure I understand how a prompt (A user selectable value) helps you with security. Perhaps you mean a macro expression?
Anyway, in a dimensional, caching environment the usage of per-user dynamically created expressions might cost dearly in performance even if it does work. Also, it leaves such questions as visible ancestors unanswered. May I suggest that you read the security section (pp 147-197, especially pp 147-149, 162-163,164-192) of the Dynamic Cubes redbook (http://www.redbooks.ibm.com/redbooks/pdfs/sg248064.pdf)? I think it will help you get the basic concepts of securing dimensional data in a cache-friendly, performance oriented way. Otherwise, if you could elaborate further on your desired security concerns, we may be able to find some sort of a solution.

[rockytopmark]

Reports will be executed using CMS within a Java based portal. The portal will set 2 "parameters" based on the login. Those parameters will be passed to the Cognos reports. 

The parameters will be:
1. level
2. Org

Level can be a State, District or School
Org will correspond to one and only one member of whichever level.

Customer prefers this isn't done in the reports, but in the Dynamic Cube.

[CognosPaul]

I'm actually in the same project here, so I can provide some further insights. The cube will be feeding a series of reports that are accessible to a large audience. Upwards of 100,000 possible users. We've done some extensive series of tests on LDAP with similar numbers of users, and those have always shown abysmal performance. Instead we're going for a somewhat unique solution.

Instead of 100,000 unique users, we're going to have 5. Each corresponding to a level in the org tree. The reports are accessed via CMS through an application, so we can easily control what is being delivered to Cognos. So when a human logs in, he will log into one of those users, and the application will automatically append &p_Org=%HumanOrgID%&p_OrgLevel=%UserLevel% in every REST call.

Consider the following:

#
  '[K12 DQ POC].[Dim Organization].[Organization].['
+prompt('p_Level','token','all')
+']->:[RO].['
+ prompt('p_Org','token','All')
+']'
#

This works perfectly in Report Studio. To handle the ancestors, I'm doing the very ugly union([OrgMember],parent([OrgMember])) (with another 3 levels of nesting). IBM really needs to add the ascendants function.

The client wants this done in the cube itself, so the report developers don't need to use the above macro or any variants.

Since posting this, we've actually learned that prompts are only supported in calculated members and measures, and it doesn't seem to like building MUNs.

[rockytopmark]

Well, I managed to successfully do this... using a Lookup Table Security Filter, but just with the same table the dimension is built from.

Attached is a paper showing the steps taken to make it work.

What is unknown at this point, is what negative effects, if any, come from having these parameters embedded in the Dynamic Cube... will In-Memory Aggregate Cache, Result Set Cache, Data Cache be adversely affected?  I will be investigating this next and will update this thread with my findings.

Mark

[Andrei I]

Thank you Mark for taking your time to provide an extensive description of a possible solution.