Constructing Wide World Importers database - Part 1

In order to get this database first go here ( https://github.com/Microsoft/sql-server-samples ).

There are three releases of which the first one Wide World Importers sample database v1.0 is the one you should get.

Before you begin to do this task, verify that you have done a dependent tass (upgrading to SP1). If not, go here and complete the task.

Click on that link. This should take you to this site here .

I will show you the easy way to get this task done. SQL Server has both Backup / Restore hookups that you can use.
Download WideWorldImporters-Standard.bak

From Databases choose Restore Database....

Restore Database screen gets displayed. Click on Device and click the ellipsis button to locate the backup file.

  

Go locate the backup file you downlaoded as shown (your location will be different).

Click OK. The file can now be accessed by the processor.

Click OK. After a short while of processing you should see this message.

 Wide World Importers is now in your named instance of SQL Server 2016 SP1 as shown.

It does come with data as shown.

 

Don't you think it was easy!

Download and apply SQL Server 2016 SP1

Getting the SQL Server 2016 SP1:

 I wanted to construct the Wide World Imports database on SQL Server 2016 and found that SP1 is a requirement. Hence, I needed to first apply SP1 to the existing instance.

Go to this link, select the language and click Download

The file name is ENU\SQLServer2016SP1-KB3182545-x64-ENU.exe (551.8 MB).
This SP1 contains SQL Server 2016 RTM Cumulative update 3(CU3).
---------------
Version:
13.0.4001.0
File Name:
ENU\SQLServer2016SP1-KB3182545-x64-ENU.exe
Date Published:
11/15/2016
File Size:
551.8 MB
--------------------
The supported OS are:

Windows 10 , Windows 8, Windows 8.1, Windows Server 2012, Windows Server 2012 R2, Windows Server 2016

  For complete system requirements, please reference the detailed Systems Requirements page
        6 GB of available hard disk space for the Service Pack update, 10 GB for a Slipstream installation
        Service Pack 1 can be applied to any of the following Microsoft SQL Server 2016 editions:

            Microsoft SQL Server 2016 Enterprise
            Microsoft SQL Server 2016 Developer
            Microsoft SQL Server 2016 Standard
            Microsoft SQL Server 2016 Web
            Microsoft SQL Server 2016 Express

Run (as Administrator) the downloaded file. In the beginning I had some problem and ran the compatibility program. Here are some of the screen shots. It took quite a while with the R Service related step and the final updating step, otherwise it was without any trouble.

 

 

 

About in SSMS 2016
Microsoft SQL Server Management Studio                        13.0.15600.2
Microsoft Analysis Services Client Tools                           13.0.1605.86
Microsoft Data Access Components (MDAC)                   10.0.14986.1000
Microsoft MSXML                                                              3.0 6.0
Microsoft Internet Explorer                                                 9.11.14986.0
Microsoft .NET Framework                                                4.0.30319.42000
Operating System                                                                6.3.14986
----------------------------------------------

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:
http://hodentek.blogspot.com
http://hodentekMSSS.blogspot.com
http://hodentekHelp.blogspot.com

The next tasks for you are:
Apply SP1 before you construct a database (WWI):

http://hodentekmsss.blogspot.com/2017/02/download-and-apply-sql-server-2016-sp1.html

Construct WWI database on the named instance by any of the following methods:

http://hodentekmsss.blogspot.com/2017/03/using-bacpac-file-to-construct-wide.html

http://hodentekmsss.blogspot.com/2017/02/constructing-wide-world-importers.html

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.

Creating Server Code Script on Appery.io to access a Collection

In creating a mobile app based on back-end data in Appery.io, 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 appery.io 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.

ApperyIO7_00.PNG

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

ApperyIO7_02.PNG

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

ApperyIO7_03.PNG

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.

ApperyIO7_05.PNG

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

ApperyIO7_06.PNG

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

ApperyIO6_00

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.

ApperyIO6_01

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

 

 ApperyIO6_02.PNG



Click new service in the screen above.

The New service page opens as shown.

ApperyIO6_03.PNG

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

ApperyIO6_04.PNG

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.

ApperyIO6_05.PNG

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:

ApperyIO6_06.PNG

Choose BODY.

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

ApperyIO6_07.png

Click Generate. Generate Service Response window opens.

ApperyIO6_08.PNG

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

ApperyIO6_09.PNG

Click Import Response.


The Response is now in the visual designer.

ApperyIO6_10.PNG

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.

ApperyIO6_11.PNG

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

ApperyIO6_12.PNG

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.

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:

OData:Atom
OData:JSON

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.


URLData_00.png

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

http://services.odata.org/northwind/northwind.svc

The http: is the HOST
http://Services.odata.org/northwind/northwind.svc: 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)

URLData_01.PNG

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:
http://services.odata.org/V4/Northwind/Northwind.svc/Products

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

URLData_02.png

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

URLData_02b.png

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:
http://services.odata.org/V4/Northwind/Northwind.svc/Products?$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 


URLData_03.png(FF)
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".
 http://services.odata.org/Northwind/Northwind.svc/Customers(%27ALFKI%27)/$links/Orders

URLData_04 FF

But in ME you would get the following:

URLData_04b.PNG

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

URLData_05.PNG

These are the columns in the Orders table.

URLData_06.PNG

Importing a CSV text file into Appery.io

Appery.io 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 Appery.io and Click on Databases tab.

ApperyIO4_0.PNG

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

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

ApperyIO4_1.PNG

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

ApperyIO4_2.PNG

Importing a collection: A Collection in Appery.io 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.

ApperyIO4_4.PNG


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

ApperyIO4_5.PNG

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

ApperyIO4_6.PNG







http://hodentekmsss.blogspot.com/2017/02/importing-csv-text-file-into-ms-
access.html
http://hodentekmsss.blogspot.com/2017/02/importing-text-file-into-ms-access.html
http://hodentekmsss.blogspot.com/2017/01/importing-csv-file-into-sapss-sql.html
http://hodentekmsss.blogspot.com/2016/12/report-based-on-text-file-using-power-bi.html