BI Blend Design and

Reference Guide

8.4.0 Release
Copyright © 2024 OneStream Software LLC. All rights reserved.

Any warranty with respect to the software or its functionality will be expressly given in the
Subscription License Agreement or Software License and Services Agreement between
OneStream and the warrantee. This document does not itself constitute a representation or
warranty with respect to the software or any related matter.

OneStream Software, OneStream, Extensible Dimensionality and the OneStream logo are
trademarks of OneStream Software LLC in the United States and other countries. Microsoft,
Microsoft Azure, Microsoft Office, Windows, Windows Server, Excel, .NET Framework, Internet
Information Services, Windows Communication Foundation and SQL Server are registered
trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.
DevExpress is a registered trademark of Developer Express, Inc. Cisco is a registered trademark
of Cisco Systems, Inc. Intel is a trademark of Intel Corporation. AMD64 is a trademark of
Advanced Micro Devices, Inc. Other names may be trademarks of their respective owners.
Table of Contents

Table of Contents
BI Blend Overview 1
Key Elements of BI Blend 1
BI Blend Features 2
BI Blend Stage Cache Engine 2
BI Blend Configuration Overview 4
Workflow and Cube Settings 4
Define Aggregation and Report Points 6
BI Blend Dimension Property Usage 7
Attribute Dimensions 9
Designing BI Blend 13
Blend Unit 13
Examples 13
BI Blend Essential Design Considerations 16
Understand Data Records 16
Understanding BI Blend Aggregation 16
BI Blend Processing and Performance 17
Default Server Selection 18
BI Blend Database Table Creation and Structure 19
OneStream Application Database Tables 19

BI Blend Design and Reference Guide i

Table of Contents

BI Blend Database Tables 20

BI Blend and Cube Settings 20
Cube Properties 20
Cube Dimensions 21
Cube References 21
Data Access 22
Integration 22
BI Blend Processing Example 23
Import Blend Data 23
Blend Steps 26
Validating Members 27
BI Blend Status 28
BI Blend Processing Logs 29
Basic Log File Parsing 29
Technical Overview 31
BI Blend Workflow Settings 31
BI Blend Settings 34
Leveled Hierarchy 40
Leveled Hierarchy Processing 41
Star-Schema Leveling Column Fields 41
Setting IsBIBlendBase 43

BI Blend Design and Reference Guide ii

Table of Contents

BI Blend Application Setup 47

Set Up Workflow for BI Blend 47
Calculations 50
BI Blend Derivative Rules 50
Time-Aware Derivative Logical Operators 51
Logical Operator Definitions 53
Logical Operators Usage 65
Common Error Messaging 67
Reporting Components 68
Dashboard Adapter – BIBlendInfo Method Query 68
Large Pivot Grid 69
BI Blend Data Adapter 69
Fdx Specialty Connector BRAPI’s 70
Using Fdx Connectors 71
Server Roles 72
Optional Application Server 72
Batch Processing Support 72

BI Blend Design and Reference Guide iii

BI Blend Overview

BI Blend Overview
BI Blend is a “read-only” aggregate storage model designed to support reporting on large volumes
of data that is not appropriate to store in a traditional OneStream Cube. BI Blend data is large in
volume and most often transactional in nature. As an example, to analyze data by invoice, a
standard cube would require metadata to store the data records. In a short period of time, most all
the invoice metadata would be unneeded because of the transactional nature of the data.
Therefore, storage in a Cube design is not a best practice solution for transactional data.

A key challenge to report on Transactional data is to present it in a uniform format supporting

standardized reporting yet be flexible enough to support ever changing records and reporting
requirements. The overall large size of the data sets requires a model suitable for responsive
reporting and analysis.

BI Blend is a solution that approaches these challenges in a unique and innovative way. It is a
solution that rationalizes the source data for uniform and standardized reporting, much like the
Standard OneStream Cube models, but stores the data in a new relational column store table for
responsive reporting.

The BI Blend solution is intended to support analytics on large volumes of highly changing data,
such as ERP system transaction data, which typically would not reside in a OneStream Cube.
The processing is unencumbered from the intensive audit controls within a traditional
Consolidation Cube, such as managing Calculation Status.

Key Elements of BI Blend

l Flexible for change

l Fast Aggregation (through data as Stored Relational Aggregation)

l Single Reporting Currency translation

l Leveraged OneStream Metadata, Reporting and Integration tools

l Non-Cube, executed to a relational table optimized for reporting on large data sets by
storing results in a column store index

BI Blend Design and Reference Guide 1

BI Blend Overview

BI Blend Features
BI Blend is a blend of Multidimensional and Transactional Data. This is done by utilizing the
OneStream platform application to generate a database structure for OLAP reporting. By utilizing
the existing OneStream Workflow processes, users need to be familiar with the interface and tools
required to develop BI Blend reporting solutions.

BI Blend provides focused reporting tables that are aggregated and saved as stored parent
intersections for fast reporting at a later point in time. BI Blend does note replicate an entire cube,
but rather focuses on specific reporting use cases that result in many parent intersections that
would not perform well under Calc-On-Fly aggregation.

BI Blend also solves for use cases that are not pure analytic reporting problems. By leveraging
OneStream hierarchies along with BI Blend configuration settings, it is possible to aggregate on a
few dimensions (entity or account for example) while including transaction information (Invoice
number) that is not associated with a cube. The ability to combine the dimensional structure with
transaction details allows for selective enrichment of transactional data.

BI Blend is designed to support analytic models where some level of aggregation may be
required, but contains constantly changing information that should not be loaded to a cube. This
includes the following items:

l Read only access data

l Very large data sets that are transactional in nature

l Fast aggregation as stored relational aggregation

l Flexible and changing records

l Data related to cube data

l Data populated and rebuilt on demand

l Ancillary and supplemental data

BI Blend Stage Cache Engine

BI Blend uses in-memory processing and does not write-records to stage tables. All results are
output to the designated target database. This engine is used to rationalize the transactional data
by leveraging the OneStream Metadata Engine. During processing the BI Blend Cache will utilize
the Cube dimensions to transform the records into a unified reporting format. The Cube
Hierarchies are used to derive aggregation points to be stored for reporting.

BI Blend Design and Reference Guide 2

BI Blend Overview

The BI Blend Stage Cache will also accept properties on Entities and Accounts to perform direct
method translation to the application reporting currency. The Engine ultimately generates a
finalized table with a Column Store Index, creating a structure for OLAP reporting. The table can
be accessed with the standard OneStream BI Dashboard and Pivot Grid reporting tools for
analytic reporting as well as Relational Blending into Cube Views and Dashboards.

The BI Blend Stage Cache Engine supports:

l Hierarchy Aggregation without complex calculations

l Simple currency translation using the Direct Method only. Any destination currency can be
defined, but only one per BI Blend process.

l Limited use of member properties

l Does not use dimension relationship properties such as Aggregation Weight, Percent
Consolidate, or Percent Ownership

l Limited parent level calculations using derivative rules

l Basic time math using helper rules

l Supports simultaneous multi-period data loads by record for up to 12 periods using

Attribute Value dimension Members

The Stage Engine integrates transactional data. Source data is processed through the standard
Stage Engine, where the data uses the common OneStream tools to parse and transform records.
BI Blend source data contains additional members that are defined in the integration as Attribute
members. Stage Derivative Rules enhance the data with groupings available in reporting.

BI Blend Design and Reference Guide 3

BI Blend Configuration Overview

BI Blend Configuration Overview

BI Blend is designed around all the common elements used in all OneStream applications. It does
not require additional training. Any users familiar with setup, Workflow and Reporting will be
comfortable using BI Bend as it utilized all the functionality around:

l Data Integration

l Metadata

l DataSources

l Transformation Rules

l Derivative Rules

l Workflows

l Reporting tools and Dashboards

Workflow and Cube Settings

The Cube Dimensions assigned to the Cube are used by the BI Blend Stage Cache Engine to
define the target for transforming records. The assigned Cube Dimension’s hierarchies will
generate the aggregation points for the resulting BI Blend Relational Table. This is a primary
difference between the BI Blend Model and a Standard OneStream Cube Model. Within each
Data Unit, the parent levels, such as an Account parent, are derived dynamically in the Standard
OneStream Cube Model. The BI Blend Model will derive these totals and save them as stored
values in the output relational table.

The interface to the BI Blend Model is the OneStream Workflow interface. Workflow is a key
element of BI Blend. Workflow BI Blend settings establish the Cube Dimensions that are used to
derive the metadata and aggregation points in the resulting BI Blend Relational Tables.

The data integration used by the BI Blend Engine uses dimensionality based on cube dimensions,
stage attributes, and BI Blend extended dimensions. This array of properties, which is available
for data integration, lets the designer enrich the source data for reporting.

BI Blend Design and Reference Guide 4

