5
28 August 2019 2:37:52 PM
4 min
This article shows how to drill down to the next level in a visualization within Microsoft Power BI, when a filter is applied using a Slicer. This could be applied even if multiple selections are made within the slicer. For example – We have a car with 3 model series (L Series, S Series, T Series) in the slicer. Business requires to show the values at this level when no selection is made. Once a ...
.webp?width=160&height=85&name=powerbi-1%20(1).webp)
This could be applied even if multiple selections are made within the slicer.
For example – We have a car with 3 model series (L Series, S Series, T Series) in the slicer. Business requires to show the values at this level when no selection is made. Once a selection is made, the visual should show the next level in the hierarchy.
Visualisation without any filter selected (Shows the top level):
Visualisation with single filter selected (Shows the next level for S Series):
Visualisation with multiple filters selected (Shows the next level for S Series & T Series):
In order to achieve the above in Power BI, follow the below steps,
Step 1: We have a ‘Model’ hierarchy setup in Power BI. The hierarchy was imported in Power BI using Octane connector from TM1. You could use any other hierarchy setup.
Lowest level is ‘model’ column with unique model names.
Step 2: Create another table ‘Model Levels’ (used in this example) with 3columns from the above hierarchy in Step 1; 1st column being the Parent 2 & Parent 1; 2nd Column if Parent 2 then value is ‘L2’ else ‘L1’; and 3rd column being the lowest level model names.
Step 3: Create a relationship between ‘Model’ & ‘Model Levels’ table with the lowest level model names. Select ‘Both’ in Cross Filter direction while creating the relationship.
Step 4: Create a measure ‘Sales_By_Model’ which calculates the actual value depending on the filter selections. If its selected, It drills to the next level, else, shows the top level.
Step 5: Create a slicer using Parent 2 from the original ‘Model’ hierarchy table.
Step 6: Create a visualisation using the Model Name (Parent 2 or Parent 1) from ‘Model Levels’ table created in Step 2 and for values, use the measure created in Step 4.
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.
0
17 October 2023 8:30:00 AM
3 min
In the ever-evolving world of data visualization and reporting, Power BI continues to shine as a leading tool, constantly enhancing its capabilities. The August update brings a slew of exciting features, focusing on making your reporting experience smoother, more precise, and more efficient than ever before. Let's dive into the game-changing updates:
.png?width=517&height=291&name=PowerBi%20(2).png)
Tired of the hassle of switching between web and mobile layouts while developing your reports? Say hello to the new layout switcher! Power BI has introduced intuitive buttons, thoughtfully positioned at the bottom of your screen, right alongside the page navigator. This means you can effortlessly transition between different layouts during your report development journey. It's all about streamlining your workflow and enhancing your productivity.
.gif?width=584&height=298&name=layout%20switcher%20(2).gif)
For the detail-oriented report creators among us, this feature is a dream come true. The new bubble range scaling setting gives you the power to finely calibrate how bubble (marker) sizes represent your data, offering unparalleled precision and customization. Here are the three fantastic options at your disposal:
Customizing this setting is a breeze. You'll find it in the formatting panel under Markers > Shape > Range scaling for scatter charts or Bubbles > Size > Range scaling for maps. Your reports, your way!
Efficiency meets aesthetics with the on-object interaction upgrade. Now, you can horizontally resize on-object menus, a game-changer, especially for developers dealing with lengthy field names. But that's not all – Power BI has taken it up a notch by optimizing the positioning of these menus to maximize canvas space usage. As a result, the on-object menu gracefully ascends and expands into the canvas area, ensuring the visibility of field wells without the need for tedious scrolling. It's all about intuitive design that puts your data in the spotlight.
The August update for Power BI is a testament to its commitment to empowering users. These enhancements promise a reporting experience that's smoother, more precise, and visually stunning. If you're ready to elevate your reporting game, now's the time to explore the incredible features of the Power BI August update. Your data storytelling journey just got a whole lot more exciting!
0
5 October 2023 8:30:00 AM
6 min
In today's data-driven world, making informed decisions is not just a competitive advantage; it's a necessity. Power BI, Microsoft's robust business intelligence tool, has emerged as a go-to solution for organizations across various industries. Whether you're a small business owner, part of a dynamic team, or part of a large enterprise, Power BI offers a range of options to cater to your unique data analysis and visualization needs.
.png?width=533&height=300&name=PowerBi%20(1).png)
In the realm of business intelligence and data analytics, Power BI stands tall as a dynamic and versatile tool that empowers organizations to turn raw data into actionable insights. It's more than just software; it's a comprehensive ecosystem with various components and features that seamlessly integrate to provide a robust analytics experience. In this blog post, we'll dive deep into the key components and features of Power BI that make it an indispensable asset for data-driven decision-making.
 |
Power BI: Key Components and Features |
|
Power BI Desktop |
At the heart of Power BI is Power BI Desktop, a Windows application that serves as the creative hub for building reports and dashboards. Within this powerful tool, users can connect to an array of data sources, perform data transformations, model data, and craft interactive visualizations. It's the canvas where data stories come to life. |
|
Power BI Service |
Once your reports and dashboards are ready, they can be published to the Power BI Service, a cloud-based platform that takes collaboration and accessibility to new heights. The Power BI Service enables users to share and collaborate on reports and dashboards effortlessly. Features like data refresh, automatic updates, and external sharing make it a hub for data-driven decision-making. |
|
Power BI Mobile |
In today's fast-paced world, the ability to access data on the go is invaluable. Power BI Mobile offers iOS and Android apps that allow users to access their reports and dashboards wherever they are. The mobile apps provide a responsive and touch-friendly experience for seamless interaction with data, ensuring that insights are always at your fingertips. |
|
Power Query |
Power Query is a data transformation and cleansing tool integrated into both Power BI Desktop and Excel. It simplifies the process of connecting to various data sources, cleaning and reshaping data, and creating data models for analysis. With Power Query, data preparation becomes a breeze. |
|
Power Pivot |
Power Pivot is a data modeling tool that empowers users to create relationships between data tables, build calculated columns and measures, and perform advanced data modeling tasks. It's a crucial component for those seeking to gain a deeper understanding of their data. |
|
Power View |
Within the Power BI ecosystem, Power View is the go-to tool for creating interactive and visually appealing reports and dashboards. It helps users transform data into insights that are not only informative but also captivating. |
|
Power Map |
For spatial and geographical data visualization, Power Map comes to the rescue. This 3D mapping tool allows users to explore and interact with data in a spatial context, providing a new dimension to data analysis. |
|
Power BI Embedded |
Power BI Embedded extends the reach of Power BI by allowing developers to embed reports and dashboards into custom applications, websites, or portals. This feature ensures that users can seamlessly access data visualizations within the applications they already use. |
|
Power BI Premium |
For organizations with larger data needs and a thirst for advanced features, Power BI Premium delivers. It provides dedicated capacity for data processing, enhances data security, and allows for the sharing of reports with a broader audience without the need for individual licenses. |
When it comes to choosing the right Power BI plan for your organization, you have three primary options: Power BI Free, Power BI Pro, and Power BI Premium. Each plan offers distinct features and capabilities, allowing you to tailor your solution to your specific requirements.
Best for: Small-scale, individual use with limited sharing and collaboration needs.
Power BI Free is the entry-level option, providing a taste of the tool's capabilities at no cost. It's perfect for solo entrepreneurs, startups, or anyone looking to explore Power BI's capabilities without a financial commitment. With Power BI Free, you can connect to various data sources, create basic reports and dashboards, and even share them with others.
Best for: Teams and organizations that require more sharing and collaboration features, as well as scheduled data refresh.
Power BI Pro takes collaboration to the next level. It's designed for teams, departments, and organizations where data sharing and collaboration are crucial. With Power BI Pro, you can publish reports to the Power BI service, share them with colleagues, and even collaborate on reports in real-time. Scheduled data refresh ensures that your data is always up to date.
Best for: Larger enterprises that require dedicated capacity, extensive sharing options, and advanced features.
Power BI Premium is the choice for organizations with more extensive data analytics and reporting needs. It offers dedicated capacity, meaning faster performance and more reliable service. With Power BI Premium, you can share reports with external users, distribute content to a broader audience, and take advantage of advanced features like paginated reports and AI capabilities.
The choice of the right Power BI plan depends on various factors, including your organization's size, budget, and specific requirements for data analytics and reporting. Smaller businesses and individuals may find Power BI Free or Power BI Pro more than sufficient, while larger enterprises often opt for the robust capabilities of Power BI Premium.
In summary, Power BI is a versatile tool that can cater to the needs of organizations of all sizes. Whether you're just starting your data analytics journey or looking to expand your existing capabilities, there's a Power BI plan that's just right for you. By selecting the plan that aligns with your unique needs, you can unlock the full potential of your data and drive informed decision-making within your organization. So, go ahead, harness the power of Power BI, and transform your data into actionable insights.
0
10 June 2020 10:00:00 AM
3 min
DataFusion is a TM1 connector developed by Octane. It was borne out of the frustration clients were facing extracting data out of their IBM Planning Analytics application into other enterprise Reporting tools like Power BI, Tableau and Qlik. Typically this was set up as a manual task that required an IT or Finance person to drop a csv and upload into Reporting tools. This created a number of issues. Firstly it required the operator to ensure that the correct slab of data has been captured and uploaded. It also created a risk as end users would have to rely on operator to perform this task on as regular basis as required. This created a single person dependency and if he or she is on leave the data may not get refreshed as teams scramble to find out how to do this task.
The other issue with this approach was a lack of consistency. Being manual the end users would not have a clear idea of when the data from Planning analytics was uploaded. There also was a constant reconciliation issue as numbers would move in Planning Analytics TM1 and would not be reflected in the Reporting tools in a timely manner.
Enter Data Fusion – This is a Planning Analytics TM1 connector Octane has developed to solve this problem. This allows your Planning Analytics TM1 to connect to all major Reporting tools (Power BI, Tableau and Qlik). Datafusion comes with an easy to navigate admin portal to allow admin to set up the data block amongst frequency of data flow to reporting tools. End users can be given access to load data on an ad hoc basis if required. Datafusion uses TM1 security for data access.
Datafusion not only brings in the updated Data but also metadata so if you have a new element like a store/division or region in you Planning Analytics TM1 it will flow through to your reporting tool automatically.
Quick and direct data accessibility from all major reporting tools. Ensure your reporting tools across enterprise are reporting on the most up to date data and everyone is using the same numbers for reporting
Set up the connector once and it runs automatically. All processes are automated and requires no human intervention for scheduled runs.
Reduces the turnaround time of the data refresh process by direct and automated process. Also eliminate all your reconciliation issues with varying numbers between Planning Analytics TM1 and your Power BI’s, Tableau and Qlik.
Easy to use interface and quick setup. Fully supported by Octanes Support teams.
Flexible enough to extract any new records with new elements built. Bring in data and metadata and have it displayed in your reports automatically
There is zero manual handling of data and almost fool proof for your data reconciliations and traceability of data in your Reports.
If you are using Planning Analytics TM1 and taking data out using csv or any other manual method then you should contact us for a demo to see how DataFusion can automate this process for you.
.png)
print
Leave a comment