Using Planning Analytics (TM1) data in Power BI

This is a continuation of the last post on using Planning Analytics data in Qlik, but this time it’s using the same data source in Power BI.

As mentioned in the related article, there are a lot of ways you can do this including third party connectors or creating flat files from TM1, though Power BI can connect natively, and this example shows how to do that using a public cube view as the source.

Getting data from Planning Analytics into Power BI requires using a POST request instead of the GET type which is Power BI’s default. To get around this we need to delve into the advanced query editor so that’s where we will start.

Before you begin, there are a couple of caveats here in that this example has UseSSL set to F (due to self signed certificates and Power BI’s dislike of them) in the tm1s.cfg file and the TM1 database is using built in authentication for simplicity.

After opening the data editor in Power BI, follow these sites to link in the Planning Analytics data.

1. Add in two new text parameters
   - The first is called Username. Enter a default value which represents the user account
   - The second is called Password. Enter a default value which is the password for the user
   

2. Create the query
   - Click New Source > Blank query
   

3. Paste in the following sample code
   Note that the fields for <server>, <port>, <cube name> and <view name> need to be updated. After the data is imported there are a number of subsequent steps to convert the Odata object into a usable table.

   
   let

   #"Header" = Binary.ToText(Text.ToBinary(Username&":" & Password)),

   #"BaseURL" = "http://<server>:<http port>/api/v1/Cubes('<cube name>')/Views('<view name>')/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)

   ]

   )

   ),

   #"Converted to Table" = Record.ToTable(Source),

   #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each ([Name] = "Axes" or [Name] = "Cells")),

   #"Transposed Table" = Table.Transpose(#"Filtered Rows"),

   #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),

   #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Axes", type any}, {"Cells", type any}}),

   #"Expanded Axes" = Table.ExpandListColumn(#"Changed Type", "Axes"),

   #"Expanded Cells" = Table.ExpandListColumn(#"Expanded Axes", "Cells"),

   #"Expanded Cells1" = Table.ExpandRecordColumn(#"Expanded Cells", "Cells", {"Ordinal", "Value", "FormattedValue"}, {"Cells.Ordinal", "Cells.Value", "Cells.FormattedValue"}),

   #"Expanded Axes1" = Table.ExpandRecordColumn(#"Expanded Cells1", "Axes", {"Ordinal", "Cardinality", "Hierarchies", "Tuples"}, {"Axes.Ordinal", "Axes.Cardinality", "Axes.Hierarchies", "Axes.Tuples"}),

   #"Expanded Axes.Hierarchies" = Table.ExpandListColumn(#"Expanded Axes1", "Axes.Hierarchies"),

   #"Expanded Axes.Hierarchies1" = Table.ExpandRecordColumn(#"Expanded Axes.Hierarchies", "Axes.Hierarchies", {"@odata.etag", "Name"}, {"Axes.Hierarchies.@odata.etag", "Axes.Hierarchies.Name"}),

   #"Expanded Axes.Tuples" = Table.ExpandListColumn(#"Expanded Axes.Hierarchies1", "Axes.Tuples"),

   #"Expanded Axes.Tuples1" = Table.ExpandRecordColumn(#"Expanded Axes.Tuples", "Axes.Tuples", {"Ordinal", "Members"}, {"Axes.Tuples.Ordinal", "Axes.Tuples.Members"}),

   #"Expanded Axes.Tuples.Members" = Table.ExpandListColumn(#"Expanded Axes.Tuples1", "Axes.Tuples.Members"),

   #"PA Data" = Table.ExpandRecordColumn(#"Expanded Axes.Tuples.Members", "Axes.Tuples.Members", {"Name"}, {"Axes.Tuples.Members.Name"})

   in

   #"PA Data"

4. Verify that there are no syntax errors and then click the Done button to close the Advanced Editor window.
   See example screenshot below
   

5. (Optionally). Fields can be renamed or further filtering and transformation can be applied that are data set specific eg formatting values or excluding non leaf cells.
   

6. Click Close and Apply to update the Power BI model so that you can then start building visualisations.