Sunday, October 15, 2006

Mondrian 2.2, Cube Designer, and CurrentDateMember

I made the first release candidate of mondrian-2.2 a few days ago.

The most exciting feature is Pentaho's brand new Cube Designer:
The Pentaho Cube Designer simplifies and expedites the analysis process by eliminating the need for administrators to use XML to create new OLAP schemas. Instead, Cube Designer’s wizard-driven graphical user interface walks administrators through the steps of connecting to relational data sources, defining analytical dimensions, identifying measures for analysis, and publishing the schema to the Pentaho server.
At last, a serious UI for building Mondrian schemas! I've been helping the Pentaho folks develop this for the last few months, and was delighted when they decided to release it open-source.

It's been almost four months since Mondrian release 2.1. There are no earth-shatteringly huge new features in the core Mondrian 2.2, because I've been busy with the Cube Designer and olap4j. But if you peruse the change log, you'll see there have been lots of development activity, resulting in numerous fixes in areas such as performance and native SQL generation.

A useful addition is a set of functions for accessing the current date, contributed by Zelaine Fong and Benny Chow. If MDX had a date datatype, we would have implemented these functions long ago. But MDX doesn't have a date datatype, because everyone who uses dates wants to access them as a dimension (say, [Time].[2006].[October].[15]), not scalar values. To make things worse, we noticed that everyone's Time dimension is structured differently: some have quarters while some don't; some use month names while others use month numbers; and some time dimensions are structured on weeks rather than months.

So, Benny and Zelaine created a function which formats the current date as a string, then looks up a member of the time dimension. For example,
SELECT
{ [Measures].[Unit Sales] } ON COLUMNS,
{ CurrentDateMember(
[Time],
'[\"Time\"]\\.[yyyy]\\.[\"Q\"q]\\.[m]\\.[d]')
.Lag(3) :
CurrentDateMember(
[Time],
'[\"Time\"]\\.[yyyy]\\.[\"Q\"q]\\.[m]\\.[d]')
} ON ROWS
FROM [Sales]
prints the sales total for today and the previous three days.

The trick is with the format string, '[\"Time\"]\\.[yyyy]\\.[\"Q\"q]\\.[m]\\.[d]'. This is substituted with the current date, to give [Time].[2006].[Q4].[10].[15]. Then Mondrian looks in the [Time] dimension to find a member with that unique name.

The quotes around "Time" and the backslashes before the dots (\\.) are needed to prevent the formatting facility from replacing things that it shouldn't. It makes the code a little difficult to read, but it allowed us to use the existing formatting facility, which is well-tested and well-documented, rather than inventing a new one just for this function.

Now suppose that the granularity of the time dimension is less than daily. In a monthly time dimension, should 15th October fall resolve to [2006].[10] or [2006].[11]? The BEFORE , AFTER and EXACT keywords help Mondrian decide. So,
      CurrentDateMember(
[Time],
'[\"Time\"]\\.[yyyy]\\.[\"Q\"q]\\.[m]',
BEFORE)
would evaluate to the greatest member which is less than the current date, namely, [Time].[2006].[10].

This function fits well with another feature in mondrian-2.2, which I intend to blog about in a few days, is the extension of Parameters to system, schema, and session scope. Parameters are now much, much more than bind variables, and are a great way of sharing constants and expressions across your whole application.

So, while you're waiting for that blog entry... go and download the new mondrian release candidate, and try it out. Hopefully, the full release will be out this week or next.

23 comments:

Anonymous said...

Hi Julian,

I´d like to know if has been fixed in this version a problem we´ve found when creating dashboards using Jboss Portal.
If you have several different JPivot queries executing in several portlets, there is an error because it shows the same.
It seems like it takes only one. It´s not able to solve all the queries step by step. It´s a huge problem, because you see the same jpivot instead of the several different ones.

Thanks in advance
Todobi

Julian Hyde said...

I'm not familiar with that problem: is there a bug logged?

I'd lay a dime to a dollar that the problem is in the web layer. (JPivot or elsewhere in the portal.) Mondrian executes each query in a separate thread, and is capable of executing dozens of queries simultaneously without getting confused.

Look to the names that are being assigned to components in the portal, and in particular the name of the <mdx:query> element. I'm not a web programmer, but suspect that they need to be unique within the page.

Anonymous said...

Ow, it took me a long time to read this. It's true, I also think that the problem is in the web layer. I'm almost that it is what you are talking about (that query name thing) though i'm neither a web programmer.

Could you add this as a bug in Pentaho?

Thanks!

Anonymous said...

I tried to use the example in jpivot with the FoodMart database but i'm not get any data. I've change the clock from my computer for 1997, but i'm getting nothing.
The query I using
select
{[Measures].[Unit Sales], [Measures].[Store Cost], [Measures].[Store Sales]} on columns, {(CurrentDateMember([Time],'[\"Time\"]\\.[yyyy]'), [Promotion Media].[All Media], [Product].[All Products])} ON rows
from Sales
Can you help me?
Thanks

Anonymous said...

Even I tried to run following query but no results for current date.

