PowerBI calling a REST Api for each row in a table.

Take this scenario.
Your writing a PowerBI report to pull data from a REST Api. The Api doesn't provide a search route which returns all the rows and data you require in one call. You can retrieve a list of ID's for the object type you require but a separate call is required to get the individual details per row. Or you may have an existing table with the IDs stored, and require additional details which is provided by an API call per row.

I was asked about this scenario by a colleague, and it is definitely able to be solved with PowerBI. I will urge caution though as if you have large recordset on the base table, and you plan to do an API call per row, that will be slow and result in a large number of calls to the API in a short duration which could be flagged as an attack by some monitoring systems.

Demo API

For this blog I have provided a demo NodeJS API that you can download and run in VSCode, but you could also use any public API, such as my SQL Versions API (details on that will follow at a later time).

To run the API, follow the instructions in README.md.


Demo Data

The demo API has some static sample data. A collection of Course Instructors, which has a mapping to the Course collection and also a mapping to a Person collection. The complete sample data was taken from an old .Net app sample (https://msdn.microsoft.com/library/bb399731(v=vs.100).aspx).

Here is a snippet of the structure and data:

CourseInstructor = [
    {
      "CourseID": "1045",
      "PersonID": "5"
    },
    ...
];
Course = [
    {
        "CourseID": "1045",
        "Title": "Calculus",
        "Credits": "4",
        "DepartmentID": "7"
    },
    ...
];
Person = [
    {
        "PersonID": "1",
        "LastName": "Abercrombie",
        "FirstName": "Kim",
        "HireDate": "1995-03-11 00:00:00.000",
        "EnrollmentDate": null
    },
    ...
];



Lets get start

1. Get the base source
For this walk through lets start with the CourseInstructor data. This is a list of CoursID's and PersonIDs, but no other details. We will then use the individual API calls to build the completed table.
In the Demo API the API route is http://localhost:3000/api/courseinstructor.

In PowerBI select Get Data

In the form type "web" to narrow the results and locate the

In the next screen enter the API route (e.g. http://localhost:3000/api/courseinstructor). This should be a HTTPS uri for security.

If your API needs Authorization, or a Token, then you should set the appropriate headers. I have an example in one completed solution at the end.

2. Convert to table

The results will first be shown in a list as PowerBI will use the Json.Document() function to retrieve the data.

Click the "To Table" button in the menu ribbon.

In the To Table dialog you should be able to leave the defaults. However if the API JSON data isn't very structured, in that each document has different elements and size, then you may need to experiment with the error handling options.

3. Expand the columns

The results will now be a typical referenced table which you need to expand the columns on. Click on the expand columns button.

Select the required columns. This will depend on your API data, in the demo data cases we want both columns as we will add additional API routes to get that data. 

My tip is to untick the "Use original column name as prefix" option. Or if you want to use that make sure you have renamed the column to a meaningful name prior to this step. You can also always add a meaningful name by editing the step settings afterwards, but before you proceed with other steps.

The results should now be expanded as required.

4. Expand the data with additional API calls per row

Now for the fun part. We now have rows of data which contain our ID(s). To expand this data we need to make additional API calls for each row. To achieve this we add a custom column, and dynamically build the API Uri.

Click the Add Column tab in the ribbon, and click Custom Column.

Give the column a meaning full name (e.g. CourseCollection).

Here is the tricky part, we have to dynamically build the API Uri. To do this we can use the Text.Combine() function to concatenate the uri and add the column value where the parameter should be. For our demo data the completed Uri will be http://localhost:3000/course/[CourseID]

Adjust the following expression as required:

= Json.Document(

  Web.Contents(

    Text.Combine({"http://localhost:3000/api/course/",[CourseID]})

  )

)

If you need to provide headers for authorization then use the parameters of the web.Contents() function, for example:

= Json.Document(

  Web.Contents(

    Text.Combine({"http://localhost:3000/api/course/",[CourseID]}), [Headers=[#"Z-API-Token"=""]])))

NOTE: If prompted regarding the data privacy, click continue and set as appropriate.

IMPORTANT: The process of updating the table may take some time depending on the number of rows and speed of the API.

The results should now include the custom column (e.g. CourseCollection). 

This table needs to be expanded, the same as earlier by clicking on the expand columns button and then select the desired columns to include. In my demo data case, I am only interested in the Title column.

Your table should now include the data from the additional API call.

Continue to model the data as you require, or additional API calls. In the case of the demo data we will repeat these steps but for the Person column and the API Uri route http://localhost:3000/person/[PersonID]

The completed table will look like:

The query steps will be similar to the following:

The complete M Query for creating the query will be similar to this. This is available from the Advanced Editor of a query, and is a working query for the demo API.

let

    Source = Json.Document(Web.Contents("http://localhost:3000/api/courseinstructor")),

    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"CourseID", "PersonID"}, {"CourseID", "PersonID"}),

    #"Added CourseCollection" = Table.AddColumn(#"Expanded Column1", "CourseCollection", each Json.Document(

  Web.Contents(

    Text.Combine({"http://localhost:3000/api/course/",[CourseID]})

  )

)),

    #"Expanded CourseCollection" = Table.ExpandRecordColumn(#"Added CourseCollection", "CourseCollection", {"Title"}, {"Title"}),

    #"Added PersonCollection" = Table.AddColumn(#"Expanded CourseCollection", "PersonCollection", each Json.Document(

  Web.Contents(

    Text.Combine({"http://localhost:3000/api/person/",[PersonID]})

  )

)),

    #"Expanded PersonCollection" = Table.ExpandRecordColumn(#"Added PersonCollection", "PersonCollection", {"LastName", "FirstName"}, {"LastName", "FirstName"})

