Category: Datazen

Combine Excel Online and Load on Demand to power up your Excel data source in Datazen

image

I hadn’t planned on a post today, but after watching the Philadelphia Eagles get their first win of the season, I was inspired to do something I’d first thought about a couple weeks back.  Specifically, I wanted to use an Excel document as a data source in my Datazen dashboard, but enable Load on Demand capabilities so I could take advantage of parameters.  I also wanted to see how that would work in combination with people just typing in data into the Excel sheets that are the data source.  How quickly would I see their changes in my Datazen dashboard?

Out of the box, Datazen allows you to use Excel files in a shared folder as a real time data source.  And in the post I did around combining Datazen dashboards with Power BI Q&A functionality, I first talked using the sync capabilities of OneDrive to sync an Excel file from my local drive to the Datazen server.  The thought was, that way if I refresh a data source locally, I can have it sync up to the server.  The issue I saw with using this as the solution for this scenario were two-fold –

1. I was the only person updating information in this scenario.  Ideally multiple people could make updates using Excel Online at the same time.

2. I couldn’t do parameters with the out of the box Excel data source in Datazen.

Well, what if I used the workaround I mentioned about how you could use Excel files as a data source for KPI’s?  Since that had you using Excel like SQL, maybe then I could use parameters.  Let’s give this a shot.

In my Datazen server, I’m going to create a new data source called Excel as SQL, using the instructions from the blog post I referenced –
image  I’m going to create an Excel file with two tabs – one has Category, Amount and Target along with some sample data.
image
The other just has Category and a single sample value.
image
I then save my file.  It’s now available to me either on the server or in a web browser via Excel Online, thanks to OneDrive.
image

On the Datazen server, I’m going to create two SQL queries to use.  The first one, called Data, looks like this –
image

You see I’ve setup a parameter called @Product to use – here’s what that looks like
image

I used a default value to make sure I get back at least one value, otherwise the Datazen Publisher won’t be able to build the data view properly.  Next, I’ll build my other query called FilterValues.  It’s pretty basic –
image
Now I can build my dashboard.  I’ll bring in two elements – a Selection list and a Number with Delta.
imageThen bring in both of the data feeds and hookup the data to the dashboard elements.
image
I’ll setup my parameter to be equal to what’s passed into my dropdown list.
image
Now I publish my dashboard.  When I hit it from a web browser or app, I get back what I’d expect to.
image
This is fine, but I’ll want to add more items on an ongoing basis.  If I try to open the document and make the changes directly on my desktop, the data source is locked and not available to Datazen –
image
But, if I instead make the changes in Excel Online, the changes get synced and my data source isn’t locked!  This is true even if I have multiple people adding items at the same time.  I’m going to update my Excel Online document by adding a number of new categories (as suggested by my children) –
image
How quickly these show up can vary – I’ve had it in as little as 5 seconds, and sometimes it’ll take up to a minute.  For example, these showed up by the time I finished typing the previous sentence.
image
And I’ll now update my data in the other sheet as well.
image
What’s cool here is I don’t need to refresh the page to see this new data, since it’s always going to back to the original data source thanks to Load On Demand.  Once my data shows up, my values are automatically updated when I change the dropdown.
image And notice I added the Seahawks data – I’ll never see that as a value if unless I add it to the dropdown as well.

This offers some great flexibility for folks to manage data and make updates this way.  You could also do multi-select values vs. a single value by changing your SQL statement and allowing multi-select in your dropdown list.

Thanks for reading everyone!

Throwback Thursday Fun – Use a Visual Studio LightSwitch App to enable search/auto-complete for large selection lists in Datazen

image

This is an admittedly goofy post, but maybe it points some folks in the right direction.  One question that comes up is around how can you better manage a large lookup list in Datazen.  Currently, the product doesn’t allow search functionality in the dropdown list.  There is a way to work around this, however.  Previously, I posted about how to set default selection values in Datazen dashboards viewed in a browser.  The next logical step here would be to build a custom app and pass the values to that url.  I did just that, and I built the app in Silverlight!

Why did I do it in Silverlight?  Well, I  figured for Throwback Thursday (and because it’s admittedly a hacky solution) I’d kick it old school and build a C# LightSwitch app.  And since I realize most folks aren’t looking to do Silverlight development these days, I won’t bore you with the normal code review and walkthrough.  Instead, I decided to post the app I threw together up in Azure so you can play around with it and see if inspires you (or makes you smile.  or weep).  Here’s what you do to give it a whirl –

1. Go to https://datazentbtapp.azurewebsites.net/desktopclient where you’ll be prompted to install the app locally on your machine.  There’s no security on it or anything (and I didn’t bother signing the .xap file).
image

