How to Create Connection Between Anaplan and Power BI with API 2.0

ABerenguela
edited January 2023 in Best Practices

Power BI is a popular reporting tool. One of the reasons is that in Power BI you can get data from different sources, and there is an increased interest of our customers and Anaplan builders to be able to import data from Anaplan. In order to make this connection, we developed a solution to set up the integration.

It is possible to have this integration between Anaplan and Power BI using Anaplan Rest APIs. Anaplan released two versions of Rest APIs — 1.3 and 2.0. For version 1.3, the authentication to Anaplan can be done using user and password (basic authentication).

For the Anaplan Rest API version 2.0, the authentication can be done only using a token authenticated, so if version 2.0 will be used, then a token string should be created and should be passed on the actions.  

Kindly follow the below process:

ABerenguela_0-1586265258047.png

In the next instructions, we will see how to create the token from a CA Certificate and authenticate into Anaplan in a single Power BI query. The whole code will be explained in this article.

  • Step 1: Let’s start with a Blank query as a source. Right-click over the new query and select Advanced Editor:

ABerenguela_1-1586265258058.png

  • In this first step, we need to create the token. For this, you will need to add your CA Certificate content, the encodedData, and encondedSignedData strings. Please have a look at the below link for more information: Authentication-Strings

Basic authentication can be used for this as well, however, please keep in mind the need to encode it first (password can be easily encrypted via the following link base64encode with the format email: password).

ABerenguela_2-1586265258062.png

  • Click on Done, so the string will be shown that needs to be authenticated :

ABerenguela_3-1586265258064.png

  • Step 2: Come back to the Advanced Editor and send all the previous code into parenthesis to the variable mytoken1. After that the code should be added to run the export action. Replace the workspace, model, and export with your IDs. Use Anaplan Connect or Postman to get them.

ABerenguela_4-1586265258068.png

  • Click on Done and the Export action will be launched. Later, we will come back to this query, as in the next step we will need to download the file.

ABerenguela_5-1586265258069.png

  • Step 3: Now we need the code to download the file. I recommend creating an API with version 1.3. It is only to collect the code because the number of columns and types are different in each file. Add a new Web source -> Advanced

ABerenguela_6-1586265258077.png

Fill with this information:

Add 3 headers:

  • Authorization Basic Your_encoded_password  
  • Content-Type application/csv; charset=UTF-8     --csv or txt depending of your file
  • Accept application/octet-stream
  • Click OK, and Power BI will download the file. The Query2 is now added:

ABerenguela_7-1586265258079.png

  • Go to Query2->Advanced Editor, copy the whole code:

ABerenguela_8-1586265258081.png

  • Close the editor and come back to the Query1->Advanced Editor.

Paste at the end of the code the Web.Contents, changing the version from 1/3 to 2/0 and the Authorization with the Token variable.

Paste the TransformColumnTypes without changes.

Finally, the response of the code will be #”Changed Type”. This is the content of your file:

ABerenguela_9-1586265258083.png

As you can see, I added the function InvokeAfter. This is to give time to Anaplan to finish the export, and then we can continue with the download. I set this up with 20 seconds. Adjust this delay time depending on the size of your file.

  • Click OK, and the query will launch the three steps and show the exported data.

ABerenguela_10-1586265258085.png

  • Finally, delete the Query2 and rename Query1 to a proper name.

A final recommendation: if you have a large file, it is better to split the Export over multiple actions because a large file can take a long time to be uploaded into Power BI, and use Chunks will create wrong rows in the concatenation of each one.

Comments

  • anori
    edited November 2022

    Hi,

    I did connect Power BI to Anaplan, it woks perfectly at the first time but I could not refresh data after modification either from Power BI Desktop or from the service. I am using the 2.0 API (token authentication) and tried also with 1.3 & basic authentication...

    Could you please give any advice or help ?

    BR,

  • alexpavel
    edited November 2022

    @anori  from your description of the issue, if it worked for the first time, it means that the token is not correctly refreshed or re-created. 

  • anori
    edited November 2022

    Hi Alex,

    Thank you for your reply.

    Yes that's what I think but how to refresh it or to force it to be re-created knowing that I have the right script to create it at the biginning?

    BR,

  • AlejandroGomez
    edited November 2022

    Yet another mazing contribution @ABerenguela!   Muchas gracias 🙂 

  • Lehtohen
    edited November 2022

    Excellent post!

    I've been doing some small demos about this and did not yet have CA Certificates available so I was just using the basic authentication. At first I was struggling a bit with creating the authentication token using basic authentication. Below is a script for how to achieve that in case someone is struggling with the same issue. The solution was to input blank string to Content (initially I just removed the whole line). 

    -------------------------------------------------------------------------------------------------------

    let
    //Step 1
    mytoken1=(let
    user_pw = "Use base64 encoder to encode username:password",
    url = "https://auth.anaplan.com/token/authenticate",
    header= [#"Authorization" = "Basic " & user_pw],
    webdata1 = Web.Contents(url, [Headers=header,Content = Text.ToBinary("")]),
    response1 = Json.Document(webdata1),
    tokenInfo = "AnaplanAuthToken " & response1[tokenInfo][tokenValue]
    in
    tokenInfo),
    #"auth_key2" = mytoken1,
    //Step 2

    ---------------------------------------------------------------------------