Dashboards are an effective way to tell a story with your OneStream data. But what if the story could be even more compelling if you displayed additional data – data that is not loaded into your OneStream cube? Relational Blend dashboards make this possible.
Relational Blend dashboards display a combination (or blend) of data – some from the cube and some from the stage. They are built using the same components, data adapters, parameters, etc. as regular dashboards. The key difference with Relational Blend dashboards is that you need to join the cube data tables with the stage data tables during setup in order to display the blended data.
Consider the following scenario: You have been asked by the finance manager to load accounts-receivable (AR) data into OneStream and create a dashboard that shows each customer’s AR data grouped into the corresponding “bucket” (e.g., 0-30 days, 31-60 days, 61-90 days, etc.).
Since nothing related to AR aging exists in your OneStream environment, you proceed by doing the following:
- Write a query to pull the requested data out of the source system.
- Create a scenario into which you can load the data.
- Use custom UD3 members for each AR bucket.
- Enable a Workflow Profile, Transformation Rule, and Data Source to load the data.
Next, load the data, verify their accuracy, create the dashboard, and notify the finance manager. The result should look similar to this:
The finance manager is thrilled – for about an hour, when the client has a follow-up request. They want to see invoice detail when they click on a total in the dashboard.
Transactional data – such as invoice number, transaction date, payment method, etc. – are generally not loaded into the OneStream cube. You can, however, load this data into the stage tables without impacting the performance of the OneStream cube. Once the data are in the tables, you can leverage them with a Relational Blend dashboard.
Relational Blend Dashboard Creation
To accomplish your goal, you need to create a second dashboard, positioning it in a frame to display below the current dashboard and configuring the two dashboards to work as a team.
Starting where you left off above, this can be accomplished by performing the steps below. (Note: Although additional detail for each step is provided in the following sections, this is not a click-by-click tutorial.)
- Determine necessary changes.
- Configure existing components.
- Load and examine data.
- Create new dashboard.
- Perform final configuration.
- Combine the dashboards.
Determine Necessary Changes
Before you start making changes or writing queries, find out exactly what is expected. Some considerations:
- What additional information would the requestor like to see?
- What additional information is available in the source system?
- What types of data (strings, numbers, dates, etc.) will you be bringing into the stage?
Configure Existing Components
Add Bound Parameters to Your Original Cube View
The dashboard described above is comprised of an embedded Cube View, which needs to be modified so it can communicate with the dashboard you will create below. Remember, the two dashboards need to work as a team. You accomplish this, in part, by adding Bound Parameter names on the Advanced tab:
Enter any values you choose. Just remember that you need exact spellings for the new dashboard to function correctly.
Modify Scenario to Allow Additional Attributes
The scenario you use may or may not be ready to accept the additional data you’re going to load. Using the information gathered during planning, determine how many new items (attributes) you’ll be loading to the stage tables are text and how many are values. You are allowed 20 text attributes and 12 value attributes. Enable the necessary number of each as described below.
- Navigate to the Application tab in OnePlace and select Cubes.
- Click the desired cube; then click the Integration tab.
- Click on your Scenario.
- You have 20 Attribute Dimensions and 12 Attribute Value Dimensions available. Note that they may not be enabled.
- Select them one at a time and ensure that a sufficient number of them set to Enabled = True.
Notice that the labels (Attribute1, Value 1, etc.) are generic. This is a good time to map out which data items will get loaded into each field. For example, Invoice Date = Attribute1, Order Number = Attribute2, Discount Applied = Value1, and so on.
Modify the Data Pull From Your Source System
There are many ways to pull data out of a source system and load it into OneStream. We can’t cover them all here, but in this specific example, the data is being pulled from the source system using a Connector. The source system database is SQL Server, so the underlying T-SQL query (specifically, the SELECT statement) needs to be updated to include the additional attributes that need to be loaded to the stage tables. If they’re loaded through an Excel file, add the required columns to the spreadsheet.
Modify Data Source
Much as in the previous section, the steps needed to accomplish this could vary greatly based on the specific situation. These are the steps for this example:
- Navigate to the Application tab in OnePlace.
- Click on the Data Source used by your workflow and click the Create Source Dimension button.
- Select an attribute from the Name drop-down list and click OK.
- Map the attribute to the location in the data file.
- Repeat for each new attribute.
Load and Examine Data
Execute your workflow to load data into the cube and stage tables. Once complete, examine the data in the database tables to determine how to link them for use in your dashboard.
- Navigate to the System tab and click on Database.
- Expand Application Database.
- Expand Tables.
- Locate the WorkflowProfileHierarchy table.
- Filter the ProfileName column for the workflow profile used to load your data and make note of the ProfileKey value. You will need this later.
- Locate the StageSourceData table.
- Filter the Wfk column on the ProfileKey value you found in Step 6. This is the cube data you loaded through your workflow. (Scroll to the right to see the column headings that match your dimensions.)
- Locate the StageAttributeData.
- Filter the Wfk column on the ProfileKey value used in the previous step. This is the stage data you loaded through your workflow. (Again, scroll to the right to see the column headings that match your newly added attributes.)
- These two tables can be joined on column Ri.
Create New Dashboard
A simple dashboard comprised of a Data Adapter and a BI Viewer component will suffice. If the BI Viewer component is not available in your version of OneStream, use a Grid View component instead. Follow the following steps to create these items:
- Navigate to the Application tab and click on Dashboards.
- Expand the Dashboard Maintenance Unit where the Accounts Receivable dashboard (described above) is stored; select Data Adapters and click the Create Data Adapter button.
- Name the Data Adapter, choose Command Type as SQL, Database Location as Application, and click the Edit button.
- Write an SQL query to pull the desired fields from both the StageSourceData and StageAttributeData tables. Remember to display all of the transactional data requested by the finance manager.
The key things to remember for your query are:
- Join the tables on column Ri.
- Limit your query results to the specific workflow, using a WHERE clause. In this example, the WHERE clause would be:
WHERE StageSourceData.Wfk = ‘087a1ea4-0307-4cae-887d-c3b89559d9aa’
- The WHERE clause also should refer to the Bound Parameter Names created above. In this example, it would be:
WHERE StageSourceData.U3 = |!DrillUD3!| AND StageSourceData.U2 = |!DrillUD2!|
The last point above is very important. Remember that the two dashboards need to work as a team and this part of the query helps make that happen.
BI Viewer Component
- Click on Components and click the Create Dashboard Component button.
- Select BI Viewer and click OK.
- Name the component, click the Data Adapters tab, and click the Add Dashboard Component button.
- Select the data adapter you just created, click OK and save the BI Viewer Component.
Create Dashboard and Embed the BI Viewer Component
- Click on the Dashboard group that contains your AR dashboard and click the Create Dashboard button.
- Name your dashboard, click the Dashboard Components tab, click the Add Dashboard Component button and select your BI Viewer component.
Perform Final Configuration
You now have two dashboards and they are on the brink of being able to work as a team, but there is one last configuration step to make this happen.
The original AR dashboard is comprised of a Cube View component linked to a Cube View, and the component needs to be modified so the dashboards behave as desired when clicked:
- Navigate to the Cube View component for the original dashboard.
- Scroll down to the User Interface Action section.
- Set the Selection Changed User Interface Action to Refresh.
- Set the Dashboards to Redraw to be the new dashboard created as part of this request.
This configuration setting, along with the Bound Parameter Names defined and used above, connect the dashboards and allow them work together.
Combine the Dashboards
As mentioned earlier, the goal is to have your new dashboard display below the original dashboard in the same window. This is accomplished by creating a new dashboard to serve as the “frame” for the other two. Although you already are familiar with creating a dashboard, there are two configuration settings worth noting:
- Format the dashboard with a Layout Type of Grid having two rows (one for each dashboard) and one column so the dashboards fill up the frame.
- Positioning matters. When adding dashboards on the Dashboard Components tab, they appear in the order shown below. The first one will be on top with the second on the bottom. Use the Move Up or Move Down buttons to change the order as needed.
- Launch the dashboard you just created; it should look like this:
As requested, clicking on an AR total in the upper part of the frame displays the invoice detail in the lower part of the frame.
Want to learn more information? Find more information on how to leverage OneStream.