BI Blend Configuration Overview

BI Blend extended dimensions are a component of the BI Blend engine Blend X extended
architecture which consists of three dimensions that provide an additional 57 fields to capture text,
numeric values, and time. The Blend X extended architecture uses the Attribute DateTime
dimension to deliver time-awareness to the data set which is then used by calculations to enrich
data for reporting:

l 13 cube dimensions let transformation rules align source records to cube data and to
generate aggregation points based on the dimension hierarchies.

l 20 stage text attributes text records support cube dimension aggregations when the
records are aligned with metadata in a User Defined 8 dimension. They do not support the
use of transformation rules.

l 12 stage value attributes support numeric records.

l 3 label dimensions are text-based fields. The SourceID field is the key record attribute used
to partition the records for multi-threading aggregations and for the processing of derivative
rules.

l 13 Blend X Attribute DateTime Dimension fields are designed to capture time-based fields.
The xBlendDateTime field usage is extended to function as the time-based property in
Derivative Rule computational math functions.

l 20 Blend X text attributes expand the scope of the data set.

l 24 Blend X value attributes capture numeric data or time-series values in a matrix load
format.

In the below example, the Integration settings on the cube are enabled for Attribute1 and
Attribute2 to expand the details to be collected for BI Blend analytic reporting, such as and Invoice
Number and Customer Code.

BI Blend Design and Reference Guide 5

BI Blend Configuration Overview

Define Aggregation and Report Points

The BI Blend Engine can leverage the hierarchical structure of OneStream dimension metadata
hierarchies to generate aggregations. Hierarchy parent levels are created as stored records in the
resulting BI Blend tables, adding to the final number of records generated. BI Blend records
processed will use transformation rules targeting a cube’s assigned dimension.

BI Blend Design and Reference Guide 6

BI Blend Configuration Overview

The dimensions assigned to the integration used by BI Blend, by Scenario Type for example,
must contain the base member targets defined in the transformation rules. Therefore, the
resulting BI Blend aggregation can differ from that used in other reporting cubes. A dimension and
its hierarchy can be unique for BI Blend reporting.

BI Blend requires a cube to determine assigned dimensions. Dimensions by Scenario Type or a

specialty cube functioning as a dimension outline can be used to yield alternate reporting results
that may differ from standard application cubes.

The BI Blend Stage Cache Engine functionality supports:

l Hierarchy Aggregation, no complex calculations

l Simple currency translation using the Direct Method only. Any destination currency can be
defined, but only one per BI Blend process.

l Limited use of Member Properties

l Does not utilize dimension Relationship Properties such as Aggregation Weight, Percent
Consolidate or Percent Ownership.

l Limited parent level calculations using Derivative Rules

l Basic Time math using Helper Rules

l Supports simultaneous multi-period data loads by record for up to 12 periods using

Attribute Value Dimension Members

BI Blend Dimension Property Usage

BI Blend Design and Reference Guide 7

BI Blend Configuration Overview

l Scenario Properties
o The Scenario is defined through the workflow point of view
o Workflow Tracking Frequency
o Input Frequency

l Time
o Time defined in the BI Blend table name is driven off the workflow tracking frequency
or based on the Time defined in the source file. Time can be mapped as a matrix load
to the attribute value attributes or the Blend X value attributes.

l Entity
o Currency is referenced for simple Translation
o No other Member or Relationship Properties are used
o Aggregation weights are not used and will double count alternate hierarchies
o BI Blend does not utilize Entity Relationship Properties. Therefore, Entities as a
shared member is not supported.

l Account
o Account Types is used for hierarchical aggregation and translation rates

l Flow
o Aggregation weights are not used and will double count alternate hierarchies
o Does not recognize Flow Members impact of Switch Type on Account Types
o Does not recognize Flow Members impact of Switch Sign
o Does not support complex currency calculations or alternate input currencies
o No other Member or Relationship Properties are used

l User Defined

BI Blend Design and Reference Guide 8

BI Blend Configuration Overview

o Does not support data stored as User Defined Attribute Members

o Aggregation weights are not used and will double count alternate hierarchies
o No other Member or Relationship Properties are used

l User Defined 8
o The UD8 Dimension may contain members, organized in hierarchies, which are used
to aggregate Attribute Members contained in the data records.

Other Dimensions
l Consolidation: Is limited to functionality for Local and Translation. The results for
Translation will be limited to a single target reporting currency per BI Blend process.
Complex translation is not supported and only the Direct Method is used for all Account
Types.

l Origin: The Origin member is only supported for data generated through the Workflow BI
Blend Engine as the Import member

l View: View is not supported in BI Blend and must be derived in reporting tools or time math
business rules. The View dimension is not available or managed in BI Blend. Accumulating
results, for YTD reporting, must be done in the reporting tools as a calculation or performed
using business rules. The output results of BI Blend include an identifying column
identifying the account type to identify flow and balance type accounts.

l ICP: ICP Partner detail can be included in the data records, but Eliminations are not
performed.

Attribute Dimensions
External Cube information is collected using the Attribute Dimensions by enabling the fields on the
Cube Integration Settings.

A major feature of BI Blend is the ability to utilize Attributes as an element of aggregations. The
Attribute Dimensions can be used to contain data record elements, such as “invoice” detail. The
Attribute Value is used in solutions to improve performance when loading large data sets for
multiple periods (up to 12). In these designs, each Attribute Value is associated with a base
period.

Attribute Value dimensions cannot be assigned static values when used with BI Blend workflows.

BI Blend Design and Reference Guide 9

BI Blend Configuration Overview

Attribute Text Dimension

The Cube Integration settings contains 20 Attribute Text Dimensions available for data
integration. By associating the imported members of an Attribute Text Dimension to a UD8
metadata hierarchy, BI Blend can generate the aggregated parent level results for reporting. You
can manage Attribute Dimension in the following ways:

l Attributes as records can be “aggregated” within a Pivot Grid as a reporting tool feature.

l Aggregations of Attributes can be calculated using BI Blend Derivative Rules.

l Attributes can be associated with a base member UD8 metadata members and aggregated
as a member of a hierarchy.

Associating an Attribute with a UD8 base member requires the Attribute Dimension Record exists
as a base member in a UD8 Dimension. Only UD8 can be used to generate Attribute Dimension
Members for aggregation. Because the record must be reflected as a dimension member,
Aggregation Attributes may not be appropriate for highly transactional, changing, record member.

BI Blend Design and Reference Guide 10

BI Blend Configuration Overview

Attribute Value Dimension

Cube data integration provides 12 Attribute Value dimension members to capture numeric values.
These fields capture Time field values for matrix data files.

Blend X Attribute DateTime Dimension

The Attribute DateTime dimension members are unique to BI Blend data sources and cannot be
used on non-Blend workflow types. These fields are designed to capture Time based fields and
have the extended capability to function as time values in derivative rule functions.

To use the DateTime dimension, the data source must have the integrated dimension set as a
Blend DateTime data type. The formatting as mm/dd/yy hh:mm is required to support the time-
based derivative rule functions.

BI Blend Design and Reference Guide 11

BI Blend Configuration Overview

Settings include:

l xBlendDateTime: This is a special column field used by the BI Blend engine to serve
derivative calculations for DataSeries/Time value computations.

l xBlendTime: Twelve time column fields to collect additional time calculations that can
support date difference calculations.

Blend X Attribute Text Dimension

Twenty additional text column fields are unique to Blend workflows. These fields are not
supported in other non-Blend workflows. Additionally, the Blend X Attribute Text fields do not
support extending the records to UD8 for parent level aggregations.

Blend X Attribute Value Dimension

Twenty-four additional value column fields are unique to Blend workflows and are used to capture
numeric values. These fields are not supported in other non-Blend workflows. The Attribute Value
fields can be used to map time to create a matrix load. The combination of Attribute Value and
Blend X Attribute Value fields provides a maximum time-based matrix load of data at 36 periods.

BI Blend Design and Reference Guide 12

Designing BI Blend

Designing BI Blend
Designing the BI Blend Workflow definitions should be determined by understanding how the data
will be consumed and analyzed in reporting. The common use of BI Blend will fall into
requirements within the models of Transaction Analysis and External Data. However, it can be
considered to support an Aggregation model where the BI Blend aggregation processing may be
a preferable solution of the standard Cube design.

Blend Unit
Determining which dimension should be defined as the Blend Unit is a key decision which will
affect the performance of the BI Blend process and the reporting results.

The Blend Unit acts to define the dimension which used to break down the data into effective
pages for processing, or partitions. Blend Unit’s partition used to process the aggregations
defined in the BI Blend settings, such as Account or UD aggregations.

Each Blend Unit member’s aggregations are executed as part of a Multi-Threading process. The
selection of the Dimension as the Blend Unit can impact the performance of the application. The
larger the number of Blend Unit pages, the more opportunity there is for multi-threading tasks to
be initiated.

