Power BI Desktop – Database Connectors

by | Aug 30, 2018 | IT, Power BI

Overview

In a previous post we saw a Power
BI overview. In this one, we are going to talk with more practical examples in Power BI Desktop tool about Database connectors (SQL Server, IBM Informix, Oracle and MySQL database).

Before we proceed we must make sure that we have installed the latest Power BI Desktop version. We can download the setup from this
link.

 

What are Data Connectors?

Data connectors allow users to connect and access data from several data sources. In this post, we will talk for the most used database connectors in Power BI Desktop App.

 

File

The simplest way to import data to Power BI Desktop is from a file. A file can be one of the following:

 

Type
Excel
Text/CSV
XML
JSON
Folder
Sharepoint

 

Excel

Import data from excel is very simple.

In the Power BI Desktop, select


Get Data > Excel

 

From the Open Dialog select the excel file and click Open.

 

 

JSON

What is JSON?

JSON (JavaScript Object Notation) is a lightweight data-interchange format easy for someone to understand.

Select SQL Data  As JSON

For demo purposes suppose that we don’t have any JSON file and we create a new one from a SQL Server database(AdventureWorks).  The first thing we must do is to execute the following Select
statement to retrieve the data in JSON format by using the switch {FOR JSON AUTO}.

SELECT
TOP
10[CustomerID]
 ,[NameStyle]
 ,[Title]
 ,[FirstName]
 ,[MiddleName]
 ,[LastName]
 ,[Suffix]
 ,[CompanyName]
 ,[SalesPerson]
 ,[EmailAddress]
 ,[Phone]
 ,[PasswordHash]
 ,[PasswordSalt]
 ,[rowguid]
 ,[ModifiedDate]
 FROM
[SalesLT].[Customer]
 FOR
JSON AUTO

In the image below we can see the results from the previous SQL statement. Copy the line No 1 and paste it to a JSON Editor like
JSON Editor Online.  We can also do the transformation from the SSMS, by using Find & Replace method (CTRL+H) and Regular Expression

like \n.

Results 

Convert To JSON Format

After the file is converted using one of the methods described, the result will be like this:

