When I published the latest version of Essbase Member Operations utility, I received a comment from Kumar Ramaiyer. If you don’t know Kumar Ramaiyer is a Vice President at Oracle, who looks after Essbase development. Kumar’s comment was about using an XML to perform the same operations in EsbMemberOperation utility.
I happen to remember a blog post from Harry Gates (I got short term memory 😉) about XML editing is only available in API and replied to Kumar saying “It can be accessed through API only”. In mind, I was thinking what difference it is going to make if I do it using “A” or “B” if the way is the same. However, I still thought of exploring the option, and the result is this blog.
Now to get the details my google-fu was tested. Finally, I got hold of new release feature link where this was the first time (maybe the last time too) where it gets a mention.
<insert rant begin>
If you think that there is an option called import outline using XML hiding in MaxL, you are so wrong. As I said earlier updating outline using an XML file is only available as an API method.
If you think that you can use the XML from export outline MaxL, you are wrong again. They are a lot different. Now I get that the XML for updating and exporting cannot be the same because update XML needs extra attributes and nodes. However, can’t they be somewhat similar?
Here is what you get using export outline MaxL.
Here is what an update outline XML looks like.
Why can’t they follow a similar method, You could then easily use that to create an edit XML file by adding the edit actions!!!
If you think that there is documentation available on creating the XML file, you are somewhat wrong again. There is an example available here, and there is an XSD file available in the bin folder for you to explore!!!
<insert rant over>
Now that we are done with my whining 😊 let’s get to work. I’m not going to cover all the attributes (read properties) or creating a new dimension in this blog post. However, I plan to cover enough so that you can easily use that information to continue on your own.
“Teach to fish”.
Before we start creating the XML, we need to understand mbredit.xsd file. Here is what the XSD file looks like.
<?xml version="1.0" encoding="utf-8"?>
<xs:schema targetNamespace="mbredit" elementFormDefault="qualified" attributeFormDefault="unqualified"
xmlns:otl="mbredit" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<xs:simpleType name="datastorage_t">
<xs:restriction base="xs:token">
<xs:enumeration value="storeData" />
<xs:enumeration value="dynamicStore" />
<xs:enumeration value="dynamic" />
<xs:enumeration value="neverShare" />
<xs:enumeration value="labelOnly" />
<!--"shared" only valid for unique name oultine to set it as a shared member, non-unique name outline ignored it refer to "prototypeMbr"-->
<xs:enumeration value="shared" />
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="consolidation_t">
<xs:restriction base="xs:token">
<xs:enumeration value="+" />
<xs:enumeration value="-" />
<xs:enumeration value="/" />
<xs:enumeration value="%" />
<xs:enumeration value="*" />
<xs:enumeration value="^" />
<xs:enumeration value="~" />
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="currencyconversiontype_t">
<xs:restriction base="xs:token">
<xs:enumeration value="none" />
<xs:enumeration value="category" />
<xs:enumeration value="noConversion" />
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="hierarchytype_t">
<xs:restriction base="xs:token">
<xs:enumeration value="stored" />
<xs:enumeration value="dynamic" />
<xs:enumeration value="multiHierarchy"/>
</xs:restriction>
</xs:simpleType>
<xs:complexType name="currencyconversion_t">
<xs:attribute name="conversionType" type="otl:currencyconversiontype_t" use="required"/>
<xs:attribute name="currencyMbrName" type="xs:string" use="optional" />
</xs:complexType>
<xs:complexType name="mbrinfo_t">
<xs:sequence minOccurs="0" maxOccurs="unbounded">
<xs:element name="category" type="otl:category_t" minOccurs="0" maxOccurs="1" nillable="true" />
<xs:element name="attDataType" type="otl:attrdatatype_t" minOccurs="0" maxOccurs="1" nillable="true" />
<xs:element name="dataStorage" type="otl:datastorage_t" minOccurs="0" maxOccurs="1" nillable="true" />
<xs:element name="timeBalance" type="otl:timebalance_t" minOccurs="0" maxOccurs="1" nillable="true" />
<xs:element name="skip" type="otl:skip_t" minOccurs="0" maxOccurs="1" nillable="true" />
<xs:element name="consolidation" type="otl:consolidation_t" minOccurs="0" maxOccurs="1" nillable="true" />
<xs:element name="currencyConversion" type="otl:currencyconversion_t" minOccurs="0" maxOccurs="1" nillable="true" />
<xs:element name="hierarchyType" type="otl:hierarchytype_t" minOccurs="0" maxOccurs="1" nillable="true" />
<!--prototypeMbr is used to set shared member for non-unique name outline, specifies the prototype member-->
<xs:element name="prototypeMbr" type="otl:mbrlocator_t" minOccurs="0" maxOccurs="1" nillable="true" />
<xs:element name="solveOrder" type="xs:unsignedByte" minOccurs="0" maxOccurs="1" />
<xs:element name="alias" type="otl:alias_t" minOccurs="0" maxOccurs="32" nillable="true" />
<xs:element name="formula" type="xs:string" minOccurs="0" maxOccurs="1" nillable="true" />
<xs:element name="udas" type="otl:uda_t" minOccurs="0" maxOccurs="unbounded" nillable="true" />
<xs:element name="mbrId" type="otl:mbrlocator_t" minOccurs="0" maxOccurs="1" nillable="true" />
<xs:element name="aggLevelUsage" type="otl:agglevelusage_t" minOccurs="0" maxOccurs="1" nillable="true" />
<xs:element name="type" type="otl:mbrtype_t" minOccurs="0" maxOccurs="1" nillable="true" />
<xs:element name="commentEx" type="xs:string" maxOccurs="1" minOccurs="0" nillable="true" />
<xs:element name="comment" type="xs:string" maxOccurs="1" minOccurs="0" nillable="true" />
<xs:element name="formatStr" type="xs:string" minOccurs="0" maxOccurs="1" nillable="true" />
</xs:sequence>
<xs:attribute name="twoPassCalc" type="xs:boolean" use="optional"/>
<xs:attribute name="flow" type="xs:boolean" use="optional" />
<xs:attribute name="relDesc" type="xs:boolean" use="optional"/>
<xs:attribute name="expense" type="xs:boolean" use="optional"/>
</xs:complexType>
<xs:complexType name="alias_t">
<xs:sequence />
<xs:attribute name="aliasTable" type="xs:token" use="optional" default="default" />
<xs:attribute name="alias" type="otl:mbrlocator_t" use="required"/>
</xs:complexType>
<xs:complexType name="uda_t">
<xs:sequence />
<xs:attribute name="replace" type="xs:boolean" use="optional" default="false"/>
<xs:attribute name="name" type="otl:mbrlocator_t" />
</xs:complexType>
<xs:complexType name="mbrdelete_t">
<xs:sequence />
<xs:attribute name="thisMbr" type="otl:mbrlocator_t" use="required" />
</xs:complexType>
<xs:complexType name="mbradd_t">
<xs:sequence>
<xs:element name="mbrInfo" type="otl:mbrinfo_t" minOccurs="0" maxOccurs="1" nillable="true" />
</xs:sequence>
<xs:attribute name="mbrName" type="otl:mbrlocator_t" use="required" />
<xs:attribute name="parent" type="otl:mbrlocator_t" use="required" />
<xs:attribute name="preSibling" type="otl:mbrlocator_t" use="optional" />
</xs:complexType>
<xs:complexType name="mbrmove_t">
<xs:sequence />
<xs:attribute name="thisMbr" type="otl:mbrlocator_t" use="required" />
<xs:attribute name="parent" type="otl:mbrlocator_t" use="required" />
<xs:attribute name="preSibling" type="otl:mbrlocator_t" use="optional" />
</xs:complexType>
<xs:complexType name="mbrrename_t">
<xs:sequence />
<xs:attribute name="thisMbr" type="otl:mbrlocator_t" use="required" />
<xs:attribute name="mbrName" type="otl:mbrlocator_t" use="required" />
</xs:complexType>
<xs:complexType name="mbrupdate_t">
<xs:sequence>
<xs:element name="mbrInfo" type="otl:mbrinfo_t" minOccurs="0" maxOccurs="1" />
</xs:sequence>
<xs:attribute name="thisMbr" type="otl:mbrlocator_t" use="required" />
</xs:complexType>
<xs:complexType name="mbrassociate_t">
<xs:attribute name="thisMbr" type="otl:mbrlocator_t" use="required" />
<xs:attribute name="attrDim" type="otl:mbrlocator_t" use="required" />
<xs:attribute name="attrMbr" type="otl:mbrlocator_t" use="required" />
</xs:complexType>
<xs:complexType name="dimassociate_t">
<xs:attribute name="dimName" type="otl:mbrlocator_t" use="required" />
<xs:attribute name="attrDim" type="otl:mbrlocator_t" use="required" />
</xs:complexType>
<xs:group name="edit_actions">
<xs:choice>
<xs:element name="otlUpdate" type="otl:outline_t"/>
<xs:element name="mbrUpdate" type="otl:mbrupdate_t" />
<xs:element name="mbrAdd" type="otl:mbradd_t" />
<xs:element name="mbrDelete" type="otl:mbrdelete_t" />
<xs:element name="mbrRename" type="otl:mbrrename_t" />
<xs:element name="mbrMove" type="otl:mbrmove_t" />
<xs:element name="mbrAssoc" type="otl:mbrassociate_t" />
<xs:element name="dimAdd" type="otl:dimadd_t" />
<xs:element name="dimUpdate" type="otl:dimupdate_t" />
<xs:element name="dimAssoc" type="otl:dimassociate_t" />
<xs:element name="markForDelete" type="otl:markForDelete_t"/>
<xs:element name="deleteMarked" type="otl:deleteMarked_t"/>
</xs:choice>
</xs:group>
<xs:simpleType name="timebalance_t">
<xs:restriction base="xs:token">
<xs:enumeration value="none" />
<xs:enumeration value="first" />
<xs:enumeration value="last" />
<xs:enumeration value="avg" />
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="skip_t">
<xs:restriction base="xs:token">
<xs:enumeration value="none" />
<xs:enumeration value="missing" />
<xs:enumeration value="zero" />
<xs:enumeration value="both" />
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="dimstorage_t">
<xs:restriction base="xs:token">
<xs:enumeration value="dense" />
<xs:enumeration value="sparse" />
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="category_t">
<xs:restriction base="xs:token">
<xs:enumeration value="account" />
<xs:enumeration value="attribute" />
<xs:enumeration value="country" />
<xs:enumeration value="curpartition" />
<xs:enumeration value="none" />
<xs:enumeration value="time" />
<xs:enumeration value="currencytype" />
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="dimstorecategory_t">
<xs:restriction base="xs:token">
<xs:enumeration value="account" />
<xs:enumeration value="attribute" />
<xs:enumeration value="busunit" />
<xs:enumeration value="customer" />
<xs:enumeration value="distribution" />
<xs:enumeration value="geography" />
<xs:enumeration value="market" />
<xs:enumeration value="organization" />
<xs:enumeration value="other" />
<xs:enumeration value="product" />
<xs:enumeration value="scenario" />
<xs:enumeration value="time" />
<xs:enumeration value="units" />
</xs:restriction>
</xs:simpleType>
<xs:complexType name="dimproperty_t">
<xs:sequence minOccurs="0" maxOccurs="unbounded">
<xs:element name="category" type="otl:category_t" minOccurs="0" maxOccurs="1" nillable="true" />
<xs:element name="storage" type="otl:dimstorage_t" maxOccurs="1" minOccurs="0" nillable="true" />
<xs:element name="storageCategory" type="otl:dimstorecategory_t" maxOccurs="1" minOccurs="0" nillable="true" />
<xs:element name="dimSolveorder" type="xs:unsignedByte" minOccurs="0" maxOccurs="1" nillable="true" />
<xs:element name="attDataType" type="otl:attrdatatype_t" minOccurs="0" maxOccurs="1" nillable="true" />
<xs:element name="genName" type="otl:genlevname_t" minOccurs="0" nillable="true" maxOccurs="unbounded" />
<xs:element name="levName" type="otl:genlevname_t" minOccurs="0" nillable="true" maxOccurs="unbounded" />
</xs:sequence>
<xs:attribute name="haEnable" type="xs:boolean" use="optional" default="false"/>
<xs:attribute name="independent" type="xs:boolean" use="optional" default="false" />
<xs:attribute name="nameUnique" type="xs:boolean" use="optional" default="false"/>
<xs:attribute name="compression" type="xs:boolean" use="optional" />
</xs:complexType>
<xs:simpleType name="agglevelusage_t">
<xs:restriction base="xs:token">
<xs:enumeration value="default" />
<xs:enumeration value="none" />
<xs:enumeration value="bottom" />
<xs:enumeration value="top" />
<xs:enumeration value="bottomTop" />
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="mbrtype_t">
<xs:restriction base="xs:token">
<xs:enumeration value="numeric" />
<xs:enumeration value="text" />
<xs:enumeration value="date" />
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="attrdatatype_t">
<xs:restriction base="xs:token">
<xs:enumeration value="bool" />
<xs:enumeration value="string" />
<xs:enumeration value="datetime" />
<xs:enumeration value="double" />
</xs:restriction>
</xs:simpleType>
<xs:complexType name="dimadd_t">
<xs:sequence>
<xs:element name="properties" type="otl:dimproperty_t" minOccurs="0" maxOccurs="1" nillable="true" />
</xs:sequence>
<xs:attribute name="dimName" type="otl:mbrlocator_t" use="required" />
<xs:attribute name="preSibling" type="otl:mbrlocator_t" use="optional" />
</xs:complexType>
<xs:simpleType name="mbrlocator_t">
<xs:restriction base="xs:string">
</xs:restriction>
</xs:simpleType>
<xs:complexType name="genlevname_t">
<xs:sequence />
<xs:attribute name="name" type="otl:mbrlocator_t" use="required" />
<xs:attribute name="number" type="xs:unsignedShort" use="required" />
<xs:attribute name="nameUnique" type="xs:boolean" default="true" use="optional" />
</xs:complexType>
<xs:complexType name="dimupdate_t">
<xs:sequence>
<xs:element name="properties" type="otl:dimproperty_t" />
</xs:sequence>
<xs:attribute name="dimName" type="otl:mbrlocator_t" use="required" />
</xs:complexType>
<xs:complexType name="dtsmbr_t">
<xs:sequence>
<xs:element name="dtsAlias" type="otl:alias_t" maxOccurs="unbounded" minOccurs="0" nillable="true" />
</xs:sequence>
<xs:attribute name="mbrName" type="otl:mbrlocator_t" use="required" />
<xs:attribute name="number" type="xs:unsignedShort" use="required" />
<xs:attribute name="enable" type="xs:boolean" use="optional" default="true"/>
</xs:complexType>
<xs:complexType name="markForDelete_t">
<xs:attribute name="dimName" type="otl:mbrlocator_t" use="required" />
</xs:complexType>
<xs:complexType name="deleteMarked_t">
<xs:attribute name="dimName" type="otl:mbrlocator_t" use="required" />
</xs:complexType>
<xs:complexType name="outline_t">
<xs:sequence minOccurs="0" maxOccurs="1">
<xs:element name="impliedShareSetting" maxOccurs="1" minOccurs="0" nillable="true" >
<xs:simpleType>
<xs:restriction base="xs:token">
<xs:enumeration value="default" />
<xs:enumeration value="defaullOn" />
<xs:enumeration value="defaultOff" />
<xs:enumeration value="forceOn" />
<xs:enumeration value="forceOff" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name="dateFormatValue" type="xs:string" maxOccurs="1" minOccurs="0" nillable="true" />
<xs:element name="aliasTable" type="xs:token" maxOccurs="32" minOccurs="0" nillable="true" />
<xs:element name="dtsMbr" type="otl:dtsmbr_t" maxOccurs="unbounded" minOccurs="0" nillable="true" />
</xs:sequence>
<xs:attribute name="caseSensitive" type="xs:boolean" default="false" use="optional" />
<xs:attribute name="enableMemberType" type="xs:boolean" default="false" use="optional" />
</xs:complexType>
<xs:element name="otlEditMain">
<xs:complexType>
<xs:sequence>
<xs:group ref="otl:edit_actions" minOccurs="0" maxOccurs="unbounded" />
</xs:sequence>
<!-- if specified otlVersion number is -1, outline edit skip version number check and proceed but reset outline version number to 0-->
<xs:attribute name="otlVersion" type="xs:integer" use="required" />
</xs:complexType>
</xs:element>
</xs:schema>
Reading XSD (or should I say reading the XML using XSD)
The way you should look at the XSD is by looking at the very bottom; it starts with otlEditMain and references edit_actions group.
Now let’s look at edit actions. We got the following actions.
- Outline Update (this is nice. It allows you to add Alias tables, change DTS options, and many more)
- Member Update
- Member Add
- Member Delete
- Member Rename
- Member Move
- Member Association (Attribute)
- Dimension Add (nice one)
- Dimension Update
- Dimension Association (Attribute)
- Mark for deletion
- Delete marked items
Outline Update
For outline update, it looks at the outline_t element, and you can change the following properties of an outline.
- Implied Share setting
- Date format
- Add alias tables
- Change DTS
- Update case sensitivity
- Enable Member type (This is used for Text Lists and String formatting)
If you look at implied share, it accepts the following values.
- Default
- Default On
- Default Off
- Force On
- Force Off
Member Add
Member add looks at the mbradd_t element and uses three attributes (two mandatory and one optional) and a child element.
You need to tell which member you are adding using mbrName attribute, use parent attribute for adding this member under a parent and finally you can decide the position of this member in the outline using preSibling attribute. preSibling is an optional attribute.
Once done you must add the child element attributes using mbrInfo. Now, mbrInfo, in turn, looks at the mbrinfo_t element, and this is a massive element in its own. If you look at the dataStorage element, it looks at datastorage_t for values.
One thing to keep in mind “shared” tag is for tagging shared members in a regular outline, for adding shared members in a non-unique (read duplicate members allowed outline) use prototypeMbr instead.
I’ve no idea how a shared member can be created using the XML file.
No matter what I do, it all results in an error, here is the thing prototypeMbr comes under mbrInfo and mbrInfo can be used with mbrAdd and mbrUpdate tags, now I’m adding this as a new member I’ve to use mbrAdd then I get this error.
I did try using the UNIQUE name to add the member; there is something that I’m missing, I’ve no idea what it is.
After a lot of tries, I reached out to Kumar and team, and they were gracious enough to help me with the syntax. Apparently, there is an issue with the documentation, the doc uses mbrAdd in a wrong way (it works for regular members, however breaks for shared ones), and that was throwing me off. I updated my syntax, and it started working.
I’ve no idea where commentEx is used as it is not visible from EAS. Same applies for mbrId, you can add a number for that, and it’ll take it. I have got a feeling that PBCS might use this later (remember those member GUIDs from metadata extract.
Member Update
Member update looks at the mbrupdate_t element, and it follows an attribute and a child element.
You need first to tell which member you are updating using the thisMbr tag and then use mbrInfo element we saw earlier to update the properties.
Where you see mbrlocator_t, replace that with the member names/udas/attribute members.
To update a shared member, you need to use fully qualified name of the shared member. Even though the outline is not enabled for duplicate members, you might have a member shared twice and therefore fully qualified name.
Just keep following the same pattern and without knowing you learned how to read the XSD!!!
I bet you might be thinking, this is all great. However, can you create a utility to generate this XML file? That’s what I did, and I’ll publish the Groovy code as another post :), this one is a bit longer than I thought.
I have a hunch that this is what OAC’s Cube Designer is using (I bet it is using this approach, and maybe an incremental build using XML file).
