Friday, February 24, 2017

WWI a new sample database with SQL Server 2016

There have been a number of sample databases that debuted with various SQL Server versions starting from pubs, Northwind, Foodmart and AdventureWorks

Northwind employees have been in the limelight for years and it is time for them to retire. Some 9 employees managed the whole business and now businesses have gone global and IOT and robots are standing in queue.

In this background Microsoft has introduced the Wide World Importers, WWI for short. You can get an overview here , or read on.

This is how Microsoft describes WWI's business:

"Wide World Importers (WWI) is a wholesale novelty goods importer and distributor operating from the San Francisco bay area.

As a wholesaler, WWI’s customers are mostly companies who resell to individuals. WWI sells to retail customers across the United States including specialty stores, supermarkets, computing stores, tourist attraction shops, and some individuals. WWI also sells to other wholesalers via a network of agents who promote the products on WWI’s behalf. While all of WWI’s customers are currently based in the United States, the company is intending to push for expansion into other countries.

WWI buys goods from suppliers including novelty and toy manufacturers, and other novelty wholesalers. They stock the goods in their WWI warehouse and reorder from suppliers as needed to fulfill customer orders. They also purchase large volumes of packaging materials, and sell these in smaller quantities as a convenience for the customers.

Recently WWI started to sell a variety of edible novelties such as chilli chocolates. The company previously did not have to handle chilled items. Now, to meet food handling requirements, they must monitor the temperature in their chiller room and any of their trucks that have chiller sections."

In an importing and redistributing business the Workflow is very well defined to address all exigencies and so it is with WWI as charted by Microsoft:

Workflow of stocks:

    WWI creates purchase orders and submits the orders to the suppliers.
    Suppliers send the items, WWI receives them and stocks them in their warehouse.
    Customers order items from WWI
    WWI fills the customer order with stock items in the warehouse, and when they do not have sufficient stock, they order the additional stock from the suppliers.
    Some customers do not want to wait for items that are not in stock. If they order say five different stock items, and four are available, they want to receive the four items and backorder the remaining item. The item would them be sent later in a separate shipment.
    WWI invoices customers for the stock items, typically by converting the order to an invoice.
    Customers might order items that are not in stock. These items are backordered.
    WWI delivers stock items to customers either via their own delivery vans, or via other couriers or freight methods.
    Customers pay invoices to WWI.
    Periodically, WWI pays suppliers for items that were on purchase orders. This is often sometime after they have received the goods.

You will be hearing more about WWI in my blogs:

Thursday, February 23, 2017

Azure SQL Database even more secure with threat detection

This cyber security will be coming soon in April 2017 offering protection from threats in addition to more sophisticated search capabilities. If you are using Azure DocumentDB then you are compliant with the latest security measures.

Securing Azure SQL Database takes just a few minutes and requires no expert knowledge of how it is done. The feature learns profiling and discovering anomalous activities and probably with built-in AI.

This is likely to cost an extra $15/month/server according to this site here .

Read here for more.
Read some more here.

Wednesday, February 15, 2017

Creating Server Code Script on to access a Collection

In creating a mobile app based on back-end data in, server code that accesses a collection can be used as shown here.

In this post we start with a collection we created by importing a CSV file, FFD.csv.

We now create a Server Side script to access this collection.

In the platform  click on Server Code tab to open the user interface as shown.  In the script editor there may be some default code. Remove this script.


From the Snippets side click and add Retrieve all Objects. This inserts a line of code into the editor as shown below:

var result = Collection.query("dbId", "collectionName"); 

You need to insert correct values for the arguments. The JsonTest was the database in which the imported collection was saved. The imported CSV file was named as a collection with the name 'Custo'. The dbId parameter was found from the Settings tab for the JsonTest database


If the script is properly formulated you could Run the script. If there are errors you would see it in Trace info.


After the first line of code click return for a new line. In the Script Editor add another line of code to appear below the first line. For this choose the snippet, resposeSuccess.

This inserts this code:

Apperyio.response.success({"param1":"value1"}, "application/json");

Modify this code as shown below:

Apperyio.response.success(result, "application/json");

Click Save.


Click Save and Run. You should the response to the script in the panel as show below.


Tuesday, February 14, 2017

Microsoft Cloud architects make most money

According to a post from Bekker's blog here Cloud architects make the most money compared to other IT professionals except in Canada and Middle East/Africa.

Review this graphic from Bekker's blog for more details:

Creating a Service Code Service Endpoint in Appery.IO

