3 Replies Latest reply: Jan 14, 2013 9:36 PM by 971735 RSS

    Time Dimension Structure

      Okay, I am familiar that the structure of the Time dimension requires the following items for each level of its hierarchy:


      Now, from what I have gathered, the Member is the unique key/ind field, and the lowest level of granularity should have a Time_Span of 1, and all other levels should have a Time_Span equal to the number of lowest-level items contained within it. Do I have that correct so far?

      Now, as for the End_Date; does that need to contain the exact key/ind of the last record? Or can it be the Long_/Short_Description?

      Finally, how does the Time dimension know the order of items? Is it an Order By Member?

      Thanks in advance.

      Edited by: Kleinknecht on Jan 14, 2013 6:10 AM
        • 1. Re: Time Dimension Structure
          I attempted to maintain a test cube, but I received the following error on the TIME compilation:

          ORA-01840: input value not long enough for date format

          Does that mean I actually have to use a DATE format in the Time dimension? I am working with academic terms with a term being the smallest granularity, so I don't think DATE can really apply...
          • 2. Re: Time Dimension Structure
            (1). What are the levels in your TIME dimension, with a brief explanation of each level?

            (2). Will you be defining time-series calculated measures (like YTD, Prior-Period etc.), using this TIME dimension?

            Generally the concept is simple.
            For example, if TIME dimension has DAY -> MONTH -> QTR -> YEAR levels, then
            DAY will have timespan of 1 and END_DATE will be the date of the member at this level
            MONTH will have timespan of 30 or 31 (or less for February) and END_DATE will be the last date of the member at that level
            and so on ... for QTR and YEAR level members.

            END_DATE attribute in olap is defined as DATE datatype and so is the relational table/view column datatype to which END_DATE is mapped.

            I think Ordering of time dimension members is set based on the levels and END_DATE attribute.

            When a dimension is tagged as a TIME dimension in AWM, then there are few structures defined behind-the-scenes to simplify the creation of time-series measures. We can then use the calculation templates in AWM to create time-series calculated measures.

            NOTE that (although rare, in some cases) you can create a time dimension and may set it as USER type dimension instead of TIME type dimension.

            • 3. Re: Time Dimension Structure
              Thanks for the response.

              My Time dimension has the following Levels:

              Academic Year
              Calendar Year

              And will have the following two Hierarchies:

              Academic Year -> Term
              Calendar Year -> Term

              I went ahead and did some testing, and this is what I've come to discover:

              (1) Your lowest level of granularity, whatever it is, must have a key that is of DATE type (this is the Member field in AWM). So in my case my term_key has to be DATE. For my purposes, I just made each term have a term_key equal to the first day of that term. So, for example, Fall 2003 would be represented as TO_DATE('08/01/2003', 'mm/dd/yyyy') {not accurately the first day of the term, but close enough for this time dimension}.

              (2) As you say, the Time Span of the lowest granularity level (aka 'base granule', typically Day) is always 1, then each level above it is a Count of all the base granularity within it. So in my case each Academic Year is a count(*) of all of its associated Terms, likewise with Calendar Year.

              (3) Also, as you say, each End Date has to be a DATE--as its name implies. Also, the End Date of your base granule looks like it has to be equal to itself, so I have foregone even having a Term_End_Date and instead point the AWM field to Term_Key. I then assigned my Academic and Calendar Year End Dates based on a MAX(term_key) query.

              So far these conclusions appear to be accurate within testing. If I am in error somewhere, please correct me!