Beer Kegerator with IoT Part 2 – How A Beer to the Face is a Good Thing

Normally, getting splashed in the face with beer is not a good thing. I am going to show you how using your face with beer can be a good thing. This blog is a continuation of my previous blog on using IoT with a Beer Kegerator (link).

Deploying the first version of the IoT app was well received, and there were a lot of great feedback suggestions on how to improve the experience. I wanted to showcase some of the newer Microsoft technologies, and ended up choosing facial recognition support for the app. This would have great benefits, as it would allow accurate tracking of who is pouring the beer and when they requested a pour. Coupled with the volume tracking of the selected beer, this would provide excellent data capabilities on who is drinking beer, when the beer is poured, along with the consumption rates of the different beers provided.

I also wanted to expand the app to include all four beer lines that we have available, so this required a bit of a redesign of the app. In order to do this, I needed to have the app enable/disable any of the four lines when someone requests to pour a specific beer.

Additional Hardware

First off, I purchased the additional three flow sensors so that I had the ability to track volume on all four beer lines. Modifying the code to account for this was simple, as it was just a matter of designating additional GPIO pins to receive the digital pulses.

More interesting is how I was going to handle the selection of beer when a pour is requested. I needed to verify that only the selected beer was able to be poured, while the other lines would not work. This meant providing the ability to disable each of the beer lines when necessary. I solved this problem with a food-grade quality solenoid valve, which allows opening/closing control of the valve via an electrical signal.


This valve is normally closed, which means that when powered off the valve is closed, thereby stopping the flow of beer in the line it’s connected to. Providing power to the valve (12 volts) opens the valve as long as the power is applied to it.

The next question is how do I control the 12 volt signal to each of the solenoid valves connected to the four beer lines? This can be done with a simple relay switch, which allows a control signal to toggle the 12v on or off to the solenoid valve. I found a simple circuit board with four relays on it:


This will work perfectly for my situation, as I can use 5v control signals from the raspberry pi to turn on/off the different beer lines (activating a solenoid via the relay board) when a pour is requested.

Software Changes

With the hardware issues resolved, we now turn to the changes needed in the software. In order to support and control four beer lines, changes are needed in both the IoT app on the Raspberry Pi, as well as creating a web app to handle administration.

IoT Kegerator App

The main changes to the IoT app are centered around how to display the four different beer lines that a user can choose from, along with the process to select a beer and have the facial recognition routines recognize the person, so the selected beer line can be opened for the pour.

The previous version of this app only handled one line, so I needed to leverage the touchscreen I am using with the Raspberry Pi, in order that a person can touch an icon of the beer they want.


This will start the facial recognition process so they can be recognized. Once the app recognizes them, it will open their selected choice for 30 seconds for them to pour their beer. After 30 seconds, the system will close the line and be ready for the next person.


The following code snippet shows how the app processes a request from a person, when they touch one of the beer icons:


The first thing this method does is display the associated web camera feed and then calls the facial recognition routine to take a snapshot of the webcam and process it for recognition. If it returns with a valid registered person, the routine will open the solenoid valve for that beer line ONLY, by setting a GPIO pin which will drive a specific relay, which in turn enables the 12v signal to the solenoid valve to open it. The method will then set the 30 seconds and start a timer to count down to 0, where it will then reset for the next time.



This timer method runs every second, and records the digital pulses coming from the open beer line flow sensor, for the 30 seconds the beer line is open for. After it reaches the end of the 30 seconds, it will then take the total of the recorded digital pulses and send that info to Azure for saving into the database. The method will then reset some counter variables and turn itself off.

Facial Recognition

The final change needed for the IoT Kegerator App is adding support for facial recognition. Since I wanted to showcase Microsoft technology as part of this app, it made sense to leverage the Azure Cognitive Services suite of functionality that they provide (more info here). Specifically, I will be using the Face API that is part of the Computer Vision area.

The Face API is designed to detect and analyze faces/facial features in images. This will work for my implementation, as I can save the picture that is taken by the webcam when a person is requesting a beer. The trick then is to compare that image taken from the webcam against a “trained” model of images for a registered user. If recognized, the API reports that back to the app, which in turn will open the beer line for the person. Here is a code snippet showing how the Face API is called and leveraged to recognize a face when the app takes a picture.


