Back to top


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.

 

Back to top


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.  

 

Back to top


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.

  

 

Back to top


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. 

  

 

Back to top


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

 

Back to top


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.

 

Back to top


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.

 

 

Back to top


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. 

 

Back to top


References

 

Back to top


See Also

 

Back to top