This article shows you how to request analyses from Mapp Intelligence via Analytics API and import them into an Excel file. Please be sure to consult our technical documentation here for more information on the API.

Prerequisites

  • Mapp Intelligence Analytics API has to be activated
  • Access has to be enabled. Learn more about it on this page
  • Power BI Desktop or Excel needs to be installed

Get your API JSON

  1. Go to Mapp Intelligence and open the desired analysis.
  2. Copy JSON Configuration via context menu.

  3. Paste the request into any editor.

  4. Replace all " with "".

    Please be sure that you have the correct upper and lower limits set. When you copy the JSON from Mapp Intelligence, the upper limit will stay the same as it is onsite. If you would like to increase it, you can either change it before you copy the JSON or after in the JSON itself. Mapp can produce results with 5 million rows, but limits may apply depending on your consuming system (e.g. excel).

Automated Import

  1. In Excel, open your file.

    For an example, please see this file: JSON_API_powerBI_demo_version1_5.xlsx

    Please note, this file is only an example and does not cover all potential scenarios and uses. 

  2. Select Power Query editor.

  3. In getToken replace <> placeholders with values
    • clientiD="<clientiD>",
    • clientSecret="<clientSecret>",

  4. In getAnalysisData

    1. Paste your JSON into queryAsJson = "<yourJSON>".

    2. Please be sure that you check and update noOfDimensionColumns to make sure it matches the number of dimension columns you will get from your results.

    3. Execute


If you receive an error that Authentication is not specified, go to data source settings and configure the URLs shown to use anonymous. Since PowerBi does not allow data sources to be called from a URL that was transmitted by default, you have to activate ignore privacy settings.

  1. getData > Query Options > Privacy (global and current workbook) > Select Ignore Privacy Levels…
  2. Alternatively:
    1. Change the script to use fixed status and result URLs.
    2. Add the correlationID to the statusURL and the calculationID to the resultURL which you received in the response. 

Manual Data Transformation

First, you will need to complete your authentication, send a JSON query, and receive your results. for example:

Then you can follow these steps to transform your results into a table. 

  1. How to set up headers:

    1. Right-click List in the header row and set it as a new query.
    2. Convert to a table.
    3. Click the expand icon.
    4. Uncheck keep original names as a prefix.
    5. Delete columns that should not be used as a header.
    6. Transpose table.
    7. Select the API connection that you configured in the Credential Store from the drop-down list under Credential Store Record.
    8. Select the GET Method and UTF-8 encoding.

  2. How to set up rows

    1. Right-click List in the row and set it as a new query.
    2. Convert to a table.

    3. Add a custom column and replace the given formula with:

      List.First([Column1])
      CODE

    4. Add another custom column, replacing the formula with:

      List.First(List.Skip([Column1],1))
      CODE
    5. Continue for every available column in your results, increasing the column number accordingly.
    6. Remove the first column with the values "List".
    7. Define value replacement null to 0 as needed.
    8. Check your results.
  3. Click close and load.
  4. Copy your header and row query into a single sheet.
  5. Now you should be able to view your table with the data.



Please note that onsite, if there are no values returned, you will see a 0. However, When using an API this will return as null. 

Technical Documentation

Excel specifications and limits