BDA Metadata Manager

Application Summary:

Extract, edit, and load dimensional metadata in xlsx sheets template format.

BDA Metadata Manager Overview

The BDA Metadata Manager provides administrators and implementers a simple, code free avenue to extract, modify, and upload OneStream dimensions. Dimensional metadata is extracted to easy-to-read parent/child xlsx sheets; updates to dimensions are performed using the same sheet templates.

In addition to member creates and edits, the Metadata Manager Solution can perform deletes of data-free members.

OneStream practitioners navigate to the BDA Standard Utilities dashboards, click on “BDA Metadata Manager” and then select dimensions and define their scope for extraction into easily understood Excel templates. Dimension updates are performed by importing these templates back into OneStream via the same metadata utility.

Common uses cases are administrative review, documentation, implementation, maintenance, and migration between environments.

Setup and Installation

The installation process for all BDA Solutions is the same: download the install zip file from Partner Place, import, and run the BDA_FW_Dashboard_Setup dashboard using the purchased product keys. The zip file contains all BDA Solutions; the keys unlock the purchased Solutions.

See the BDA Installation Solution Guide SV1.0.0 for more information.

BDA Metadata Manager Solution

Functionality Overview

There are five general functional areas:

1. Dimensional selectors

2. Optional properties

3. Property update behavior

4. Export dimensions

5. Import dimensions

Dimension Output

The extract process dynamically creates and opens an Excel workbook by selected dimension.

Each dimension has unique corresponding tabs; one or many or all may be selected, e.g., Entity, Flow, UD3, etc.

Partial dimension extracts are performed by entering a parent member in the dimension’s text box. The utility will extract that member and its descendants.

The workbook name is programmatically generated and follows a naming convention of “MetadataExtract.HHMMSS”, e.g., MetadataExtract.063736.xlsx, where the last six digits correspond to the server’s HHMMSS time.

Sheet Names

Metadata sheets use a single character dimension type code, e.g., A = Account, E = Entity, F = Flow, S = Scenario, Un = UDn.

Affected dimensions are driven by the dimension name; the sheet name does not determine functionality. As a matter of good practice, use sheet names that match dimension types to drive clarity, especially when loading multiple dimensions.

Export Location

The Import/Export Security Utility creates Excel workbooks on export. These are located in the C:\Users\WindowsUserName\AppData\Local\Temp\OneStream folder.

Sample Partial Export

Dimension Import

An extracted dimension file can be modified and imported into a OneStream application.

Some considerations around import functionality:

• Use a sample export of a given dimension type to create an import template.

• The dimension import process can add members as well as remove, move, copy, rename, and sort members by using Action Type/Action Value keywords.

• Members and hierarchies are added via member rows in the dimension’s worksheet.

• Shared members are added by a new row with an alternate parent.

• Members and hierarchies are deleted using a Remove/Node, Member, Relationship Action Type/Action Value. Members to be deleted must not contain data.

• Move members using a Move/MemberName Action Type/Action Value.

• Copy members to a new parent using a Copy/MemberName Action Type/Action Value.

• Rename members using the Rename/NewMemberName Action Type/Action Value.

• Sort children and descendants of a member using the Sort/MemberName Action Type/Action Value.

• Property updates are performed by changing values in the relevant columns.

• Only valid values are accepted, e.g., using “Conditional” in the Allow Input column will fail as that Boolean property accepts only True or False.

• Properties can be removed by using the Update Properties – Remove On Blank option in the Property Update Mode selector.

• Property updates are not supported when member imports use Action Type/Action Value.

• Imports can be net new members and hierarchies or a mix of new members and edits. However, as a matter of practice:

• Take care that member adds, removes, and edits do not clash with each other as this may result in unpredictable results.

• Import only relevant members, i.e., if performing an import of 42 new members, do not include dimension hierarchies, members, etc. that are not germane to those new members. While including existing members in an unchanged manner is technically valid, functionally this introduces the opportunity for error.

Action Type/Action Values

Artifacts

Export

There are two export dimensional metadata areas:

1. Dimensions

2. Optional export properties

Dimensions

Dimension sheets are extracted according to their selected status and parent level member. Use Root to extract the full dimension type.

Fields

Dimension extracts include only their relevant fields, e.g., Accounts’ IsIC property is irrelevant to User Defined dimensions and so is excluded from those latter dimension types’ export.

# Standard Fields

By dimension type, exports (and imports) include the standard fields list below.

Optional Properties

In addition to the Standard Fields, the Metadata Extract & Load utility has the following options:

