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 hodentek@live.com

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.

https://youtu.be/6S25lHM15E4

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
Go
Create FULLTEXT CATALOG ftCat_Nwind as default
Go

------
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.


Caution:
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:

https://hodentekmsss.blogspot.com/2017/05/installing-sql-server-2016-sp1-for.html

TABLOCK hint in an Update SQL Query

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

   FORCESCAN 
   FORCESEEK 
   HOLDLOCK  
   NOLOCK  
   NOWAIT 
   PAGLOCK  
   READCOMMITTED  
   READCOMMITTEDLOCK  
   READPAST  
   READUNCOMMITTED  
   REPEATABLEREAD  
   ROWLOCK  
   SERIALIZABLE  
   SNAPSHOT  
   SPATIAL_WINDOW_MAX_CELLS = integer 
   TABLOCK  
   TABLOCKX  
   UPDLOCK  
   XLOCK 

Table hints can be used with any of the following operations:
Applies to:
DELETE
INSERT
SELECT
UPDATE
MERGE

Here is a query that updates a value in the Northwind Shippers table:
----------
Use Northwind
GO
UPDATE  Shippers
WITH (TABLOCK)
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"