Datazen: Load On Demand Data with Parameterized Queries

Posted by: Filip Posted on Mon Oct 28, 2013 @ 5:04 PM Filed under: Datazen, Tutorials 0 comments

One of the great features of Datazen is its ability to download the entire data set onto the Windows 8 client and then perform all filtering and calculations locally.  Not only does this make all interactions (after the initial load) instantaneous, but it also allows the dashboard to be fully functional when the connectivity is lost (as long as the administrator has allowed this for the data view being used).  This makes Datazen dashboards truly mobile, as you are able to enjoy them on your mobile device on the go or while away from the office.

Even though this approach is great for most dashboards, it presents a problem when dealing with extremely large data sets.  If the dashboard is dealing with hundred thousand or millions of records, downloading the entire data set can be a burden for the client, especially if it is a tablet with lower end hardware.  On top of that, the time needed to transfer the data can make the initial load of the dashboard slow.

To solve this problem Datazen 2.0 has introduced parameterized queries.  This allows the data views used by the dashboards to accept parameters from the dashboard and use them in the data queries (i.e. SQL, MDX) to return only the data need by the current view of the dashboard.  When the end user interacts with the dashboard and changes a filter, the data queries are re-executed and again only the necessary data is sent back to be rendered in the dashboard.

Creating Dashboards with Load On Demand Data

Creating dashboards that load data on demand is a two step process.  First you need to create the appropriate data views that allow parameters from the dashboards to be passed into the queries.  Once this is done, you have to connect the parameters to the appropriate filters in the dashboards in order for the view to be properly used by the dashboard.

Creating Parameterized Views

Start off by creating a new data view inside your Data Source in the Control Panel.  Currently parameterized views are supported for SQL and MDX queries.  Note that you have to be logged in as admin or the hub owner to configure data sources in the hub.

Check the "Real Time" checkbox and then click on the "Define Parameters" link beside it.  Note that since parameterized queries are executed every time the view changes in the client, they have to be real time and cannot be cached on the Datazen Server.

Here, we can configure all the parameters we need to pass from the dashboard into our query in order to optimize it.  In our example we will use parameterized queries to return only results within the selected time range and only for the selected product.

Note that for the Product parameter we use the "%" symbol as a default value.  This is a Transact SQL operator that will match any string, meaning that we want all values returned when nothing is specified in the parameter.

We can now click on "Done" and create our query.

In our SQL query we can now make use of the parameters by enclosing them in a set of double braces such as  {{ @ParamName }}.  Note that the Product WHERE clause has a special case for the empty string.  The reason for this is that product selection will come from a Selection List control in a dashboard that has the "All" option enabled. When the user selects "All" the selection list will pass the empty string as a selection which we handle in our query to select all products.

We can click on Next to finish creating our query which ends the server part of the configuration.

Using Parameterized Views in a Dashboard

Once we have our parameterized view created, we can configure our dashboard to use it.  In our Windows 8 Datazen client, we can create a dashboard that will have the Time Navigator for the time range selection and a Selection List to select a product.  The idea is to pass the selected values to the parameterized query so that we only receive the data we need to display.  Our sample dashboard can look like this:

We can go into the Data View and import the data source we created previously into our dashboard.

The data source will appear with a green P to denote that we can configure parameters on it.  We can click on the gears icon beside the green P and select the Param option.

The parameter configuration screen will allow us to bind any of the available data parameters we have in our dashboard to each of the parameters available in the query.

We proceed by binding the Time Navigator selected start and end time to the two time parameters and the selected item in the Product selection list to the Product parameter.

By completing the parameter setup we have now successfully configured load on demand with parameterized queries.  

We can now bind other visual controls to the Data for Product Time parameterized query.  Each time one of the parameters changes through end user input, the data source will be refreshed, along with all the controls that use that data.  With this approach, you do not need to configure the Product selection list to filter anything on the client since the filtering is done on the database and only the needed results are returned.

But the main advantage is that dealing with millions of records will not be any slower than dealing with a few hundred.

blog comments powered by Disqus