none
mdx calculated measure based on dimensions value

    Question

  • I am new to mdx and SSAS, We are trying to convert our store procedure to mdx query. I have a cube like below:


    Fact table:

                 [Target] (FK),
                 [Date] (FK),
                 [Action] (FK),
                 [Amount] (Measure),

     Dim Date:

                [DateKey] (pk),
                [Date],
                [DayOfYear],
                [DayofWeek],
                [....]


    Dim Action:  has fourkind of actions (added, removed, connected, ..)

                [ID] (PK)
                [Name] 
    Dim Target:

                [ID] (PK)
                [Name] 
    I want to create a calculated measure like what we did in t-sql 

         SELECT ....,


           CASE 
        WHEN a.[Action]='added' 

        THEN DATEDIFF(DAY, a.[Date],GETUTCDATE()) 
        ELSE DATEDIFF(DAY, a.[Date],GETUTCDATE())*-1
                 END


         AS 'Lifespan'

         FROM FilterData a 
         GROUP BY a.[Target]

     Question: How can I create a caculated measure for lifespan ?

     I have tried following but failed, [Dim  Action Types].[Name].CURRENTMEMBER always is "ALL";

        With Member [Measures].[LifeSpan] as
        CASE  
        WHEN  [Dim  Action Types].[Name].CURRENTMEMBER IS [Dim  Action Types].[Name].&[added] THEN  1*'datediff("d",[Dim Date].[Date],Now()) '
        WHEN [Dim  Action Types].[Name].CURRENTMEMBER IS  [Dim  Action Types].[Name].&[removed] THEN -1*'datediff("d",[Dim Date].[Date],Now()) '
        ELSE  0
        End 
        select [Measures].[LifeSpan] on columns,
        [Dim Action Targets].[Name].Children on rows
        from [OLAP Prep]

    -----------output---------------


                            LifeSpan

        tilerecipes/account-news 0
        tilerecipes/competitornews 0
        tilerecipes/innovation-blog 0
        tilerecipes/sales-now    0
        tilerecipes/inbox        0
        tilerecipes/in-the-news    0
        tilerecipes/jobs        0
        tilerecipes/l-and-d        0

    I also posted the question on stackoverfolow :http://stackoverflow.com/questions/25499619/mdx-calculated-measure-based-on-dimension-value

                                                      
    Tuesday, August 26, 2014 6:51 AM