Examples

l Entity Structure as three members requires all aggregation to happen within only three
members.

BI Blend Design and Reference Guide 13

Designing BI Blend

l Assigning the larger Dimension as the Blend Unit, such as UD3 (200 members) as the
Blend Unit, would allow multi-threading to process more aggregations on smaller datasets.

l Larger Blend Unit members enhance performance through a more even distribution of
records.

l When a Blend Unit page completes the aggregation process the engine loops over the rows
on the page and summarizes any duplicate rows, but not with duplicates created in another
page.

l Derivative rules run on each page, for a single row, in an exclusive manner for that page
and do not cross pages.

l Many members in blend unit means more smaller pages which leads to better memory
management, faster aggregation performance and more parallel processing.

Performance Settings
BI Blend processing is a CPU and Memory intensive process. The number of table records is
heavily impacted by the Attribute details in the records and level of aggregations defined for Cube
Dimensions. In each BI Blend configuration setting, the performance can be tailored to the
environment with the Performance Controls properties.

l Max Degree of Parallelism (No SQL): Defaults to 8 processors

l Max Degree of Parallelism (SQL): Defaults to 4 processors

l Row Limit: Sets a maximum row limit to return to control potential server queries

l Application Servers: Allows a named server to be dedicated for BI Blend processing.

Blend Unit Partitioning

The concept of partitioning a Blend Unit is a performance solution for large data sets aggregating
within a Blend Unit. This permits multithreading of the aggregations within the Blend Unit.

BI Blend Design and Reference Guide 14

Designing BI Blend

Blend Unit Partitioning is accomplished by assigning the Data Source “Source ID” property to a
record field. The members within this field will be used as the key to partition aggregations within
the Blend Unit page. The resulting records will be not be summarized across the Source ID /
Blend Unit partition. Important to note, is the use of Derivative Rules in BI Blend function within a
partition and cannot not reference data found in other partitions. If required, the summarization
will need to be performed in the database or in the reporting layer.

BI Blend’s assignment of a Blend Unit and aggregation generates “pages”, which are visible in the
Log File. Each “Blend Unit Page” cannot exceed 2gb. If the 2gb size is exceeded, the
aggregation will fail. The error message “Error Array Dimensions Exceeded Supported Range”
will be presented. A solution if impacted by size constraints is to increase the size of the Blend
Unit Dimension being used or reduce the number of Dimensions used in BI Blend.

BI Blend Design and Reference Guide 15

Designing BI Blend

BI Blend Essential Design Considerations

Understand Data Records
l Any member not within the data record aggregation path will be bypassed. This means that
a datasource can contain a complete set of records. BI Blend can then bypass those
records by “filtering” or selecting an aggregation point that excludes those record sets.

l Attribute Members can be aggregated by being associated as a base member in a UD8

hierarchy. If the source record Attribute does not find a base target UD8, no error message
is presented, and the record is ignored. This allows the data set to be flexed easily to adapt
to reporting changes. Users should review the messaging for by passed Aggregations. If
the member is not within the aggregation path it will be ignored.

Use of Common Members

Common Members reference metadata designs which have the same member names across
dimensions. An example of Common Members is where Dimensions such as UD2 and UD3 both
have members called “Top” with children as “None”.

l Caution when using common members (Top / None) across dimensions as the common
members may cause inconsistent results.

l If fully summarized intersections are required, the designer should consider selecting
another Blend Unit.

l Limiting a Blend Unit to a member may not be optimal for BI Blend processing but will yield
fully aggregated dimension results.

l If duplicate records are encountered, accept the duplicates but use aggregation queries
when you consume or query the BI Blend Table (Group By on dimensions while performing
a sum on Measures).

l Unique top members across dimensions are preferred, change dimension aggregation
information to pick a parent that does not include common members, such as None or Top.

Understanding BI Blend Aggregation

Aggregation is a simple aggregation based on the Cube Dimension hierarchy. Additional
aggregation points for non-cube / attribute members can be included in the results using
Derivative Transformation Rules.

BI Blend Design and Reference Guide 16

Designing BI Blend

The BI Blend Engine aggregation utilizes the Cube Dimension hierarchies. This is done by
evaluating parent members and identifying all Base members within the hierarchy. These base
members are aggregated to the parent. Each parent within the hierarchy is evaluated using the
same methodology. BI Blend processing has no concept of “sub-parent” rollups. Each parent is
evaluated and aggregated according to the base members within its hierarchy.

Entity properties such as “Percent Consolidate” and other Dimension’s “Aggregation Weight” are
not used in the BI Blend processing. The aggregation is derived strictly from each Parent as a
sum of its base members. Therefore, duplicate members within a hierarchy (shared members),
should be avoided to eliminate “double-counting” of results.

BI Blend supports changing the “Blend Unit” from Entity as the “page dimension”, to any other
dimension in the Cube. This requires that Currency translation to be defined as a simple
translation based on the Cube’s default currency, not the Parent Entity’s currency property. This
allows the correct results when the Entity is not the Blend Unit, by providing a common currency
throughout the aggregation levels to yield correct results.

Each defined aggregation is stored in a cache, and each can be calculated independently.

BI Blend Processing and Performance

The requirements related to the BI Blend environment will vary widely by the volume of source
records together with the BI Blend Settings definition.

BI Blend Design and Reference Guide 17

Designing BI Blend

Default Server Selection

BI Blend processes will be queued across the available Stage servers. On the BI Blend WorkFlow
Settings, a defined server can be assigned to dedicate all BI Blend processing.

Learning Mode
Learning Mode occurs during the first process instance of BI Blend and the design related to the
choice of Blend Unit. This mode restricts multi-threading to two threads by two Blend Units to
generate predictive statistics based on the number of records generated from the BI Blend
settings. Subsequent processes will be optimized to multi-thread each Blend Unit. Should the
Blend Unit be changed, the Learning Mode again be enabled. Additionally, if the number of
aggregating dimensions is increased from the prior settings, the Learning Mode will again be
enabled.

l Only two threads run

l Default mode when BI Blend Task is run for the first time

l Limited multi-threading is done to help ensure free memory is not exceeded

Log File Statistics

l Blend Unit

l Base Rows

l Parent Factorial

l Explosion Factor

Second Pass Processing

After successful learning mode, the same thread can be evaluated. BI Bend processing will
observe the current number of rows in each Page Dimension (Blend Unit) and apply the Explosion
Factor to determine if the process will exceed the amount of free memory available on the server.

l If a new Blend Unit is added to the file, the calculation estimations for logging and memory
usage will use an average across the Page member statistics in its calculations.

BI Blend Design and Reference Guide 18

Designing BI Blend

BI Blend Database Table Creation and

Structure
The output of BI Blend is an SQL Database table in a Column Store Index format. These are read
only fact tables, optimized for reporting by having a high level of compression. The data is highly
structured, containing the parent member values, much like Cubes. These tables do not need to
be pre-defined or configured. The BI Blend process will create the data tables and corresponding
error table automatically.

OneStream Application Database Tables

A database table captures the activity related to BI Blend tasks. This table has an associated
MethodQuery in Dashboard DataAdapter as BIBlendInfo.

l StageBlendInformation table is a table generated in the OneStream application database

tables to manage the tables generated through the BI Blend Workflow process

BI Blend Design and Reference Guide 19

Designing BI Blend

BI Blend Database Tables

The BI Blend assigned database will have tables created for each BI Blend Workflow Task by
Workflow Tracking Frequency. Each time a table is created, a matching “error” table is created.
The error table will be created whether or not an error was present.

l BI Blend Table - Each BI Blend task will create a table as:

o Prefix – “BIB_”
o Application Name
o Workflow Channel Name
o Workflow Scenario
o Workflow Time

For example:

l BI Blend Error Table – Each BI Blend table will have a corresponding error table:
o Suffix – “_ME”

BI Blend and Cube Settings

A key element of the BI Blend solution is to rationalize data for BI Reporting. The BI Blend
Reporting solutions must be tied to a Workflow and the assigned Cube.

Cube Properties
Currency translation is primarily controlled by the Cube Properties. Rule Type is not used, all
translations use the Direct Method. The rate is determined by Account Type.

l RateType for Revenues and Expenses – rate used by Account Type Property

l RateType for Assets and Liabilities – rate used by Account Type Property

BI Blend Design and Reference Guide 20

Designing BI Blend

Cube Dimensions
The Cube Dimensions are key to BI Blend results. The assigned Dimensions, by Scenario Type,
are used to rationalize the data for reporting and derive the reporting subtotals.

Cube References
Cube References, and the associated Extensibility they manage, are fully supported by the BI
Blend Engine. Extensible Dimension hierarchies will be used if BI Blend is based on a top-level
Cube.

