cfinlan

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

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

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

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.

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”

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

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!

Providing context for the shadow – Part 2 of the Powerful Productivity Series

This next scenario was one I was going to tackle at some point, but I decided to do it now because of a couple articles I read this past week. Interestingly enough, one of the articles was a blog post from Tableau, and the other was a survey done by Domo. I found them interesting because they both touched on a topic I’ve been intimately involved with for the last several years, and that’s sales reporting. I’m guessing, however, my reaction to each article was different than other folks who read them because of that.

The Tableau post told the same story that every company that sells a BI solution talks about to their customers – here’s how we use our own product to run our business. For example, Marc Reguera does a great job telling customers how the finance team at Microsoft uses the Microsoft stack to run the business. I’d have been shocked if the article was about how the sales teams at Tableau reported on their business using Power BI (although I’m guessing James Phillips would have blogged about that article as opposed to me. But I digress). The report they showed had the usual metrics you see every sales organization look at – top opportunities in the pipeline for the quarter, YoY growth rates, Achievement vs. Quota, etc. There was nothing “wrong” with the post at all – but my first reaction was immediate and made me laugh. That was because I created a report similar to this four years ago for a VP I supported at the time. I’d spent months doing the following –

– Getting security access to the right data

– Ordering a VM to host my reports

– Setting up SQL Server on the VM to host my data from these sources

– Creating SSIS jobs to cleanup and bring the data in from various .csv files

– Setting up SQL Server Reporting Services

– Building the report in Report Builder

– Deploying the report

There was no doubt in my mind I was about to be the new BI hero in town. I presented the report to him and waited for his reaction. Which was the following –

Shrug re-enactment drawing courtesy of Rob Collie

Yep, that was his reaction.

“You don’t like the report?” I asked.

His response and subsequent explanation really stuck with me. And it was partially highlighted in the survey results I called out earlier – on slide 17, some specific issues were culled from the responses. The ones I noticed right away were these –

– inaccurate

– not up to date

– unreliable

– hard to understand

When it comes to sales reporting, the vast majority of data comes out of the Customer Relationship Management, or CRM, system. This is where the sales organization enters all of the information around their customers and the sales opportunities they have with them. You might have heard of some of the companies that sell CRM systems – Microsoft, Salesforce, and SAP are three of the largest vendors. However, every CRM system has the same “flaw” – they all rely on salespeople to enter data in the system. And the quality of the information entered can vary wildly depending on the salesperson for a number of reasons.

This isn’t meant to be an blanket indictment of folks working in sales. Being a good salesperson is HARD. Having a quota you are responsible for, presenting or speaking to customers most days, making cold calls or traveling – it is a job that comes with a lot of pressure. And every time you enter something into CRM, it’s going to be immediately tracked, dissected, discussed by your manager, their manager, etc. Which means sometimes, people are very “selective” about what they put in CRM – some might think to themselves, why bring a lot of extra attention to yourself if you don’t have to? Or they’ll put the value of the opportunity much lower than it really is for similar reasons. Because of travel, perhaps they don’t update their data in the system for a few days because their too busy. Or they hate entering stuff in CRM and put it off as long as possible. Or they are told to put a bunch of opportunities in the system that they have little confidence in but need to show a “full pipeline” for management. Combine this with the usual number of data entry errors folks will make, and you have a scenario where you have a sexy dashboard that is showing a pretty picture of a lot of bad data.

Should these folks be doing these things? Of course not. But the point my manager made was that since this did happen, he needed a solution that could account for this “shadow pipeline” as well as what came out of CRM so he could make a proper forecast of where the quarter would end up. And he wanted it structured the way they had it currently in Excel, which looked like similar to this overly simplistic re-creation –

Why couldn’t he just force his team do it properly in CRM, you might ask? Because they were a global team that worked with the regional sales folks, but didn’t directly enter any of the data. This is hardly unusual – most, if not all, customers I talked to had many, many groups leveraging this data that weren’t entering it directly. So they needed a way to include this information in the report.

At first glance, this seemed like an easy enough request to add. I’d follow a similar pattern to what I did in the first post in this series and build a form where his direct reports could enter their shadow pipe, then show it in the format he wanted in the SQL Server Reporting Services report I’d built. So I went back and did that (which took several hours of work) and re-presented it to him. Again with the shrug.

