Tips and tricks for major release OneStream 5.2

Every major release comes with detailed release notes. The ‘OneStream 5.2.0 Release Notes’ takes this to the next level. In addition to the earlier available documentation, OneStream has created five (!) new manuals about the features released from version 5.2. What are the new features, fixes and how to upgrade? We’ve summarized them for you, so you can easily benefit from all the new functionalities this major release has to offer. What are the tips and tricks for OneStream 5.2?

Features available in OneStream 5.2

In short, the following features are available in this major new release:

• BI Blend
  • BI Blend expands the OneStream platform further into the BI/ Analytics landscape. With this solution, OneStream is able to do high performance, high volume analytic reporting.
• BI Viewer
  • A new dashboard component to create dashboards using drag-and-drop functionality on existing and new data sources. This addition makes OneStream a serious player in the BI tools market, and a serious competitor for Tableau and/ or PowerBI.
• Other dashboard components
  • Pivot Grid (Standard and Large Data)
    • A pivot component within a dashboard allowing the user to drag-and-drop the dimensions interactively, add totals and (conditional) formatting and even calculated accounts. There is a specific component for (really) large data sets.
  • BI Blend adapter
    • To connect from a dashboard component to the BI Blend data.
  • Enhancements to the SQL Table Editor
    • Possibility to select multiple rows and pass those to another component/ parameter.
    • Allow users to save the table state like column order, making this more interactive.
  • Table View
    • A new option in the OneStream windows client Spreadsheet utility. This feature has been added to show and edit data in a user-friendly tabular layout.
  • REST API
    • OneStream now has a REST API, allowing other tools to interact with OneStream via this API. It accepts and returns data in JSON format. Details are described in the ‘OneStream  API Overview Guide’.

How to upgrade to OneStream 5.2

The ‘OneStream 5.2.0 Upgrade Guide’ is included in the release notes. At first glance, it looks a bit more challenging than earlier versions (it’s 5 pages). Actually, it contains literally every step you need to take to upgrade your current environment. In practice it took me less than 1 hour (having everything on 1 server) to upgrade. So, upgrading remains as easy and quick as it was before. A (small) database upgrade is required.

Now, let’s have a deeper dive into the most interesting new components available.

Tips for using BI Blend in OneStream 5.2

The BI Blend solution is accessed via the OneStream Workflow interface. It offers a read-only aggregate storage model that rationalizes the source data for uniform and standardized reporting. BI Blend data is stored in a new relational column store table outside of the OneStream database.

During our tests and research, we’ve noticed the following:

• Fast aggregation for (very) large amounts of data.
  • Eliminations are not performed.
• Single reporting currency translation.

• • Direct only,
  • 1 reporting currency per BI Blend process.

• Leveraging OneStream Metadata, Reporting and Integration tools.
  • But doesn’t utilize relational properties, like aggregation weight and percentage consolidation.
  • Additional aggregation points can be included via Derivative Transformation rules.
• Executed outside the cube.
• View dimension is not supported.
• Basic time math (via helper rules).
• Simultaneous multi-period data loads up to 12 periods.
  • BI Blend is a CPU and Memory intensive process.

Data within BI Blend

The Stage engine is used to integrate transactional data where BI Blend source data will contain additional members and values that are defined in the integration as attribute members.

BI Blend will utilize the Cube dimensions to transform the records into a unified reporting format (including aggregated values), which is stored in a structured relational table for OLAP reporting. This table can be accessed via the standard OneStream BI Dashboard and Grid reporting tools as well as relational blending into Cube views and dashboards. The output are read-only fact tables optimized for reporting with high level compression. These tables are created by the BI Blend process automatically.

 

 

 

 

 

 

 

The Cube’s integration settings will define the Dimensions the BI Blend Engine can utilize. Determining the Blend Unit dimension is a key decision. And unique top members across dimensions are preferred. BI Blend does support Extensible Dimension hierarchies, tested and designed up to two levels of nested cubes. The BI Blend settings can be found (by scenario type) on the workflow import channel.

During the initial processing a learning mode will automatically run to optimize the processing. if the Blend Unit is changed, you should enable this again.

We’ve noticed the following:

• Data access and other data cell level controls are not supported in BI Blend.
• The validation is limited to target mapping. Data intersection validation is not performed.
• A log file is created and intended to provide statistics to manage the blend task.
• Only UD8 can be used to generate Attribute Dimension members for aggregation.

Tips for using Pivot Grid in OneStream 5.2.