in

    #"Expanded PersonCollection"

Demo Files and Source

I have provided a completed PBIX in the github repository. 

https://github.com/Matticusau/SQLDemos/tree/master/PowerBI_ApiDataSource

Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

New SQL Server Builds API.... improve your health checks with version and support information

Things have been a bit busy for me personally over the last few months but that is another story. What I have done is use some of this time to get back to a little project I started a while back.

Introducing the SQL Server Builds API..... providing version information including release dates, and known support life-cycle information.

Overview

The SQL Server Builds API provides SQL Server Version information and known support life-cycle information. As it is a personal project of mine the data is currently manually maintained based on known sources. The main objective of the tool is to enhance health check and project planning type processes, it is not intended for licensing or other legal auditing processes.

In prior roles and particularly in my current role one of the most common checks I perform on a SQL Server is the version and also the patching process. To aid in this what I would do is run reports based off my own custom database of version numbers to check and validate the version of the instance being checked.

Querying the API
To query the API it is pretty simple. The base URI is http://sqlserverbuildsapi.azurewebsites.net/api/SQLVersions

The API takes two parameters

• Version - This is a string representation of the version of your SQL Instance returned by SELECT SERVERPROPERTY('PRODUCTVERSION') e.g. '12.00.2342.00'
  
• MissingUpdates - A boolean value (True/False) of if to include any updates released since the current patch level in the results

So if I wanted to query for information about the version number 12.00.2342.00 my completed URI would be something like:

http://sqlserverbuildsapi.azurewebsites.net/api/SQLVersions?Version=12.00.2342.00&MissingUpdates=false

NOTE: The MissingUpdates param defaults to false so you could omit it from the URI above.

Where the data comes from

The official source of truth with life-cycle data has to be Microsoft Support Life-cycle (https://support.microsoft.com/lifecycle), and this API/tool is by no means trying to replace that. In fact if you are doing any official auditing for licensing or any other legal means you MUST use that source over this API's data. At least perform your own validation of the data for each record.

Most DBA's and SQL admins would refer to http://sqlserverbuilds.blogspot.com.au. I am no different and I would previously refer to that site as well. So, it is only natural that it is still one of the sources I go to for building my API's database.

The database behind this API is currently manually maintained, and even if it was somewhat automated mistakes can happen. If you find I am missing any updates, or if there is any incorrect data please make sure you reach out and let me know.

Suggestions for using the API

The API could be used in any number of tools and checks. Here are just some I can think of:

• Custom health check scripts
• Enhance on-premise CMDB data
• PowerBI reports
• Other custom Applications that interface/manage SQL Server instances

PowerShell Examples

#1 - Get version information (static version number)

# Set the version number to test
$VersionNumber = '12.00.2342.00' # SQL 2014

# Call the API to get the version information
$VersionData = Invoke-RestMethod -Uri "http://sqlserverbuildsapi.azurewebsites.net/api/SQLVersions?Version=$($VersionNumber)";

# at this point simply return the $VersionData variable for the raw info
$VersionData

# Want to improve your Health Check script, calculate the health of Support Status
if ($VersionData.ExtendedSupportEnd -le (Get-Date)) {$SupportStatus = 'Critical'}
elseif ($VersionData.MainstreamSupportEnd -le (Get-Date)) {$SupportStatus = 'Warning'}
else {$SupportStatus = 'Ok'}

# format the output data string
$OutputData = @"
Instance = $($Instance.Name)
Version = $($VersionData.Version)
Product = $($VersionData.Product)
Branch = $($VersionData.Branch)
Update = $($VersionData.Update)
MainstreamSupportEnd = $($VersionData.MainstreamSupportEnd)
ExtendedSupportEnd = $($VersionData.ExtendedSupportEnd)
SupportStatus = $($SupportStatus)
"@

# Return the hashtable
ConvertFrom-StringData -StringData $OutputData;

#2 - Get Missing updates (static version number)

# Set the version number to test
$VersionNumber = '11.00.2342.00' # SQL 2012

# Call the API to get the known missing updates (released since current version)
$VersionData = Invoke-RestMethod -Uri "http://sqlserverbuildsapi.azurewebsites.net:80/api/SQLVersions?Version=$($VersionNumber)&MissingUpdates=$true";

# return the output, it is already formated as an Array
$VersionData

# or as a table
$VersionData | Format-Table -Property Version,Branch,Update,@{Name="Released";Expression={Get-Date $_.Released -Format 'yyyy-MM-dd'}},Description -AutoSize

Other Examples
There are more examples in my GitHub repository - https://github.com/Matticusau/SQLServerBuildsAPI-Examples

Including:

• Simple static value examples like above
• Both Single and Multiple Instance examples with live version number data direct from a SQL Instance
• c# Windows Form client

Just want to see it in action, I have an MVC form available at https://sqlserverbuildsportal.azurewebsites.net

Where is the source code for the API

At this time I am not making the API opensource.

Want to partner with me

This is a personal project. If you have a similar project or a project that you would like to use this API in please reach out and lets improve the world together.

If you have any questions around this API please make sure you comment or reach out to me through any of my contact means.

Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.