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.

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

  • 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.

  • 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.

  • TargetCubeDims – Results will be limited to the transformed cube dimension target members.

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

  • 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.

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

  • TargetCubeDimsAttributesEx – Results will include the transformed cube dimension targets and the BI Blend extended dimensions.

  • 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.

Prefix Alias Keys:

  • User Defined: U1T through U8T

  • Attributes: A1 through A20

  • Attribute Values: V1 through V12

  • Label: Lbl

  • TextValue: Tv

  • Blend X Attributes: xA1 through xA40

  • 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. Translation will occur during a blend import even if Entity and Account do not have Aggregation Info defined. 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:

MaxMembersDim

Setting will evaluate the source and assign the largest member set as the 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.

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.

  • 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.

  • 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.

  • 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.

  • 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.

  • Parent Level Selection, TreeDescendants – This is a non-inclusive option written as NA Clubs;TreeDescendants.

  • 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.

  • 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.

  • 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:

    • N – Name

    • D – Description

    • ND – Name and Description

  • Cube Dimension Aggregation Control Syntax

    • 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.

    • 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

  • TopMember;RestrictMember;Labels;SSLeveled

  • 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.

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

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