Thursday, June 22, 2017

New data connectors in the June 2017 Power BI

Although some of the new connectors are generally available, some are in BETA. Here is a complete picture
of the data connectors available in Power BI (June 2017).

Here is complete picture of all data connector updates from Microsoft site. The ones highlighted are the new ones.

Here is a connector update video from Microsoft site.

Wednesday, June 21, 2017

Data Bars are new in Power BI (June 2017)

Looking at a bunch of data does not give a feeling for the big and small values without making comparisons by reasoning. This next table is trivial in that you can easily see the small and big values. However I have chosen this to make the explanation easy to understand.

This comparison is built into the idea of presenting data bars in Microsoft programs such as MS Excel and Microsoft SQL Server Reporting Services 2012.

In the June 2017 version of Power BI Desktop which is in preview 'Data Bars' are supported. What Data Bars do is to show visually how big or small data values are. Here is an example from my previous post how the data is visualized after adding Data Bars.

Four types of Data Bars are shown in the above: Data Bars, Color Scales, Icon sets and 'Above average' types.

In the present version this is how 'Data Bars' feature is implemented.

I have imported the same data as in the previous post into Power BI using 'Get Data'.

I added the two fields 'State' and 'Item Value' into the fields as shown above.
 I had to rename 'State' to 'St' as 'State' may be keyword in Power BI which automatically formats as s map (this was on another page of the view)

This is probably some 'bug' in the software in this Preview version here.

Now right click the 'Item Value' in the fields as shown.

In the Conditional formatting menu choose 'Color scales'.

Here you can choose colors to represent Minimum, Middle and Maximum values and click OK.

That is all there is to it.

Tuesday, June 13, 2017

Power BI Report Server and Power BI Premium

From creating reports to distribute and consume them across devices using Power BI you can use the premium version. You publish reports to Power BI Report Server which is similar to Reporting Services report server to organize and manage access to reports and update when needed. With Power BI you can handle not only today's needs but also future shift to the Cloud.

Power BI report comes with Power  BI Premium. It became generally available last month. It costs a minimum of $625/month to embedd Power BI Premium into apps.

Get you free trial to check it out!

Power BI Report governance behind your on-premises reporting behind corporate firewall is made easy.Power BI is compatible with the Cloud.

"With Power BI apps, now in preview, you can easily deploy a collection of purpose-built dashboards and reports to a large number of business users and empower them to make data-driven decisions. "

How much does it cost to use Power BI?

Well. Microsoft has this online calculator to find out for yourself how mch it would cost.
Play with the app to estimate your cost.

Sunday, June 4, 2017

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

Course summary:

Databases - organized repositories of information have become indispensable. Knowledge of databases is a must for professionals and in fact even more relevant since the emergence of Big Data in today's world.

In this introductory course you will learn all 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 then 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; learning the ins and outs of SQL Server Management Studio and of course a full dose of SQL. You will be both coding as well using Graphical User Interface during this training.

During each meeting the students will be assessed for their knowledge, competency and proficiency in SQL.

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 2012 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.
Section 1 - Jul 6 - Aug 8; TTh; 5:30 pm - 7:30 pm; 10 mtgs (Register Online)

Microsoft SQL Azure Database: SQL Server in Microsoft Cloud
Windows PowerShell for SQL Server
SQL Server 2016

You may also contact the instructor: Dr. Jayaram Krishnaswamy at

Friday, June 2, 2017

Adding Full-Text feature to an existing installation of SQL Server 2016

If you need to do a text-based search in SQL Server you need to install this feature. Chances are that you forgot...

It is a good practice that you only install what you need, otherwise you may end of reserving resources for things you do not need. However often times you need to add a feature to an existing installation of SQL Server 2016. There is no better place to go than the SQL Server Installation Center.

This slide show shows how you may do this.

Thursday, May 25, 2017

Creating a default Full-Text Catalog

A full-text catalog is a logical container where a group of full-text indexes are stored. A full-text catalog is needed before you can create a full-text index. Full-text catalog is a virtual object and does not belong to the file group.

