Gaps between PowerBI streaming tiles and SharePoint

So I spend an evening playing (I actually have a lot of fun exploring these things) and figuring out how the pieces of SharePoint, PowerBI and Flow are supposed to work together.

In my head - they already connect.  But I have never seen anyone blog them.  So I decided to give it a stab.

Turns out there are some gaps.

The Idea

The Idea is simple.  We can create a PowerBI that uses SharePoint List as a datasource.  But instead of configuring scheduled refresh, we want to use the PowerBI Rest Dataset to push data in a streaming way.  And since Microsoft Flow has an action to do this, as well as the triggers to listen to SharePoint List.  We can get SP List-push-to PowerBI without needing schedule refresh.  That is a crazy fun idea.

The Reality

The reality is that there are several gaps.  These are probably solvable, but I just want to list them first, and we'll tackle them in the future.

Gap 1.  SharePoint List dataset != Push-enabled REST dataset

PowerBI makes a distinction between what's a REST/Pushable Dataset vs normal datasets like external lists.  In fact, Flow can not connect to a non-REST dataset.

So we need to create a REST dataset in PowerBI Service (it is not a feature of PowerBI Desktop), and then use the REST dataset as a live connection in a PowerBI Report.

Gap 2.  The only way to create a PowerBI REST dataset is via the REST API. 

There is no UI.  Ouch.  That pretty much makes this a developer task.  OK that's fine, we create a REST dataset via REST endpoint and a JSON schema (double ouch).  

Now we can build our PowerBI report, connect the REST dataset from PowerBI Service.  We save and publish this report to PowerBI Service and then insert the PowerBI Report in an SPFx webpart (PRO license needed for embed) into a SharePoint modern page.

This part is actually really seamless.  Don't worry, we have more gaps.

Gap 3. PowerBI report does not livestream REST dataset results.  

So I'm staring at my PowerBI visual in a SharePoint modern page.  In a separate window, I update the source SharePoint List.  In yet another separate window, I can see the Flow ran and push the new list item into the streaming dataset.

Excellent.  Except, the SPFx PowerBI Report Visual isn't updating.  It doesn't update.  I waited 15mins for it to do nothing!

If I F5, then I immediately see the new value.  But it doesn't do live streaming refresh :-(

It turns out, to see live stream results we need a PowerBI Dashboard or PowerBI Tile (streaming tile).

PowerBI Dashboard can only be created in the PowerBI Service.  We take an existing report and pin the visual.  This asks us to add the visual as a tile in a PowerBI Dashboard.

Gap 4. SPFx PowerBI report webpart does not show PowerBI dashboard embed.

So I create a PowerBI Dashboard and I go back to the SPFx PowerBI Preview webpart.  Only to find it doesn't do dashboard embed.

It only does Report embed.  So we will need to build our own SPFx that let us do dashboard embed.  This requires a embed token from MSGraph - but we should be able to piggyback the graph-util helper in SPFx to do our token exchange.

There's potentially one more issue.

Gap 5.  Does embed PowerBI Dashboard or Tile actually connect to streaming datasets?

I don't know the answer to this yet.

Gap 6. PowerBI REST Dataset endpoint can only add rows, not update them

The REST API lets us add rows to a REST dataset easily, or clear the table.  But there's no way to update an existing row.

The use case for the streaming REST dataset is like a ongoing stock ticker or temperature meter.  You don't update a record that's streamed past.  You only care about new records.

Flow only has an action to add row to PowerBI Dataset.

Gap 7. Flow does not have an action to Clear the dataset

The REST API lets us clear the dataset table, so technically, I could clear the table each time and repopulate it with the entire list again.

But unfortunately, Flow only has one PowerBI Action - add row to a REST dataset.  It does not have a clear rows action.

More work to do, more exploration to be had

Parts of the puzzle works really well.  Flow pushes data freely from SharePoint list changes into the streaming dataset.  If the dashboard or tile is shown on a webpage by itself, we immediately see it update like magic.

But if we want the dashboard/tile embedded within a SharePoint modern page.  There's still work to be done.

Ultimately, if we want live streaming data capability, it might be easier to use PowerBI LiveQuery against Azure SQL, and have Flow push data into that, instead of PowerBI REST streaming Dataset.

Working with SharePoint WebHooks with JavaScript using an Azure Function

This blog post covers additional explanation on how to subscribe to SharePoint WebHooks and have it running with only JavaScript in Azure Functions.  The entire code is in one single JavaScript file.

The SharePoint team delivered on the promise to ship SharePoint WebHooks, and made an reference example in C#.  Be sure to watch the PnP webcast as well.