BI Blend Design and Reference Guide 21

Designing BI Blend

Cube References, and the associated Extensibility related to extended Cubes and Dimensions,
have limited support when BI-Blend loads are performed using a top level cube to load to
extended cubes. Use of a top level cube to load to Extended Cubes and Dimension only supports
two levels of Extensibility. The first being the Cubes Main dimension and the second being the
next level extended dimension.

Data Access
Data Access and other data cell level controls are not supported in BI Blend.

Integration
The Integration for the Cube provides the Dimensionality available to the BI Blend Engine. The
active Dimensions can be used in both the Transformed and Un-Transformed states just like all
Data Integration processes in the standard Stage Processing. The activation of the additional 20
Attribute Dimension members can be activated to support the inclusion of non-cube data in the BI
Blend output. The Attribute Value Dimension member can be activated and used in BI Blend to
improve performance when loading large volumes of records containing many time periods by
allowing all time to be associated by record.

BI Blend Design and Reference Guide 22

Designing BI Blend

BI Blend Processing Example

Processing BI Blend used the standard Workflow environment and tools to provide the users with
familiar environment and eliminates the need for any additional training.

Import Blend Data

The Workflow has a new Workflow Name as Blend or Blend as Workspace. You load data the
same as other OneStream Workflows that commonly result in Cube data.

The configuration of BI Blend is managed in the Workflow Profile. However, some of the Stage
Engine's Workflow Properties may not be valid, or used in BI Blend configurations. The primary
configuration for BI Blend is done using the Workflow Name and BI Blend Settings. The BI Blend
Engine's architecture, such as in-memory processing, makes some Workflow properties, such as
the Append Default Load Method, an invalid selection. Such selections will not have an effect on
Workflow Blend behaviors.

BI Blend Design and Reference Guide 23

Designing BI Blend

Data can be collected from:

l Files (Fixed or Delimited)

l Connectors

l Other Workflow Stage Data

Delimited Files
When loading data to a BI Blend workflow using a delimited file, a bypass must be used if the first
row of the file contains column headers. See the example in the image below. If the bypass was
not used in this case, the file would fail to load and would display an error similar to that in the
second screen shot.

BI Blend Design and Reference Guide 24

Designing BI Blend

How to Use a File Bypass

Use a bypass to prevent file load failures in cases where a delimited file contains column headers.

BI Blend Design and Reference Guide 25

Designing BI Blend

1. Create a bypass:

a. Navigate to Application tab > Data Collection > Data Sources.

b. Under Delimited Files, select a data source.

c. Click Create Source Dimension.

d. From the Name drop-down menu, select Bypass.

e. Click OK. The default Bypass Type is Contains Within Line.

f. In the Bypass Value field, type the name of the first column header.

g. Click Save.

2. Load the delimited file into a BI Blend workflow.

Blend Steps

Data is Queued for Processing on a Data

Management Server

Cube Dimensions are integrated

BI Blend Design and Reference Guide 26

Designing BI Blend

Transformation Rules are executed

Multithreading of Page Dimensions or “Blend Units”

begins

Write to target table

Validating Members
BI Blend is processing the records in relation to the cube dimensions and the assigned
transformation rules to create a common reporting solution. Should any member in a record not
find a target in the cube dimension through transformation rules, a mapping error will be
presented. Validation is limited to target mapping. Validation of data intersections is not
performed, such as those from constraint properties.

BI Blend Design and Reference Guide 27

Designing BI Blend

BI Blend Status
The BI Blend Status page displays the information for the current Blend task. This helps to
indicate what results are available. Should there be a failure in the Blend task, the correction must
be performed, and the entire task must be re-executed.

l Execution Status: Displays the results of the most current Blend task noting information
such as target table name and time. If errors were encountered, the “Reload” status will be
displayed to indicate the Blend task must be re-executed.

l Table Status: This status displays the state of current table to denote the last time the table
was updated and its size.

BI Blend Design and Reference Guide 28

Designing BI Blend

BI Blend Processing Logs

Upon the successful completion of a Blend task, statistics and information are written to the log
file.

Basic Log File Parsing

The Log File is intended to provide statistics to manage the Blend task. The source file can identify
data records. BI Blend generates records not only by the base member, but also on the parent
level hierarchies found in the associated workflow/cube dimensions. The Log File gives insight to
this structure to estimate the processing needed on the current and potential future files being
processed.

l Blend Unit: is the identified dimension used as a partition, generating page records.

l Base Page Rows: the number of data record rows for the Page member.

l Parent Factorial: total potential data cell intersections available for population at parent
level.

BI Blend Design and Reference Guide 29

Designing BI Blend

l Explosion Factor: the number of data record cells generated by the base rows across the
Dimensions and parent levels.

Task Activity

A new Task Activity status has been added to monitor BI Blend processes called BI Blend Load
and Transform. This tracking breaks out the various processing steps for analysis.

BI Blend Design and Reference Guide 30

Designing BI Blend

Technical Overview
BI Blend Workflow Settings
The primary design element of BI Blend are the BI Blend Settings found on the Workflow Import
Channel by Scenario Type. These settings allow the BI Blend administrator to define and optimize
the generation BI Blend tables to meet the reporting requirements.

BI Blend Design and Reference Guide 31

Designing BI Blend

BI Blend Design and Reference Guide 32

Designing BI Blend

Setting Function

Measure Type Defines how the time dimension column will be

determined in the relational tables.

Content Type Controls the type and level of detail displayed in the
relational tables for base level records only (analysis of
source against aggregated parents is not supported).
The designer controls how the BI Blend tables utilizes
dimension hierarchies and the inclusion of Attributes
related to the Cube Integration Settings.

Create Star Schema (Optional) Creates the output as a Star Schema table set and
related Views. Supporting Dimension tables will be
created for each Cube Dimension assigned an
Aggregation Control. The Dimension tables will contain
column fields for MemberName, MemberDesc,
NameAndDesc, ParentName, IndentLevel,
IsBaseand MemberSeq, which can be used in
reporting against the Star Schema view. This is an
optional setting.

DB Location Sets the name of the SQL Database that holds the BI
Blend results tables.

Explosion Adjustor Estimates the size of tables generated, as derived from

the initial Learning Mode.

BI Blend Design and Reference Guide 33

Designing BI Blend

Setting Function

Column Alias Defines custom column names for the output database
table. Spaces in the names are not
recommended. Use underscores to represent spaces.

Translate Enables translation to a single destination currency.

Blend Unit Token Token to assign a Dimension to partition as a Page

Dimension and the basis of the BI Blend multi-
threading.

Dimension Aggregation Parent member point to set top level aggregation

member. Can be set as a parent level, children, list,
distinct member or no aggregation.

Max Degree Non SQL Parallelism The default setting is 8.

Max Degree SQL Parallelism The default setting is 4.

Row Limit Estimated by analyzing the Blend Log File, base level
rows, and the Explosion factor to determine free
memory usage.

Shrink After Finalize Compacts the BI Blend database upon table

finalization. The default setting is “True”.

Application Servers The BI Blend processes default to the Stage Servers,

with queuing controls. This can be overridden with
named servers.

BI Blend Settings
Measure Type - Determines how time will be assigned in the BI Blend Table. Time is always
generated as a database column based on the file contents or Workflow settings.

BI Blend Design and Reference Guide 34

Designing BI Blend

l TimeSource – Create Time columns by the time referenced from members found as
records in the source file.

l TimeWFView – Time columns are generated from the Workflow Tracking frequency. As an
example, a yearly tracking frequency for a monthly input scenario would create 12 time
columns regardless of the number of periods in the data records. This option is used for
Time Math helper rules.

l TimeWFViewAV – This type uses the Attribute Value Dimension. This solution requires
time based value records to be associated with Attribute Value Dimension members in the
data source. Each value (1-12) is associated with a time period column in the output
table. This method is efficiently processes records in large multi-period datasets.

Content Type - Lets the designer determine the record detail that will be written to the BI Blend
tables. Cube dimensions, such as Entity Account and User Defined, are supported by Source and
Target, or transformed, results. The designer can also include or exclude the attribute or BI Blend
Extended Dimensions.

l TargetCubeDims – Results will be limited to the transformed cube dimension target

members.

l TargetCubeDimsSource – Results will include both the source and transformed cube
dimension target members.

l TargetCubeDimsAttributes – When data is integrated to attribute dimensions for non-

cube dimension records, such as invoices assigned as an attribute,
TargetCubeDimsAttributes must be used to process and include the detail. Results are
transformed Cube target members including Attributes.

l TargetCubeDimsAll – Output to table provides all available Source, Target and Attribute
results.

l TargetCubeDimsAttributesEx – Results will include the transformed cube dimension

targets and the BI Blend extended dimensions.

l TargetCubeDimsAllEx - Output to table provides all available source, target and attribute
and BI Blend Extended Dimension results.

