Dashboard Selection Made Easy – Part 2: Dimension with Large Number of Members

In first part of my blog I explained how to simplify time selection. In second part of the blog I will explain how effectively provide selections for dimensions with large number of members.

There can be dimensions with thousands and thousands of member, for example, Material or Sold-to. No way dashboard can provide selection for each and every possible Material or Sold-to. It is more a requirement for a report with selection screen. Dashboard is meant for fast, simple, intuitive and interactive data analysis. User should be one click away from desired selection at most. In case of dimensions with large number of members, only limited number dimension members should be offered for selection, for example, top 10 customers or top 20 materials (biggest contributors).

In my example I use Flight data model dashboard that helps analyse flight occupancy rate (trend, dependency on Plane Type, etc). Dashboard provides Time selection and Region selection that drives Airline selection (in our case a dimension with large number of members. Top 3 Airlines are selected based on Seats Occupied)

Time or Region selection change causes Airline selection change (Top 3 and 3 airlines individually)

If user needs to provide selection for specific Airline other then Top 3, then he always has a choice to drill down to analysis application and provide required selection

Here is a list of steps needed to implement dashboard selection for dimension with large number of members:

• Create Top N Query with activated Top N Condition
• Create Top N List Query that limits dimension with large number of members using Query Replacement Path Variable (Query from previous step)
• Use Top N List Query in Lumura Designer Dashboard to populate Large Number of Members Dimension selection Drop Down

Create Top N Query with activated Top N Condition

Note: Query is limited by Month and Region same as other Lumira Designer Dashboard DataSources

Note: Query lists Top 3 Airlines based on Time and Region selection

Create Top N List Query that limits large number of members dimension using Query Replacement Path Variable 

Note: in addition to Time and Region Top N List Query is limited by Top 3 Airlines using Query Replacement Variable

Use Top N List Query in Lumura Designer Dashboard to populate Large Number of Members Dimension selection Drop Down

Note: every time when Time or Region selection change it trigger Top N List Query refresh. Script attached to On Result Set Changed event to re-populate Airline Drop Down with new Top 3

if (g_month != DROPDOWN_MONTH.getSelectedValue() ||
	g_region != DROPDOWN_REGION.getSelectedValue() ) {
//
DROPDOWN_CARRID.removeAllItems();
var airline	= DS_CARRID_TOP_N_LIST.getMembers("CARRID", 1000);	
var seats_occ = 0.0;
var seats_occ_max = 0.0;
var airline_key = "";
var airline_text = "";
var airline_selection = "";
airline.forEach(function(element0, index0) {
	seats_occ = 0;
	seats_occ_max = 0;
	airline.forEach(function(element, index) {
		seats_occ = DS_CARRID_TOP_N_LIST.getData(GLOBAL_SCRIPTS.Get_Measure_Member_Id(DS_CARRID_TOP_N_LIST, "Seats Occ."),{"CARRID":element.internalKey}).value;	
		if ( seats_occ >= seats_occ_max && 
			   airline_selection.indexOf(element.externalKey) == -1 
		) {
		 	seats_occ_max = seats_occ;
		 	airline_key = element.externalKey;
			airline_text = element.text;
		}
	});
	DROPDOWN_CARRID.addItem(airline_key, airline_text); 	
	if (airline_selection.length==0) {
		airline_selection = airline_key;
	} else {
		airline_selection = airline_selection + ";" + airline_key;				
	}			  	
});
DROPDOWN_CARRID.addItem(airline_selection, "Top 3",0);
DROPDOWN_CARRID.addItem("", "All",0);
DROPDOWN_CARRID.setSelectedValue("");

//		
	g_month = DROPDOWN_MONTH.getSelectedValue();
	g_region = DROPDOWN_REGION.getSelectedValue();
}

Note: Top 3 entries are added to Drop Down in according to Seats Occupied in descending order

Note: g_month and g_region global variables are checked before execution of script comparing with current selection to avoid recursive call

DS_COUNTRY.setVariableValueExt("CARRID", DROPDOWN_CARRID.getSelectedValue());

Note: once Airline value selected from Drop Down this selection is applied to all Dashboard DataSources (except for DS_CARRID_TOP_N_LIST)

DS_COUNTRY.setVariableValueExt("CARRID","");
DS_COUNTRY.setVariableValueExt("CALMONTH_TECHED", DROPDOWN_MONTH.getSelectedValue()); 

DS_COUNTRY.setVariableValueExt("CARRID","");
if ( DROPDOWN_REGION.getSelectedText() == "All" ) {
	DS_COUNTRY.setVariableValueExt("CONTINENT", "");	
} else {
	DS_COUNTRY.setVariableValueExt("CONTINENT", DROPDOWN_REGION.getSelectedValue());			
}

Note: once Time or Region selection changes it resets Airline selection for all Dashboard DataSources (except for DS_CARRID_TOP_N_LIST. Airline selection was never applied to it)