Power BI TM1 Custom Connector

Saturday, August 01, 2020

INTRODUCTION

There is no built-in TM1 connector for Power BI when you first install Power BI Desktop.  TM1 REST API is based on OData V4 protocol which Power BI does support so it should be possible to use the OData Connector which is shipped with Microsoft Power BI. 

It would seem the TM1 output doesnt conform correctly to what Power BI is expecting so the response is unsupported and so the OData Driver shipped with Power BI.

Micatio have developed a TM1 Connector which supports TM1 Views and raw MDX statements to bridge the gap.

DOWNLOAD

Latest Version 1.00 - Aug 2020 Beta Release
New Features: None 
click here to download the TM1 Connecter  

INSTALLATION AND USAGE 

Micatio TM1 Connector is a custom connector which must be placed in the local Custom Connectors folder on your local PC.

  1. Download the MicatioPowerBITM1Connector.mez file and place within the Documents folder under the existing Power BI Desktop\Custom Connectors folder. 

Custom Connector Folder

 

     2. Within Power BI Desktop, select "Get Data"  and select "Other".  Pick the connector named "Micatio TM1 Connector (Beta)"

Get Data Picker

3. Accept the Warning regarding Third Party Data/Connectors.

 

4. Power BI will now present the TM1 Data Connection details

 

TM1 Data Source Details

1. TM1 URL API, for example https://yourcloud.planning-analytics.ibmcloud.com/tm1/

   You can obtain the hostname from your IBM Planning Analytics Welcome Kit, typically this involves updating "yourcloud"    

2. Source Type currently supports either a TM1 View or full MDX statement

3. Source Location specifies how the connector will receive the View/MDX statement, this can be Text, File or URL. Use Text for most cases and File for complex long MDX statements. 

4. Source Location Value contains either the File Name (C:\Files\View.txt), URL (https://intranet/content/View.txt) or just plain text, this is based on the previous Source Location setting. 

    Source Type is View 

       Text Box should contain the Cube name and View which is delimited by a pipe (|), for example to reference the Sales cube and Order Summary view the text box would contain  

                Sales|Order Summary    

    Source Type is MDX 

       Text Box should contain a full valid MDX statement, for example: 

                  SELECT  ( { EXCEPT({ HEAD([Customers].Members,500)},{[Customers].[DummyCustomer]})}*{[CustomerStatus].[Active]}*{[Period].[Jul]}) on COLUMNS,

                             ({[Order].[Value]}) on ROWS

                               FROM [Sales]

                               WHERE ([Year].[FY2020])

5. Click the "OK" button when all inputs have been entered. 

 

Completed Source Details

6. The final step is to enter the TM1 Non-Interactive Username and Password. 

 

The account details should be visible from your IBM Welcome pack under the heading "Non-interactive Account"

7. Power BI will then attempt to retrieve and download the data from TM1, select either "Load" or "Transform" to apply modifications.