Please note that the CustomPerson class is based off the Face API Person class. The UploadandDetectFaces method calls the Face API to detect any faces in the webcam picture, and returns an array of detected faces. I then call the IdentifyAsync method against the array of detected faces to see if any correspond to any registered user face models that I trained previously. If the Face API determines there is a match I get additional details about the recognized face and return that to the calling method.

Here is a screenshot of the final redesigned app (with hardware):


In conclusion, now that I’ve implemented this latest version of the IoT Kegerator app, I am able to track the consumption of each beer line, along with how much beer each registered user is consuming. I also have the ability to track time series data on when beer is being consumed, along with consumption rates of each beer on the four lines. Here is a quick example of a monthly consumption report:


Here’s to getting a beer to the face!

Beginning IoT – Installing Windows 10 IoT Core on an x86/x64 Device

This is the second part in a 2-part series on how to install Microsoft Windows 10 IoT Core on an Internet-of-Things (IoT) device. Part 1 described how to install Windows 10 IoT Core on a Raspberry Pi 3 device. This article will focus on the steps required to install Windows 10 IoT Core on an x86/x64 device.


What is Windows 10 IoT Core?

Windows 10 IoT Core is a version of Microsoft’s Windows 10 operating system that has been optimized for smaller devices that can run on either ARM or x86/x64 devices. These devices can run with or without a display device.

When we talk about the different IoT devices, the processor type needs some explanation. ARM devices are called Advanced RISC Machines, with RISC standing for Reduced Instruction Set Computer. What this means is that the processor has been slimmed down to only include a reduced set of commands it can process. While this means that the processor can’t do certain things, it requires a low amount of power to execute what it can do, so that translates to increased battery life. The Raspberry Pi is classified as an ARM device.

Devices with the x86/x64 architecture are classified as CISC processors, which stands for Complex Instruction Set Computer. These processors do not have their instruction sets slimmed down, so they can perform more complex operations, at the cost of increased power consumption (and therefore lower battery life). Intel’s Baytrail devices running the Intel Atom processor E3800 is an example of an x64 device.



Before you can install on an x86/x64 device, you need to make sure you have a PC that is running Windows 10 1507 (version 10.0.10240) or higher. You can find out what version you are running by clicking on the search box (next to the Start button) and typing ‘winver’. This will display a dialog as shown here:



For certain x86/x64 device you can use IoT Core Dashboard to run through the installation process. However, since the process is similar using IoT Core Dashboard (which I already covered in Part 1), I am going to go through the installation process steps using the Windows ADK IoT Core Add-Ons.

You will need an IoT device to install on – here are some options that Microsoft supports:


We will be using the Intel Atom E3800 (aka Baytrail) built on an industrial PC for examples in this article. Typically we would need a micro SD card for storage but since the industrial PC comes with onboard memory storage, we do not need an SD card.


For software, we will need to install the following on the PC we build the image on:


Installation Steps

Once you have the prerequisites installed, you are now ready to begin the installation process. We will be building a basic image, which involves combining the Windows IoT Core packages along with a board support package for the target hardware (Baytrail device) into a flashable file (FFU file).

First off, you need to set your OEM name, which will help you distinguish your created packages from packages other manufacturers have created. Edit the setOEM.cmd file, located at C:\IoT-ADK-AddonKit and set the OEM_NAME variable accordingly. Please note you can only use alphanumeric characters.



You should now open the IoTCoreShell.cmd file, which is a specialized command-line window that you will be doing a lot of the work for building the image. This file is located in the directory where you installed the IoT Core ADK Add-Ons (C:\IoT-ADK_AddonKit). Open an administrator-elevated privilege command window and navigate to the C:\ IoT-ADK_AddonKit directory. Type in IoTCoreShell.cmd to open the IoT Core Shell. This application will prompt you to select the architecture you are working with (1 for ARM, 2 for x86, 3 for x64). We are creating an x64 image so select x64.



At this point, you need to install certificates which will be used to sign the package binaries. Since this article is focused on a test image, you can run the installoemcerts.cmd command to install test certificates in the root certificate store of the PC you are building the image on. This only needs to be done the first time you are building an image.



