Adatis BI Blogs

A quick look at U-SQL Database Projects

U-SQL Database Projects were released today (see here for the announcement from Microsoft) and it promises easier development and deployment. I’ve blogged in the past about using PowerShell to deploy U-SQL scripts, and I’ve worked with the older “U-SQL Project” project quite a bit, so I wanted to try the new project out as quickly as I could to see what it has to offer.Once you’ve updated to the latest version of ADL Tools, you’ll be able to select the new project type:DevelopingThe project contains a number of database object templates, allowing you to get off the mark quickly with development, and the project allows all of the usual visual studio organization with folders, and intellisense in the code editor.Object templates available:Here’s the template of a Procedure Script item:Source ControlThe projects integrates much more nicely with TFS than the older “U-SQL Project” does. It actually gives you the icons (padlock, check mark, etc..) in the solution explorer, so it actually looks like it’s under source control!Something that I’d really hoped had been fixed, but hasn’t, is when copying and renaming an existing item, it doesn’t recognize the rename. You have to undo the checkout of the non-existent object (the copy, before being renamed):Something that has been fixed is that with the older project style, you wouldn’t be able to edit the “Procedure2.usql” item from my previous example until you’d checked it in – now you can!DeploymentWhere you’re deploying to is controlled by the Cloud Explorer in Visual Studio and which Data Lakes you have access to. By right clicking and selecting deploy on the project, you are presented with a simple dialogue box where you select which Data Lake Analytics account, making it nice and easy to deploy to development vs UAT, etc…The Database Name specified in this textbox is the name of the database your project will be deployed to. Unfortunately, it is just a free form textbox, so be careful of any typos!

IoT Hub, Device Explorer, Stream Analytics, Visual Studio 2015 and Power BI

As we saw in my previous blog, the IoT Hub allow us to collect millions of telemetry data and establish bi-directional communication between the devices, however, more than quantity, what we need is valuable insights that will lead to smart decisions. But how can we do that? Collecting the data There are thousands of sensors we can use, depending on the purpose. If we check the Microsoft documentation we will find tutorials for the Raspberry Pi, Arduino, Intel Edison or even simulators created with .Net, Java or Node. The first step is always the creation of the IoT Hub on the Azure Portal. Next, we have to add the devices, which can either be done using C# and the IoT Hub Extension for VS 2015 or the Device Explorer. This last tool, provided by Microsoft, can easily register new devices in the IoT Hub and check the communication between the device and the cloud. Once the devices are properly configured we will need to store the data, which can be done using a SQL Azure Database.   Represent the data Now that we collected the data, we want to be able to represent it. One of the best ways to do that, is by creating some Power BI reports and dashboards, which will be populated via Stream Analytics. A good example of a similar architecture and example dashboards can be found on Piotr’s blog Using Azure Machine Learning and Power BI to Predict Sporting Behaviour. Note that on his example, he used Event Hubs instead of the IoT Hub.   Insights and actions Let’s imagine a transportation company is collecting the telemetry from a food truck equipped with speed, location, temperature and breaking sensors. In order to assist their delivery process, they have a report being refreshed with real time data that triggers some alerts when certain values are reached. One of the operators received an alert from the temperature sensor, and after checking the dashboard he realizes the temperature is too high and it will affect the quality of the products being transported. Instead of calling the driver and make him aware of the situation, because the sensors are connected to an IoT Hub, he can simply send a command to the sensor and reduce the temperature.   More info:

Using R Tools for Visual Studio (RTVS) with Azure Machine Learning