Appery.IO has a neat visual interface to create a REST API service (endpoint). There are two options for creating  a service. In the first option you can generate REST API automatically for a database table or a Custom REST API. In the former, you will be asked for a connection to a table. At present it appears that connecting to a named instance of SQL Server table is not possible.

In the following post, I describe creating a service endpoint using server side code for a Collection(similar to a table). The same interface can be used for creating service flow with advanced logic to include REST API, SOAP Services, Custom SQL Queries, etc. 

API Express create new project FFD


Note: I am not sure why there is a textbox below the Create New Project button as the following screen asks for the name of a New Project.

In the New project window enter the name.


New project is created. Click on SQLData in the project page to open the SQLData page as shown.


Click new service in the screen above.

The New service page opens as shown.


Provide a Group name (JanSvc2_14) and choose Custom REST API and click create.


Drag and drop the Server Code (SC) from the PALETTE to the empty box as shown. If you want to remove it you click the red circle with x.


In the PROPERTIES -SERVER CODE click the handle and select the only script there -loadGoodsScript. For the Script Query Parameters click on the handle to reveal the following:


Choose BODY.

For SCRIPT BODY place check mark for Use previous component and click Generate.


Click Generate. Generate Service Response window opens.


Click Run Server Code API.
The code is run and the result is displayed as shown.


Click Import Response.

The Response is now in the visual designer.


Now you can test the API. Click TEST in the menu. The Service Test page is opened displaying the endpoint and a Test button as shown.


Click Test.
The Test succeeds otherwise there would be an error which you can debug by going to the Settings.


Copy the endpoint in the above and paste it to the address of a browser and you should see this.

Now you have a endpoint to work with.

Sunday, February 12, 2017

URL Accessing data on a REST based OData service

This is often a easy way to find database data by sending queries to the service site.
There are two formats of resource representations:


The document OData:Operations describes the actions that can be performed on the URLs. Although most sites follow the conventions defined by specification not all of them do.

The following is a picture of the URL Components from the the OData site that is relevant to our discussion.


As an example let us take the Northwind OData service at this site here:

The http: is the HOST is the SERVICE ROOT

The SERVICE ROOT is required to find all the resources provided by the OData service.

Simply put, if you access the Service Root you would see the following in Microsoft
Edge (Microsoft Edge 39.14965.1001.0)


This is essentially the same as what you see in Mozilla Firefox

URLData_01b.PNG Mozilla (Firefox 51.0.1 (32-bit))

The collection in either of these you see are the tables in the Northwind database exposed as a service.

Accessing the table:

Now how do we access one of these tables. Let us say the Products table. All you have to do is to append Products to the Service Root as in the following:

The Firefox browser would display this:
Only part of the browser display is in this image.


However in Microsoft Edge you would get a response asking you whether or not you want to save.


I have found this is does not always happen and displays the same information as the Firefox.

You may query the browser to format it in JSON with the following instruction:$FORMAT=json

Filtering the table:
After getting to the Products table, let us say you want to get only the first Product which has ProductID=1, then you change the URL to 

In ME you get the same results above.

You can further filter the table.  For example you can find all the Orders placed by the Customer whose CustomerID="ALFKI".$links/Orders

URLData_04 FF

But in ME you would get the following:


Looking at the Northwind database, this result is the same as the query.


These are the columns in the Orders table.


Wednesday, February 8, 2017

Importing a CSV text file into supports importing data files in CSV or JSON formats. In this post you will learn the procedure to import a CSV text file.

The file we are going to import is FFD.CSV saved on the files/folders of the local computer which has been imported recently into other data sources such as MS Access 2016 here and here; SAP SQL Anywhere 17; and Power BI. The data file is created by exporting the data from a Query on SQL Server 2012 using the Export/Import Wizard.

Log in to and Click on Databases tab.


There are already two databases under my account. Click Create new database.

Create new database window appears. Insert name, herein it is Jan8_2017.


Click Create and the database is created as shown. The database is empty and has no collection.


Importing a collection: A Collection in is like a table in a relational database.
Now we can either create a collection or import one.

Since we are importing the data we click on the link, import a collection.

The Import data screen is displayed as shown. Provide a name for this collection - SQLServerData. Click on Browse... and locate the file FFD.CSV on your computer.


Click Import.
Data in the FFD.CSV file is now in the collection in the database Jan8_2017


Note that some of the letters in the ProductName came with a ? mark as these are not characters in the English language set.

Here is a sample of the data in the FFD.CSV file