[
 {
 "CustomerID":
1,
 "NameStyle": false,
 "Title":
"Mr.",
 "FirstName":
"Orlando",
 "MiddleName":
"N.",
 "LastName":
"Gee",
 "CompanyName":
"A Bike Store",
 "SalesPerson":
"adventure-works\\pamela0",
 "EmailAddress":
"orlando0@adventure-works.com",
 "Phone":
"245-555-0173",
 "PasswordHash":
"L/Rlwxzp4w7RWmEgXX+/A7cXaePEPcp+KwQhl2fJL7w=",
 "PasswordSalt":
"1KjXYs4=",
 "rowguid":
"3F5AE95E-B87D-4AED-95B4-C3797AFCB74F",
 "ModifiedDate":
"2005-08-01T00:00:00"
 },
 {
 "CustomerID":
2,
 "NameStyle": false,
 "Title":
"Mr.",
 "FirstName":
"Keith",
 "LastName":
"Harris",
 "CompanyName":
"Progressive Sports",
 "SalesPerson":
"adventure-works\\david8",
 "EmailAddress":
"keith0@adventure-works.com",
 "Phone":
"170-555-0127",
 "PasswordHash":
"YPdtRdvqeAhj6wyxEsFdshBDNXxkCXn+CRgbvJItknw=",
 "PasswordSalt":
"fs1ZGhY=",
 "rowguid":
"E552F657-A9AF-4A7D-A645-C429D6E02491",
 "ModifiedDate":
"2006-08-01T00:00:00"
 },
 {
 "CustomerID":
3,
 "NameStyle": false,
 "Title":
"Ms.",
 "FirstName":
"Donna",
 "MiddleName":
"F.",
 "LastName":
"Carreras",
 "CompanyName":
"Advanced Bike Components",
 "SalesPerson":
"adventure-works\\jillian0",
 "EmailAddress":
"donna0@adventure-works.com",
 "Phone":
"279-555-0130",
 "PasswordHash":
"LNoK27abGQo48gGue3EBV/UrlYSToV0/s87dCRV7uJk=",
 "PasswordSalt":
"YTNH5Rw=",
 "rowguid":
"130774B1-DB21-4EF3-98C8-C104BCD6ED6D",
 "ModifiedDate":
"2005-09-01T00:00:00"
 },
 {
 "CustomerID":
4,
 "NameStyle": false,
 "Title":
"Ms.",
 "FirstName":
"Janet",
 "MiddleName":
"M.",
 "LastName":
"Gates",
 "CompanyName":
"Modular Cycle Systems",
 "SalesPerson":
"adventure-works\\jillian0",
 "EmailAddress":
"janet1@adventure-works.com",
 "Phone":
"710-555-0173",
 "PasswordHash":
"ElzTpSNbUW1Ut+L5cWlfR7MF6nBZia8WpmGaQPjLOJA=",
 "PasswordSalt":
"nm7D5e4=",
 "rowguid":
"FF862851-1DAA-4044-BE7C-3E85583C054D",
 "ModifiedDate":
"2006-07-01T00:00:00"
 },
 {
 "CustomerID":
5,
 "NameStyle": false,
 "Title":
"Mr.",
 "FirstName":
"Lucy",
 "LastName":
"Harrington",
 "CompanyName":
"Metropolitan Sports Supply",
 "SalesPerson":
"adventure-works\\shu0",
 "EmailAddress":
"lucy0@adventure-works.com",
 "Phone":
"828-555-0186",
 "PasswordHash":
"KJqV15wsX3PG8TS5GSddp6LFFVdd3CoRftZM/tP0+R4=",
 "PasswordSalt":
"cNFKU4w=",
 "rowguid":
"83905BDC-6F5E-4F71-B162-C98DA069F38A",
 "ModifiedDate":
"2006-09-01T00:00:00"
 },
 {
 "CustomerID":
6,
 "NameStyle": false,
 "Title":
"Ms.",
 "FirstName":
"Rosmarie",
 "MiddleName":
"J.",
 "LastName":
"Carroll",
 "CompanyName":
"Aerobic Exercise Company",
 "SalesPerson":
"adventure-works\\linda3",
 "EmailAddress":
"rosmarie0@adventure-works.com",
 "Phone":
"244-555-0112",
 "PasswordHash":
"OKT0scizCdIzymHHOtyJKQiC/fCILSooSZ8dQ2Y34VM=",
 "PasswordSalt":
"ihWf50M=",
 "rowguid":
"1A92DF88-BFA2-467D-BD54-FCB9E647FDD7",
 "ModifiedDate":
"2007-09-01T00:00:00"
 },
 {
 "CustomerID":
7,
 "NameStyle": false,
 "Title":
"Mr.",
 "FirstName":
"Dominic",
 "MiddleName":
"P.",
 "LastName":
"Gash",
 "CompanyName":
"Associated Bikes",
 "SalesPerson":
"adventure-works\\shu0",
 "EmailAddress":
"dominic0@adventure-works.com",
 "Phone":
"192-555-0173",
 "PasswordHash":
"ZccoP/jZGQm+Xpzc7RKwDhS11YFNybwcPVRYTSNcnSg=",
 "PasswordSalt":
"sPoUBSQ=",
 "rowguid":
"03E9273E-B193-448E-9823-FE0C44AEED78",
 "ModifiedDate":
"2006-07-01T00:00:00"
 },
 {
 "CustomerID":
10,
 "NameStyle": false,
 "Title":
"Ms.",
 "FirstName":
"Kathleen",
 "MiddleName":
"M.",
 "LastName":
"Garza",
 "CompanyName":
"Rural Cycle Emporium",
 "SalesPerson":
"adventure-works\\josé1",
 "EmailAddress":
"kathleen0@adventure-works.com",
 "Phone":
"150-555-0127",
 "PasswordHash":
"Qa3aMCxNbVLGrc0b99KsbQqiVgwYDfHcsK9GZSUxcTM=",
 "PasswordSalt":
"Ls05W3g=",
 "rowguid":
"CDB6698D-2FF1-4FBA-8F22-60AD1D11DABD",
 "ModifiedDate":
"2006-09-01T00:00:00"
 },
 {
 "CustomerID":
11,
 "NameStyle": false,
 "Title":
"Ms.",
 "FirstName":
"Katherine",
 "LastName":
"Harding",
 "CompanyName":
"Sharp Bikes",
 "SalesPerson":
"adventure-works\\josé1",
 "EmailAddress":
"katherine0@adventure-works.com",
 "Phone":
"926-555-0159",
 "PasswordHash":
"uRlorVzDGNJIX9I+ehTlRK+liT4UKRgWhApJgUMC2d4=",
 "PasswordSalt":
"jpHKbqE=",
 "rowguid":
"750F3495-59C4-48A0-80E1-E37EC60E77D9",
 "ModifiedDate":
"2005-08-01T00:00:00"
 },
 {
 "CustomerID":
12,
 "NameStyle": false,
 "Title":
"Mr.",
 "FirstName":
"Johnny",
 "MiddleName":
"A.",
 "LastName":
"Caprio",
 "Suffix":
"Jr.",
 "CompanyName":
"Bikes and Motorbikes",
 "SalesPerson":
"adventure-works\\garrett1",
 "EmailAddress":
"johnny0@adventure-works.com",
 "Phone":
"112-555-0191",
 "PasswordHash":
"jtF9jBoFYeJTaET7x+eJDkd7BzMz15Wo9odbGPBaIak=",
 "PasswordSalt":
"wVLnvHo=",
 "rowguid":
"947BCAF1-1F32-44F3-B9C3-0011F95FBE54",
 "ModifiedDate":
"2006-08-01T00:00:00"
 }
]

 