The next step is to extract the board support package (BSP) files for the device you are building an image for, and run the buildpkg.cmd command to build the package files used in creating the image. You can download and extract this BSP zipfile and copy to C:\IoT-ADK-AddonKit\Source-x64\BSP directory to begin using for building an image.



You can now begin to create the packages and build the FFU image for the Baytrail x64 device. Go back to the IoTCoreShell.cmd command window and enter buildpkg all which will build the .cab files for all the BSP directories the program sees under C:\IoT-ADK-AddonKit\Source-x64\BSP. Please note that if you had selected x86 when you ran the IoTCoreShell.cmd, running this command to build all the packages would look in the C:\IoT-ADK-AddonKit\Source-x86\BSP to build any BSP files located there.



Once the program finishes building all the BSP packages, you can now create a new project by entering the following, where Product_Name and BSP_Name are the name of the product you would like and the BSP name, respectively.

newproduct <Product_Name> <BSP_Name>

So, for example, entering newproduct MyBayTrailDevice BYTx64 will create a project and its files under C:\IoT-ADK-AddonKit\Build\amd64\MyBayTrailDevice for the BYTx64 board support package files.



You are now ready to build the actual FFU flashable image file. This can be done by entering buildimage <Product_Name> Test, replacing Product_Name with your product name (MyBayTrailDevice in our example). The second parameter specifies whether you are building a Test or Retail image. This process takes about 20-30 minutes to complete and once finished you will have a file named flash.ffu created under the C:\IoT-ADK-AddonKit\Build\amd64\ MyBayTrailDevice\Test subdirectory.



If you encounter any errors, the buildimage process will error out and specify a log file that has detailed information on the error.

Now that you have a flashable FFU image file, you will need to flash it onto the IoT device you are working with. For our example, this is the Baytrail device and since it has onboard storage space we need to use a bootable USB thumbdrive with the FFU file on it. In order to create this, we will use Windows PE to create a bootable disk and then copy the flash.ffu file onto it. Here are instructions to create a bootable WinPE thumbdrive:


Copy the flash.ffu file to the root of this bootable drive once you’ve created it. You are now ready to insert this USB thumbdrive to the IoT device and power it up. Make sure you specify in the IoT device’s BIOS to boot first from a USB drive.


WinPE will boot up and open a command window for you at the x: drive. Change to the d: drive and enter dir to see your flash.ffu file. WinPE comes with DISM, the Deployment Image Servicing and Management tool and we will be using this to flash the FFU file onto the IoT device. Enter the following in the command line to flash the FFU file:

dism.exe /apply-image /ImageFile:Flash.ffu /ApplyDrive:\\.\PhysicalDrive0 /skipplatformcheck



Once DISM has successfully completed the flashing process, you can power down the IoT device and remove the USB thumbdrive. Turn on the IoT device and have it boot normally off its storage. After a few minutes you should see the Windows 10 IoT Core startup screen and it should prompt you to select a language and whether you want Cortana activated. Once you make these selections the default application will appear.



Congratulations! You have successfully installed Windows 10 IoT Core on a x64 IoT device! Future steps can now be to modify the image to have it include your custom application or you can add drivers to the image if you need other functionality (such as Bluetooth or serial communications).




Beginning IoT – Installing Windows 10 IoT Core on a Raspberry Pi

This is the first part in a 2-part series on how to install Microsoft Windows 10 IoT Core on an Internet-of-Things (IoT) device. This article will focus on the steps required to install Windows 10 IoT Core on a Raspberry Pi 3. Part 2 will focus on installation on an x86/x64 device.


What is Windows 10 IoT Core?

Windows 10 IoT Core is a version of Microsoft’s Windows 10 operating system that has been optimized for smaller devices that can run on either ARM or x86/x64 devices. These devices can run with or without a display device.

When we talk about the different IoT devices, the processor type needs some explanation. ARM devices are called Advanced RISC Machines, with RISC standing for Reduced Instruction Set Computer. What this means is that the processor has been slimmed down to only include a reduced set of commands it can process. While this means that the processor can’t do certain things, it requires a low amount of power to execute what it can do, so that translates to increased battery life. The Raspberry Pi is classified as an ARM device.

