[Mondrian] Help: Alternative MDX function to LinkMember()

Wed Apr 2 15:11:36 EDT 2008

I've looked at the threads, and made comments on a couple of them.

RE. the LinkMember feature request. Frankly it's not particularly high
priority. Contributions welcome, of course.

Re. your MDX query... I'm hoping someone in the community will help you
there. I don't have much time to debug MDX.

Julian

  _____  

From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On
Behalf Of Daniel Murray
Sent: Wednesday, April 02, 2008 11:48 AM
To: jhyde at pentaho.com; Mondrian developer mailing list
Subject: Re: [Mondrian] Help: Alternative MDX function to LinkMember()

Thanks Julian for responding.

1) Please refer to the following threads:
http://forums.pentaho.org/showthread.php?t=60628
http://forums.pentaho.org/showthread.php?t=60624
http://forums.pentaho.org/showthread.php?t=60727

2) I will log the feature request. Can you advise how long it would take to
get this feature (I imagines its not the only one being worked on...)

3) This is my case:

I have an MDX that reads:  

select NON EMPTY {[Time_Cutoff].[All Time_Cutoff].[2006].[2Q].[Jun],
[Time_Cutoff].[All Time_Cutoff].[2006].[3Q].[Jul], [Time_Cutoff].[All
Time_Cutoff].[2006].[3Q].[Aug], [Time_Cutoff].[All
Time_Cutoff].[2006].[3Q].[Sep], [Time_Cutoff].[All
Time_Cutoff].[2006].[4Q].[Oct], [Time_Cutoff].[All
Time_Cutoff].[2006].[4Q].[Nov], [Time_Cutoff].[All
Time_Cutoff].[2006].[4Q].[Dec], [Time_Cutoff].[All
Time_Cutoff].[2007].[1Q].[Ene], [Time_Cutoff].[All
Time_Cutoff].[2007].[1Q].[Feb]} ON COLUMNS,

  NON EMPTY {[Time_Issue].[All Time_Issue].[2006].[2Q].[Jun],
[Time_Issue].[All Time_Issue].[2006].[3Q].[Jul], [Time_Issue].[All
Time_Issue].[2006].[3Q].[Aug], [Time_Issue].[All
Time_Issue].[2006].[3Q].[Sep], [Time_Issue].[All
Time_Issue].[2006].[4Q].[Oct], [Time_Issue].[All
Time_Issue].[2006].[4Q].[Nov], [Time_Issue].[All
Time_Issue].[2006].[4Q].[Dec]} ON ROWS

from [Cube] where [Measures].[Risk%]

Note: (C_Year = TimeCutoff Year, I_Year = TimeIssue Year)

                                 C_Year: 2006
2007

                                 C_Qtr 2   C_Qtr3
C_Qtr4

                                 C_Mth6    C_Mth7    C_Mth8     C_Mth9
C_Mth10    C_Mth11     C_Mth12         

I_Year  I_Qtr   I_Mth  

2006    2Q        4          3.60         7.00         10.50        12.60
17.50         22.20          24.90

                       5          0.80         4.20           7.50
10.40        14.30         18.10          21.40

                       6          0.00         0.80           4.60
8.20        12.90         16.30          20.70    

            3Q       7                         0.00           0.60
3.60          7.60         12.40          16.90

                       8                                           0.00
0.60          5.10          8.90          14.60 

What I need is a generic MDX that would generate the followin result:

                                 Time_Cutoff

                                 4thMonth            

I_Year  I_Qtr   I_Mth  

2006    2Q        4          10.50       

                       5          10.40        

                       6          12.90         

            3Q       7          12.40          

                       8          14.60 

Both Time Dimensions have the same structure (ie: Time.All
Time.Year.Qtr.Month); I need to maintain 2 separate Time dimensions.

As noted in the posts, I have tried to build the SET I'm looking for
(translated here and in the above example to: Períodos Cortes=TimeCutoff,
Períodos Cosehcas as TimeIssue) using:

SET [TEST_cast] as 'StrToSet("{[TimeCutoff]\\.[All
TimeCtoff]\\.[CAST([TimeIssue].CurrentMember.Lag(-4).Parent.Parent.Name AS
STRING)]\\.[CAST([TimeIssue].CurrentMember.Lag(-4).Parent.Name AS
STRING)]\\.[CAST([TimeIssue].CurrentMember.Lag(-4).Name AS STRING)]}",
[TimeCutoff])'

I have placed the above in the MDX builder in Workbench, along with other
Members and no problems... thinking it's working, I try to use it in the
select, I get an error message something along the lines of "...make sure
... is not a Hierarchy or Dimension..."

I hope you can help... Regards, Daniel Murray

2008/4/2, Julian Hyde <jhyde at pentaho.com>: 

Sorry, I can't think of an alternative if you've tried StrToMember already.
We should really implement LinkMember. Can you log a feature request at
SF.net please?

Also, can you send a link to your posts re. StrToSet and StrToMember? I
don't remember seeing them.

Julian

  _____  

From: mondrian-bounces at pentaho.org [mailto:mondrian-bounces at pentaho.org] On
Behalf Of Daniel Murray
Sent: Tuesday, April 01, 2008 9:48 AM
To: mondrian at pentaho.org
Subject: [Mondrian] Help: Alternative MDX function to LinkMember()

Please advise what is a valid function in Mondrian instead of using
"LinkMember"?

In MSSQL Anaysis I can use:

Member [Measures].[Risk 4thMonth] as
([Measures].[Risk%],
LinkMember(ParallelPeriod([Time_Issue].[Time_Issue] .[Month],
-4, [Time_Issue].[Time_Issue].CurrentMember),
[Time_Cutoff].[Time_Cutoff]))
select
{[Measures].[Risk 4thMonth]} ON COLUMNS,
NON EMPTY {[Time_Issue].[All Time_Issue].[2006].[2Q].[Jun],
[Time_Issue].[All Time_Issue].[2006].[3Q].[Jul], [Time_Issue].[All
Time_Issue].[2006].[3Q].[Aug],...

This is an alternative route to previous questions posted on the Mondrian
forum concerning problems with StrToSet and SetToStr.

I´d appeciate anyone's help... Thanks, DMurray3

_______________________________________________
Mondrian mailing list
Mondrian at pentaho.org
http://lists.pentaho.org/mailman/listinfo/mondrian

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20080402/3f5f2495/attachment.html 