epma 101 loading dim members via flat file

7/30/2019 Epma 101 Loading Dim Members via Flat File

1/14

EPMA 101: Loading Dimensions via Flat File

Now that Ive created a standard EPMA Planning application, I need to build out some of the dimensionality. For this post, I

going to build the S_Product1 shared dimension via flat file.

Housekeeping

Before I jump in, I need to take care of a few housekeeping items:

1. Ensure that the appropriate EPMA, Essbase, and Planning Windows services have been started2. Verify that I have security to modify EPMA dimensions

Note: I have to do step 1 because I am running on a VMWare and I do not have enough resources on my laptop to run

everything by default.

First, I check to make sure the necessary Windows services are running. I do this on my operating system, through Start |

Administrative Tools | Services. There are 8 total EPMA services, 1 Planning service, and 1 Essbase service, and a number of

other foundation services required to make it all work (remember that Im working off of v11.1.1.3). Everything looks goo

to go.

Second, I verify that I have correct security for modifying EPMA dimensions. FYI - I am going to use a new native ID in Shared

Services that I just created to run through these steps. The EPMA admin documentation states that you need a minimum of

the Dimension Editor global role in order to modify and create shared dimensions. I have provisioned only that role to my n

ID.

Now that my housekeeping tasks are complete, I am going to walk through the steps to loading a dimension via flat file. Sin

my shared dimensions have already been created (please refer to thispostfor a refresher), there are 4 tasks required for th

1. Create the load file2. Create a profile3. Load the file and validate the load4. Redeploy the application

Step One: Create the Load File

First, I need to create the dimension file. There are a bunch of options here. I can create a file that loads just the one dimen

or any number of dimensions for EPMA. Another choice I can create this file to update existing dimensions or to create ne

dimensions. Since were taking baby steps, I am going to create this to update 1 single shared dimension in EPMA.

According to the EPMA admin guide, EPMA supports .ads file imports with special encoding (whatever that means). The file

can contain the following sections, which can be ordered in any manner:

Dimensions Dimension Associations Members

Hierarchies
http://thetravelingconsultant2.wordpress.com/2010/02/12/epma-101-creating-an-epma-planning-app-part-i/http://thetravelingconsultant2.wordpress.com/2010/02/12/epma-101-creating-an-epma-planning-app-part-i/http://thetravelingconsultant2.wordpress.com/2010/02/12/epma-101-creating-an-epma-planning-app-part-i/http://thetravelingconsultant2.wordpress.com/2010/02/12/epma-101-creating-an-epma-planning-app-part-i/


2/14

Property ArrayGeneral notes about .ads file syntax:

There has to be at least one section in the file Each section is marked with an exclamation point and a valid section heading The first line under each section starts with an apostrophe and defines the fields for that section Valid field delimiters include semicolons, commas, pipes, spaces, and tabs True values for member properties are represented by Y and false values are represented by N Property names (attribute dimensions, UDAs, etc.) cannot contain spaces #root is used to denote the top dimension member

The Dimensions section:

Purpose: defines dimension to be imported/updatedo This section is requiredo If the dimension does not exist, it will be created based on the specifications of this sectiono If the dimension already exists, this section will update its current properties

Section header: !Section=Dimensionso The section header must match EXACTLY what is written above

Field headers: Name|DimensionClass|DimensionProperty1|DimensionPropertyNo The field list doesnt have to include every dimension property and the field headers dont have to match th

EPMA property names exactly; these can be mapped in the profile

Dimensions section examples (using pipes as a delimiter):

!Section=Dimensions'Name|DimensionClass|DimensionAliasS_Product1|Generic|Products

!Section=Dimensions'Name|DimensionClass|DimAlias|DimStorage

S_Product1|Generic|Products|NeverShare

The Dimension Associations section:

Purpose: defines associations between dimensionso This section is requiredfor new dimensions and when using replace mode with existing dimensionso This section is optionalif using merge mode with existing dimensions

Section header: !Section=DimensionAssociationso If this section is going to be used, the section header must match EXACTLY what is written above

Field headers: BaseDimension|Property|TargetDimensiono Field headers must match EXACTLY what you see above

Dimension Associations section example (using pipes as a delimiter):

