Reporting

Originally written by Jacqui Slone and Chul Smith, updated by Chul Smith

Getting data loaded into a flashy new system doesn’t really mean anything unless you’re able to consume all of that data in a meaningful, easy-to-read, and elegant manner. OneStream has an abundance of ways to present information, based on the customer-provided reporting requirements.

So, what are reporting requirements? Simply put, customers answer the question, “When do which users need to view what data?”

These requirements will obviously include the monthly, quarterly, and yearly reporting packages at varying levels, depending on the audience, but they’ll also include any data to be reviewed throughout the various data submission processes. In a nutshell, reporting requirements answer when, who, and what. Together, you need to determine the ‘how and where’ and recommend the best methods – there are usually more than one – to present the data, given those requirements.

Determining the Reports to Build

Prior to endorsing any reporting methods, you must determine all of the reports you need to build. To do that, I recommend building a report inventory of every report, big or small, that users view at any point within (or upon completion of) their data submission process.

A customer with 13 business units produces a balance sheet, income statement, and statement of cash flows for each BU, along with a fully consolidated version of each. The 13 BUs plus the consolidated version equates to 42 reports! After a bit of analysis, all of the rows and columns are the same for these reports, with the differentiator being the BU. You would know to narrow this down to three reports, with some type of mechanism to vary by BU (which will be covered later in this chapter).

In another example, a customer may have a user who enters long-term debt rollforward data into a form. That user must look at some type of report to ensure that the data entered validates to the activity of each of the accounts. You have several options to make this user’s process less painful (assuming we all think manual data entry can be somewhat painful). You could present a report showing the validation, or you could build that validation directly into the form so the user can view it in real time as they enter data. Either way, these are Cube Views, and you know that they should be presented to the user while they’re entering data rather than letting them get three steps further into the process to ultimately discover that they need to go back three steps to correct an error.

A final example could enable a planning user to view a full report of KPIs just as they’re updating driver data in one of their forecasting models.

Include any visuals, charts, or graphs in the report inventory that will be required in annual reports, executive decks, or self-service dashboards for end-users. Many projects prioritize visuals lower on the list due to them being ‘nice to have’. I would argue that visual data provides clarity to a dataset and should, therefore, be prioritized like any other element. Yes, the visual underbelly is the data itself, but I would much rather look at a trending line graph than a matrix full of numbers.

The bottom line is that the more complete the inventory, the better the analysis and results of what reports need to be built, how they’ll be organized, and when they’ll be presented to the user.

Evaluating Reporting Options

Now that you’ve got a complete listing of all reports that need to be built, you need to determine and recommend the most optimal way of presenting them to their respective audiences.

The first question to ask is, “Will the users of this report have a OneStream license and have the ability to interact with the data?” This will lead you down one of two paths: one path grants the user the control to view, navigate through, and explore data as they’re trying to understand and interpret it; the other path results in a package of static, predefined reports that may generate questions upon which the user depends upon a licensed user to explain any detail external to the presented dataset.

Assuming that the user will have a license, the second question to ask is if that person will be submitting data during the data collection process or if they’ll only be consumers of the data. This is important because it will drive out those reports that need to be presented at some point during the process. I’ll explain further when we talk about Cube View groups and profiles.

Expanding on the previous section (where you determined that you have three reports), where should you build them – a Cube View or dashboard, or an Excel/Spreadsheet Quick View or retrieve? You have so many options; it’s sometimes difficult to determine what’s best for the customer. In the following pages, you’ll read through the characteristics and capabilities of each to help you select the best solution for your use case.

Cube Views Overview

By now, you are probably familiar with Cube Views and when and where you use them. Maybe you’ve built some yourself and have learned (the hard way) some of the topics that will be covered in this chapter. From those of you who are tasked with building your very first Cube View, through to seasoned Cube View veterans, you’ll hopefully pick up a trick or two.

Determining Cube View Build Items

You’ve got your report inventory, and you’re ready to start building your Cube Views – but you’ll need to hold your horses! You’ll want to spend some additional time analyzing the inventory – just as you did when you noticed that of the 42 reports, you only really need three.

Look for similarities or consistencies in rows and columns. Maybe all of the trending reports start with the final month of the prior year and run through the current reporting month. Maybe the variance reports compare Actuals to Budget and Actuals to Forecast, but not both in one report. Maybe you notice that some variance reports have the same columns ordered differently, depending on the report. In all of those cases, you know that you’ll need to build each of those column sets, but in the last case, you could challenge the customer to take this opportunity to standardize things by ensuring that columns are always in a specific order. If they’re open to it, great! You’ve just lightened your workload.

While analyzing the columns, spend some time on the row sets, too. It’s the same type of exercise. Is there a consistent, detailed account report? Is there a consistent, summarized account report? Are there KPIs or statistical reports that share the same or similar row sets?

Sometimes, consistencies and standards don’t exist in the customer’s current report inventory, but they’re hoping that OneStream will help resolve this for them. It presents a great opportunity for you to help establish standards and simplify their reporting.

