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.02 - April 2021 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.
- Download the MicatioPowerBITM1Connector.mez file and place within the Documents folder under the existing Power BI Desktop\Custom Connectors folder.
2. Within Power BI Desktop, select "Get Data" and select "Other". Pick the connector named "Micatio TM1 Connector (Beta)"
3. Accept the Warning regarding Third Party Data/Connectors.
4. Power BI will now present the TM1 Data Connection 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. TM1 URL Location
Either Cloud for IBM Cloud or OnPremise for local TM1 Instances.
3. Source Type currently supports either a TM1 View or full MDX statement
4. 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.
5. 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])
6. Click the "OK" button when all inputs have been entered.
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.