Tuesday, October 10, 2017

Get the October 2017 Update of Power BI and test drive...

Power BI updates are regular and the latest update is in October 2017.

There are lots of new features as well as updates to Reporting, Analytics, etc.

This is the complete feature updates in this version and we will review some of them in future posts:

Reporting

Bookmarking (preview)
Selection pane and visual display controls
Bookmark links for shapes and images (preview)
Spotlight
Scatter & bubble markers
Increase the number of data points shown in scatter charts



Analytics

Quick measures for SSAS live connections
Sales from new customers quick measure
Cell-level formatting for multidimensional AS models



Data connectivity

Vertica connector (beta)
SAP BW connector: support for additional member properties



Other

Power BI Desktop in the Windows Store
Improved access to help content


Review the feature details here:
https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-october-2017-feature-summary/

But, first get the latest desktop from the above link. This allows you to download the msi program shown here.



Friday, September 8, 2017

Azure Cosmos can be test driven free now

Getting data from the Web in PowerBI - Loading Error Fix

In my previous post,there were no errors in visualization but one of the data points, namely for the year 2011 did not get loaded correctly. The reason was a reference to the (shown in a link) data that got embedded in the last column of the data as shown here:




In order to get the last column correctly I had to make a slight change using the transformation:
= Table.TransformColumnTypes(#"Renamed Columns",{{"2011", type number}})

Here are mode details of query:


Here is the query after changes.



Here is the data visualization settings window.





Here is the final result with data for Hinduism included.



Getting data from the Web in PowerBI

The latest update of PowerBI has access to data from very many sources shown here:


In this post getting data from the web will be described.

Getting data from the web is quite easy if the web page has tables of data and is accessible without providing authentication such as many of the public web pages like Wikipedia.

Get Data | Other brings up the list of the data sources the first of which is Web.


Click Connect at the bottom of the Get Data page. This brings up the window where you type-in the url of your source, in this case that of the Wikipedia link.


Click OK.

The Navigator gets displayed with all the different tables of information on the page as shown.



I choose the Population composition trend by religion between the years 1951 to 2011 for every 10 years.

The data that will be loaded into PowerBi is shown here.


I then click the load button at the bottom of the navigator.

In order to show the minority religions in India, I remove Hinduism although it has been on the decline in the duration considered (5.11% decrease from 1951 to 2011).

The unwanted rows can be removed using the following menu option.

I remove Hinduism from this data set.


You can remove 1 or more rows from the data set. I have also removed the data for 2011 as there appears to be an import problem of the data.

This image shows the visualization settings.


Now the final data for creating a clustered chart is as shown.

You surely agree, a chart tells a much better story than a bunch of data.

I will troubleshoot the data for 2011 which presented some problems in another post.

Tuesday, September 5, 2017

Go here to create apps that work with SQL Server

This is a very useful site that you should acquaint yourself with. You can create apps that work with SQL Server with all of the following:

C#
Java
NodeJS
PHP
Python

on

Windows
macOS
RHEL
UBUNTU
SLES (Suse...)



You need to start at this link:
https://www.microsoft.com/en-us/sql-server/developer-get-started/

Monday, September 4, 2017

STRING_AGG is a new string function in SQL Server 2017

You can create comma separated, concatenated strings from values in columns in a database. The concatenated strings are separated by a separator which gets added except for the end of the string.

The full syntax is here:

STRING_AGG ( expression, separator ) [ <order_clause> ]

<order_clause> ::=  
    WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] ) 


If you have SQL Server 2016, this will not work as shown here.


But it does recognize it as a function because I am using SQL Server Management Studio v17


Why not download SQL Server 2017 RC2 and try?

Download SQL Server 2017 Preview

SQL Server is just not on Windows OS only but it has widened its orbit.

These are the download links for installing /deploying SQL Server 2017 RC2 on various OS:

Deploying on Azure:
https://azure.microsoft.com/marketplace/?term=%22SQL+Server+vNext%22
OR
https://azuremarketplace.microsoft.com/en-us/marketplace/?term=%22SQL%20Server%20vNext%22

Linux:
Install on Red Hat Enterprise Linux 7.3
https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-red-hat

Install on Ubuntu Linux 16.04
https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-ubuntu

Install on SUSE Linux Enterprise Server v12 SP2:
https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-suse