In addition to the rows and columns, also note the dimensions that don’t change by Cube View. For example, the customer’s balance sheet always looks at all departments because it doesn’t make sense to them to break down by their Accounts Payable balances by department. If the department in OneStream is UD1, then you know that the total UD1 member will be used in the POV section of the Cube View. From a maintenance perspective, having the correct members set on the POV of the Cube View allows the Cube View creators to quickly know which dimensions will be listed or required in the rows and columns.

Report formatting may seem like a trivial piece of building reports when, in fact, it’s generally the opposite. I’ve been on numerous projects where I’ve asked for the formatting requirements and then received the response, “Whatever is the default in OneStream is fine.” Zero times has OneStream’s default been fine! It’s not that the default settings look horrible; it’s just not how the customers want to see their data. You can customize the look and feel of the reports to suit their requirements specifically.

Headers are important because users need to understand the data they are viewing. It doesn’t present well when all of the dimensions are listed out in the report name or page caption. Footers can be useful for page numbers, user IDs for who ran the report and when, etc. I think we all know the benefits of using footers. Consistent headers and footers across all Cube Views really give the customer their own customized standard look and feel to OneStream reports.

Another important standard for the customer to establish is the formatting of the data itself. Scaling, percentages (to how many decimals), and KPIs (to how many decimals) are all examples of those standards.

Why am I harping on at you to establish formatting standards so early in the build? Once the standards have been set, you can use dashboard parameters to format your Cube Views instead of formatting each one individually. Should the standard change at some point in the future – the customer wants to go from one decimal to two – the administrators only need to change the parameter, and it will flow through to all of the Cube Views where that parameter exists. No more clicking through each Cube View to update the formatting of data or headers! That will be covered later in this chapter as well.

Your customer has scaled reports, and they perhaps need everything to foot and cross-foot, so there’s a rounding component. Believe it or not, this topic creates heartburn on many projects. The debate centers around whether you should store rounding data in the database or just in the Cube View. OneStream strongly recommends that you build rounding into the Cube View. If the customer insists on storing these amounts in the database, bogus members will be required to store the data. Complex business rules or Member Formulas will be required to calculate the rounding data. Both of these will impact overall application performance and potentially create hundreds or thousands of data points that hold no value. Additionally, any changes to the metadata structures will impact both the rounding members and the business rules. On a past project, a customer asked me to “bless” the rounding rules, but I was unable to do so since I strongly opposed the decision. Customers looking to stray from OneStream’s recommended approach happens occasionally, but when it does, the customer needs to know, acknowledge, and accept that it’s not a recommended practice.

The reporting options presented earlier in this chapter will somewhat drive where your Cube View build will reside within Workspaces. A Workspace is a framework for building software using software, creating a robust environment for developing products on the platform. It simplifies the development process and extends development capabilities for solution creators.

Workspaces store maintenance units and facilitate community development by providing an isolated environment for developers to segregate and organize solution objects. Maintenance units are stored, created, and maintained in Workspaces, which vary by dashboard project need and application.

If you determine that a unique Workspace isn’t necessary for your Cube Views, they will reside on the Workspace called Default. In addition to Workspaces, you can create unique maintenance units to further organize your Cube View groups.

The decision to create separate Workspaces and/or maintenance units isn’t critical to get completely nailed down at this point in your build. Cube Views can always be copied to a Workspace or maintenance unit should you realize that it would be beneficial at some later point during the build. I would advise that any time you copy a Cube View to a new location, you take the necessary steps to sunset the old Cube View. This ensures that your Cube View library doesn’t balloon and begin to create confusion among administrators (and super users).

Establishing Cube View Components to Setup

OK, so you’ve now determined build items – row/column sets, POVs for each Cube View, formatting, rounding, and navigation links. Let the build begin!

Within your default Workspace and maintenance unit, start by creating Cube View groups that will contain your row and column sets. Sometimes, there are only two – one for rows and one for columns. But you may want to break these up depending on the different types of row and column sets. You want to avoid duplicating identical sets since it will not only potentially confuse the administrators but also increase the maintenance when changes are needed.

A second type of Cube View group will contain those Cube Views that will be presented during the data submission process (versus those that are run once all data is final). These groups are then added to Cube View Profiles where necessary.

Cube View Profiles can be defined as groupings of Cube View groups. You add these to Workflow Profiles to present groups of Cube Views to the user during their data submission process. For example, upon a sub-consolidation, the user wants to review the results for some key reports. Those Cube Views are maintained in one or more Cube View groups, but you add those groups to a Cube View Profile and attach it to the Workflow Profile step. Those Cube Views then appear in the Analysis section during the process (See Figure 10.1). This is why it’s important to know which Cube Views will be run upon completion of the data submission process, and which ones will be presented during the process. I’ll cover this in more detail in the next section: Organizing Groups and Profiles.

Figure 10.1

You’ll also set up non-Cube View components that will aid you in building the Cube Views as dynamically as possible. As mentioned previously, you may want to create a customer-specific maintenance unit (and/or Workspace) that will contain the Cube View parameters that you identified during the report inventory analysis.

Again, two common uses for parameters are the pop-up prompts that the users select when they run the Cube View, plus the formatting text for Cube Views. You can put them both into the same maintenance unit/Workspace or create separate ones for each. Again, it’s important that you’ve already established a logical naming convention for the parameters, the maintenance units, and Workspaces to ease navigation and maintenance.

