Community Server

  • Subscribe to our RSS feed.
  • Twitter
  • StumbleUpon
  • Reddit
  • Facebook
  • Digg

Friday, 9 October 2009

Current date function on Mondrian

Posted on 06:39 by Unknown
Current date function on Mondrian
Pentaho BI Suite - Mondrian

I create quite a lot of operational dashboard views like "How many users did we acquire through which kind of channels in the last 7 days on a daily level?". In the past my solution was to write some Javascript within an xaction to define the date variables and then to pass on these variables as parameters to an MDX query. The results set of the MDX query would then be passed on to a JPivot component. While you can fairly easily implement this in an xaction, it is probably not the most convenient way to do this.
Ideally your OLAP server would offer this kind of functionality and as I only found out recently, Mondrian has actually been supporting this for a while. Mondrian offers a function called CurrentDateMember() that basically gets the current date for you and maps it to your time dimension. As everybody's time dimension is not set up the same way, CurrentDateMember() solves the problem by providing a formatting string, that can do the mapping for you.
But it doesn't stop there: In case you have a time dimension that is less than daily, you can specify BEFORE, AFTER or EXACT as the last argument of the function and it will figure out your time dimension details respectively.

So in a glance CurrentDateMember() has following arguments:
  1. your time dimension, i.e. [Login Time]
  2. the mapping format, i.e. '["Login Time"]\.[yyyy]\.["Q"q]\.[m]\.[d]', which will resolve to [Login Time].[2009].[Q4].[10].[7]
  3. mapping method (optional): BEFORE, AFTER or EXACT

The mapping format uses the VB format syntax, which is used for MDX in general as well. As you can see in the example above, you have to use \\ before dots and quotes are need to escape the formatting process.

By using CurrentDateMember(argument1, argument2).Lag(3) you can go 3 siblings back (in example it could give you the date of 3 days ago). If you want to go 3 days ahead in time, specify Lag(-3).

Please find some working examples below:

Mapping [2009-10-12] date format:

select {CurrentDateMember([Login Date], '[yyyy-mm-dd]').Lag(3.0)} ON COLUMNS,
{[Login Channel].[All Login Channels]}  ON ROWS
from [Logins]
WHERE
{[Measures].[Distinct Users]}
For some reason, although the documentation clearly states "MM" for
month number with a leading 0, only "mm" will work. It is a good idea
to check the mondrian.log in case you experience errors, as you will
see there if the translation works (i.e.: if the Mondrian log shows
[2009-MM-09], you know that the month number was not translated).


The above example was for a dimension with one hierarchy only. Please find below an example with a dimension with more than one hierarchy:

SELECT
CurrentDateMember([Date].[Date],'[Date]\.[Date]\.[yyyy-mm-dd]').Lag(357.0) ON COLUMNS,
NON EMPTY {Hierarchize({[Measures].[Sales]})} ON ROWS
FROM [Sales]


Mapping [2009].[4].[October].[12] date format:
select {CurrentDateMember([Login Time Monthly], '[yyyy]\.[q]\.[mmmm]\.[d]').Lag(3)} ON COLUMNS,
{[Login Channel].[All Login Channels]}  ON ROWS
from [Logins]
WHERE
{[Measures].[Distinct Users]}
Mapping [2009].[41].[6] date format:

select {CurrentDateMember([Login Time Weekly], '["Login Time Weekly"]\.[yyyy]\.[ww]\.[w]').Lag(3)} ON COLUMNS,
{[Login Channel].[All Login Channels]}  ON ROWS
from [Logins]
WHERE
{[Measures].[Distinct Users]}

So when should you now mention the dimension in the formating string? If you avoid mentioning it, Mondrian will have to search for the existence of the various members within all the dimensions, which takes processing time. If you have only one date dimension, then you could theoretically not mention the dimension string, if you have more, then there is no way around it.

Imagine now, we want to have a monthly and weekly summary of the last 6 periods. So how do we approach this?

Last 6 weeks:
select {
CurrentDateMember([Login Time Weekly], '["Login Time Weekly"]\.[yyyy]\.[ww]').Lag(6) :
CurrentDateMember([Login Time Weekly], '["Login Time Weekly"]\.[yyyy]\.[ww]')
}  ON COLUMNS,
{[Login Channel].[All Login Channels]}  ON ROWS
from [Logins]
WHERE
{[Measures].[Distinct Users]}

Please keep in mind that here our time dimension looks like this: [year].[week].[day]

Last 6 months:

select {
CurrentDateMember([Login Time Monthly], '["Login Time Monthly"]\.[yyyy]\.[q]\.[mmmm]').Lag(6) :
CurrentDateMember([Login Time Monthly], '["Login Time Monthly"]\.[yyyy]\.[q]\.[mmmm]')
} ON COLUMNS,
{[Login Channel].[All Login Channels]}  ON ROWS
from [Logins]
WHERE
{[Measures].[Distinct Users]}


Please keep in mind that here our time dimension looks like this: [year].[quarter].[month].[day]


I hope that this tutorial showed you the power of CurrentDateTime(). It is a very useful function, especially if you have to do a lot of analysis across time. It tooks me quite some time to use this function correctly (especially as there are not many examples), so I hope you can implement it within 5 minutes.

Related info:
Julian Hyde's Blog: http://julianhyde.blogspot.com/2006/10/mondrian-22-cube-designer-and.html
VB Format Syntax: http://www.apostate.com/vb-format-syntax