In order to text search an full-text index will be needed. For example, the Categories table in Northwind does not have a full-text indexed column. If you try to create a Full-Text indexed column you will get this error:
Msg 9967, Level 16, State 1, Line 11
A default full-text catalog does not exist in database 'Northwind' or user does not have permission to perform this action.


You can create a Full-text catalog using the SQL Server Management Studio or using Transact-SQL(T-SQL)
Here is how it is created for Northwind database in SQL Server 2016 using T-SQL

USE Northwind
Create FULLTEXT CATALOG ftCat_Nwind as default

Before the above statement was run there was no Catalog:

After the above statement was processed the named catalog was created:

You can access the properties as shown here

All tables and views are eligible and you can assign the objects to the Catalog by transferring using the > button.

Creating a Full-Text catalog for Northwind using SQL Server Management Studio is easy. Right click Full-Text Catalogs in the Storage node and provide a name for the catalog and a owner. Choose the accent sensitivity and click OK to create.

The Catalog gets created as shown.

Although SQL Server 2016 SP1 allows you to create a Full-Text Catalog, you will find it missing in the System Views as in the following query.

This feature has not improved even from the older versions. It has not been fixed and the error messages that you get such as the following does not give a clue.

Property IsAccentSensitive is not available for the Full-Text catalog <catalog name>
Property PopulationStatus is not available for FullTextCatalog <catalog name>

The reason for this error is that the Full-Text feature was not installed in the first place.
Watch the video and review features not installed:

TABLOCK hint in an Update SQL Query

TABLOCK is one of several table hints in SQL Server which includes the following:


Table hints can be used with any of the following operations:
Applies to:

Here is a query that updates a value in the Northwind Shippers table:
Use Northwind
UPDATE  Shippers
SET CompanyName='Speedy Gonsalez'
WHERE ShipperID=1

SELECT * from Shippers
The query updates the name of the shipping company whose ID=1 to a new name. When TABLOCK is used in the above manner it should be within the parenthesis.

Read this recommendation from Microsoft:

"An UPDATE statement always acquires an exclusive (X) lock on the table it modifies, and holds that lock until the transaction completes. With an exclusive lock, no other transactions can modify data. You can specify table hints to override this default behavior for the duration of the UPDATE statement by specifying another locking method, however, we recommend that hints be used only as a last resort by experienced developers and database administrators"

Wednesday, May 24, 2017

Installing SQL Server 2016 SP1 for Native Mode Reporting Services Course

This video presentation was prepared for the course to fast track teach Report authoring using Report Builder 3. The Reporting Services is installed for Native Mode.

Conversion of Power Point Slides to Video seems to have some problems. My apologies...

Monday, May 22, 2017

Error Msg 103, Level 15, State 4

The following is formatted in JSON

