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.

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.

SqlAnyOleDB_00

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

SqlAnyOleDB_01

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

SqlAnyOleDB_02

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

SqlAnyOleDB_03

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.

SqlAnyOleDB_04

Again use the dba/sql password and click connect.

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

SqlAnyOleDB_05

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.

Report Builder Report using data from SQL Anywhere 16

Report Builder 3.0 is the present version shipped at the same time as SQL Server 2012. It is a one-stop report authoring tool which can even be launched from Report Manager or from a Reporting services Integrated SharePoint Site. Of course you can download and launch it after installing on your desktop.

The most viewed Report Builder tutorials are here:
Report Builder is described in great detail here:

http://jayaramkrishnaswamy.sys-con.com/node/982742
Creating reports using Report Builder 2.0 is described here

http://jayaramkrishnaswamy.sys-con.com/node/1227111

 

You can master Report Builder 3.0, just follow the step-by-step instructions

Report Builder 3.0 is described exhaustively in my latest book

Out of the box it can connect to a variety of vendor products and the inclusion of ODBC and OLE DB makes it extremely convenient to connect to many other (not out of the box supported) products

Case in point is SQL Anywhere Server 16 for which you can set up an ODBC connection

This post shows you some of the steps that you can follow to turn out a report from SQL Anywhere 16. The following assumes that you have already started the SQL Anywhere Personal server. The procedure has still some unanswered questions, please read the last section

The following screen shows how you connect to an ODBC source for a connection embedded with the report

The next slide shows an ODBC System DSN 'demo' created using the SQL Anywhere 16. In reality this ODBC DSN gets registered when you install SQL Anywhere 16

 The next slide shows that connectivity is OK with 'dba' as username and 'sql' as password

The final connectivity screen after testing the authentication is shown in the next image

 The Credentials for this connection are shown in the next image

In order to create a dataset for your report use of Query Designer is not possible as it is not supported. You need to have this information on your hand to insert into the Report Builder's data set page.

The next image shows the InteractiveSQL tool in which a SQL Select statement is used to choose a all the fields from the Contacts table.

The dataset will be created using this embedded connection as shown.

For this, the query contacts.sql created in Sybase's InteractiveSQL is used and persisted to the desktop. This is imported into Report Builder's data set interface as shown using the import button

.

The next image shows the report designer interface taking in a few columns from the data set shown on the left.

This last image shows the report being displayed in Report Builder 3.0

Caution

While the above procedure is correct you may find problems while repeating this procedure and this will be mainly due to the odbc32 and odbc64 problems as I understand it

However, take a look at my bug report to Microsoft connect here

https://connect.microsoft.com/SQLServer/feedback/details/799460/report-displays-correctly-in-ssdt-and-deploys-without-errors-the-same-report-is-not-displayed-in-report-manager-or-report-server