How to improve dashboard refresh performance

Share on facebook
Share on twitter
Share on linkedin
Share on reddit
Share on email

Let say you have the following three widgets on the dashboard

Image

Typically each widget can use its own individual SQL query to populate the widgets.

Users widget: select count(*) from xuser

Projects widget: select count(*) from xcategory

Dashboards widget: select count(*) from xobject

Now in the above example the SQL is pretty simple and it might very well be the case the results for each query return back in micro or milli seconds.

But what happens, if each of the query starts taking time in say 5 seconds per query. So the total response time for the entire dashboard to finish refreshing is around 15 seconds and that is a lot of latency.

In the following example we will achieve the same result with a single SQL and updating each widget at the client side.

1. Issue single SQL to get all the desired results

we combine all the SQL in union clause and then use it with the box widget.

NOTE: we may use any other widget as this is going to be the main data collector and not displayed on the dashboard.

a. Add the box to the dashboard

Image

b. Right click and add the data source

select ‘Total Users’ as metric_name,count(*) as metric_value
from xuser
union
select ‘Total Projects’ as metric_name, count(*) as metric_value
from xcategory
union
select ‘Total Dashboards’ as metric_name, count(*) as metric_value
from xobject

Image

c. In the javascript tab add the following channel values

glb_api[“add_channel_value”](“user_count”,glb[“_json”][“data”][1][0]);
glb_api[“add_channel_value”](“project_count”,glb[“_json”][“data”][1][1]);
glb_api[“add_channel_value”](“dashboard_count”,glb[“_json”][“data”][1][2]);

Image

Overview: In the above box, we added the SQL that returns the data simply as two column array. Next in our post data javascript, we take each array value and assign it to a specific channel value. We are simply piggy backing on the dashboard drill down functionality. For now you can simply follow the procedure.

So far, this is how our widget on the dashboard look like

Image

Don’t worry about how the data looks as we will hide it from dashboard display.

2. Add the individual widget containers

Image

a. double click on the circle and type some value in it.

Image

Change the font size to your desired setting.

b. right click and duplicate the widget 2 times

Image

So now we should have three circle containers on the dashboard

Image

c. Right click on the first circle and select “Data Source”

Image

1. In the SQL tab, pick the connection “Just text in SQL” from the drop down (it is near the end of the list)

2. Click on the “Show Filters/Prompts”

3. Expand the Other node and Select the channel variable “user_count” (remember we added this channel variable inside the javascript of the parent box)

4. Click on “Add Selected Filter”

5. The value “param<user_count>” is added in the SQL box.

6. Close the window.

Clean up the javascript TAB. For new widgets, the data source is inherited from last time you updated any widget’s data source.

Since we don’t need the javascript here we simply select and delete it and keep it blank.

Image

 

Click OK and you should see that the circle now has the user count value

Image

Repeat the steps for the second circle but this time select the project_count variable

Image

And for the last widget pick dashboard_count

Image

Now that we have our widgets, we can the parent box settings to make it appear hidden.

Image

Right click on the main box, select “Toggle Background” once to hide the background, and then select “Toggle Border” to hide the border.

Image

Now select the box and change its text color to white

Image

You can type the color “FFFFFF” or selec the white color

This is how your final dashboard will be

Image

Notice how the box is not showing on the right side but still is responsible for fetching data and populating rest of the widgets.

The Core Tools

Create dashboard for any Database

Data Visualizer and Dashboard Application
SALE
This is the best dashboard software for its price. One good thing we did was to hire their consulting services to build few dashboard prototypes and provide some quick dashboard training.
- Terry Seal, IL
We evaluated Xcelsius and Qlikview and the cost for organization to implement dashboards was quoted over 10,000 USD. For fraction of the above quoted price, we were able to buy the licenses for the web based dashboard software and get some free training. This is truly a dashboard software for small businesses like us.
IT Manager of a Trucking company, OH