“What’s the problem now?” I asked.

“I can’t change the shadow pipe values,” he said. “I need to be able to do that on the call.” He was referring to the regular team call this report was used on.

“Well, I can show you how to change them, but I don’t understand. Why do you want to change values on the call they just typed in for that call the day before?”

In hindsight, this was a really stupid question. He could have said, “Because I said so, dumbass.” But he decided to explain anyways.

“There could be a number of reasons. First off, a lot of stuff can happen around here in a day, so there could be an update that needs to be reflected. Second, I just hired three new people. They don’t know the accounts real well and might change their forecast when we talk about it. Third . . . ”

“Okay, I get it. But you can change the values – just open the app I built, change the dropdowns, find the value to change, hit save, re . . .”

That was not what he wanted to hear.

“I’m not doing all that, Chris. I want to change it like I do now, where it takes a second and all the calculations update automatically. And I also want a way where I can add notes on the call that I can use when I share this information up the chain. You should never give an executive a sales report without context. Otherwise you’re just creating a bunch of additional work. They’ll always want to know why the numbers are what they are, what are we doing to help the teams drive more revenue, do we need to re-allocate funds around . . .”

He was absolutely right – I’d seen that scenario play out time after time. So I went back and took another crack at it using Excel and this new add-in Microsoft had released called Power Pivot. However, the way the data needed to be laid out meant people would have to enter data in “holes” in the middle of a pivot table. There were also a bunch of subtotals in the report currently where I needed to add up data from both the entered data in Excel and the summarized data from SQL Server that also sat in locations in the current report I couldn’t see how to do in Excel. It also didn’t address the issue of having a central place for folks to go and enter the data because we didn’t have SharePoint setup (this project spurred me to do this for the team on my own, actually).

Eventually, the solution I provided him was a completely custom solution in Visual Studio that took probably three weeks to build, and countless additional hours to tweak over the time I was supporting them using it. The good news was, he was happy with this and it led to a number of great projects I worked with them on. The bad news was, making changes was something only I could do, so it died as soon as I left and they needed to make a change. Plus, I never felt comfortable I’d found the best solution.

What if I had the opportunity to do it over again now? Well, I’m not waiting any longer – let’s redo this baby right now using Office 365 and Power BI and what we can come up with.

I’m going to run through the entire scenario in my head as best as I remember how this used to work, and map it to the functionality in Office 365 or Power BI in each step –

– A meeting invite was created and sent to the participants via e-mail (Outlook and Exchange)

– Data needed to be cleaned up and combined from CRM and another data source that has the quarterly sales targets stored (Power Pivot/Power Query in Excel 2013).

– As opposed to building a custom app, I’ll create the report using Excel as the front-end and taking advantage of Cube Formulas.

If I had simply spent more time reading Rob Collie’s blog post from January 2010 (!), I would have seen I could have provided my manager with the exact functionality he was looking for by leveraging cube formulas. For the people who’ve never used them before, in essence you have the ability in Excel to blow up a pivot table and layout the cells however you want while still being linked to the data model dynamically. Rob spends more time talking about cube formulas than I do about Datazen (well, close anyways), so I’d strongly urge you to read his blog to learn more, but here’s how I’d do it for this project –

1. From the Power Pivot window in my Excel 2013/2016 Preview workbook, I’ll create a new PivotTable (or I could create it on the Insert tab in Excel. It doesn’t matter) –

2. Add to the pivot table the results I need for my report from the CRM system. I added a slicer as well just to make sure everything is still connected to the live data source after I convert the data –

3. Under PivotTable tools, select OLAP Tools, and Convert to Formulas in the dropdown –

You’ll see this has converted the cells to individual formulas in the formula bar that looks something like this, depending on which cell you have highlighted –

4. Cut and paste the last row of values down one from it’s current location so your report looks like this –

5. If I click the slicer, my values update to the proper results

I now can lay out the report any way I need to, allowing data entry in certain fields, add formulas that properly sum the fields that are entered and tied to the data source, add an area for comments to be entered for each row, etc. It would have met the requirements he had for his frontend experience. Single tear . . .

– Central place to collect shadow pipeline and notes from users for the report (OneDrive for Business Access Web App)