The next couple of items were presented in the Business Rules Chapter but should be mentioned here as they’ll likely be used extensively throughout the Cube View build. As a refresher, I’m speaking of XFBR and member list business rules. These provide an additional advanced mechanism for Cube View builders to create dynamic Cube Views. For example, take a customer column set that contains two columns – one for the current forecast and one for the prior forecast. The current forecast is anchored on the workflow. The Cube View should know what the prior forecast is, based on the current forecast – in other words, the user shouldn’t have to specify both the current and prior forecasts; OneStream should be able to logically know the prior forecast based on the current forecast selected. This is where the XFBR business rule comes into play. You call the rule from column two and send the current forecast to the rule as a variable. The rule takes that variable (current forecast) and – based on the logic – will return the prior forecast.

In addition to XFBR business rules, you’ll likely use a member list business rule to return the members you want to show up in a Cube View. An example of this would be if you’re writing a Top 20 customers report. Maybe you want to list them out by sales in descending order. You would write the member list rule and, again, call it from the Cube View. It would run the logic in the rule and return your customers in descending order.

The thing to remember about these rules is that they run and return cube dimensionality. They don’t run any data calculations; they tell the Cube View the dimension members from which to pull data, not the data itself. Should you need data calculations, you have a couple of options – Cube View math or UD8 dynamic calculation members. Which one is better, and when do you use each?

Before answering these questions, I’ll define each of them. Cube View math takes one row or column and adds, subtracts, etc., from another row or column. For example, column one contains Actual data, and column two contains Budget data. Your variance (column three) could be set up with Cube View math that subtracts Actual from Budget. An example of it being used in rows might be taking an expense line in the P&L and dividing it by the total sales line to produce a percentage of sales line.

Alternatively, UD8 dynamic calculations can also handle both of the above examples. Most applications ‘reserve’ UD8 to hold these dynamic reporting members. The administrators create these members and write Member Formulas on each. The resulting data calculates every time the member is called in a Cube View rather than storing it in the database. As previously mentioned, dynamic calculations take any dimensions that aren’t found in the Member Formula from the Cube View from which they’re called. Going back to my variance example, you could set up an Actual to Budget variance UD8 dynamic calculation, and the Member Formula would subtract Actual from Budget. You could do the same with the percentage of sales example. You use these UD8 members on their respective rows or columns instead of None.

Back to the initial question, “Which one is better, and when do you use each?” Cube View math is straightforward and easy to read/write in the Cube View – no coding knowledge required! One downside of having to write it into each row or column set is that you could be writing this multiple times depending on the number of templates or Cube Views that require that calculation. This increases the risk that the calculation differs amongst them. It also increases maintenance should the calculation change. Additionally, all pieces of the calculation must exist in the Cube View in order to use Cube View math. I’ve seen many decks and reports that present KPIs without the underlying data from which they’re derived. Using Cube View math to write a report like this would mean adding columns (as this cannot be done with rows) with the data used for the calculation and suppressing them, so they’re hidden on the resulting report. By doing this, you’ve got columns of data that will never be shown but are necessary to provide the resulting calculation. This can confuse administrators and super-users who need to make future modifications to the Cube View. More importantly, it could negatively impact the performance of the rendering of the Cube View.

On the other hand, UD8 members contain Member Formulas, and you just need to add them to any Cube View. This method ensures consistency across all Cube Views using it. The administrators can also add a Member Formula for drilldown that provides end-users with the ability to see each component of the calculation should they drill down on the calculation. There’s always a downside and – here – the administrators need to have some basic coding skills.

I lean towards using dynamic calculations from the start due to the benefits I previously mentioned. However, the use case and customer requirements can drive me to use Cube View math. I make three high-level assessments that help clarify this:

1. Do multiple reports present the calculation?

2. Are all of the components that comprise the calculation also presented in the report?

3. What does the report layout look like?

The first two points make sense, but what do I mean by point three? Take, for example, a report with Actuals in one column and the P&L accounts in the rows. In column two, the customer wants to show the percentage of sales on every row. Cube View math would be difficult in this case because the calculation differs on every row.

A second example (again taking a report with Actuals in one column and the P&L accounts in the rows) might present gross profit percent at the very top. You could use Cube View math or a dynamic calculation since either one easily produces the calculation.

An additional reporting question to ask the customer is if they want to display dimension descriptions on their reports in multiple languages – OneStream calls these culture codes. The maintenance impact requires the administrators to ensure that all members, Cube Views, and any parameters contain descriptions for all enabled cultures. It may also impact business rules or other elements that reference the member description rather than the name. This can be cumbersome but can be done given the requirement. OneStream recommends matching the Windows Regional Settings of the users’ primary computers to what you set up in the OneStream configuration. The OneStream Cloud Team will need to modify the server settings on the application server configuration file for the culture codes to work properly. The culture codes do not apply to any native OneStream menu items, which means that users will still see them in English regardless of the culture code assigned.

To review, the components you will likely use during your Cube View build are Cube View groups, Cube View Profiles, a dashboard maintenance unit (and/or Workspace) that will contain parameters, an XFBR rule, and possibly some dynamic calculation members in UD8 or any other dimensions. You can set up all of these components as you build your Cube Views rather than building them all upfront. They’re just things to keep in mind as you go through your build.