Devices with the x86/x64 architecture are classified as CISC processors, which stands for Complex Instruction Set Computer. These processors do not have their instruction sets slimmed down, so they can perform more complex operations, at the cost of increased power consumption (and therefore lower battery life). Intel’s Baytrail devices running the Intel Atom processor E3800 is an example of an x64 device.



Before you can install on the Raspberry Pi, you need to make sure you have a PC that is running Windows 10 1507 (version 10.0.10240) or higher. You can find out what version you are running by clicking on the search box (next to the Start button) and typing ‘winver’. This will display a dialog as shown here:



You will also need to download and install the Windows 10 IoT Core Dashboard from here.

Of course you will also need a Raspberry Pi device to install onto. There are several different kits available on Amazon – I used the Canakit Starter Kit here.

Finally, you will need an SD card reader so that you can write the installation files to the SD card that will be placed in the Raspberry Pi.


Installation Steps

Once you have the prerequisites, you are now ready to begin the installation process.

First off, run the Windows 10 IoT Core Dashboard program, and click on Set up a new device from the menu on the left. This will display a screen that allows you to select the Device Type, OS Build and other information to configure as part of the installation.


Select Broadcomm [Raspberry Pi 2 & 3] as the Device Type, and Windows 10 IoT Core (17134) as the OS Build. You can also select Windows Insider Preview or Custom if you want to install a preview build of Windows 10 IoT Core, or a custom image file (flash.ffu file).

Next, insert your SD card into the Windows 10 PC you are using. Be aware that your SD card should be at least 8GB in size and I prefer formatting it prior to this step (this is optional – understand that the installation process will overwrite any pre-existing data on the SD card). The IoT Core Dashboard program should recognize the SD card and display it in the Drive selection.

You can then enter values for the Device Name and Password, and whether you want to use a Wi-Fi Network Connection when the Raspberry Pi starts up with our installation.


Check the box to accept the software license term and click on the Download and Install button. This will begin the installation process. During this process, Windows 10 IoT Core will be downloaded and the installation process will flash the files to the SD card.


If you see a prompt for UAC (User Access Control), click Yes to continue. The process may then open a command window to clean the SD card (if you had something on it previously) before it flashes the new installation onto it.


The installation process will then run the DISM program (Deployment Image Servicing and Management tool) to flash the installation files onto your SD card.


Once this is complete, the command window will close, and IoT Core Dashboard will state that your SD card is ready to be placed in the Raspberry Pi and started up.


Eject the SD card from your PC and place it in your Raspberry Pi. Connect an HDMI cable to a display source (monitor) and then plug in the power to start the device. If you didn’t choose to use a Wi-Fi network connection on startup you will need to plug in an Ethernet cable if you want Internet access.



You will first see the Windows logo with a spinner when the device is first powered on:



Let the device power itself on, it usually takes a minute or two (and might reboot itself). Once you get to the following screen, plugin a USB mouse and make your language selection.


Clicking Next will display a screen asking if you want to configure Cortana – I selected Maybe Later.


Windows 10 IoT Core will then run the default application, which looks like this:


Congratulations! You have now completed the installation process and you have a standard Windows 10 IoT Core installation on your Raspberry Pi! You are now ready to begin deploying your applications to this device!

Happy coding!



Using IoT on a Beer Kegerator

Being born and raised in the great state of Wisconsin, beer has been a part of most of my adult life. Couple that with my love of technology, I always wondered how I could leverage some cool tech with a beer theme. Since the proliferation of inexpensive hardware and the Internet of Things (IoT), it has now become easy (and cheap!) to provide solutions that can be used to monitor (among other things) beer-related activities. This article will describe and detail the steps I took to create a solution for monitoring beer consumption on a beer kegerator.

The first thing I needed to do before building anything is to understand and design what it is I wanted to build. Since I want to monitor beer consumption from a kegerator, I needed to draw out the major parts of my solution. Once I know that I can then begin to build and test the different parts of the system. The drawing below shows the major parts of my solution:


As you can see, when someone taps a beer from the kegerator, an inline flow meter sensor sends information to an IoT device, which then processes the information and sends it to the cloud, where it is stored for data analysis.

Now that I have an idea of my overall architecture, I can begin to think about what hardware and software I need to create my solution.



