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.

image

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.

Comments (0)

Skip to main content