Organizing Groups and Profiles

As I mentioned earlier, Cube Views are organized into Cube View groups and Cube View Profiles. This section goes into more detail about how to think about the organization of the groups and profiles.

The two main drivers of how you set these up are usage and security. Earlier, I mentioned that you should set up one group for your row templates, and a second group for your column templates.

This allows the Cube View administrator and any super-users who will be creating Cube Views to easily find them. This is one example of addressing the usage of the Cube Views in the group – they’re all row or column templates that will be shared among other Cube Views.

Another group should contain the Cube Views that only the administrators will access – again, usage. Additional groups that fall into the usage category are groups that will contain Cube Views used during the data submission process. Depending on your workflow design, you may need more than one group for these Cube Views. It’s also helpful to set up groups that will contain Cube Views in development or old Cube Views that are no longer used.

An additional factor to note is if the Cube View is specific to a particular dashboard or Workspace. The Workspace allows administrators to build sets of dashboards within an isolated environment, allowing developers to segregate and organize solution objects. This eases the migration process between applications. The administrator can extract an entire Workspace and import it into another application with confidence that all objects referenced within it have been included in the migration.

Finally, you should set up a group that contains standard reports that all business units will use once all data has been submitted. It may be helpful to set up additional groups by business unit (if each has a group of super-users who will build Cube Views specifically for their business unit) for data entry forms, for dashboard-specific Cube Views, for foreign entities, and for corporate-only reports.

Regarding security, each of these groups can be secured to grant access (who can view the Cube View) and maintenance (who can modify the Cube View). It’s important to determine those two security groups because Cube Views cannot be secured by individual Cube View, only by Cube View group. For example, you don’t want Business Unit B’s super-users modifying Business Unit A’s Cube Views. You also don’t want any business unit super-users modifying any of the corporate or standard Cube Views. These would all need to be in separate Cube View groups. Another example would be having a group of standard/corporate Cube Views that the customer wants to allow only administrators to manage.

Once your Cube View groups have been set up and contain respective Cube Views, Cube View Profiles bring them together for use throughout the workflow. Maybe Business Unit C only uses the standard reports – you’d set up their profile and add the standard reports group to it. Business Unit A has their own group, but they also need to view the standard reports – you’d set up their profile and add the standard reports group and Business Unit A group to it. You can see that this allows for mixing and matching groups into profiles, so every business unit is able to run the reports specific to them rather than having to navigate through a massive list of Cube Views to find the few that apply to them.

Like groups, security can be applied to profiles. There’s also a visibility property on profiles.

This allows administrators to hide the Cube Views within the profile from different places in OneStream (always visible, never visible, visible only in OnePlace, dashboards, Excel, forms or workflow, or a combination of any of these). This property applies to the entire profile, not specific Cube View groups or Cube Views. You may want to hide all Cube Views on the OnePlace Cube View pane and, in that case, would exclude OnePlace in the visibility property.

Exploring High-Level Advanced Cube View Properties

You have some advanced Cube View properties available to use, if applicable. These don’t necessarily need to be identified prior to the build but will assist all users should you decide to use them. I’ll highlight three of the most useful and common properties.

Navigation links allow users to navigate directly from one Cube View or report to another Cube View, report, or dashboard. For example, the customer wants to start with one of their high-level BU P&Ls, and from the sales line they want to see more detail by UD1 – you can build both Cube Views and create a link so a user can generate the detailed Cube View directly from the high-level Cube View. It’s a way to provide some ‘drill’ capability in a more reporting-friendly way to both licensed and non-licensed users. Again, these are commonly used when users need to quickly jump from one report or dashboard to another.

List parameters in Cube Views are a handy tool to allow users to select from a dropdown menu to enter data that doesn’t relate to any of the metadata. For example, the customer wants the data submitter to control whether a specific rule should run during the calculation of an entity. Perhaps they only want the rule to run prior to reviewing the results of that entity but not every time throughout the process. You need a mechanism to trigger (or flag) the rule to run. Since OneStream only stores data at specific metadata intersections, you’re unable to store text like “run” or “don’t run”. Instead, the data could be a 1 for “run” or a 0 for “don’t run”. This can be confusing and not intuitive for a user to remember, especially in cases where the main data submitter is out of the office and the backup person needs to complete the step. This is where the list parameter comes into play. The administrators can set up a parameter that presents the two options from which the user can select. In the background, the data that’s stored is a 1 or 0 based on the user selection (See Figure 10.2).

Figure 10.2

The last component to keep in mind is Cube View Extender rules that were covered in the Rules Chapter. A number of use cases would include advanced formatting or varying images and moving/removing headers or footers by entity or Cube View.

Basic Build Principles

This section focuses on common Cube View build principles. These aren’t hard and fast rules, just more information for you to help make educated assessments and address your specific business requirements accordingly.

It’s important to determine a standard Cube View anchor. This subject isn’t as heavy as it may sound. Basically, anchoring is determining a common or standard way for your users to run reports. Different customers have different preferences, but if you mix and match anchors among Cube Views, it adds complexity and can introduce confusion for end-users. For example, in Cube View 1, the user knows they need to change their POV to see a specific dataset, while in Cube View 2 the user knows they need to change their Workflow Profile to see a specific dataset. Consistent anchors allow the user to navigate to their dataset regardless of the Cube View they’re running.

The three main anchors are setting the POV to reference the workflow, the user’s individual POV (right-hand pane), or a parameter that produces a pop-up window for the user to make dimension selections at runtime. There are benefits and pitfalls to each one.

Setting the anchor on the workflow allows Cube Views to use it as a reference for scenario, time, and possibly entity. The idea is that because the user selects a workflow to complete their work, the Cube Views they’re running will likely relate to that workflow. By anchoring them on workflow, the users won’t need to select these scenarios, times, or entities because OneStream knows they’re already in the workflow. The downside of this is that if they’re not in a month-end situation, and want to run a Cube View for a past time period, they would first need to change their workflow in order to see data for that particular time. At a minimum, any Cube Views used for data input should be anchored on workflow.

Setting the anchor on the user’s POV allows the user to open their POV pane to select any dimensions for which they want to see data. Yes, it’s simple enough to open that pane to ensure it’s correct when they’re completing their work; however, it can cause confusion if their POV is set to a time period for which they’re not completing their work. So, you go back to anchor all data input forms on workflow. Now you’ve got some Cube Views anchored on workflow (data entry forms) and some Cube Views anchored on POV (reports or schedules), users will need to remember this should they need to go back to a prior period to view a data entry form.

Using parameters to prompt the user for dimensions at runtime is also commonly used. I would not recommend pairing this with anchoring on the POV. As a user running a Cube View with a prompt, I select the dimensions for which I want to see data. If any dimensions are left out of the prompt, I need to now go to my POV to select them. It doesn’t make a lot of sense to require the user to select dimensions via two methods for the same Cube View.

I generally use a combination of anchoring on workflow with runtime parameters. This way, the user avoids ever having to go to their POV to select anything. I’ve found that users pick up navigation and running Cube Views much more quickly than if the customer prefers to use the POV pane.

I shouldn’t need to mention this next subject, but I will for the sake of completeness. Build your Cube Views as dynamically as possible. Granted, there are occasional exceptions where a handful may need to be hardcoded, but document them and ensure that the administrators know exactly which ones will require additional maintenance.

Sometimes, you’ll build a Cube View, and it takes a while to render. The hot dog that rolled off the grill isn’t the only situation where you apply the five-second rule. If a Cube View takes longer than five seconds to render, it’s a good idea to see if you can improve performance by moving some dimensions around, slightly redesigning it, or breaking it into a number of smaller Cube Views and using navigation links to lead the end-user through them.

You’ve heard the term Data Unit several times throughout this book. The Data Unit doesn’t only drive efficiencies in business rules, it also drives efficiency in rendering Cube Views. You want to minimize having Data Unit dimensions in the rows if possible. Challenge customers when you see this during your report analysis – maybe they’ll be open to a slightly different layout: breaking some larger reports into several smaller reports, or using parameters that will return a smaller dataset that still satisfies the reporting requirement. It’s not wrong to have Data Unit dimensions in the rows and, again, if the Cube View runs in under five seconds, you’re fine.

A second culprit of poor Cube View performance is the nesting of multiple dimensions in the rows or columns. Granted, OneStream allows you to do it, and does it well with Cube View paging (for data explorer only). It really depends more on the design of the Cube View and the volume of the dimensions you’re nesting.

If you’re trying to return four nested dimensions of only 20 members, that’s going to return a row set of 160,000 lines. Again, suggest some alternatives to try to minimize that. If the customer insists that the report needs to contain all 160,000 lines, I suggest you challenge them to show it to you. Any dialogue generally presents an opportunity to discuss the requirement and figure out a way to meet it in a different way.

A Cube View follows a very specific priority when it reads the dimensions for which to return data. It looks at the row, then the column, then the Cube View POV, and finally at the user’s POV found in the right-hand POV pane. The minute it finds a particular dimension, it will use it. For example, if you’ve got a UD1 listed in the columns and in the Cube View POV, it will use the UD1 found in the column, not the Cube View POV. Similarly, if an account is specified in the row and column, it will use the account found in the row.

When you have dynamic calculations working in both the rows and the columns, you’ll face a situation at the intersection of the two – so which one wins? Based on my statements above, the row formula – whether it’s a dynamic calculation member or Cube View math – wins. This is where the row and column overrides come into play. The rows and columns contain several override properties by row. Using the row override in the column will use the column formulas for the specified rows. Finally, using the column overrides in the rows will use the specified formulas for the specified columns.

I try to minimize the use of overrides just for the fact that they’re not immediately visible to any administrator; that person would need to specifically look for them on the Overrides tab.

Ultimately, the priority is as follows:

1. Column overrides (found on rows)

2. Row overrides (found on columns)

3. Row Member Filters

4. Column Member Filters

5. Cube View POV Members

6. User’s POV Members (in the POV pane)

Simple conditional formatting is native to the application. Cube View Extender business rules are available for more complex conditional formatting. An example would be changing the logo found on the reports depending on the entity for which the report runs. More information about Cube View Extender rules can be found in the Rules section of the book.