For hardware, I chose to use a Raspberry Pi as my IoT device. The Pi is a low-power, inexpensive device that met my needs for this project (built-in ethernet network, multiple GPIO pins, easy to install apps). Please note that I also considered using the ESP8266 chip for this project – this little chip is great for simple IoT project as it’s really cheap, has built-in wireless networking (with a full TCP/IP stack!), and multiple GPIO pins for use. The main drawback for this project is that this chip only provides 3.3v for power and I needed 5v for the flow sensor, so it was easier to use the Pi. The other drawback is that I can’t install Windows 10 IoT Core on the ESP8266, so using a Pi simplified my design.

The other piece of hardware I need is a flow sensor to measure the flow of beer through the line when it’s being tapped. Initially I chose a really cheap sensor designed for coffee-makers but found out that these won’t work for measuring beer flow (see Testing section), so I went with a more expensive sensor. I chose the Swissflow SF-800 (link), which is about $60 USD. This flow sensor sends digital pulses when a liquid is flowing through it, so that allows me to measure how much beer is being dispensed. This sensor requires +5Vdc to power it properly, so that required me to use a Raspberry Pi (which also provides +5Vdc).



The software selections I made were driven (in part) by my hardware choices, but also by what apps I wanted to provide. I wanted to have an app that runs on the Raspberry Pi and processes the incoming pulse data from the SF-800 sensor and then send that data to Azure. I also wanted this app to have a user interface that displayed how much beer is left in the current keg, along with the ability for an administrator to “reset” the app (when the keg is empty and is changed out for a full one).

Windows 10 IoT Core provides the operating system for the Raspberry Pi, and this also allows me to easily deploy and manage any apps I want running on the device. Please review this link on how to install Windows 10 IoT Core on the Raspberry Pi.


The app that I am creating for this solution is a Universal Windows Platform (UWP) app and is designed for running on IoT devices that have Windows 10 IoT Core on them. This app will process the incoming digital pulses from the SF-800 and send them to Azure IoT Hub.


The following code snippet shows how I receive the incoming digital pulses from the SF-800 flow sensor. I have this sensor connected to GPIO pin 5 from the Raspberry Pi so that when the value on that pin changes it triggers an event in my app to signal that a pulse was sent by the SF-800.


I also have a timer on another thread that ticks every 0.5 second and looks to see if any incoming pulses have been received by the SF-800 flow sensor. If there have, it sends them off to Azure IoT Hub for storage.


The software in the Azure cloud that I will be leveraging is Azure IoT Hub, Stream Analytics and Azure SQL. Azure IoT Hub provides the mechanism to receive incoming telemetry data from my IoT device and route it for processing and storage. I am having Azure IoT Hub route my data to Stream Analytics, which then will process it and save it in an Azure SQL database. Once in the database, I am free to consume it in a number of ways, such as PowerBI or any custom app that can consume data from SQL.


As incoming telemetry data is received from the Raspberry Pi, Azure IoT Hub receives that data and Stream Analytics is used to process that incoming data and save it in an Azure SQL database. This is done through the Stream Analytics interface by setting up and input (Azure IoT Hub) and an output (Azure SQL database) and configuring a query to do any processing needed at that time.



Once I created the software components and connected the hardware that I have, it is time to test the functionality of my solution. I first tested the solution by connecting my Raspberry Pi (with my UWP app installed) to a breadboard where I have the SF-800 flow sensor connected. I also have a couple of LEDs to indicate a heartbeat pulse (green) and to indicate flow sensor pulses (red).


I configured Azure IoT Hub and started my Stream Analytics job so that incoming data from my IoT device will be received and processed properly. Testing this way involved blowing air through the SF-800 device (I used my breath – GENTLY!), making sure the air flow was in the proper direction (going the wrong way can damage the sensor).

Once I knew this was working I wanted to validate the accuracy of the digital pulses of the SF-800. To do this, I got some plastic tubing of the same size being used in the kegerator along with a funnel. I then measured out 1 cup of water and then proceeded to pour it through the flow sensor while everything was running.



Now that I have tested my solution, it is ready for deployment! This included placing the flow sensor inline with the actual kegerator tubing on the line I wanted to monitor. I still kept the breadboard as this was not a fully productized solution (meaning I didn’t create the wiring on a PCB).


