5 Replies Latest reply on Jan 30, 2013 10:07 PM by 872733

    [AWM] Set End_Date and Time_Span via Aggregates?

    872733
      I am creating a Time dimension in AWM with a top Level of 'All Time'. Like all other Time levels, I need to supply an End_Date and Time_Span. Unlike my other levels, however, All Times' end_date and time_span will be constantly changing every time a new year is inserted. What I want to know is, instead of having a column on the table for each, is there any way to add a SUM aggregate for Time_Span and a MAX aggregate for End_Date in AWM, so that it will be automatically updated every time the cube is maintained?
        • 1. Re: [AWM] Set End_Date and Time_Span via Aggregates?
          Nasar-Oracle
          Whatever TIMESPAN and END_DATE values you need for your ALL_TIMES top member, you have to provide it through source sql-view that is mapped to your TIME dimension. The MAX logic that you are talking about should be in your source sql view.

          Then you can maintain your TIME dimension also when you maintain your cube.

          Is that what you are asking?
          • 2. Re: [AWM] Set End_Date and Time_Span via Aggregates?
            872733
            The source we've been using are tables rather than views, I was not entirely sure if views would function as a data source for AWM.

            I managed to work around not having a direct column source for All Time, however, by setting All Time's End_Date to TO_DATE('12/31/9999', 'mm/dd/yyyy') and Time_Span to 99999999999. A tad inelegant, but it appears to do the job. If anyone has a more preferable method, please let me know.

            Edited by: islan on Jan 30, 2013 11:15 AM
            • 3. Re: [AWM] Set End_Date and Time_Span via Aggregates?
              872733
              Setting top level All Time's End_Date to TO_DATE('12/31/9999', 'mm/dd/yyyy') and Time_Span to 99999999999 appears to work, if an inelegant solution.
              • 4. Re: [AWM] Set End_Date and Time_Span via Aggregates?
                Nasar-Oracle
                (1). Generally the top node in TIME dimension is just a dummy member, since the cube data does not make any sense for ALL_TIME or ALL_YEARs etc.
                For this kind of top node, I always use some dummy date for END_DATE and a dummy number for TIMESPAN.
                There is no issue with that. This is how it is supposed to be done.

                Having a one top node for each hierarchy is a requirement for reporting, so that if a dimension is not used in a report, then the reporting tool will "LIMIT" that dimension to its top node.


                (2). One clarification. You can use table or a sql view as a source to map to a dimension or a cube. I am not sure why you said that you cannot use sql view as a source in AWM.
                1 person found this helpful
                • 5. Re: [AWM] Set End_Date and Time_Span via Aggregates?
                  872733
                  I did not say that you could not use an sql view as a source in AWM, I said that I was not sure if I could or not. From this thread, I have learned that I can.