cubeSavvy Utilities – updated MDX capabilities

As promised when I introduced cubeSavvy Utilities, expanding the MDX Query functionality to handle PROPERTIES and PROPERTY_EXPR was my next task.

I’m happy to say that version 1.1, now available for download, incorporates this feature.

This version can also handle an empty Columns axis:

Or an empty Rows axis:

As you can see above, this gives you the ability to specify whatever you want in a Column or Row – and even by Dimension. Below is the MDX query from the empty Columns axis screenshot above. Note how I specify [Pkg Type] for the Product dimension property, but [ChineseNames] for Year.

SELECT {} on axis(0),
{crossjoin([100].children,{[Jan],[Feb],[Mar]})} PROPERTIES [Product].[Pkg Type], [Year].[ChineseNames] on axis(1)
FROM Sample_U.Basic

This flexibility gives you the power to control exactly what you want to see in the rows and columns. Therefore, I removed the 3 “Display Alias” checkboxes that were in the first version. Now you can let your MDX query do the talking. I love MDX and XMLA!

Happy querying!

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.

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.

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