!Section=DimensionAssociations'BaseDimension|Property|TargetDimensionCurrency|SecurityClass|S_SecurityClassDimensionS_Scenario|Alias|S_AliasAccount|UDA|S_UDA

The Members section:

Purpose: defines dimension members and their propertieso This section is optional


3/14

o Relationship member properties cannot be defined here because they require a parent child relationshipo Member property values can also be defined in the Hierarchies and Property Array sections

Section header: !Members=DimensionNameo The section header must match EXACTLY what you see above, but with the appropriate dimension name

inserted

Field headers: Name;MemberProperty1;MemberPropertyNo The field list doesnt have to include every property and the field headers dont have to match the EPMA

property names exactly; these can be mapped in the profile

Members section examples (using semicolons as a delimiter):

Note: this came straight from the admin guide and seem to follow HFM properties

!Members=S_Account'Name;ConsolidationAccountType;IsCalculated;IsConsolidated;PlugAccount;Custom1TopMemberCustom2TopMember;Custom3TopMember;Custom4TopMember;NumDecimalPlaces;EnableCustom1Aggr;EableCustom2Aggr;EnableCustom3Aggr;EnableCustom4Aggr;XBRLTags;ICPTopMember;IsICP;UsesLinItems;SecurityClass;EnableDataAudit;CalcAttributeAccount_[None];Revenue;N;N;;;;;;0;Y;Y;Y;Y;;;N;N;;N;ExchangeRates;GroupLabel;Y;Y;;;;;;0;Y;Y;Y;Y;;;N;N;;N;Plug;Revenue;N;Y;;;;;;0;Y;Y;Y;Y;;;N;N;;N;

The Hierarchies section:

Purpose: defines dimension members, their member and relationship properties, and hierarchical/parent-childrelationships, and controls member order

o This section is requiredfor new dimensions and when using replace mode for existing dimensionso This section is optionalfor existing dimensions that use the merge methodo If all members under a parent are present and replace mode is used, the members of an existing dimension

reordered

Section header: !Hierarchies=DimensionNameo The section header must match EXACTLY what you see above, but with the appropriate dimension name

inserted

Field headers: Parent,Child,Relationship_Property1,Relationship_PropertyNo The field list doesnt have to include every property and the field headers dont have to match the EPMA

property names exactly; these can be mapped in the profile

Hierarchies section example (using commas as the delimiter):

!Hierarchies=S_Product1'Parent,Child,IsPrimary,Alias=Default,UDA1#root,Total_Products,Y,Total Products,Total_Products,Products-A-F,Y,A-F Products,Total_Products,Products-G-K,Y,G-K Products,Total_Products,Products-L-R,Y,L-R Products,

Total_Products,Products-S-T,Y,S-T Products,Total_Products,Products-U-Z,Y,U-Z Products,TestUDA

The Property Arraysection:

Purpose: defines multi and single value properties for members of a dimensiono This section is optionalo If you choose to omit the property value, you are essentially deleting it for the associated membero If you choose to omit both the property key and value, you are erasing all properties for that property key

(alias, UDA, etc.) for the associated member

Section header: !PropertyArray=DimensionName


4/14

o The section header must match EXACTLY what you see above, but with the appropriate dimension nameinserted

Field headers: Property|Name|Key|Valueo Field headers must match EXACTLY what you see above

Property Array section example (using pipes as the delimiter):

!PropertyArray=S_Product1'Property|Name|Key|ValueAlias|Total_Products|Default|Total ProductsAlias|Products-A-F|Default|A-F ProductsAlias|Products-G-K|Default|G-K ProductsAlias|Products-L-R|Default|L-R ProductsAlias|Products-S-T|Default|S-T ProductsAlias|Products-U-Z|Default|U-Z ProductsUDA|Products-U-Z|TestUDA|

So what does all of this mean? How do I create my file?

Wellas the old adage goesthere are many ways to skin a cat. Based on the direction above, there are multiple ways for

creating a dimension load file. For now, I am going to keep things simple. I will utilize the Dimensions section (which is

required), Hierarchies section, and the Property Array section.

Now how did I come up with that decision?

The Dimensions section is required no way of getting around this The Dimension Associations section is not needed for this particular dimension since Ive already associated my

S_Products1 dimension with the Alias dimension (please see past blog), I dont have any more associations to make

