While the new Deltek Vantagepoint REST API is secure and convenient, it is sometimes a hassle to set up a valid connection that include the Bearer token and takes care of all the authentication details.
It’s especially tricky when you want to pull data into some data tools like Microsoft Excel, Microsoft PowerBI or other reporting applications.
This is why I started this new PassThrough Connector project that will allow you to define as many connections to different Deltek Vantagepoint endpoints and then communicate with them via an API Token that can be passed along with the standard REST call.
The project login page is located here: https://passthroughconnector.steepvalley.net/
This site is not affiliated with Deltek and is not part of their service offerings. The site uses non-proprietary technology to query the standard REST API provided by Deltek Vantagepoint.
Also, please do not judge me on my admittedly poor design skills – this is more about the functionality of it. But please feel free to send in comments and suggestions on how to make this better…
A Word On Security
All your connection information is stored as encrypted text and no one but your account will be able to view or edit the Vantagepoint Connection Info.
Also, the generated API Key is a 32bit GUID and cannot be guessed by anyone. But should someone get access to that API key they will be able to query your Vantagepoint instance without any further authorization necessary.
So please keep the API Key save and maybe even change it once in a while.
To set up a connection you first have to log in. You can do so by clicking on the “Register” or “Login” which will bring you to the default login screen.
You can simply provide an email and password or you can sign in with your preferred service provider (Google, Microsoft, Facebook, Twitter). In this case the site will only receive the email address as a reference.
When you register for the first time you will see an Account Information screen. None of these fields are mandatory.
After you confirm the account info or if you log into an existing account you will see the Configuration Overview.
Here you can add, modify or remove connections to Vantagepoint and also check your license status
By default every new user receives a trial balance that allows for 100 connections per month at no cost at all. Should you need more than these then please contact me. (see additional pricing below)
Click on “+ add new connection” to get started with the setup.
- In the new page, give your connection a name.
- A new API Token has already been provided by you can always reset this to a new API (which will then invalidate the old API key)
- Enable and/or disable the allowed REST commands. As a security precaution you should only allow for GET commands.
- Then enter the necessary Vantagepoint information including the base URL to your Vantagepoint installation and the connection details from your API Configuration screen in Vantagepoint.
- Validate your connection
Once the connection has been established you are ready to use the API key
If you click on the “check out some samples” link you can see how you would request data from this passthrough service and how it would return the info from your Vantagepoint instance to the caller.
You can also copy the sample into Excel or PowerBI to get started (see below)
Real Word Usage
Using this in Excel is really easy. Start a new Excel workbook and go to the “Data” tab. There click “From Web” or “Get Data” – “From Web” in the toolbar.
In the dialog, paste in one of the sample URL from my sample page and click OK
Confirm the “Anonymous” option and click Connect
In the Power Query Editor you now see a list of items that all say “Record”. Click on the “To Table” option on the top toolbar and confirm the default options in the next dialog
The editor now still shows the same “Record” list but also shows a new button “Split Column”.
Once clicked it will present you with a list of columns from Vantagepoint. Confirm and you will now see all the detailed record rows and columns broken out for you. Click on “Close and Load” to get the data back to Excel.
Now you can use it to run mass emails, run reports for accounting or anything else you need to do
Microsoft Power BI
Microsoft Power BI uses the same Power Query Editor as Excel so you can pretty much follow the same steps as above