SELECT
{ [Measures].[Unit Sales] } ON COLUMNS,
{ [Time].[1997],
CurrentDateMember(
[Time],
'[\"Time\"]\\.[yyyy]')
} ON ROWS
FROM [Sales]

Can any helpme on this?

Julian Hyde said...

anonymous,

The CurrentDateMember function cannot create members which are not already there. The Time dimension in the FoodMart schema only contains members up to 1998, and you are asking for [Time].[2007].

The solution is to populate the time dimension with members up to the current year.

Julian

Anonymous said...

Hi Julian. Thanks for the reply. I changed system date to 1997 then I tested that. but not getting the results.

Even i have another cube designed by me which contains Time dimenesion having years from 1950 to 2050. Here also i am not getting the data of current Year.

Here is the query.

SELECT
{
[Request Ship Date].[All Request Ship Dates].[2005],
[Request Ship Date].[All Request Ship Dates].[2006],
CurrentDateMember(
[Request Ship Date],'[\"Request Ship Date\"]\\.[\"All Request Ship Dates\"]\\.[yyyy]'
)
} ON COLUMNS,
{
[Order].[Order Number].[001S000160000] ,
[Order].[Order Number].[001S000220000]
} ON ROWS
FROM [Inventory Sales]

I can see the results of 2005 and 2006 but nothing is coming for 2007.

Mondrian verion is 2.2.1

Here is the mdx in the soap request after the conversion of double quotes and single quotes

SELECT
{
[Request Ship Date].[All Request Ship Dates].[2005],
[Request Ship Date].[All Request Ship Dates].[2006],
CurrentDateMember(
[Request Ship Date],'[\"Request Ship Date\"]\\.[\"All Request Ship Dates\"]\\.[yyyy]'
)
} ON COLUMNS,
{
[Order].[Order Number].[001S000160000] ,
[Order].[Order Number].[001S000220000]
} ON ROWS
FROM [Inventory Sales]

Can you find any thing wrong in the currentdatemember function in mdx of soap request.

Thanks in advance
Prakash

Julian Hyde said...

Prakash,

I have no idea why this function is not working for you. If I were you, I would run the query using a debugger, and put a breakpoint in CurrentDateMemberUdf.java. That might give you some clue.

Julian

Anonymous said...

Julian,

Thanks for the reply. Let me do some more hit and try. Will let you if i gets any thing.

Thanks
Prakash

Anonymous said...

Julian,

I found it.
actually i need to use '\' before h,s and q because these are format characters.

Following query is working now.

SELECT
{
[Request Ship Date].[All Request Ship Dates].[2005],
[Request Ship Date].[All Request Ship Dates].[2006],
CurrentDateMember(
[Request Ship Date],
'[Re\que\st S\hip Date]\.[All Re\que\st S\hip Date\s]\.[yyyy]'
)
} ON COLUMNS,
{
[Order].[Order Number].[001S000160000] ,
[Order].[Order Number].[001S000220000]
} ON ROWS
FROM [Inventory Sales]

Thanks for your help.

Prakash.

Anonymous said...

Hi Julian,

I don't know how to get Week of Years and Day of years through CurrentMemberDate. SimpleDateFormat symbols of java is not giving results.

Does mondrian has different set of dateformat symbols? if yes then can you pls provide me that?

Julian Hyde said...

Mondrian's format strings are based on Visual Basic format strings, just like the MDX language.

According to
http://www.apostate.com/programming/vb-format.html, week of year is ww (returns a number between 1 and 53); day of year is not supported.

