Home / Tech Tips / Creating a Performance Optimized Dashboard Leveraging Derived Tables in IDT

Creating a Performance Optimized Dashboard Leveraging Derived Tables in IDT

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

performance-optimized-dashboard-1

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”

performance-optimized-dashboard-2

A stand-alone report by table is now created as shown below.

performance-optimized-dashboard-3

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

performance-optimized-dashboard-4

performance-optimized-dashboard-5

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

performance-optimized-dashboard-6 performance-optimized-dashboard-7

Select the Query browser from the dashboard and select Universe query option, connect to BI platform and select the e-staff universe.

performance-optimized-dashboard-8

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

performance-optimized-dashboard-9

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

performance-optimized-dashboard-10

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

performance-optimized-dashboard-11

In the dashboard designer, map the query prompt to cell J5

performance-optimized-dashboard-12

Drag a column chart and map the Y-Axis & X-Axis to the Query elements as shown

performance-optimized-dashboard-13

performance-optimized-dashboard-14

performance-optimized-dashboard-15

Drag a connection refresh and map its trigger value to cell J5

performance-optimized-dashboard-16

performance-optimized-dashboard-17

Now when you preview the dashboard, the Refresh query runs initially and the Total Salary by Region graph shows up

performance-optimized-dashboard-18

When the State label is selected, the same graph shows Total Salary by States

performance-optimized-dashboard-19

When the City is selected, the graph shows “Total Salary by City”

performance-optimized-dashboard-20

PS: The “Refresh Query” option can be hidden behind the company logo or behind the background.

About Rajesh Ponnurangam

Rajesh Ponnurangam is a Senior Business Intelligence Consultant with Infosol. He is a SAP certified associate with extensive experience in design and development of Business Intelligence applications for apparel, retail, consumer goods and manufacturing industries. He specializes in Business Objects solutions, SAP HANA and BW data modeling. He has a proven track record in designing strategies for ETL, BI application development, implementation, rollout and support.

Check Also

InfoSol News: Keeping You in the Loop on All Things BI!

InfoSol sends out a monthly newsletter that will keep you up to date about Limitless BI ...