Integrate Anaplan in Power BI

[ninja14127]

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.

[jesse_wilson]

Hi@JamesFleming,

Please feel free toemailme 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]

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]

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

[JaredDolich]

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@kjacokeswas 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 integrationgo 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@kjacokesrecommended. (secret: get to know@kavinkumar——我学会了一切我知道睡觉anced 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/{workspaceID}/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.

步骤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.

[ABerenguela]

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 = "证书。string or Basic ...string",

header= ["Authorization" = auth_key,

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

Best Regards

[alexpavel]

@ABerenguelareally nice implementation... but it works like a charm

[CommunityMember116919]

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]

@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 onpythonas 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 byMario Ariashas anAWESOME videoon 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]

@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]

@CommunityMember116919

I primarily use Python too. Much easier to use!