Azure Machine Learning Whilst in R you can implement very complex Machine Learning algorithms, for anyone new to Machine Learning I personally believe Azure Machine Learning is a more suitable tool for being introduced to the concepts. Please refer to this blog where I have described how to create the Azure Machine Learning web service I will be using in the next section of this blog. You can either use your own web service or follow my other blog, which has been especially written to allow you to follow along with this blog. Coming back to RTVS we want to execute the web service we have created. You need to add a settings JSON file. Add an empty JSON file titled settings.json to C:\Users\<your name>\Documents\.azureml. Handy tip: if you ever want to have a dot at the beginning of a folder name you must place a dot at the end of the name too, which will be removed by windows explorer. So for example if you want a folder called .azureml you must name the folder .azureml. in windows explorer. Copy and paste the following code into the empty JSON file, making sure to enter your Workspace ID and Primary Authorization Token. {"workspace":{ "id" : "<your Workspace ID>", "authorization_token" : "<your Primary Authorization Token>", "api_endpoint": "", "management_endpoint": "" }} You can get your Workspace ID by going to Settings > Name. And the Primary Authorization Token by going to Settings > Authorization Tokens. Once you’re happy save and close the JSON file. Head back into RTVS, we’re ready to get started. There are two ways to proceed. Either I will take you line by line what to do or I have provided an R script containing a function, allowing you to take a shortcut. Whichever option you take the result is the same. Running the predictive experiment in R – Line by line With each line copy and paste it into the console. Firstly a bit of setup, presuming you’ve installed the devtools package as described on the github page for the download, load AzureML and connect to the workspace specified in settings.JSON. To do this use the code below: ## Load the AzureML package. library(AzureML) ## Load the workspace settings using the settings.JSON file. workSpace <- workspace() Next we need to set the web service, this can be any web service created in Azure ML, for this blog we will use the web service created in this blog. The code is as follows: ## Set the web service created in Azure ML. automobileService <- services(workSpace, name = "Automobile Price Regression [Predictive Exp.]") Next we need to define the correct endpoint, this can easily be achieved using: ## Set the endpoint from the web service. automobileEndPoint <- endpoints(workSpace, automobileService) Everything is set up and ready to go, except we need to define our test data. The test data must be in the exact same format as the source data of your experiment. So the exact same amount of columns and with the same column names. Even include the column you are predicting, entering just a 0 or leaving it blank. Below is the test data I used: This will need to be loaded into R and then a data frame. To do so use the code below, make sure the path is pointing towards your test data. ## Load and set the testing data frame. automobileTestData <- data.frame(read.csv("E:\\OneDrive\\Data Science\\AutomobilePriceTestData.csv")) Finally we are ready to do the prediction and see the result! The final line of code needed is: ## Send the test data to the web service and output the result. consume(automobileEndPoint, automobileTestData) Running the predictive experiment – Short cut Below is the entire script, paste the entirety of it into top left R script. automobileRegression <- function(webService, testDataLocation) { ## Load the AzureML package. library(AzureML) ## Load the workspace settings using the settings.JSON file. amlWorkspace <- workspace() ## Set the web service created in Azure ML. automobileService <- services(amlWorkspace, name = webService) ## Set the endpoint from the web service. automobileEndPoint <- endpoints(amlWorkspace, automobileService) ## Load and set the testing data frame. automobileTestData <- data.frame(read.csv(testDataLocation)) ## Send the test data to the web service and output the result. consume(automobileEndPoint, automobileTestData) } Run the script by highlighting the whole of the function and typing Ctrl + Enter. Then run the function by typing the below code into the console: automobileRegression("Automobile Price Regression [Predictive Exp.]","E:\\OneDrive\\Data Science\\AutomobilePriceTestData.csv") Where the first parameter is the name of the Azure ML web service and the second is the path of the test data file. The Result Both methods should give you the same result: an output of a data frame displaying the test data with the predicted value: Wahoo! There you have it, a predictive analytic regression Azure Machine Learning experiment running through Visual Studio… the possibilities are endless!

Introduction to R Tools for Visual Studio (RTVS)

Introduction This blog is not looking at one or two exciting technologies, but THREE! Namely Visual Studio, R and Azure Machine Learning. We will be looking at bringing them together in harmony using R Tools for Visual Studio (RTVS). Installation As this blog will be touching on a whole host of technologies, I won’t be going into much detail on how to set each one up. However instead I will provide you with a flurry of links which will provide you with all the information you need. Here comes the flurry…! · Visual Studio 2015 with Update 1 – I hope anyone reading this is familiar with Visual Studio, but to piece all these technologies together version 2015 with Update 1 is required, look no further than here: · R – Not sure exactly what version is needed but just go ahead and get the latest version you can, which can be found here: · Azure Machine Learning – No installation required here, yay! But you will need to set up an account if you have not done so already, this can be done here · R Tools for Visual Studio - More commonly known as RTVS. The name is fairly self-explanatory but it allows you to run R through Visual Studio. If you have used R and Visual Studio separately before it will feel strangely familiar. Everything you need to download, install and set up can be found here: · Devtools Package - The final installation step is a simple one. Installing the correct R packages to allow you to interact with Azure ML. If you’ve used R to interact with Azure ML before you probably have already done this step, but for those who have not, all the information you will need to do so can be found here: Introduction to RTVS Once all the prerequisites have been installed it is time to move onto the fun stuff! Open up Visual Studio 2015 and add an R Project: File > Add > New Project and select R. You will be presented with the screen below, name the project AutomobileRegression and select OK. Microsoft have done a fantastic job realising that the settings and toolbar required in R is very different to those required when using Visual Studio, so they have split them out and made it very easy to switch between the two. To switch to the settings designed for using R go to R Tools > Data Science Settings you’ll be presented with two pop ups select Yes on both to proceed. This will now allow you to use all those nifty shortcuts you have learnt to use in RStudio. Anytime you want to go back to the original settings you can do so by going to Tools > Import/Export Settings. You should be now be looking at a screen similar to the one below: This should look very recognisable to anyone familiar to R:   For those not familiar, the top left window is the R script, this will be where you do your work and what you will run. Bottom left is the console, this allows you to type in commands and see the output, from here you will run your R scripts and test various functions. Top right is your environment, this shows all your current objects and allows you to interact with them. You can also change to History, which displays a history of the commands used so far in the console. Finally the bottom right is where Visual Studio differs from RStudio a bit. The familiar Solution Explorer is visible within Visual Studio and serves its usual function. Visual Studio does contain R Plot and R Help though, which both also feature in RStudio. R Plot will display plots of graphs when appropriate. R Help provides more information on the different functions available within R. Look for my next blog, which will go into more detail on how to use RTVS.


