Wednesday, November 30, 2016

What are IoT Gateways?

IoT Gateways connect IoT Devices or 'Things' to some kind of computing platform or to the cloud to provide intelligence for the IoT Solution. Although it is possible to directly connect the device to cloud if some form of intelligence to be derived before going to the cloud, a Gateway is a better solution.

If devices are TCP/IP enabled as in modern devices connecting them to the cloud is easy but there are legacy devices which are not TCP/IP enabled and therefore they need an intermediary device to connect to the cloud.

These are some of the reasons that requires an intermediary Gateway:

  • Support for legacy devices 
  • Run edge analytics- Reduces communication bottleneck by pre-processing (batching, filtering, compressing etc) 
  • Minimize Latency- processing near edge faster than in the cloud 
  • Conserve network bandwidth- 
  • Reliability of operation- 
  • Address security- Gateways can provide an extra level of security
Microsoft Azure IoT Gateway:
In the case of Microsoft technology, Microsoft Azure IoT Gateway helps developing a solution for on-premises computation augmenting the functionality provided by Microsoft Azure cloud services. Microsoft Azure IoT Gateway can be used to create a gateway solution.

More here:

Intel Corporation:
Intel IoT Gateway connects legacy and New systems for seamless and secure data flow using pre-integrated, pre-validated Hardware and Software Blocks

Of course both Legacy and New systems are supported. Perhaps there are more legacy devices than the new ones.

Intel IoT Gateway is built on McAfee and Wind River using their security and operating system technologies.


A variety of programming languages can be used:

Dell's Edge Gateway 5000 also provides Gateway solutions for transporting information from edge devices to the end point computing cloud.

Here are some specs for the Dell's Edge Gateway 5000

Intel® Atom™ E3825 1.33GHz | 2 cores
Memory: 2G (4x256Mx16 DDR3L)

Intel® Atom™ E3827 1.75GHz | 2 cores
4G (8x 256Mx16 DDR3L)

