Mdx Help
From OpenI Wiki
| Table of contents |
On the fly percentage calculations
Here's a sample of mdx where percentages are calculated on the fly, not built into the cube, using the WITH MEMBER clause:
WITH MEMBER
[Measures].[PCT] AS '[Measures].[Customers] / ([Measures].[Customers], [Product].[All Product])',
FORMAT_STRING = '#.00%'
SELECT {[Measures].[Customers], [Measures].[PCT]} ON COLUMNS,
{[Product].[All Product].Children} ON rows
FROM [Customer Sales by Corporate]
The results may not be very helpful, but with a little member expanding you get this. Notice all the percentages are based on <code> #Unique Customers that purchased that cell's product/ #Unique Customers that purchased all products </code>
AVG Function
To date calculations
- periods to date: http://databasejournal.com/features/mssql/article.php/10894_3372741_3
- YTD, MTD, WTD: http://www.databasejournal.com/features/mssql/article.php/10894_2232111_4
MSDN Function Reference
Except - explicitly excluding data
Usage:
Except(«Set1», «Set2»[, ALL])
Sample:
select measures.members on columns,
except({[Customers].[country].[USA].children}, {[Customers].[All
Customers].[USA].[CA]}) on rows
from sales
Calculated Measure: Pct Increase over Previous period
- This can be done using the WITH MEMBER clause for ad-hoc calculated measure, or as a calculated member in the cube. Keep in mind that adding/changing calculated members do not require a re-processing of cubes
- Must designate a dimension of type Time, then you have special time based mdx functions available to that dimension
- the time dimension also (obviously) needs a datetime field.
- Sample from Apple Weekly:
first calculate the difference:
[Measure].[Accounts] - ([Measure].[Accounts],[Data Load].CurrentMember.PrevMember)
Then the ratio is easy:
[Measure].[Account Diff Over Prevous Period]/[Measures].[Accounts]
Slicing by more than one member of a dimension
- common problem - need to slice by more than one member (i.e. - CA and NV)
- just using the slicer functionality (WHERE clause) only permits slicing by one member at a time, or if a level exists, one level at a time)
- Can be solved with MDX like this (from Apple Weekly):
WITH MEMBER [Customer Type Change].[All Customer Type Change].[New Acquisitions] AS '[New Subscription] + [Conversion]'
- A new dimension member will be available in the dimension browser called New Acquisions that contains the aggregate of New Subscriptions and conversions. Now you can specify in the slicer (or bring it up to column/row for that matter)
- This can also be used to create on the fly segment buckets.
How to integrate with OpenI/jpivot
- jpivot aleady generates mdx - drill member, drill replace, drillthrough, swap axes, sorting.
- jpivot already has architecture to support this. There is an interface called Model, which contains result/dimension accessors. The model is decorated with behaviors such as: drill replace, swap axes, member tree, sort rank, non empty. Likewise, we can create a decorator called percentage.
- Where to start, use the XMLA swap axes implementation as a sample starting point:
-
config.xml -
com.tonbeller.jpivot.xmla.XMLA_SwapAxes -
com.tonbeller.jpivot.core.Extension
-
INF, NumberFormatException
Exception stack looks like this:
javax.servlet.jsp.JspException: java.lang.NumberFormatException: For input string: "INF" at com.tonbeller.wcf.component.RendererTag.doEndTag(RendererTag.java:160) ...
Probably a calculated member or WITH MEMBER where you end up trying to divide by zero. OpenI needs to handle this gracefully, but a workaround is to wrap you mdx inside of an iif statement. See sample usage here (http://www.databasejournal.com/features/mssql/article.php/10894_3426201_4).
Cell highlighting
WITH MEMBER [Time].[Total] AS '[1997] + 1998.0',FORMAT_STRING = '|$#,#.00|style="red"'