The Pivot Grid Standard is a new component within the Dashboard Maintenance Unit. Please note that this feature is only available in the Windows App. You can create a pivot table in a ready-to-go layout. This enables your users to customize the layout of the report, using drag-and-drop operations and conditional formatting. The result is displayed in a cross-tabular format and can be sorted, grouped and filtered. It also supports drill-down and can be printed and exported.

Pivot Grid loads the entire table contents from the Data Adapter that is returned to the client machine. Performance is tested acceptable until 100.000 records.

Possible data sources are:

• Cube views. The MD command type is recommended for a simplified view of the data.
• SQL
• Method Query, such as CertificationWorkFlowUnit, DataUnit, DataUnitComparison or ICMatchingforWorkflowUnit.

 

 

 

 

 

 

 

 

 

 

How to use Pivot Grid

A few tips and tricks to get the most out of Pivot Grid:

• Dimensions can be dragged to the hidden fields (to limit the amount of data loaded), filter area, row area, column area or data area.
• Multiple dimensions can be assigned to the row or column using a comma separator.
• The Grid settings are modified via right-clicking the properties box. Users can apply global filters (show pre-filter), activate (sub-)totals for groupings and aggregations. Users can save their adjusted pivot grid or restore to the original setup.
• Calculated fields can be added to the column and/ or grouping to perform string, date-time, logical and math operations over data (both as text and as decimal). These can be built from the Expression Editor.
• The user can also apply (advanced) conditional formatting by right-clicking on a cell in the pivot grid.

 

 

 

 

• It is best practice to name the resulting table for easy recognition. This table is generated when the data adapter is run.
• A loop parameter can be used to loop through a list of Entities to return multiple entities for a cube view. But don’t loop over any dimension that is in the row/ column of the grid!

Large Data Pivot Grid

The Large Data Pivot Grid is comparable to the Pivot Grid. The main difference is the functionality for supporting large data sets. This feature supports paging functionality and server-side processing, where the Pivot Grid processes on the client side. The Large Data Pivot Grid is tested with acceptable performance with 2 to 4 mln records. Fields can be excluded to remove unwanted database columns from the large data grid.

Tips for using Table Views in OneStream 5.2

Table views can be used when creating Dashboards where relational data is required. This feature offers a flexible solution to design a form or as a data collection tool.

Tips and tricks for using Table Views:

• It is easily accessed via the OneStream Windows Application SpreadSheet utility.
• This feature is designed to update records in a data table and utilizes client-side functionality. It is not possible to do inserts to create new records and does not support paging (for large data sets).
• The table view is defined in a business rule with new BRAPI functions (also for authorization), this is then inserted into a spreadsheet component.

 

 

 

• A new SpreadSheet Business Rule type is available with functions like GetCustomSubstVarsInUse, GetTableView and SaveTableView to define the look, feel and behavior of the table.
• The “OneStream Table Views” guide contains explicit examples of possible functionality within the script as an easy starting point.

 

 

 

 

 

 

• Parameters can be used to make the table view more dynamic. Also, member functions like IsDirty, IsHeader, OriginalValue can be used to determine how the data is processed.
• The table view uses XFTV Named Ranges .
• Security is incorporated within the script and can be applied on Data level or table level.

Tips for using BI Dashboard Designer & Viewer in OneStream 5.2

The most extensive addition to 5.2 is the BI Dashboard Designer & Viewer. Dashboarding has been pretty labor-intensive in prior releases, mainly due to the building and maintenance of it. Building a dashboard required a large number of components to be linked together, making it pretty maintenance intensive. That will all change now, at least that is the promise…

The BI Viewer is a new dashboard component, which is actually a BI dashboard builder on its own. In my opinion, due to the extensive functionality it could have been a separate menu item in the Presentation menu.

During our research and tests, we’ve noticed the following:

• Via drag and drop you can quickly and easily create a dashboard with a number of dashboard items to show your data.
• Next to basic chart types like bar, line and scatter there are some interesting possibilities to visualize your data.
• Gauges can communicate 2 values (the needle and the marker scale) and can be represented in a (semi-)circle or as a bar.

 

 

 

 

 

• Range filters are presented as a bar charge and can apply date filters to other dashboard items by using the slider.

 

 

• Grid shows data like an excel table with the use of icons and sparkline charts. The layout can be defined by column.

 

 

Please note that for delta calculation, Grid is bound to two measures.