The Members section is completely optional, and can be split out and pushed into other sections Since I chose to omit the Members section, the Hierarchies section is now required I will focus most of my membe

load information in this section

The Property Array section will be flexible for adding and deleting specific member properties later, but does notsatisfy my needs now. So I chose not to utilize this.

OK, so now I have my load file for the shared Products dimension. I have saved this locally on my machine and called it

S_Products1.ads. Ive also associated all .ads files with the Notepad program on my computer.

!Section=Dimensions'Name|DimensionClass|DimensionAlias|DimDataStorageS_Product1|Generic|Products|NeverShare

!Hierarchies=S_Product1'Parent|Child|DataStorage|Alias=Default|Plan1Aggregation#root|Total_Products|StoreData|Total Products|Total_Products|Products-A-F|StoreData||Products-A-F|AP001|StoreData||Products-A-F|AP002|StoreData||Products-A-F|AP003|StoreData||Products-A-F|AP004|StoreData||Products-A-F|BP001|StoreData||Products-A-F|CP001|StoreData||Products-A-F|DP001|StoreData||Products-A-F|DP002|StoreData||Products-A-F|EP001|StoreData||Products-A-F|FP001|StoreData||Products-A-F|FP002|StoreData||Total_Products|Products-G-K|StoreData||Products-G-K|GP001|StoreData||Products-G-K|GP002|StoreData||

Total_Products|Products-L-R|StoreData||


5/14

Products-L-R|LP001|StoreData||Products-L-R|LP002|StoreData||Products-L-R|PP001|StoreData||Products-L-R|RP001|StoreData||Products-L-R|RP002|StoreData||Total_Products|Products-S-T|StoreData||Products-S-T|SP001|StoreData||Products-S-T|SP002|StoreData||Products-S-T|SP003|StoreData||Products-S-T|TP001|StoreData||Total_Products|Products-U-Z|StoreData||

Products-U-Z|VP001|StoreData||Products-U-Z|ZP001|StoreData||#root|OlympicBrand|StoreData||~OlympicBrand|AP004|ShareData||OlympicBrand|DP001|ShareData||OlympicBrand|GP001|ShareData||OlympicBrand|SP003|ShareData||OlympicBrand|VP001|ShareData||

Note that Ive created a very simple dimension to get my feet wet. Ill progressively add more difficult dimensionality as this

blog progresses. Also note that Ive specifically chosen to list parents before children in the hierarchies section, to avoid any

snafus that might be created by that situation as well.

Now that my file is ready, I need to get it loaded!

What you didnt see:

Boy oh boy. Getting an .ads file to work using the bare minimum documentation Oracle has provided has wreaked some

serious havoc. My first set of files inevitably failed. So I ran through about 2 dozen tests trying to understand the logic behin

this process. I also read every section of the Oracle documentation on this (and ended up going with the trial and error

methodology since the documentation confused me more). Here are my notes:

Definitely start small and break the load into sections, at first, until you are comfortable with how the EPMA flat fileload process works.

Not all member properties work in all sections. This is not documented, and I cant figure out the rhyme or reasonbehind it. I have attempted to start some documentation on this below for Planning applications. Note: I will have tadd to this as I continue to test each type of dimension there are definitely more properties to be added to each

table.

The Property Array section must follow the field headers EXACTLYas I described (which were noted when I went bathrough and edited this blog post). For example, I originally tried Property|MemberName|Key|Valueand then

watched EPMA continuously error out. After several tries I figured out it that it didnt like MemberName as a field

header it needed to be just Name.

Whatever field headers you decide to use within the Hierarchies section REQUIREmappings in your import profile(this set of steps is discussed in Step Two). The Property Array section does not have this same requirement, maki

it more flexible for the properties it does support.

You can have the same section (pointing to a different dimension) listed several times in the same .ads fileSo far, in my testing to-date, Ive discovered that the Hierarchies section supports the following Planning properties:

Section syntax (using pipes as the delimiter):

*Note: the following non-italicized words are required as written

!Hierarchies=DimensionName

Parent|Child|MemberProperty1|MemberPropertyN

Hierarchy Planning Properties Tested to Date

EPMA Property Dimension Type Field Header Name Possible Values


6/14

n/a All Parent

n/a All Child