To see precisely what is implemented, see mondrian.util.Format (http://mondrian.pentaho.org/api/mondrian/util/Format.html).

Julian

Dave Greco said...

Has the syntax for CurrentDateMember changed?

Since my upgrade from 2.3 something to 3.0, my queries using CurrentDateMember no longer work. The syntax I'm using is:

CurrentDateMember([Pickup Date], "[Pickup Date]\.[yyyy]\.[Qq]\.[mmmm]")

But I've tried a few variations on this. Any ideas?

Julian Hyde said...

> Dave Greco wrote:
>
> Has the syntax for CurrentDateMember changed?

The hasn't changed at all since 2.4. You must have been using an early version in 2.3.

Maybe you are hitting the same problem as Prakash: some of the letters in "Pickup Date" may correspond to format strings, in which case you will need to escape them. You can escape individual characters with a backslash ',' or whole sub-strings using double-quote '"'.

Julian

Dave Greco said...

To try and eliminate escaping problems, I renamed the dimension to just "A". I then tried things like:

{ CurrentDateMember( [A], "[\A]\.[\A]\.[yyyy]" ) } ON ROWS

{ CurrentDateMember( [A], '[A].[A].[yyyy]' ) } ON ROWS

{ CurrentDateMember( [A], '[\A]\\.[\A]\\.[yyyy]' ) } ON ROWS

{ CurrentDateMember( [A], "[\A]\.[A]\.[yyyy]" ) } ON ROWS

The last one works in ReX, so that seems to be the winner. But, it does not work from Jpivot- get an NPE:

java.lang.NullPointerException
at com.tonbeller.jpivot.mondrian.MondrianQueryAdapter.(MondrianQueryAdapter.java:64)
at com.tonbeller.jpivot.mondrian.MondrianModel.initialize(MondrianModel.java:503)
at com.tonbeller.jpivot.olap.model.OlapModelDecorator.initialize(OlapModelDecorator.java:132)
at com.tonbeller.jpivot.tags.OlapModelProxy$MyState.initialize(OlapModelProxy.java:77)
at com.tonbeller.jpivot.tags.StackStateManager.initializeAndShow(StackStateManager.java:76)
at com.tonbeller.jpivot.tags.OlapModelProxy.initializeAndShow(OlapModelProxy.java:160)
at com.tonbeller.jpivot.tags.OlapModelTag.doEndTag(OlapModelTag.java:81)

Julian Hyde said...

Don't know what's going on here, Dave. I suspect that the two client tools in question (Rex and JPivot) are having allergic reactions to the embedded backslashes.

Try using quotes and no backslashes:

{ CurrentDateMember([A], '"[A].[A]."[yyyy]') } ON ROWS

If that doesn't work, I have no idea.

Julian

Unknown said...

Hi Julian,
when i tried to query a dimension member in a cube(created using schema workbench) i encountered the folloung error

Mondrian Error:Failed to parse query 'SELECT {[Measures].[Total_Dist]} ON COLUMNS,{[Plans].[Plan Name]} ON ROWS FROM [Test Cube]'

Mondrian Error:Error while parsing MDX statement 'SELECT {[Measures].[Total_Dist]} ON COLUMNS,{[Plans].[Plan Name]} ON ROWS FROM [Test Cube]'

Mondrian Error:No function matches signature '{Level}'

can u please help me on this??

Thanks,
Sri

Julian Hyde said...

You can't display levels on an axis, only members. Change your query to

{[Plans].[Plan Name].Members} ON ROWS

Julian

PS This question has nothing to do with the original thread topic. It would have been better to post to the mondrian forum.

Naumaan said...

Im trying to generate a cube on the fly using a single jsp , the jsp substitutes jdbcuser,jdbcurl,cataloguri & mdxquery values read from a xml file(keeps al these values into the jp mondrianquery tag. It works for a jsp written in the /web-inf/queries folder passing a parameter to it.

But a single jsp doesnt work ! could u guide me in tat direction !

Unknown said...

Hi Every body,

Could someone help me please !
I want to use CurrentDateMember() fonction but i need to change year in order to use the current year on row.

with
member Measures.[test1] as
(CurrentDateMember([Temps],'["Temps].["[Temps].CurrentMember"]."\[m].[d]'),[Measures].[CAF])

SELECT
NON EMPTY {Hierarchize({[Measures].[test1]})} ON COLUMNS,
NON EMPTY {Hierarchize({[Temps.hTemps].[Annee].Members})} ON ROWS
FROM [Avancement]

This request don't work but Why??
How can i have the current year
Thanks in advance

Julian Hyde said...

I think it may help if you put the member in the [Time] hierarchy, and fix your format string. Like this:

with member [Time].[test1]
CurrentDateMember([Time], '[\"Time\"]\\.[yyyy]')
...

Unknown said...

it make error :

#ERR: mondrian.olap.fun.MondrianEvaluationException: Expected value of type MEMBER; got value 'mondrian.olap.fun.MondrianEvaluationException: Exception while executing function CurrentDateMember: java.lang.IllegalArgumentException: Invalid member identifier '["Ti5e"]\.[2013]' at org.olap4j.impl.IdentifierParser.parseIdentifier(IdentifierParser.java:350) at mondrian.olap.Util.parseIdentifier(Util.java:620) at mondrian.udf.CurrentDateMemberUdf.execute(CurrentDateMemberUdf.java:62) at mondrian.olap.fun.UdfResolver$ScalarCalcImpl.evaluate(UdfResolver.java:197) at mondrian.calc.impl.GenericCalc.evaluateMember(GenericCalc.java:149) at mondrian.calc.impl.MemberArrayValueCalc.evaluate(MemberArrayValueCalc.java:63) at mondrian.rolap.RolapEvaluator.evaluateCurrent(RolapEvaluator.java:687) at mondrian.rolap.RolapResult.executeStripe(RolapResult.java:972) at mondrian.rolap.RolapResult.executeStripe(RolapResult.java:1119) at mondrian.rolap.RolapResult.executeStripe(RolapResult.java:1119) at mondrian.rolap.RolapResult.executeBody(RolapResult.java:848) at mondrian.rolap.RolapResult.(RolapResult.java:452) at mondrian.rolap.RolapConnection.executeInternal(RolapConnection.java:680) at mondrian.rolap.RolapConnection.access$000(RolapConnection.java:51) at mondrian.rolap.RolapConnection$1.call(RolapConnection.java:631) at mondrian.rolap.RolapConnection$1.call(RolapConnection.java:630) at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334) at java.util.concurrent.FutureTask.run(FutureTask.java:166) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603) at java.lang.Thread.run(Thread.java:722) ' (class mondrian.olap.fun.MondrianEvaluationException)

Unknown said...

CurrentDateMember([Temps], '["Temps"]\.[yyyy]')