Datazen – Page 4 – Christopher Finlan

Render unto Datazen – a solution for rendering issues

In the latest server build (which I spoke about here last week), some users were running into issues with the Datazen rendering service. In plain English, it just means they weren’t seeing their dashboard thumbnails properly in the web browser or client apps. We’ll be tweaking the server install slightly to make sure this doesn’t occur for anyone, but if you have an issue prior to that being updated, there is a simple fix for this.

In your Datazen server file explorer, navigate to the following folder –

C:\Program Files\Datazen Enterprise Server\renderer\svc

Copy the ‘Microsoft.IdentityModel.dll’ file –

Paste it into the following folder –

C:\Program Files\Datazen Enterprise Server\renderer\viewer

You can then restart the Datazen Server rendering service on your server –

And your days of rendering issues should be over (if you were having any to begin with, that is).

New Datazen Server Build Brings some MDX Magic

Oh, how I’ve waited for this day. Finally, the days of handwriting MDX queries in Datazen server are over for me. Thanks to the latest server update, the server can now handle MDX generated from a tool like SQL Server Management Studio or SQL Server Report Builder. Not only that, I have additional control over the field names and field types when the query results are returned. Let me show you how it works using SSMS – Right-Click on your database and select browse – The handy Model browser comes up where I can drag and drop my fields from my model and build my query. I’ll bring in Country Region Name and State Province Name from Geography plus Internet Total Sales from my Measures. My results are returned like so – Now I want to get the query that generated by this to use with Datazen. I can simply toggle off Design mode by clicking this button in the toolbar – And my query text appears – I’m going to copy this text and switch over to my Datazen control panel and paste this query as a new data view under my SSAS data source – Before today’s update, when I hit next, this query wouldn’t work. It would just bring back a blank result set. But now, a couple things now happen. The first one is, a new screen appears in the wizard – I now have the ability to change the display name, the data types being returned from the data source, and exclude certain fields from the result set. This gives me a lot of flexibility I didn’t have previously. So I’ll change my display name to the following – I hit next and Whammo (that was for Mike Gannotti) – the query not only works, it is all cleaned up and ready for my Datazen dashboards – This is great news for folks who love Datazen, but don’t love handwriting their MDX queries. And the new abilities to change the display name, data type and field list is there for all datasets, not just SSAS. So SQL Server customers, grab the new server build of Datazen and take advantage of this great new functionality. Thanks for reading!

Combine Datazen dashboards with Power BI Q&A functionality

Happy Friday, everyone!

Did you know that Power BI now allows you to directly link to individual dashboards? This got me thinking – I love the Q&A functionality in Power BI. Could I easily put together a solution where I used Datazen for my dashboards and jumped into the Power BI service to ask additional questions? Why yes, yes I can. Here’s how –

For this example, we’ll use a simple Excel workbook as the data source. But let’s leverage the capabilities of OneDrive for Business to make this solution a bit more elegant.

Why? Couple reasons –

1. Power BI will automatically refresh datasets sitting in OneDrive every hour, so anytime you update your Excel data, it’ll get grabbed automatically by Power BI shortly thereafter.

2. You can leverage the OneDrive for Business sync client to save Excel files locally on your PC and have them automatically sync to other machines running the sync client. This makes it easy to get my Excel files onto my Datazen server after I’ve installed my sync client on there – I can refresh my files locally, save them to my OneDrive folder on my PC, and they’ll show up on the Datazen server within minutes! I could even use a tool like Power Update to automate the data refresh of my Excel files.

Let’s get started –

First, I’m going to open Datazen and draw my dashboard first so I know how I should lay out the data in my Excel workbook.

With that done, I’m going to create my workbook from my live data source using Power Query to make sure it’s laid out the way I need.

Now, I’m going to save the file to OneDrive for Business. Once that’s done, I’m going to my PowerBI.com site and creating a new dashboard called “StoreQA”.

Now, I’m going to add my data to the dashboard. So I click “Get Data”

Then I select “Files”

