Francesco

Francesco Sbrescia's Blog

Objects detection with Data Lakes Analytics

In this blog I’m going to show one of the advantages of linking Data Lakes Analytics with Machine Learning.

We’ll be uploading a series of images to the Data Lake, we will then run a USQL script that will detect objects in the images and create relative tags in a text file.

First of all you need an instance of  Data Lake Store and one of Data Lake Analytics, once these are up and running we need to enable Python/R/Cognitive in your Data Lake Analytics instance (here is a blog to help you out on this).

First things first, we need to put an image in our Data Lake Store, following Azure Data Lake best practices I put the images in my laboratory subfolder.

LabFolderStructure

Once our images are in place we need to create a script, in your Data Lake analytics instance click on New Job

newJob

This will open a new blade with an empty script, let’s give our new Job a name “ImageTagging”.

In order to use Image tagging we need to import the relevant ASSEMBLIES:

REFERENCE ASSEMBLY ImageCommon;
REFERENCE ASSEMBLY ImageTagging;

Next we need to extract information (location, filename etc.) on the image file(s) we want to analyse, in this case we’ll process all images in the specified folder.

@images=
EXTRACT FileName string, ImgData byte[]

FROM @"/Laboratory/Desks/CSbrescia/ImageTagging/{FileName:*}.jpg"
USING new Cognition.Vision.ImageExtractor();

The following step is where the magic happens, the script analyses all the images located in the folder indicated before, it detects all objects present in each image and create tags; here is the structure of this “variable”:

  • Image name
  • Number of tagged objects detected
  • A string with all the tags
@TaggedObjects=
PROCESS @images
    PRODUCE FileName,
            NumObjects int,
            Tags string
    READONLY FileName
    USING new Cognition.Vision.ImageTagger();

Now we can write our variable with all the tags to an output file

OUTPUT @TaggedObjects

TO "/Laboratory/Desks/CSbrescia/ImageTagging/ImageTags.tsv"
   USING Outputters.Tsv();


Here are the images I used in this example

FruitAmsterdamRSZ

And here is the list of objects detected

taggedObjs

 

In conclusion, we have created a pretty handy tool for automatic image tagging using Data Lake with very little knowledge required on the background processes involved.

To be noted that there seems to be an image size limit, i had to resize all images to about 500 kb. 

 

Creating a tiled KPI in SSRS 2016 CTP 3.3

SSRS 2016 CTP 3.3 has introduced the new web portal which features Kpi’s and mobile reports.

Kpi’s are small tiled reports pinned to the main SSRS web portal, they give users a quick glance on performance.

This blog will focus on creating a KPI tile based on the Microsoft Adventure works data warehouse installed on a SQL Server 2016 CTP 3.3, with the latest SSDT and report builder installed.

As of CTP 3.3 the only way of creating a new KPI is through report builder (v 3.0).

This blog assumes you have already created and deployed a Data Source to the SSRS server.

1)      Open report builder, click on "File" and then on "new dataset": select the Data Source of your choice and hit create. 

2)      Once in the dataset editing window, select a stored procedure that you have previously created  (the one i used can be found here: spObtainInternetSalesByYearKPIV1.sql (386.00 bytes)). Go to file and then save the dataset with a name of your choice on the SSRS server.

 

 

 


 

3)      Go to the SSRS web portal, click on new and then on KPI

 

4)      In the new window enter a name for your KPI and then go to “Trend set”, in the drop down select “Dataset trend” and in the “Pick dataset trend” box click on the three dots.

You will be prompted with a view of all SSRS folders, navigate to the folder where the dataset you created earlier is and click on it.

A new window will open with a preview of the data from your dataset, select the column your KPI is based on, in my case is “SalesKPI” and click “OK”.

 


 

5)      Go back to the SSRS web portal where you’ll see the newly created KPI.

The value displayed on the KPI is a sum of all values of the column we selected earlier. In our example this doesn’t say much. You’ll also notice the KPI colour is green despite sales falling sharply.

To change the KPI displayed value and the background colour we need to change the underlying stored procedure. At this stage no calculation, filtering or any kind of logical operation can be done on the values returned by the datasets.

6)      After modifying the stored procedure (spObtainInternetSalesByYearKPIV2.sql (939B)you need to update the dataset and replace the one we created earlier.

7)      We can then head back to the SSRS web portal and change the KPI by clicking on the 3 dots and then on “Manage”. Once in the KPI edit window:

a.      Set “Value” to “Dataset field” and “Pick Dataset field” to the name of your dataset, if you used my stored procedure select the column “KPIFaceValue" and click “OK”.

b.      Do the same for “Goal”, Status and “Trend Set” selecting the columns “target”, “KPIColour” and “salesKPI” respectively.

c.      Apply all changes and head back to the SSRS web portal.

8)      We now have a modified KPI with a target progress(expressed as a percentage) and a colour that indicates the status of our measure

Conclusions

The new tiled KPI’s are a nice addition to SSRS and give the tool a more modern look. This feature is clearly still in development stage band things might change with new releases of SSRS 2016.





Deploying to SQL SERVER 2016 from source control

This is a quick blog to help others avoid what I went through while trying to deploy a DB from TFS to SQL Server 2016 with no success.

Apart from the usual option of backup/restore there was no other viable way of deploying through source control. Visual Studio would throw an “unable to connect to target server” error despite the connection being successfully tested.