Integrate with Power BI

  • Updated
  • Updated
Avatar
Written by Charissa F.
  • Updated
Platform — Premier

Note

Some of the features covered in this article may not be a part of your current licensing plan. Contact your Sales Representative to learn how you can get access.

Connect Samsara data and third-party data into Microsoft Power BI to create KPI dashboards and uncover actionable business insights without having to write code.

Supported Data Types

The connector provides a rich range of data that enables you to create dashboards and reports that fit your specific needs and use cases. The following table displays in alphabetical order popular Samsara reports and fields that you can use in Power BI:

Data Type

Fields

Attribute Assignments

Attribute name, Attribute ID, Object type, Object ID, Attribute assignment ID

Coaching Sessions

Session ID, Driver, Assigned Coach ID, Coachable Behavior Type, Behavior Note, Behavior Last Coached, Coaching Type, Session Status, Session Note, Session Updated At

Devices

Asset ID, Health Status, Last Connected, Cellular Data Used, Hotspot Usage, Model, Serial

Document Conditional Fields

DocumentID, Conditional Field First Index, Last Index, Triggered Field Index, Value

Document Fields

Document ID, Field Type, Label, Value {BarcodeType, Barcode; DateTime; MultipleChoiceSelected, Value; Number; Photo URL; Scanned Document URL; Signature Name, Signed At Time, Signature URL; Text}

Documents

Document ID, Document Type, Document Name, Notes, Route ID, Route Stop ID, Driver ID, Vehicle ID, Created At Time, Updated At Time

Driver Fuel And Energy

Day UTC, Driver ID, Energy used (kWh), Distance traveled (km), Idling time (min), Efficiency (mpg), Carbon emissions, Fuel energy cost amount, Engine hours, Fuel consumed (L)

Driver Coach Assignments

Driver ID, Coach ID, Assignment Created At, Updated At

Drivers

Driver ID, Name, Activation status, ID card number, License number, License state, Timezone, ELD exempt, ELD day start hour

DVIR Defects

Vehicle ID, Trailer ID, Defect Type ID, DVIR ID, Defect ID, Created At Time, Is Resolved, Resolved At Time, Resolved By, Mechanic Note, Comment

DVIR Defect Types

Defect ID, Label and Section

DVIRs

Vehicle ID, Trailer ID, Type, Submission Begin Time, Submission Time, Updated Time, Signed At Time, Signature ID, Formatted Address, Mechanic Notes, Odometer, Safety Status

Equipment Stats

Timestamp, Equipment ID, Name, Odometer, Engine hours, Engine RPM, Engine state, Fuel percent

Fleet Vehicles

Fleet vehicle ID, Name, VIN, Make, Model, Year, License plate, Vehicle regulation mode

HOS Daily Logs

Driver ID, Vehicle ID, Start Time, End Time, Drive Distance, Personal Conveyance Distance, Yard Move Distance, Duty Status Durations, Certification Status, Certified Time, Claims, Carrier information, Home Terminal Address, Shipping Docs, more

HOS Logs

HOS Logs ID, Driver ID, HOS Status Type, Log Start Time, Log End Time, Lat/Long, Vehicle ID, Remark

HOS Violations

Type, Description, Driver ID, Start Time, End Time, Duration, HOS Violation ID

Idling Segments

Idling segment ID, Start time, End time, Duration (min), Vehicle ID, Fuel consumed (L), PTO active

Route Stops

Route ID, Route Stop ID, Scheduled Start Time, Scheduled End Time, Actual Start Time, Actual End Time, Skipped Time, Route Stop State, Address ID, Location Lat/Long, Location Street Address, Route Stop ETA, Route Stop Name, Notes

Routes

Route ID, Route Name, Notes, Driver ID, Asset ID, Scheduled Start Time, Scheduled End Time, Actual Start Time, Actual End Time

Safety Events

Safety events ID, Time, Driver ID, Vehicle ID, Coaching state, Max acceleration, Latitude, Longitude, Behavior label, Behavior name, Behavior source

Tag Assignments

Tag name, Tag ID, Parent tag ID, Object type, Object ID, Tag assignment ID

Trailer Stats

Timestamp, Trailer ID, Name, Location information, Odometer, Engine hours, Fuel percent, Reefer Substate pretrip and defrost values, Reefer Run Mode, Reefer State Zone, Set Point Temperature, Supply Air Temperature, Return Air Temperature, Door State Zone, Ambient Air Temperature

Note

All temperature readings in MilliCelsius for Zones 1-3.

Trailers

Trailer ID, Name, Serial Number, License Plate, Gateway Model and Serial (if applicable), Notes

Vehicle Fuel and Energy

Day UTC, Vehicle ID, Energy used (kWh), Distance traveled (km), Idling time (min), Efficiency (mpg), Carbon emissions (kg), Fuel energy cost amount, Engine hours, Fuel consumed (L)

Vehicle Stats

Timestamp, Vehicle ID, Currently assigned driver ID, Location information, Geographic information, Last speed value, Odometer value, Engine hours, Fuel percent

Note

This table provides a snapshot of readings at the time the table is generated or refreshed.

