The article explains everything you need to know about integrating Power BI and Microsoft Excel with SI.
Capabilities
Leverage D-Tools SI API to build reports and dashboards in Power BI and 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 the catalog through our API service endpoints.
Compute additional values using Power BI DAX language (similar to Excel).
Use Cases
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 who 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 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 know it will be their responsibility to develop it.
This documentation provides an overview and a tutorial on creating an API query using Power Queries, which can be applied to any Microsoft product that supports Power Query.
Users have reported that they cannot implement an API request without a Power BI or Excel subscription.
Details
Power BI is a Business Analytics solution that lets you visualize your data, share insights across your organization, and embed them in your app or website.
It can 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 that 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 emailing api@d-toolshelp.com before performing the setup.
D-Tools will enable access to your specific account within 48 hours.
Understanding the SI API
Please take a few minutes to familiarize yourself with how our API works. The following article provides an overview of our API's functionality and technical aspects: https://docs.d-tools.com/en/articles/9225625-d-tools-si-api-overview.
Initial Setup
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 from us.
The steps below will walk you through 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 unavailable, please email api@d-toolshelp.com requesting access to this particular integration, and we will enable it. Please note that you must be on the latest D-Tools version and subscribe to Software Assurance (SA) to access this feature.
2/ Select Power BI, then click the "View API Key" link
3/ Click the Copy button to copy your API Key and 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/ Open Power BI > Get Data > Web.
6/ Click on Advanced.
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.
7/ You should now see a list of all projects published to the API being consumed by Power BI.
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.
10/ At this point, you should have all your project data loaded on the query. You can expand the items list using the method described in step 9.
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.