I encountered a testing issue I failed to realize until after I deployed my solution for the first time. I was originally using a cheap flow sensor designed for coffee makers, and when I deployed this to the beer line I noticed that it made the beer foam as it was passing through the sensor. This was something I didn’t test for prior to deployment so it forced me to rethink my design (and what sensor to use). I eventually found the SF-800 sensor and this worked much better when I deployed it with my solution.

In conclusion, now that this solution is connected to the kegerator, I can monitor how much beer is left in the current keg! I can also enhance my solution by leveraging an Azure Webjob to send an email notification when the keg is getting low. How great is that? No more tapping a beer just to find out that there isn’t any left!



Disabling Windows Update in Windows 10 IoT Core

If you’re working with Windows 10 IoT Core on your devices and have wondered how you can disable Windows Update, well wonder no more! This article will detail out the steps needed to disable this service.

First off, I’d like to state that I don’t recommend that you disable Windows Update on your devices running Windows 10 IoT Core, as this will prevent any future updates from being installed on your devices. Doing so may expose your devices to security vulnerabilities that would be potentially resolved in an update of IoT Core.

However, there may be some situations where you need to disable Windows Update on your devices, such as controlling the amount of data downloaded to your device (if connected to the Internet through a metered connection), or you need a completely stable environment that doesn’t ever change (giving an administrator the ability and control to update only when absolutely necessary).

Having said the above, here are the steps needed to disable Windows Update on devices running Windows 10 IoT Core:


  1. Install and Run IoT Core Dashboard

Download and install IoT Core Dashboard (link) to a Windows 10 PC that is on the same network that your target device is connected to. Once you have installed IoT Core Dashboard you can run it by typing ‘IoT Core Dashboard’ in the Windows 10 Start menu search box:


  1. Launch Remote PowerShell session on Device

Once IoT Core Dashboard is running, you should see a device entry listing (under ‘My Devices’) for the target gateway device. Right mouse click this entry to display a context menu and click on ‘Launch PowerShell’. This will launch a remote PowerShell window to the target device – you will be prompted for a username and password to access the device.


  1. Verify PowerShell session windows is displayed

Once you enter a username and password for the target device, you will see a PowerShell window for the target device, similar to this:


  1. Enter PowerShell commands to disable Windows Update

The following PowerShell commands will disable Windows Update on the target device:

sc.exe config wuauserv start=disabled

sc.exe stop wuauserv


Here is a screenshot of the results of running these commands. This is what you should see if the commands have executed successfully:



  1. Verify Windows Update is disabled

The following PowerShell commands will verify that Windows Update has been disabled on the target device:

sc.exe query wuauserv

reg.exe query HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\wuauserv /v Start


Here are some screenshots of the results of running these commands. The first command should show a STATE of 1 (STOPPED), while the reg.exe query should show a REG_DWORD value of 0x4:




You are finished! Windows Update should now be disabled on the target device.

There is another option for disabling Windows Update on your devices running Windows 10 IoT Core, and that involved disabling this when creating the OS image for your device (the .FFU file). Disabling Windows Update with this option would mean that after flashing IoT Core on the device you would have it permanently disabled right from the get-go, meaning you would not have to run the PowerShell command described above.

If you are using Windows Configuration Designer (link) when creating your IoT Core image, there are options that allow you to disable Windows Update and other types of updates on the device. These settings are under Runtime Settings/Policies/Update and there are 4 different settings to control updates (see highlighted areas in the screenshot below):


I hope this article helps in understanding the options and steps in disabling Windows Update on your devices running Windows 10 IoT Core.


Thanks for reading!



Displaying and Automating Legacy Data using PowerBI

One of the more common requests involving business intelligence projects is surfacing data from legacy, line-of-business systems in an automated fashion. I recently was involved with a project where the client was making this exact request. This blog will explain how I tackled the problem and used a number of Microsoft technologies to come up with a solution.

The Problem

The client had a large set of data from their AS/400 mainframe system that they needed to present on a weekly basis to their executives. This data is used to make critical business decisions, so the accuracy and timeliness of presenting this data is crucial to the client’s business.