Alias All Alias=AliasTable

Default: n/a

Valid for Plan1 All MemberValidForPlan1 Y, N

Default:


Default:


Default:

Valid for Wrkforce All MemberValidForWorkforce Y, NDefault:

Valid for Capex All MemberValidForCapex Y, N

Default:

Aggregation for Plan1 All except

Currency

Plan1Aggregation +, -, *, /, %, ~, Never

Default: +


Currency

Plan2Aggregation +, -, *, /, %, ~, Never

Default: +


CurrencyPlan3Aggregation +, -, *, /, %, ~, Never

Default: +

Aggregation for Wrkforce All except

CurrencyWorkforceAggregation +, -, *, /, %, ~, Never

Default: ~

Aggregation for Capex All exceptCurrency

CapexAggregation +, -, *, /, %, ~, NeverDefault: ~

UDA All UDA

Default: n/a

Member Formula All MemberFormula

Default: n/a

Data Storage All DataStorage StoreData,

DynamicCalcAndStore,

DynamicCalc, NeverShare,

LabelOnly, ShareData

Default: NeverShare

Two Pass Calc All TwoPassCalc True, False

Default: False

Data Type All DataType Currency, NonCurrency,

Percentage, SmartList,

Date, Text, Unspecified

Default: Unspecified

And, Ive discovered that the Property Array section supports the following Planning properties:

Section syntax (using pipes as the delimiter):

*Note: the following non-italicized words are required as written

!PropertyArray=DimensionName

Property|Name|Key|Value

Property Array Planning Properties Tested to Date

Property Value Type Row Syntax Row Sample

Alias Multi Value Alias|MemberName|AliasTable|Value Alias|USD|Default|United States Dollar

UDA Multi Value UDA|MemberName|UDA| UDA|Entity1|TestUDA|

DataType Single Value DataType|MemberName||DataTypeValue DataType|Sales||Revenue

MemberFormula Single Value MemberFormula|MemberName||Formula MemberFormula|Net Income||

TwoPassCalc Single Value TwoPassCalc|MemberName||YorN TwoPassCalc|Qtr1||Y

Step Two: Create a Profile


7/14

In order to load my dimension file, I first need to create a profile. A profile is a mapping interface stored within EPMA that

shows it how to map your file to EPMA. This is a required step when using flat files or interface tables to import dimensions

First, I log into Workspace, then go to Navigate | Administer | Dimension Library.

I am taken immediately to the shared dimensions library. Next, I go to File | Import | Create Profile.

Now a new window opens up, asking me to define the new profile. So I enter a name (might as well call it the same as the

dimension), import type (in this case Flat File), description, and keep the Application field as Shared Library.


8/14

Note: If you want to delete a profile, within the shared dimensions library navigate to File | Import | Import Dimensions. Th

highlight the profile you wish to delete and press the trash icon in the upper right-hand side of the window.

Next, I browse to my S_Products1.ads file location and press Upload when prompted. Then I press OK. Next, Im aske

for my file properties. Since Im using pipe delimiters, I change the column delimiter to reflect this and uncheck the second

option, based on the contents of my load file.

Then I press Next. Now I am asked to select some mapping options between my flat file dimension and the shared

dimensions that already exist.

Since I am not creating any new dimensions (Im just updating an existing shared dimension), I leave the first optionunchecked.

The Select All Merge As Shared option will remain checked, as I want to merge my flat file dimension with theexisting shared dimension.

Shared Library this refers to the target dimension, and was auto-selected for me. No changes needed there. Merge/Replace - I will be merging my contents with those already in the shared dimension, so I keep the option set

Merge. Merge basically means that anything in the source file will overwrite or add to the target. And anything no

the source file (but in the target) will remain unchanged.

Note: Replace works similar to Merge in that additions and updates are made. But then any items that exist in the

target but not the source get removed.

Merge As Shared this is auto-selected if you select the Merge option. This is ignored when you select Replacewhen you insert the IsPrimary column into your file. Therefore, I will leave the default.

Note: When Merge As Shared is selected, the first occurrence of a member in your source file becomes the prima

member for shared hierarchies. If a primary already exists in the dimension, then all members in the source file

become shared members. De-selecting this option will move the primary occurrence of a member to the first paren

specified in the file.


9/14

