One of the common questions that has come up around Datazen is folks wanting to see some basic information around their dashboard usage. While there isn’t anything like that available on Datazen server right now, there is a way you can setup the collection of some simple metrics on each dashboard. Let’s walk through how you do it.
You’ll need to setup a table on a SQL Server instance (or Azure SQL Database is a great option, and what we used for this example) to collect the metrics. The table will have just a few fields – ActivityId, ActivityDate, ActivityDashboard, and ActivityUserName (I’ll provide the SQL scripts I used for this as part of a zip file you can download at the end of this post).
Once you’ve created the table, you need a way to insert the data coming in from your Datazen dashboards. We’ll use a simple stored procedure that runs each time someone opens a dashboard. You need to make sure the procedure also includes a simple select statement after your stored procedure executes. I’ll explain why in a little bit –
Once that’s done, switch over to your Datazen server instance and add this SQL instance as a new data source.
Here’s where the magic happens – you’ll want to add the stored procedure as a query, and this needs to be a real-time data connection. You’ll also need to make sure you are passing through the parameters from Datazen that you need to capture. So you’ll first add a new parameter from the ‘Define Parameters’ link highlighted here –
Setup a parameter for your Dashboard Name and assign a default value that will appear when a user is entering the dashboard design screen for that dashboard. This allows you to determine who might have made changes to a dashboard, for example. Here’s what I chose –
Once complete, I’ll now enter my query text and make sure I’ve checked the ‘Personalize for each member’ option. This will give me the ability to capture the username for each user and pass that as a parameter. Here’s my final result –
Notice I added single quotation marks around the username parameter, and also used curly brackets around each parameter I am passing back to the stored procedure. It won’t work properly if you don’t do that.
I hit finish to save this information, and I now have the following in my list of data queries. It was successfully setup, but I don’t have the ability to preview the data like I do with other queries I’ve created. That’s okay, as long as you have the successful status message like I’ve highlighted here.
To test this out, I go into my Datazen publisher app and create a brand new dashboard with a single widget. It doesn’t matter which one, be creative!
Once complete, you now can add the data source you created earlier to the dashboard. Here’s where you see the results of the query that runs in the stored procedure. Datazen has to have a result set returned for the data view to be added to the dashboard, which is why we had to have the query included –
To make sure you properly now capture the dashboard name, you need to click the Param. selection under the gear in the data source tab to bring up the selection screen.
Then type in the dashboard name as the default value that gets passed back to the SQL table.
Hit Apply and run a preview of your dashboard. If you check your table in SQL Server, you’ll see the resulting record has been inserted into the table.
You notice you don’t need to hook it into any of your dashboard widgets for this to work, and this will work for any dashboard you setup and publish to your Datazen server.
Now you can view a simple report (maybe even in another Datazen dashboard!) that shows you the user activity for each of the dashboards you’ve added this data view to, including the time, username and dashboard name. It also opens up a number of possibilities for you to fire off other events every time a user opens a dashboard that I’ll explore in future posts.
Here is a zipped file of the SQL scripts I used for this post – http://1drv.ms/1TbMVPN
Special thanks to Christopher Moncayo for his help with this post.
