- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 12-27-2010 04:19 AM
(4226 views)
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
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
10 REPLIES 10
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Cascading prompts are not currently supported wtih OLAP.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks TSBruce
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Is there any way to provide cascading OLAP prompts? via stored processes or in the information map?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Ultimately I want to have those dynamic cascading prompts in a web based user interface. will that be possible with the "custom" approach?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
"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?
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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