What is Azure Functions?

Azure Functions, in the simplest sense, is a Azure WebJob that lets you run a JavaScript function in a file (it can do C# too), and it will run it for you when you trigger it.  Because it is a Serverless platform - you don't pay for the WebJob unless your function is running.  This makes it really economical (you also get like a million free runs per month...)

Azure Function comes with a super user-friendly UI and you can paste or upload your JavaScript directly in the browser.  You don't need any tools installed.

View fullsize

Running in the browser

Of course, this is server-JavaScript.  So think NodeJS.  We can talk to lots of REST APIs (Graph, SPO) but there will be no browser DOM.

What can you do with this?

You can trigger it on a timer.   Hey that sounds like a SharePoint Timer Job.

You can trigger it on a web request.  This is super useful if you have a client-side UX and you need a button to do some high level elevated permission action.  You call the Azure Function to do it for you, using an App-Only permission elevation.

Why is SharePoint WebHooks important?

A SharePoint WebHook is a REST endpoint that you can attach a remote End Point to.  Right now, there is only a List endpoint.  So any updates to the list items will trigger an event, and SharePoint will call your function.

Hey.  Wait that sounds like a SharePoint Remote Event Receiver.  You are right!

Design

So the idea of our function is this - when triggered:

It will Auth and then talk to SharePoint REST and do one of the following.

• Check if it has a request parameter "subs" - then it will list the current subscriptions on our target list
• Check if it has a request parameter "sub" - it will try to attach itself to the target list
  SharePoint will immediately call the function with a validationtoken parameter so…
• Check if the request has a validationtoken parameter - it will immediately reply with that token as text/plain.
• Skipping all three conditions, it will run the default action
  The default action is that it will add an list item in a different destination list.  Because the function is running on its own App-Only permission, it can update a list that the original user doesn't have access to.

GET Subscriptions

options = {
    method: 'GET',
    uri: "https://johnliu365.sharepoint.com/_api/web/lists/getbytitle('subscribe-this')/subscriptions",
    headers: headers
};
request(options, function (error, res, body) {
    context.log(error);
    context.log(body);
    context.res = { body: body || '' };
    context.done();
});

View fullsize

GET subscriptions.  Array result is [] empty by default.  It has subscriptions after you attach hooks successfully.

POST Subscription (to add itself)

options = {
    method: 'POST',
    uri: "https://johnliu365.sharepoint.com/_api/web/lists/getbytitle('subscribe-this')/subscriptions",
    body: JSON.stringify({
        "resource": "https://johnliu365.sharepoint.com/_api/web/lists/getbytitle('subscribe-this')",
        "notificationUrl": "https://johnno-funks.azurewebsites.net/api/poke-spo2?code=q8sq9wxm62asd-YOURTRIGGERCODE",
        "expirationDateTime": "2017-01-01T16:17:57+00:00",
        "clientState": "jonnofunks"
    }),
    headers: headers
};
request(options, function (error, res, body) {
    context.log(error);
    context.log(body);
    context.res = { body: body || '' };
    context.done();
});

View fullsize

Sending POST subscription without handling validation token.  The request fails.

Handle Validation Token

// if validationtoken is specified in query
// immediately return token as text/plain
context.log(req.query);
context.log(req.query.validationtoken);
context.res = { "content-type": "text/plain", body: req.query.validationtoken };
context.done();

View fullsize

The function is called twice.  Second time by SharePoint to validate the subscription.

Result Video

The Poked list item was created by "SharePoint App" not "John Liu" the user.

Notes

This demo builds on top of the code from Azure Functions, JS and App-Only Updates to SharePoint Online that covers authentication with certificate, and running AppOnly permissions. 

Additionally, I've moved ClientID and Certifate ThumbPrint to Azure Function App Settings.  This means they are no longer part of the code.

App Settings

var clientId = process.env['MyClientId'];
var thumbprint = process.env['MyThumbPrint'];

Function App Settings > Configure App Settings

#AzureFunctions tip of the day: Store secrets/settings in App Settings, not your code! <⚡> pic.twitter.com/GrpwX1mLpe

— Christopher Anderson (@crandycodes) September 9, 2016

Source Code

I'm making an effort to put all my demo source code on GitHub going forward.  This is part of the "Upgrade Your JS" demos.

https://github.com/johnnliu/demo-upgrade-your-js/tree/master/azure-function-web-hook

Let me know what you think about SharePoint WebHooks, Azure Functions and JavaScript that will rule everything ;-)