2. You’ll see the little DatazenTBTApp logo on your desktop if you choose that option.  Double-click on it to open the desktop app.
image
3.  The app will open and you’ll see the only screen in the app –
image
4.  The dropdown list on the left contains over 2500 records to choose from, so it’s not something you can easily scroll through.  No problem – just type the first few letters in the auto-complete combobox in the app.  You’ll jump to the results that match what you’re typing.  I’ll start by typing “MGS” – I quickly jump to the matching results that start with MGS, and my dashboard updates automatically to give me the proper result –
image

That’s basically it – feel free to try typing in some different letters/words (obviously Fabrikam and Contoso are well represented in this product list), watch your dashboard update, and relive the magical year of 2010!

(And yes, I might do one in HTML5/JavaScript in a follow-up post sometime.)

How to auto-refresh a Datazen dashboard in a web browser

image

This is a common question I get asked about Datazen – people have setup a live connection to their data and want to make sure their dashboard is automatically showing the latest information.  One scenario was they wanted to view a Datazen dashboard on a big TV screen and have it update every 10, 20, 30 seconds, etc.  I was going to do an article about building a custom solution in Visual Studio using an iFrame, javascript etc.  But much like Indiana Jones found out in Raiders of the Lost Ark, sometimes a simple solution can be quite effective.  Here’s how I solved this, Indiana Jones style –

1. Download a copy of the Mozilla Firefox browser to your PC and install it.

2. Once installed, open up the browser and click the “hamburger” menu on the right hand side.  You’ll see an option for installing a browser add-on
image

3. Click the add-on icon and you’ll see the add-on manager open up.  Do a search for the term auto refresh
image

4. You’ll see a number of different add-on’s appear in the results.  I chose to use the one called “ReloadEvery”.  It offers options to right-click a webpage and have it reload at certain time intervals, which is what I was looking to do.
image

5. Choose the install button to install the add-on.  Once installed, you’ll need to restart your browser to have the add-on enabled.  Note –  The developer of the app requests you donate a few bucks to his development efforts if you feel generous.

6. Let’s test it out – I’ll go to my Datazen dashboard and right-click on the page.  A new menu option has appeared called “Reload Every”
image

7. I can now enable the functionality and tell it how often I want the page to refresh.  I’ll choose every 10 seconds –
image

That’s all I need to do.  My page updates every 10 seconds, even if I hit F11 and leave it as full-screen.  An easy solution to common request!

How to render a Datazen dashboard as an image file

I was going to run with part two of my productivity blog series, but knowing the start of the NFL season is tonight, I’m guessing most folks will be watching that vs. searching for my blog posts.  So instead, I have a couple quick items for folks –

1. A minor server update was posted this week that fixed a couple small bugs that were on our backlog.  You can download the latest build here – https://www.microsoft.com/en-us/download/details.aspx?id=48112

2.  There’s a way to render your Datazen dashboard as a static jpeg file if you need to do so.  How practical this tip is might be debatable (as you’ll see shortly), but here’s how you do it  –

Using the web browser, navigate to the demo Datazen server – http://demos.datazen.com

image
Enter the username/password – datazen/datazen and hit enter.

Click on any of the dashboards to have them come up in your browser.  Note the guid of the dashboard in the address bar and copy it –

image
Change the address to the following format.  Note I have added the guid after the ‘xaml=’ part of this url –

http://demos.datazen.com/m/DashboardServer.axd?xaml=bfd44166-e13f-42a2-b453-da6f7539793c&v=635775225103435624&output=image&width=1024&height=768

Now the item returned in your browser will be a static image of the dashboard, which I can save and use locally.  It only returns the default state  and doesn’t accept parameters in the url.  Nor will it change to reflect the current dashboard state if you’ve interacted with it, etc. – DashboardServer

However, notice the fonts/style match those of the Windows Store App vs. the ones you see in a web browser.  This made it a useful trick when I needed a quick screenshot and I was still using a Windows 8/8.1 machine.  It also gave me an easy blog topic, so perhaps it was more useful than I originally thought.

Now, with that out of the way – Are you ready for some football?

Powerful Productivity using Power BI and Office 365 – Part 1 in a series

Alright the long weekend is here (and sadly over by the time I finished this post)!  And since Monday is was a celebration of the worker here in the US and Canada, I wanted to do something special for this post.  Microsoft Office is the go to productivity suite for 1.2 billion people every day.  That translates to 1 in 7 people on the planet earth using Office to get things done, both personally and professionally.

