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

Action Type	Action Value	Parent/Child Columns Reference
Remove	Node, Member, Relationship	Target Member
Move	NewParentName	NewParentName/MemberName.
Copy	NewMemberName	ParentName/NewMemberName.
Rename	RenamedMemberName	ParentName/MemberName.
Sort	Children, Descendants	ParentName/MemberName

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.

Accounts	Entity	Flow	Scenario	UD
Dimension	Dimension	Dimension	Dimension	Dimension
Parent	Parent	Parent	Parent	Parent
Child	Child	Child	Child	Child
Description	Description	Description	Description	Description
Action Type	Action Type	Action Type	Action Type	Action Type
Action Value	Action Value	Action Value	Action Value	Action Value
Text1	Text1	Text1	Text1	Text1
Text2	Text2	Text2	Text2	Text2
Text3	Text3	Text3	Text3	Text3
Text4	Text4	Text4	Text4	Text4
Text5	Text5	Text5	Text5	Text5
Text6	Text6	Text6	Text6	Text6
Text7	Text7	Text7	Text7	Text7
Text8	Text8	Text8	Text8	Text8
Account Type	Currency	Allow Input	Use In WF	Allow Input
Allow Input	Is Cons	Is Cons	WF Track Freq	Is Cons
Is IC	Is IC	Agg Weight	WF Time	Agg Weight
IC Plug	Allow Adj	Switch Sign	WF Start	In Use
Is Cons	Allow Adj from Children	Switch Type	WF End	Formula Type
Agg Weight	Pct Cons	Proc Type	Num Input Periods	Formula
In Use	Pct Own	Alt Curr	Type	Formula For Calc Drill Down
Formula Type	Own Type	Src Mbr Alt Curr	Input Freq
Formula		In Use	Default View
Formula For Calc Drill Down		Formula Type	Retain NP Data
		Formula	Input View Adj
		Formula For Calc Drill Down	No Data Adj View
			No Data NonAdj View
			Cons View
			Use Cube FX
			Rate Rev
			Rate Assets
			Clear On Calc
			Use 2 Pass Elim
			Formula
			Formula For Calc Drill Down

Optional Properties

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

Option	Purpose
Exclude Shared Descendants	Master hierarchy only – first instance of a child from hierarchy top down
Include Orphans	Child members without a parent
Include Varying Properties	On a separate v-suffixed sheet, varying properties by Time and Scenario
Include Statistics (slower)	Shared, Children count, Parents count (requires shared member status), generation depth, delimited hierarchy path
Include Constraints	Constraints and Defaults in Accounts, Entity, Flow, and U1
Include Less Used Items	Additional properties by dimension type. See its section for full details.

Additional Properties Fields

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

Accounts	Entity	Flow	Scenario	UD
Flow Constraint	Sibling Consolidation Pass	Shared	Shared	UD2 Constraint
IC Constraint	Sibling Repeat Calc Pass	#Children	#Children	UD3 Constraint
IC Member Filter	Auto Translation Currencies	#Parents	#Parents	UD4 Constraint
UD1 Constraint	Flow Constraint	Depth(s)	Depth(s)	UD5 Constraint
UD2 Constraint	IC Constraint	Path(s)	Path(s)	UD6 Constraint
UD3 Constraint	IC Member Filter			UD7 Constraint
UD4 Constraint	UD1 Constraint			UD8 Constraint
UD5 Constraint	UD2 Constraint			UD2 Default
UD6 Constraint	UD3 Constraint			UD3 Default
UD7 Constraint	UD4 Constraint			UD4 Default
UD8 Constraint	UD5 Constraint			UD5 Default
Shared	UD6 Constraint			UD6 Default
#Children	UD7 Constraint			UD7 Default
#Parents	UD8 Constraint			UD8 Default
Depth(s)	UD1 Default			Shared
Path(s)	UD2 Default			#Children
	UD3 Default			#Parents
	UD4 Default			Depth(s)
	UD5 Default			Path(s)
	UD6 Default
	UD7 Default
	UD8 Default
	Shared
	#Children
	#Parents
	Depth(s)
	Path(s)

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.

Properties
Dimension
Member
Varying Property
Time
Cube Type
Scenario Type
Value
Action Type
Action Value

# 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