Dynamic Cascading Prompts and OLAP

[metalray]

Hello,
I have read a great tutorial PDF by Kevin Davidson and Minh Duong on using dynamic and cascading prompts in EG 4.2. I now wonder if cascading prompts also work with having the prompt type "OLAP Member". So far, it does not look like it. When I have a prompt that has an OLAP cube as a source it seems only selectable hierarchies are displayed. Is there a way to use cascading prompts that display various levels of the hierarchie instead?

Thanks,
metalray

[TSBruce]

Cascading prompts are not currently supported wtih OLAP.

[metalray]

Thanks TSBruce

[metalray]

Is there any way to provide cascading OLAP prompts? via stored processes or in the information map?

[AngelaHall]

They are currently not supported 'out-of-the-box' as mentioned in the thread.

You can develop a "custom" approach by using relational tables as the drivers for dynamic prompts, and a stored process that takes these values and builds a custom OLAP query based on the user's selection.

The relational table(s) can be scheduled to refresh during the cube refresh process so they dont get out-of-sync.

~ Angela
http://sas-bi.blogspot.com

[metalray]

Thanks Angela. Thats what I was looking for. Your suggestions are a real contribution to this board.

But how do I dynamically generate a MDX query based on non OLAP selections from relational based prompts?You mentioned a stored process. I will try that and let you know. My first idea to that solution is to use SAS Access and read the data from a SAS library with views (Access Engine) so I dont have to do a refresh.

[metalray]

Ultimately I want to have those dynamic cascading prompts in a web based user interface. will that be possible with the "custom" approach?

[metalray]

"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?

[AngelaHall]

You are correct. Two different security approaches would be required.
Member Level Security on the OLAP Cube (http://support.sas.com/documentation/cdl/en/olapug/59574/HTML/default/viewer.htm#a003212398.htm)
and Identity Level (or row-level) Security on the relational table used for the prompting.

Might I suggest you look into the Information Map mechanism for row level security. You could utilize this as your source for the dynamic prompt.
http://support.sas.com/kb/31/563.html - to set up the imap
http://support.sas.com/documentation/cdl/en/engimap/61078/HTML/default/a003050257.htm - to connect to the imap from your STP

Or use the imap structure as an example to build in the security for yourself in the STP.

~ Angela

[nash_sas]

hi Angela,

As a followup to this, I am looking for similar functionality with having dynamic prompts and the cube on the same page. I understand that for dynamic prompts with data driven from the cube, we can go with a custom approach using stored process but I am wondering if we can have the cube on the same page ? Thanks

[JustZ]

I was also looking how to build a cascading prompt with OLAP. When I couldn't find the answer I thought of a very simple solution that does the trick for me. I just created a hierarchy of the two dimensions that I wanted to cascade, after that you can create a prompt on this hierarchy without having to customize anything.

Maybe this will work for you too.

Regards,

Just