The LINQ Project is fast becoming one of the most anticipated integrated technologies in Visual Studio 2008 (ORCAS).  LINQ is simply a set of extensions to the .NET framework that encompass language-integrated query, set, and transform operations. It extends C# and Visual Basic with native language syntax for queries and provides class libraries to take advantage of these capabilities. Microsoft have seen that the next big challenge in programming technology is to reduce the complexity of accessing and integrating information that is not natively defined using OO technology. The two most common sources of non-OO information are relational databases and XML. Put simply, the new extensions, allow you to easily perform database type queries directly in code! Standard query operators allow queries to be applied to any IEnumerable<T> based information source.  Here are some sample snippets, taken straight from Microsoft's web site...   using System;using System.Linq;using System.Collections.Generic; class app {static void Main() {  string[] names = { "Burke", "Connor", "Frank",  "Everett", "Albert", "George",  "Harris", "David" };     IEnumerable<string> query = from s in names     where s.Length == 5    orderby s    select s.ToUpper();    foreach (string item in query)  Console.WriteLine(item); }} As you can see in this very simple snippet of code, the idea is to mimic syntax of T-SQL, and if it is as good as it looks so far we may have a lot more to say on it.... I'm sure there will be more to come!

ORCAS CTP..... What's in it?

  The March CTP release of ORCAS (Visual Studio 2008) incorporates a number of new and exciting technologies, as well as enhancing some of the older greats.   The current release of the IDE implements tools and functionality supporting LINQ (Microsoft's .NET Extensions, Language Integration Query), AJAX, Windows Presentation Foundation (codenamed CIDER), Windows Communication Foundation, Windows Workflow Foundation and the .NET framework 3.5 (downloaded separately).  According to Microsoft the latest framework will support applications built in .NET 2.0 and 3.0.  Further to this raft of newly integrated features, it also extends role-based and Team System collaboration features. Traditionally, Microsoft offers a Go Live license at beta 2, and that will almost certainly be the case here. Microsoft have saturated the market with new capabilities, and many of these platforms and technologies represent the culmination of years of work. More on LINQ and ORCAS in coming posts...

Dates for the diary

As reported by David Francis PerformancePoint has a target release to market date of September - seems a little ambitious with CTP4 only coming out a month earlier  Ben Tamblyn has also tickled our tastebuds with talk of a UK Business Intelligence Conference later this year. As Sach has reported SQL 2008 and VS 2008 get released early next year. And lastly and most definitely most importantly is the 7th July 2007 which is the date that Sacha and Julia's daughter Rosie Tomey was born!! Huge congrats mate :) 

Visual Studio 2008 and Javascript Intellisense

Visual Studio 2008 (Codname ORCA) has intorduced a new feature to aid with Web development, official release date has not yet been announced, however a BETA 2 is due to released later this year.  One of the most publicied, and simplistic enhancement to the Visual Studio 2008 enviroment is the somple introduction of intellisensce with Javascript.  You can read more about this here and here.

Visual Studio/BIDS Auto Hide Speed

If you use auto hide panes a lot you can change the speed they take to show and hide - the default setting is too slow in my opinion.  Select Tools->Option->Environment->General Then you can slide the scroller to your preference