Configuring Power BI to Connect to DLI for Data Query and Analysis

Preparations

• Environment requirements:

  Make sure that your system environment meets the following requirements:

  • Operating system: Only Windows 10 and Windows 11 (64-bit) are supported.
  • Power BI does not support parsing complex field types: Map, Struct, and Array.
• Toolkits:
  • DLI ODBC driver: dli-odbc-xxx-setup-x64.exe
  • Power BI installation package: Power BI installation package. Select the installation package for Windows (64-bit).

• Preparing connection information

  Table 1 Connection information

  Item	Description
  DLI AKSK	AK/SK-based authentication refers to the use of an AK/SK pair to sign requests for identity authentication.
  DLI's endpoint address	Endpoint of a cloud service in a region.
  DLI's project ID	Project ID, which is used for resource isolation.
  DLI's region information	DLI's region information

Step 1: Configure ODBC

1. Install the ODBC driver.
   1. Obtain the ODBC installation package by referring to Preparations.
   2. Double-click dli-odbc-xxx-setup-x64.exe to install the ODBC driver.

      After the installation is complete, a Path environment variable is automatically generated. Do not delete this environment variable.

      

2. Connect ODBC to DLI.
   1. In Windows, click Control Panel, double-click Administrative Tools, and double-click ODBC Data Source (64-bit).
   2. Configure an ODBC data source.
      1. Click User DSN.
      2. Click Add.
      3. Select Huawei Cloud DLI Driver (64-bit) and click OK.
      Figure 1 Creating an ODBC data source connection
      
   3. In the dialog box that appears, enter the information for connecting to DLI through ODBC.

      Table 2 Parameters for connecting Power BI to DLI

      Parameter	Mandatory	Description	Example Value
      Data Source Name (DSN)	Yes	Custom DSN name	-
      DLI Server Region (REGION)	Yes	DLI region ID	cn-north-4
      DLI Server Host (HOST)	Yes	DLI endpoint address dli. {REGION}.{domainName}	dli.cn-north-4.myhuaweicloud.com
      Project ID (PROJECTID)	Yes	ID of the project where DLI resources are	0b33ea2a7e0010802fe4c009bb05076d
      Access Key (AK)	Yes	AK that acts as the authentication key	-
      Secret Key (SK)	Yes	AK that acts as the authentication key	-
      Database Name (DATABASE)	Yes	DLI database name	dli
      Queue Name (QUEUENAME)	Yes	DLI queue name (only Spark SQL queues are currently supported)	dli_test
      Obs EndPoint (OBSENDPOINT)	Yes	OBS endpoint address obs.{REGION}.{domainName}	obs.cn-north-4.myhuaweicloud.com
      Extra Configuration (KEY=VALUE;)	No	Other parameters: For example: The JOBTIMEOUT parameter specifies the maximum running time of the SQL statement; if not set, the default is 3,600 seconds, and the SQL statement is canceled upon timeout. spark.sql.shuffle.partitions is a Spark runtime parameter.	JOBTIMEOUT=3600; spark.sql.shuffle.partitions=100;

      Figure 2 ODBC data source connection parameters
      
   4. Click Test to check whether the data source connection is successful. If successful, click OK to save the connection.

Step 2: Connect Power BI to DLI Using ODBC

1. Click and install Power BI. Obtain the Power BI installation package. Select the installation package for Windows (64-bit).
2. Start Power BI Desktop.
3. On the Home tab, click Get data.
4. Click More... to see other types of data sources available.
5. Select ODBC from the list as the data source type and click Connect.
6. In the ODBC Driver Manager window that appears, select the ODBC data source configured in Step 1: Configure ODBC and click OK.

   Power BI connects to DLI using ODBC and allows you to preview and select tables and views in the database.

   

   When previewing database tables, select limit. Otherwise, all partitioned tables are scanned.