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. At the top left corner on the home tab, click Get Data.

    ../../../_images/13_powerbi_get_data1.png

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

    ../../../_images/14_powerbi_signin1.png

  2. In the Get Data dialog, select Qubole Connector.

    ../../../_images/09_powerbi_getdata.png

  3. Click Connect to open the Qubole Connector dialog.

    • Enter the DSN (Data Source Name), which is available in ODBC configuration. For example: Qubole ODBC Driver DSN 64.
    • In Data Connectivity Mode field, select the DirectQuery radio button and click OK.
    ../../../_images/10_powerbi_connectivity_mode.png

  4. Click 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_sampledata1.png

  5. Click 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_data1.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 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_columns1.png

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

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

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

    ../../../_images/21_visualizations1.png

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

  9. You can publish the visualization created to Power BI app to be shared with others. Click Publish in the top panel.

    ../../../_images/22_publish-share1.png

  10. 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_bi1.png ../../../_images/24_publishing_to_power_bi1.png ../../../_images/25_publishing_success1.png

    Before proceeding to view the dashboard created on Power BI app, make sure that you have installed an on premise or enterprise gateway. You can find the details here: Power BI.

  11. 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_gateway1.png

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

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

  13. Once you are on Power BI app, click Settings in the top right hand corner:

    ../../../_images/27_settings1.png

  14. Click Manage gateways.

    ../../../_images/28_manage_personal_storage1.png

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

    ../../../_images/29_gateway_cluster1.png ../../../_images/30_connected1.png

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

    ../../../_images/31_data_source_type1.png

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

    ../../../_images/32_data_source_settings1.png ../../../_images/33_data_source_settings_21.png

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

    ../../../_images/34_mng_personal_storage1.png

  19. Click Datasets, and select the datasets you uploaded from Power BI.

    ../../../_images/35_datasets1.png

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

    ../../../_images/36_use_a_gateway1.png

You can experience and share the visualization on Power BI app.