If you spot a bug or want to update the code - send me a Pull Request.

All Demo Downloads will be on Github - blog housekeeping

I've taken a short break from writing blog posts - I haven't been idle, I have been writing something.  Hopefully to be able to share it with everyone soon.  Anticipation is killing me.

As we roll into a new month September!  There are a few planned updates I'm doing for the rest of the year...

1. Several old blog posts that was in Draft will be merged and published.  These are summary posts from the Office 365 Saturday events I've been going to.
2. I've been in several SharePoint Sydney user group sessions and that needs summaries too.
3. SPFx is announced, and now Developer Preview.  Posts there too.
4. I'm looking around to see what's the best way to record some video sessions as I retire them to the archives.

First big announcement.

All future demo downloads will be on Github!

The download files for my demos on Upskill Your Javascript - from building JS WebParts for SharePoint to Office Add-ins and Azure Functions is up first.

https://github.com/johnnliu/demo-upgrade-your-js

The main driver for this is that the files are updated overtime, and Github really provides a much better place for me to point people to and say the latest files are over here.  Check it out, and if you have Issues - tag them directly on the lines.

So that's the first of the big news.

Seems obvious now...

Update on SharePoint and Office Development - 2016 Feb edition

We rotate through different topics and presenters in the Sydney SharePoint user group.  But I finally got my turn to present a developer topic, and I wanted to do a quick primer on all things Office Developer related.

This turned out to be a REALLY complex talk.  I wanted to cover ALL the cool new stuff in Office Dev.  But as it turns out every month (and now, every week) new things come out. 

I ended up with:

• Brief Introduction to dev.office.com
• Brief Introduction to PnP
• Where we have been with SharePoint Add-Ins
• Where we are going with Office Addins: 
• NEW API: Microsoft Graph API
• NEW AUTH: OAuth 2.0 and ADAL(js)
• NEW TOOLS: New tools with Node, NPM, and Yeoman Generator (YO OFFICE)
• Build a demo Office Addin that talks to both SharePoint Online and Microsoft Graph
• Run on web and desktop

The presentation clocked in at 1 hour and 20mins - I started a bit earlier.  And kind of flopped off at the end as I run out of steam after the demo :-O

Then we gave out all the swag from dev.office.com so all is happy faces.

The PowerPoint presentation is here:

2015 February Presentation - Update on SharePoint and Office Development

Demo Fail

So at the end, the Office Add-In didn't load from the App Catalog on my desktop.  I went home and got it to work, here are screenshots to proof it.

View fullsize

Oh NO - no apps

View fullsize

Add SharePoint App catalog to trust center

View fullsize

View fullsize

View fullsize

Here it is.  The same addin working on desktop - talking to Microsoft Graph and showing Group Conversations

Azure Logic Apps: Build SharePoint Workflows by clicking buttons: a picture guide

TOC: Azure Logic Apps

• Build SharePoint Workflows by clicking buttons [This article]
  • Introduction
  • SharePoint Online
  • Office 365
  • Connect them all
• Hybrid Workflows - SharePoint On-Premise
• "Code", Template Language Expression
• Observations
• Social
• XML

Introduction - What are Azure Logic Apps

Microsoft announced a series of Azure App Services today:

http://weblogs.asp.net/scottgu/announcing-the-new-azure-app-service

Specifically, I want to focus on Azure Logic Apps.

http://azure.microsoft.com/en-us/documentation/articles/app-service-logic-what-are-logic-apps/

In Microsoft's words.

Azure App Service is a fully managed Platform as a Service (PaaS) offering ... allow any technical user or developer to automate business process execution via an easy to use visual designer.

Best of all, Logic Apps can be combined with API apps and Connectors from our Marketplace to help solve even tricky integration scenarios with ease.

Microsoft has a tutorial on how to create Azure Logic Apps:

http://azure.microsoft.com/en-us/documentation/articles/app-service-logic-create-a-logic-app/

http://channel9.msdn.com/Shows/Azure-Friday/Azure-App-Service-Logic-Apps-with-Josh-Twist

You should go through these first.  There are a number of new Azure templates that are wordy to describe, but a video will show how it all works together fairly quickly.

So I only want to focus on the SharePoint Online Connectors.  They are easy to set up, but actually, tricky to find.

Create the Logic App

The Workflow:

• Grab tweets from my twitter timeline and put them into my SharePoint Online List, then email me.

Once you watched the video and we start by creating our own Azure Logic App. 

 

Once it's ready, head into Triggers and Actions - this is where the rules are defined.

