Data Connector SDK and Partner Center
The Data Connector SDK enables you to create custom connector for Power BI and Power Query. These connectors have the ability to bind to applications, data sources, and services. All of this enables you to provide rich business intelligence and robust analytics over multiple data sources. Through this SDK you can develop a connector that binds to a Rest API in order to provide a business analysis a friendly view.
There are some great examples of what can be accomplished using this SDK on GitHub. In the past I have written about using the Partner Center API and Power BI, however, that approach has some drawbacks. In order to address those drawbacks I put together a custom data connector that binds to the Partner Center API. The documentation and source for the data connector is available at Partner-Center-Query. Please be aware this data connector makes live calls against the Partner Center API. If you have a significant need for reporting then I would recommend you utilize Partner Center Analytics app for Power BI (direct partners in CSP) or Partner-Center-Reporting.
Walkthrough
To highlight some of the enhancements and security improvements I wanted to walkthrough a few queries.
Token Management
When requesting information through the Partner Center API, you must specify the appropriate credentials. In order to keep these credential secure they should not be stored in clear text. This is accomplished using the OAuth authentication mechanism provided by the Data Connector SDK. The following code shows how OAuth authentication mechanism is setup.
PartnerCenter = [
Authentication = [
OAuth = [
FinishLogin = FinishLogin,
Logout = Logout,
Refresh = Refresh,
StartLogin = StartLogin
]
],
Label = Extension.LoadString("DataSourceLabel")
];
The following code shows how the necessary access token is obtained.
// OAuth Implementation
FinishLogin = (context, callbackUri, state) =>
let
parts = Uri.Parts(callbackUri)[Query],
result = if (Record.HasFields(parts, {"error", "error_description"})) then
error Error.Record(parts[error], parts[error_description], parts)
else
TokenMethod("authorization_code", "code", parts[code])
in
result;
Logout = (token) => logout_uri;
Refresh = (resourceUrl, refresh_token) => TokenMethod("refresh_token", "refresh_token", refresh_token);
StartLogin = (resourceUrl, state, display) =>
let
authorizeUrl = authorize_uri & "?" & Uri.BuildQueryString([
client_id = client_id,
redirect_uri = redirect_uri,
resource = "https://api.partnercenter.microsoft.com",
state = state,
response_type = "code",
response_mode = "query",
login = "login"
])
in
[
LoginUri = authorizeUrl,
CallbackUri = redirect_uri,
WindowHeight = 720,
WindowWidth = 1024,
Context = null
];
TokenMethod = (grantType, tokenField, code) =>
let
queryString = [
client_id = client_id,
grant_type = grantType,
redirect_uri = redirect_uri
],
queryWithCode = Record.AddField(queryString, tokenField, code),
tokenResponse = Web.Contents(token_uri, [
Content = Text.ToBinary(Uri.BuildQueryString(queryWithCode)),
Headers = [
#"Content-type" = "application/x-www-form-urlencoded",
#"Accept" = "application/json"
],
ManualStatusHandling = {400}
]),
body = Json.Document(tokenResponse),
result = if (Record.HasFields(body, {"error", "error_description"})) then
error Error.Record(body[error], body[error_description], body)
else
body
in
result;
Using this approach the user requesting information from the API will be prompted for credentials. Those credentials will be cached using the secure mechanism built into Power BI. No secure information will be stored in clear text. Also, it worth mentioning that this flow utilizes app + user authentication because various API calls only support the app + user flow.
Paging
Various methods within the Partner Center API will return results using paging. In other words the number of records is limited for a single request to the API. An example of this would be querying for Azure utilization records. The API will only return a maximum of 1,000 records at once, so you need to make additional requests in order to obtain the complete set of records. This data connector accomplishes this by using the following code when performing requests against the Partner Center API.
PC.ContinuationRequest = (relativePath as text, continuationToken as text) =>
let
source = Json.Document(Web.Contents("https://api.partnercenter.microsoft.com/v1/",
[
Headers = [
#"Accept" = "application/json",
#"MS-ContinuationToken" = continuationToken,
#"MS-PartnerCenter-Application" = application_name
],
RelativePath = relativePath
]
))
in
source;
PC.GetPagedResources = (relativePath as text) =>
let
GetResources =
(relativePath as text) as record =>
let
data = PC.Request(relativePath),
items = try data[items] otherwise null,
next = try data[links][next] otherwise null,
res = [Items = items, Next = next]
in
res,
GetResourcesContinuation =
(nextData) as record =>
let
relativePath = nextData[uri],
data = PC.ContinuationRequest(relativePath, nextData[headers]{0}[value]),
items = try data[items] otherwise null,
next = try data[links][next] otherwise null,
res = [Items = items, Next = next]
in
res,
resourcesList =
List.Generate(
() => [result = GetResources(relativePath)],
each [result][Items] <> null,
each [result = GetResourcesContinuation([result][Next])],
each [result][Items])
in
resourcesList;
PC.Request = (relativePath as text) =>
let
source = Json.Document(Web.Contents("https://api.partnercenter.microsoft.com/v1/",
[
Headers = [
#"Accept" = "application/json",
#"MS-PartnerCenter-Application" = application_name
],
ManualStatusHandling = {400},
RelativePath = relativePath
]
))
in
source;
The above code will continue to request data from the API until the value for Next is null. This ensures all pages of information are returned. The following provides a small example of how this logic can be used to obtain a complete list of Customers.
PC.Customers = () =>
let
customersList = PC.GetPagedResources("customers"),
customersListTable = Table.FromList(customersList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
customersRecordTable = Table.ExpandListColumn(customersListTable, "Column1"),
customersTable = Table.ExpandRecordColumn(customersRecordTable, "Column1", {"companyProfile", "relationshipToPartner"}, {"companyProfile", "relationshipToPartner"}),
customersInfoTable = Table.ExpandRecordColumn(customersTable, "companyProfile", {"tenantId", "domain", "companyName"}, {"tenantId", "domain", "companyName"})
in
customersInfoTable;
Outcome
When everything is put together you can gain valuable insights into your Cloud Solution Provider business. The following figure show an example of a report that you could build.
With this particular report you can quickly see over the past 90 days only four Azure subscriptions have been active. Considering there are more than four Azure subscriptions, this should be something that is investigated. There could be a training issue, and when it is corrected it should result increase usage and profits.