none
Calculated measure based on a character column RRS feed

  • Question

  • Hi,

    I am new to SSAS, so please excuse if the query is that simple.

    I have a column 'delayed_flag' in the fact table, its permissible values are 'N' and 'Y'. Is it possible to add this column in the measure group(without doing any aggregations)?

    Here the requirement is to get the number of transaction that are delayed by checking this flag. If i dont add this column in the measure group, is it possible for me to add  a calculated member, where i can put the logic like if 'N' use 1 else 0.

     

    Thanks in advance,

    Nikesh

    Friday, November 11, 2011 10:17 AM

Answers

  • Yes, it is possible. In the DSV add named calculation for the fact table, for example: case when delayed_flag = 'n' then 0 else 1 end. Then you can define a new measure based on this column with the aggregatino type SUM.

    HTH

    Friday, November 11, 2011 11:17 AM
  • I assume you are using a SUM aggregation on this Flag measure to find out the late deliveries.

    In my understanding FLAG is a definite candidate for Dimension. So that you can use FLAG as a filter in all of your future calculations.  And getting the late deliveries will be one among them.

     

    Tinto

     

    • Proposed as answer by Tinto James Tuesday, November 15, 2011 1:28 AM
    • Marked as answer by Jerry NeeModerator Tuesday, November 22, 2011 10:21 AM
    Monday, November 14, 2011 12:43 PM

All replies

  • Yes, it is possible. In the DSV add named calculation for the fact table, for example: case when delayed_flag = 'n' then 0 else 1 end. Then you can define a new measure based on this column with the aggregatino type SUM.

    HTH

    Friday, November 11, 2011 11:17 AM
  • Thank you Stanhe, i tried it and working fine
    Friday, November 11, 2011 1:04 PM
  • Sorry  if the question is simple, i am very new to SSAS.

    Here i have a fact table which has columns called flag and total_quantity_in_litre and the dimension heirarchy is given below;

    Geography is the heirarchy under Dimension Location.

    Global->Country->Airport.

     I need to calculate the total volume(globally) and i used the MDX as

    [Measures].[Volume in Litre],[Location].[Geography].[All] and is working fine.

    Now I need to sum up the volume only if the flag is 1(This flag is there in the fact table.

    I tried adding calulated members with Case when and IIF statements but nothing is working as desired.

    Is there any function which takes the data from the fact table before aggragating it up.

    Thanks in advance,

    Nikesh

    Monday, November 14, 2011 11:13 AM
  • How did you implement this flag in your cube? If it is implemented as dimension, then including it in the tuple should give the correct result ([Measures].[Volume in Litre], [Location].[Geography].[All], [Flag].[Flag].[1])
    Monday, November 14, 2011 11:46 AM
  • I dont have a dimension called flag in the cube, i have a measure based on this (aggregate which means the no of late deliveries).Can i use this measure instead of creating a new dimension?

     

    Thanks,

    Nieksh

    Monday, November 14, 2011 12:21 PM
  • I assume you are using a SUM aggregation on this Flag measure to find out the late deliveries.

    In my understanding FLAG is a definite candidate for Dimension. So that you can use FLAG as a filter in all of your future calculations.  And getting the late deliveries will be one among them.

     

    Tinto

     

    • Proposed as answer by Tinto James Tuesday, November 15, 2011 1:28 AM
    • Marked as answer by Jerry NeeModerator Tuesday, November 22, 2011 10:21 AM
    Monday, November 14, 2011 12:43 PM
  • Yes , I am using sum aggregation to find the number of late deliveries. I added a new dimennsion called flag and now i am able to calculate the delayed volume acccurately. Thanks a lot Tinto and Stanhe. Was really helpful.

     

    Nikesh

    Monday, November 14, 2011 1:03 PM