How to insert a live Datazen dashboard into a PowerPoint slide

image

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 –
OfficeSnip
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
image

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!

image

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

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!

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

Using Datazen with Plus One to visualize your social media data

image

The idea for this post came from having spent the last year or so playing with a app called Plus One.  Check out their site to learn much more about it, but in short, it’s an app you download onto your desktop, enter the keywords you want to search across different social networks (Twitter, Facebook, etc.), and get back the results.    And in the FAQ, it states – “You are not limited to just the reports found within Plus One Social.  Because we store the data in a friendly desktop database, Microsoft Access, you can connect your favorite self serve bi tool . . .

Naturally, I wanted to see if Datazen would work with Plus One, which meant I had to do a few things I hadn’t done before to get started –

– Install the Plus One application on Windows Server and
– Use Microsoft Access as a data provider

Installing Plus One is pretty straightforward – you go to this link, enter in your information, and they’ll send you the download link.  Download and install the app on the Windows Server you’re running Datazen on, and the Plus One icon will appear on the desktop.

image

This next part is tricky – BEFORE you try to open the app, you have to install two Access ACE ODBC drivers.  If you try to do it before that, you’ll get an error message.  The problem is, Datazen requires the 64-bit ODBC driver, while Plus One requires the 32-bit driver.  So how do we work around this?

1. Follow the instructions in this blog post to install the driver and setup the data source that Datazen needs.  However, use this data provider schema file as opposed to the one in the post because you need to leverage Access as your data source, not Excel.

2. Once that’s done, install the 32-bit Access Driver from here.  It’s from the 2007 version of Office, so there’s only one item to download and install.  That should allow you to open Plus One Social to get started.

Open the app now, and you’ll see the search bar where you can enter your search terms.  I’m going to do a search for Twitter data related to Datazen (surprise, surprise), so I type in datazen.
image

I’m asked to authorize the app on my Twitter Account (you need accounts with the different social networks you want to search across)image

I authorize the app, and it grabs the results from Twitter.  It’ll then go and grab the last seven days of tweets (Plus One is limited to one week of data, so you’ll need to save it on a rolling basis to keep older data), and I can even setup an auto-refresh to get the latest data.
image
Now that I’ve got my Twitter data, I want to view it in Datazen.  The default Access database sits in the following folder location on the machine –

C:\Users\UserName\Documents\Plus One Social\PlusOneDB.accdb

If you were to open the file in MS Access, you’d see five tables with data – Hashtags, Mentions, Messages, Queries, and Users.  You can now write SQL queries against those tables in the Datazen server to pull back the data you need for your dashboard(s).  Let’s do one together.

I’ve got the Microsoft Access data provider all setup
image

I want to see what platform people are using on my dashboard, whether it’s iOS, Android or something else.  So I’m going to write a simple case statement query to bring back the results.  Something like this –

SELECT Source = CASE WHEN Messages.Source like ‘%iPad%’ THEN ‘iOS’ When Messages.Source like ‘%iPhone%’ THEN ‘iOS’ WHEN Messages.Source like ‘%Android%’ THEN ‘Android’ ELSE ‘Other’ END,
Count(Messages.MessageID) AS CountOfMessageID
FROM Messages
Group By CASE WHEN Messages.Source like ‘%iPad%’ THEN ‘iOS’ When Messages.Source like ‘%iPhone%’ THEN ‘iOS’ WHEN Messages.Source like ‘%Android%’ THEN ‘Android’ ELSE ‘Other’ END

Except I get an error –
image

The issue here is you have to write the SQL Query like you would in Access, when doesn’t use case statements.  It does use switch statements, so I try that instead –

SELECT Switch (Messages.Source like ‘%iPad%’, ‘iOS’, Messages.Source like ‘%iPhone%’, ‘iOS’, Messages.Source like ‘%Android%’, ‘Android’,True, ‘Other’ ) as Source, Count(Messages.MessageID) AS CountOfMessageID
FROM Messages
GROUP BY Switch (Messages.Source like ‘%iPad%’, ‘iOS’, Messages.Source like ‘%iPhone%’, ‘iOS’, Messages.Source like ‘%Android%’, ‘Android’, True, ‘Other’ )

image
And that works without issue.  I went ahead and created seven different queries in total –
image
and set them up to refresh every hour, making sure I set it to run 10 minutes after I know my refresh on Plus One will finish.
image
With that all setup, it was easy to create my dashboard (thanks to Datazen) and always stay on top of our social media activity.  This was the end result –
image

Thanks for reading!

How to setup your Datazen server to leverage Office365 for e-mail notifications

Happy Saturday everyone!

As promised, here is a quick follow-up to my post last week on how to setup a Datazen Test/Dev server in Azure.  As you’ll remember, I had you skip over the part where you setup you e-mail provider during the install wizard.  However, it’s pretty painless to now setup that information on your Datazen Server.  Couple quick caveats –

1. I’m admittedly showing you a method that is a little “hacktastic”, so I’d recommend you setup a dummy e-mail account to use for this purpose as opposed to simply leveraging your everyday Office365 account.

2. This is probably only necessary if you’re using the default Datazen authentication for your Datazen instance and don’t have an SMTP server in your organization you can leverage instead.

With that out of the way, let’s get started –

First, you’ll need your Office365 settings for SMTP e-mail.  You can find that by logging into your Outlook account on Office365 and going to this link.  You can also go up to the upper right-hand corner of the window and click on the gear icon, and then select Options

image

You can then look under Accounts –> POP and IMAP on the lefthand menu to find the SMTP settings

image

Once you have that information, open a Remote Desktop connection to the Datazen server you setup in Azure.  Once opened, navigate to the following path –

C:\Program Files\Datazen Enterprise Server\controlpanel\

You should see a web.config file.  MAKE A BACKUP copy of the file first just in case and save it somewhere safe.

image

Open the file in notepad and scroll all the way to the bottom until you find the mail settings.  It should look like this assuming you followed the steps I laid out previously.

image

To change the settings to leverage your Office365 account to send e-mails, you just need to change them to the following in between the tags –

Then save the file.  To test it, login to the control panel and add a new user to the server (this was all covered in the previous blogpost if you need a reference).  Once you’ve added him/her, you’ll see them listed with a “Set Password Link” next to their name –

image

You can now click on the “Send Reminder” link to send the e-mail to your user directly.  Assuming it was successfully setup in the web.config file, you should see a green success message just below the button.

image

And the user should get an e-mail in their inbox with an invitation to join the dashboard hub which looks like so.

image

They can then setup their account by entering their password after they click the link they receive.

image

That’s it!  You’ve now successfully setup your Office365 account to act as the mail server for your Datazen instance. 

I’ll be doing another blogpost in the next few days on how to setup a custom data provider and connect to a SAP HANA instance from your Datazen server.  Until then!