Azure Cosmos can be test driven free now

Read the posts.

Global scope of Cosmos:
https://hodentekmsss.blogspot.com/2017/05/globally-distributed-azure-cosmos-db.html

Cosmos DB widely available:
https://hodentekmsss.blogspot.com/2017/09/azure-cosmos-db-is-available-in-all.html

Listen and watch this video:



Just go this link:
https://www.onmsft.com/news/microsofts-new-distributed-database-azure-cosmos-is-now-free-to-try

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.

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/

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.3https://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

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:

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.