locked
Dynamically Changing Time RRS feed

  • Question

  • Hi,

     

    Please Help!

    I need to create a daily report which holds the present day's count 'x' and the previous day's count 'y' ( to show the diff in count betn the 2 days).

    I need to know if these dates will change on a daily basis as and when the cube is refreshed. if not, what do we need to do for this dynamically changing time?

    Wednesday, June 11, 2008 7:14 PM

Answers

  • Create a named set.. for arguments sake call it [today and yesterday]

    I have assumed the following for this example:

    - time dimension is called [Time]
    - contains a hierarchy called [Calendar Year Hierarchy]
    - the day level is called [Date]

    define the named set as:


    Code Snippet

    {

    tail(NONEMPTY([Time].[Calendar Year Hierarchy].[Date])).item(0), tail(NONEMPTY([Time].[Calendar Year Hierarchy].[Date])).item(0).lag(1)

    }



    Then all you have to do is drag the named set onto the columns on the analytical grid.

    if you dont want to create the named set, you can also customize the MDX expression for the grid and enter it directly. The downside with this method is that you will lose drilldown, change to chart etc capability in the grid.

    the important assumption here is that your cube only contains data for dates until today. if you have data for future dates then a different method needs to be used.
    Thursday, June 12, 2008 12:29 PM
  • A named set is a predefined mdx expression that returns a set. It is stored as part of the cube.

    You can use it to specify sets from any of your dimensions, using any appropriate mdx functions. With practice they can be quite powerful.

    Generally i find that named sets are quite useful when you need to use a common set of columns or rows in a number of reports, grids, charts etc.

    Try these links for a good coverage.

    quick overview:
    http://msdn.microsoft.com/en-us/library/ms174485.aspx

    excellent article:
    http://www.databasejournal.com/features/mssql/article.php/3568591


    You will also eventually find good uses for Calculated Members




    Thursday, June 12, 2008 2:23 PM

All replies

  • its not clear exactly where or how you are trying to do this.. you could either do it using an MDX expression  that dynamically references the time member for present day and yesterday, Or your could leverage the time intelligence formulas.

    if you gave more detail on what kind of report/scorecard etc you need and also what your time dimension looks like i may be able to give you better examples.
    Thursday, June 12, 2008 12:46 AM
  • Thanks Proffy. I am building an Analytic Grid report in the dashboard which needs to hold values like below:

    TL 14th 15th
    Status X X
    A    
    B 9 9
    C    
    D    
    E    
    F    
    G    
    H    
    I    
    J    
    K    
    L    
    M    
    Total 9 9

     

    In the above report, i need to display the count of status (a,b,c,e,f,......) for a particular capability called X for 14th(SYSDATE -1) & 15th(SYSDATE).

     

    Now these dates need to change on a daily basis since this a daily report and should hold values of only the (present-date) & (present-date -1)

    Please let me know how we could build the dynamically changing time so that the report automatically refreses everytime the cube is referesed.

    Pls. let me know if you require further inputs.

    Thursday, June 12, 2008 6:53 AM
  • Create a named set.. for arguments sake call it [today and yesterday]

    I have assumed the following for this example:

    - time dimension is called [Time]
    - contains a hierarchy called [Calendar Year Hierarchy]
    - the day level is called [Date]

    define the named set as:


    Code Snippet

    {

    tail(NONEMPTY([Time].[Calendar Year Hierarchy].[Date])).item(0), tail(NONEMPTY([Time].[Calendar Year Hierarchy].[Date])).item(0).lag(1)

    }



    Then all you have to do is drag the named set onto the columns on the analytical grid.

    if you dont want to create the named set, you can also customize the MDX expression for the grid and enter it directly. The downside with this method is that you will lose drilldown, change to chart etc capability in the grid.

    the important assumption here is that your cube only contains data for dates until today. if you have data for future dates then a different method needs to be used.
    Thursday, June 12, 2008 12:29 PM
  • Thanks Proffy. this should help.

    But i am not too sure about how to go about creating the named set. Do we have any link with the procedure detailed?

    Also, for data with Time values more than Today's date, what procedure would be required to follow?

     

    Thursday, June 12, 2008 12:53 PM
  •  

    Also proffy i would like to understand when exactly Named Sets are used. I am very new to BI concepts and little confused when we could use it. probably there are many areas i could start using named sets if im clear with the concept. Thanks
    Thursday, June 12, 2008 1:13 PM
  • A named set is a predefined mdx expression that returns a set. It is stored as part of the cube.

    You can use it to specify sets from any of your dimensions, using any appropriate mdx functions. With practice they can be quite powerful.

    Generally i find that named sets are quite useful when you need to use a common set of columns or rows in a number of reports, grids, charts etc.

    Try these links for a good coverage.

    quick overview:
    http://msdn.microsoft.com/en-us/library/ms174485.aspx

    excellent article:
    http://www.databasejournal.com/features/mssql/article.php/3568591


    You will also eventually find good uses for Calculated Members




    Thursday, June 12, 2008 2:23 PM
  • Excellent!! Thanks so much, this truly helps. Is it possible to create aliases for the 2 columns that appear ?

    Thursday, June 12, 2008 6:34 PM
  • to alias the columns you would need to customize the mdx query driving the grid and use calculated members.

    p.s.

    Please remember to mark the responses as answers or helpful if they were useful. thanks!
    Friday, June 13, 2008 12:11 AM
  • Proffy,

     

    We do not have a calendar hierarchy in our Time Dimension.

    I have soemthing called as Date field in my Tiem Dimension. Will this pick up Sysdate and Sysdate-1 if i use the query like below:

     

    CREATE SET IDC_Sourcing_Metrics.[Yesterday_Today]

    AS '{

    tail(NONEMPTY([Time].[Date])).item(0),

    tail(NONEMPTY([Time].[Date])).item(0).lag(1)

    }'

     

    If not, how can we pick up these values? Please help. Thanks

    Friday, June 13, 2008 2:12 PM
  • anymore inputs required here? please help

    Saturday, June 14, 2008 7:49 PM
  • that looks ok to me.. as long as the [Date] level has represents a day. Why dont you just try it out and see what results you get?

    without knowing more about your time dimension and how facts relate to dates in your cube i cant really say more.
    Sunday, June 15, 2008 1:47 AM
  • Thanks Proffy. Date has a level day representation of a day.

     

    I am not getting any value when i built the mentioned query, Not sure where i am going wrong.

    CREATE SET CurrentCube.[Yesterday&Today]
    AS '{
    tail(NONEMPTY([Time].[Date])).item(0),
    tail(NONEMPTY([Time].[Date])).item(0).lag(1)
    }'

     

    Please help, or do we have an alternative method of doing this?

    Monday, June 16, 2008 6:16 PM
  • the code you have used will only create a named set i.e. set it up in the cube.

    you still need to use it in a query e.g.

    Code Snippet

    select [Measures].[x] on 0,

    [Yesterday&Today] on 1,

    from [Some Cube]


    Tuesday, June 17, 2008 12:07 AM