Results 1 to 7 of 7

Thread: How to to combine multiple date dimensions - LinkMember?

  1. #1
    Join Date
    Mar 2009
    Posts
    3

    Unhappy How to to combine multiple date dimensions - LinkMember?

    MDX Newbie here.

    The facts in our cube are based around events, and an event can have these dates (among others) associated with it: LoggedDate, LastInvoiceRejectedDate, InvoiceClearedDate. The cube includes measures for CountInvoicesCleared and CountInvoicesRejected based on their related cleared/rejected dates. For each month, I'd like to view the percentage of invoice actions that are rejections ie

    Sum(events with LastInvoiceRejectedDate) x 100 / ( Sum(events with LastInvoiceRejectedDate) + Sum(events with LastInvoiceClearedDate) )

    My current query ties each item to the LoggedDate but invoices for an event logged in January could be rejected in Feb then cleared in March. How do I get the monthly counts to correlate in the correct monthly columns (eg an invoice cleared in March should count for March, irrespective of the LoggedDate)? Reading around it looks like LinkMember is a likely contender, but I don't see how to get it to work.

    Current query:

    WITH

    SET [KeyMonths] AS
    '[LoggedDate].[All].[2009].children'

    -- THESE GENERATE a "Syntax error in axis definition" ERROR
    --
    -- ('LinkMember([LoggedDate].[All].[2009],[InvoiceClearedDate].[All])).children'
    -- ('LinkMember([LoggedDate].[All].[2009],[InvoiceRejectedDate].[All])).children'


    SET [ThisYear] AS '[LoggedDate].[2009].[January]:[LoggedDate].[2009].[December]'

    SET [Performance] AS
    '{
    ([Measures].[Count Invoices Cleared]),
    ([Measures].[Count Invoices Rejected])

    }'
    SELECT
    [KeyMonths] ON 0,
    [Performance] ON ROWS
    FROM [MainCube]

    Using Analysis Services 2000.


    Cheers
    Colin
    Last edited by Dundonian; 03-22-2009 at 02:20 PM.

  2. #2
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    Have You Considered Role-Playing Dimension for Dates?

    I assume you're working with Analysis Services 2k5 or above. Have You considered role-playing dimension treatment of the dates you mention?

    Let me know if you have questions ...

    Bill

  3. #3
    Join Date
    Mar 2009
    Posts
    3
    Hi Bill

    It's all 2K based (SQL Server and Analysis Services).

    In the database, I've added a TimeDimension table with all dates from 01 Jan 1999 up to 2030, and in the cube I've added a copy of this table with a suitable alias for each date in the fact table. I'm then using the date field from these aliased tables for my date dimensions rather than directly using the dates in the FACT table - otherwise I've found that some data seems to be missing in the cube after processing. Is this what you mean by "role-playing dimension treatment"? What's the next step to combining the aggregates of two different dates?

    Colin

  4. #4
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    Ah, 2k - based ...

    Sorry to hear you're not on 2k5 or later ... the suggestion I made earlier is not supported in 2k...

    Are you moving to 2k5 soon? The built-in capability for role-playing dimensions is far superior to any "workaround" we might devise for 2k - and you'll almost certainly be reworking same upon upgrade if you do choose to work around it in 2k ...

    Bill

  5. #5
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    Ah, 2k - based ...

    Sorry to hear you're not on 2k5 or later ... the suggestion I made earlier is not supported in 2k...

    Are you moving to 2k5 soon? The built-in capability for role-playing dimensions is far superior to any "workaround" we might devise for 2k - and you'll almost certainly be reworking same upon upgrade if you do choose to work around it in 2k ...

    Bill

  6. #6
    Join Date
    Mar 2009
    Posts
    3
    Unfortunately, there are no plans as yet to move to SQL 2K5 though I agree that would help lots.

    Have now managed to agree a change in requirement. Would be nice to know how to workaround in 2K, but no longer needed for my current work.

    Colin

  7. #7
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    Sounds Reasonable ...

    I suppose it's good to hear that the requirement has been modified... If you need further help with this or anything else within the integrated MS BI solution, you have only to ask.

    Thanks, and good luck!

    Bill

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •