Overview

Whether designing or building a OneStream application, it’s vital to keep end-user experience, performance, and administration in balance. An application that lacks any one of them risks full acceptance and ultimately a successful rollout within the organization. In this article, you’ll read about guiding principles that will steer you towards establishing the balance needed within your application.

Design

Design the process. It’s important to know exactly who is doing what and when they’re doing it.

By designing the process, you’re designing the Workflow. There are many steps that need to be completed throughout it. An accounting to reporting process may include:

• importing data from a source system or a file
• entering of data via form
• posting of journal entries (which can include preparation, approval/rejection and posting steps)
• calculating/translating/sub-consolidating data
• reviewing/rejecting/approving data
• publishing final reporting packages

Alternatively, a planning or forecasting process often looks completely different from the accounting process:

• seeding data from actuals, prior forecasts or budgets
• updating drivers, limits, percentages
• creating targets for different departments, regions or business units
• adding/updating calculations
• calculating/translating/sub-consolidating data
• reviewing/rejecting/approving of final submissions

The process includes knowing your business rules and member formulas along with how/when they will be triggered throughout the data submission process. Calculate, translate, and consolidate only when necessary and not excessively. One common request is to run calculations when a user saves data in a form. It’s important to know which calculations will run when that user saves. If the process or calculations haven’t been well planned, the system can run calculations unnecessarily and/or excessively and that takes time. This wait time negatively impacts performance (or perceived performance) as well as the end-user experience.

Utilize extensibility in the Cube design. This foundational design principle should be incorporated into every OneStream application. Our customers’ end-user experience, data quality and application performance all benefit from extensibility as it’s one of OneStream’s many differentiators. Although the business requirements may not alert the implementation team that extensibility is necessary during the initial implementation, it provides future flexibility should the need arise. The maintenance of extended cubes and dimensions may not be as straightforward as other products however, administrators will quickly learn where and how to maintain them.

Write efficient, concise calculations. Doing this in both business rules and member formulas improves performance by reducing redundancy and excess. You establish efficiency by pairing process knowledge with good VB.net and/or C# practices. Specifically for OneStream, keep the following in mind.

1. Understand how, when and who will trigger the calculation – knowing the entire process, from data submission through corporate consolidation, will help you optimize application performance. Building the triggers into the process results in rules running only when necessary and not excessively. This couples tightly with Workflow design, covered later in this article.

You can also get creative by building in “perceived performance”. Let’s take a forecast seeding process as an example. For the M9 forecast, OneStream needs to copy nine months of Actuals data along with 3 months of the prior forecast data as a starting point for the FP&A team. For simplicity, each month takes one hour to complete so once M9 closes, FP&A needs to wait 12 hours to begin their process. If we think about this, M1 – M8 have been closed for some time. We can seed M1 – M8 data while no one’s waiting for it to complete. At the same time, the prior forecast data has been ready weeks prior to the current month closing so we’ll seed that, too. Now, when M9 closes, the only data that needs to be copied is M9 Actuals. Although it still takes 12 hours to seed all months, FP&A users only need to wait an hour after M9 closes to start their process. This is what I mean by “perceived performance” – it’s not faster, but because end-users wait less, it seems faster.

 

2. Add conditions for data unit dimensions – most calculations don’t need to run on both an entity’s local currency as well as a translated currency because OneStream translates the result of the calculation. However, there are exceptions to this, such as copying Net Income from the P&L to the Balance Sheet. When the NI is copied to Current Year Net Income in local currency, if OneStream were to translate this, it would use the closing rate and direct method – we don’t want this. In this case, we want the calculation to happen in both local and translated currencies.

A second example of adding conditions on data unit dimensions is excluding the calculations from running on parent entities. Parents consolidate the values of their children. Once that happens, if the calculation runs again, the result of the calculation will likely yield the same result as the consolidation. This introduces redundancy and can negatively affect the overall performance of the application.

3. Target specific intersections when writing clear and calculate statements – leaving a dimension open on a calculation when the calculation will yield results on a small subsection of that dimension doesn’t necessarily mean that performance will suffer. However, OneStream is evaluating intersections that will never yield data and that takes time. It may be milliseconds for that particular calculation but those add up quickly in applications containing a high volume of calculations.

 

4. Minimize nested loops and eliminate looping over lists. By itself, looping through lists of text is fast and unnoticeable to an end-user. Updating metadata properties via apis within loops also has a minimal effect on rule performance. However, introducing database calculation calls into those loops is where the time begins to add up. To counter this, OneStream introduced filtering into their database calculation calls. So instead of creating a list of base members under a parent and doing a calculation, write the calculation and filter it on Parent.base.

 

5. Set data buffers once, outside of any necessary For/Next loops – data buffers should only be used when cell-by-cell processing is necessary. While looping through each cell, avoid database calls. Instead, create a result data buffer and while looping, add the result cell to it. Once the loop completes, write the result data buffer to the database. You’re only hitting the database once rather than at every trip through the loop.

 

6. Minimize writing zero or near zero data to the database – by wrapping your calculate statements with RemoveZeros. Unnecessary zeros in the database generally provide no value while increasing Data Unit size which can negatively impact performance. That being said, copying data between two scenarios that have differing View and No Data Zero View properties often requires loading/calculating zeros.

Establish standards, minimize exceptions, and create dynamic, consistent artifacts. Inconsistencies among artifacts and within processes can increase maintenance complexity, lengthen the time to resolve issues and confuse end users and administrators. Standard naming conventions, a seemingly trivial subject, can greatly improve the administrator’s experience maintaining the application. Consistency among Cube Views allows easy adoption for both end-users and administrators. Sharing row and column sets and utilizing parameters and member filters eases maintenance.

Conclusion

The two most inadequately designed areas that I’ve seen are Workflow and extensibility. The requirements and design phases are understandably early in the implementation to know the exact steps and tasks that will happen in the final Workflow. However, it’s important to have a mid- to high level outline in mind. It provides the skeleton on which to build and gives the implementation team, including SMEs, an idea of what the Workflow may look like.

You’ll often find that customers may push back on building extensibility into their application. Why? Not only is it a difficult concept to grasp for those new to OneStream, but it’s also a challenge for them to understand it enough to realize the benefit. I suggest that you collect the requirements and present a design that includes extensibility, explaining why it’s the best design for their application.

It's important to keep all the principles in mind throughout the implementation. Regardless of if you’re writing rules or member formulas, building Cube Views or dashboards, or designing the cube structures or process, think about how it affects the end-user’s experience, the application’s performance, or the administrator’s responsibility to maintain the application once the consulting team departs.

 

Original Source: Blueprint Bulletin