CreateStarSchema – Generate the BI Blend database as Star Schema, required for Leveled
Hierarchy reporting.

ColumnAlias – Provided functionality to rename the output table columns using the stage table
keywords. If left blank, the system generates column names based on the default table and
dimension labels.

BI Blend Design and Reference Guide 35

Designing BI Blend

Prefix Alias Keys:

l User Defined: U1T through U8T

l Attributes: A1 through A20

l Attribute Values: V1 through V12

l Label: Lbl

l TextValue: Tv

l Blend X Attributes: xA1 through xA40

l Blend X Attribute Value: xV13 through xV36

Example: to custom label columns for User Defined Dimensions and a Stage Attribute

U1T=CostCenter,U2T=Products,U3T=Regions,A1=DepartmentCode,A2=ExpenseID

Translate - Translation can be defined as any destination currency. Entity and Account must have
a member defined for Aggregation Info to determine the local currency and rates to use. Only the
direct method is applied based on the rates defined on the cube settings, determined by account
type. No complex currency translation is supported, by sub-parent levels. All Entities will be
translated to the destination currency.

Blend Unit Token- Assigns the Cube Dimension as the partitioning dimension to generate as
pages and the corresponding level of multithreading. Available dimensions for Blend Unit are:

Setting will evaluate the source and assign the largest member set as the
MaxMembersDim
Blend Unit
E# Entity
F# Flow
U1-U8 User Defined
A1-A20 Attribute Dimensions

Aggregation Controls – Used to set the parent level for the top-level aggregation. The intent of
these filters is to limit the return of records to the parents that need aggregation for reporting. The
complete dataset will be used, however, records outside any defined aggregation path will be
ignored. As an example, if an aggregation control for Entity was set for US Clubs, any records
present in the dataset for Entities outside the US Clubs hierarchy will be ignored. This allows BI
Blend to generate results focused on specific reporting and analysis requirements.

BI Blend Design and Reference Guide 36

Designing BI Blend

When using BI-Blend with extended dimensions, BI-Blend will ONLY aggregate the ultimate base
members of an extended dimension. Loading a member that becomes a parent as a result of
dimension inheritance, such as mapping source data to a parent member, will result in NO
aggregation for the extend parent.

Aggregation Info Settings

NOTE on Filtering – Applying Aggregation Information Filters will return both the Filtered Parents
and the base member records. The exception to this behavior is related to the when the Blend
Unit Dimension uses the ;Member filter. The “Member” filter on a Blend Unit dimension will return
only that member.

l Not Used - This will not include any parent level members in the results. The records will
be collected at the row record level detail if included in the Data Source.

l Parent Level Selection - Selecting a single parent will limit the records to the members
within that hierarchy and generate records for all members with data within the hierarchy.
Selecting NA Clubs would create records for NA Clubs as TreeDescendantsInclusive.

BI Blend Design and Reference Guide 37

Designing BI Blend

l Parent Level Selection, as Member – The dimension assigned as the Blend Unit can be
restricted to return a summary parent member. This is a situation where the results are only
required at an aggregated parent. By entering the setting as NA Club;Member, all the
descendant’s results will be aggregated to the parent, but the descendant members will not
be included in the output. This option for a specified Parent Members is only available on
the Blend Unit. For non-Blend Unit dimensions, using ;Member will return the Parent
Member defined, as well as all the base members found within the parent member’s
hierarchy.

l Parent Level Selection, Children – Focuses aggregation points to a member’s children.

The selection of NA Clubs;Member.Children, property will return the Children of the Parent
member as Children Inclusive. Alternatively, NA Clubs;Children can be used as a “non-
inclusive” filter. Base members are also returned.

l Parent Level Selection, TreeDescendants – This is a non-inclusive option written as NA

Clubs;TreeDescendants.

BI Blend Design and Reference Guide 38

Designing BI Blend

l Star Schema Only – When “Create Star Schema” is set to “True” the SSOnly filter can be
used, NA Clubs;SSOnly. This setting will not generate parent level records in the BI-Blend
data table. The “SSOnly” Aggregation Control will create a complete Star-Schema
Dimension table containing fields MemberName, MemberDesc, NameAndDesc,
ParentName, IndentLevel, IsBase and MemberSeq for Cube Dimension hierarchy as
specified by the parent member in the Aggregation Control.

l Star Schema Leveled Hierarchy – The “SSLeveled” property, CostCenters;;;SSLeveled,

requires “Create Star Schema” is set to “True”. If enabled, the corresponding Star-Schema
Dimension table will have zero-based column fields added, which correspond to the
hierarchical structure of the dimension. Not valid for use on Account and Attributes. See
the section on Leveled Hierarchy.

NOTE: Aggregation point or member must be defined on the Blend Unit

dimension.

l Reporting Labels as Name, Description or Name and Description – By default, any

Dimension set as an Aggregation will return the results using the Name field found in the
Dimension properties. To modify the results, the Dimension or Attribute must be used as an
Aggregation Control. Using the label properties on a standard, non-Star Schema, BI Blend
table will replace the record with the labeling method. The delimited field for the label can
be modified as:
o N – Name
o D – Description
o ND – Name and Description

l Cube Dimension Aggregation Control Syntax

o TopMember;RestrictMember;Labels;StarSchemaControl
Houston;;ND
Houston;Member;ND
Houston;Member.Children;D
Houston;;;SSLeveled
Attribute Dimension Syntax, the number of fields is larger because to the inclusion of
the Dimension Name.

BI Blend Design and Reference Guide 39

Designing BI Blend

o Dimension Name; TopMember;RestrictMember;Labels

UD8BlendAttributes;Contracts;;D
UD8BlendAttributes;Contracts;Member;D

Leveled Hierarchy
Leveled hierarchy is for aggregation reporting where parent values reflect the aggregation points.
It is activated as a BI-Blend Aggregation Control impacting the Star-Schema Dimension tables
with the creation of Leveled and IsBaseBIBlend column fields. Leveling adds the hierarchy
context that is useful in Pivot Grid and custom Dashboard reports.

Syntax

l TopMember;RestrictMember;Labels;SSLeveled

l Clubs;;;SSLeveled

The column fields generated by the “SSLeveled” property will be created to the maximum depth of
the hierarchy, starting from the defined parent to the base member. The leveled columns will only
be created on base members and their ancestors where data is populated in the BI-Blend data
table. Levels greater than those containing data will not be created. The property is not valid for
use on Account and Attribute Aggregation Controls.

The leveling process will generate two field placeholders.

BI Blend Design and Reference Guide 40

Designing BI Blend

l XFLeveled - Created only on base-level records for data intersections whose hierarchy is
less than the maximum depth

l XFStored – Created only on parent level members. This represents the data intersection
of stored-parent values created by the BI-Blend engine

Leveled Hierarchy Processing

The “SSLeveled” property enables the generation of the hierarchy leveling fields. Efficiency is
accomplished by limiting the leveling only to members that contain data in the BI-Blend data table.
However, leveling is an additional process used by the BI-Blend Engine which will affect the
overall BI-Blend processing time.

Star-Schema Leveling Column Fields

The “SSLeveled” property enabled generates new columns in corresponding Star-Schema
Dimension Tables. BiBlend will ONLY aggregate the ultimate base members of an extended
dimension. Loading a member that becomes a parent as a result of dimension inheritance will
result in NO aggregation for the extend parent.

l IsBIBlendBase: Reflects the hierarchy status of where the actual data records exist.
Restricts the creation of leveling only on members that contain data in the data table.
Designates the member as a base (1) or parent member (0) by the usage in the BI-Blend
data table.

l Level x: Zero-based to maximum descendent depth only on member where BI Blend data
exists.

NOTE: The BI Blend leveled hierarchy is only available for star schema-enabled
workflows.

Example: Using the Leveled Hierarchy in the Large Data

Pivot Grid
In the example below, the table has a hierarchical view.

BI Blend Design and Reference Guide 41

Designing BI Blend

Notice in this example the items labeled “XFStored” indicate that the table is being “leveled” to
create leveled columns based on the maximum depth of hierarchy from the BI-Blend data.

BI Blend Design and Reference Guide 42

Designing BI Blend

Setting IsBIBlendBase
Use the IsBaseBIBlend parameter to show where actual data records exist in the table.

1. From the Leveled Large Pivot Grid, right-click in the blue row and select Show Prefilter.

BI Blend Design and Reference Guide 43

Designing BI Blend

The PivotGrid Prefilter dialog box opens.

2. Click the green +.

BI Blend Design and Reference Guide 44

Designing BI Blend

3. Click Rt and scroll through the list to select UD2IBBIB.

BI Blend Design and Reference Guide 45

Designing BI Blend

4. Click <enter a value> and type 1.

5. The equation looks like this:

6. Click OK.

