February 2008 - Posts

• Comparing Essbase Outlines

  Comparing Essbase Outline files is a pretty easy task if you have the correct tools. I'm sure you can imagine it would be laborious to manually do this by opening two Outlines and comparing each Member by Member, not to mention easy to make a mistake! I do not know if any tool that does this out of the box, but if you do, then I am very happy to hear what it is and try it for myself. So I split this task into two: Extracting the Outline Comparing the Extract Extracting the Outline The tool I use is OlapUnderground Outline Extractor. It is a free tool and can be downloaded from here (make sure you download the correct version based on your Essbase install) It has support for extracting Outlines to XML which is my preferred choice as XML is brilliant for displaying hierarchical data. So, lets start by extracting the Sample->Basic Database Outline. Open OlapUnderground Outline Extractor Login to your Essbase Server and Select Sample->Basic Choose XML as your File Type Select your desired Export File format and Field Options Select <<All Dimensions>> (you can compare at Dimension level also) Make sure 'All dimensions  in one XML file' is selected Choose an output filename and location Now Export   This will then create an XML export of your Outline which will look something like this: <?XML version="1.0" encoding="UTF-8" ?> - <!-- olapunderground Essbase Outline Extractor v3.3.2: 18/02/2008 19:19:06 --> - <EssbaseOutline Server="Localhost" AppName="Sample" DbName="Basic"> - <Dimension Name="Year" Storage="Dense" DimType="Time" AttribDataType="None"> <Member PARENT0_Year="Year" CHILD0_Year="Qtr1" ALIAS0_Year="" PROPERTY0_Year="+ X" FORMULA0_Year="" /> <Member PARENT0_Year="Qtr1" CHILD0_Year="Jan" ALIAS0_Year="" PROPERTY0_Year="+" FORMULA0_Year="" /> <Member PARENT0_Year="Qtr1" CHILD0_Year="Feb" ALIAS0_Year="" PROPERTY0_Year="+" FORMULA0_Year="" /> <Member PARENT0_Year="Qtr1" CHILD0_Year="Mar" ALIAS0_Year="" PROPERTY0_Year="+" FORMULA0_Year="" /> <Member PARENT0_Year="Year" CHILD0_Year="Qtr2" ALIAS0_Year="" PROPERTY0_Year="+ X" FORMULA0_Year="" /> <Member PARENT0_Year="Qtr2" CHILD0_Year="Apr" ALIAS0_Year="" PROPERTY0_Year="+" FORMULA0_Year="" /> <Member PARENT0_Year="Qtr2" CHILD0_Year="May" ALIAS0_Year="" PROPERTY0_Year="+" FORMULA0_Year="" /> <Member PARENT0_Year="Qtr2" CHILD0_Year="Jun" ALIAS0_Year="" PROPERTY0_Year="+" FORMULA0_Year="" /> <Member PARENT0_Year="Year" CHILD0_Year="Qtr3" ALIAS0_Year="" PROPERTY0_Year="+ X" FORMULA0_Year="" /> <Member PARENT0_Year="Qtr3" CHILD0_Year="Jul" ALIAS0_Year="" PROPERTY0_Year="+" FORMULA0_Year="" /> <Member PARENT0_Year="Qtr3" CHILD0_Year="Aug" ALIAS0_Year="" PROPERTY0_Year="+" FORMULA0_Year="" /> <Member PARENT0_Year="Qtr3" CHILD0_Year="Sep" ALIAS0_Year="" PROPERTY0_Year="+" FORMULA0_Year="" /> <Member PARENT0_Year="Year" CHILD0_Year="Qtr4" ALIAS0_Year="" PROPERTY0_Year="+ X" FORMULA0_Year="" /> <Member PARENT0_Year="Qtr4" CHILD0_Year="Oct" ALIAS0_Year="" PROPERTY0_Year="+" FORMULA0_Year="" /> <Member PARENT0_Year="Qtr4" CHILD0_Year="Nov" ALIAS0_Year="" PROPERTY0_Year="+" FORMULA0_Year="" /> <Member PARENT0_Year="Qtr4" CHILD0_Year="Dec" ALIAS0_Year="" PROPERTY0_Year="+" FORMULA0_Year="" /> </Dimension> -<!-- ... --> </EssbaseOutline> NB. For some reason sometimes the Extract adds a funny character at <EssbaseOutline Server="Localhost^..." ..> you can check this by opening the file in a text editor (just remove the white space). Now lets make a change to the Outline - I've added 2008 as a child to the Year Dimension and moved the Quarters into it. Extract the modified Outline. (I have called the extract file Sample-Basic-Post.xml) That's the extracts done! Comparing the Extracts Now that you have successfully extracted the Outlines we need to compare them. There are tones of XML comparison tools so you can just use your preferred one. I use JExamXML as it is pretty easy to use and is free. It is command line based so this may put some of you off and it requires Java. You can download your copy from here. Open a command prompt and navigate to your extracted JExamXML folder, then use the following format to compare the XML extract files: java -classpath "jexamxml.jar" com.a7soft.examxml.ExamXML $1, $2, $3 /D samples\options Where  $1 is the first XML file, $2 is the second XML file and $3 is the difference file to create For my files it was: $1 = OutlineCompare\Sample-Basic-Pre.xml, $2 = OutlineCompare\Sample-Basic-Post.xml, $3 = OutlineCompare\Delta.xml A file with the differences was created called Delta.xml (the 3rd parameter) which looked like this: <?xml version="1.0" encoding="UTF-8" ?> <EssbaseOutline Server="Localhost" AppName="Sample" DbName="Basic"> <Dimension Name="Year" Storage="Dense" DimType="Time" AttribDataType="None"> <!-- Deleted Element(s) --> <Member PARENT0_Year="Year" CHILD0_Year="Qtr1" ALIAS0_Year="" PROPERTY0_Year="+ X" FORMULA0_Year="" /> <Member PARENT0_Year="Year" CHILD0_Year="Qtr2" ALIAS0_Year="" PROPERTY0_Year="+ X" FORMULA0_Year="" /> <Member PARENT0_Year="Year" CHILD0_Year="Qtr3" ALIAS0_Year="" PROPERTY0_Year="+ X" FORMULA0_Year="" /> <Member PARENT0_Year="Year" CHILD0_Year="Qtr4" ALIAS0_Year="" PROPERTY0_Year="+ X" FORMULA0_Year="" /> <!-- Added Element(s) --> <Member PARENT0_Year="Year" CHILD0_Year="2008" ALIAS0_Year="" PROPERTY0_Year="+" FORMULA0_Year="" /> <Member PARENT0_Year="2008" CHILD0_Year="Qtr1" ALIAS0_Year="" PROPERTY0_Year="+ X" FORMULA0_Year="" /> <Member PARENT0_Year="2008" CHILD0_Year="Qtr2" ALIAS0_Year="" PROPERTY0_Year="+ X" FORMULA0_Year="" /> <Member PARENT0_Year="2008" CHILD0_Year="Qtr3" ALIAS0_Year="" PROPERTY0_Year="+ X" FORMULA0_Year="" /> <Member PARENT0_Year="2008" CHILD0_Year="Qtr4" ALIAS0_Year="" PROPERTY0_Year="+ X" FORMULA0_Year="" /> </Dimension> </EssbaseOutline>   And there you have it. The two Outline successfully compared. Posted Feb 18 2008, 07:57 PM by JakeSmillie with no comments Filed under: Hyperion, Essbase, Outline, Oracle Share |