Email ThisBlogThis!Share to XShare to Facebook
Posted in | No comments
Newer Post Older Post Home

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Popular Posts

  • Pentaho Kettle Parameters and Variables: Tips and Tricks
    Pentaho Kettle Parameters and Variables: Tips and Tricks This blog post is not intended to be a formal introduction to using parameters and ...
  • Using Parameters in Pentaho Report Designer
    Using Parameters in Pentaho Report Designer Introduction How to define a parameter Additional info about the new parameter dialog Hidden Par...
  • Pentaho Data Integration: Scheduling and command line arguments
    Pentaho Data Integration (Kettle): Command line arguments and scheduling Tutorial Details Software: PDI/Kettle 4.1 (download here ), MySQL S...
  • Jaspersoft iReport: How to pass a parameter to a sub-dataset
    Jaspersoft iReport: How to pass a parameter to a sub-dataset Let’s say our main report is grouped by continent and the details band holds sa...
  • Using regular expressions with Pentah...
    Using regular expressions with Pentaho Data Integration (Kettle) There are quite some transformations steps that allow you to work with regu...
  • Pentaho Data Integration and Infinidb Series: Bulk Upload
    Pentaho Data Integration and InfiniDB Series: Bulk Upload Introduction Prepare Tables Using mainly Kettle steps Check if file exists Setup I...
  • Pentaho Data Integration: Remote execution with Carte
    Pentaho Data Integration: Remote execution with Carte Tutorial Details Software: PDI/Kettle 4.1 (download  here ), installed on your PC and ...
  • How to create a loop in Pentaho Kettle
    I finished my first ever video tutorial! This video will demonstrate you how easy it is to create a loop in Pentaho Kettle. Enjoy!
  • Understanding the Pentaho Kettle Dimension Insert/Update Step Null Value Behaviour
    We will be using a very simple sample transformation to test the null value behaviour: We use the Data Grid step to provide some sample dat...
  • Metadata Driven ETL and Reporting
    Metadata Driven ETL and Reporting with Pentaho Data Integration and Report Designer Tutorial Details  Software : If PDI Kettle 4.2 GA and PR...

Categories

  • "Bulk Loader"
  • "Bulk Loading"
  • "Hadoop"
  • "Kettle"
  • "Pentaho Book"
  • "Pentaho Data Integration"
  • "Pentaho Kettle"
  • "Pentaho Report Designer MDX MQL JDBC Parameters How To"
  • "Pentaho Report Designer MDX Parameters"
  • "Pentaho Report Designer MQL Parameters"
  • "Pentaho Report Designer Parmaters"
  • "Pentaho Report Designer"
  • "Pentaho Reporting 3.5 for Java Developers"
  • "Pentaho Reporting Book"
  • "Routing"
  • "Schema Workbench"
  • "Testing"
  • "Unicode"
  • "Unit testing"
  • "UTF8"
  • Agile development
  • automated testing
  • Big Data
  • Book Review
  • C-Tools
  • CBF
  • Clustered transformation
  • Command Line Arguments
  • Community Build Framework
  • D3JS
  • Dashboarding
  • Data Integration
  • Data Warehouse
  • Database Change Management
  • Database Version Control
  • Date Dimension
  • DBFit
  • ETL
  • ETLFit
  • Federated database
  • Google Charts
  • Google Visualization API
  • Hadoop
  • HTML5
  • iReport
  • JasperReports
  • JasperSoft
  • JasperStudio
  • Kettle
  • Kimball
  • Loop
  • Master data management
  • Metadata
  • Metedata editor
  • Mondrian
  • multidimensional modeling
  • OLAP
  • Open Source
  • Parameter
  • Parameters
  • Pentaho
  • Pentaho BI Server
  • Pentaho Data Integration
  • Pentaho Data Integration 4 Cookbook
  • Pentaho Kettle
  • Pentaho Metadata Editor Tutorial
  • Pentaho Report Designer
  • PostgreSQL
  • PRD
  • Report Layout
  • REST
  • Routing
  • Saiku
  • Scheduling
  • Slowly Changing Dimension
  • Sqitch
  • SVG
  • Talend
  • Talend MDM
  • Talend Open Studio
  • Tutorial
  • Variable
  • Web service
  • Xactions

Blog Archive

  • ►  2013 (24)
    • ►  December (2)
    • ►  November (3)
    • ►  October (2)
    • ►  September (1)
    • ►  August (3)
    • ►  July (2)
    • ►  June (1)
    • ►  May (2)
    • ►  April (1)
    • ►  March (3)
    • ►  February (1)
    • ►  January (3)
  • ►  2012 (20)
    • ►  November (3)
    • ►  October (3)
    • ►  August (1)
    • ►  June (1)
    • ►  April (1)
    • ►  March (3)
    • ►  February (5)
    • ►  January (3)
  • ►  2011 (19)
    • ►  November (3)
    • ►  July (2)
    • ►  June (1)
    • ►  May (4)
    • ►  April (2)
    • ►  March (1)
    • ►  February (3)
    • ►  January (3)
  • ►  2010 (17)
    • ►  December (1)
    • ►  November (6)
    • ►  September (1)
    • ►  August (1)
    • ►  June (2)
    • ►  May (1)
    • ►  April (3)
    • ►  February (1)
    • ►  January (1)
  • ▼  2009 (18)
    • ►  December (3)
    • ►  November (1)
    • ▼  October (5)
      • Send Pentaho Report By Email
      • Current date function on Mondrian
      • How to change the Pentaho Login Screen
      • Converting binary data type to string in...
      • Setting up an "All" Parameter
    • ►  September (7)
    • ►  July (2)
Powered by Blogger.

About Me

Unknown
View my complete profile