7. After applying IsBaseBIBlend, the leveled table now changes to show only those rows that
contain actual data (XFLeveled) and has removed the rows that were added for leveling
(XFStored).

BI Blend Design and Reference Guide 46

Designing BI Blend

8. Click Save to save your changes.

BI Blend Application Setup

External Database Connection
The BI Blend target table must be a separate external database, that is not in the Application
Database. You will update the Application Server Configuration Utility to confirm a connection to
the new BI Blend target database.

Set Up Workflow for BI Blend

1. Create a Workflow structure to manage BIBlend. Apply the appropriate Security groups as
required. (Administrator)

BI Blend Design and Reference Guide 47

Designing BI Blend

2. Select the Import channel to Blend, by Scenario Type

a. Blend: Limited to the BI Blend data processing

b. Blend – Workspace: Additional support to display Workspace Dashboards

BI Blend Design and Reference Guide 48

Designing BI Blend

3. If designing BI Blend as a Workflow Process, Dashboards can be assigned through the

Validate Profile.

4. Assign the DataSource and Transformation Rule Profile which BI Blend will utilize to derive
the metadata and subtotals in the resulting table

5. Set the BI Blend Parameters which is a collection of properties which define the BI Blend
process defined in this document.

BI Blend Design and Reference Guide 49

Designing BI Blend

Calculations
Enrichment of the data processed by the BI Blend engine can be performed through the in-
memory processing of records, or at run-time using workspace data adapters. The use of existing
metadata hierarchies is an optimal solution to add parent level aggregations and reporting points
to the data set. Derivative Rules enrich the data set with time-based and other calculations. Run-
time calculations can be designed into Dashboard SQL Data Adapters. The inclusion of
aggregations and calculations adds to the record count of BI Blend processed data sets.

BI Blend Derivative Rules

A special category of Derivative Rules is designed for the BI Blend Engine. The calculation of
each derivative rule is performed within the Blend Unit. The calculations within the Blend Unit
partition cannot reference data found in other Blend Unit partitions. If required, the summarization
will be performed in the database or reporting layer.

The dependency of calculations on the Blend Unit is an important factor to consider when
designing a BI Blend Workflow. There are three classes of derivative rules specifically designed
for the BI Blend engine:

l BlendUnit All: Rules will process on all Blend Units.

l BlendUnit Base: Rules will process only on hierarchy base-level Blend Units.

l BlendUnit Parent: Rules will process only on parent-level Blend Units.

BI Blend Design and Reference Guide 50

Designing BI Blend

The use of each class of derivative rules depends on the design of the Blend Unit in the Workflow
BI-Blend Settings. First, determine which dimension is used to derive the Blend Unit by the Blend
Unit Dimension Token selection. Second, understand and review the selection made for the
corresponding dimensions Entity Aggregation Info.

Parent level members create base and parent level partitions. If a base-level member is selected,
only a single partitioned Blend Unit page is created, and BlendUnit All is used to define the
derivative rules.

Time-Aware Derivative Logical Operators

Blend X Derivative statistical computations filter records by time filters. This lets you develop
dynamic, standardized calculations to group records by time that function across all data sets.
Time filtering is keyed off the primary date field xBlendDateTime, as xBDT, for data series
statistical calculations.

Date Grouping
Date groupings are appended to the Blend X time-aware functions to dynamically filter the records
by a date range. The criterion for the range includes the start and end range, as well as the
grouping level.

Expression Syntax for Statistical Grouping

xBDT:[<Start DateTime>~<End DateTime>]!GroupingLevel {Y, HY, Q, M, W, D ,TH, TM, TS}

Grouping Expression Example

xBDT:[YYYY-MM-DD hh;mm;ss~YYYY-MM-DD hh;mm;ss]!Y

Example
A#[TicketCost]=Stats_SumCost:A1#[*]=None:A2#[*]=None:A4#[*]=None:xBDT#[2022-01-01
00;00;00~2022-12-31 23;59;59]!Y

BI Blend Design and Reference Guide 51

Designing BI Blend

All records within the Blend Unit are filtered by the range. Results are generated at the level of
detail defined in the Grouping Level. As an example, grouping a full year data set by “Y” will return
a single result for each year in the data set. Similarly, specifying “Q” will return four quarterly
records.

Rolling Time-Aware Filters

Rolling time-aware filters are appended to the Blend X time-aware functions to dynamically
calculate rolling calculations. All records within the Blend Unit are filtered by range. The syntax
references the date, operation for the range, and grouping level. A fixed date or variable is
supported.

Expression Syntax for Rolling Date

xBDT:[<D-X>~<Ref DateTime>]!GroupingLevel {Y, HY, Q, M, W, D ,TH, TM, TS}

Rolling Date Example

xBDT:D-90~YYYY-MM-DD hh;mm;ss]!Y

Example
A#[TicketCost]<<MySuffix_:A1#[*]=None:A2#[*]=None:A4#[*]=None:xBDT#
[CurrentDateTimeLocal~D+6]!Y

Data Buffer Logic

The Blend X logical operators support data buffer math. Calculation operations are not restricted
to defined constants. The secondary buffer is applied to the Math Value field in the complex
expression.

BI Blend Design and Reference Guide 52

Designing BI Blend

Blend X Derivative Naming Convention

BI Blend table results maintain account type references for data records. When enriching the data
set using derivative rules, the rule naming convention property functions to properly apply
Account Type to each of the data records generated. Account Type prefixes are as follows:

l R: Revenue

l E: Expense

l N: Non-Financial

l A: Asset

l L: Liability

Logical Operator Definitions

Add
Adds the derivative member’s value by the value set in the Math Value field.

Subtract
Subtracts the value specified in the Math Value field from the derivative member’s value.

Multiply
Multiplies the derivative member’s value by the value specified in the Math Value field.

Divide
Divides the derivative member’s value by the value specified in Math Value. You can also use a
buffer value as shown in the following image:

BI Blend Design and Reference Guide 53

Designing BI Blend

In this case, accounts named TicketCost in the load file are targeted. These accounts are
renamed to Math_TCPerNPS.

This is the complex expression that performs the division. The MathBufferValue amount is
captured for the NPS account. The values for the original TicketCost account records are then
divided by the value of the NPS account:

Lag (Years)
Returns the past value (lag) per the number of years set in the Math Value field of the rule. Math
Value = 1 by default. The lag operator looks at the first date in the set, then returns the lag value
for the number of years specified. In the data set shown below, the value 12 would be returned if
the Lag (Years) value is set to 1:

BI Blend Design and Reference Guide 54

Designing BI Blend

Lag (Months)
Returns the past value (lag) per the number of months set in the Math Value field of the rule.
Math Value = 1 by default. The lag operator looks at the first date in the set, then returns the lag
value for the number of months specified. In the data set shown below, the value 9.99 would be
returned if the Lag (Months) value is set to 1:

Lag (Days)
The Lag (Days) logical operator returns the past value (lag) per the number of days set in the
Math Value field of the rule. The data set is first sorted in descending order by the Created date.
The lag operator looks at the first date in the set, then returns the lag value for the number of days
specified. In the data set shown below, the value 23.39486017 would be returned if the Lag
(Days) value is set to 2:

Lag (Hours)
Returns the past value (lag) per the number of hours set in the Math Value field of the rule. The
data set is first sorted in descending order by the Created date/time. The lag operator looks at the
first date/time in the set, then returns the lag value for the number of hours specified. In the data
set shown below, the value 43.53177343 would be returned if the Lag (Hours) value is set to 2:

BI Blend Design and Reference Guide 55

Designing BI Blend

Lag (Minutes)
Returns the past value (lag) per the number of minutes set in the Math Value field of the rule. The
data set is first sorted in descending order by the Created date/time. The lag operator looks at the
first date/time in the set, then returns the lag value for the number of minutes specified. In the data
set shown below, the value 21.12 would be returned if the Lag (Minutes) value is set to 4:

Lag (Seconds)
Returns the past value (lag) per the number of seconds set in the Math Value field of the rule. The
data set is first sorted in descending order by the Created date/time. The lag operator looks at the
first date/time in the set, then returns the lag value for the number of seconds specified. In the data
set shown below, the value 17.77 would be returned if the Lag (Seconds) value is set to 3600 (60
minutes):

Lag Change (Seconds)

Returns the difference between the latest value and a past value (lagged value), per the number
of seconds set in the Math Value field of the rule. The data set is sorted in descending order by
the Created date/time. The lag change operator looks at the first date/time in the set, then looks at
the lag value based on the number of seconds set in the rule. The difference between the latest
value and the lag value is returned. In the data set shown below, the value -2.25 would be
returned if the Lag Change (Seconds) value is set to 3600 (60 minutes):

BI Blend Design and Reference Guide 56

Designing BI Blend

Lag Change (Minutes)