Now I want to select the “OneDrive for Business” tile

And then find the file I just saved

and hit “Connect”. It’ll then bring my data into the dashboard and I can leverage Q&A with it.

I’m going to leave this open for now, and jump back to my Datazen Publisher App to bring in the Excel data to my Datazen dashboard. You’ll need to setup the OneDrive for Business folder on your Datazen server as the location for the Excel data (this assumes you setup the sync agent on the box already).

With that done, you can bring in the Excel data to your Datazen dashboard

and hook back in the elements accordingly.

Once that is done, you’ll want to create a link from the Datazen dashboard to the Q&A in Power BI for this dataset. Go back to your browser and grab the URL for the dashboard from the address bar. It’ll look like the example here – https://app.powerbi.com/dashboards/xxxx

To go directly to the Q&A functionality for this dashboard, just add ‘/qna’ at the end of the url, so it then looks like this – https://app.powerbi.com/dashboards/xxxx/qna
You can test it out and see how it takes you right to the question and answer area for this dashboard –

Simply copy that link and select an element on your dashboard you’d like to link to the Q&A piece from

by using the Drill-Through Target functionality to point to a custom URL

Now when I click on the element, I get right to the Q&A page in Power BI with the same dataset so I can ask it more questions about my data

If you really wanted to get clever, you could even use the parameter functionality. See how when I ask a question in Power BI, the URL changes to include the question text –

https://app.powerbi.com/dashboards/f31f7d2f-13ba-4e21-8b22-754ac62d39d7/qna?q=what%20were%20total%20sales%20for%20grocery%20by%20year

and gives me the following result

I can simply change it to use a parameter instead, so it would look like this –

https://app.powerbi.com/dashboards/f31f7d2f-13ba-4e21-8b22-754ac62d39d7/qna?q=what%20were%20total%20sales%20for%20{{ SelectionList01.SelectedItem }}%20by%20year

and when I run my dashboard, I can change the dropdown

and the question will dynamically change when I click on my element with the link to Power BI!

Remember, you have to publish the dashboard to your server before you can test the URL drillthrough in Datazen!

And with Power BI now having the ability to setup custom links with it’s elements, you could even pin the tile for the question you just asked so you could jump back to your Datazen dashboard!

Hopefully this helped open up a number of possibilities with the tools for you to explore. Have a great weekend, and don’t forget to download the Windows 7 publisher app currently in preview from here and give it a spin.

Thanks for reading!

How to enable user activity reporting on your Datazen dashboards

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.

Shape of the Union or Free Datazen Maps!

This blog post will be short, but hopefully valuable for everyone. I’ve included a link to a zip file at the end of this post containing all the US states and some variations on the continental US you might find valuable (4 region view, 9 region view, etc.)

It’s easy to add these to your server and make them available in your dashboards. To do so, go to the Datazen hub you wish to add the new map shape to and click “Custom Maps” on the left-hand navigation bar –

Then click “Upload Map Shape” to open a new dialog and select the files you wish to upload –

Once you’ve uploaded them, you’ll have access to them whenever you select any map object in your designer screen by selecting “Custom Map from Datazen Server” in the dropdown. You’ll be prompted through a few screens to select the map you want to use.

Now you have your new map in your dashboard!

Here is a link to the files for you to download and use – http://1drv.ms/1FOAwXL

Enjoy!

How to use Visual Studio LightSwitch oData feeds with Datazen and Azure

In the most painfully overdue blog post ever, considering my love of all things LightSwitch and Datazen, I finally sat down and consumed an oData feed I generated from a LightSwitch project I published to Microsoft Azure to use in a Datazen dashboard. It’s really pretty simple, and I’m kicking myself for not doing it before. Anyways, here’s how you do it –

1. If you don’t have the foggiest idea what Visual Studio LightSwitch is, you can read more about it here. It’s a fantastic tool for building rapid line-of-business applications for the desktop and mobile web apps. Plus, it’s available in the community edition of Visual Studio 2013, which is free for individual developers!