On the right hand side you wouldn't have any API Apps in the resource group.  We'll configure them in a minute.  Click Visit the Marketplace

Configure the Office 365 Connector

There is a giant header at the top to add Office 365 Connector.  We should go ahead and add that. 

Note, in your happiness to add the Office 365 connector, you will, like me, completely fail to read "send and receive emails, calendar and contacts".  No files, or SharePoint sites.  Essentially, this connector is only for Exchange-related services.

Still useful for sending emails, so let's configure it.

  

• Read this too:  http://azure.microsoft.com/en-us/documentation/articles/app-service-logic-connector-office365/ 
• You will be seeing the "Creating..." box a lot!
• I had a look inside once it is created - there isn't much to see, or change.
• You'll notice we haven't authenticate the API App - we'll do that later.

Go back to Triggers and Actions on the Logic App

• Sometimes - the API Apps are listed by their template name and not the API App Name that you've assigned.  This is actually very confusing, I'm sure it'll be fixed soon.
• Adding either the Office 365 Connector or the SharePoint Online Connector will also add the HTTP and Recurrence API Apps automatically.

Configure the SharePoint Online Connector

http://azure.microsoft.com/en-us/documentation/articles/app-service-logic-connector-sharepoint/

So far so good.  Let's do the next one.  And I think you'll fall into another hole.

You look at this and you say, surely.  That one is for SharePoint, since the earlier Office 365 connector isn't.

Yes, I say.  That's exactly what I want.  Those are the right triggers and actions!

And here is the next hole I fell in.

Chances are, you got all the way to the end and thought why do I need a service bus for Azure Logic App to talk to Office 365.  Something don't smell right.

I have failed to read the text again - this one is for On-Premises SharePoint, and you will need to install a listener proxy that will talk to Azure Logic Apps via an Azure Service Bus.

We'll cover that later when we talk about Hybrids.

For now, go back to the Marketplace.

Use the search filter.  There are 2 SharePoint connectors.  The SharePoint Online Connector isn't shown in the default view.

Create -> Package Settings -> OK <- Create

TIP: Unpin

While you wait for the API App to be created, your Startboard is currently looking like a mess.  Right click on the other connectors and unpin from Startboard.

Add the Twitter Connector

This is in the video above.  So I'll jump through this one really quick.

More spinning.  All done!  OK now we have all our connectors.  Let's look at them.

Triggers

I want to show the various different triggers from different connectors.  But I won't actually use these in the later example.

Office 365 Trigger looks like this.	SharePoint Online Trigger looks like this	Twitter Trigger looks like this	The trigger I want to use is a simple Recurrence timer.  It will run every hour.

Activities

Next, pull down some tweets.

Add Twitter Connector and Authorize

 

Twitter Authorize, and set up to grab my timeline.

I want to put them into SharePoint Online.

 

Click Authorize - you'll see a pop up

• I want this to talk to my Office 365 Work Account - so pick the top one. 
• Note - sometimes, it doesn't seem to work, I would close the pop up and try Authorize again.  I consider this another Preview Bug

 

You should see these actions to perform on the connector.  Notice it connects to two lists specified during the setup, and there doesn't seem to be a way to change that afterwards.

Pick Insert Into tasks

We'll need to loop through the tweets and insert each one.

 

We need to change the TweetText reference from the First tweet to Each Repeating Item tweet.

Change it from:

• @first(body('twitterconnector')).TweetText

to

• @repeatItem().TweetText

Last action is an email.  Add the Connector.

 

The Office365 Connector (Exchange) is the same as Office 365.

I set the body to the created date of the first tweet (in descending order, so actually the latest tweet).

• @first(body('twitterconnector')).Created_at

The big picture:

Save

:-)

You save and close the Triggers and Actions editor.

:-(

Thanks.  I think you forgot I had just saved...  (another preview bug)

Back on the Logic App screen, I see this:

 

With a Recurrence trigger I find it always run first time I save.  But if it doesn't, you can manually Run Now

And the results

Here we have it.

The list, in my Tasks list on my SharePoint Online.

Email

The email I received.

• I don't know why the email is default to send with low importance.  You can change this.

 

Click the Pencil - I find that it is not intuitive that's clickable.  I think it should be next to the cog wheel.

And that's quite possibly the easiest Tweets to SharePoint Online List example (including set up the infrastructure) that I have ever done.

Summary

• Created Office 365, SharePoint Online, Twitter connectors.
• Created Azure Logic App on recurrence schedule
• Write tweets to SharePoint List
• Next episode, we'll look at Hybrid.  Going Cloud to/from On-Premises.