Finally, native OneStream substitution variables allow Cube View builders to make their Cube Views dynamic. A full list of these can be found in the Member Filter Builder on the Variables tab (see Figure 10.3).

Figure 10.3

The radio buttons allow users to quickly find the variable based on POV, workflow (WF), the Global POV (Global), Cube View (CV), Member Filter (MF), and substitution variables unrelated to the above (General). Common uses include headers, footers, and custom names for rows or columns.

Cube View Performance

I recommended a few common remedies for improving Cube View performance, but you’ve also got one last wildcard in the arsenal, just in case you’ve exhausted them: application server configuration. The OneStream Cloud team would need to change these settings.

There are a number of settings that can be changed to optimize the responsiveness of Cube Views (see Figure 10.4). This is across the entire environment, so if there are development, test, and production applications in one environment, it will affect all three applications.

Figure 10.4

Dashboards Overview

OneStream dashboards are multifaceted and go beyond static data analysis. Dashboards provide additional functionality that far exceeds that of a Cube View or spreadsheet report. While those reports are very common in every application, dashboards provide additional reporting layers and functionality.

Dashboards use data adapters to generate higher-volume, custom datasets, and components to display the data and provide controlled user interactions. Parameters help make the overall user experience more dynamic in nature, and the dashboard layout displays everything in a comprehensible and user-friendly format.

In their simplest form, dashboards can house a variety of reports, allowing a user to tab through each one while doing analysis in OnePlace or a workflow. Dashboards are used to help administrators with application management, whether it is displaying audit information, workflow status, or running a series of automated tasks. On a more advanced level, dashboards provide detailed analytics using sophisticated data queries and calculations to drive results.

Given the vast possibilities that dashboards offer, and their ‘choose your own adventure’ capabilities, it is the responsibility of the dashboard designer to truly understand business needs, user requirements, and all contributing factors prior to building. The sections in this chapter highlight the pertinent information one needs when designing a dashboard, the logical way in which a dashboard should be constructed, and vital considerations that occur throughout the design and build.

Determine Dashboard Purpose and Build Items

Dashboards might not always be the first obvious choice when reviewing your reporting options. This is why building a report inventory (as discussed at the beginning of the chapter) is so important. Your inventory should include some detail about the report’s objectives, user interaction, and anticipated outcomes.

Common reporting objectives that make dashboards a good contender:

1. Data location – the required data is stored outside the financial model or in an external database. This could also include multiple data sources where you need to blend different datasets.

2. Multiple reports – this includes taking a series of standalone reports and organizing them into one dashboard or displaying multiple reports in one dashboard view.

3. User Actions – this includes a high level of user interaction, such as filtering and drilling across multiple reports or modifying and calculating data.

Now that you’ve decided to use dashboards as a reporting tool and have an understanding of the dashboard’s objectives, it’s time to start drilling down into the requirements and nitty-gritty details. All dashboards begin with meticulous planning and a detailed blueprint. The more detailed you are, the better your dashboard-building experience will be.

Data Consumption

Your dashboard’s reporting objectives are directly related to data consumption, which is the way in which a user interacts with a dashboard and its data. This is the basis upon which your design and build decisions are made. Based on your report objectives, define the intended audience and what they need to do, view, or understand from the content presented on the dashboard.

Static Analysis

Static reports require minimal (if any) user interaction and have a ‘what you see is what you get’ display. These are ideal for gathering a collection of reports and viewing them in one dashboard, as displayed in Figure 10.5. This could be used to create a financial report package for management analysis or assigned to a workflow task for accessible reporting needs. Users can easily analyze this data and navigate from one report to the next, but they cannot change or manipulate the view.

Figure 10.5

Interactive Analytics

Interactive analytics take static analysis a step further by giving users the ability to slice the underlying dataset into a variety of customized views. For example, the dashboard in Figure 10.6 allows users to modify rows and columns, drill down, and apply filters based on how they want to see the data.

Figure 10.6

The dashboard in Figure 10.7 is another example of interactive analytics where the dashboard dynamically changes views based on user selection. The entity selection drives different Cube View results, and the selected Cube View data cell drives different source detail results.

Figure 10.7

Functional Interaction and Analysis

Functional analytics incorporate interactive analysis with actual business processes and tasks. You can provide a controlled way for users to modify and calculate data, run a series of system tasks, or navigate to other areas in the application. The dashboard in Figure 10.8 is an allocation form that allows the user to control multiple POV members, enter data, calculate it, and see the calculated results.

Figure 10.8

Data Requirements

Now that you have an idea of the kind of dashboard you need, it is time to take a deep dive into your data requirements. When analyzing data requirements, the first thing you need to understand is where the source data is stored. Dashboards can have multiple datasets derived from both internal and external data sources.

Data that is stored in the application or system database is considered internal. This includes data from the cube, the Stage, Analytic Blend, custom SQL tables, a Solution Exchange Solution, and status and audit information.

Figure 10.9

Using data from an external source means the data is not stored in the application and is, therefore, dynamically queried from an external database each time the report is rendered. For example, you may want to use data from an external ERP system where the results directly relate to the application’s data and provide a greater level of detail. By contrast, you may want to look at a more operational dataset stored in a blend database. The results may not directly relate to your financial data but could impact data assumptions or trends and change the way a user interacts with stored financial data.