2. You need an app you are going to deploy and use the data feed from. Beth Massi has an excellent walkthrough (including a link to a sample app you can use) of how you can deploy a LightSwitch App to Azure. It’s very easy to do, and this is a great option to make your app available for Datazen to consume.

3. Once published, LightSwitch exposes the different data sources as easily consumable data feeds via oData, which happens to be one of the data sources that Datazen consumes out of the box with either anonymous or basic authentication. You could also create a custom data provider that used more advanced authentication options. To learn more about what oData is, you can read about that here.

So I published a simple app and put in some sample data to use with my Datazen Server –

Now, how do I setup Datazen server to use this feed as an oData source? Well, this confused me the first time I did it, so let’s go through it –

Now add a new data connection –

This is where you might get tripped up. Unlike every other data source listed, you don’t actually enter the data connection string at this point. You give your data source a name, choose the authentication method (and enter your credentials), hit next to setup permissions for the feeds, then hit finish.

If you try and put in the URL as the data connection name, it doesn’t save it, so it looks like the oData data source doesn’t actually work. Once you’ve saved the data connection details, you’ll see a new folder appear in you folder/data connections list –

Now, you click the folder name, and click the new data view button to add your first oData feed.

Here, you can now enter the url in the OData view URL area that you wish to consume.

Hit next then success, the feed is there and working!

This gives you an easy way to visualize your data from your LOB app using your Datazen on your favorite mobile device. It also opens up a number of options around filtering your data prior to bringing it into Datazen using the entity set filter options with LightSwitch.

Thanks for reading!

Drillthrough from one Datazen Map Dashboard to another dynamically

Tonight’s post came out of a conversation I had with my friend Chris here at Microsoft (yes, we’re both Chris). I was looking to see if I could drillthrough from a map of the United States into each individual state, and see the county-level information of another Datazen dashboard. The answer is – yes, kindof!

Why the caveat? Because I want to dynamically have the state change based on which one I select for drillthrough. You can’t do that by drilling from one dashboard to another inside the Datazen app. But you can do it by using the web viewer functionality in Datazen for your lower level dashboard. Let me show you what I did.

First, in the Datazen Designer, I dragged a single Range Stop Heat Map onto the canvas and renamed it accordingly.

Next, I used Power Query in Excel to search online and find the state by state results for the 2012 Presidential Election. I brought the results into an Excel file, and loaded the file into my Datazen dashboard under the “Data View” tab by choosing “Add Data” and “Local Excel File”.

I made “Obama” the Values selection and “Romney” the Targets selection. It doesn’t really matter which one you choose for which for this example, however. Then I flipped back to my layout view.