Returns the difference between the latest value and a past value (lagged value), per the number
of minutes set in the Math Value field of the rule. The data set is sorted in descending order by the
Created date/time. The lag change operator looks at the first date/time in the set, then looks at the
lag value based on the number of minutes set in the rule. The difference between the latest value
and the lag value is returned. In the data set shown below, the value -5.60 would be returned if the
Lag Change (Minutes) value is set to 4:

Lag Change (Days)

Returns the difference between the latest value and a past value (lagged value), per the number
of days set in the Math Value field of the rule. The data set is sorted in descending order by the
Created date/time. The lag change operator looks at the first date/time in the set, then looks at the
lag value based on the number of days set in the rule. The difference between the latest value and
the lag value is returned. In the data set shown below, the value 8.85 would be returned if the Lag
Change (Days) value is set to 3:

BI Blend Design and Reference Guide 57

Designing BI Blend

Lag Change (Months)

Returns the difference between the latest value and a past value (lagged value), per the number
of months set in the Math Value field of the rule. The data set is sorted in descending order by the
Created date/time. The lag change operator looks at the first date/time in the set, then looks at the
lag value based on the number of months set in the rule. The difference between the latest value
and the lag value is returned. In the data set shown below, the value -3.37686333 would be
returned if the Lag Change (Months) value is set to 1:

Lag Change (Years)

Returns the difference between the latest value and a past value (lagged value), per the number
of years set in the Math Value field of the rule. The data set is sorted in descending order by the
Created date/time. The lag change operator looks at the first date/time in the set, then looks at the
lag value based on the number of years set in the rule. The difference between the latest value
and the lag value is returned. In the data set shown below, the value 3.37686333 would be
returned if the Lag Change (Years) value is set to 1:

Lag Change (Step Back)

First, sorts a data set in descending order by the Created date. Then, it “Steps Back” from the first
record in the data set and selects the record based on the number of positions back specified in
the Math Value. The first record in the data set is position zero. In the data set below, the
difference between the 1st record and the 6th record is returned:

BI Blend Design and Reference Guide 58

Designing BI Blend

Lag (Step Back)

Sorts a data set in descending order by the Created date and “steps back” from the first record in
the data set, selecting the record based on the number of positions back specified in the Math
Value. In the example below, the value of the 6th record (sorted descending) in the data set is
returned:

Lag Change (Hours)

Returns the difference between the latest value and a past value (lagged value), per the number
of hours set in the Math Value field. The data set is first sorted in descending order by the Created
date/time. The lag change operator looks at the first date/time in the set, then looks at the lag
value based on the number of hours. The difference between the latest value and the lag value is
returned.

In the data set shown below, the value -28.02 would be returned if the Lag Change (Hours)Math
Value is set to 2, where 15.5154439 - 43.53177343 = -28.02.

Absolute Value
Generates a new record that contains the absolute value of the values in the load file.

Round (Precision)
Generates a new record that contains the rounded value of the values in the load file. The
Math.Round(decimal, int) method is used. The Math Value is the number of decimal places in the
return value. It rounds a decimal value to the specified number of decimal places and rounds
midpoint values to the nearest even number.

BI Blend Design and Reference Guide 59

Designing BI Blend

Modulo (Divisor)
Calculates the modulo (remainder after division) value. For example, 12/10 modulo = 2. The
divisor is set in the Math Value field within the Derivative Expression Editor dialog box. The
default divisor value is 10.

Power (Exponent)
Generates a new record that contains the exponential value of the values in the load file. For
example, 5 to the power of 2 is 5 x 5 = 25. The power value is set in the Math Value field of the
Derivative Expression Editor dialog box.

Proportion (Count)
Calculates the proportion of 1 out of the total number of rows that were created. For example, in
the rule expression A#[TicketCost]=Stats_PropCount:A1#[]=None:A2#[]=None:xBDT#
[2022-01-01~2022-12-31]!Y, a derivative record is created for each record where the account =
TicketCost and that fall within the date range 2022-01-01 - 2022-12-31. If 54 total rows are
created, the proportion count value will equal 1/54 = 0.018518519.

Proportion (Value)
Calculates the proportion of the value of a field out of the total for that column, where Value / Sum
of column = Proportion (Value). For example, in the rule expression A#[TicketCost]=Stats_
PropValue:A1#[]=None:A2#[]=None:xBDT#[2022-01-01~2022-12-31]!Y, a derivative record is
created for each record where the account = TicketCost, and that falls within the date range 2022-
01-01 to 2022-12-31.

Clip (Lower Bound)

Sets a minimum lower threshold for records that are created as a result of this rule. The default
value is 10. Values less than the lower bound are set to the lower bound value. For example,if the
original value is 9, the value is set to 10 in the derived record.

Clip (Upper Bound)

Sets an upper threshold for records that are created as a result of this rule. The default value is 35.
Values greater than the upper bound are set to the upper bound value. For example, If the original
value is 36, the value is set to 35 in the derived record.

Count
Creates one record in the BI Blend table. This is a count of the total number of rows returned per
the rule expression.

First DateTime
Creates one record in the BI Blend table that shows the value for the earliest created date/time of
the records matching the rule expression.

Last DateTime
Creates one record in the BI Blend table that shows the value for the latest date/time of the
records that match the rule expression.

BI Blend Design and Reference Guide 60

Designing BI Blend

Minimum Value
Creates one record in the BI Blend table that shows the lowest value for the records that match
the rule expression.

Maximum Value
Creates one record in the BI Blend table that shows the highest value for the records that match
the rule expression.

Average
Creates one record in the BI Blend table that shows the average value for the records that match
the rule expression.

Median
Creates one record in the BI Blend table that shows the median value for the records that match
the rule expression.

Mode
Creates one record in the BI Blend table that shows the mode value for the records that match the
rule expression. The mode is the value that appears the most number of times in a data set. If
there are multiple instances of a mode in a file, the first value is returned if the values are sorted
ascending. This is also done for the mode calculation in Excel.

Standard Deviation
Creates one record in the BI Blend table that shows the standard deviation value for the records
that match the rule expression. It uses the standard deviation based on the entire population.
Numbers can be validated by using the STDEV.P function in Excel.

Col DateDiff (Days)

Returns the number of days within the BeginDate and EndDate specified in the load file. The
value is rounded down to the nearest whole number. The Math Value syntax, //xDt1;xDt2,
indicates that the difference between EndDate and BeginDate is returned. XDt1 and xDt2 are
aliases for BeginDate and EndDate, which are set in the Data Source:

BI Blend DateTime dimensions are set on the cube:

BI Blend Design and Reference Guide 61

Designing BI Blend

Here is an example of a rule expression for this operator: A#[TicketCost]=Stats_

DateDiffDays1and2:A1#[]=None:A2#[]=None:xBDT#[2022-01-01 00;00;00~2022-12-31
23;59;59]!D. This filters the data series to return account data between the specified date range,
and sets the Group Level by day.

For all accounts named TicketCost in the load file, create a new record and do the following:

1. DateDiffDays.E: the .E portion of this rule name sets the Account Type to Expense.

2. Change account name to Stats_DateDiffDays1and2.

3. Set all A1 instances (Product column in db, Instance column from load file) to None.

4. Set all A2 instances (Customer column in db, Instance col from load file) to None.

BI Blend Design and Reference Guide 62

Designing BI Blend

Col DateDiff (Months)

Returns the number of months within the BeginDate and EndDate specified in a load file. The
value is rounded down to the nearest whole number. The Math Value syntax, //xDt1;xDt2,
indicates that the difference between EndDate and BeginDate is returned. XDt1 and xDt2 are
aliases for BeginDate and EndDate, which are set in the Data Source (see Col DateDiff (Days).

Here is an example of a rule expression for this operator: A#[TicketCost]=Stats_

DateDiffMonths:A1#[]=None:A2#[]=None:xBDT#[2022-01-01 00;00;00~2022-12-31
23;59;59]!M. :xBDT#[2022-01-01 00;00;00~2022-12-31 23;59;59] filters the data series to
return account data in the date range specified. !M sets the Group Level by Month.

Col DateDiff (Years)

Returns the number of years within the BeginDate and EndDate specified in a load file. The value
is rounded down to the nearest whole number. The Math Value syntax, //xDt1;xDt2, indicates
that the difference between EndDate and BeginDate is returned. XDt1 and xDt2 are aliases for
BeginDate and EndDate, which are set in the Data Source.

Col DateDiff (Hours)

Returns the number of hours within the BeginDate and EndDate specified in a load file. The value
is rounded down to the nearest whole number hour. The Math Value syntax, //xDt1;xDt2,
indicates that the difference between EndDate and BeginDate is returned. XDt1 and xDt2 are
aliases for BeginDate and EndDate, which are set in the Data Source:

Col DateDiff (Minutes)