WindRiver: Helix Device Cloud for Linux
Ubuntu Snappy( and Windows IoT Industry

There are some useful video's on Dell's Gateway (you may find others on the Internet) that explains in simple terms what their gateway provides

Wind River
Wind River is an Intel company and call their Gateway as Wind River Intelligent Device Platform XT
They use Intel SoC and other Intel products.

Intel Products: Intel Baytrail, Intel Quark and Intel Core and propriety software(Wind River Intelligent Device Platform XT 3, Wind River Workbench, McAfee Embedded Control 6.6)

The trend is more towards Open Source than proprietary, a movement most suited for IoT development. Looks like Intel will make a lot of money with IoT.

Sunday, November 27, 2016

What is Polyfill as related to JavaScript?

In the early days of Internet (some 10 years ago or so) one had to write some kind of code that recognizes the browser and control the flow so that the audience is given the information that there was no browser support for the functionality.

An extreme kind of instruction was that the browser's JavaScript functionality was not on, etc.

A Polyfill ( piece of code, plug-in,add-in, script) is a browser fallback so that feature(s) works in a modern browser also works in older browsers which per se did not support what is supported in the modern browser.

Here is a comprehensive collection of HTML5 Cross Browser Polyfills.

The term Polyfills was coined by Remy Sharp to describe JavaScript shims that replicate standard API found in modern browsers for those older browsers that do not support.

Polyfills are created by developers to fulfill this requirement and one can create ones own Polyfill .

One of the most popular Polyfills is htmlshiv (

In IE versions of 9.0 and prior versions
and would have been parsed as empty elements. These tags however described nesting of elements and styling them would have been difficult. Html5shiv takes care of this by directing the page if opened in IE 9 to use the appropriate script:

Wednesday, November 23, 2016

How do you retrieve JSON formatted data from SQL Anywhere 17?

SQL Anywhere 17 is a SAP Database.  Some of the earlier versions were released by SYBASE.

In SQL Anywhere 17 you have three different ways of getting JSON formatted data / JSON document.

You can use the FOR JSON clause  with:

  • SELECT Statement
  • Subqueries
  • Queries having Group By clause
  • Aggregate Functions and
  • Views
The result is a JSON array consisting of:

  • Scalar elements
  • Objects
  • Arrays
There are three ways of calling the FOR JSON Clause:

  • For JSON Raw
  • For JSON Explicit
Note that SQL Server 2016 did get JSON support for the first time and has only For JSON Auto clause.

You run SQL Queries in Interactive SQL. Here is an example of a query that provides json document using the FOR JSON AUTO clause.


Tuesday, November 22, 2016

Do you want to create apps using streaming data?

Apps that use streaming data can be created with many programming languages including HTML, .NET, etc. with full SDK support.

According to this site,
"PubNub is a global Data Stream Network (DSN) and easy-to-use secure realtime communication API for IoT, mobile and web apps. The service scales to hundreds of millions of devices with 1/4-second worldwide latency and over 70 SDKs"

This is the stuff you should look into if you are interested in streaming data.

Here are developer support items that you may be interested in.




Server Desktops


How do you create a Power Bi report using JSON data? Continued

This is a continuation of my previous post.

Once the query is properly created turning it into a report in Power BI is quite easy. It takes only a couple of clicks. Here is the last query in the previous post.

The column data types are text and you need to change them. Clicking each column you can change the data type. The title_id is kept as is text. The qty was changed to whole number as well as the ytd_sales column. The Amount column was changed to decimal. Here are the allowed data types in PowerBI.

Now that the plot data is ready you can click the first menu item, Close & Apply in the Query editor.

Now you place Id in the Axis and the other three (Qty, Ytd_Sales, Amount) in the value fields to display a Clustered Column Chart as shown.
or a Multi-row card report as shown.

You can also choose any other chart type.

Monday, November 21, 2016

How do you create a Power Bi report using JSON data?

Power BI supports retrieving data from a variety of data sources that includes data in JSON format.

In the post you will learn how to retrieve JSON data from SQL Server 2016. You then retrieve this data into Power BI to create a report.

Creating a report requires massaging the JSON data retrieved using the Power BI user interface.

Creating JSON formatted data:

If you want to know how to retrieve JSON formatted data from your database in SQL Server go here:

Here is the data that is going to be used in this post. The data is from a query against the pubs database (original compatibility 100) whose compatibility is changed to work with SQL Server 2016.


You can run the query and save it as a file. Make sure there are neither extraneous characters in the file (like some sql message) nor does it have white spaces in side the JSON data.

Getting JSON Data into Power BI:

It is very easy to get the JSON data into Power BI. Just use the Get Data menu item in Power BI.

If you want to know details of how to retrieve JSON formatted data into Power Bi go here:

The data that was created in SQL Server is now retrieved into Power BI.

This is the first step. The data comes into Power BI as a list.

 This is the formula behind this.
You can convert this list into a table using the menu ribbon item To Table in Convert.
After this the UI appears as shown with the list converted to a table.


This is the formula behind this conversion:
= Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
When you click on a single  record this is what you see.


The first record has three items shown at the bottom.

Now you can expand the record by clicking on the icon shown on the column 1 to split it into the items by continuously clicking it.

First cancel the dialogue that showed up earlier. Next click on Column1 and then click the icon to expand the column.

This is what happens the first click.


After clicking OK, this is what you will see in the Power BI UI.

The formula behind the above is as shown here:
= Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"qty", "title_id", "titles"}, {"Column1.qty", "Column1.title_id", "Column1.titles"})
As you can see that Column1.titles is still a list (and therefore can be expanded).
This can be further split by clicking on the icon in Column1.titles.

When you click this icon, you will see the following change.
You will observe that the list goes over into Record and the following is displayed.


Click OK.
The display changes to this.