It’s also a key part of every successful business intelligence implementation I’ve seen – and I’m not just talking about the ability to export to Excel.  In my previous life in shadow IT, the VP I supported used to hammer this point home to me.  He and his team lived in the Office suite day in and day out to do their jobs, so whatever I was providing to his team, I needed to always keep that in mind.  They had jobs to do that didn’t involve learning a complicated new report or app – if it wasn’t easy to adopt, show immediate value, and work well with things like PowerPoint, Excel and Outlook, then it was dead on arrival.

While I’d like to think I did this pretty well, looking back now, I can’t believe how much additional work I had to do simply to achieve the same results I could get just a few years later.  And I’m not talking about me being more skilled (or balder) – the level of productivity someone can achieve using tools like Office365 and Power BI is STUNNING.

You’re probably thinking – “Yeah, it’s “stunning” a Microsoft employee is telling us how amazing their new stuff is,” as you roll your eyes and switch to Jen Underwood or Rob Collie’s blog.  I’ll admit, that isn’t a terrible idea, but hear me out.  I just want to see what my old job would have been and how much more I could have accomplished.  I used to spend my days building apps, setting up databases, building reports, etc.  With the way things have been automated and simplified in Office 365 and Power BI, could they be done now entirely using those tools?

“OH MY GOD – HE’S NOT BLOGGING ABOUT DATAZEN!  THE PRODUCT MUST BE DEAD, etc.” – Thought by at least one person reading this blog post.

To this person I say, yeah, um, no.  No, the reason I am not including Datazen is because one of the rules I need to follow for this self-imposed challenge is that I can’t use IT to setup a VM for me.  I have to do everything in the context of Office 365 and Power BI for this particular challenge.

So, without further adieu, here’s a typical project request I might have gotten 5-6 years ago –

There’s a request in my inbox from someone whose team needs a line of business app built.  They need a simple app built to capture ten fields of data for a one-off project.  A couple temps will be entering the data for a week or two, and they’ll need to do it via a web interface.  There are some reports I’ll need to build off of the data, and I’ll need to show the final numbers in PowerPoint deck I provide back to them so they can add some additional commentary.

Requests like this I used to eat for breakfast (along with Honey Nut Cheerios, usually).  Let’s assume this was asked of me in 2009.  I probably completed the request this way (FYI – SharePoint wasn’t set up and available to me) –

– Ordered a virtual machine (VM) from IT then waited until it was finally provisioned for me.
– Installed SQL Server 2005/2008 on the VM to host the database
– Setup Internet Information Services (IIS) on the VM to host the website
– Built the app using ASP.Net maker (which I had purchased myself) to do these little forms over database projects.
– Deploy the app to the server from my local machine
– Built a couple reports for them in SQL Server Reporting Services using Report Builder.
– Used SnagIt to grab screenshots of the reports and app I could use in the PowerPoint deck.
– Shared the files via e-mail to the folks for them to add commentary and present out.

I can’t believe that’s really how I used to do a project like that then.  At least in 2011 I could have used Visual Studio LightSwitch and Power Pivot.  Let’s try redoing that in Office 365 and Power BI –

1.  Starting with Office 2013, Microsoft Access can be used to make line-of-business apps that you deploy to Office365 sites.  These can be accessed via the web by users to enter data that’s saved in a SQL Azure database that Microsoft provides (as long as you have no more than 1 GB of stored data).

So I can open my team site in Office 365 and select Site Contents –
image

Then I choose Access App, and enter the title of the app I want to build.
image
I’m then greeted with a webpage that says I’ve successfully created the app.  I can start adding tables and screens in Microsoft Access on my desktop to finish the application.
image
About 7 minutes later (no seriously, I timed it), I have created my table and form, and it is now available online, via browser, for folks to start entering data –
image
I also need to run reports.  No problem there, since if I look back in Microsoft Access on the Info tab, I am provided information around how I can connect to my database and run reports –
image
Once I’ve created the read-only connection, I can open Power BI and use the direct connect with SQL Azure functionality to connect to the database and create my dashboards and reports.  To get my connection information, I grab the details from Access for the read-only connection –
image
and then plug it into Power BI –
image
This allows me to create a report in a web browser against the dataset immediately and share it out with individuals or a group.
image
Now I can add the report to a PowerPoint slide by using some newish, built-in PowerPoint functionality (remember, I can’t use the default Snipping Tool in Windows because it isn’t in Office 365).

In Office 2013 and Office 2016 Preview, under the insert tab, I can insert a screenshot right from PowerPoint.

image

If I select Screenshot, I see all the open windows on my desktop, and I can simply select the Power BI browser window that’s open to insert the screenshot.  I then resize my screenshot so they can add commentary to the slide accordingly.image
Once I’m finished my deck, I can save my PowerPoint deck either directly to an Office 365 site, or my personal OneDrive for Business site and share the file securely via a link in my web browser to approved users only.
image