A manual process was implemented, performed by an employee, to extract this data. Microsoft Excel 2013 was used for this process, and was performed on a weekly basis. The employee created used PowerPivot to create tabular list representations to show the necessary data. A PowerPivot data model was created as part of this manual process.

There were two main issues causing pain with this process: The data used to create the PowerPivot data model was very large, and necessitated multiple passes of extraction from the AS/400 system into temporary Excel spreadsheets, as the in-memory limits of Excel were being reached, causing application crashes. The second issue was that the resulting tabular data generated was not visual and the client wanted a graphical and interactive experience when presenting this data.

The Solution

In order to solve these problems, as well as automate the process, I utilized the PowerBI technology stack, along with SQL Server 2012 and SharePoint 2013. The PowerBI tools (PowerPivot and PowerView) provide the data model and interactive graphical dashboards. SharePoint provides a central area for the PowerView dashboards. SQL Server Integration Services (SSIS) and SQL Server Analysis Services (SSAS) provide the ability to extract, transform and load the data from the AS/400 system for consumption by the PowerBI tools. Finally, SQL Server Agent jobs automate the update of the data, so that any data changes on the AS/400 side are propagated to the SSAS data model.

The first step in the solution was to surface the data from the AS/400 system to SQL Server, so that other SQL Server technologies could be leveraged to manipulate the data. A linked server in SQL was created to do this. The steps to do this are beyond the scope of this post, but here is a link that explains the steps:

Once the linked server to the AS/400 data was in place, I needed to use SQL Server Integration Services (SSIS) to massage the data coming from the AS/400. There were a number of data fields that needed to be formatted properly (mostly dates), so I decided to create a dedicated SQL Server database to hold this formatted data. SSIS allows you to create a package that you can design to perform various actions, including the data formatting that was required.

The SSIS package created references the linked server I created, performs the data manipulation steps and then loads the results into tables in the dedicated SQL database. This package is published to the SQL Server environment and can be run on demand by an administrator. This extraction and transformation was then automated by creating a SQL Server Agent job that schedules the execution of the SSIS package.

Now that I had the formatted data in my dedicated SQL Server database, I was ready to process this data for use with the PowerBI tools. In order to do this, I needed to create a data model using SQL Server Analysis Services (SSAS) that can be used by the PowerBI tools.

Using SQL Server Data Tools (SSDT) I created a tabular data model, which allowed me to reference the dedicated SQL Server database that was created as a source. Using Data Analysis Expresssions (DAX) along with measures, the data model was designed to perform the necessary calculations to present the data in its final form in the PowerView dashboards. Once the data model was compiled and published to SQL Server, I automated the processing of these calculations using a SQL Server Agent job on a scheduled basis (very similar to the agent job created for the SSIS package).

Finally, I used SharePoint 2013 as a central location to access the PowerView reports I created, displaying the data. The PowerPivot add-in for SharePoint needed to be installed, and SharePoint Server 2013 was also required.

Before I could create a PowerView report in SharePoint, I needed to create a data connection to the SSAS data model I created in the previous step. Since I’m hosting these reports in SharePoint, I used the Microsoft BI Semantic Model for Power View connection type ( when creating the data connection.

Once the data connection was created, I am now able to create the PowerView report. I did this by clicking the ‘Create Power View Report’ in the menu for the data connection, as shown here:

This creates a blank PowerView report and displays the report editor, ready for design of the report. At this point, I was able to create different views for the report I needed using the PowerView designer. Below is an example report that can be created with this designer.

As you can see, this process involved a number of Microsoft technologies that came together to provide a scalable, automated process for displaying legacy data in SharePoint using PowerBI. SQL Server Analysis Services and Integration Services provide the ETL capabilities and calculation power for manipulating the data for consumption by PowerBI. Users viewing the PowerView reports can be guaranteed the displayed data is fresh, since the reports query the Analysis Services model data, which is kept up-to-date by SQL Server Agent jobs.


Dynamics 365 – A First Look

Microsoft recently announced their next generation of Azure-hosted business services, titled Dynamics 365. General availability is scheduled for November 1st, 2016. Here is a first look at the features and functionality being provided as part of this new offering.

If you would like further information on how to prepare for the upcoming release of Dynamics 365, please contact us.