Hands-on Learning Event in Honolulu 1 : Introduction to Structured Query Language

Database skills are essential in whatever organization and in whatever position and it is crucial that you have this in your arsenal (Resume')
Databases, organized repositories of information, have become indispensable in today's world. In this introductory course you will learn about relational databases and the basics of Structured Query Language (SQL) including sorting; grouping result sets; using DDL, DML, DCL, and TCL. All SQL statements will initially be written for one table. Most practical, modern and relational databases will include a large number of tables and SQL queries have to access information from several tables. This course will introduce you to querying more than one table. With this skill you will be able to query two or more tables in a database.
This is a hands-on course which will take you from installing SQL Server 2016 to learning the ins and outs of SQL Server Management Studio and of course a full dose of SQL. The course covers most of the requirements to take Microsoft's certification (98-364), the Microsoft Technology Associate. During each meeting, you will be assessed for your knowledge, competency and proficiency in SQL.

What's new in this session:

SQL Server Using Powershell
JSON and XML

 Section 1 - Apr 24 - May 24; TTh; 5:30 pm - 7:30 pm; 10 meetings (Register Online)

Get the March 2018 Power BI update

The update is out and you can get it from here:

You can also get it from Windows Store if you are running Windows 10.

Well what is new?


On reporting you have the following:

Report page tooltips (preview)
Bookmarking is now generally available
Display units and precision control for table & matrix columns
Turn off the visual header in reading mode for a report
Improved default visual placement

On custom visuals you have the following:

Custom visuals
Mapbox
User List by CloudScope
Timeline by CloudScope
KPI Chart by Akvelon
R DataTable
Outliers Detection
Data Insights by MAQ Software
Dumbbell Chart by MAQ Software
Clustering using OPTICS by MAQ Software

For data connectivity you have these new ones:

SAP HANA connector enhancements
DirectQuery multidimensional support is now generally available
SSL certificate validation support
SAP BW DirectQuery is now generally available

And you also have the following:

Improved error reporting
View previous errors you’ve encountered

Watch this video to learn the new stuff in the March 2018 update:

R package for Machine Learning - caret

The most trending topics these days are Artificial Intelligence and Machine Learning. Every day you get tons of news about AI and ML. R programming language is well placed to initiate you into these 'esoteric' topics. There is a learning curve and should you learn to scale the peaks there will be rewards. So much of 'carrot' talk.

In R, the package that does a lot of work for you as far as AI and ML are concerned is the package 'caret'. You should load this package and use its library to begin your AI/ML related activities. Of course there are more roads to Rome than one.

If have been working with R, you can launch R and install the package. If you are using Microsoft R (R Server/ R Client), you can launch the R GUI.

Run the install statement ass shown:

> install.packages("caret")

Installing package into ‘C:/Users/Owner/Documents/R/win-library/3.3’

(as ‘lib’ is unspecified)

also installing the dependencies ‘minqa’, ‘nloptr’, ‘RcppEigen’, ‘lme4’, ‘SparseM’, ‘MatrixModels’, ‘pbkrtest’, ‘quantreg’, ‘car’

After contacting the site to download a number of packages are downloaded and checked as shown here:

package ‘minqa’ successfully unpacked and MD5 sums checked

package ‘nloptr’ successfully unpacked and MD5 sums checked

package ‘RcppEigen’ successfully unpacked and MD5 sums checked

package ‘lme4’ successfully unpacked and MD5 sums checked

package ‘SparseM’ successfully unpacked and MD5 sums checked

package ‘MatrixModels’ successfully unpacked and MD5 sums checked

package ‘pbkrtest’ successfully unpacked and MD5 sums checked

package ‘quantreg’ successfully unpacked and MD5 sums checked

package ‘car’ successfully unpacked and MD5 sums checked

package ‘caret’ successfully unpacked and MD5 sums checked

Note that the 'caret' library requires ggplot2 which will also be loaded, if you already have it.

Please look up other R related posts on this blog:

http://hodentekhelp.blogspot.com/search?q=ggplot

Let us come back and look at how it may be used.

As is, this post is not 100% SQL Server except that I used the Microsoft R for this post.

You need Python to install the command-line query tool mssql-cli

In my previous post you have seen how to download mssql-cli. Installing mssql-cli requires a Python command-line tool for managing Python packages.

In this post you will learn downloading and installing Python that will enable to use PIP.

You downlaod Python from here:

https://www.python.org/downloads.

This site has a vast number of downloads not only for Windows but also for other platforms. For Windows 10 there are a number of them.

I installed the following:

python-3.7.0b2-webinstall

Python_00.png

Double click the downloaded file to begin installation.

Python_0

Python_2

You get these files into your computer:

Python_4

The PIP tool is in the Scripts directory in the above.

On a Windows 10 computer the following are installed in the All Programs

Python_6

You can get help on PIP as shown here by launching Python 3.7 from the above shortcut and when you are in the Python (>>>) use HELP:

Python_5

You have a new updated mssql-cli, the interactive command-line query tool for SQL Server

The public preview was announced in December last year. Now there is an update.

Read here.

You can download mssql-cli from here:


Using mssql-cli you get to do all this:

    T-SQL IntelliSense
    Syntax highlighting
    Pretty formatting for query results, including Vertical Format
    Multi-line edit mode
    Configuration file support

You can install mssql-cli if you have Python on your computer. For Windows you can install Python from here and make sure it is added to your PATH.

https://www.python.org/downloads/

Get started with latest release of Microsoft SQL Operations Studio

Read my previous articles (9) on previous versions.

Download the February (latest) release of Microsoft SQL Operations Studio here.

There are couple of enhancements over the previous release. The following has been fixed or added:

Added Auto-Update Installation feature
Added Connection Dialog ‘Database’ Drop-down
Added functionality for new query tabs keeping active connection
Fixed bugs in SQL Editor and auto-completion

This is the version I have now.

When I update, my version is:

When I launched my previous release, I could access the latest. Clicking on this I could install the latest release.

Set up preparatory stage.

I did not see a drop-down of all databases. It did not find the databases. It just reverted to default in the drop-down.

However, it could keep two query windows open at the same time, maintaining two different connections.

Accessing POWER SHELL in Microsoft SQL Operations Studio

You can access the console in Microsoft SQL Operations Studio by clicking this option in the User Interface as shown.

PS_SQLOPS_0.png

This displays the terminal.

PS_SQLOPS_1.png

The following version of Power Shell is supported:

PS_SQLOPS_2.png