In my previous blog, I talked about “5 Easy Steps for Designing a Dynamic Y-Axis Web Intelligence Report Leveraging Derived Tables in IDT”. In this post I am going to show how the same derived tables concept designed in IDT can be utilized to create a performance optimized dashboard.
Consider an employee dashboard with 4 different tabs “City”, “Employee”, “Region” and “State” reporting the same measure in the graphs of the dashboard. As an easy process we would create four different queries mapped to Excel or to the components directly. But what if we need more tabs? Creating too many queries and mapping them to Excel becomes clumsy and also the performance deteriorates. This article shows how to build a dashboard with 1 query with ‘N’ number of report by elements.
The article uses a simple e-staff universe with Salary by dimensions – Region, State, City and Employee.
Create a derived table in the data foundation layer of the universe with a dummy SQL statement which would display the “Report by” list of values
The SQL statement shown in this example is for Microsoft SQL server 2008. The SQL syntax does change based on the underlying database. For ex: Oracle syntax would be “Select ‘Region’ “Report_By” from dual”
A stand-alone report by table is now created as shown below.
Save the data foundation layer and in the business layer, drag the “Report_By” Field to the list of dimensions and create a new field named “Report Selection” with a SQL case statement as shown below
PS: The case statement needs to end with an END CASE
Once the universe is tested, create a dashboard with 4 different tabs mapped to Excel and with the properties shown in the screenshot. Make default selection as Region
Select the Query browser from the dashboard and select Universe query option, connect to BI platform and select the e-staff universe.
Once the universe is selected drag the “Report selection” dimension and the desired measure to the Result Objects section and the “Report By” dimension to the Filters section
Select the prompt option for the “Report By” Dimension with the Prompt properties as shown below. Make sure it’s an optional prompt and a default value is selected
In the usage option section of the query, modify the refresh options as shown in the screenshot. Map the trigger cell to J5, which is the destination cell for the label based menu
In the dashboard designer, map the query prompt to cell J5
Drag a column chart and map the Y-Axis & X-Axis to the Query elements as shown
Drag a connection refresh and map its trigger value to cell J5
Now when you preview the dashboard, the Refresh query runs initially and the Total Salary by Region graph shows up
When the State label is selected, the same graph shows Total Salary by States
When the City is selected, the graph shows “Total Salary by City”
PS: The “Refresh Query” option can be hidden behind the company logo or behind the background.