{"student":{"name":"Linda Jones","legacySkill":"Access, VB 5.0"}etc..
{  "student":{"name":"Adam Davidson","legacySkill":"Cobol, MainFrame"}
{"student":{"name":"Charles Boyer","legacySkill":"HTML, XML"}

This is a valid JSON according to RFC 4627.


However when you use this in SQL Server to look at the JSON using the OpenJSON, for example, you will get this error:


The reason for this error:

Msg 103, Level 15, State 4, Line 2
The identifier that starts with "wclass"...

lies in the fact that SQL Server string starts with a single quote and therefore you need to provide this declaration:

declare @json nvarchar(Max)
set @json=
{"student":{"name":"Linda Jones","legacySkill":"Access, VB 5.0"}
{"student":{"name":"Adam Davidson","legacySkill":"Cobol, MainFrame"}
{"student":{"name":"Charles Boyer","legacySkill":"HTML, XML"}

When you do this the error goes away as shown:


The character count in @json is also important as you see in this SQL query:


The answer is that RFC 4627 validation requires a string to start with a double quote("), but the SQL Server's JSON validation requires the JSON to begin with a single quote(') as we saw in this post.

Sunday, May 21, 2017

SELECT query on OpenJSON using SQL Server 2016

OpenJSON converts an array of objects in a variable in JSON Format to a rowset
that can be queried with standard SQL Select statement.

Here is an example:

We are going to look at a JSON list of my first batch of students who took my course shown here. 

{"student":{"name":"Linda Jones","legacySkill":"Access, VB 5.0"}
{"student":{"name":"Adam Davidson","legacySkill":"Cobol, MainFrame"}
{"student":{"name":"Charles Boyer","legacySkill":"HTML, XML"}

This is the result of running OpenJSON using the above:

Now you can run a SELECT query with a with clause on the rows returned by OpenJSON as shown here:

The first member "wclass" has nulls for the selected columns. It exists because it actually was in the original XML that got converted to JSON.
Here are my more recent JSON related articles:

JSON validation in SQL Server:

Nested JSON using SQL Server 2012:

Retrieve JSON formatted data from SQL Anywhere 17

Tuesday, May 16, 2017

Globally distributed Azure Cosmos DB

This is how Azure Cosmos DB is described by Microsoft,

"Azure Cosmos DB is Microsoft's globally distributed, multi-model database. With the click of a button, Azure Cosmos DB enables you to elastically and independently scale throughput and storage across any number of Azure's geographic regions. It offers throughput, latency, availability, and consistency guarantees with comprehensive service level agreements (SLAs), something no other database service can offer"

If you are looking for Global Distribution, horizontal scaling, guaranteed latency, high availability, comprehensive SLA's and unrivalled Data Model and APIs that no RDBMS, no no-sql database can provide, there is nothing out there to match Azure Cosmos DB.

Azure Cosmos DB Engine supports multiple database systems and it is based on the following:

 Atom: Consisting of a small set of primitive types (String, Bol, number)
 Records: Structs
 Sequence: Arrays of atoms, records and sequences

Azure Cosmos DB engine currently supports the following:

Document DB SQL
Mongo DB
Azure Table Storage
Gremlin Graphh Query API
It will support other API's in the future.

For developers looking for a globally distributed database system Azure Cosmos DB is the answer.

Saturday, May 13, 2017

Building an ODBC Connection String in Report Builder 3 for SQL Server 2016

ODBC source is one of the many sources that you can connect to in Reporting
Services using the Report Builder.

Here are the steps. It is assumed you have SQL Server 2016 SP1 installed; Report Builder is also
installed on the same machine. For these steps it is assumed that the SQL Server is up and running
and you are the owner of the computer who installed the SQL Server.

1. Launch Report Builder with Administrative Privileges.
2. Right click Data Sources and Click Add DataSource... to bring up the Data Source properties window.
3.  Provide a name for the data source, DS_ODBC
4. Choose Use a connection embedded in my report
5. Select connection type: click handle and choose ODBC at the bottom of list
6. Click Build... to bring up the Connection properties window
7. In the Data source specification section choose Use connection string:
8. Click the Build... button that gets activated to display Select Data Source window
9. Click Machine Data Source
10. In Machine Data Source tabbed page, click New... to display Create New Data Source
11. Accept the default, User Data Source and click Next
12. In the Select a driver for which you want to set up a data source, scroll down and
    select SQL Server (SQLSRV32.dll); click Next to display the next window
13  Click Finish. Create a New Data Source to SQL Server is displayed
14. Provide a name of your choice (ODBC_DSN); Description:(ODBC for Report Builder); Server
    use handle to locate: Hodentek9\OHANA. Click Next
15. Accept the default in the displayed page: with Windows NT authentication using the
    Network login id. Click Next
16. Change the default database by clicking handle to Northwind. accept all other
    defaults. Click Next
17. Accept all defaults on the displayed page. Click Finish
18. In the ODBC Microsoft SQL Server Setup page test the connection and make sure it works. Click OK
19. ODBC_DSN enters the Machine Data Source tabbed page. Click OK
20. SQL Server Login window gets displayed. Enter creator owner's credentials
21. Copy the connection string from the connection properties window.
Dsn=ODBC_DSN;description=ODBC for Report Builder;trusted_connection=Yes;app=Microsoft SQL Server;wsid=HODENTEK9;network=DBNMPNTW
22. Test Connection to make sure it works
23. Click OK
The Connection string enters the Data Source Properties window as shown.

Thursday, May 11, 2017

Report Model managment is discontinued in Reporting Services Web Portal

RS Web Portal is the new name for Report Manager of earlier versions.

The following two features related to Report Model are discontinued  in SQL Server 2016.
Whereas HTML4 is supported, the future SQL Server Reporting Services will be using HTML5.

Feature                                                                Replacement or workaround
Upload report models through the web portal     This can still be done through the SOAP API.
Manage report models through the web portal    This can still be done through the SOAP API.

However the Report Model is still retained in couple of places, for example the Window for selecting permissions for a Custom Role.

Branding a Reporting Services Web Portal

Branding is an important feature of a Reporting Service Web Portal to drive business. It should be very attractivewith a nice logo; colors and features that should be able to choose how you present it.
This will be true for mobile reports as well. Microsoft has provided ample guidance as to how you go about branding your reporting site.

Brand Package consists of

a zip file with 3 named files:

color.json, metadata.xml,

and logo.png

Wednesday, May 10, 2017

Fix for the rsErrorOpeningConnection in SSRS 2016

The error, rsErrorOpeningConnection has been asked many times in almost
all versions of SQL Server Reporting Services starting from SSRS 2008. Various
reasons have been given. The error message hints at what may be wrong.

In the present case, a simple report with embedded data source  was created in Report Builder 3 by the creator owner who is also the local administrator. This report was saved to the
Report Server (also gets uploaded to the Web Portal front-end).

The report is processed in the Report Builder without any error.

If the Where_2 report is double clicked this is the message displayed after correct authentication
by the local administrator.

The Data Source is shown in the following and the report gets correctly displayed in
the Report Builder and it can be saved to the Report Server as seen above.

The rsErrorOpeningConnection arises because of the credentials to the Data Source is not
properly configured (what is shown is the default). Review the credentials for the data source:

When you make the proper entries for the report as shown here:

The error goes away.

 The SQL Server 2016 SP1, the Report Builder 3 are both on a Dell Laptop with Windows 10 Pro. The Reporting Services configuration is for Native Mode managed by the laptop owner.

Wednesday, May 3, 2017

Hands-on training event in Honolulu: SQL Server Reporting Services 2012 in Native Mode

Updated information: SQL Server 2016 SP1 will be used

This hands-on course will provide you with the ability to create cutting edge reports using Microsoft's latest reporting services product, SQL Server 2012.
The course is offered by the Pacific Center for Advanced Technology Training(PACTT):
Report writing is one of the most important IT related jobs in an enterprise. This course is for you if you would like to master report writing/authoring using SQL Server Reporting Services. The course begins with a presentation of essential technical background, after which you will work through guided hands-on exercises.
At the end of the course you will have gained:
• A solid understanding of how to interact with SQL Servers
• Knowledge and experience installing and configuring reporting Services
• A working knowledge of using Report Builder to author reports and deploy them to report server
• A working knowledge of Report Manager's ability to carry out administrative tasks related to all aspects of reports; creating shared data sources, folder and folder hierarchies on the report server, and report models; managing security and subscriptions; and configuring data driven subscriptions
• An understanding of how to embed report viewer controls in windows and web projects.

Venue: Honolulu Community College Honolulu Community College
874 Dillingham Blvd Honolulu, HI 96817-4598

Phone: (808) 845-9211

Official Website:

Recommended Prep:
Computer literacy on a PC and experience using a database
- Section 1 - May 18 - Jun 27; TTh; 5:30 pm - 7:30 pm; 12 mtgs

Tuesday, May 2, 2017

SQL Server Management Studio for SQL Server 2016 is a separate Install

Recently downloaded SQL Server 2016 SP1 and to my surprise I did not find SQL Server Management Studio. This is by design that Microsoft wants user to download it separately, it is free however. In addition to SSMS (v17.0) lots of other components related to SQL Server vNext CTP 2.0 are also installed.

Another surprise, SQL Server 2016 SP1 now installs the SQL Server Configuration Manager.

How's that for confusion!

Install SSMS (Release 17) from this link:

 SQL Server 2016 SP1 installation installs the following:

SQL Server Management Studio installs the following shown by arrows:

Saturday, April 29, 2017

SQL Server Reporting Services 2016 with SharePoint

SharePoint just uses the SSRS add-in to render reports. With SSRS 2016 you need to use the SSRS 2016 add-in for working with SharePoint 2013.

Depending on the SQL Server version you can look up what combination of SharePoint works with Reporting Services (SQL Server Version).

Although the designated platform for SharePoint Server 2010 were Windows Servers 2018 (x64 Bit) or Windows Servers 2018 R2 (x64 Bit), it was still possible to install on Windows 7. In fact this was how I could work with Share Point Integrated mode for Reporting Services as detailed in my book. I used the versions highlighted in the above image.

Wednesday, April 26, 2017

Vertical alignment property of Text is missing in Report Builder 3

The latest Report Builder v3 used with SQL Server Reporting Services has the vertical alignment property missing. In order to have proper formatting, this property should be back in Report Builder.

Sunday, April 23, 2017

KanaType and KanaType sensitvity in SQL Server

This is specifically for data in Japanese language databases. There are two types of Japanese phonetic scripts, the Hiragana and the Katakana. Of course there is also the Kanji which are modified/simplified Chinese characters used in Japan together with Hiragana and Katakana.

While dealing with Japanese data Kanatype is used to distinguish between Katakana and Hiragana. Katakana is usually used for words not found in Japanese, like, Bus, Laser, harassment etc.

For example, the 'dataset' in Reporting Services have this property:


Here is a piece of text in Hiragana
ふるい もの の なか に ある かくれた うつくしさを 「さび」 と いいます。 また、かざらない そぼくな うつくしさ を 「わび」 と いいます。ふたつ を あわせて、「わび・さび」 と いいます。


Here is a piece of text in Katakana

The same text written in Katakana using online translator:
フルイ モノ ノ ナカ ニ アル カクレタ ウツクシサヲ 「サビ」 ト イイマス。 マタ、カザラナイ ソボクナ ウツクシサ ヲ 「ワビ」 ト イイマス。フタツ ヲ アワセテ、「ワビ・サビ」 ト イイマス。


Monday, April 17, 2017

Saving Power BI reports to SQL Server Report Server - Part 3

The readers of my posts Saving Power BI reports to SQL Server Report Server - Parts1 and  2
  might have been under the impression that any report created in Power BI can be hosted on the Report Server. I am partly to blame for this and I apologize. Let me correctly present what kinds of reports can be saved to what kind of report server. The other reason for the problem was created by the Power BI itself for giving the wrong guidance for the users. It must be changed.

The only kind of reports that can be saved to Report Server (in fact you are only saving it to the Web Portal URL) are the ones created with data from the Analysis Services server.

In order to save you need to configure a stand-alone Report Server whose database is stored not on a SQL Server 2016 but an earlier version. In my case I use the SQL Server 2012.

After you fulfill the above conditions then only you can host the report.

I was able to get the correct server by an answer to my question on

Sunday, April 9, 2017

Saving Power BI reports to SQL Server Report Server - Part 2

In an earlier post the method to save a report from Power BI to SQL Server Report Server was described. In order to do so you need to use the Technical Preview of Power BI Desktop.

In this post the installing of the Technical Preview will be described. The present Windows 10 Professional is a x64 bit machine and therefore we start with the .msi file shown here.

When you double click the above file you will be launching the wizard.

Click Next> to open the License Terms window.

Accept terms and click Next>. The default directory for the program is displayed. If needed change. Herein default is accepted.

Click Next>.  You are now ready to Install.

Click Install. It is installed. It may take 10 to 15 minutes.

Click Finish and if you have agreed to launch the program, the Power BI gets launched.The splash screen shows that this Power BI Desktop goes with SQL Server Reporting Services.

The program is now ready to create/save reports.

 NOTE: If you already have configured a SQL Server 2016 Reporting Services server you will not be able to upload the reports from Power BI. You need to install the SQLServerReportingServices.exe mentioned in previous post and configure a Standalone SQL Server Reporting Services which will provide a http://<domain>/ReportServer.

Saturday, April 8, 2017

Saving Power BI reports to SQL Server Report Server

If you use the Technical Preview version of Power BI Desktop. You can save reports to your on- premises SQL Server.

NOTE: If you already have configured a SQL Server 2016 Reporting Services server you will not be able to upload the reports from Power BI. You need to install the SQLServerReportingServices.exe mentioned below and configure a Standalone SQL Server Reporting Services which will provide a http://<domain>/ReportServer.

You can download this for both x32 and x64 versions. With this you can,

  •     Connect “live” to Analysis Services models – both Tabular and Multidimensional (cubes)
  •     Visually explore data and create an interactive report
  •     Save that report to your report server running the Technical Preview
  •     View and interact with the report in your web browser

If you do have Power BI for Desktop on your computer you still need the (January 2017) Technical Preview. This can sit alongside your present Power BI.
Technical Preview of Power Bi for Desktop download link
Preview version details below:
Version:            Date Published:

0.1                1/11/2017

File Name:                                                               File Size:

PBIDesktopRS.msi                                      102.2 MB

PBIDesktopRS_x64.msi                           118.3 MB

SQLServerReportingServices.exe    49.6 MB

System Requirements:
Supported Operating System

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

    You'll need
        .NET Framework 4.5.2 or later
        SQL Server Database Engine (2008 or later), to store the report server database
        SQL Server Analysis Services (2012 SP1 CU4 or later), to store your data models
        Get SQL Server 2016 Evaluation
        Try SQL Server vNext CTP1.1

Wednesday, April 5, 2017

New in SQL Server 2016 Reporting Services: File Share

This is feature is applicable only for Native Mode.

In order to specify a single file share account ( a new feature of SQL Server 2016) you must get to the Subscription Settings page of the SQL server 2016 Reporting Services Configuration Manager shown here.

The file share account that you set up here allows you to set a single set of credentials in multiple subscriptions that deliver reports to a file share.

When you want to change credentials you just change the credentials of the file share and you need not change the credentials of every subscription in the shared folder. In the subscriptions in the file share each subscription will specify; Use file share account.

However you may also configure individual subscriptions with specific credentials as well or, have some on file share and some with individual specific credentials.

Uploading a Moble report to a Report Server

This post is about displaying a Mobile Report created by the SQL Server Mobile Report Publisher after publishing the report to a SQL Server Reporting Services which is actually the Web Portal URL of the Reporting Services.

The present Reporting Services configuration is pertinent to SQL Server 2016 Reporting Services on the local machine.

While it should also be possible to publish it directly from the SQL Server Mobile Report Publisher, it was not possible because there were errors connecting to the Report Server although the SQL Server 2016 Database Engine; the Reporting Services were all up and running and an empty folder 'Reports' was created on the Web URL. I have not tried to verify the problem yet. This could be because of the constant upgrades coming to the OS automatically.

In this post, I consider uploading a report to the Web Portal URL after saving the file created by the SQL Server Mobile Report Publisher to a local folder.

You need to make sure the Reporting Services database is accessible which the same as the SQL Server Database Engine  running; the Reporting Services has started and the Service and Web Portal URL have no problems.

Then use the Upload button on your Web Portal URL to upload the report created and saved (to local file system) to the computer. Once it gets uploaded the report gets into the WEB Portal URL as shown.

The Northwind Report has three charts, the creation of one of them is detailed in my earlier post here. I have just added two more grid items this upload.

You can view each of them on the WEB Portal URL as well as shown here.


Herein http://hodentek8/Reports_OHANA is my Web Portal URL.