Thursday, December 1, 2016

SQL Anywhere 17 Reports using Power BI

 Power BI is a great tool to turn out reports from many data sources. With OLE DB connector added in the October Update of Power BI it is quite easy to generate a report.

In this post I describe creating a report using data from the Financial Data (Group 0) of the sample database 'demo'

Step 1:
Connect to SQL Anywhere 17
Launch SQL Central and connect to Demo database

 Step 2:
Launch PowerBI and connect to SQL Anywhere 17 Demo database using a OLE DB connection. Make sure to use the Advanced button to run the query as shown.

When you get the data in the Query Editor click Load.

The Financial Data gets loaded as shown.

Choose data fields as shown:


Choose the visualization as shown.


Drag Amount to Tooltips as shown.


Now when you hover over the graph you get to see the tool tip. The repeated data is because Amount is both in Value and Tooltips fields.


Wednesday, November 30, 2016

Install SQL Server 2016 SP1 and get productivity enhancements

SQL Server 2016 Service Pack 1 is released  and now generally available(GA).

According to the above release blog:

Many of the features (listed in the table here) available in Enterprise Edition are now available in several other editions as well after installing the SP1 (Standard, web, express and LocalDB).


Note that Change Data Capture requires SQL Server Agent not available in Express and In Memory OLTP not possible in LocalDB due to security reasons

DBCC CLONEDATABASE added supports cloning of CLR, Filestream/Filetable, Hekaton and Query Store objects are available after installing SP1

DBCC CLONEDATABASE  (source_database_name, target_database_name)    –– Default CLONE WITH SCHEMA, STATISTICS and QUERYSTORE metadata.
DBCC CLONEDATABASE  (source_database_name, target_database_name) WITH NO_STATISTICS                                                                                               –– SCHEMA AND QUERY STORE ONLY CLONE
DBCC CLONEDATABASE  (source_database_name, target_database_name) WITH NO_QUERYSTORE                                                                                              –– SCHEMA AND STATISTICS ONLY CLONE
DBCC CLONEDATABASE  (source_database_name, target_database_name) WITH NO_STATISTICS,NO_QUERYSTORE                                                          –– SCHEMA ONLY CLONE

New Create/Alter supports modifying and deploying database objects such as
Stored Procedures, 
User–Defined Functions, and 

This is just a short list from more than a dozen others. Make sure you visit the link referenced at the top.

The SQL Server 2016 Enterprise specific sample database, WorldWorldImporters can now be used in Standard and Express editions.

Download SQL Server 2016 SP1 here .

Monday, November 28, 2016

Using JSON Validator in SQL Server

The Transact-SQL IsJSON() tests whether a expression(string) is JSON valid. If it is valid you should get a 1 as return value, a zero(0) if it is not valid and a null if the expression is null.

How do you use it?

This is a json string, a very simple one:
{"wclass":{"student":["jay", "john", "sam"]}}
The following code snippet shows how you may use it:
declare @json nvarchar(150)SET @json=N'{"wclass":{"student":["jay", "john", "sam"]}}';
Select ISJSON(@json)

When you run this in the SQL Server 2016 query pane, you get the return value 1 (see image below).

Sunday, November 27, 2016

Retrieve data from SQL Anywhere 17 in JSON format

SQL Anywhere 17 is a SAP Database.  Some of the earlier versions were released by SYBASE.

In SQL Anywhere 17 you have three different ways of getting JSON formatted data / JSON document.

You can use the FOR JSON clause  with:

  • SELECT Statement
  • Subqueries
  • Queries having Group By clause
  • Aggregate Functions and
  • Views
The result is a JSON array consisting of:

  • Scalar elements
  • Objects
  • Arrays
There are three ways of calling the FOR JSON Clause:

  • For JSON Raw
  • For JSON Explicit
Note that SQL Server 2016 did get JSON support for the first time and has only For JSON Auto clause.

You run SQL Queries in Interactive SQL. Here is an example of a query that provides json document using the FOR JSON AUTO clause.


Thursday, November 24, 2016

Problem connecting to SQL Anywhere 17 to Power BI using OLE DB - the workaround

 This is a continuation of my previous post wherein the Power BI created connection string spawned an error.

In the previous post we saw that the following connection information did not work.

I changed the connection string by using database instead of Initial Catalog and it seemed to work by displaying the demo database database objects in the Navigator.

However attempting to expand any of them displayed other error messages. Also, even without changing initial catalog to database displayed the same objects in the Navigator.  

There are no other connections to this database and it is not to reason out this error.

It was then decided to try out the Advanced Option in the From OLE DB dialog. 

Used the same Connection Information and then in the Advanced Option dialog inserted a SELECT query (you can use any other query) and clicked OK. However there was one more dialog requesting Username/Password.

The response was correctly returned as shown.

I believe the From OLE DB stage of the wizard is not working too well unless you use the Advanced Option. Even with SQL Server 2016, the problem was if one did not use the Advanced Option (this has not been tested yet) the connection will open all the objects on the server and not just the one chosen with the Data Link stage of the wizard.

Wednesday, November 23, 2016

Problem connecting to SQL Anywhere 17 to Power BI using OLE DB

SQL Anywhere 17 is a relational database from SAP. The earlier editions were from the parent company Sybase.

SQL Anywhere 17 is a SAP database. The earlier editions were from the parent company Sybase.

Start SQL Central which is like SQL Server Management Studio for SQL Server and connect to SQL Anywhere Demo 17 database with credentials dba/sql.

Launch Power BI and click on Get Data and choose From OLE DB. Click on Build to open Data Link Properties as shown.


Choose SQL Anywhere OLE DB Provider 17 from the above drop-down.
Click Next. Provide User Name and Password and click allow saving password and browse to find the database demo. as shown


Click demo and click Test Connection.
This should display a success message as shown.


Click OK. The From OLE DB window gets filled as shown.


Again the Access a datasource using OLEDB Provider comes up as shown. Actually this UI needs to be better designed. The credentials were already entered in a previous wizard screen.


Again use the dba/sql password and click connect.

Looks like the Power BI does not accept the UI designed connection string


The connection string was created by Power BI after accepting valid entries. Looks like there is a problem.

 This is being looked into and come back to this site. The work around will be posted as soon as it is found.

Tuesday, November 22, 2016

Creating a report using JSON formatted data with Power BI - Part 2

This is a continuation of my previous post.

Once the query is properly created turning it into a report in Power BI is quite easy. It takes only a couple of clicks. Here is the last query in the previous post.

The column data types are text and you need to change them. Clicking each column you can change the data type. The title_id is kept as is text. The qty was changed to whole number as well as the ytd_sales column. The Amount column was changed to decimal. Here are the allowed data types in PowerBI.

Now that the plot data is ready you can click the first menu item, Close & Apply in the Query editor.

Now you place Id in the Axis and the other three (Qty, Ytd_Sales, Amount) in the value fields to display a Clustered Column Chart as shown.
or a Multi-row card report as shown.

You can also choose any other chart type.