• Maps can colorize the required areas in proportion to the appropriate values. But can also show icons on specific latitude/ longitude intersections with labels and drill-through possibility or (pie) graphs at specific locations varying in size.

 

 

 

 

If multiple datapoints are too close to each other, they can be clustered allowing the user to drill down/ zoom in.

• Charts can show multiple data points and allow drill through. There are various types of charts. Personally, I really like the range chart, showing max/min values over a period of time. Also, you can now apply two y-axes, which was a struggle before.

 

 

 

 

• Cards are like tiles that shows the difference between two values in different representations, such as number, icon, percentage, chart.

 

 

 

 

 

 

• The (Large data) pivot as described above can also be added via the BI dashboard designer as a component on your dashboard.
• Another nice feature is Panes where each pane has its own Y-axes, but both use the same x-axis, legend and data.

 

 

 

 

 

• Text boxes can be used to show information in a textual way but make the content shown dependent of the selection in the dashboard. This can be used to give the user instructions or a detailed description of the item selected.
• Treemap visualizing data can be grouped in columns or squarified where the data is shown in colors and sizes.

 

 

 

 

Color schemes can be used in various ways, managed globally and even added to the dimension members. This allows you to show a specific entity/ department in the same color via a global color scheme.

• When the new BI Viewer Component is added, the starting point is the BI Designer tab where a data connection needs to be added. This process is similar as before (so not described here in detail).
• The Data items pane allows you to create and modify data binding via drag-and-drop.

 

 

 

 

 

 

 

 

 

 

 

• The BI Designer has its own section for parameters to filter on data (inbound) and pass from the BI Viewer to other dashboard components (outbound). These can be linked to existing OneStream parameters e.g. pParameter = |!osParameter!|
• Data can be added from all known sources and is stored in a results table. It is advised to give this a specific name to distinguish between different data adapters. It is also possible to store multiple time periods to allow data ranges.
• Data can be filtered based on static values, other field values and/ or parameters (via binding).

 

 

 

 

 

 

• It is also possible to create calculated fields to apply complex expressions to data fields.
• There is a separate menu for conditional formatting which is quite extensive. Next to color ranges you can apply icons, bars, gradient filling etc.

 

 

 

 

 

Many functions that I use in Excel or other dashboarding tools are already available in this first release, which is quite impressive. Besides many chart types and possibilities to present the data, you can easily add a secondary axis, advanced filtering (on argument, data series or even data points) and drilling down to the source data/ documents.

Also, delta calculations are flexible with delta icons and selections like higher is better/ worse. Even tolerances can be applied easily. Another example is the processing order of calculating totals that can be configured row by row or first columns then rows. In the layout it is easy to do cell merging, word wrapping or show banded rows via a simple standard button.

For the various item on a dashboard the cross-data-source filtering can be switched on or off. When you select a bar for a specific period, other items on the dashboard can directly take over this selection as a filter.

Other new features in OneStream 5.2.

Next to the features described above, there are also many other interesting new features and enhancements like:

• There is a CubeView MD (multi-dimensional) adapter available (as discussed above), returning the Cube View data in a structured way for easier processing in dashboards.
• It is now possible to copy adapter components in dashboards, saving you a lot of time.
• Spell check is implemented for the text editor and data cell attachments.
• Various Market Place updates are available, most interesting is the Administrator Solution Tools (combining Home Page Manager, Log Cleaner and Member Formula Builder functionality).

The impact of OneStream 5.2

The new 5.2 release adds a lot of functionality to OneStream as a platform. With this release, I dare to compete against other BI providers even when consolidation or Business Planning is not in (the first) scope. OneStream is now an adult solution for organizations with specific BI requests that are looking into tools like Tableau and / or Power BI.

More details

If you’d like to read the OneStream documents, I advise to start with the BI Dashboard Viewer chapter (page 427 of the “BI Dashboard Designer and viewer” document).

Want to more about the impact of this major release on your organization? Please give me a call or drop me a line. I’m happy to discuss the details of the new features for your platform.

Let’s discover the OneStream Market Place together

Want to know more about the OneStream Market Place? In a series of blogs, we’ll delve into the details of the Market Place and the solutions available. In the blog ‘The OneStream Market Place in real life’ you’ll find more details about the basics of the Market Place. And in the blog ‘Why you get more out of your OneStream software with Task Manager (UTM)’, we discussed why the Task Manager is so popular.

Any questions or suggestions for a Market Place solution you would like to read about? Don’t hesitate to contact me!