Power of PowerBI with Azure Stream Analytics and IoT Hub

Recently i gave a talk at a Microsoft Integration Commuity Event on Azure IoT Hub. In the demo i used Azure Stream Analytics for ingestion and PowerBi as its output.

To demo the IoT Hub bidirectional feature it had an abnormal detection scenario using Stream Analytics together with EventHub and a Azure Function sending a message back to the device.

The scenario is illustrated below:

Azure IoT Hub demo scenario

I used two real devices and one multi-device simulator written in C#.

  • Raspberry Pi 3 + GrovePi+ with sensors for temperature/humidy, rotary and a LCD-display. OS was Windows 10 IoT Core and a custom UWP app that sent the telemetry data to IoT Hub every second.
  • Adafruit Feather M0 (sensor: BME280 for temperature/humidity). The SDK used was the one from here https://github.com/Azure/azure-iot-arduino. It has only HTTP support at the moment but MQTT is said to be coming very soon.
  • C# Simulator was a commandline application with a random telemetry simulator to get somewhat realistic movements on its faked sensors.

The Azure Stream analytics jobs had 1 input, 2 outputs and a two part query. One for the PowerBi output where “group by TumblingWindow” was used and one for the abnormal detection (vibrationLevel > 400).

Stream Analytics Query:

Normal AS
System.Timestamp AS time,
AVG(temperature) AS avgtemperature,
AVG(humidity) AS avghumidity,
MAX(vibrationLevel) AS maxvibrationlevel,
FROM [devices-in]
TUMBLINGWINDOW(second,5), time, deviceId, longitude, latitude
Abnormal AS
SELECT deviceId, MAX(vibrationLevel) as maxvibrationlevel from [devices-in]
GROUP BY TUMBLINGWINDOW(second,30), deviceId

SELECT * INTO [pbi-out] FROM Normal
SELECT * INTO [abnormal-vibro-out] FROM Abnormal WHERE vibrationlevel > 400

The TumblingWindow (together with Sliding and Hopping -window) is the star of Stream Analytics. Thousands (or millions!) of devices sending telemetry data every second can be grouped using a time window. For demo purposes i used a short window.

The EventHub output for the abnormal scenario was read by an Azure Function that sent a Cloud-2-Device message back to the device that caused a background color change and a message on the LCD.

Azure Function code :

#r "Newtonsoft.Json"

using System;
using System.Net;
using Newtonsoft.Json; 
using Microsoft.Azure.Devices;