Additional Properties Fields

Ticking the Statistics, Constraints, and Less Used Items boxes extracts the following additional fields:

The properties Shared, #Children, #Parents, Depths(s), and Path(s) are informational only and cannot be updated.

Include Varying Properties

The Accounts, Entity, Flow, and UD1 through UD8 dimension types support varying properties by time and scenario. When varying properties exist and are extracted, their sheet name follows a DimensionTypev naming convention, e.g., Ev, Av, Fv, and U3v.

# Partial Example

This Flow dimension type extract shows formulas that vary by Scenario Type – Model, Plan, and Actual – and Time – 2013Q1. Its Excel sheet name is Fv

Import

Dimension type metadata extracts contain properties, e.g., Text1 through Text8, Agg Weight, Is Cons, etc. On import, these properties can be treated in three ways:

1. Ignore Blanks – unvalued property fields have no impact on existing member properties.

2. Remove On Blank – unvalued property fields remove existing member properties.

3. Ignore Properties – property fields, whether valued or not, have no impact existing member properties.

Use Cases

The below UD7’s Location dimension will be used as the sample for the below use cases.

Initial Dimension State

Here is the dimension in its initial state.

Partial Sample Extract

Add New Member

To add Mozambique to Southern Africa, create a separate template (as noted, this template could include all existing members but it is safer to only update what is needed) with a single member row. Mozambique is a child of Southern Africa.

Import Template

Results

Before	After

Add Shared Member

Adding a shared member requires a repeated child member name under a new parent. In this example, Middle Income Countries is a new member with parent Top and has five children that are also children of Central and Southern Africa: RSA, Namibia, Botswana, Angola, and Cameroon. Loading this file will not move or remove the existing children.

This shared member behavior is implicit. Member loads with new parents are automatically added to hierarchies. Inadvertent shared members must have their relationship with a parent member deleted; this is not the same as deleting a member entirely.

Adding a shared member via member repeats is functionally the same as an explicit Copy. Either method is acceptable; the Shared Member approach is more succinct.

Import Template

Results

Before	After

Remove

There are three remove types: Member, Node, and Relationship.

Member and Node removes will not succeed if the members have data; that data must be deleted before a remove is possible, either manually or through the Metadata Extract & Load Utility.

Remove Member

A remove member requires the member name in question and its parent.

The Action Type/Action Value fields must have the values Remove/Member.

Import Template

Result

Before	After

Remove Node

A remove node requires the member name in question and its parent.

The Action Type/Action Value fields must have the values Remove/Node.

Import Template

Result

Before	After

Relationship

Removing a relationship strips the member away from its parent and places it in the Orphans hierarchy.

Removal of a parent member will orphan all of its descendants.

Single Member

A single member relationship remove does just that: removes hierarchical relationships for specified members.

Import Template Single Member

Result

Before	After

Parent Member

A parent member relationship remove deletes the dimensional relationship for both itself and all of its descendants.

Import Template Parent Member

Result

Before	After

Move

Members can be moved between parents by specifying its existing parent, the member name, and an Action Type/Action Value of Move/NewParentName.

Import Template

Result

Before	After

Copy

A copy is functionally equivalent to a creating a shared member via add new parent/child relationships of existing members.

Members can be copied by specifying its existing parent, the member name, and an Action Type/Action Value of Copy/NewParentName.

Import Template

Result

Before	After

Rename

Members can be renamed by specifying its existing parent, the member name, and an Action Type/Action Value of Rename/NewMemberName.

Import Template

Result

Before	After

Sort

Hierarchies can be sorted alphabetically by specifying the target member’s parent, the target member name, and an Action Type/Action Value of Sort/Child or Sort/Descendants.

Children

A children sort performs a sort on the Child column member name and its immediate descendants.

Import Template Children

Result

Before	After

Descendants

A descendants sort performs a sort on the Child column member name and all of its descendants.

Import Template Children

Result

Before	After

Administration Tasks

As noted in the Overview section of this document, access to this Solution must be tightly controlled because of its wide data scope.

Once installed, there are no settings.

Data Structures

This Solution uses three tables: BDA_FW_Solution_Keys, BDA_FW_Task_EditHistory, and BDA_FW_Task_Status.

BDA_FW_Solution_Keys

Solution key(s) for the relevant BDA Solutions.

Sample Data

Table Schema

BDA_FW_Task_EditHistory

Tasks in Task Editor with 10 generations of audit.

Sample Data

Table Schema

BDA_FW_Task_Status

Tracks completed task status when feature is in use

Sample Data

Task Schema