Freigeben über

Partner Center API and Power BI

  • Artikel

Power BI provides a feature rich toolset that makes it possible to construct meaningful reports that can provide actionable data. With Power BI you can import data through several different mechanisms include HTTP request. From an advanced query you can utilize the Web.Contents function to obtain the contents downloaded from a web address. Having this capability means that a query can be constructed that returns information from the Partner Center API. With this in mind I would like to walk you through how to utilize the Partner Center API as a data source for a Power BI report.

Let’s walkthrough how to use the Partner Center API as a data source for a report. In order to do this, you will need Power BI Desktop installed

  1. Open Power BI Desktop, and close the getting started wizard if it loads clip_image002

  2. Click Edit Queries in the toolbar

    clip_image004

  3. Add a black query as a new source by clicking on New Source in the toolbar and then selecting Blank Query clip_image005

  4. Right click on Query1 and then click on Advanced Editor to open the advanced query editor

    clip_image006

  5. Enter the following for the query and then click Done

    let
    Source = (AccountId as text, ApplicationId as text, ApplicationSecret as text, Password as text, Username as text) => let
    aadRequest = [
    client_id = ApplicationId,
    client_secret = ApplicationSecret,
    grant_type = "password",
    password = Password,
    resource = "https://api.partnercenter.microsoft.com",
    scope = "openid",
    username = Username
    ],
    aadContent = Text.ToBinary(Uri.BuildQueryString(aadRequest)),
    aadTokenRequest = Web.Contents("https://login.microsoftonline.com/" & AccountId & "/oauth2/token",
    [
    Headers = [#"content-type"="application/x-www-form-urlencoded"],
    Content = aadContent
    ]
    ),
    aadTokenJSON = Json.Document(aadTokenRequest),
    aadToken = aadTokenJSON[access_token]
    in
    aadToken
    in
    Sourceclip_image008

    This query will create a function that will be used to obtain an Azure AD token, which will be used to obtain a Partner Center token. It obtains an Azure AD token by making a HTTP POST against https://login.microsoftonline.com using App + User authentication. It is important to note for this sample we will be querying Azure usage records so App + User authentication is required.

  6. Rename the query to GetAzureADToken

  7. Create another blank query, rename it to GetPartnerCenterToken, and then specify the following query using the advanced query editor

    let
    Source = (AccountId as text, ApplicationId as text, ApplicationSecret as text, Password as text, Username as text) => let
    aadToken = GetAzureADToken(AccountId, ApplicationId, ApplicationSecret, Password, Username),
    pcTokenRequest = Web.Contents("https://api.partnercenter.microsoft.com/GenerateToken",
    [
    Headers = [
    #"Authorization"="Bearer " & aadToken,
    #"content-type"="application/x-www-form-urlencoded"
    ],
    Content = Text.ToBinary("grant_type=jwt_token&client_id=" & ApplicationId)
    ]
    ),
    pcTokenJSON = Json.Document(pcTokenRequest),
    pcToken = pcTokenJSON[access_token]
    in
    pcToken
    in
    Source

    clip_image010

    This query will obtain an Azure AD token using the GetAzureADToken function create in step five to obtain a Partner Center token. Once you have this token you can utilize the Partner Center API.

  8. Add another blank query, rename it to GetCustomers, and then specify the following query using the advanced query editor
    let
    Source = (PartnerCenterToken as text) => let
    customersRequest = Web.Contents("https://api.partnercenter.microsoft.com/v1/customers",
    [
    Headers = [
    #"Authorization"="Bearer " & PartnerCenterToken,
    #"Accept"="application/json"
    ]
    ]
    ),
    customers = Json.Document(customersRequest),
    items = customers[items],
    customersTable = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    expendedRecord = Table.ExpandRecordColumn(customersTable, "Column1", {"id", "companyProfile", "relationshipToPartner"}, {"id", "companyProfile", "relationshipToPartner"}),
    expandedCompanyProfile = Table.ExpandRecordColumn(expendedRecord, "companyProfile", {"tenantId", "domain", "companyName"}, {"tenantId", "domain", "companyName"})
    in
    expandedCompanyProfile
    in
    Source clip_image012

    This will create a function that can be used to obtain a list of customers using the Partner Center API.

  9. Add another blank query, rename it to GetSubscriptions, and then specify the following query using the advanced query editor

    let
    Source = (CustomerId as text, PartnerCenterToken as text) => let
    subscriptionsRequest = Web.Contents("https://api.partnercenter.microsoft.com/v1/customers/" & CustomerId & "/subscriptions",
    [
    Headers = [
    #"Authorization"="Bearer " & PartnerCenterToken,
    #"Accept"="application/json"
    ]
    ]
    ),
    subscriptionsJSON = Json.Document(subscriptionsRequest),
    items = subscriptionsJSON[items],
    subscriptions = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    subscriptionsExpanded = Table.ExpandRecordColumn(subscriptions, "Column1", {"id", "offerId", "offerName", "friendlyName", "quantity", "unitType", "creationDate", "effectiveStartDate", "commitmentEndDate", "status", "autoRenewEnabled", "billingType", "suspensionReasons", "contractType", "orderId"}, {"subscriptionId", "offerId", "offerName", "friendlyName", "quantity", "unitType", "creationDate", "effectiveStartDate", "commitmentEndDate", "status", "autoRenewEnabled", "billingType", "suspensionReasons", "contractType", "orderId"}),
    subscriptionsFiltered = Table.RemoveRowsWithErrors(subscriptionsExpanded, "{Column1"),
    subscriptionsRemoved  = Table.SelectRows(subscriptionsExpanded, each [subscriptionId] <> null),
    subscriptionsAppended = Table.AddColumn(subscriptionsRemoved, "tenantId", each CustomerId)
    in
    subscriptionsAppended
    in
    Source
    clip_image014
    This will create a function that will obtain a list of all subscriptions that belong to the specified customer using the Partner Center API.

  10. Create another blank query, rename it to GetAllSubscriptions, and then specify the following query using the advanced query editor

    let
    Source = (PartnerCenterToken as text) => let
    customers = GetCustomers(PartnerCenterToken),
    Customer.Subscriptions = (customerId) => let
    subscriptions = GetSubscriptions(customerId, PartnerCenterToken)
    in
    subscriptions,
    customersSubscriptions = Table.AddColumn(customers, "Custom", each Customer.Subscriptions([tenantId])),
    removeNoSubErrors = Table.RemoveRowsWithErrors(customersSubscriptions, {"Custom"}),
    customExpand = Table.ExpandListColumn(removeNoSubErrors, "Custom"),
    expanded = Table.ExpandRecordColumn(customExpand, "Custom", {"subscriptionId", "offerId", "offerName", "friendlyName", "quantity", "unitType", "creationDate", "effectiveStartDate", "commitmentEndDate", "status", "autoRenewEnabled", "billingType", "suspensionReasons", "contractType", "orderId"}, {"subscriptionId", "offerId", "offerName", "friendlyName", "quantity", "unitType", "creationDate", "effectiveStartDate", "commitmentEndDate", "status", "autoRenewEnabled", "billingType", "suspensionReasons", "contractType", "orderId"}),
    removeIdColumn = Table.RemoveColumns(expanded, {"id"})
    in
    removeIdColumn
    in
    Source
    clip_image016This will create a function that calls the GetCustomers and GetSubscription functions to get a complete list of subscriptions for all customers.

  11. Create another blank query, rename it to GetSubscriptionMonthlyUsage , and then specify the following query using the advanced query editor

    let
    Source = (CustomerId as text, SubscriptionId as text, PartnerCenterToken as text) => let
    Subscription.Usage = (tenantId, subscriptionId) => let
    usageRequest = Web.Contents("https://api.partnercenter.microsoft.com/v1/customers/" & tenantId & "/subscriptions/" & subscriptionId & "/usagerecords/resources",
    [
    Headers = [
    #"Authorization"="Bearer " & PartnerCenterToken,
    #"Accept"="application/json"
    ]
    ]
    ),
    usageJSON = Json.Document(usageRequest),
    items = usageJSON[items],
    usage = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
    in
    usage,
    records = Subscription.Usage(CustomerId, SubscriptionId),
    usage = Table.ExpandRecordColumn(records, "Column1", {"category", "subcategory", "quantityUsed", "unit", "resourceId", "id", "resourceName", "name", "totalCost", "currencyLocale", "lastModifiedDate", "attributes"}, {"category", "subcategory", "quantityUsed", "unit", "resourceId", "id", "resourceName", "name", "totalCost", "currencyLocale", "lastModifiedDate", "attributes"}),
    transform = Table.TransformColumnTypes(usage,{{"quantityUsed", type number}, {"totalCost", Currency.Type}})
    in
    transform
    in
    Source

    clip_image018

    This will create a function that will utilize the Partner Center API in order to obtain monthly Azure usage records for the specified subscription

  12. Create another blank query, rename it to GetMonthlyUsage, and then specify the following query using the advanced query editor

    let
    Source = (PartnerCenterToken as text) => let
    subscriptions = GetAllSubscriptions(PartnerCenterToken),
    azureSubscriptions = Table.SelectRows(subscriptions, each ([billingType] = "usage") and ([status] = "active")),
    narrowed = Table.SelectColumns(azureSubscriptions, {"subscriptionId","tenantId"}),
    usage= Table.AddColumn(narrowed, "Custom", each GetSubscriptionMonthlyUsage([tenantId], [subscriptionId], PartnerCenterToken)),
    usageExpanded = Table.ExpandTableColumn(usage, "Custom", {"category", "subcategory", "quantityUsed", "unit", "resourceId", "id", "resourceName", "name", "totalCost", "currencyLocale", "lastModifiedDate", "attributes"}, {"category", "subcategory", "quantityUsed", "unit", "resourceId", "id", "resourceName", "name", "totalCost", "currencyLocale", "lastModifiedDate", "attributes"}),
    transform = Table.TransformColumnTypes(usageExpanded, {{"quantityUsed", type number}, {"totalCost", Currency.Type}})
    in
    transform
    in
    Source
    clip_image020

    This will create a function that use the GetAllSubscriptions and GetSubscriptionMonthlyUsage functions to obtain Azure usage records for all active Azure subscription owned by the partner’s customers.

Now that all of the required functions have been created we can create three queries that will provide data to construct a report. In order to create the necessary queries, perform the following

    1. Create a blank query, rename it to Customers, and then specify the following query using the advanced query editor

      let
      pcToken = GetPartnerCenterToken("{INSERT-ACCOUNT-IDENTIFIER-HERE }", "{INSERT-APPLICATION-ID-HERE}", "{INSERT-PASSWORD-HERE}", "{INSERT-USERNAME-HERE}"),
      customers = GetCustomers(pcToken),
      #"Renamed Columns" = Table.RenameColumns(customers,{{"companyName", "Company Name"}})
      in
      #"Renamed Columns"
      clip_image022

      This query will return a list of customer associated with the specified CSP reseller. It is recommended that credentials not be specified in the query itself but rather leverage parameters. That was done here for simplicity, however, if you would like to do it then the only change you need to make after creating the parameters would be the second line. It would be modified to something similar to the following

      pcToken = GetPartnerCenterToken(#"AccountId", #"ApplicationId", #"ApplicationSecret", #"Password", #"Username"),

    2. Once you click done the query will execute and you should see a message stating The user was not authorized. This is excepted and all you need to do is click Edit Credentials -> Connect clip_image024  clip_image026

    3. Add another blank query, rename it to Subscriptions, and then specify the following query using the advanced query editor

let
pcToken = GetPartnerCenterToken("{INSERT-ACCOUNT-IDENTIFIER-HERE }", "{INSERT-APPLICATION-ID-HERE}", "{INSERT-PASSWORD-HERE}", "{INSERT-USERNAME-HERE}"),
subscriptions = GetAllSubscriptions(pcToken)
in
subscriptions
clip_image028

    1. Add another blank query, rename it to MonthlyUsage and then specify the following query using the advanced query editor

let
pcToken = GetPartnerCenterToken("{INSERT-ACCOUNT-IDENTIFIER-HERE }", "{INSERT-APPLICATION-ID-HERE}", "{INSERT-PASSWORD-HERE}", "{INSERT-USERNAME-HERE}"),
usage = GetMonthlyUsage(pcToken)
in
usage
clip_image030

  1. Click Close & Apply in the toolbar so you can start building a report

  2. Start building reports that you need

    clip_image032

Hopefully through all of this you learned how you can leverage the Partner Center API as a data source for Power BI reports. You can download a partially completed report from here, the queries to obtain the customers, subscriptions, and usage will need to be added by you.

Comments

  • Anonymous
    November 16, 2016
    Hi Isaiah, I am attempting the above walk through. The Customers query makes a call to GetPartnerCenterToken with 4 parameters. However GetPartnerCenterToken has been setup with 5 parameters. The call appears to be missing the parameter "ApplicationSecret". Can you clarify this please?
  • Anonymous
    February 24, 2017
    Hi, I tried your code and it is very helpfull. The only problem I'm facing is that I can't refresh my data. It only connects one time, after that I get an error 400: bad request. DataSourcePath=https://login.microsoftonline.com/(myid)/oauth2/token. Any help is welcome.
  • Anonymous
    October 31, 2017
    I'm having an issue with the "MonthlyUsage" and "Subscription" queries. They just return "Access to the resource is forbidden" with an "Edit Credentials" button. I've tried all of the credential means I can think of, nothing works. Customers query works fine.
    • Anonymous
      November 27, 2017
      Hi Brian, The credentials you are using must have AdminAgent privileges. Have you confirmed that the account is configured accordingly?