[MSTG] Hierarchy Drilling

 

Hierarchy Drilling on MicroStrategy Dashboards

MicroStrategy supports various drilling options - System Hierarchy, User Hierarchy, URL APIs, Drill Maps, etc. The mechanism of drilling works differently on reports and dashboard.

Hierarchy drilling is very well supported on reports. MicroStrategy defaults to System Hierarchy when there are no user hierarchies built. In case there are user hierarchies, they over write the system hierarchy w.r.t drilling.

In MicroStrategy version 9, drilling options are enhanced in dashboards. When a dataset is bought into a dashboard and placed as a grid, there are couple new options under Data > Grid Data Options > General, as shown below (below the respective grid).

When drilling is enabled, options can be set to "Drill Anywhere" or "Drill Within".

What does "Drill Anywhere" do? - Users can drill on attributes on a dashboard (from grids/graphs) based on system/user hierarchy, MicroStrategy will open the original dataset and continue drilling on the report. Point to be noted is that, user is navigated away from the dashboard to be original dataset which is behind the dashboard. 

What is "Drill Within" do? Obviously, every user wanted to drill within a dashboard, without navigating out of the dashboard. With the "Drill Within" option this is doable, but the destination drill objects should be included in the dataset. For example, if the user wanted to drill from Country to State in the grid, both the attributes should be in the dataset (not required to be in the grid of the dashboard). Performance should be considered while enabling this option.

Bottom line - drilling within a report on a dashboard is not dynamic yet. Meaning, when the dashboard is executed and a user prefers to drill only on one of the grids/graphs having the other remain as is, this is not possible without pre-loading the data into the dashboard/server.

[MSTG] Using MicroStrategy Scheduler

 

Using MicroStrategy Scheduler

Scheduling Reports based on time and event

Reports can be schedule based on time or event using MicroStrategy scheduler.

Event based Scheduling: Lets assume we have a status table (name: load_status) in our warehouse which indicates an event of a fact table data load. We wanted to execute a few reports based on the event (when it turns '1').

Command manager script

 
Create a command manager script (name:fact_load.scp) with a "trigger event" command. This will be a .scp file with the following command (assuming "FACT LOAD" is the event name)
TRIGGER EVENT "FACT LOAD";

Shell Script

 
Create a shell script (name:face_load.sh) which will have the SQL to check the status table (load_status) for a particular event (in our case - '1'). If the status is 1, the shell script will run the command manager script (fact_load.scp). Upon successful completion, a post SQL can be included to change the event to '0' in the load_status table. Below is a sample shell script-

export PATH=:.:$PATH

CONNECT=CONNECT PARAMETERS
# get  part_date
sql_return=`$CONNECT << EOF
   set pagesize 0
   set pause off
   set feedback off
   set heading off
    select job_status
    from load_status;
EOF`
echo sql_ret is :$sql_return:
echo  job_status is :$job_status:

 if [ "$job_status" == 1 ]; then
  echo " going to call trigger"
 cmdmgr -n "ISERVERNAME" -u administrator -p PASSWORD -f "fact_load.scp" -o "fact_load.log"
  echo "finish call to mstr"

   sql_return=`$CONNECT << EOF
   set pagesize 0
   set pause off
   set feedback off
   set heading off
 update  load_status
    set job_status=1;
    commit;
EOF`

  else
   echo sleep for some time
  fi

exit 0

Batch File
Create a batch file "fact_load.bat" which can trigger the shell script, fact_load.sh

Windows Scheduled

Create a windows scheduler which will run the batch files on a regular interval.

MicroStrategy Scheduler

Create a MicroStartegy Scheduler to executes reports based on the event "FACT LOAD"

Administration > Schedule Manager 

Right click > New Schedule

Once the schedule wizard opens - Click "Next"

Enter Name and Click "Next"

Select "Event Triggered" and click "Next"

Enter "Start Date" and Select "No End Date". Click "Next"

Click on "Events"

Click on the top button of the wizard which will allow us to create a new event
Enter the new event name as "FACT LOAD"

Click "Next" and then "Finish"

Right click on the new schedule we create and select "Schedule Multiple Reports"
This wizard should allow you to select the project, report and the user/user group to which you wanted to associate.

Creating a time triggered schedule is similar. Just that "Time Triggered" should be selected instead of "Event Triggered" in the wizard.

[MSTG] Comparison Of Various Types Of MicroStrategy Filters

 

Comparison of various types of MicroStrategy Filters

Report Filter	View Filter	Report Limit
Report filter is applied in report SQL.	View filter is not applied in report SQL.	Report limit is applied in report SQL.
Applies to any object present or not present in report	Applies to anything present on report only	Applies to only metrics
Applies before aggregation	N/A	Applies on Aggregation
Appear in WHERE clause	N/A	Applies in HAVING clause
Never applies in last SQL pass	N/A	Specially applied in the last pass 1
Condition is not case sensitive	Condition is case sensitive	N/A