• Essbase Error - 1012045 Unable to convert bitmap to fixed member in function

  This error occurs within Calc scripts sometimes when attempting to fix on certain members. I have only seen this as a problem in version 7.1.6, however that is not to say it doesn't happen in other versions Take the following simple calc script for example:   CALC SCRIPT: //ESS_LOCALE English_UnitedStates.Latin1@Binary SET MSG SUMMARY; SET NOTICE DEFAULT; SET CACHE HIGH; SET CACHE ALL; SET CALCPARALLEL 4; SET UPDATECALC OFF; SET CLEARUPDATESTATUS OFF; FIX (@DESCENDANTS("Dim 1 Member Parent"), "Dim 2 Member") //This line gives error CLEARDATA "Dim 3 Member"; CLEARDATA "Dim 3 Member 2"; ENDFIX     #1 Try changing the fix to use a different function:   CALC SCRIPT: //ESS_LOCALE English_UnitedStates.Latin1@Binary SET MSG SUMMARY; SET NOTICE DEFAULT; SET CACHE HIGH; SET CACHE ALL; SET CALCPARALLEL 4; SET UPDATECALC OFF; SET CLEARUPDATESTATUS OFF; FIX (@IRSIBLINGS("Dim 1 Member Child"), "Dim 2 Member") //Use a member child but fix on siblings CLEARDATA "Dim 3 Member"; CLEARDATA "Dim 3 Member 2"; ENDFIX   #2 Try changing what you fix on:   CALC SCRIPT: //ESS_LOCALE English_UnitedStates.Latin1@Binary SET MSG SUMMARY; SET NOTICE DEFAULT; SET CACHE HIGH; SET CACHE ALL; SET CALCPARALLEL 4; SET UPDATECALC OFF; SET CLEARUPDATESTATUS OFF; FIX ("Dim 3 Member", "Dim 2 Member") //First fix on one of the members in 3rd Dimension CLEARDATA "Dim 1 Member Parent"; ENDFIX FIX ("Dim 3 Member 2", "Dim 2 Member") //Now fix on the other member in 3rd Dimension CLEARDATA "Dim 1 Member Parent"; ENDFIX   #3 If all members in Dim 1 are relatively static then explicitly state them in your fix.   Basically the tip is to play about with the Calc script until it resolved and see what works for you.   If all else fails then you could Copy the database and play about with different: dense/sparse settings; calc cache settings and; data cache settings Jake Posted Feb 11 2008, 05:06 PM by JakeSmillie with no comments Filed under: Hyperion, Essbase, Calc Scripts, Oracle Share |