Once folks get the link I send via Outlook and Exchange, they can review the deck together if they  wanted and even make the changes in real time.

So I was able to meet the goal of only using Office 365 and Power BI to complete the project.  How much more productive was I?  The amount of time I estimate it would have taken me to complete the project previously was –

– Minimum 2 weeks (336 hours) for a VM to be provisioned for this project.
– 2 hours to setup SQL server
– 1 hour to setup IIS
– 3 hours to build and test the app
– 2 hours to deploy the app to IIS and test (this never worked right away)
– 2 hours to build and test the Reporting Services reports
– 1 hours to build the slide deck and send via e-mail

So 348 hours minimum were needed to get everything done on my side, and this is assuredly being generous with the estimate.  How long did the same thing take using Office 365 and Power BI?

– 0 hours for VM setup (not required)
– 20 minutes to build, test and deploy app
– 0 hours to setup IIS
– 0 hours to setup SQL server
– 20 minutes to build report in Power BI
– 1 hours to build the slide deck and share the link for review

So I went from needing 348 hours to needing about 2 (let’s round up).  That’s a productivity improvement of over 150x!  Just think about that – this project went from something I had to do a fair amount of additional work around just to request the VM, do all the setup and configuration, etc. to something I could have back to them for the initial data entry piece within an hour of the original request.  Plus, the entire project can be done or reported on from every mobile device.  Yeah, that wasn’t a requirement for the project originally, but it certainly would be now, and that’s all included with no additional development work required on my part.

I really enjoyed doing this first post in this series – it’s easy to forget just how powerful the tools like Office 365 and Power BI are (I know I often take this stuff for granted) and how much value they bring to our customers.    I’ll do more of these now and again that I sprinkle in between my normal Datazen posts, which I know my dozens of loyal readers eagerly await each week.

Thanks for reading!

How to set default selection values for Datazen HTML5 reports

Here’s a tip you can use for Datazen reports you view in a web browser.  As you probably know, you can drill thru from one dashboard to another in Datazen.  But did you know you can pass dashboard parameters through the URL?

Here’s an example of what you can do –

On the public Datazen dashboard site, you have the “Media Sales By Category” dashboard.

image

When you open it, you see the URL looks like this –

http://demos.datazen.com/viewer/publicdashboard?dashboardguid=6c4900f6-e833-4b99-92df-beba47b84fbf&v=635767662624137089&bg=ffffff&fg=999999

You can add the parameter for the category dropdown at the end of this web address.  It’s easy to find what the syntax would be – it’s available in the “Custom URL Parameters” screen in the Datazen Publisher, which pops up when you want to create a drillthrough to a custom URL.

image

In this case, the parameter syntax is – SelectionList01.SelectedItem, so my URL would look like this if I wanted to have the dropdown list set to “Print” –

http://demos.datazen.com/viewer/publicdashboard?dashboardguid=6c4900f6-e833-4b99-92df-beba47b84fbf&v=635767656921122468&bg=ffffff&
fg=999999&SelectionList01.SelectedItem=Print

Putting that URL in my browser gives me the following screen –
image
You aren’t limited to one parameter, either.  You could set it for as many as you have available in your dashboard, so it could be as detailed as this URL, for example –
http:/server/viewer/dashboard?v=1414428039277&apilocation=server&dashboard=Cascading%20DSG%20Dashboard%20-%20Target&group=Runtime%20and%20GE%20Issues&hub=V3%20Testing&TimeNavigator.SelectedStartTime=2014-03-01T00%3A00%3A00&TimeNavigator.SelectedEndTime=2014-11-01T00%3A00%3A00&TimeNavigator.ViewportStartTime=2014-03-04T02%3A25%3A47&TimeNavigator.ViewportEndTime=2014-10-28T21%3A34%3A12&TimeNavigator.TimeUnit=Month&SelectionList.SelectedItem=&
SelectionList.SelectedItems=&SelectionList01.
SelectedItem=&SelectionList02.SelectedItem=&SelectionList02.
SelectedItems=&SelectionList03.SelectedItem=&SelectionList03.SelectedItems=&SelectionList04.
SelectedItem=&SelectionList04.SelectedItems=

Maybe this will spark some ideas for folks – You could wrap your Datazen dashboard in an iFrame and pass parameters to the url, then maybe setup an auto-refresh of the dashboard in the frame without losing your parameter selections.

It certainly sparked that idea for me, but I want to watch SportsCenter before I fall asleep, so I’ll leave that project for another of my fellow bloggers . . .

Thanks for reading!