Returns the number of minutes within the BeginDate and EndDate specified in a load file. The
value is rounded down to the nearest whole number minute. xDt1 and xDt2 are aliases for
BeginDate and EndDate, which are set in the Data Source:

Col DateDiff (Seconds)

Returns the number of seconds within the BeginDate and EndDate specified in a load file. xDt1
and xDt2 are aliases for BeginDate and EndDate, which are set in the Data Source:

Log (P+1)
Generates a new record that contains the natural log (1+x) value of the values in the load file. The
following function is used in the calculation: Math.Log() function. Natural Log (1+x).

NOTE: The formula reference in BI Blend for the Log P1 function is not calculated
for values <=1 and returns null in that case.

BI Blend Design and Reference Guide 63

Designing BI Blend

Apply Suffix or Prefix

Renames accounts to either MyPrefix_TicketCost or TicketCost_MySuffix using the following
expressions: A#[TicketCost]>>MyPrefix_ or A#[TicketCost]<<_MySuffix, respectively.

Create If (> x)
Creates a record in the BI Blend table for each record where the Actual Value is greater than the
value set in Math Value.

Create If (< x)
Creates a record in the BI Blend table for each record where the Actual Value is less than the
value set in Math Value.

Filtering Records with Rule Expressions

Rule expressions in transformation rules can be used to filter records by date and time ranges
using xBDT syntax. When data is loaded, derivative records are created for records that fall within
the specified date and time range. Here are some examples:

xBDT#[2022-01-01 00;00;00~2022-01-31 23;59;59]: Creates derivative records for imported

records that are >= start date/time and <= finish date/time.

xBDT#[CurrentDateTimeLocal~D+6]: Creates derivative records for imported records that are

greater than the current date/time plus 6 days.

xBDT#[D-90~2022-03-31 23;59;59]: Creates derivative records for imported records that fall
within the range of the specified date/time minus 90 days. Records equal to the current date/time
are imported as well.

Sub-String Grouping in Rule Expressions

Syntax used in a rule expression can capture a specified number of characters from a string,
starting with the character in the first position. In the example below, the first 6 characters of an
account name are captured and applied in derivative records. Loaded records with an account
name TicketCost will show an account name of Ticket in the derived records.

A#[TicketCost]@6

Syntax used in a rule expression can capture a sub-string between the first and last characters of
a string. In the example below, a 4 character sub-string is captured starting at the 7th character of
the string. Loaded records with an account name TicketCost will show an account name of Cost in
the derived records.

A#[TicketCost]@7,4

BI Blend Design and Reference Guide 64

Designing BI Blend

Logical Operators Usage

Logical Operators extend a normal mapping rule with VB.Net scripting functionality. To use logical
operators:

1. Navigate to Application tab > Data Collection > Transformation Rules.

2. Under Rule Groups, expand Derivative, then select a derivative rule group.

3. Select a value from the Type drop-down menu.

4. Click Insert Row.

5. Type a name into the Rule Name field.

6. Type a description into the Description field.

7. Type an expression into the Rule Expression field. For example, A#[TicketCost]=Math_
Power2:A1#[]=None:A2#[]=None:xBDT#[2022-01-01 00;00;00~2022-12-31
23;59;59]!Y

8. Double-click the new row in the Logical Operator column.

9. In the Derivative Expression Editor dialog, from the Expression Type drop-down menu,
select the logical operator you would like to use.

10. Type an appropriate value into the Math Value field and click OK.

11. In the Derivative Type column, set the value to Final (Exclude Calc).

12. In the Order column, type a value that is greater than the current highest order value in the
list.

13. Click Save.

14. Navigate to the OnePlace tab and set a BI Blend Workflow POV.

15. Click Load and Transform.

16. Select a file and click Open.

17. Click OK.

BI Blend Design and Reference Guide 65

Designing BI Blend

18. Log in to the database server for the environment and execute a query on the BI Blend
database where the data was loaded to. You will see records returned.

19. Verify that the values in the applicable amount column are equal to the expected results.

BI Blend Design and Reference Guide 66

Common Error Messaging

Common Error Messaging

Cannot find a matching base member in the data record, no data intersections are found.

NOTE: A BI Blend data source cannot be used to import data to Stage because the
extra BI Blend attributes and date dimensions do not work for Stage. The following
error message is one example of the messages you might see.

You will see the following error message when loading to a workflow if attribute value dimensions
have been assigned a static value.

BI Blend Design and Reference Guide 67

Common Error Messaging

Reporting Components
Dashboard Adapter – BIBlendInfo Method Query
BIBlendInfo Method Query is available to retrieve the relative information from the
StageBIBlendInformation Table.

BI Blend Design and Reference Guide 68

Common Error Messaging

Large Pivot Grid

The Large Pivot Grid is a Dashboard Component which is designed to allow pivot style dashboard
reporting and analysis on external database tables. Key features of this Component are:

l Server based processing – Key solution for accessing data in large tables. Pivoting
requests are performed on the server, returning only the requested “slice” of data.

l Supports paging to manage large data sets

l Measures support only a single aggregation type, (Sum, Min, Max)

BI Blend Data Adapter

The BI Blend Adapter is used to simplify writing queries to the BI Blend tables by eliminating the
need for SQL scripting. As an Adapter, it cannot support the complete contents of very large BI
Blend tables. The BI Blend Adapter should contain “where” clauses to slice the results into
reporting slices. The BI Blend Adapter does not support paging to manage large volumes of
records.

BI Blend Design and Reference Guide 69

Common Error Messaging

Fdx Specialty Connector BRAPI’s

FDX, Fast Data Extract, BRAPIs allow a variety of options for connecting to DataSources for BI
Blend. A key differentiator between the FDX BRAPI’s and other collection methods is support of
parallel processing, in memory processing and management of the Time dimension.

FDX BRAPI’s provide functionality to build Connectors to extract data from:

l Cube Views: Extract data through a Cube View definition. Ideal for defining data
definitions through a Cube View, including Dynamic Calc results.

l Across Cube Data Units: Extract Cube data to a BI Blend target table through defined
Data Unit filters.

l Stage Workflow Imports: Ability to leverage existing Stage Data. Uses may be reporting
on existing “attribute” records contained in Stage, or simply enhanced dashboard reporting
on Stage data.

l Source Systems / Data Warehouses: Performance oriented solution to connect to source

system.

Performance is gained through the BRAPI’s ability to parallel process. For example, extracting
data by Cube Data Unit will parallel process all the Data Units defined in the filter. Second, the
FDX BRAPI’s do not generate a “.CSV” file as do Data Management File “Export Data” or “Export
File” processes. The results of the export are managed during the BI Blend “in-memory”
processing.

BI Blend Design and Reference Guide 70

Common Error Messaging

In cases of very large data sets, which where multiple periods are loaded, the processing time can
be slow because each period is reflected as a data record. FDX BRAPI’s offer solutions to pivot
the Time records to columns in order to create a matrix data layout. The Datasource can
associate each of the periods with an “Attribute Value” field within the Integration settings. The
design will treat each record as a collection of 12 periods when processing.

l FdxExecuteCubeView: Extract data defined through a Cube View. Any data presented in
the Cube View will be extracted, such as Dynamic Calculated results.

l FdxExecuteCubeViewTimePivot: Cube View Data will generate all time as Columns

which can be assigned as Attribute Value members in the Data Source.

l FdxExecuteDataUnit: Cube Data extract solution to extract data from Data Unit members.

l FdxExecuteDataUnitTimePivot: Cube Data extract solution to extract data from Data Unit
members. Generate all time as Columns, which can be assigned as Attribute Value
members in the Data Source.

l FdxExecuteStageTargetTimePivot: Extract existing Workflow’s Stage Data. Generate all

time as Columns, which can be assigned as Attribute Value members in the Data Source.

l FdxExecuteWarehouseTimePivot: Extract data from an external source system.

l FdxGetCubeViewOrDataUnitColumnList: Connector BRAPI used to return field names.

l FdxGetStageTargetColumnList: Connector BRAPI used to return field names.

l FdxGetWarehouseColumnList: Connector BRAPI used to return field names.

Using Fdx Connectors

Workflow Connectors are required to realize the benefits of the parallel processing and Time Pivot
capabilities of the FDX BRAPI’s. Once created, the Connectors are assigned to the BI Blend
Workflow. Each type of Connector is defined using “name value pairs” on the properties of the
Workflow Text fields.

BI Blend Design and Reference Guide 71

Common Error Messaging

Server Roles
Optional Application Server
The default processing will be BI Blend processed are queued across the available Stage
Servers. Within the Workflow BI Blend Settings, a defined server can be established for all BI
Blend processes. This can be as a list and support wild cards.

Batch Processing Support

Batch Processing is supported through automating the BI Blend process.

BI Blend Design and Reference Guide 72