Users

User ID, Name, Email, Role and Tag combinations

Some tables, such as Idling and Vehicle Fuel and Energy, can take longer to load depending on the window of data selected (up to 6 months of data). In such cases we recommend an async scheduled refresh or limiting the query window.

Equipment Stats and Trailer Stats tables provide a snapshot of readings at the time the table is generated or refreshed.

Note

This table is not a comprehensive list of all the data fields available with the connector but lists the most common and relevant fields. To view data types in beta, see [Beta] Additional Data Types in Power BI.

Supported Templates

Samsara provides the following templates to populate Power BI with data from your organization.

To use a template:

  1. Download the template, according to your data needs, using the above links.

  2. Open the .pbit file, which opens Power BI.

  3. Enter the region and date inputs.

  4. Enter the Samsara API token for your organization.

    The token must be valid to establish the connection.

Requirements

  • Power BI:

    • Power BI Pro subscription

      Premium workspace upgrade required for incremental refresh with dataflows

    • October 28, 2024 release for Power BI desktop, or later

      April 2024 desktop release limited to 9 GA tables

    For information about pricing and product comparison, see the Microsoft Power BI documentation.

  • Samsara:

    • API access from Power BI Marketplace app install

      Table access corresponds to your API access, which is subject to your Samsara plan. For example, to access the IFTA table in Power BI, you will need the license named for the IFTA API (Tel-Prem). To review if any data type requires a specific license review Required Licenses for API and Webhooks Usage.

    • (Optional) If you want to limit access to a group of assets, it is recommended to assign Tags to those assets in the Samsara dashboard.

Deployment

Samsara built the integration and will assist with deployments as needed. Follow the steps below to get started. If there are any issues, please contact Samsara Support. For additional setup guidance and common questions for the Power BI Connector, see Power BI Connector FAQs.

Note

The Power BI connector is compatible with other Power Query products (besides just Power BI Desktop).

Refer to the Microsoft Power BI documentation for more information.

  1. Log in to the Samsara dashboard.

  2. (Optional) Tag any relevant assets with Power BI Tracking if you want to limit access to only those assets.

  3. Install the app integration.

    1. Select the Settings icon ( gear icon ) at the bottom of your Fleet menu and then click Apps.

    2. Search for and then select Power BI to install the app.

    3. If you tagged your assets to limit access, change the access scope from Entire Organization to the tag name.

    4. Click Allow to agree to the terms of the Samsara Data Import and Sharing Addendum.

    5. Click Generate Token.

    6. Record the token as you will need it to set up the following installation steps. The token can be regenerated if necessary.

  4. Install Power BI Desktop.

    The Power BI Desktop installation is the same for both standard and personal gateway.

  5. Install Power BI Service.

    There are two ways Power BI Service can be set up: standard and personal gateway. The standard gateway has a longer installation process but is recommended for organizations where multiple people access multiple data sources.

For additional setup guidance and common questions for the Power BI Connector, see Power BI Connector FAQs.

Install Power BI Desktop

Install Power BI Desktop using one of the following options:

Install Power BI Desktop Using Dataflows

Dataflows are collections of tables managed in the cloud, enabling direct connection to Power BI Service without a gateway and may provide quicker refresh times.

  1. Go to your selected Workspace in the Power BI Service.

    Note

    You cannot use your personal workspace My Workspace to connect to Dataflows.

  2. Select New > Dataflow > Add new tables.

  3. Search for and then select the Samsara source that appears.

    Although this connector is certified by Microsoft and available for GA, Microsoft requires that the beta label is displayed for the first certified version.

    power-bi-install-desktop-dc-samsara.png

  4. Enter the required and optional fields.

    The data gateway is not required for this connection. If you choose to use a gateway, ensure it is already installed and configured.

    power-bi-install-desktop-dc-fields.png

  5. Select the data you want to load and click Transform data.

  6. Open Power BI Desktop, and then go to Get Data > Dataflows.

  7. Sign into Dataflows using your Samsara account.

  8. Select the workspace folder where the Dataflow connection was established. Choose the relevant tables and load them into Power BI Desktop.

  9. Create your Power BI report.

    If you want to publish the report to Power BI Service and set up refreshes, continue with the following steps.

    1. Save your report and click Publish in Power BI Desktop.

    2. Locate the published report in your Power BI Service workspace and click Schedule refresh.

      Note

      Setting up scheduled refreshes using Dataflows does NOT require a gateway. A Personal Cloud connection will be indicated as green alongside PowerPlatformDataflows as your data source credentials.

      power-bi-install-desktop-dc-data-sources.png

    3. To schedule a refresh, expand the Refresh dropdown, select your desired settings, and then click Apply.

    Refresh and Power BI Service connection is complete.