All replies

  • Hi Ewan ,

    Could it be the key member defined with the ID column and not the Name ?

    So .... CASE   WHEN  [Dim  Action Types].[Name].CURRENTMEMBER IS [Dim  Action Types].[Name].&[added] THEN ...

    Will become : CASE   WHEN  [Dim  Action Types].[Name].CURRENTMEMBER IS [Dim  Action Types].[Name].&[1111] THEN ...

    Adding a short example from Adventure Works :)

    with member a as 
    Case when [Customer].[Customer].CURRENTMEMBER is [Customer].[Customer].&[20075]
    Then 0 Else 1 End
    select a on 0, [Customer].[Customer].Children on 1
    from [Adventure Works]


    Regards, David .

    Tuesday, August 26, 2014 8:15 AM
  • thanks for your reply.  

    I can get  some data use query :

    -------------

    WITH MEMBER [Measures].[TimeSpan] as 
    case
    WHEN [Action Types].[Name].CURRENTMEMBER IS [Action Types].[Name].&[added] THEN datediff('D',[Date].[Date].CURRENTMEMBER.NAME,Now())
    WHEN [Action Types].[Name].CURRENTMEMBER IS  [Action Types].[Name].&[removed] THEN -datediff('D',[Date].[Date].CURRENTMEMBER.NAME,Now())
    ELSE  0
    END
    //[Date].[Date].CURRENTMEMBER.NAME


    SELECT [Measures].[TimeSpan] ON 0,
    NONEMPTY([Date].[Date].Children*[Action Types].[NAME].Children)on 1
    FROM [OLAP Prep]

    To get the  output  like this 

    ---------------------------------

    TimeSpan
    2014-07-23 00:00:00.000 added       34
    2014-07-23 00:00:00.000 connected 0
    2014-07-23 00:00:00.000 disconnected 0
    2014-07-23 00:00:00.000 removed      -34
    2014-07-25 00:00:00.000 added       32
    2014-07-25 00:00:00.000 connected 0
    2014-07-25 00:00:00.000 disconnected 0
    2014-08-04 00:00:00.000 added        22
    2014-08-04 00:00:00.000 connected 0
    2014-08-04 00:00:00.000 disconnected 0
    2014-08-07 00:00:00.000 added       19
    2014-08-07 00:00:00.000 connected 0
    2014-08-07 00:00:00.000 disconnected 0
    2014-08-08 00:00:00.000 connected 0
    2014-08-08 00:00:00.000 disconnected 0

    --------------------------------------------------------------                                                                                                 But it won't help much , I want  this timespan value as a measure like "Amount" for each database rows, then I can do sum ... it's hard to explain.   it is supposed to be a column in fact datatable named "timespan" (measure) , but it is calculated by today's date, can't created in datatable, later on I created a named calculated column in dsv, using GETDATE(). But  the GETDATE() is the processing date, so I have to create a calculated measure here...

    The query I used in t-sql :

     SELECT 
    DATENAME(DAY,a.[Date])+'/'+ DATENAME(MONTH,a.[Date]) AS 'X',
    SUM(CASE WHEN a.[Action]='added' THEN DATEDIFF(DAY, a.[Date],GETDATE())  

                                 ELSE                           DATEDIFF(DAY, a.[Date],GETDATE())*-1 END)
     AS 'TimeSpan',
    FROM FactData a

    GROUP BY a.[Date]

    • Edited by ewan7 Tuesday, August 26, 2014 9:24 AM
    Tuesday, August 26, 2014 9:20 AM
  • Hi ,

    "It's hard to explain.. " . I believe you :)

    If you are around the solution and need calculate the new measure for the granularity row, think about defining a fictive measure (Null as CalcMeasure) in your DSV, and then define your logic in a scope .

    I hope it will help you to achieve what you want :)

    http://cwebbbi.wordpress.com/2013/05/29/aggregating-the-result-of-an-mdx-calculation-using-scoped-assignments/


    Regards, David .

    Tuesday, August 26, 2014 10:25 AM
  • Tried on this mdx script cmd:

    CALCULATE;

    Scope([Measures].[Tile Life Span]);
        This = 0; // We zero out the whole thing first
        Scope([Date].[Date].Children,[Action Types].[Name].&[added]);
            This =  datediff("d",[Date].[Date].CURRENTMEMBER.NAME,Now());
        End Scope;
        Scope([Date].[Date].Children,[Action Types].[Name].&[removed]);
            This =  -datediff("d",[Date].[Date].CURRENTMEMBER.NAME,Now());
        End Scope;
    End Scope; 

    I tried use below mdx query to get  total life span for each tile.  in t-sql should be :

    select sum(tilelifespan)

    from factdata

    group by tile

    --------------------------

    SELECT [Measures].[Tile Life Span] ON 0,
    NONEMPTY([Tile].[NAME].Children)on 1
    FROM [OLAP Prep]

    still return  all  0 ;

    shall I cross join the [DATE]  on axis 1/column ? I 

    blow is my fact table,  FK are all dimensions , we use rowcount as default measure.

    ----------------

    CREATE TABLE [dbo].[tblActionLog](
    [ActionLogId] [bigint] IDENTITY(1,1) NOT NULL,---- pk
    [ActionType] [int] NOT NULL, ---- FK  (added, removed.....)
    [Tile] [int] NOT NULL,----FK
    [EmployeeId] [nvarchar](30) NOT NULL,----FK
    [Date] [int] NOT NULL,----FK
    [CountryCode] [nvarchar](10) NOT NULL,---FK
    [BusinessRegion] [int] NOT NULL,---FK
    [ActionDate] [datetime] NULL,
     CONSTRAINT [PK_tblActionLog] PRIMARY 

    • Edited by ewan7 Tuesday, August 26, 2014 12:50 PM
    Tuesday, August 26, 2014 12:40 PM
  • I'll try to investigate further .. Can you show a similar example for the Adventure Works db ?

    Could it be that the 'datediff' is the problem ? have you tried to isolate that issue ? (instead of case when .. then datediff ..  , try something simple : case when .. then 111 else when .. then 222 Else 333 )

    Now I hope we will know for sure in what part of the scope we're in .


    Regards, David .

    Tuesday, August 26, 2014 1:05 PM
  • Thank you for your help!  I couldn't find a sample in Adventure Works. How about I send an email to you ? 

    • Edited by ewan7 Wednesday, August 27, 2014 7:22 AM good
    Wednesday, August 27, 2014 7:20 AM
  • No problem :) feel free to sent it to : dudi.harazi@gmail.com

    Is it possible to get a sample db so I'll be able to look in ?


    Regards, David .

    Wednesday, August 27, 2014 9:24 AM
  • But it won't help much , I want  this timespan value as a measure like "Amount" for each database rows, then I can do sum ... it's hard to explain.   it is supposed to be a column in fact datatable named "timespan" (measure) , but it is calculated by today's date, can't created in datatable, later on I created a named calculated column in dsv, using GETDATE(). But  the GETDATE() is the processing date,

    Hi Ewan,

    As you can said the GetDate() function returns the processing date. To avoid this issue, you can process the cube automatic at 12:00 AM every day. There are several methods present by using those you can automate cube processing. Please refer to the link below which describe two methods to automate cube processing.
    http://aniruddhathengadi.blogspot.kr/2011/12/how-to-automate-cube-processing.html

    Regards,


    Charlie Liao
    TechNet Community Support

    Thursday, August 28, 2014 3:11 AM
    Moderator
  • thank you! I sent two emails , please check it. 
    Thursday, August 28, 2014 3:39 AM
  • thank you! I am new to SSAS, let alone my problem, do we need process the cube everyday. Will it automatically process when data is modified or added ? Is there any default processing configuration to this ? 

    i will take your suggestion if there isn't any generic solution on SSAS. 

    Thank you!

    Thursday, August 28, 2014 4:07 AM
  • Hi ,

    Do you want your OLAP db will process during the night ? Is it need to be updated for every change in the data warehouse ?

    Need an updated data ? have a look at the proactive caching for a near real-time data : http://technet.microsoft.com/en-us/library/ms174769(v=sql.110).aspx

    Need a true real-time data ? ROLAP can be the answer : http://www.sql-server-performance.com/2013/ssas-storage-modes/

    If you need the data to be upload once a day, I would recommend using a job which contains a SSIS package (Analysis Services Processing Task) .

    You can schedule the job and divide the process to dimensions and then partitions/ process data and then indexes/ processing in one transaction and many more options ..

    Good Luck !!


    Regards, David .

    Thursday, August 28, 2014 12:17 PM
  • We do have a night job (SSIS) to import third part data into data warehouse. Will it be processed automatically when do the importing (inserting data) ?  We do want the every changes be updated on SSAS. Does that mean we need process the cube daily  ?

    If yes,  I will consider the named caLcuated column (which use ts-ql  GETDATE()).  

    Thank you !

    Friday, August 29, 2014 1:25 AM
  • My question is similar with this post

    http://social.msdn.microsoft.com/forums/sqlserver/en-US/74c843e7-b973-4f09-8c25-73bf47528ef3/mdx-get-day-value-from-date-dimension-row-level

     
    Friday, August 29, 2014 2:27 AM
  • Hi Ewan ,

    If it's still relevant, Maybe I can help with all the zero values .. 

    So I used your data you've sent me and generated a cube (dim names slightly different since it was generated automatically) . Please try to run the next code in SSMS, by adding each time a different members for the axis instead of the XXX text, and follow by the change you'll see (look at the messages tab and not only the results tab ) .

    With Member [Measures].[LifeSpan] as
        CASE  
        WHEN  [Tbl Action Types].[Name].CURRENTMEMBER IS [tbl Action Types].[Name].&[added] THEN  1*datediff("d",[Tbl Dim Date].[Date],Now()) 
        WHEN [Tbl Action Types].[Name].CURRENTMEMBER IS  [tbl Action Types].[Name].&[removed] THEN -1*datediff("d",[Tbl Dim Date].[Date],Now())
        ELSE  0
        End 
        Member [Measures].[Texting] as membertostr([Tbl Action Types].[Name].CURRENTMEMBER)
        
        select {[Measures].[LifeSpan],[Measures].[Texting]} on columns,
        XXX on rows
        from [OLAP Pre]
     
    1)    [tbl Action Targets].[Name].Children
        
    2)    [Tbl Action Types].[Name].[Name]*
     [tbl Action Targets].[Name].Children
           
    3)    NONEMPTYCROSSJOIN ([Tbl Action Types].[Name].[Name],
    [tbl Action Targets].[Name].Children) on rows


    Regards, David .

    Sunday, August 31, 2014 9:07 AM