public static void Run(string alertMessage, TraceWriter log)
     log.Info($"Got message {alertMessage} from eventhub");
     string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["iothub"].ConnectionString.ToString();

     dynamic messageIn = JsonConvert.DeserializeObject(alertMessage);
     string deviceid = messageIn.deviceid;
     log.Info($"Will send alert notification to device {deviceid}");

     ServiceClient serviceClient = ServiceClient.CreateFromConnectionString(connectionString);
     var message = new {
         alert = true, 
         display = "TECH. DISPATCHED!"

     var commandMessage = new Message(System.Text.Encoding.ASCII.GetBytes(JsonConvert.SerializeObject(message)));
     serviceClient.SendAsync(deviceid, commandMessage); 
     log.Info($"Sent {message} to device {deviceid}");


After some simplified PowerBI reporting the demo looked like this:


The map highlights the location of the devices and the circle size are related to the vibration level.

The real power here is the scaling. Knowing that one could take this from 6 devices to millions (and back down again!) without any investment or upfront costs is pretty amazing and unthinkable just a couple years ago. That is what cloud computing means to me.

Code on GitHub

HL7 FHIR JSON decoding in BizTalk

HL7 FHIR represents the next generation healthcare interoperability standard and tries to combine the good stuff from older standards yet leveraging (somewhat) modern things like JSON, XML, HTTP and REST.

With BizTalk you can use the XML-schemas found on the main FHIR site. As BizTalk is all about XML it’s a perfect match.

A side note is that FHIR does not use any versioning in its namespace which will lead to problems if you need more than one version deployed. As usual this can be solved by modifying the namespace in and out of BizTalk using a namespace altering pipeline component.


While FHIR resources can be represented in XML they also can come dressed in JSON. Lets have a look at how we can handle that in BizTalk.

If we try to use the out-of-the-box pipeline components in BizTalk 2013 R2 for JSON –> XML conversion (or any other non-FHIR aware json decoder) the generated XML will not conform with the the FHIR-schemas and specification. The differences are highlighted here http://hl7.org/fhir/json.html#xml but two key ones are:

  • How FHIR resource type are defined.
    • In XML its the actual root-node name
    • In JSON its the ‘resourceType’ field.
  • Values are normally placed in a xml attribute instead of in the elements.

Lets look at a simplified example with a FHIR “Encounter” in JSON and HTTP POST it to a BizTalk (WCF-WebHttp) ReceiveLocation using a pipeline with the new and out-of-the-box BizTalk 2013R2 JSON Decoder.

The source json document used
The source json document used

After the pipeline the XML looks like this:

Several problems. The ResourceType element should not exist and the values should be placed inside value xml attributes.
Several problems. The ResourceType element should not exist and the values should be placed inside value xml attributes.

This does NOT match the FHIR schema.


To solve this we need a “FHIR aware” JSON to XML Decoder. Luckily there is a great open source one for .NET called .NET API for HL7 FHIR. It´s a really feature rich API that can do a lot more than just FHIR JSON and XML conversion!

Lets create a BizTalk pipeline component using .NET API for HL7 FHIR.

using Hl7.Fhir.Serialization;
using Microsoft.BizTalk.Component.Interop;
using Microsoft.BizTalk.Message.Interop;
using System.IO;

namespace Kramerica.Bts.Fhir.Common.PipelineComponents
    public partial class FhirJsonDecoder : IComponent, IBaseComponent, IPersistPropertyBag, IComponentUI
        //Execute is the main method invoked every time a message passes the pipeline component
        public IBaseMessage Execute(IPipelineContext pc, IBaseMessage inmsg)
            IBaseMessagePart bodyPart = inmsg.BodyPart;
            if (bodyPart != null)
                string json;

                using (Stream originalDataStream = bodyPart.GetOriginalDataStream())
                    if (originalDataStream != null)
                        //Read the json message
                        using (TextReader tr = new StreamReader(originalDataStream))
                            json = tr.ReadToEnd();

                        //Use FHIR-NET-API to create a FHIR resource from the json
                        //This 'breaks' the stream a puts the complete message into memory
                        ResourceReader resourceReader = new Hl7.Fhir.Serialization.ResourceReader(FhirParser.FhirReaderFromJson(json));

                        //Use FHIR-NET-API to serialize the resource to XML
                        byte[] resourceXmlBytes = Hl7.Fhir.Serialization.FhirSerializer.SerializeToXmlBytes(resourceReader.Deserialize());

                        //Create the new BizTalk message
                        var memoryStream = new MemoryStream();
                        memoryStream.Write(resourceXmlBytes, 0, resourceXmlBytes.Length);
                        memoryStream.Position = 0;
                        inmsg.BodyPart.Data = memoryStream;

            return inmsg;

As there is a 1:1 correlation between the FHIR ResourceType and xml root node name there is no need to have a configuration parameter for root node name/namespace as we have in the standard JSON decode component. Surely we could have some parameters controlling some aspects of the .NET API for HL7 FHIR but not for this simple proof-of-concept.

Lets see how the XML now looks after a HTTP POST of the source FHIR JSON to the same ReceiveLocation with a pipeline using our new pipeline component.

This looks much better and matches the FHIR XML schemas.
This looks much better and matches the FHIR XML schemas.

Great, we now have a correct FHIR XML instance and we can use it in a BizTalk integration process.

To return/send an instance of a FHIR message we just need to reverse the process by creating a FHIR JSON to FHIR XML BizTalk pipeline component and use that in a send pipeline.

SoapUI in high DPI

Running SoapUI on my new fantastic 4K 15.6″ Windows 10 laptop had me looking for a pair of binoculars.

Windows 10 itself handles the extreme high DPI resolutions quite good. But SOAPUI does not scale very well. Luckily the solution is simple.


Add the following registry setting:

reg add HKLM\Software\Microsoft\Windows\CurrentVersion\SideBySide /v PreferExternalManifest /d 1 /t REG_DWORD

Create a file called {your-soapui-exe}.manifest in the SoapUI bin folder, such as soapUI-5.2.1.exe.manifest, with the following content:

<?xml version=”1.0″ encoding=”UTF-8″ standalone=”yes”?> <assembly xmlns=”urn:schemas-microsoft-com:asm.v1″ manifestVersion=”1.0″ xmlns:asmv3=”urn:schemas-microsoft-com:asm.v3″> <description>soapui</description> <asmv3:application> <asmv3:windowsSettings xmlns=”http://schemas.microsoft.com/SMI/2005/WindowsSettings”> <ms_windowsSettings:dpiAware xmlns:ms_windowsSettings=”http://schemas.microsoft.com/SMI/2005/WindowsSettings”>false</ms_windowsSettings:dpiAware> </asmv3:windowsSettings> </asmv3:application> </assembly>


Done. Restart SoapUI and it scales so much better!

This could also be applied to other applications having the same problem.

Azure Logic Apps Text-to-speech with TalkToMe API

Many years ago i made a text-to-speech WCF binding that i used in demos and proof-of-concepts with BizTalk Server. Its time to bring that up to 2015!

This time i will use the Azure App Service stack with a custom Azure API App that can be used from a Azure Logic App. The API App will let you connect a browser session to it that will act as the “loudspeaker”.

TalkToMe GitHub repo – instructions and code

The API app has two parts, both hosted together. First the API that hosts a SignalR hub and the second one is the UI that connects one (or more) browser as the API App “Loudspeaker”. As an Azure API App is just a ‘normal’ Web App it can host both parts and no other deployment is needed. Sweet!

  • WebAPI
    • SignalR Hub
  • Client UI (Hosted inside the API App!)

SignalR is the brilliant communication framework that will allow us to trigger functionality in the client (browser) yet abstracting away the actual connection details (web-sockets, long-polling etc.).

To test the API App i used a Logic App that gets weather data from api.openweathermap.com that TalkToMe API then will read out. Note that by design the API will return 202/Accepted even if no browser (ie. SignalR client) is connected and no queuing or similar is performed.

How to use in Azure Logic Apps

  1. Deploy Azure API App
  2. Create and deploy a Logic App where TalkToMe is used as an ‘action’ Logic App
  3. Connect one or more browsers to the API by browsing the root URL of the deployed API. If you are unsure of the URL you can find the link in the ‘essentials’ section of the API App in the portal.image image
  4. Run the Logic App

When the logic app runs and the TalkToMe action fires the portal should show something like image

And you should hear the artificial voice read the weather 🙂 image

Test API using HTTP

If you just want to try the API App, outside of an Logic App, you can use a simple REST call.

REST param Value
URL http://{your-apiapp-url}/api/TalkToMe
Content-Type application/json
Body { “TextToRead” : “Nice weather today” }




Azure Logic App with File Connector for on-premise filesystem integration

Azure Logic Apps is Microsofts new shiny integration PaaS offering building on API Apps as the integration building-blocks. Sure – the tooling is far from ready and the management capabilities is lacking in functionality. But i like the way this is moving. For cloud/SaaS scenarios there is already an impressive number of connectors available and a simple API App model for custom extensions.

I decided to document the steps for simplified integration scenario of Azure Logic App with FILE Connector for on-premise filesystem integration. Its really very simple but there are some small quirks and even a small bug!

The scenario is that the Logic App should pick up files on the local file-system without requiring a custom developed scheduled process/script/application to upload the files to Azure. The FILE Connector uses a “Hybrid Connection service” to make an outgoing connection to the ServiceBus relay. This means (IT-dept Security responsible – please cover your ears) you normally don´t need any firewall openings.

To get this scenario running start by creating a “Service Bus Namespace” using the old (current) portal at https://manage.windowsazure.com and copy the connection string using the “Connection Information” function. You do not need to create a relay as this will be created automatically for you by the FILE Connector API app.


Click on the Connection Information as seen in the screenshot above.

Next step is to create the actual File Connector API using the the new/preview portal. Search for ‘File Connector’ if you are having trouble finding it.

Fill in the Service Bus Connection string copied earlier and the local root folder to be used. You will specify a relative folder to this when configuring the File Connector API APP in the Logic App later on.soapfault_com_api-app-fileconnector11


When the API app is created after a couple of minutes its time to setup the Hybrid Connection. Or at least that is what i thought…

The summary page of the API app is supposed to show some Hybrid Connection information but this never appears. This seems to be some kind of a bug (it surely can’t be me can it…) in the portal (see below) not saving the parameters specified in Package Settings correctly.



If this happens to you the easiest way of solving it is to click the Host link in the essentials section (marked in a square above) followed by Application Settings. Scroll down to the App settings section and have a look at the File_RootFolderPath and ServiceBusConnectionString. They are empty!



Edit them manually.soapfault_com_api-app-fileconnector14

Click Save and then reopen the File Connector API app blade. After some time you should now see the “Hybrid Connection” icon saying its setup is incomplete. Sweet!



Click to open the Hybrid Connection configuration.



You can either download the Hybrid Connection msi manually from here or use the Click-Once-Installation using the provided link ‘Download and Configure’.




If everything works out you should now see this on the summary page.


Great! Now lets try the File Connector API app as an Logic App trigger.

Lets assume this on-premise legacy system produces XML files and that we want to access the data. In this heavily simplified test-scenario i use the Biztalk JSON Encoder and Slack Connector as Logic App actions but anything goes.


Note that the folder specified is relative to the folder specified as a root folder.

Ok, lets drop an XML file in my fictitious on-premise system and see what happens.



The file disappears from the filesystem and if we look in the Azure portal both actions run successfully.



And the message appeared in Slack. Nice!


WCF service hosted in Azure Websites

While WCF might not be the most viable .NET technology stack on the open web as opposed to WEB API 2.0 it is still very relevant in enterprise and B2B scenarios.

It is sometimes considered hard to configure and host WCF. Lets see how hard it really is now with .NET 4.5.

The other day i quickly needed a simple test SOAP endpoint exposed on the internet. I thought i would host it in Azure Websites.

First let us create the web site in Azure. As an alternative to normal ftp-deploy lets use Azure Websites great Kudu features and GIT support.

C:\>mkdir EchoService
C:\>cd EchoService
C:\EchoService>azure site create EchoService --git --location "North Europe"


Lets crate a simple untyped WCF service echoing any SOAP-request back as a response. This is probably not a real world scenario although the untyped nature of System.ServiceModel.Channels.Message is really powerful. But any WCF service you find appropriate would work.

The sample below is a minimal single-file wcf service without any code-behind. Save it to a file called EchoService.svc.

<%@ ServiceHost Language="C#" Service="EchoService" %>

using System.ServiceModel;
using System.ServiceModel.Channels;

public class EchoService
    [OperationContract(Action="*", ReplyAction="*")]
    public Message Echo(Message message)
        return message;


Save the file and push it to the remote repository automatically created in your Azure Website.

c:\EchoService>git add .
c:\EchoService>git commit -m"First checkin"
c:\EchoService>git push azure master



GIT pushed the EchoService.svc file to the remote repository and Kudu automatically deployed it into the website wwwroot folder. If you want to learn more on the amazing kudu stuff in Azure Websites i highly recommend having a look at the short videos made by Scott Hanselman and David Ebbo.

You can reach the service at http://yourwebsitename.azurewebsites.net/EchoService.svc and maybe use something like SoapUI to try it out. The WCF default configuration will expose an endpoint using the BasicHttpBinding meaning any SOAP 1.1 envelope will work. Metadata publication is disabled by default but as this is an untyped service there is really no need for it. If needed it can easily be enabled in code or configuration.


As show Microsoft PaaS service Azure Websites is i really simple way to host a WCF endpoint in the cloud. With the help of .NET 4.5 this is easier than ever.

Great times for cloud developers

The last couple of years has meant a incredible things for us developers as they now can stay focused on the important stuff and forget about stuff that used to take hours, days and sometimes even weeks.

Lets take an example. Using the Azure command-line tools (which are built in node/javascript and are platform agnostic running on Windows, Linux, OSX) and GIT i want to:

  • Create new cloudbased WebSite
  • Create a distributed source control repository locally and at my remote website
  • Setup automatic continuous-integration from the remote GIT repository
  • Create a html file
  • Add file to local repository
  • Push to remote repository and have the site automatically deployed from there.

Ok, lets begin…

azure site create reallycoolsite --location "North Europe" --git

copy con default.html
<!doctype html><title>Really cool site</title><p>Nothing to see here...</p>^z

git add .

git commit -m "Added deafult.html to this really cool site"

git push azure master

Thats it! Done! Thanks to Azure, Kudu and GIT it took 4 (!) commands. Well, 5 if you count creating the website homepage. The site could be ASP.NET, Node.JS, PHP or static HTML.

If this was an ASP.NET application i could attach to and debug the application running in Azure directly from my local Visual Studio.

Now what if we want to use the real power of cloud computing, scaling! Can we do that? Well then we need to issue another command…

azure site scale mode standard reallycoolsite

azure site scale instances 3 reallycoolsite

Now we have a website running load-balanced on 3 server instances. I can scale up and down and pay for the minutes i use. No IIS, no applicationppol, no NLB-setup and no script editing. The developer can focus on the site content and functionality.

When i need more control i just go to the default created log/debug/diagnostic site created by Kudu at https://<yoursite>.scm.azurewebsites.net. Here you can copy files, look at deployment details and have a go at the shiny new debug console.

2014-02-02 09_45_16-Diagnostic Console


The abstraction and simplification provided by Azure Websites is really really effective and powerful yet lets you gain more detailed control when you need it.

SoapUI screen font problem

On my virtual (VMWARE) windows development machines i sometimes experience a very annoying visual problem in SoapUI where text and menu-items disappear as i move the mouse around. As seen below the File and Tools menu options are gone.

2013-10-17 09_05_21-Win7x64-BTS2010 - VMware Workstation

After poking around in the SoapUI settings without luck i finally tried the Windows Visual Effects settings in Windows 7 and after disabling the “Smooth edges of screen fonts” everything works just fine again. 🙂

2013-10-17 09_13_32-Win7x64-BTS2010 - VMware Workstation



SoapUI 2-way SSL problem using PKCS12

If you like me spend a lot of time in the swiss-army-knife of webservice development called SoapUI when not in Visual Studio or PowerPoint, you could end up getting bitten by this problem.


Java based SoapUI has great built-in support for both consuming webservices and exposing mockservices using 2-way SSL aka. two-way or mutual SSL. It is a proven and interoperable way to exchange data in B2B and enterprise scenarios in a secure way. It means that during the TLS/SSL handshake the client proves its identity to the server using a PKI challenge the same way as the server does to the client in normal TLS/SSL. To allow this the client needs a client certificate with a corresponding private key.


Doing most of my work in .NET i tend not to use the JKS (Java Key Store) that much and to call a webservice requiring certificate authentication in SoapUI i just point to a PKCS12 file directly. The PKCS12 file (.P12 or .PFX) are easy to move to and from the windows certificate store and is one of the standard containers for certificates with private keys.




Normally this works perfect but if you are having trouble getting the SSL handshake to work one (of many) reason can be that the client certificate you are using has a trust chain including an intermediate CA which is getting more and more common.
The symptom is that SoapUI during the handshake simply wont participate in the client authentication thus leaving you with either a 403 or simmilar.




In .NET this normally just works as you most likely have the complete chain of trusted certificate issuers in the Windows Certificate Store. But in SoapUI and other Java clients you can no longer rely just on the P12 file.


To solve this for SoapUI and other Java applications you need to create a JKS file containing the full certificate chain under the same alias.


1. Convert the PKCS12 to a JKS using Javas keytool.exe utility. You can find the utility in Javas JRE\BIN folder. If not elsewhere you have this together with the SoapUI installation.


jre\bin\keytool.exe -importkeystore -srckeystore MYCLIENTCERT.P12 -destkeystore MYCLIENTCERT.JKS -srcstoretype PKCS12 -deststoretype JKS -deststorepass password -srcstorepass password -destalias my-client-cert-alias -srcalias "my client cert"


2. Then you need all public certificates in the chain including both root and intermediate CA cert. Export them from windows certificate store in Base64 format (.CER)


3. Open all three files in a texteditor and chain them like this:
Save the file as my-complete-cert-chain.pem


4. Import the file to the JKS using
jre\bin\keytool.exe -import -keystore MYCLIENTCERT.JKS -alias my-client-cert-alias -file my-complete-cert-chain.pem


Now you got a JKS with your client certificate certificate chain that should work fine in SoapUI or in other Java-based webservice clients. 🙂

ASP.NET Web API support for ODATA $select

Since VS 2012.2 update ASP.NET Web API can take advantage of the ODATA Query parameters. Having the API consumer dictate part of the query can be powerful yet something that naturally needs some performance and security consideration before implementation.

Enabling the support is as simple as decorating the controller class or selected methods with the ActionFilterAttribute Queryable.


Now the API automatically supports things like $top, $orderby, $filter directly on the URL. 🙂

So a HTTP GET to http://server/service/api/wines?$top=2&filter=WineType eq ‘Red’ would get me this response


You can control what queries to allow using properties on the ActionFilter Queryable.

One thing missing in the Web API ODATA implementation compared to the full-blown ODATA implementation in WCF Data Services is the $select keyword. The $select lets the API consumer pick what fields to return. With large objects this can save both bandwidth and serialization/deserialization performance.

The fact that ASP.NET Web API is open source in combination with the nightly-builds available on MyGet makes testing upcoming features super easy. Since a week or two back both $select and the $expand are available in the nightly-builds. (http://www.myget.org/gallery/aspnetwebstacknightly).

To install run this command:

install-package Microsoft.AspNet.WebApi.OData -pre -source http://www.myget.org/F/aspnetwebstacknightly


After installation i can now make a HTTP GET like this
http://server/service/api/wines?&$top=2&$filter=WineType eq ‘Red’&$select=Id,Name and get only the ‘Id’ and ‘Name’ fields in the response.



The nightly-build i tried (5.0.0-beta1-130511) only had $select support when using JSON. With XML it causes a serialization exception. I guess supporting this with the DataContractSerializer would require alot more work compared to what they had to do with JSON.

Anyway – i think its a nice feature so thanks to the team working at getting this feature into the framework in a future release and the opportunity for me to beta-test it already today 🙂