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.
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
- Go through the read me sheet and the video to understand the working of this Utility. (You can download the video from here)
- Use a single dimension approach (one dimension at a time) if you are using this tool.
- This utility support up-to 17 generations, if you have more please let me know.
- Please make sure that data entered is in correspondence with the header of each input sheet.For example
- Each input sheet (expect Time Balance) every even column is the property. Don’t enter member name in this one.
- Enable property input window only if you are using it.
- For Alias Input drag all field called “name” followed by “Alias – Value” from application_Map (For Dimensions and Members)
- For Data Storage Input drag all field called “name” followed by “DataStorage” from application_Map (For Dimensions and Members)
- For Member Formula Input drag all field called “name” followed by “MemberFormula” from application_Map (For Dimensions and Members)
- For Variance Reporting Input drag all field called “name” followed by “VarianceReporting” from application_Map (For Dimensions and Members)
- For Time Balance Input drag all field called “name” followed by “TimeBalance” followed by “SkipValue” from application_Map (For Dimensions and Members)
- For Two Pass Calc Input drag all field called “name” followed by “TwoPassCalc” from application_Map (For Dimensions and Members)
- If any property column is missing add a blank column after “name”
- 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)
- 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
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
Hi Ankur
It is not available in Editor, but it is there
http://download.oracle.com/docs/cd/E17236_01/epm.1112/esb_tech_ref/frameset.htm?maxl_expotl.html
You can just use the syntax given in the EXCEL, or you can go through the above link for more details
Nice Blog Celvin..
I have used Applied OLAP Essbase extractor.
Can u provide me the link to Excel based Outline Extractor utility
Regards
Mahesh Balla.
Oracle
Hi Mahesh, there is a link given in the blog itself at the end (go with this one)
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
Hi Celvin,
The online folder link is back up and running. Thanks very much. This approach is very helpful.
-Arun
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();
}
}
}
@umar thanks for posting. Let me try that one and we can talk later on developing something on this.
Thanks Celvin , let me know
@umar Ive started updating your code 🙂
I'll send it once done. send me a mail from your id
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!
@ramesh xml file is created on the EAS server, check whether it is there on the server.
Hi Celvin,
I am getting compile error in hidden module :Parent _Child.
Can you please help me out.
Thanks,
Yamini
I would look at Next Gen Outline Outline extractor http://www.appliedolap.com/resources/downloads/next-generation-outline-extractor