Observe that there are no more columns ot be expanded.
The formula now appears as shown:
= Table.ExpandRecordColumn(#"Expanded Column1.titles2", "Column1.titles", {"Amount", "ytd_sales"}, {"Column1.titles.Amount", "Column1.titles.ytd_sales"})

Renaming the columns

You can right click on a column and from the drop-down menu rename it. The renamed columns appear like this as shown below. while renaming a column, you may get a message Insert Step. Just click Insert for the message and go to the next column.


The formula behind the above is this:
= Table.RenameColumns(#"Expanded Column1.titles3",{{"Column1.qty", "qty"}, {"Column1.title_id", "title_id"}, {"Column1.titles.Amount", "titles.Amount"}, {"Column1.titles.ytd_sales", "titles.ytd_sales"}})
The various steps to get to this stage is shown here:


Now we have prepared the query and ready to create a report.

Continued in Part 2 here:
Creating a report using JSON formatted data with Power BI - Part 2

Thursday, November 17, 2016

How do you retrieve JSON formatted data from SQL Server?

You can use 'FOR JSON AUTO' at the end of your regular SQL Query to retrieve data from the query in JSON format. You can only use this in SQL Server 2016.

Here is an example from AdventureWorks2014 database in SQL Server 2016. The query is as follows. It is a simple query that return 17 rows of data of 'Persons' living in area code 808.

SELECT        Person.Person.BusinessEntityID, Person.Person.FirstName, Person.Person.MiddleName, Person.PersonPhone.PhoneNumber
FROM            Person.EmailAddress INNER JOIN
                         Person.Person ON Person.EmailAddress.BusinessEntityID = Person.Person.BusinessEntityID INNER JOIN
                         Person.PersonPhone ON Person.Person.BusinessEntityID = Person.PersonPhone.BusinessEntityID
                         Where PhoneNumber like '808%'
For JSON Auto


The response to this query is as shown here:

Wednesday, November 16, 2016

How do you use OLE DB connectivity in Power BI?

October update of Power BI allows you to connect to SQL Server 2016 using OLE DB Connectivity.

This post shows you how in just a few steps.

Launch Power BI from its shortcut on the desktop as shown. It may message you about auto recovery of files. Do not worry about it.


Click Get Data from the toolbar. Get data page is opened displaying all the data ssources from which you retrieve your data.


Click on 'Other' to reveal data sources contained therein as shown.


Now click on OLE DB and click Connect at the bottom to open the next dialog as shown.


At this step you are going to build the part of a connection string with non-credential properties.

Click Build and the Data Link Properties window gets displayed wherein you find a  OLE DB Providers for various types of data sources.


In the present case pick SQL Server Native Client 11.0 as native clients are generally faster. After highlighting SQL Server Native Client 11.0 click Next.

You need to select by entering the name of the server. Click on the handle tor eveal all SQL Servers as shown.


Click HOEDENTAK8\OHANA which is a named instance pf SQL Server 2016. Click Use Windows NT Integrated security for the log information. For the third items click on the handle for the database selection as shown.


Choose AdventureWorks2014 and click Test Connection. You should get the following message.


Click OK and you get returned to the From OLE DB window displaying the connection string as shown.


Notice that although you provided authentication information it was not registered (probably by design).

Click OK. The Navigator opens as shown.


You can choose table or tables to continue. Here it is looking at the Address for Person as shown.


This completes using the OLE DB connectivity using the Native Provider. It will probably allow you to create reports etc.

Two things bother me, one is the authentication and the other relates to the objects exposed. I did choose AdventureWorks2014 but it exposes everything on the server. Should it be like this?

Monday, November 14, 2016

How do you connect to SQL Server 2016 using OLE DB?

You would use OLE DB connectivity. You could use a file reference in your programming step to connect using the connection string.

OLE DB and ODBC connectivity are useful connectivity options that needs no emphasizing.

This post describes that you can connect to SQL Server 2016 in just a few steps.

Step 1:
Launch SQL Server Instance if it has not started, using Control Panel or otherwise.

Step 2: Create a new text document from desktop. Right click desktop  (empty region) and click New | Text Document.

Rename the document. If it is New Text Document.txt change it to, for example, SQLServer2016.udl (as in the present case).

You may get a warning that changing the file name extension may become unusable. Click Yes.
The document gets saved to the desktop with the name and extension you provided. The icon of the document will also be changed.

Step 3: Configuring the connection

Right click the somename.udl you created to display the following:


The file properties window has four tabs, Provider, Connection, Advanced and ALL.

In the Provider tabbed page you need to enter the Server Name or use the Refresh button to find all the regsitered servers as shown.


You have two options in connecting to a SQL Server instancce, Use Windows NT Integrated Security or use Specific Username and password.

The SQL Server Instance OHANA is configured for Windows Login. Click the first option. You need not provide username/password (they will be greyed out).

Select the database on the server using the drop-down as shown. Choose AdvenutreWorks2015.


If the SQL Server has not started you may get these warnings when you try to browse for the SQL Server.


After choosing the Server and the database you are basically done. You can test the connection by clicking the button and you would get this reply.


If you open the *.udl file (herein SQLServer2016.udl ) in a text editor such as Notepad you will see the following:
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AdventureWorks2014;Data Source=HODENTEK8\OHANA

Saturday, November 12, 2016

Can you return data in JSON format from a web service in SQL Server?

The short answer is yes provided we run our queries in SQL Server 2016.

We have seen in an earlier post using ODATA service to generate a report from Power BI.

These were some example OData services that were considered in the previous mentioned link.

Northwind traders here:

Adventure Works data here:

SQL Server 2016 supports JSON and it is possible to run a query in SQL Server Management Studio to return data from a ODATA service by running a query fashioned a shown.
SELECT '$metadata#Products(ProductID,ProductName)/$entity' AS '@odata.context',  
ProductID, Name as ProductName  
FROM Production.Product 
WHERE ProductID<400 br="">
This retrieves the following result:

Note that the size of text returned is limited by the settings.

Thursday, November 10, 2016

What is TypeScript and how different is it from JavaScript?

JavaScript is loosely typed. You can simply declare a varible. Loosely typed language can result in errors sooner or later in compilation. For exmaple if you declare the variable 'AGE', it could be written as an integer (25) or even as a string (twentyfive, twenty-five etc). In a typed language there is no ambiguity, 'AGE' will be declared as a varible of type int (for integer).

TypeScript is strongly (option) typed. TypeScript is Open Source. TypeScript does not enforce strong typing. Let us say you declare a variable 'Age' and later when you use it for first time use, such as age=25; the language takes 'Age' as an integer (a feature called Type Inference). Type Script also has advanced features like interfaces and generics.

The advantage of strongly typed language is that the compiler finds it easy to handle unambiguously resulting in easier maintenance and increased performance.

TypeScript 2.1 is in the process of being released with yet more new features. Check out the following post.

Tuesday, November 8, 2016

Where do I find user defined data types in a database if they exist?

Launch SQL Server Management Studio (herein Microsoft SQL Server Management Studio    Version:13.0.15600.2)

In the databases node identify the database. Under Programmability you will find the Types node. Types node has all the types including User-defined type as shown here:


Just to see what is required in creating a new User-Defined type right click one of them and look at its properties as showon for the user defined data type called OrderNumber shown here:


You can also define a default and rule. User-defined data types can also be created using T-SQL.

Once it is created you can use it in creating a table as shown here. The data-type will be available in the drop-down to choose.


Monday, November 7, 2016

Is there a JSON validator in SQL Server?

Sure there is if you are using SQL Server 2016.

The Transact-SQL IsJSON() tests whether a expression(string) is JSON valid. If it is valid you should get a 1 as return value, a zero(0) if it is not valid and a null if the expression is null.

How do you use it?

This is a json string, a very simple one:
{"wclass":{"student":["jay", "john", "sam"]}}
The following code snippet shows how you may use it:
declare @json nvarchar(150)SET @json=N'{"wclass":{"student":["jay", "john", "sam"]}}';
Select ISJSON(@json)

When you run this in the SQL Server 2016 query pane, you get the return value 1 (see image below).

Tuesday, November 1, 2016

How do you find Query Store related objects in SQL Server?

First off you should be using SQL Server 2016 because Query Store is a new feature in SQL Server 2016.

You should enable Query Store for the database you are going to use and you can enable Query Store using SQL Server Management Studio. You may review this post for enabling Query Store.

All objects in the SQL Server can be accessed using the sys.objects as shown:


If you filter the Sys.Objects as shown here you can find Query Store related objects.

Select * from Sys.all_objects
Where name like 'query_store%' or name like 'sp_query%' or name like 'query_context%'


The above query retrieves Views and extended stored procedures related to the Query Store.