Get Data Into Power BI Desktop

The next step is to import data into Power BI.  On the Home tab select

Get
Data > More > File> JSON, and click Connect.

From the dialog, select the JSON file which was created earlier and click Open.

When we import the JSON file we see at the top ribbon the following pop-up message, click the button “Apply changes“.

At the next step on the FIELDS blade, we can see the table Customer, click the button
… and select Edit Query. 

When the data loads, we are not able to see all the data but 10 lines with the word (Record). If we click the Record then it will be drilled down from the top level of the JSON structure.

To fix this, click the left top button [To Table], leave the default setting as the image below shows and click
OK. 

At the next step, we can see the button

,
which allows us to LOAD COLUMN VALUES. 

Select the columns that we want to appear, and click OK. 

Finally, we can see the data split into different columns, in the left corner of the ribbon, click the button
Close & Apply tto return in the main visualization window. 

Now, we are able to use the JSON data to create reports and dashboards. 

 

Database

Getting data from databases is quite easy, too.  There are a lot of database types that Power BI Desktop can connect and retrieve data from.

At the table below we can see what type of databases Power BI Desktop supports.

 

 Types
SQL Server database SAP Business Warehouse Application Server Oracle database
Access database SAP Business Warehouse Message Server (Beta) IBM DB2 database
SQL Server Analysis Services database Amazon Redshift IBM Informix database (Beta)
IBM Netezza Impala MySQL database
PostgreSQL database Google BigQuery Sybase database
Teradata database Snowflake SAP HANA database
Exasol (Beta) Jethro

 

SQL Server Database

On the Home tab select

Get Data > SQL Server

 

Fill in the necessary information and click OK.

 

 

Setting Value
Server Type the Database Server Name
Database Type the Database Name
Data Connectivity mode Select the data connectivity mode. Import
: Data are imported into .pbix file | DirectQuery : No data is imported into .pbix file, it connects directly to our data source.
Advanced Options
Include relationship columns This option creates automatically relations between tables which they have relational columns.
Navigate using full hierarchy Enable this if need to drill into schema database
Enable SQL Server Failover support This feature works for SQL Server Failover cases. If this enabled, then Power BI moves from one node to another when failover happens.

 

At the image below we can see a connection example to a SQL Server database.

 

 

Setting Value
Windows Type the Windows credentials to access the database
Database Type the SQL Server login credentials
Microsoft account Type the O365 Microsoft Account

 

IBM Informix Database(Beta)

 

 Caution
Before we start the connector configuration we must clarify that the IBM Informix database system uses Distributed Relational Database Architecture (DRDA) protocol.

 

From the Home tab select


Get Data and from the left side select Other > ODBC (as the image below shows), and click Connect.

 

 

 Note
Until the day this post was published the IBM Informix database connector (Power BI Desktop v2.61.5192.601.) was in Preview, as the image below shows.

 

So, click the button Continue.

 

At the next step, type the Server Name, Database Name add the SQL statement (this is optional) and click
OK.

 

After that, we must type the credentials that are used to connect the Informix database, Username, User Password and click Connect to make the connection.

 

Oracle Database

We need to install the Oracle Client. For every Power BI desktop version we can download each installer :

 

 Caution
If an Oracle account does not exist we MUST create one to log in and download the file.

 

From the Home tab select


Get Data and from the left side select Database> Oracle Database (as the image below shows ), and click
Connect.

 

In the Oracle Database dialog form, type the Server name, the
SQL statement (if available) and click Connect.

 

 Note
If a SID is required, you can specify that using the format: ServerName/SID, where SID is the unique name of the database. If the ServerName/SID format doesn’t work, try using ServerName/ServiceName,
where ServiceName is the alias used when connecting.

 

If the Oracle database requires Credentials, then a new dialog shows up and we must type the credentials(Windows / Database) and click
Connect…

 

MySQL Database

For MySQL Database connection we must first download
and install MySQL Connector (NET 8.0.12), Windows (x86, 32-bit).

At the next step from  Home tab select

Get
Data and from the left side select Database> MySQL Database (as the image below shows), and click Connect.

 

In the MySQL database dialog, type the Server, Database.
SQL statement (if this available) and click OK.

 

 

Azure

Microsoft Azure services also integrate with Power BI Desktop, we can create dashboards and reports using real-time insights, SQL Azure database data, etc. Until now Power BI supports 11 Azure services which are shown at the next table.

 

