How to connect Power BI Desktop to CDP Public Cloud Quick Start

[#1]

A great way to connect Power BI desktop to CDP Public Cloud is using the Cloudera Data Warehouse Experience with Hive LLAP.

Prerequisites

• CDP Public Cloud environment with Data Warehouse Experience using either Hive LLAP or Impala
• User will need a CDP login and will need to set up their Workload Password
• From their CDP login, they will need the Workload User Name and Workload Password

  

Note

Both Impala and Hive LLAP Virtual Warehouses will work with the Hive LLAP driver that is included with Power BI.  The steps below will work for both.  It is important to include port 443 when entering the server string in Step 6.

Steps to connect Power BI to CDW with Hive LLAP

1. From within the DW experience in the CDP Management console, select the Virtual Warehouse you want to use and click the  and select Copy JDBC URL: 

   

2. From that string, you will only need the bold portion such as the following:
   

   jdbc:hive2://hs2-azure-hive-1.env-5iskrk.dwx.cloudera.site/default;transportMode=http;httpPath=cliservice;ssl=true;retries=3

   We will call the bold portion the Server.
3. Open Power BI Desktop and select Get Data from the Tool Ribbon:
   
   
4. This will bring up the Get Data dialog box:
   
   
5. Search for and select the Hive LLAP (Beta) connector:
   
   
6. The Hive LLAP dialog box will be displayed. Do the following: 
1. Take the Server from Step 2 and add a colon and 443.
2. Enter the Database name.
3. Select HTTP for the Thrift Transport Protocol.
4. Select DirectQuery for the Data Connectivity Mode:
   
   
5. Click OK
7. An ApacheHive Dialog box will be displayed. Do the following:
1. For User Name, enter your CDP Workload User Name.
2. For Password, enter your CDP Workload Password:
   
   
3. Press Connect
8. A Navigator dialog box will be displayed. Do the following:
1. Select the Tables you want to use.
2. Click the Load button:
   
   
9. The new table will now be available for your Power BI report in the Fields list:
   
   

Enjoy!