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 the Product manager for InfoBurst, Infosol’s flagship product. With more than a decade of demonstrated success assisting large entities, he is continually helping clients with their implementations using technologies such as Business Objects, Tableau, Power BI, SAP HANA and BW. He is responsible for product development initiatives through vision, strategy and roadmap development for InfoBurst.

Check Also

Tech Tip: How to stop Windows 10 Update from breaking Xcelsius (SAP Dashboards)

Tip: How to stop Windows 10 Update from breaking Xcelsius (SAP Dashboards) Trick: Easy install ...