Excel based Essbase outline extractor 14

This entry was posted in Essbase Outline Extract Utilities on by (updated 1905 days ago)
Before I write anything on what this blog is about, I think I should talk about the history of this one. (It’ll cover what this blog is about :))
I thought of writing this one and saved it as a draft when “Export Outline” MaxL was introduced in 11.1.2 version and then “this blog” was in draft state for a long time (more than 5 months) and one fine day a friend of mine (Mihriban) reminded me about the functionality and I said “Yes you can extract the outline as an XML file”. I thought “Why not give it a try and publish this one.”
I tried to extract Sample Basic outline and I used EXCEL to open the XML file and it gave me a columnar format of the outline. (I was under an impression that xml file opens up as Parent Child format).
I was thinking that “There is nothing more to write, other than fire the MaxL and open it in EXCEL”.
So “this blog” was shelved again 🙂
3 weeks before (this draft was updated in mid Jan, so in December 2011) I had to use the MaxL to extract an outline (I couldn’t install  Outline Extractor because of user permissions issues) and was pretty confident that I could do it in fraction of seconds. (Let me be honest it took an entire week to make this Macro). This is the only blog which took me such a long time to finish 🙂
After extraction I opened the xml file and to my surprise (horror) it was not in Parent Child format. I tried to create a macro which will make it in Parent Child format and couldn’t create one.
So I deleted “this blog” and was sad that I won’t be able to write “this blog” at all. After 2 days of disappointment I thought of working on this again (at last I succeeded in getting a logic) and was able to come up with this utility. So that’s the history of “this blog”.
Before continuing
 
If you can use Outline Extractor or Razza (not sure whether many of you have this) it is advisable to do so.
Disclaimer:

This is an EXCEL oriented tool and takes a long time (around 4 ~ 5 minutes for a large dimension) to get the desired result. (I’ve not included UDA and Attribute information into this, maybe in the next version :))
This utility follows a single dimension approach (only one dimension information can be created at a time) More manual steps are involved to get the required output.

 

Key Points
  1. Go through the read me sheet and the video to understand the working of this Utility. (You can download the video from here)
  2. Use a single dimension approach (one dimension at a time) if you are using this tool.
  3. This utility support up-to 17 generations, if you have more please let me know.
  4. Please make sure that data entered is in correspondence with the header of each input sheet.
    For example
    1. Each input sheet (expect Time Balance) every even column is the property. Don’t enter member name in this one.
    2. Enable property input window only if you are using it.
    3. For Alias Input drag all field called “name” followed by “Alias – Value” from application_Map (For Dimensions and Members)
    4. For Data Storage Input drag all field called “name” followed by “DataStorage” from application_Map (For Dimensions and Members)
    5. For Member Formula Input drag all field called “name” followed by “MemberFormula” from application_Map (For Dimensions and Members)
    6. For Variance Reporting Input drag all field called “name” followed by “VarianceReporting” from application_Map (For Dimensions and Members)
    7. For Time Balance Input drag all field called “name” followed by “TimeBalance” followed by “SkipValue” from application_Map (For Dimensions and Members)
    8. For Two Pass Calc Input drag all field called “name” followed by “TwoPassCalc” from application_Map (For Dimensions and Members)
  5. If any property column is missing add a blank column after “name”
  6. If you wish you load the information into Essbase make sure that member formula is in a single line. (You can use Notepad ++ to do a find and replace)
  7. Clear the Member Input sheet and Properties sheet after use

I know it is time consuming, but if you don’t have any other option, go with this one 🙂

HTH

About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with “Intekgrate Corporation” based out of Aurora office. I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

14 thoughts on “Excel based Essbase outline extractor”

  • Reply ↓
    Passion is the Key...

    Brother…

    I am using the same version 11.1.2 but I don't see the maxl command "export outline"? I did not go through the entire blog, since it was stating about "Outline Extractor" which I was aware of.

    How do we use this if it is not available as the option in the MaxL editor?

    Cheers
    Ankur Jain

  • Reply ↓
    Arun Pandiyan

    Hi Celvin,

    I'm unable to download the outline extractor excel file. The onlinefolder link is not available. Can you please share the link to download this file. I'm in the same position as you i.e. unable to install outline extractor due to permission issues. Your help on this matter would be much appreciated.

    Thanks,
    Arun

  • Reply ↓
    umar ayoob

    You can do this using the below method

    Maxl to export the outline into an xml file

    export outline sample.basic all dimensions to xml_file "c:/temp/test.xml";

    Java To create a parent-child format file from the above xml file

    import javax.xml.parsers.DocumentBuilderFactory;
    import javax.xml.parsers.DocumentBuilder;
    import org.w3c.dom.Document;
    import org.w3c.dom.NodeList;

    import org.w3c.dom.Element;

    import java.io.BufferedWriter;
    import java.io.File;

    import java.io.*;

    public class outline1 {

    public static void main(String argv[]) {

    try
    {

    File fXmlFile = new File("test.xml");;
    DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
    DocumentBuilder dBuilder = dbFactory.newDocumentBuilder();
    Document doc = dBuilder.parse(fXmlFile);
    doc.getDocumentElement().normalize();

    BufferedWriter out = new BufferedWriter(new FileWriter("Test1.txt"));

    NodeList AppList = doc.getElementsByTagName("Member") ;
    for(int i= 0; i <AppList.getLength() ; i ++)
    {
    Element MbrElement = (Element) AppList.item(i);
    System.out.println(MbrElement.getAttribute("name"));
    Element PMbrElement = (Element) MbrElement.getParentNode();
    NodeList AliasNodes = MbrElement.getElementsByTagName("Alias");
    Element AliasEle = (Element) AliasNodes.item(0);
    if (AliasNodes.getLength() != 0 )
    {
    out.write( PMbrElement.getAttribute("name") + "|" + MbrElement.getAttribute("name") + "|" + AliasEle.getTextContent() + "|" + MbrElement.getAttribute("DataStorage"));
    out.newLine();
    }
    else
    {
    out.write( PMbrElement.getAttribute("name") + "|" + MbrElement.getAttribute("name") + "|" + "" + "|" + MbrElement.getAttribute("DataStorage"));

    out.newLine();
    }
    }
    out.close();
    }

    catch (Exception e) {
    e.printStackTrace();
    }

    }

    }

  • Reply ↓
    Ramesh

    Hello,
    I really liked this utility and i wanted to try but for some reason my .xml file is not getting created. the maxl execution is successful. what potentially could be causing that?

    Thanks for the reply!