Wednesday, March 25, 2015

Introducing cubeSavvy Utilities

cubeSavvy Utilities
I’ve wanted to combine my various Essbase-related tools into a single, integrated tool for a while. That tool is now called cubeSavvy Utilities. It encompasses my MDX Query Tool – XMLA Edition, ASO Export Parser, and MaxL Outline XML Parser.
cubeSavvyUtilities_home
I’ve also made some enhancements to the MDX Query tool’s functionality. The most obvious is the new ability to ‘Display Both Members and Aliases in Rows’, as seen below. However, it can also now handle queries that either have just COLUMNS or just ROWS. The next version will have expanded capability to display DIMENSION PROPERTIES and PROPERTY_EXPR. These are currently just ignored.
cubeSavvyUtilities_mdx
All 3 functions have been made scriptable, with the addition of a cubeSavvyUtilities.conf configuration file to specify the same parameters found in the GUI version. The configuration file also stores Essbase server information, like user name, password, server name, and APS url. I’m extremely security conscious, so the password is stored in encrypted format. Just enter it the first time and it’s encrypted for future uses. Running from the command-line/script is as easy as: java -jar cubeSavvyUtilities.jar out. The possible flags are ‘aso’ (for the ASO Export Parser), ‘out’ (for the XML Outline Parser), and ‘mdx’ (for the MDX Query Tool).
Following are the contents of a sample cubeSavvyUtilities.conf file:
#——————————————————————————-
#cubeSavvy Utilities, copyright 2015. Harry Gates
#Place quotation marks around entries:
# ASOExportParser.FileToParse=”C:\\Documents and Settings\\harry\\asosamp.txt”
# OR
# ASOExportParser.FileToParse=”C:/Documents and Settings/harry/asosamp.txt”
#To enter new Essbase.Password
#1) Enter new password value in line: Essbase.Password=”newPassword”
#2) Set: Essbase.PasswordEncrypted=false
#3) Essbase.PasswordEncryped will be set to true the first time the program is run as a script
#To specify filters:
# ASOExportParser.Filters=[“<DESC/Qtr1″,”<DESC/Senior”,”<CHILD/Digital Cameras”]
#To specify no filters, you can delete everything after the equals sign on the line: ASOExportParser.Filters=
#The MDX Query can span multiple lines by surrounding it with three quotation marks as follows:
# MDXQuery.MDXStatement=”””
# SELECT CrossJoin([Measures].CHILDREN, [Market].CHILDREN) on columns,
# Product].Members on rows
# from Sample.Basic
# “””
Essbase.APSUrl=”http://localhost:9100/aps/JAPI”
Essbase.ServerName=”epm”
Essbase.UserName=”admin”
Essbase.Password=”password”
Essbase.PasswordEncrypted=false
ASOExportParser.Application=”ASOsamp”
ASOExportParser.Database=”Sample”
ASOExportParser.FileToParse=”asosamp.txt”
ASOExportParser.OutputFile=”outputConf.txt”
ASOExportParser.Filters=[“<DESC/Qtr1″,”<DESC/Senior”,”<CHILD/Digital Cameras”,”<DESC/Sale”,”<CHILD/Price Paid”,”<DESC/Original Price”]
OutlineParser.FileToParse=”/Users/harry/test_124.xml”
OutlineParser.OutputFile=”/Users/harry/out124.txt”
OutlineParser.FieldDelimiter=”|”
MDXQuery.OutputFile=”/Users/harry/mdxResult.txt”
MDXQuery.DisplayAliasInRows=false
MDXQuery.DisplayMemberAndAliasInRows=true
MDXQuery.DisplayAliasInColumns=false
MDXQuery.MDXStatement=”””
SELECT CrossJoin([Measures].CHILDREN,[Market].CHILDREN) on columns,
[Product].Members on rows
from Sample.Basic
“””
#——————————————————————————-
 I originally developed each of these tools to scratch my own Essbase development and administration “itches”, where Oracle had yet to provide a solution. I hope you find them useful, too.
You can download cubeSavvy Utilities here.
Please let me know if you have any questions or ideas for improvement. I can be reached at harry.gates@cubesavvy.com

No comments:

Post a Comment