External data queries require additional setup outside of the application. This includes creating a unique connection string via the database configuration utility and an external database connection via the application server configuration file. Refer to the OneStream Installation and Configuration Guide for more details on the setup of an external database connection.

Using multiple datasets is a typical requirement, and dashboards do not limit you to just one source. You can use data blending to query data from various sources such as Analytic Blend, custom tables, or cube and Stage to do analysis all in one dashboard. Data blending establishes relationships between the datasets to provide a greater level of granularity. For example, the dashboard from Figure 10.7, in the previous section, is displaying two distinct datasets: one from the cube and one from the Stage. The business rule in Figure 10.10, below, displays how the dashboard’s data query was written using a dashboard dataset business rule and then called from a method query data adapter.

Figure 10.10

Dashboard dataset business rules are designed to provide more flexibility by combining SQL and VB.Net or C# into a customized data query, cache the dataset in memory for better performance, and – in some cases – are more user-friendly overwriting a SQL query directly into a SQL data adapter. This is OneStream’s recommended approach for writing custom data queries in dashboards.

Dashboard datasets are used in conjunction with method query data adapters. Method queries provide another layer of flexibility as each predefined method type provides a set of variables and data results. This is helpful when building reports with custom datasets, and the required syntax and results can be tested during the data adapter build. Refer to the OneStream Design and Reference Guide for more details on method query syntax.

Any time you are querying data, internal or external, it is important to understand the data volume and how this could impact overall dashboard performance and usability.

Here are a few things to consider while analyzing the size of the dataset(s):

• How much data do you have in your data query results?

• Will this dataset grow over time?

• Are aggregations or calculations required to get the data results you need?

• Are there data dependencies, and does one dataset drive the results of another?

• Is the environment capable of managing high data volumes, and do you foresee any performance impacts?

This information is crucial to the overall functionality of the dashboard, which is why it should be done before you start building. If you worked ahead and discovered the original plan doesn’t support the data requirements, it’s not too late to go back to the drawing board.

Data Display and Layouts

Now that you’ve thoroughly assessed the data characteristics and how the user must interact with the data, it’s time to plot out how all the pieces will work together.

If you have perused the various areas of a Workspace, dashboard maintenance unit, or have already built a dashboard, you probably noticed there is an extensive collection of components as well as a variety of layouts. As you begin planning out the kind of components to use and how the dashboard’s layout should display these components, decide how each piece of the dashboard influences, modifies or affects another part of the dashboard and how this impacts the data.

Determine the order of operations, the actions involved, and the anticipated results when the action is executed. This not only helps establish the components and layout(s) you need, it also provides some insight into the additional objects you may have to build outside of the dashboard.

If it’s more flexibility you need, you can throw in some parameters and strategically placed business rules and – for a little flair – top it off with images, logos, and color palettes. This is where you and/or the group requesting the dashboard may need some willpower. It is very easy to get caught up in everything a dashboard can do, but that does not mean your dashboard has to do everything.

The dashboard in Figure 10.11 is the functional interactive example from earlier in the chapter. This was designed based on a functional interactive process. Each of the highlighted components control a part of that process and has control over other areas of the dashboard. For example, when a user selects an entity or changes the period in the combo box, the data in both grids update with the chosen entity’s dimensionality and data. The user can then select their allocation drivers, enter data, and process their allocations as needed. In addition to choosing the right components, the way they are presented to a user should complement the objective and easily guide them through each step without errors or performance issues.

Figure 10.11

All dashboards have a specific layout, and like components, there are a variety of dashboard layout types that have different purposes and, for some, different display settings. The layout is the foundation upon which dashboard objects are arranged, and while it tends to formulate near the end of the design phase, it is actually the first thing you should build. Some dashboard layouts will only require minimal construction due to their data and/or user interaction requirements. These are more simplistic in nature because there aren’t any dependencies across each dataset (as displayed in Figure 10.5 previously).

Multi-dashboard layouts require embedding a series of dashboards into one main one to create what essentially looks like a single cohesive dashboard. This kind of layout is common in dashboards that require an abundance of user interactions and, used correctly, can help with overall dashboard performance and tend to be more functional.

Designed and constructed properly, components and layouts can help control or avoid performance issues. However, if they do not support the data requirements or how the actions are arranged, you may run into some issues. When analyzing performance impacts, assess the query execution time, the amount of data loading, and the amount of data rendering. Some components, such as the large pivot grid, are designed for extremely high data volumes. The large pivot grid is used for interactive analytics and can manage millions of data records.

These ‘simplistic’ layout-tabbed dashboards can be easily constructed; however, the amount of data in each tab could cause issues when running a dashboard or when trying to navigate from tab to tab. Each report running independently might have little to no performance issues, but once you begin adding more data per tab, this could result in a slower analysis experience.

These types of performance issues will require some additional thought and design considerations. How can you minimize the number of data refreshes? This may be solved by embedding another dashboard into the main dashboard and specifying which one should be refreshed after a user interaction.

How can you display a large dataset in a dashboard that is meant to be more functional? This may be solved by using a list box component to align with displaying data in rows; however, it only shows slices of data to the user at one time, rather than everything at once.

