[Mondrian] Date dimensions and attribute-hierarchies in mondrian 4

[Pedro Alves]

This same questions comes up rath frequently from a different number of
users (blogged about it nearly 3 years ago -
http://pedroalves-bi.blogspot.pt/2009/03/interesting-olap-date-crosstab-question.html)
where I had to use dummy dimensions to be able to do this. Now I can
do
the same with dummy hierarchies but is equally a hack, imo.


I can see where you're headed with one attribute per hierarchy, but unless
i'm seeing this wrong, we'd still need one or more "normal" date
hierarchies (the ones with year-quarter-month-day) to do the usual
drilldown, resulting in about 8 or more hierarchies just for the date
dimension when we include week combinations. Wouldn't this result in a
fairly complicated scenario for the end user (even mdx writer) that would
have to now exactly which level to drag? In the end James' business
question, as well as mine, is simply "months by year" which should be
relatively straightforward to answer.


I know and rerspect the decision to follow ssas behaviour, but would it
make sense, even if by popular demand :) to add an option in mondrian
properties to allow this, defaulting to false or something?


Cheers


-pedro



On Tuesday, April 10, 2012, Julian Hyde wrote:

> On Apr 10, 2012, at 7:47 AM, James Dixon wrote:
>
> On the call yesterday you mentioned that you would do ‘day of week’ as
> part of a date dimension instead of a separate attribute. My problem with
> date dimensions is that I often want to put year on a different axis from
> month, week, or day of week. I have not been able to do this with Analyzer
> using a date dimension. I thought this was an MDX limitation. Is there
> something in the new Mondrian version that lets me put different levels of
> a date dimension on different axes?
>
>
> James,
>
> In old versions of mondrian (consistent with SSAS 2000), you weren't
> allowed to have the same dimension on more than one axis. (E.g. you
> couldn't have Time.Weekly on columns and Time.Yearly on rows.)
>
> A couple of years ago I changed it (consistent with SSAS 2005 and later).
> Now you can have the same dimension on different axes, but you can't have
> the same hierarchy on different axes. So,
>
> select [Time.Weekly].[Year] on 0
>  [Time].[Year] on 1
> from [Sales]
>
> is valid.
>
> In Mondrian 4, the attributes in the date dimension would all have their
> own hierarchy. You don't reference attributes directly from MDX, but you
> reference the hierarchies and levels. For example, [Time].[Year] is the
> hierarchy for the "Year" attribute, and [Time].[Year].[Year] is its level.
> You can put these hierarchies wherever you want. E.g.
>
> select [Time].[Year].[Year.Members * [Time].[Quarter].[Quarter].Members on
> 0,
>  [Time].[Day of Week].Members on 1
> from [Sales]
>
> You can also use Year and Quarter to build levels in the [Time].[Monthly]
> hierarchy, but then you can't use them on on separate axes:
>
> select [Time].[Monthly].[Year].Members on 0,
>  [Time].[Monthly].[Quarter].Members on 1
> from [Sales]
>
> is illegal.
>
> Benny,
>
> I think that analyzer should help if someone wants to "break" a hierarchy.
> If they try to build a query like that, analyzer should ask them whether
> they want to use attribute hierarchies instead. It would rewrite to
>
> select [Time].[Year].[Year].Members on 0,
>  [Time][Quarter].[Quarter].Members on 1
> from [Sales]
>
> Do you agree?
>
> I've logged http://jira.pentaho.com/browse/ANALYZER-1184 for this.
>
> Julian
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20120410/857bdcf4/attachment.html