Install Power BI Desktop Using a Direct Connection

  1. Open Power BI Desktop.

  2. On the popup, click Get data. If necessary, you may need to go to File > New.

  3. Search for Samsara. Then select Samsara (Beta) and click Connect.

    Note

    Although this connector is certified by Microsoft and available for GA, Microsoft requires that the beta label is displayed for the first certified version.

  4. Enter the region and time range in Month/Day/Year format for how long you want to pull data, and then click OK. This data connector currently has a 6 month limit for data refresh. This means it can only retrieve the last 6 months of data.

    The start date and end date fields are text fields, not date types. This is because Microsoft does not support entering date fields when using the standard gateway. To avoid errors later in this process, ensure that you use the proper format.

  5. Enter Samsara API token you generated when you installed the Power BI app and click Connect.

    After entering Samsara API token, you can then view all the data tables available and select which ones to load. Some tables will take a while (up to a few minutes) to load, especially for larger datasets.

Install Standard Gateway

  1. Download the Power BI Gateway – standard mode.

  2. Sign in with your Microsoft credentials.

  3. Click Configure to begin the gateway setup.

  4. Select Register a new gateway on this computer and then click Next.

  5. Fill out the required fields for gateway name and recovery key.

    Note

    The recovery key is not the same as API token.

  6. Review the gateway status to confirm it is ready for use.

    power-bi-standard-gateway.png

  7. Go to Power BI Service online, and then select Settings > Manage connections and gateways > On-premises data gateways.

    Verify that you can see the connection you created.

    power-bi-standard-gateway-on-prem.png

  8. Return to the gateway popup and click Connectors.

    You should see the name of your Samsara connector. This ensures that the service account the gateway is using has the correct permissions to access the folder in which the connector was installed.

    power-bi-on-prem-gateway.png

    If you don't see the Samsara connector, the folder path was not set up correctly with the gateway installation or the Power BI Service account does not have permission to read the folder path.

    To resolve issues with the path, try the following steps.

    1. In Windows Explorer, go to the folder path you previously created in the Power BI installation steps: ~/Documents/Power BI Desktop/Custom Connectors

    2. Right click Custom Connectors and choose Properties.

    3. Go to the Security tab and verify that the user PBIEgwService exists in the list. If the user does not exist, proceed to create the user as described in the following steps.

    4. Click Edit and then Add the user.

    5. Enter the name NT SERVICE\PBIEgwService and then click OK.

    6. Ensure the user PBIEgwService has the following access permissions:

      • Read & execute

      • List folder content

      • Read

    7. Click OK.

    8. On the Log On tab, change the service account for the On-Prem Data Gateway Service to the Local System account.

      power-bi-standard-gateway-local-perms.png

    9. Click OK.

    10. Verify that you can now see the Samsara connector on the Connectors section of the On-premises data gateway page.

  9. Publish the report in Power BI Desktop that you want to see in Power BI Service. The published report should be visible in your Power BI Service workspace.

  10. Select Schedule Refresh for the dataset (calendar icon next to the three dots and refresh icon).

  11. Select Datasets > Gateway and cloud connections and verify that you see the gateway listed with a not configured correctly status.

    power-bi-standard-gateway-not-connected.png

  12. Click on the gear icon next to the gateway to manage connections and gateways.

  13. Select the two options under Power BI section to enable refresh.

    power-bi-standard-gateway-connection-options.png

  14. Save your changes and close.

  15. Create a new connection for the standard gateway.

    1. Click New and fill out the required fields.

      power-bi-standard-gateway-connection-options-comp.png

      If Samsara API Get Records (Beta) does not display under Connection type, there is a path issue.

      Note

      The start date and end date fields are text fields. Ensure the text you enter follows the proper format.

    2. Click Create and then close the pop up.

      The new connection should appear if created successfully and is green when running. This connection is shareable and reusable among members of the same organization.

  16. Return to My workspace > Schedule refresh > Datasets >Gateway and cloud connections.

  17. Click the dropdown under Maps to and select the proper gateway.

  18. Click Apply.

    The Standard Gateway installation is fully complete. You can now refresh the report and dataset.

Install Personal Gateway

  1. Download the Power BI Gateway - personal mode.

  2. Sign in with your Microsoft credentials.

  3. Click Connectors in the left panel.

  4. Ensure that the path matches the path for the Power BI Desktop installation.

    The Samsara connector will not appear if the path is incorrect.

  5. Click Close.

    Your Power BI Gateway is ready to use.

  6. Set up a schedule refresh.

    1. Verify the credentials are connected in the Data source credentials section.

      power-bi-personal-gatway-data-source-creds.png

    2. If the credentials are not connected, click Edit credentials. Then enter the same API token that you used to install the Power BI Desktop under Account key and select the privacy level settings you prefer.

      power-bi-connection-api-key.png

    3. To schedule a data refresh, click Refresh and fill out the required information.

      power-bi-schedule-refresh-settings.png

    To manually trigger a refresh, you can also click the refresh icon for the connection.

    power-bi-schedule-refresh.png

Support

Refer to the Microsoft Power BI documentation for more information.

For issues related to the integration, contact Samsara Support.

Feedback

Was this article helpful?
7 out of 13 found this helpful
Have more to say? We want to hear it!
Have more questions? Submit a request
Return to top

Comments

0 comments

Article is closed for comments.

Related articles

Articles in this section

©2024 Samsara Inc.

Chat with us