Keep these benchmarks in mind as you confirm your approach:

1. Administrative maintenance – how much maintenance is required?

2. Usability – can users easily navigate through the dashboard(s), and is it intuitive?

3. Performance – is this the most performant design, and is it consistent with the growth of the dataset? Is there an alternative approach that provides better performance?

4. Presentation – is the content presented in a way that allows users to easily consume it and achieve their reporting/process goals?

These are the things you do not want to sacrifice, nor justifications as to why the dashboard isn’t laid out exactly how it was requested or using a different process to get to the anticipated results.

Dashboard Build Principles

Workspaces were defined earlier, but how do you know if you want to create one specific to the dashboards that you’re building? The benefits are best explained by the OneStream User and Reference Guide:

1. Isolation between Workspaces, which allows developers to work on the same solution or dashboard in a sandbox-like environment.

2. Greater flexibility among developers and other team members when testing, making changes, and planning.

3. Maintenance units, along with their objects, can have the same names in separate Workspaces and do not need to be renamed. This reduces the likelihood of naming conflicts, especially when importing and exporting objects from other applications or sources.

4. You can selectively share Workspace objects such as embedded dashboards, parameters, file resources, and string resources with other Workspaces. This lets you reuse objects rather than copying them.

5. Workspace objects can have the same names in different Workspaces.

6. Sets the foundation for future functionality and ongoing development.

7. Product packaging mechanism for creating, deploying, and migrating solutions.

From there, the main objects for your dashboard will be stored in a dashboard maintenance unit. The dashboard maintenance Unit (DMU) is a toolbox that organizes objects such as data adapters, files, parameters, and components, and which can store multiple dashboard groups and dashboards.

Begin by building the dashboard(s), and their layouts, and embed the dashboards where applicable. Once the layout is complete, begin building the dashboard items in a logical order; assign them to their respective dashboard locations and test them. This ensures each item is behaving as expected, and any issue is identified and resolved before moving on to the next section. The best option for building an embedded dashboard structure and layout is to start with one that already exists, copy it to a new maintenance unit and/or Workspace, and update it accordingly. Even if there are minimal similarities, starting with something of a shell beats the other option, which is starting from scratch.

Establish a universal naming convention. Naming conventions apply to multiple objects in OneStream, and dashboards are no exception. Developing this early on will not only make it easier for those who are building a dashboard but will also make maintenance easier if performed by a different group. The more sophisticated the design and layout, the more defined and consistent a naming convention should be. An effective naming convention will show how each object is related, whether it is assigned, embedded, or referenced in some way, and give a clear path to how any dashboard was built.

Figure 10.12

The naming convention in Figure 10.12 is only an example. Using a simple number/letter system, it is easy to identify the component and its respective dashboard, as well as how the dashboards are embedded. The 0_Frame_BRDF dashboard encompasses everything and can be run to get a complete picture of how the entire dashboard looks and operates. The other dashboard prefixes indicate how these are embedded, making it easy to understand where modifications need to be done. Apply these same conventions to the other dashboard objects, such as components and parameters, to show their relationship to each other and to which dashboard they should be assigned.

In some cases, you might already have established dashboard maintenance units with universal parameters. These can be referenced by other dashboard objects or in other areas of OneStream, but if they relate specifically to one dashboard project, it is best to store them in the respective maintenance unit and have them follow your standard naming convention.

Keep in mind that the DMU does not always store everything associated with dashboards. Items such as data management sequences, Cube Views, and business rules are commonly used with dashboards, but can be stored, maintained, and extracted separately if they weren’t saved in the DMU or Workspace of a particular dashboard. Files used for button images or headers may be stored in the application’s file explorer or another DMU, and parameters might also be stored in another DMU. These add up quickly, which is very important from a maintenance and migration perspective. Include this in the project documentation along with the impacts and/or dependencies one might have on an object in the overall dashboard project.

Conclusion

As you can see, reporting requirements and report design all stem from the data and business processes performed in the application. Developing a report inventory provides clarity in your report build plan, and the more you can identify, the better. Establishing these requirements early on in the project will help prepare you in selecting the best reporting mechanism to use, and the most effective way to meet the needs of the user.

Epilogue (Jacqui Slone)

The Splash User Conference has always been a major focal point at OneStream. It showcases unique talent and expertise, and – year after year – it displays our overall success. The Splash bar is set higher and higher every year, and several OneStream teams work tirelessly to execute a show-stopping event. In the months leading up to the conference, it’s easy to get wrapped up in the stress and chaos of it all. In the blink of an eye, the countdown goes from 12 months to two days, and then the plane lands and it’s go-time. The months and months of preparing for Splash are done, and it’s time to see the finished product in real time with a live audience. Every Splash happens so fast; there isn’t time to stop and think about how tired you are, how much your feet hurt, how your OneStream shirt fits, or even how hungover you might be. Chicago Splash will forever stand out in my mind because – as I walked into the Navy Pier for the keynote – it made me stop and marvel at what OneStream had become. I have always been overwhelmed with gratitude for the OneStreamers who work so hard, and I’m thankful to work with such a great group of people. In that moment, standing in the Navy Pier, I remember thinking, “Look at what we did.”