Today i thought of sharing the experience of having “Hierarchy prompt” within the Intelligent Cube based datasets/reports/grids.
Before i deep dive, let’s discuss some basics :
What is intelligent cube ?
Intelligent Cubes are multi-dimensional cubes (sets of data) that allow you to use OLAP Services features on reports, as well as share sets of data among multiple reports
It is basically in-memory storage of WH/EDW(Warehouse/Enterprise Data Warehouse) data within MicroStrategy layer/server itself.
What is Prompts in MicroStrategy ?
Prompts are basically “allowing user to dynamically input user responses during the execution of report/document within MicroStrategy” (e.g.) Select Year during the execution of report to view “Revenue by Year”
Here is detailed definition from MicroStrategy “Prompts allow you to determine how the report returns data from the data warehouse. Prompts save you time. Instead of building several different reports that each answer a different, single question, a set of questions can be asked just before a prompted report is executed. There are many types of prompts, and each type allows a different type of question to be asked. These prompt types are discussed individually later in this section.”
If you’re an Tableau user, then you can treat this “Prompt” object as “Parameters” in Tableau.
Wacky Wednesday – What is the limitation we are talking here today ? Can we use all types of prompts within Datasets/reports created on top of Intelligent Cube ?
Technically it allows only “Elements Prompt” & “Expression/Value Prompt” with “MicroStrategy Developer” interface
So how are we going to address having “Hierarchy Prompt” on top of Intelligent Cube ?
Follow below steps to achieve that :
- Create Elements Prompt and add them into Report coming from Intelligent Cube
- Now, create an hierarchy prompt but before saving , save/overwrite them on top of “Qualification Prompt” that is being used in the Cube – ensure, the hierarchy objects exists within cube and dataset you’re working with.
- Note: Key Point ==> To highlight again – Ensure, Report holds hierarchy object before saving the hierarchy prompt
After all the above step, if you run the datasets/reports coming from the Intelligent cube as source, it will be having “Hierarchy Prompt” for usage.
It is an wacky behavior but love this workaround to leap through this for having better business experience end of the day.
Hope it helps someone out there trying to achieve it!