Integrate Anaplan in Power BI

ninja14127 · July 2018

Hi community,

Have you guys have any experience integrating Anaplan data model into Power BI? If you do, please share. Our company is planning to integrate Anaplan data soon, and I am just researching for a proof of concept.

kjacokes · August 2018

Hi @ninja14127,

Great question!

We've had a customer integrate Anaplan with PowerBI.

There are a handful of options available to you, but as with most integrations, the recommended approach depends on your requirements (refresh rate/availability of data in PowerBI, automation requirements, etc.) and what, if any, ETL tools your organization owns: tools such as HyperConnect/Informatica Cloud, MuleSoft, and SnapLogic.

If there is any sensitivity around sharing this information on the forum, feel free to DM me and we can arrange to continue the conversation offline.

Cheers,

Kevin

jesse_wilson · August 2018

Hello @ninja14127,

Just following up on @kjacokes point, you also have the Azure Gateway (provided you're using the Azure service) to send RESTful requests, and leverage our API without using an ETL tool. This is a more complicated solution in terms of both development and maintenance, but saves you the investment in another tool. Of course, if you're already using a particular tool (Informatica, Mulesoft, Talend, etc) it's best to use that for you Anaplan integration as well. You can also email dataintegrations@anaplan.com with any questions.

Thanks!

JamesFleming · November 2018

Question: would Anaplan connect be able to integrate with Powerbi, with scheduled cadence refresh? And if so, is there any best practice out there that discusses high level how that works.

kjacokes · November 2018

Hi James,

Yes Anaplan Connect can integrate with Power BI. You can do a scheduled cadence refresh, or you could set it up such that you can initiate the data as a "push" from Anaplan, or as a "pull" from PowerBI. We've set it up both ways. The integration relies on Anaplan Connect for the first leg of the journey and the Data refresh functionality in Power BI for the second leg of the journey.

I would be happy to hop on the phone to talk you through how the integration might work, or answer any questions you or your IT group might have about setting up that integration.

Let me know if you have a few minutes to catch up next week and I can take you through it.

Best regards,

Kevin

jesse_wilson · November 2018

Hi @ninja14127,

As Kevin mentioned, you can use Anaplan Connect to extract from Anaplan and land a flat file somewhere, but not interface directly with Power BI or even trigger the refresh of the dataset. You could use another scripted solution to leverage the Power BI API for the latter half.

The most complete solution would be to use Azure to trigger the extract the data, then refresh the dataset.

Execute export action.
Download "file" from Anaplan.
Update Power BI dataset

Thanks,
Jesse

JamesFleming · December 2018

Hello Kevin,

That would be fantastic, please let me know the best way to get in touch. I have a collegue that I would like to invite as well.

Thanks and Regards

James

jesse_wilson · December 2018

Hi @JamesFleming,

Please feel free to email me to arrange some time to discuss this further. I'm based in Singapore (UTC+8), but am happy to move my schedule around to accomodate.

Best,
Jesse Wilson
Solution Architect - Data Integrations

Singapore

JamesFleming · December 2018

Thanks Jesse

Kevin - Question, Jesse mentions that there cannot be a direct connection between Anaplan Connect and Powerbi, and an additional tool such as Azure is required, unless loading a flat file. Is this the case, or can there be a direct setup between Powerbi and Anaplan connect to have a cadence refresh of the data within Power BI from Anaplan. This direct connection is highly desirable as a solution.

Thanks

James

JamesFleming · December 2018

Hello Kevin,

Would love to discuss direct connection to Powerbi via Anaplan connect. If you possibly have anytime today or next week would be greatly appreciated!!

Thanks and Regards

James

Kaviprakash · December 2019

Need some help to trigger data refresh in Power BI upon successful job completion in snaplogic.

JaredDolich · December 2019

Hi @ninja14127

I know you have a solution already but I thought I'd add a simple example to show you how easy it is to connect Anaplan to Power BI.

In this example, I'll use basic authentication, where you have to enter your ID and Pwd into Power BI. I'm also only showing a 1 chunk data set. If you have a file size greater than about 12 MB you'll need to do a multi-chunk download - more advanced but still completely doable in Power BI.

Now, the best method, of course, is along the lines of what @kjacokes was suggesting above. For me, I prefer to use Python, the Anaplan API and with a certificate which eliminates the problem of an ID and Pwd.

Anyway here's a simple example:

Step 1. Create your export in Anaplan. Whether you use a sophisticated method or a simple one, either way you have to create the actions in Anaplan first.

In this case save your export as a CSV format, save for everyone if you need this to exist longer than 48 hours, and give it a name you'll recognize. If you need a quick primer on data integration go to this link. It's AMAZING - I prefer to use the API for Power BI integration, so you might start there. They will also teach you how to use Postman, an open-source (free) API testing app. Remember to rerun the export every time you want to refresh the data. You can do this from within Anaplan, use the API, or one of the connectors @kjacokes recommended. (secret: get to know @kavinkumar - I learned just about everything I know about advanced data integration from him).

Here's our data:

Step 2: Open Power BI Desktop. Start by "get data" and select the "web" option in the "other" data source category.

Step 3: Enter the Anaplan API endpoints using this format:

https://api.anaplan.com/1/3/workspaces/{workspace ID}/models/{model ID}/files/{fileID}/chunks/0

To get these ID's you can use Postman to start. Anaplan connect and some of the other connectors will do the same but I find it so much easier to just run a python script which gives me everything I need including the ID's and names of the files.

Step 4: Enter your User ID and Password to connect to Anaplan

Use "Basic" authentication. Enter your User ID and Password.

Step 5: Power BI will pull the data and create a data source for you. And you're ready!

It took me 10 minutes to write this post, but about 2 minutes to get this data into Power BI.

It's super simple, just takes a little practice.

KBeltz · December 2019

Thank you, Kevin! I'm going to take a look into using Mulesoft.

ABerenguela · January 2020

Hello,

also you can run the Export action directly from Power BI, you need to do this to have updated data in the file that you are using:

in advance editor you can create the Rest API script

As Rest API you need to put the login in the header:

auth_key ="Certificate ..string or Basic ...string",

header= ["Authorization" = auth_key,

We already implemented this in a couple of customers and works fine.

Best Regards

alexpavel · January 2020

@ABerenguela really nice implementation... but it works like a charm 🙂

CommunityMember116919 · March 2020

Hi @JaredDolich ,

How did you managed tokenauth timing out issue. I can bring in the data to Power BI but I can't refresh it as the token is expiring after certain time.

Thanks.

JaredDolich · March 2020

@CommunityMember116919

Great question. I used V1.3 which does not require the token.

At first I encrypted my userID and password then changed to a certificate which is far more secure but a little harder to get to work.

Of course you should read @chase.hippen's article on python as a good starting point if you want to embed a python script.

The trick with PowerBI is to write an M script and build your own connector. Teklink by Mario Arias has an AWESOME video on this. You have to register your email with them but I promise you'll walk away with everything you need to hook up Anaplan to PowerBI. Get ready to take notes/screenshots though - they move really fast.

CommunityMember116919 · March 2020

@JaredDolich

I ended up using python as i found it much easier to work with.

But I also tried creating connectors through Teklink video and was able to create script to get the file list, and download the export reports but for some reason couldn't trigger the export action with M script, i had to do it from python.

It seems like M script doesn't have a specific command for post requests?

Here is the export query i had, just curious why it didn't work.

body = "{""localname""= ""en_US""}",
Data= Web.Contents("https://api.anaplan.com/1/3/workspaces/workid/models/modelid/exports/116000000002/tasks", [Content=Text.ToBinary(body), Headers=[#"Content-
Type"="application/json"]]),

Anyhow, thanks for the links, was very helpful.

JaredDolich · March 2020

@CommunityMember116919

I primarily use Python too. Much easier to use!

ABerenguela · April 2020

Hello,

try this format, it works for me:

url = "https://api.anaplan.com:443/1/3/workspaces/workspaces/workid/models/modelid/exports/116000000002/tasks",

header= [#"Authorization" = auth_key,

#"Content-Type" = "application/json"],

body1= "

{

""localeName"" : ""en_US""

}

",

data1 = Web.Contents(url, [Headers=header,Content = Text.ToBinary(body1)]),

Regards

CommunityMember116919 · April 2020

@ABerenguela

Port 443 did the trick, thanks for the insight.

shrey · June 2020

Hi Jared,

Great solution! Can this be done using authentication via certificate?

JaredDolich · June 2020

@shrey

Yes, but I recommend you watch this video from TekLink on YouTube. You'll need to register but it's free.

https://www.teklink.com/webinar/analytics-with-a-custom-built-connector/?passkey=afbf9a7cc95391253f867314ac86b7a5

It's a little more work than my example but TekLink's solution is much more scalable and reliable. Truly, they did a great job - presenter, Mario Arias, gives you everything you need to build a custom connector to PowerBI.

alexpavel · June 2020

@shrey take a look to this:

https://community.anaplan.com/t5/Best-Practices/How-to-Create-Connection-Between-Anaplan-and-Power-BI-with-API-2/ta-p/66364

It is exemplified how to use Certificate authentication in the attached file "API_Anaplan-PowerBI.txt" of the article.

shrey · June 2020

This is brilliant! Thank you for the response!

shrey · June 2020

This was great! I will look at the video!

jedwards · July 2020

Additional option to consider if you do not currently have an ETL/iPaaS tool and don't want to do the technical heavy-lifting:

https://www.onecloud.io/

OneCloud, modern iPaaS tool and the most straight-forward method I've seen to connect Anaplan and Power BI

anori · September 2020

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,

sandipsinha15 · October 2020

Can you please suggest how to automate this integration instead of downloading from Anaplan and upload in power BI and vice-versa?

Thanks in advance.

qsong1 · November 2020

Hi @JaredDolich

I used your method to connect with power BI, but i got the data with strange signs such as �, &,@ and all in one column. The data is in excel format in my export action. Do I miss anything?

Thank you

Best, Qianru

JaredDolich · November 2020

@qsong1

Excel is encoded with all kinds of special characters for Microsoft to interpret the file. Give CSV a try. Also, make sure your line items show up as columns without any other dimension nested. Rows should be your lists.

Lastly, if you have time, try using some of the longer term suggestions I provided you.

Integrate Anaplan in Power BI

Best Answer

Answers

Categories