Azure SQL database Azure SQL Data Warehouse
Azure Analysis Services database Azure Blob Storage
Azure Table Storage Azure Cosmos DB (Beta)
Azure Data Lake Store Azure HDInsight(HDFS)
Azure HDInsight Spark HDInsight Interactive Query (Beta)
Azure KustoDB (Beta)

 

 

Azure SQL Database

Of course Power BI Desktop couldn’t skip having a connector with Azure SQL Database. This connector gives the ability for direct connect to Azure SQL Database hosted on Azure.

Prerequisites

  • Azure Account with subscription
  • Azure SQL Database service
  • Power BI Desktop

If all of the above prerequisites are covered, we are ready to begin.

Open Power BI Desktop, and from  Home tab select

Get
Data and from the left side select Azure > Azure SQL Database (as the image below shows), and click Connect.

 

At the next step, we must type in the dialog box, information like, Server,
Database (optional), SQL Statement (optional) if exists, and click
OK.

 

If the Azure SQL Database credentials are not saved, we must type them again,
Username, Password.

 

 

Other

Vertical (Beta) Web
Sharepoint list OData Feed
Active Directory Microsoft Exchange
Hadoop File (HDFS) Spark
R script ODBC
OLE DB Blank Query

 

Open Database Connectivity (ODBC)

For ODBC connection we will see an example connection on an IBM Informix Cisco database.

Configure The DSN (Data Source Name)

 Note
In this example, we use the onsoctcp protocol for Windows OS

 

Search for the ODBC (Open Database Connectivity) application, and click on the ODBC Data Sources (64-bit)

 

Click the button Add…

 

Select “IBM Informix ODBC Driver (64-bit)” and click Finish.

 

Driver Setup

On the General tab, type a Data Source Name, e.g Cisco_InformixDB, and move in the next tab

 

In the Connection tab, fill in all the necessary information, as the image below shows.

 

 

Setting Value
Server Name database server name
Host Name IP or Computer Name, that Database Server is hosted
Service IBM Informix database server process that runs on your Host computer
Protocol Select the connectivity protocol  for communication with the server
Options
Database Name select the Informix database
User Id User identification or account name for access to the DSN
Password Password for access to the DSN

 

At the next step fill in the values in the Environment tab, and click OK.

 

 

Setting Value
Client Locale Default Value: en_US.1252. For the demo purposes, we change it to en_US.CP1252
Database Locale Default Value: en_US.1252. For the demo purposes, we change it to en_US.57372
Use Server Database Locale
Translation Library Dynamic linked library (DLL) that performs code-set conversion;
Translation Option Option for a non-IBM Informix translation library

Varying multibyte character length reporting option that specifies how to set pcbValue when rgbValue (the output area) is not large enough for the code-set-converted data

Possible values:

0=Estimate

1=Exact
Cursor Behavior  Flag for cursor behavior when a commit or rollback transaction is called

Possible values are:

0=close cursor

1=preserve cursor

Default Value :0
VMB Character 0-Estimate
Fetch Buffer Size 32767
Isolation Level The ISOLATION level supports complete-connection level settings. This means that values in the local session environment at the time of connection are propagated to all new or resumed transactions.

Default Value: 0

Get Data Source

From the top ribbon click

Get
Data and from the left side select Other > ODBC (as the image below shows), and click Connect.

 

In the next form select the Data Source Name(DSN) which was created in the previous steps, type the SQL statement (this is optional) and click OK.

 

Devart ODBC Drivers

Another way to connect and get data into Power BI for transformation is Devarts ODBC Drivers, unlike the classic ODBC Driver we have to download it from the devart.com page.

There are several ODBC drivers available to download from this page, these are MySQL,  SQL Server, Azure SQL, Oracle, Salesforce, MailChimp, MongoDB, NetSuite, and other data sources.

Connect Power BI To ODBC

There are few easy steps to follow to connect Power BI to Devart ODBC driver.

Step 1. Download the ODBC Driver from Devart ODBC drivers page.

Step 2. Install the driver and configure the ODBC data source from the, Get Data > Other > ODBC menu.

Step 3. Select the DSN that we had configured on Step 2 and type the credentials for the next step.

Step 4. Now, you are ready to get data into Power BI and begin to manipulate your data for the BI report.

 

Conclusion

Power BI Desktop is a tool with high potential for any data analytics team. They are able to use data from different types of exported files and services like JSON, Excel, SQL Server, IBM Informix, Azure SQL, Azure Table Storage, and many others. In this
post, we gave emphasis on how to Import data from a JSON file, and IBM Informix database, because the configuration for each of these is more complicated than the others.

 

References

 

See Also

468
Share This