I changed the range stop values to show a very narrow range for Neutral Start/Neutral End (99.98 and 99.99), so unless you had another Florida situation, you’d only see two colors for our map. At this point, I saved it and created a new map dashboard. But instead of using the out of the box maps, I loaded a new custom map from my local hard drive by choosing “Custom Map From File” from my map selection list. Here I loaded the Pennsylvania map files I had (here they are if you would like to use them. Just save them locally and extract them to somewhere on your machine – http://1drv.ms/1H1PtLp).

Then I created a new map dashboard, doing all the same setup items I did for the map of the US. I called it “Pennsylvania” and published it to my Datazen server.

I want everyone to see this dashboard, so I need to make it public, which is something that Datazen server supports. To do that, I need to add a guest account to my Datazen server, and then give it rights to that dashboard. So I add a guest Server User, like so –

Then I add Ol’ Guesty to my hub as a user –

Then I make sure I assign Sir Guest-A-Lot to my public dashboard (as you can tell, I’m kinda punchy right now).

Now I can link directly to this dashboard by using the following pattern as described in the Datazen documentation – http://local.server/viewer/public/dashboard?name=DashboardName.

With that complete, I can go back to my original map dashboard and set up the drillthrough to the url of this state dashboard. Since I want it to drillthrough to the selected state, and not just Pennsylvania, I’ll use the parameter value so my url looks like this –

http://sample.cloudapp.net/viewer/public/dashboard?name={{ RangeStopHeatMap3.SelectedItem }}

Now I publish my dashboard, and when I click on the state of Pennsylvania:

A web browser opens up and I get this!

So yeah, there’s obviously the drawback I need to do this 50 times in this example. But it DOES work (kindof), just like Chris and I discussed.

Thanks for reading!

How to drill-through from a Datazen dashboard to Power View in SharePoint

Welcome to the new blog home everyone!

So I’m assuming (perhaps incorrectly) most folks reading this know you can setup drill-through to a custom url in Datazen. Usually, I talk to folks about it in the context of drilling through to a traditional SQL Server Reporting Services report, and I’m passing through some parameters I’ve selected on my Datazen dashboard. But did you know you could also do something similar to a Power View report in SharePoint 2013? Well you can!

First, you need to make sure you have Cumulative Update 4 for SQL Server 2012 SP1 applied in your SharePoint instance. This update introduced the ability to use filters in a Power View URL. The filters act as “pinned filters”, meaning they will apply to your entire report. Assuming that is done, the process is fairly straightforward –

1. You’ll need to create a Power View report in your SharePoint instance (you can get more information on that here). You need to use the table name/field name of the parameter you want to pass-through as part of the url, so make sure you know both items. For example, I used a field called ‘ATUName’ from a table called ‘ATU Name’ (quite clever, isn’t it?) for my example.

This translates to [ATU Name].[ATUName] when I’m creating my url.

2. Now that we have that setup, in the Datazen Publisher app, select an element that supports drill-through targets on the design surface. Open the Drill-through Target drop down in Visual Properties pane and select Custom URL, this will open the drill-through configuration dialog.

3. Now I can enter my custom URL with the parameter I want to pass-through. You have to make sure this typed out properly per the instructions in the blog post on using this functionality. This includes the proper spacing and using the correct quote type properly. See the example they give here –

And then the end of my URL string –

Note that I included the space before and after ‘eq’. You then also need to make sure you have quotes around the parameter you are passing through.

Now, you can’t test this working live in the development process. You have to deploy the dashboard to the Datazen server, THEN you can test it. An easy way to do that is from the web viewer. If you login to your server in a web browser, you’ll see your dashboards listed like so –

Select the dashboard you updated, and you’ll now see a little arrow on the widget you added the drill-through to Power View to.

Make sure you’ve selected the value you setup as the parameter you’re passing through to the Power View report, then click the widget to jump to the Power View report.

Your report will open with that parameter now pinned and applied to each part of the report!

This works from either the browser view of the Datazen dashboards, or from the Windows 8 apps, provided the web browser opens in desktop mode since the Power View report in SharePoint still renders in Silverlight.

Have a great weekend!

How to use an Excel data source for KPI’s in Datazen

Happy weekend, folks. As mentioned in the guest post I did with Rob Collie, a folder of Excel files can be used as a data source for dashboards with Datazen. However, when it comes to KPI’s, there isn’t out of the box support to use Excel as a data source for those. But you can still use Excel if you setup a custom data connector to do so. One caveat with this approach – what’s we’re basically doing is allowing Datazen Server to use Excel like it would a normal SQL Server data source. This means you’ll need to write at least some basic T-SQL statements to actually bring back data from your Excel workbook.

Assuming you know how to do that, here are the steps you need to follow to enable this –

1. You need to download and install the Microsoft Access Database Engine 2010 Redistributable (choose the AccessDatabaseEngine_x64.exe option) on your Datazen server.

2. Create a new folder named OLEDB in the following location (this assumes you setup a custom data provider with my previous post. If you haven’t, do that first.) –

C:\Program Files\Datazen Enterprise Server\service\dataproviderschemas

3. Download the following file – Excel.zip and extract the XML file to the folder you just created.

4. Now login to your Datazen Control Panel and go to “KPIs” for the hub you want to setup with your KPIs.

5. Click “Create New Group”

6. You should see the new provider you setup in the previous steps listed as “Microsoft ACE OLEDB 12.0 Excel 2007-Newer” –

Enter a name for your KPI group, and leave the HDR=Yes item under Extended Properties if your Excel file contains a header row. Otherwise, change it to “No”.

Next, in the data source area, put the file path of the Excel document you wish to you use. Make sure it is some place the Datazen service account has access to. Test the connection to make sure it works, and hit next if you see the “Connection Successful” message –

Select the groups who should have access to the data source, then hit Finish. This will bring you back to the KPI menu with your new KPI hub showing –

If I click “Create New KPI”, I’m presented with the KPI Creation screen. On the right hand side of the screen, I see my KPI Value/Goal/Status fields I can use my Excel data source for.

If I select the dropdown for “Value”, I can choose “Execute Query” and a “Define Query” link appears. I need to click it to write my query –

Now I can write my query for this KPI Value. The table name equates to the name of the tab in the Excel file, but you need to have a ‘$’ appended to it. A sample query would look like this –

If successful, it should bring back a single result to be used with that KPI –

Hit ‘Update’ to save your query and return to the KPI setup screen. You can now repeat the process for the Goal/Status/Trend Set items if you wish, or you may set any or all of those manually.

If you want to learn more about KPI’s and how to set them up in Datazen, refer to the Datazen documentation. Hope this was helpful, and enjoy the rest of your weekend!

How to build dashboards directly on your test/dev Datazen server

Greetings all! After guest-posting on Rob Collie’s blog about Datazen earlier this week, I’m back on my own for this post. One of the things that I love about Datazen is that you can use any Windows 8 device to build your dashboards on. Simply go to the Windows 8 store, download the Datazen Publisher app, and you can be building beautiful dashboards within minutes. Unfortunately, it appears not everyone is running Windows 8 in their organization (shocking, I know), so I wanted to show you a way to build dashboards directly on the Datazen server you spun up in my previous post, which is running Windows Server 2012.

When you use Windows Server 2012 machine, you have the ability to access the Windows App Store, just like you would from any Windows 8 machine. However, you have to enable that, as it is turned off by default. Here’s how you can turn that on –

First, go to the Server Manager and open it –

Then, you’ll want to enable a new feature. So choose Add Roles and Features –

Click next until you get to the “Features” selection, then scroll down amongst the features and expand the “User Interfaces and Infrastructure” selection area.

We can then enable the “Desktop Experience” by checking that box, clicking “Add Features” when the pop-up window comes up, and then choosing “Next”.

I’d suggest now checking the “Restart the destination server” checkbox and then hitting Install. It has to restart anyways before that stuff is enabled, so you might as well do it now. It’ll take about 15-20 minutes to install everything, and then the server will restart.

Once done, the Store App will appear.

Here is where the first gotcha comes along. You can’t use the built-in administrator account to use the Windows Store on the server, so you need to make sure you’re logged in with another account besides the one you setup when you setup the server. That user can have server administrator privileges, however. So great, you can open the Windows Store as this user. Here is the next gotcha –

You need a Microsoft Account to download and install the apps – so you can either create a new account, or you can use one you already have setup and use for Xbox Live, for example.

Once that’s done, you can download the Datazen Publisher App and it will start installing

Once finished, it will show up in your list of apps –

Now, one last gotcha. In order to get the publisher working on a Windows Server 2012 machine, you will need to create a Loopback Exemption for the Datazen App. You can do this one of two ways –

Open an administrator command prompt, and then type this:

checknetisolation LoopbackExempt –a -n=componentart.datazenpublisher_4hjyx1gpectq6

or install this on your server and use the gui:
https://loopback.codeplex.com/

I prefer the latter, but it is up to you. Once that is done, you can open the Datazen Publisher app and connect to your local server as the location you want to publish to.

Once connected, you should see any dashboards or KPI hubs that you’d previously created, and you’re ready to go. You now have a one-stop shop for both dashboard creation and hosting!

Thanks for reading, and I’ll have more stuff later this week!

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this: