Authored By Nick Scott and Humza Malik | Advisory & Transformation (Data Analytics)
It goes without saying that organizations using, or considering, OneStream for their corporate performance management needs are looking to find ways to maximize this enterprise investment, and their data, to drive the business forward. And while there are robust reporting and analytic capabilities within OneStream, many organizations are seeking a centralized solution to merge business application data, operational data sets, and other enterprise-wide data sources.
This is where the power of a data warehouse and/or a visualization solution such as Microsoft’s Power BI comes into play. And fortunately for organizations asking the question, “How do we connect Power BI directly to OneStream?”, we now have an answer.
Merging Two Platforms Together
The good news is OneStream provides the mechanism to make this happen through their REST API. While figuring out these integrations yourself is possible, working with an outside partner will save you many unnecessary headaches as well as your precious time. Through our Data Analytics team’s collaboration with the OneStream team, we help companies maximize their investment in the OneStream CPM solution and achieve this centralized data platform they desire.
To efficiently integrate Power BI and OneStream, we developed a methodology that provides both a direct connection to Power BI and also preserves core features such as maintaining dimensionality and hierarchies. It takes OneStream’s extensibility concept to another level, by enabling Power BI to drill through from consolidation Cube Views to Extended Cube views within one dashboard.
(Example layout of a single dashboard view)
Reader Note: If you are unfamiliar with API development, the ensuing content is going to be quite technical, and might be more than you bargained for. If that’s the case for you, how about we schedule time for a live demo? If this is right up your alley, please keep following along.
Want to Skip the Technical Parts & See the Solution Live?
Our Approach to the OneStream REST API Implementation
At a high level, the OneStream REST API, after the authentication process, prompts a data adaptor to run to return a table extract using method query/XFBR which is then received in Power BI where a data model can be created followed by commencement of dashboard/visualization development. The REST API connection is built in Power BI while following the guidelines and practices provided by OneStream. A set of sequential processes occur as the API is kicked off using Power BI refresh functionality as mentioned below:
- Authentication: The first step is to retrieve an access token from your provider, such as OKTA, Azure AD and PingFederate. This Bearer Token once retrieved is then passed to OneStream to establish a secure connection. The first part of the API call sends out a request to the provider using the provider URL along with relevant headers and body elements such as ID, Secret Key, etc. The Bearer Token is then retrieved and saved as a parameter inside Power BI.
- Connecting to OneStream (Data Provider): In the second part of the API call, the token along with login credentials are passed in to establish a secure connection to the OneStream application. Relevant headers, body elements, and error handling elements are passed through to access the application while also requesting execution of desired data adaptor(s). A data adaptor is used to return tables using cube view/report. Based on the elements of the API call, desired tables and metadata can directly be retrieved from the Cube View into Power BI.
- Data Model Creation: As API calls execute and data/metadata are loaded into Power BI, custom steps are then used to alter and enhance your data set into flexible fact and dimension tables. Some of these enhancements include the rebuilding of dimensional hierarchies to allow for drill-down functionality for a similar navigation experience found in OneStream. Joining these tables together based on common fields creates a data model which is the foundation of Power BI report development. These relationships help create a pathway for users to navigate various datasets across dimensions. This design also significantly enhances reporting performance and scalability for future reporting.
- Dashboard Development: Now that a live connection is established between the OneStream and Power BI along with a data model to report on, dynamic transformations and calculations using DAX in Power BI Desktop that can be used to further enrich your data set. A benefit of bringing your data in to Power BI is the ability to quickly navigate to the lowest levels of your hierarchies without the use of Extensibility inside OneStream. This adds faster reporting and drill through to the details at the tip of your fingers. As dashboards holding insightful and interactive visuals/reports are created they can easily be published and deployed to your business with the use of the Power BI Online service.
The Future of Automated Solutions
We believe that as the volume of data significantly increases on a regular basis, and global trends move toward automation, the need for automated solutions and seamless data pipelines using different techniques will continue to rapidly grow as well. If this data-driven philosophy aligns with your organization’s strategic plan, we’d love to discuss and see if we might be the right partner for you and your business. Please reach out to our team today!