macOS
Pull and run SQL Server on a Linux container on Docker
https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker

Windows OS
https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2017-ctp (180 days evalaution RC2)

Docker
Pull and run SQL Server on Windows container on Docker
https://hub.docker.com/r/microsoft/mssql-server-windows/

Pull and run SQL Server on Linux container on Docker
https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker

Sunday, September 3, 2017

Azure Cosmos DB is available in all Azure regions

This is by default.  In a new region, if Azure opens business, you can associate a Azure Cosmos DB with your account (barring Geo-fencing regions) and new regions are constantly coming on line.

Here are the present and up coming Azure regions:


Saturday, September 2, 2017

Get the August 2017 Power BI update

August 2017 update has many new features:

Here’s the complete list of August updates:

Reporting
  • Show values on rows for matrix
  • Color scales on font colors for table & matrix
  • Custom subtotal settings per level of matrix
  • Line styles and legend options
  • Scatter chart performance improvements
  • New custom visuals
    Dot Plot by MAQ Software
    Power KPI
    Funnel plot

    Beyondsoft Calendar
Analytics 
  • What if parameters
  • New scatter chart analytics features
    Symmetry shading
    Ratio line
  • New quick measure: weighted average
Data connectivity
  • Live connect to the Power BI service is generally available
  • Google BigQuery connector (beta)
Get the update from here:
http://powerbi.microsoft.com/desktop?WT.mc_id=Blog_Desktop_Update

PBIDesktop_x64 .msi

Here is a whole list of Services for which Power BI Connector (Live Connect) is available:

Scroll down all the way down to see all of them. Well, that's a handful. Of course you must be able to access any of these services to bring the data into Power BI.



Sunday, August 20, 2017

Enabling FILESTREAM on an instance of SQL Server Database Engine

You could configure FILESTREAM at installation time as shown here while configuring the Database engine for a SQL Server 2012 Express named instance.



FileStream_00

At the highlevel you need the Transact-SQL Access and if you need the file I/O access you need to enable it and provide a Windows share name as shown. If remote client access to FileSTREAM data is required you need to check this as well.

You could check whether FILESTREAM is enabled or not, by reviewing the instance properties in SQL Server Configuration Manager on the SQL Server Instance as shown for the instance OHANA.


The file share location is accessed by the UNC name as shown:



If FILESTREAM is not enabled you can enable in the Configuration Manager.

Saturday, August 19, 2017

Decrypting cypherText encrypted using EncryptByPassPhrase funtion

We saw in the previous post how to encrypt using the EncryptByPassPhrase() function.

We will use the same encrypted text and decrypt it using the same PassPhrase used to create it.

We use the reverse function DecryptByPassPhrase() which takes two arguments, the first is the PassPhrase and the second is the encrypted value (cyphertext). The PassPhrase generates the key for decryption.



MSSS2017\ENCRPTPSWD_04.pn

Saturday, August 12, 2017

Hands-on learning in Honolulu: COM2037 - Introduction to Structured Query Language (SQL)

The next session will be starting October 12, 2017. Register early.





Explore funding opportunity here:










Sunday, August 6, 2017

Encrypting email password in SQL Server

There is a simple but not most secure way of encrypting a password in SQL Server. It uses the T-SQL function EncryptByPassPhrase().

This function takes two arguments: PassPhrase and ClearText. You provide both the PassPhrase and the ClearText(this is your email password that needs to be encrypted using this function). The PassPhrase generates a TripleDes algorithm with a 128 Key bit length.

ClearText can be a variable of the following types:
nvarchar,
char,
varchar,
binary,
varbinary,
or nchar


PassPhrase can be of the following types:
nvarchar,
char,
varchar,
binary,
varbinary,
or nchar


The following code uses the following variables:
PassPhrase -> "Happy birthday to you"
ClearText->   "$#MyPassword#$"
---------
CREATE TABLE ENCRPTPSWD (EncryptedText varbinary(200))
go

declare @pswrd nvarchar(15)
set @pswrd = '$#MyPassword#$'--ClearText
INSERT INTO ENCRPTPSWD (EncryptedText)
values (EncryptByPassPhrase('Happy birthday to you', @pswrd))
go


The column ENCRPTPSWD contains the result of encrypting and is therefore of type varbinary. The phrase to de-encrypt is the phrase 'Happy birth day to you'.