I press Next. Now I am taken to the final set-up screen. Here I am asked to map properties from the source file to propertin the shared library.

If you look back at my file, you will see that I have defined 6 special properties in the Hierarchies section: DataStorage, Alias

Consolidation, and the Aggregation options for plan types 1-3. Whatever properties you select (and code properly in your fi

might add) should display in this screen.

If you need to override the mapping, you can do so by changing the Shared Library target mapping by selecting a different

option from the drop-down box, as shown below.


10/14

Since my mappings look correct, I review the next set of options: choosing to select the Remove Target Value For Missing

Source Properties checkboxes. The documentation describes this property as such: If Remove Target Value for Missing

Source Properties is selected, this option tells the import engine to perform the equivalent of a Remove Value operation o

any properties whose value in the source is empty. If not selected, empty property values are not processed. I interpret this

mean that anything I have not defined in the source will have its property removed. I do not want this, so I leave my

checkboxes unchecked.

I press Finish. After confirming that my import file was created successfully, I am given the option to immediately execute

the load. I press Yes.

Onto the load!

What you didnt see:

It took me about a dozen tries to get this profile and import to work without errors. Here is what I learned along the way (w

a little help from the documentation):

If you have already created your profile and you decide after the fact to add additional properties, you will have to eyour profile first to add the properties to the profile as well

To edit an existing profile, within the shared dimensions library navigate to File | Import | Import Dimensions. Thenhighlight the profile you wish to edit. Now upload your .ads file. Press the pencil icon in the upper right-hand corner

Although I am not entirely certain this detail is correct, I will write it anyways to save any frustration it might cause case it is true. I originally had my children listed before parents in my .ads file. I found this to cause some errors, so

then rearranged my Hierarchies section to put parents before children. This seemed to help.

The member formula screen within EPMA could use some work I could not find a way to validate formulas, forexample.

The Planning rules of engagement regarding plan types and dimensionality are going to be enforced through EPMIn another version of the .ads file I attempted to override which plan types were valid for this Products hierarchy, b

to no avail. For custom dimensions in Planning, remember that the descendants are valid for only the plan types the

dimension is actually valid for.

Dont forget your dimension associations! For instance, to test the UDA property for the Hierarchies section, I needto create a UDA dimension, associate it to my base dimension, then add in all the possible UDAs as children. I cou

not auto-create a UDA through my .ads file, which is similar behavior to Planning administration (but not Essbase). I

had to do these steps manually since I did not handle the dimension associate within my .ads file.

Sometimes EPMA acts a little wacky. Although Im barely scratching the surface with the actions in this blog post, I aseeing some strange behavior. For instance, when I ran the same import multiple times, my base and shared memb

started flipping parents.

There is no abort operation, it seems. If an error is encountered, EPMA continues loading. Therefore, if you miss aparent due to a syntax error, the children will not load, creating all sorts of havoc

You really, really, have to understand the correct possible values of the .ads sections. In the versions of the .ads filethat you did not see, the values in my Hierarchies section were not always correct and sometimes this was due to


11/14

not RTFM and sometimes this was due to the documentation being poor. I have documented the properties I have

actually tested with success so that you may learn from my mistakes.

Step Three: Load the File and Validate the Load

I receive a message that my job has been submitted.

I navigate to the Job Console to check the status of my job.

Although my job has completed, there is no guarantee that it was successful. I check the Import Errors attachment to find

out whether or not there were any errors. Woohoo!

I take a look at my new shared Products dimension to see if I get the expected results. Here is the before shot of the shared

dimension:


12/14

And here is the after shot:

And if I take a look at the dimensionality within my Essbase application, I see that the newly created hierarchies have been

auto-synched.


13/14

This is what I had hoped to accomplish with this small test load Im ready to deploy!

Step Four: Redeploy the Application

To deploy the application, I navigate back to the Application Library. If I select my application, the details show that it is out

sync with deployment:

So I redeploy by right-clicking on it and selecting Deploy | Application. Then I choose to refresh the outline only.

I wait for the application to deploy.

I check the job and verify that it was successful.


14/14

Next, I open EAS to confirm the same changes in the dimensionality there as well.

All looks good! OK, thats enough for nowstay tuned for other load options in the future

epma 101 loading dim members via flat file

Documents