Test with a sample use case

Visualization of cost per employee

The dataset for this use case is stored in BLOB storage in Azure (Home > Storage accounts > canopydata> defloc). Tables are created and made readily available to users on their default database. More information on the tables is found in the Tables tab.

  1. Launch Power BI. Select Get Data in the top of left corner on the home tab.

    ../../_images/13_powerbi_get_data.png

Alternately, you can select the Get Data option from the dialogue window that appears when you start Power BI.

../../_images/14_powerbi_signin.png

  1. In the Get Data dialog, scroll down and select Qubole Connector.

    ../../_images/15_powerbi_quboleconnector.png

  2. Click Connect to open the Qubole Connector dialog.

    • Enter the dns (data source name), which is available in ODBC configuration. For example: Qubole ODBC Driver DSN 64
    • In Data connectivity mode options, select the DirectQuery radio button and click OK.
    ../../_images/16_powerbi_directquery.png

  3. Click on the folder name to connect to the required database. On successful connection, tables within the database are listed. Click the desired tables to have preview of the sample data.

    ../../_images/17_powerbi_sampledata.png

  4. Click on Edit. This initiates a query to extract required data sets from the data source. Transform the raw data by filtering, sorting, and cleaning.

    ../../_images/18_powerbi_edit_data.png

    Example

    Suppose the user is only interested in selected columns from the table. Data can be transformed using options provided on the table icon below the formula bar in the left corner. Click on Table Icon followed by Choose Columns. Tick the required columns. A query that filters the required columns will be initiated and the data will be refreshed.

    ../../_images/19_powerbi_choose_columns.png

  5. After the transformation is done, the desired data is ready for visualization. Close the Edit window, and save for further analysis.

  6. Tables are listed in the right pane in the FIELDS section. Select the fields listed under the table for visualization. Here, employee_name and employee_salary are selected in the Staples table.

    ../../_images/20_powerbi_select_fields.png

  7. Choose the desired visualization, for example bar chart, column, or pie chart, by selecting the options shown below:

    ../../_images/21_visualizations.png

    You have now successfully created your first Power BI visualization with direct connection to Qubole services.

  8. You can publish the visualization created to app.powerbi.com to be shared with others. Click Publish in the top panel.

    ../../_images/22_publish-share.png

  9. If you are not signed in, you will be prompted to sign in. Once you sign in, select the destination workspace. Publishing will happen in the following way:

    ../../_images/23_publish_to_power_bi.png ../../_images/24_publishing_to_power_bi.png ../../_images/25_publishing_success.png

Before proceeding to view the dashboard created on app.powerbi.com, make sure you have installed an on premise or enterprise gateway. You can find the details here: https://powerbi.microsoft.com/en-us/gateway/.

  1. Configure the gateway for the Qubole Connector.

    1. Once the gateway is installed, sign in with your Microsoft credentials. Navigate to the Connectors section, as shown below, and provide the path where the mez file is placed. (Make sure this location is accessible to everyone on the system.)

      ../../_images/26_on_premises_data_gateway.png

    2. The QubolePresto connector name will appear as shown in the image above.

  2. Click the Open “<Dashboardname.pbix> in Power BI” link. This will take you to app.powerbi.com.

  3. Once you are on app.powerbi.com, click on Settings in the top right hand corner:

    ../../_images/27_settings.png

  4. Click on Manage gateways.

    ../../_images/28_manage_personal_storage.png

  5. Click on … beside the Qubole Azure Gateway and click ADD DATA SOURCE.

    ../../_images/29_gateway_cluster.png ../../_images/30_connected.png

  6. Fill in the data source name and select QubolePresto from the list of data source types:

    ../../_images/31_data_source_type.png

  7. Fill in the DSN. This DSN name should match the DSN with which you created the Visualization. Click Add.

    ../../_images/32_data_source_settings.png ../../_images/33_data_source_settings_2.png

  8. Click Settings in the top right corner, and click on Settings in the list displayed:

    ../../_images/34_mng_personal_storage.png

  9. Click on Datasets, and click on the datasets you uploaded from Power BI.

    ../../_images/35_datasets.png

  10. Make sure the gateway you added is associated with the dataset.

    ../../_images/36_use_a_gateway.png

  11. Now you will be able to view the visualization on app.powerbi.com and the visualization is ready to be shared.