Now if we access that column we see the encrypted value as shown.





SQL Server and two-digit year cut-off

Remember the millenium bug fiasco. It happenned in Year 2000.
This is what chronicled in Wikipedia:

"The Year 2000 problem is also known as the Y2K problem, the Millennium bug, the Y2K bug, or Y2K. Problems resulted because people, including programmers, reduced the four-digit year to two digits. This made the year 2000 indistinguishable from 1900"

Now we have a better interpretation for people using two digit years as far as SQL Server is concerned.

The default time span for two digit years in SQL Server is 1950-2049.

Two-digit year 49 is 2049, but two digit year 50 is back to 1950.

Probably you will start seeing it in your credit cards (may be?)

You may configure it in SQL Server 2017 using,

Exec Sp_Configure 'two digit year cutoff', 2038

In SQL Server 2012 Express it is enabled by default:



Read more here:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-two-digit-year-cutoff-server-configuration-option


Friday, August 4, 2017

Adding Excel Application to SQL Server Management Studio

I am using SQL Server Management Studio Version 17.1. You may user another version.

Launch SSMS 17.1

Click Tools | External Tools...

External Tools window is displayed as shown.



Replace the Title with a name of your own, herein Excel Application. The menu contents also
changes to the one you provided, herein Excel Application.

You need to provide the location of the executable in the Command box. In the present case the
executable is here:

"C:\Program Files (x86)\Microsoft Office\root\Office16\EXCEL.EXE"

Now you External Tools window should appear as shown.


Click OK.

Now you will find 'Excel Application' as shown in the Tools menu.


Now when you click this menu item, Excel Application is launched.

Tuesday, August 1, 2017

Usage of math operator Modulo in SQL Server

x%y is defined as the remainder of the division x/y. You can use modulo to find the minutes and hours in so many minutes.

Let us say we have a cconversion exercise to convert 123490 minutes into minutes and hours.
By long hand we would do this:

123490/60 and find the remainder
We get 2058.1666666667 with 0.1666666667 hours converts to 10
That is 2058 hours and 10 minutes.

We can do the same using modulo as shown.


OR

You can use a much shorter code as shown here: (note input data has changed to 1234901)



Monday, July 31, 2017

SQL Server 2012 (x86) and SQL Server 2016 Developer on the same x64 bit computer - Part 2

Earlier in Part 1 of this post we saw that the SQL Server Management Studio 17.1 easily allows you to work with SQL Server 2012 (x32) Express, the SQL Server Management Studio that installs with SQL Server 2012 Express Advanced has many GUI related problems. The problems were neither related to Display resolution nor to resizing of windows.

Laptop: Windows 10 Pro
SQL Server 2012: and SQL Server Express (x32) 2012

The following images require no explanation.











Difference between dbo and db_owner

This question pops up now and then.

dbo is a user and db_owner is a database role. They both are in the Security node in the Object Browser. The SQL Server Management Studio (SSMS) makes it abundantly clear.

dbo


dbo.png

db_owner

db_owner.png

It will be instructive to review their properties as shown here.

dbo - Properties


dboProps.png

db_owner - Properties


db_ownerProps.png

Tuesday, July 25, 2017

Generate database object scripts with mulit-platform tool mssql-scripts

In your SSMS on your computer you can use the Generate Scripts drop-down
menu to create scripts for database objects as shown here for Northwind database on SQL Server 2016 SP1.


The above is for SQL Server on Windows Platform. However, mssql-scripts tool provide the same functionality as Generate Scripts wizard on SQL Servers on Linux and macOS. Of course mssql-scripts would work on Windows as well.

Using mssql-scripts based on Python you can generate T-SQL scripts for objects on SQL Servers, Azure SQL database and Azure SQL Data Warehouse. The generated script is saved to a .sql file or on Linux can be piped to standard Unix utilities (sed, awk and grep). The scripts can be checked into source control systems as well.

The source code for mssql-scripter is found here:
https://github.com/Microsoft/sql-xplat-cli

Sunday, July 23, 2017

String function SOUNDEX

Soundex is how a name of a person is coded as used in the census for locating a person. It is based on how a name sounds rather than how it is spelled.

