In an earlier post, I describe how working with OneStream XF reminds me of building complex LEGO models. The application, like LEGO Technic, provides a whole lot of specialized components that can be put together to build fantastic models. A great example of a OneStream XF specialized component is relational blending. This is the Bugatti Chiron of the LEGO Technic series. It really pushes the limits of what this application can do.

In the OneStream XF Design and Reference Guide, relational blending is defined as the ability to blend data stored in a relational database with data stored in the cube. The guide also identifies three types of relational blending:

Drill-back blending (one-to-many relationship) provides access to detailed information that does not exist in the analytic model.

Application blending (one-to-many relationship) leverages the OneStream MarketPlace Specialty Planning and Compliance applications.

Model blending (one-to-one relationship) combines the power of the OneStream XF in-memory analytic engine with the flexibility of relational database storage.

Choosing which type of blending to use depends upon the layout and availability of the data. Typically, we will use relational blending in situations where metadata is changing or if we don’t want to load the metadata into the dimension. Another reason to use relational blending is that the data represents transactions and not balances. Transactions are difficult to capture in cubes but easy to capture in relational tables. OneStream has done a great job identifying the strengths and weaknesses of cube and relational data storage types and has given us a great way to combine them.

Example of drill-back blending (one-to-many relationship)

A great example of one-to-many relationships is drill-through. One data intersection in the cube is made up of multiple records in the source system. OneStream has a built-in drill-through function that allows us to drill back into the source system. However, it also gives us the tools to build reports that can interactively show the “one” and the “many” side by side. This can be done using the OneStream’s powerful dashboards. In a recent implementation, I created exactly such a report, shown below.

Travel expenses by cost center by employee

If you ever traveled for work, you must have also submitted an expense report for reimbursement. Your expenses aggregated into a single amount for the period, which represent a single data point. The data points for all employees in the same cost center are further aggregated to represent another single data point. Let’s use this example to illustrate how Relational Blending can help visualize the data. The following screenshot illustrates an example of expense report details:

Figure 1 – Import Workflow Data

To understand how this works, we must first understand how data is loaded into the OneStream XF cubes. Initially, raw data is imported into the staging area, which is used as a holding place where the data is mapped and transformed so that it will fit the dimensionality of the target cube (OneStream’s analytical database). This staging area is made up of three components: the StageSourceData table (aka “stage”), where raw data is stored; the StageTargetData table (aka “transform”), where the transformed data that is loaded into the cube is stored; and the StageAttributeData table, where additional data that will not be loaded into the cube is stored. The stage table can be seen in the green “Import” chevron at the top left corner of the screen. The transform table can be seen in the “Validate” and “Process” chevrons.

In this example, the stage table shows that Vannucci, Ayowanmi Yaside has nine rows of data, which represent the details that make up two accounts: 6161_000 and 6210_000. The data in the cube will be aggregated and mapped into the two intersections of Cost Center 110, Customer code E_EM0468, and Accounts 6161_000 and 6210_000. Since the detail is stored in the stage table and not in the cube, we can use relational blending to pull it into a dashboard. The dashboard contains two components:

A cube view (on the left) and a grid view (on the right). The cube view shows the data from the cube, while the grid view shows the data from the stage table:

Figure 2 – T&E By Employee Dashboard

The grid view utilizes a data adaptor that pulls data from the stage and attribute tables. It is also linked to the cube view so it can filter the query from the stage table to the cost center and employee selected by the user. This provides a dynamic dashboard that gives a real-time drill-through from an aggregated number in the cube to the detail of the expenses in a relational table.

As the user clicks on a different cell in the cube view, the data in the data adaptor on the right changes:

Figure 3 – Dynamically Updated Data

Stage and transform tables

To create this dashboard, users need to familiarize themselves with the stage and transform tables. Luckily, OneStream provides us with a view table called vStageSourceAndTargetDataWithAttributes. This view table gives us access to the relational table where the import workflow stores the data (see Figure 1):

Figure 4 – vStageSourceAndTargetDataWithAttributes

The view table is made up of four sections:

Key fields. These contain the data unit key identifiers for the record (columns B-D in the above screenshot).

StageSourceData. Also known as the “stage” table, this is what you see in the Import screen on the workflow. It represents the raw data imported from the data source before any transformation and mapping. The columns in this section match the dimensions in the cube except for the Am (Amount), Lb (Label), Si (Source ID), and Tv (Text Value) columns.

StageTargetData. Also known as the “transform” table, this is what you see in the Transform/Load screens on the workflow. It is the transformed data that is loaded into the cube. The columns in this section match the dimensions in the cube, except for the Fs (Flip Sign) column.

StageAttributeData. This is where activated attribute and value fields live. These columns do not line up with the cube dimensions. This is where we can load the additional data that will not be loaded into the cube but will be queried by the SQL to show the details for the single point of data in the cube.

Putting it all together

From this point on, it’s like putting together a LEGO model. Once you know where the data is in the stage table, you can activate the attribute or value dimensions in the cube and map the data to these dimensions in the Data Source. Activating these attributes is done in the Integration tab of the Cube properties:

Figure 5 – Cube Integration Tab

Mapping the data columns to these attribute dimensions is done in the Data Source:

Figure 6 – Data Source

Knowing where the data exists in the stage table allows us to write SQL queries in the dashboard Data Adaptor. The Data Adaptor can then be linked to the Grid View:

Figure 7 – Data Adaptor

Figure 8 – SQL Query

Note that the SQL Query is linked to Parameters in the cube view:

Figure 9 – Cube View

These linked parameters allow the SQL query to retrieve only the data for the selected cost center (Entity dim), month (Time dim), and employee (UD2 dim).

Building a relational blending dashboard is one of the best examples of how OneStream XF is like LEGO. As you have seen, we have to build multiple objects that come together to make a great model. This model, like the Bugatti Chiron, is a work of art on the outside and a complex collection of special parts on the inside.

If you would like more of a ‘how to’ example on relational blending, click here.