As I thought about how I’d handle this piece, I initially considered the idea of just saving my report to OneDrive for Business, sharing a link with the users and allowing them to enter the data in the shadow pipeline field accordingly since the report was small enough it could be used with Excel Online. He could simply overwrite that on the call with them, or add additional notes and call it a day. There were a couple issues there I had concerns about –

1. Anyone could potentially overwrite the fields that contained cube formulas when editing in the browser. It wouldn’t be the end of the world, since I’d have multiple backups of the report, but still wouldn’t be ideal.

2. Instead, I looked at adding protection to the sheet while leaving certain cells unlocked and available for editing. Problem was, this only would work when folks opened it in Excel locally.

For the record, if I had offered this second idea to my manager on how to handle this, he would have said that was fine and not to overthink it. I know this because we ended up doing something similar for a different project once I got SharePoint stood up.

A better answer in my mind would be to let his team enter the value and notes via an Access app in Office 365 like I showed in the last post, but give him a field where he could enter a value as well, and supersede any entry folks made in the calculations if he entered a value. Why bother doing this extra work if I know he would have accepted the previous option as a solution? Because I know he’ll want to capture these values in a way that he could see historically what people were forecasting and how accurate they were vs. the actual sales amount (I know because he did ask for it later on, by the way). My proposed solution would allow him to do just that, and then he’s the only one ever updating data directly in the report.

– Share the report with my manager and his team (OneDrive for Business and Power BI)

By loading the report to OneDrive for Business and then accessing it through Power BI, I get the best of both worlds. I now have a way to share a link to the Excel document with my manager so he could view and edit it, provide a link to anyone else so they can view it only, and leverage the Power BI personal gateway to refresh the data on a schedule basis.

This is pretty easy to do. I can save the file right to OneDrive for Business from Excel directly. I just go to File –> Save As, then add an Office 365 SharePoint location

Enter my credentials and my OneDrive for Business information is added.

Once I save it there, I flip to my Power BI area and add data from my OneDrive for Business folder

then choose my Excel file I want to connect to. I want to see and use the file just like I would in Excel Online, so I choose that

Then I can view or edit it as an Excel document AND leverage scheduled refresh. No conversion to a new Power BI report necessary.

– Run the online meeting for the global team where they review the report and talk about their part of the business (Skype for Business)

There, I was able to redo everything using Power BI and Office 365 and meet his specific requests asks around being able to provide context for the shadow pipeline figures that were captured as part of this process. I wonder if I could connect the data in that Excel sheet to the PowerPoint presentation he wants to give at the end of each quarter. I bet I can . . .

Hmm – I’ll save that for another post in this series down the line. So remember, the next time you’re asked to build a sales dashboard or report, see if can you provide context to the shadow with it. Because if your solution doesn’t have that level of flexibility, people looking at your report might be “in the dark” when it comes to having the right answers (Yes! I was looking for a terrible pun to end this post with, and I succeeded. In your face, good storytellers.)

Until next time!

You can take that to the bank

This blog post has nothing to do with Power BI, Datazen, Microsoft, etc. Instead, I have a story for you – one that reminded me how important good customer service really is.

To say I’ve been having issues with my regular bank since I moved to the west coast would be a grotesque understatement. I’ve had issues with my debit card, my credit card, and they also managed to screw up a bank check I had printed from them. These series of incidents have required repeated calls to customer service, and each time I’ve called, I’ve been told “I’m sorry” by each representative I’ve dealt with, which doesn’t make me feel any better. Why? Because it’s never followed up by actually addressing the underlying issue. My son used to watch a show called “Daniel Tiger’s Neighborhood”, which I always enjoyed because it included a nice lesson for the kids just like the show it sprouted from, Mister Rogers Neighborhood. In one of the episodes, Daniel learns that simply saying I’m sorry isn’t enough. Instead, he learns it’s important to do what you can to help fix the problem/issue. So he always says “I’m sorry. How can I help?” and then follows through on the ask by actually doing what he can to help rectify the situation.

Today, once again, an issue with my current bank arose, and this was after they claimed twice before it had been fixed. I get on the phone with customer service – I speak with three people, who all again apologize, but then simply shuffle me off the phone to another person. At one point, the original person who made the error is brought on the phone. She explains they’re confused why this is still an issue, then offers to call me back Monday (!). In lieu of that, maybe they could speak to the other party who is affected by this screw-up on their part (like he cares at this point whose fault is). I explained again how this was not something that could wait, I was told the manager could fix this, etc. She says she’ll look at getting it fixed today and give me a call back. You can guess whether or not this actually happened.

