Data Integration
      Back to home
      1. Datagration Knowledge Base
      2. Getting Started
      3. Data Integration

      How Do I Retrieve Data in Power BI?

      Use PetroVisor to build your own visualizations in Power BI.

      This article describes the requirements for connecting to Power BI and the steps to connect to both a table and a script.

      Requirements

      Connect to a Table

      Connect to a P# Script

      Advanced - Configuring an Automatic Refresh

      Requirements

      • Power BI Desktop must be downloaded.
      • Install the Power BI Connector
      • Edit Power BI Security

      Download the Power BI Connector

      Check to with your PowerBI expert to ensure that the link below is the latest version before downloading.

      For more information from Microsoft, the link below is to the Power BI Connect.

      https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-data-sources

      The file should download to your download file.

      Tip:  A best practice for the Power BI Connector is to save it in its own folder on your desktop.   This PC > Documents > Power BI Desktop > Custom Connectors

      Edit the Power BI Security

      In order for the connector to work correctly, Power BI needs to have the security feature updated to recognize the connector.

      • Open the Power BI Desktop App.
      • Click File.

      • Click Options and Settings.

      • Select Options.

      • Select Security.

      • Under Data Extensions>Change to (Not Recommended) Allow any extension to load without validation or warning.

      • Click OK.

      • Restart Power BI.

      Connect to a Pivot Table

      • Click Get Data in Power BI.

      • Click More from the drop-down menu.

      • Select Other.
      • Select PetroVisor Cloud API.

      • Click Connect.

      A warning pop-up.

      • Click Continue.

      • Enter the API Get request, (example here is) PivotTables/Static Data Set/Saved
      • Enter the Workspace Name, (example here is) Dashboard Demo

      Example

      Example below is for connecting to a PV Pivot Table using the cloud connector where ‘name’ is your pivot table name, and ‘Name of your Workspace’ is your Petrovisor workspace name. If the pivot table data is not saved in PV (just the definition), you may substitute in ‘Generated’ instead of ‘Saved’. However, some generated definitions may time out the connector so saved is preferable.


      The Web API GET request needs to match the name of the table in the workspace.  Some errors that occur are extra spaces, naming the table with initials.

      Web API GET requests can be found via the following link:

      https://api.us1.petrovisor.com/index.html

      • Click OK.

      Sign In to PetroVisor.

      • Click Sign In.

      • Click "Sign in" and enter your username and password.
      • Connect.

      • Notice the API Get Call used to get the Static Table.

      Notice it shows the Pivot Table and the API Get call to retrieve the data.

      • Click "To Table."

      • Click OK. Do not specify a delimiter.

      • Click the drop down.

      • Click Extract Values.

      • Select the Tab as the delimiter from the drop-down menu. 
      • Click OK.

      Notice that the data comes into one (1) Column.  The data needs to be split into different columns.

      • Click Split Column.
      • Selecty By Delimiter.

      Ensure that the delimiter selected is Tab.

      • Click OK.

      The static data is now split into 5 columns as specified in PetroVisor.  Notice that the first row is the header row and needs to be specified.

      • Click Use First Row as Headers.

      • Check the data types that Power BI has selected the correct text types.


      This step is a good check to make sure the table resembles what was created.

      • Rename the table.

      • Select Close & Apply.

      Ensure the table was loaded.

      • Click Data Field.

      Notice that the data is available in Power BI.


        Connect to a P# Script

        • Click Get Data.

        • Click More from the drop-down menu.

        • Select Other.
        • Select PetroVisor Cloud API.

        • Click Connect.

        A warning pop-up.

        • Click Continue.

        This connection will be to the example "Value Summary."

        • Enter the API Get request, (example here is) PSharpScripts/Value Summary/ExecuteAsTable
        • Enter the Workspace Name, (example here is) Dashboard Demo

        The Web API GET request needs to match the name of the table in the workspace.  Some errors that occur are extra spaces, naming the table with initials.

        Web API GET requests can be found via the following link:

        https://api.us1.petrovisor.com/index.html

        • Click OK.

        Sign In to PetroVisor.

        • Click Sign In.

        • Click "Sign in" and enter your username and password.
        • Connect.

        • Notice the API Get Call used to get the Value Summary Table.

        Notice it shows the Pivot Table and the API Get call to retrieve the data.

        • Click "To Table."

        • Specify a Tab delimiter.  The delimiter can be selected here because the data comes in with more structure than as a Pivot Table.

        • Click Use First Row as Headers.

        • Check the data types that Power BI has selected the correct text types.


        This step is a good check to make sure the table resembles what was created.

        • Rename the table.

        • Select Close & Apply.

        Ensure the table was loaded.


        Advanced - Configuring Automatic Refresh

        By default, 3rd party connectors do not refresh automatically in PowerBI. However, there is a feature called the Data Gateway that allows you to use a server for performing data refreshes.

        To refresh automatically your report must be published to a PowerBI server. With 3rd party adapters (the PetroVisor Connector) you'll need to use an "On-Premise Data Gateway". This data gateway will not access anything in your network but will communicate the PetroVisor cloud or on-premise server. We recommend install this on a standalone server or virtual machine and it does not need direct access to anything other than the PetroVisor HTTP(S) ports. If this machine is not online the reports will be unable to refresh automatically. 

        1. Install the Enterprise Gateway as detailed here.
        2. Add the Connector file to a folder and point the gateway connectors settings to here. Ensure that the service account the gateway uses has access to it.
        3. Restart the Gateway Service
        4. Verify the Connectors tab shows the plugin loaded (as seen in the screenshot above).
        5. Modify the settings on your data source in PowerBI on the web
        6. Select the Data Sets Tab
        7. Your Gateway should appear but say it is not configured
        8. Click the Arrow in the Actions table to expand the errors
        9. Select "Manually add to gateway"
        10. Fill in the parameters as defined in your report
        11. Click "Edit Credentials" and log in to petrovisor, then add your datasource
        12. Return to the Gateway connection, expand the gateway (>) and map your credentials and choose Apply.
        13. Ensure that your Scheduled Refresh is Enabled
        14. You're done! Your reports should automatically refresh.

        We realize this is a confusing step and are working with Microsoft to make this easier. If you need any assistance, please reach out to the PetroVisor team and we're happy to help!