Home > Integrations Support > Third Party Integrations > Power BI & Excel

Power BI & Excel

 

Capabilities

  • Leverage D-Tools SI API to build reports and dashboards in Power BI or Pivot Tables in Excel
  • Automatically export projects to the API upon check-in
  • Access all project and project_items data as endpoints
  • Access to Tasks, Service Orders, Purchase Orders, Service Plans, and Catalog through our API service endpoints.
  • Compute additional values using Power BI DAX language (similar to Excel)

Use-case Examples

  • Create a list of all projects sold last year
  • Display a map with the location of all projects or service orders
  • What is the most used product and what is the #1 manufacturer? 

Limitations

  • Power Query (API query for Power BI and Excel) does not offer a way to auto-refresh when interacting with anonymous APIs. This is a popular feature request for Microsoft and we hope that they implement this capability in the future.
  • We do not offer development services for our API, Power BI (or Excel).
  • Users requesting access to this integration are fully aware that will be their responsibility to develop this integration.
  • This documentation provides an overview and a tutorial on how to create an API query using Power Queries, and that can be applied to any Microsoft product that supports Power Query.  
  • Users have reported that without a subscription to PowerBI or Excel, they are not able to implement an API request.

Details

Power BI is a business analytics solution that lets you visualize your data and share insights across your organization, or embed them in your app or website. Connect to hundreds of data sources and bring your data to life with live dashboards and reports.

 

Please visit the following link for details: https://powerbi.microsoft.com/en-us/

 

 

Pricing

This integration is only available for Software Assurance (SA) Subscribers. Please also note it is only supported by the latest versions of D-Tools System Integrator. While the connection to our API is included in your SA subscription, Power BI offers different pricing levels. The free version of Power BI or Excel may not work: https://powerbi.microsoft.com/en-us/pricing/

 

Access

Please request access to the integration by sending an email to api@d-toolshelp.com prior to perform the setup. D-Tools will enable access to your specific account within 48 hours.

 

 

Understanding how our API works

Please take a few minutes and familiarize yourself with how our API works. The following article provides an overview of the functionality and technical aspects of our API: https://support.d-tools.com/Integrations_Support/Administrator_Guide/Cloud_API_Technical_Aspects

 

 

Initial Setup

In order to use this integration, you must first set it up. This is a one-time setup. You will need an active Power BI account and have requested the API key to us. The below setup will walk you through the process of Setting up the integration with Power BI for access to D-Tools Projects. The setup for the additional API services is similar, and can be appended to your project setup.

 

1. In SI, go to Start->Control Panel, then open "Manage Integrations".

 

Important note: If this feature is not available, please send an email to api@d-toolshelp.com requesting access to this particular integration, and we will enable it for you. Please note you must be on the latest version of D-Tools and subscribing to Software Assurance (SA) to access this feature.

 

clipboard_e2eb9cc8040babea577b4895182950a89.png

 

 

 

2. Select Power BI, then click the "View API Key" link

PowerBI.JPG

 

 

3. Click the [Copy] button to copy your API Key and then close these windows.

 

 

4. Export a few projects to Power BI - From the Project Explorer, please select the Project (or Projects) you wish to export, Check each project out (click on "Check out"), and then click the Integrations tab, then on the [Export] button:

 

- 5.JPG

 

 

5. Open Power BI -> Get Data -> Web

 

PBI1.JPG

 

 

6. Click on Advance

Enter the URL part, in this case, https://api.d-tools.com/SI/Subscribe/Projects 

Here is more info about this service endpoint: https://api.d-tools.com/SI/doc/Api/GET-Subscribe-Projects_clients[0]_clients[1]_progresses[0]_progresses[1]_includeImported_searchText_pageNumber_pageSize_projectNumber 

 

Enter the X-DTSI-ApiKey followed by your API key

 

This is how it should look like:

PBI2.JPG

 

7. You should now see a list of all projects published to the API, and that are being consumed by Power BI:

PBI3.JPG

 

8. We now recommend adding the following steps:

  • Source (you are already on this step)
  • Converted to Table
  • Value
  • Convert to Table1
  • Expanded Column1
  • Added Custom (see step 9)
  • Expanded Custom

 

 

 

9. On the "Add custom" step, please create a new Query with the following code:

 

(ID as text) =>
let
    Source = Json.Document(Web.Contents("https://api.d-tools.com/SI/Subscribe/Projects?id="&ID, [Headers=[#"X-DTSI-ApiKey"="API KEY"]]))
in
    Source

 

 

This is how it should look like:

PBI4.JPG

 

 

10. At this point, you should have all your project data loaded on the query. You can further expand the items list using the same method described in step 9.

PBI5.JPG

11. Close and Apply. You can now build reports and dashboards.

 

If you need help or have any questions, our Professional Service team can be consulted and we can help. Please send an email to api@d-toolshelp.com requesting assistance.

 

 

 

 

 

Last modified

Tags

This page has no custom tags.

Classifications

This page has no classifications.