The name of the person is converted into a 4 character code, the first character in the code is the first letter of the person's name capitalized. The next three characters (they are number) that follow the rule stated here.

Rule 1:
For letters B,F,P,V                           use the number 1
For letters C,G,,K,Q,S,X,Z               use the number 2
For letters D,T                                  use the number 3
For letter L                                        use the number 4
For letters M and N                         use the number 5
For letter R                                        use the number 6

Rule2:
Disregard the letters A, E, I, O, U, H, W, Y (unless they are the first character in the name)

Example:
If the person's name is johnson, the first character in the soundex code is J

As to the rest of the characters in the code, using the above rules we have for johnson the Soundex code J525

The Soundex for Johnson is reduced to JNSN since O and H are not counted and N=5 and S=2 and J is the first character.

By the same rule, the Soundex code for Iowa is I000
Get more info from here:
http://www.archives.gov/research/census/soundex.html

and here:
http://www.ironrangeresearchcenter.org/genealogy/help/soundex/index.htm

Usage:



Testing a comparison operator in an SQL Query?

Let us say we want to know if a variable x is greater or less than another variable y. How do we write a query to test it?

One way to do this is as follows:

declare @x int
set @x=5
declare @y int
set @y=10
    IF @x < @y
 print 1
    ELSE
 print 0

Wednesday, July 19, 2017

Regarding ntext, nchar and nvarchar

These are basically used for textual information related data types. There is a strong recommendation not to use ntext as it is not going to be supported.

These are their definitions from Microsoft Documentation.

ntext (National Text):
Variable-length Unicode data with a maximum string length of 2^30 - 1 (1,073,741,823) bytes. Storage size, in bytes, is two times the string length that is entered

nchar [ ( n ) ]
Fixed-length Unicode string data. n defines the string length and must be a value from 1 through 4,000. The storage size is two times n bytes. When the collation code page uses double-byte characters, the storage size is still n bytes. Depending on the string, the storage size of n bytes can be less than the value specified for n. The ISO synonyms for nchar are national char and national character..
1
nvarchar [ ( n | max ) ]
Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes. The ISO synonyms for nvarchar are national char varying and national character varying.

ntext is supposed to be discontinued, but in actuality it is still present as a choice in data types listing even in SQL Server 16.


Sunday, July 16, 2017

Restoring the Northwind database from its backup

A backup of Northwind database was obtained from the Codeplex site and was saved to one of the folders on a Dell computer with Windows 10 OS. The computer also has SQL Server Management Studio (v 17.1). You should be able to restore using the SQL Server Management Studio installed when you installed the SQL Server 2012 Database engine.

Follow these steps to restore the Northwind database to an instance of SQL Server 2012 (x86) installed on the same computer.

Step 1. Start SQL Server Management Studio v17.1 (Run as administrator)

The SSMS is version 17.1 and Hodentek9\PCATT is a SQL Server 2012 Express

Step 2. Right click the Databases node highlighted in the PCATT isntnace as shown.




RestoreDB_01

Step 3: Click Restore Database...

Restore Database window is displayed as shown.


RestoreDB_02

Step 4: The Default Source is Database and it is greyed out as shown. Chnage it to Device. The Restore Database gets changed as shown.


RestoreDB_03

Step 5: Click the ellipsis button along 'Device' in the above image.

Select backup devices window shows on top of Restore Database window as shown.


RestoreDB_04

Step 6: Click Add button in Select backup devices window.
Locate Backup File window gets displayed as shown.


RestoreDB_05

Usually the 'backup files with extension .bak' are found in the following directory in the case of x32 bit SQL Server.
C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.PCATT\MSSQL\Backup

However, for this exercise it is stored in a different location.

Step 7: Now browse to that location and highlight the Northwind.bak (A backup file which came from a Microsoft site) as shown.


RestoreDB_06

Step 8: Click OK. The file path is entered in the Select backup devices window as shown.


RestoreDB_07.png

Step 9: Click OK
You are returned to the Restore Database - Northwind as shown.


RestoreDB_08.png

Step 10: Click OK in the above.

Microsoft SQL Server Managment Studio message reports that the database
'Northwind' restored successfully.


RestoreDB_09.png

Step 11: Click OK to the message. Verify that Northwind database is in the SQL Server 2012 instance Hodentek9\PCATT


RestoreDB_10.png


Bye