Hi Ravi,
As some further background, to replicate the video you need to be using PowerBI Desktop (PBI) and Planning Analytics Local (PA).
When I replicated the results in the video I had to disable SSL in the tm1s.cfg file and use TM1 as the authentication provider. You can get the process to work using SSL and with other authentication methods, but there is a lot more work.
Below is a basic PBI query that works against the pnlcube in the SData database that can be installed from the examples provided with PA.
Note you will need to create the two parameters (Username and Password) in the PBI query and update <tm1server> with your server name and maybe change 8010 to match your httpportnumber value from tm1s.cfg.
As other posts note, there are currently easier ways to do this such as staging the data in a relational database using the PA API, using a third party solution such as TM1PY or even using TurboIntegrator to push the data out in flat file or into a relational end point.
let
#"Header" = Binary.ToText(Text.ToBinary(Username&":" & Password)), /* This allows feeding through paramater values - need to then set PBI data connection to anonymous */
#"BaseURL" = "http://<tm1server>:8010/api/v1/Cubes('pnlcube')/Views('view1')/tm1.Execute?$expand=Axes($expand=Hierarchies($select=Name),Tuples($expand=Members($select=Name))),Cells",
content =
"{
}"
,
Source = Json.Document(
Web.Contents(
#"BaseURL",
[
Headers=[
#"Authorization" = "Basic " & #"Header",
#"content-type" = "application/json"
],
Content=Text.ToBinary(content)
]
)
)
in
Source
------------------------------
RodJ
------------------------------
Original Message:
Sent: Thu May 04, 2023 01:31 PM
From: Ravi Kotha
Subject: connection to Planning anlytics cubes from Power BI
Thanks Rod. I have looked into this video and tried the api format, but it didn't work for me.
------------------------------
Ravi Kotha
Original Message:
Sent: Thu May 04, 2023 02:39 AM
From: Rod Jager
Subject: connection to Planning anlytics cubes from Power BI
Hi Ravi,
+1 for George's suggestion as this is the easiest way.
If you are looking to use native PowerBI functionality then I can recommend this YouTube video, featuring Hubert Heijkers and Christoph Hein.
It shows how to get around some of the limitations in PowerBI in terms of the built-in Web connector:
https://www.youtube.com/watch?v=fJi-xvM--0U
This is the code referenced in the video:
https://blog.powp.co/my-power-query-web-contents-cheat-sheet-6a5bbfdce5eb
Hope this helps.
------------------------------
Rod Jager
Original Message:
Sent: Wed May 03, 2023 04:17 PM
From: Ravi Kotha
Subject: connection to Planning anlytics cubes from Power BI
Hello Friends,
We are trying to make a connection to planning analytics cubes from POWER BI. I didn't have much luck online. Probably someone has already tried this and could shed some light on how to do this. Appreciate anyone could help.
Thanks,
Ravi
------------------------------
Ravi Kotha
------------------------------