In today's fast-paced business world, having quick and easy access to detailed insights from summary reports is a must. For businesses using OneStream, the ability to drill down from a summary view to detailed source data is a powerful feature that enhances data analysis and decision-making. This blog explores how OneStream can be used to create linked cube views and drill down into source data, offering a smooth and intuitive experience for users.
The Scenario: Drilling Down to Source Data
A client engaged with Perficient wanted the capability to drill down from a summary report into detailed information within their Thing Planning database. The project team set out to achieve this by creating two main drill-down functionalities:
- Drill Down from Summary to Detail within the Cube: This allows users to navigate from a high-level summary view to more granular data within the cube.
- Drill Down from Cube to Individual Transactions: Once users are within the cube, they can drill further into specific transactions stored in the Thing Planning database.
This setup was implemented using Linked Cube Views and Bound Parameters, giving users an intuitive and seamless data exploration experience.
.
How It Works: Using Linked Cube Views and Bound Parameters
Let’s break down the steps the project team took to achieve this drill-down functionality:
.
1. Using Bound Parameters
Bound parameters are key in linking different cube views. In OneStream, these parameters are set up within the cube view’s General Settings and are crucial for enabling navigation between different levels of data. For example, the project team created a bound parameter for the UD1 dimension, naming it ParentClick_UD1.
- Bound Parameter Naming: You can use a combination of numbers, letters, and underscores in bound parameter names (no spaces or special characters). The parameter ParentClick_UD1 was used for the UD1 dimension.
.
.
2. Linking Cube Views
After setting up bound parameters, the next step is to link different cube views to each other. In the Linked Cube Views section, the project team selected the cube view to link to.
- Inserting Bound Parameters: The bound parameter is added as a parameter within the linked cube view’s Point of View (POV). This is done by enclosing the parameter name with solid lines and exclamation points, like |CVUD1|.
By using the parameter in both total and detail rows, the user interface becomes cleaner and more efficient. This means that the UD1 member can be updated once (in the POV) instead of having to be manually updated on each row.
.
3. Setting Up Cube Views for Drill-Downs
Once the linked cube views and bound parameters are in place, users can drill down easily by interacting with the cube views. Here's how the system works:
- Running the Summary Cube View: Users start by viewing the summary data in a cube view. When they right-click on any cell in the summary view, they can navigate directly to the linked detail cube view.
- Navigating to the Detail Cube View: The linked detail cube view opens, showing more granular data related to the selected summary information.
.
.
4. Building Dashboards for Parent-Detail Views
To enhance the user experience, the project team built dashboards that integrated both the parent and detail cube views.
- Adding Cube Views to Dashboards: The team placed the cube views as components within a dashboard, allowing for a seamless transition from the parent to the detail view.
- Setting Refresh and Redraw for Detail View: The detail cube view component was set to refresh and redraw whenever the user clicks on a summary item in the parent cube view. This ensures that the detail data updates dynamically based on user input.
- Combining Dashboards: The final step was to combine the parent and detail dashboards into one unified dashboard. When users click on a number in the parent cube view, the detail data automatically updates in the linked detail view.
.
.
5. Adding Detail from the Thing Planning Database
To take it one step further, the project team integrated data from the Thing Planning database.
- Setup Bound Parameters for Scenario, Time, and UD1: The team configured the bound parameters for these dimensions in the detail cube view.
- SQL Table Editor: The team copied the SQL Table Editor Register component from Thing Planning and incorporated it into the dashboard. This component was configured to use the bound parameters in the WHERE clause of the SQL query, ensuring that the data displayed is relevant to the selected cube view.
- Dynamic Data Refresh: Whenever a user interacts with the detail cube view, the Thing Planning dashboard refreshes automatically to reflect the most up-to-date data from the database.
.
The Final Result: A Seamless Drill-Down Experience
The end result is a dynamic, user-friendly dashboard that allows users to seamlessly drill down from high-level summary data into detailed source data. Here’s how it looks:
- Summary Cube View: Users start by viewing aggregated data at a high level.
- Drill Down to Detail Cube View: Clicking on any summary value opens up a detailed cube view.
- Drill Down to Individual Transactions: The Thing Planning database is also integrated, providing users with transactional details.
- Dynamic Refresh: As users interact with the cube views, the dashboards refresh in real-time to reflect the relevant data.
This integration between Linked Cube Views, Bound Parameters, and Thing Planning data creates a powerful drill-down functionality that improves the decision-making process and enhances the overall user experience.
.
.
Conclusion: Why This Matters
For businesses relying on OneStream, the ability to drill down from summary reports to granular source data helps users better understand the story behind the numbers. By linking cube views and using bound parameters, users can easily navigate through different layers of data without losing context.
+This approach not only saves time but also empowers decision-makers to dive deeper into their data, gaining insights that would otherwise be difficult to uncover in traditional reporting methods. If you're looking to enhance your OneStream environment with powerful drill-down capabilities, this approach is an excellent starting point. Happy drilling! To learn more, check out our blogs on onestream!
Author
