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 databaseSAP Business Warehouse Application ServerOracle database
Access databaseSAP Business Warehouse Message Server (Beta)IBM DB2 database
SQL Server Analysis Services databaseAmazon RedshiftIBM Informix database (Beta)
IBM NetezzaImpalaMySQL database
PostgreSQL databaseGoogle BigQuerySybase database
Teradata databaseSnowflakeSAP 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.

 

 

SettingValue
ServerType the Database Server Name
DatabaseType the Database Name
Data Connectivity modeSelect 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 columnsThis option creates automatically relations between tables which they have relational columns.
Navigate using full hierarchyEnable this if need to drill into schema database
Enable SQL Server Failover supportThis 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.

 

 

SettingValue
WindowsType the Windows credentials to access the database
DatabaseType the SQL Server login credentials
Microsoft accountType 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 DatabaseOracle 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 DatabaseMySQL 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 databaseAzure SQL Data Warehouse
Azure Analysis Services databaseAzure Blob Storage
Azure Table StorageAzure Cosmos DB (Beta)
Azure Data Lake StoreAzure HDInsight(HDFS)
Azure HDInsight SparkHDInsight 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 listOData Feed
Active DirectoryMicrosoft Exchange
Hadoop File (HDFS)Spark
R scriptODBC
OLE DBBlank 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.

 

 

SettingValue
Server Namedatabase server name
Host NameIP or Computer Name, that Database Server is hosted
ServiceIBM Informix database server process that runs on your Host computer
ProtocolSelect the connectivity protocol  for communication with the server
Options
Database Nameselect the Informix database
User IdUser identification or account name for access to the DSN
PasswordPassword for access to the DSN

 

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

 

 

SettingValue
Client LocaleDefault Value: en_US.1252. For the demo purposes, we change it to en_US.CP1252
Database LocaleDefault Value: en_US.1252. For the demo purposes, we change it to en_US.57372
Use Server Database Locale
Translation LibraryDynamic linked library (DLL) that performs code-set conversion;
Translation OptionOption 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 Character0-Estimate
Fetch Buffer Size32767
Isolation LevelThe 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.

 

 

 


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

 


Share This