I spend the next hour or so writing a letter to complain and send, which is cathartic, but hardly moves things along. Not wanting to have this other guy affected by this nonsense any longer, I head to a local branch of his bank, Wells Fargo, to make a deposit in his account directly. Wells Fargo was my mortgage company these last few years. I can’t say I liked or disliked them – they were just a place I mailed checks to once or twice a month. And since I didn’t have a checking or savings account there, I wasn’t sure I could just deposit money into someone else’s account. But I needed some exercise, so I decided to walk there with my family and hope for the best. It was a bit of a hike, so I was pretty sweaty and disheveled by the time I arrived.

“Hi there – how can I help you?” the teller asked.

“Can I make a deposit in someone’s account if I don’t have an account here?” I asked.

“Sure.” She then explains what information I need to have, what of type of payments they take, etc.

“Alright, I’ll go get cash.”

“Sounds good.”

I walk outside to get cash from the ATM – naturally, another issue with the card from my bank prevented me from getting out the full amount. I trudge back inside, resigned to having to do this again the next day.

“All set?” she asks.

“Not really,” I sigh as I start counting out some bills. “But it’s fine. It’s not your fault.”

“I’m sorry to hear that. What can I do to help?”

I was so surprised by her question I stopped counting and looked up at her. I mean, I just told her it wasn’t her fault. Why is she offering to help?

“No, it’s just – I’ve had some issues with my bank trying to get this guy his money.” And I proceed to give the 30 second explanation of the whole sordid tale and how I’ll need to do this again the next day.

“Wow,” she said. “You have every right to be frustrated. I’m really sorry about that. Here, why don’t I see if I can’t make sure you don’t have to come back tomorrow?”

“Really? How?”

“Well, there’s a couple ways we could take care of it.”

And she proceeds to explain my options, make sure the account number and name match what I have, process the transaction and explain what she’s doing each at step, provide me final receipts, etc. It was clear that if there was some way, no matter how small, she could meet my needs and make my day a bit better after spending time at her branch, she was going to do it.

“Thanks so much for coming in today, Mr. Finlan. Is there anything else I can do to help you before you go?”

“No, this was great. Thanks so much”

“My pleasure, Mr. Finlan. Again, I’m so sorry about the day you’ve had. If you ever need anything else, please let us know.”

I left and went across the parking lot to find my wife and kids, who had made the trip with me and were eating some lunch.

“How’d it go?” she asked.

I told her about my experience.

“I think we should sign up for an account there,” she said.

“Yes, we should.”

This woman had no reason to go the extra mile, or dig into the problem a bit more, especially considering I wasn’t an account holder. And she didn’t fix the underlying problem – she couldn’t. But she did make it clear, and proved through her words and actions, that she would do whatever she could to make the experience that she could control as smooth and pleasant as possible. That’s all you can ever ask a person in a customer-facing position to do, really. She unknowingly earned my banking business for her company. Or maybe she figured she’d only go the extra mile for the guy who wandered in looking like Hobo Joe.

Sometimes it’s important to be reminded about how every interaction can potentially have an impact on your business, no matter how inconsequential it may seem at the time.

With that, I’ll put my soapbox away and return to my regular scheduled blogpost early next week. Have a great weekend!

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

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 –

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. –

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 –

Then I choose Access App, and enter the title of the app I want to build.

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.

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 –

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 –

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 –

and then plug it into Power BI –

This allows me to create a report in a web browser against the dataset immediately and share it out with individuals or a group.

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.

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.
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.

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.

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.

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 –

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!

Overview of Datazen Parameter Replacement Functions

Wanted to highlight a new Technet article by my colleague Christopher Moncayo about some Datazen specific functions for parameter replacement. These can be helpful when you’re writing new queries for your data sources and need to simplify things a bit. The article can be read here –

http://social.technet.microsoft.com/wiki/contents/articles/32028.datazen-data-view-parameter-replacement-functions.aspx

Thanks to Chris for putting this together!

How to insert a live Datazen dashboard into a PowerPoint slide

