0
5 February 2021 10:42:05 AM
2 min
In my previous blog articles, I have covered various use cases of Dynamic Arrays(DA) and some of the capabilities it offers. In case you missed it, I would strongly recommend you go through them (see links below) before proceeding with reading this blog. What are Dynamic Array formulas and why you should use them Data Analysis using Dynamic Array formulas In continuation of that, I will cover ...
In my previous blog articles, I have covered various use cases of Dynamic Arrays(DA) and some of the capabilities it offers.
In case you missed it, I would strongly recommend you go through them (see links below) before proceeding with reading this blog.
What are Dynamic Array formulas and why you should use them
Data Analysis using Dynamic Array formulas
In continuation of that, I will cover another use case where I would further highlight the power of DA when used with PAfE.
With that being said, ever since I learnt about Dynamic Arrays in Excel, I was very fascinated and always wondered whether there are any PAfE formulas where we could leverage the same automatic spilling behavior that applies to excel DA formulas.
I always found the implicit operator(@) that now gets automatically prefixed to all PAfE formulas - to restrict the formulas to spill the results where - practically useless as none of those formulas could potentially result in spilling even without @ operator.
This was until I discovered one formula that in fact did, to my surprise. The formula is called TM1ELLIST which returns an array of elements either based on subset or an array of elements (in my TM1ELLIST formula I have overridden subset list with element list created using SEQUENCE formula).
The DA now opens a whole lot of new possibilities for creating dynamic reports in Excel very easily. We all have gone through the agony of not being able to have a dynamic column in PAfE reports, haven't we? Well, not anymore! Just wrap the TM1ELLIST formula within TRANSPOSE formula, which is another cool DA formula, and it will transpose the results to columns.
Note: As per the IBM documentation it mentions the following:
"TM1ELLIST does not overwrite or insert into any non-formula space like a Dynamic Report. It is up the workbook designer to consume the plural value response correctly."
However, with DA, the formula does produce TM1RptRow function like behaviour in Dynamic Reports. And to top it up, it works totally independent of the TM1RptView function. As per the same documentation, in order to avail this formula to return an array, you need to declare this formula as a Named Range in Excel and then use the traditional CSE(Control Shift Enter) or using INDEX function approach. However, this again is no more required if you're on Office 365.
0
29 April 2026 9:30:00 AM
17 min
If you are comparing TM1 and Power BI, you are asking the wrong question.
These are not competing products. They solve fundamentally different problems. TM1 is a planning engine, and Power BI is a visualisation platform. Comparing them is like comparing an engine to a dashboard: one produces the power, and the other displays the information.
The real question is: when do you use each, and how do you connect them?
TM1 writes data. Power BI reads data.
That is the entire architectural divide in eight words.
IBM Planning Analytics (TM1) is a database where finance teams build budgets, run forecasts, and model scenarios. Users actively put data into TM1 (assumptions, targets, adjustments), and the engine calculates everything downstream in real time.
Microsoft Power BI is the opposite motion. Analysts pull data out of systems and present it through dashboards and reports. Users consume data in Power BI; they explore it, but they do not typically change it.
Once you understand this distinction, everything else falls into place.
|
Capability |
IBM TM1 (Planning Analytics) |
Microsoft Power BI |
|
Primary Function |
Planning, budgeting, forecasting, modelling |
Reporting, visualisation, dashboarding |
|
Data Direction |
Read + Write (bidirectional) |
Read-only (native) |
|
Engine Type |
In-memory OLAP database |
Analytical / visualisation engine |
|
Data Modelling |
Multidimensional cubes, rules, TI scripting |
Tabular models, DAX, Power Query |
|
Excel Integration |
Native bidirectional (PAfE) |
Export/embed only |
|
User Input |
Core feature (data entry, allocations, approvals) |
Not native (requires third-party add-ons) |
|
Scenario Modelling |
Sandboxes, versions, real-time what-if |
Limited without writeback tools |
|
Licensing |
Enterprise subscription |
Per-user subscription (Pro/Premium) |
|
Ease of Use |
Moderate (requires TM1 expertise) |
High (self-service, drag-and-drop) |
|
Best For |
Finance teams managing complex plans |
Organisation-wide data consumption |
TM1 is the right choice when your team needs to create, manage, and calculate financial data, not just look at it.
This is TM1's home turf. Multi-entity budgets, rolling forecasts, and driver-based planning models are its strengths. Finance users input their assumptions, and TM1 calculates the downstream impact across every cost centre, region, and business unit in real time.
We have seen teams try to build budgeting workflows inside Power BI using SharePoint lists and Power Automate. It works for about three months until someone needs a version comparison, a conditional allocation, or an approval workflow that does not break when someone edits the wrong row. That is when the call comes in.
Power BI has no native mechanism for users to input budget data, define calculation rules, or run scenario models. It was never designed to.
Multi-entity consolidation with intercompany eliminations, currency translation, and minority interest adjustments is core TM1 territory. If your organisation operates across legal entities (subsidiaries, joint ventures, regional holding companies), TM1 handles the consolidation hierarchy natively. Data aggregates from leaf-level entities upward through the legal structure automatically.
Distributing shared costs (IT overhead, corporate services, facility expenses) across departments based on calculated drivers is the kind of multi-step, cascading logic that TM1's rule engine was built for. Try replicating that in DAX and you will understand why TM1 developers exist.
TM1's sandbox feature lets analysts create personal "what-if" scenarios without touching the base data. Best case, worst case, and management case all run simultaneously with instant comparison. It is the feature that finance teams never know they need until they have it, and then they cannot live without it.
Power BI is the right choice when your organisation needs to see, explore, and share data across every department, not just finance.
This is where Power BI genuinely excels and TM1 does not pretend to compete. Power BI transforms raw data from ERP systems, CRM platforms, databases, and spreadsheets into interactive visual dashboards that look professional and update in real time. The drag-and-drop interface means non-technical users can build their own views. Furthermore, the Microsoft 365 integration (embedding dashboards in Teams, SharePoint, and email) is seamless in a way that no other BI tool has managed to match.
If your CEO needs a single screen showing revenue, pipeline, headcount, and customer satisfaction, Power BI is the tool.
Business users across every department (sales, marketing, operations, HR) can build their own reports in Power BI without waiting for an analyst to queue it up. The DAX formula language is surprisingly deep, and Power Query handles data transformation well enough that most business analysts can be self-sufficient within a few weeks.
TM1 can produce reports, but it is designed for the finance team. Power BI is designed for everyone else.
Power BI Pro starts at approximately $10/user/month. That makes it commercially viable to give hundreds or thousands of people access to live dashboards. TM1's licensing model is built for the smaller, specialised teams who actively build and manage planning models, typically 10 to 50 users. If you need 500 people looking at data, Power BI wins on cost alone.
Power BI natively connects to over 150 data sources. Pulling data from SAP, Salesforce, Oracle, SQL databases, and flat files into a single visual layer is one of its strongest capabilities. For organisations with fragmented data landscapes (which is most organisations), this alone justifies the investment.
In most mature enterprise environments, TM1 and Power BI are not competing. They are running in sequence:
Source Systems → TM1 (Planning + Modelling) → Power BI (Visualisation + Distribution)
TM1 handles the heavy lifting: complex financial calculations, budget collection, forecast modelling, and scenario analysis. It is the engine room where 15 finance professionals build and maintain the models that drive the business.
Power BI handles the presentation: transforming TM1's output into polished dashboards for the 200 executives, managers, and analysts who need to see the results but never need to touch the model.
This architecture gives you:
The modelling depth and writeback capability of TM1
The visual accessibility and distribution reach of Power BI
A single governed source of truth for all planning data
The question becomes: how do you actually connect them?
Connecting TM1 to Power BI has historically been harder than it should be.
TM1 stores data in multidimensional cubes, while Power BI expects flat, tabular data. The traditional approach (exporting CSVs from TM1, moving files to a staging folder, importing them into Power BI) is manual, fragile, and destroys the real-time value of both platforms. Version mismatches, stale data, and reconciliation headaches are common. We have seen finance teams burn entire afternoons on this every reporting cycle.
|
Method |
Pros |
Cons |
|
CSV/Excel Export |
Simple, no setup |
Manual, error-prone, no real-time |
|
TM1py (Python API) |
Flexible, customisable |
Requires developer skills, ongoing maintenance |
|
ODBC/SQL Staging |
Standardised, widely understood |
Adds complexity, latency, and another database to manage |
|
DataFusion Connector |
Real-time, low-code, no staging DB |
Purpose-built for TM1 |
Why We Built DataFusion
We built the DataFusion connector because we kept seeing the same pattern across client engagements: a finance team with a perfectly functioning TM1 model, and a leadership team that could not see any of it because the Power BI connection was held together with CSV exports and a prayer.
DataFusion connects directly to TM1 cubes via the REST API, extracts multidimensional data, and serves it to Power BI in real time. There is no intermediate database, no custom ETL scripts, and no TM1 development skills required.
How it works:
DataFusion connects to your TM1 server via the REST API.
You select the cube and the data you need through a visual interface.
DataFusion extracts and flattens the data for Power BI consumption.
Power BI connects to DataFusion as a standard data source.
Dashboards update automatically as TM1 data changes.
The result: your finance team works in TM1, and your executives see the results in Power BI. No one exports a CSV ever again.
|
Your Priority |
Use This |
|
Building budgets and forecasts |
TM1 |
|
Visualising financial results |
Power BI |
|
Users inputting data |
TM1 |
|
Organisation-wide reporting |
Power BI |
|
Complex allocation models |
TM1 |
|
Combining data from 10+ sources |
Power BI |
|
Scenario modelling and what-if |
TM1 |
|
Self-service analytics |
Power BI |
|
Both planning AND visualisation |
TM1 + Power BI + DataFusion |
If your organisation runs TM1 and Power BI (or is considering both), we can show you how DataFusion bridges them in real time. No staging databases, no CSV exports, and no custom code.
Start your 60-day free trial now!
Octane Software Solutions is an IBM Finance & AI Partner with 90,000+ hours of TM1 experience across 100+ enterprise projects.
.png)
print
Leave a comment