This is one of these posts where I’m reverting to my old “Radar O’Reilly” mode of having to simply figure out a way to get something done, by hook or crook. That was often the missive I was given from the business – didn’t care how I got it done, just needed it done, as long as it wouldn’t get anyone arrested. So with that in mind, I wanted to see if there was a simple way I could enable this (without getting arrested).

What made the most sense to me was to insert a web page into a PowerPoint slide and take advantage of Datazen’s public access support. Since PowerPoint doesn’t support embedding a web page out of the box, I was going to install an add-in called LiveWeb, which allows you to do just that. However, and this is important, Microsoft doesn’t recommend you do this for security reasons. Microsoft gives you a workaround for this, but let’s see if we can’t find a better way (since I’m not looking to provide advice counter to that of my employer).

My next thought was – why not check the Office Apps Store to see if we have an official add-in? If you haven’t used the Office App Store before, it’s an easy way to find apps, both free and paid, for your Office365 programs to use as add-ins. To start using them in your next presentation –

1. Go to the Insert Tab in PowerPoint. You should see the Store Add-In in the ribbon. Click it to open the App Store –

2. A pop-up will open with the apps available in the store. You can now run a search for the app you wish to use

Since I wanted to use a web viewer add-in for PowerPoint, I typed Web Viewer into the search bar and hit Enter. And what do you know? Microsoft has created an app to do exactly what I wanted to do. And it’s free!

I added the app to my slide and then entered the Datazen dashboard address I wanted to include. Since it’s configured for public access, I wasn’t prompted for my user credentials and it was added and worked.

And since it is treated like any other slide element, I was able to redo my slide accordingly. Ta da – I’m able to add proper commentary and still have a fully interactive, live dashboard!

This wasn’t too tough after all, and I didn’t mess around with my registry to get it to work. Success!

Thanks for reading, and enjoy the rest of your weekend.

How to connect to Teradata from Datazen

Greetings, folks! Here’s another short and sweet post around connecting to a popular data source, Teradata. This is even easier to do than the data connections outlined in the previous posts, since you can use the native ODBC connection that comes with Datazen out of the box.

To use Teradata with Datazen, follow these steps –

1. You need to download the Teradata ODBC driver here (you’ll need to register for an account). I’d suggest you download the Teradata Tools and Utilities (or TTU for short) so you can test things if you run into issues, but you can just download the ODBC driver and plow ahead if you’re a gambling man. You’ll need the 64-bit version of the driver.

2. Once you’ve installed the driver on the Datazen server, setup a new ODBC connection. You’ll need to save the username/password in the connection, and leave everything else as the default settings or if you want to set a default database in the connection, that’s fine as well.

Excuse the scribbling to hide the IP address, trying to use Paint on an airplane with your touchpad is not a great experience.

3. From the control panel in Datazen, add a new data connection and select the ‘Generic ODBC DSN’ provider. Enter a new connection name and the ODBC DSN and hit ‘Test Connection’. You should see a message saying you’re successful –

See? Easy peasy. Have a great week, folks!

Use a Powershell Script to Restore Datazen Server

Happy Wednesday, everybody. This post is short and sweet, but potentially a big time saver for you. So you need to backup and restore your Datazen server for whatever reason. There’s instructions in the documentation on how to do this, but the process of restoring the server can be automated even further using Powershell.

I’ve included a download link to a Powershell script that automates the restoration process of Datazen. While you’ll still need to setup and run the backup through the control panel as either a one-time event or on a scheduled basis, this script will do the following –

– Stop All Datazen Services
– Delete the Data Folder
– Restore the backup using Raven
– Defrag the database
– Run Datazen.Server in console mode to sanitize the installation
– Start the services again

Note (thanks, Rui!) – Make sure the service account that is running the core service has access to the new data folder, whether by updating the script or confirming access is available manually. If it doesn’t, the core service will hang when it starts.

The biggest thing to remember when doing this process is that you are using the correct Datazen Instance ID that you want to restore. This process assumes your current server and your backup are using the same instance id. If, however, you need to restore or move the Datazen server to a new server, and restore an existing db there, you’ll need to reinstall Datazen using the command line and specify the instance id. That process is outlined here.

As promised, here’s the Powershell script you can use – http://1drv.ms/1TsbWZE

Thanks for reading!

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this:

Share this: