1 Reply Latest reply on Nov 4, 2016 4:32 PM by Yuriy Fal

    How to create a Date type dimension combining two date type dimensions?

    Isabel Font

      Hi! I have a table with 3 dimensions: username, creation date, deleted date. Using this table I can calculate the amount of users we had in a specific period of time (I use the creation date and deleted date as filters). But I don't know how to modify these dimensions to be able to see the evolution of the amount of users over a period of time. What I would like to do is to create a DATE type dimension that, combined with the username dimension can show the evolution of the amount of users. I don't know if this is possible or if there is an easier way to do it.


      Thank you very much!

        • 1. Re: How to create a Date type dimension combining two date type dimensions?
          Yuriy Fal

          Hi Isabel,


          It is the data structure (a common name for it is an "Accumulating Snapshot")

          that is not suitable for the task. The problem you want to solve requires the data

          to be re-shaped into another structure (a common name is a "Periodic Snapshot").


          This data structure would consist of distinct rows

          for every Username and Date combination

          where Date >= Creation Date AND Date < Deleted Date.


          There are two common ways of re-shaping data

          from an accumulating snapshot to a periodic one.


          The former is by using a range-Join with a Calendar dimension

          (which could be just a single Date column table).

          Then simply counting Users per Dates would get the answer.


          The latter is by making a so-called Transaction table

          (the third main type of a fact table mentioned here)

          by splitting every row into two (User-Creation / User-Deleted).

          Then a RUNNING window calculation could make a final result.


          I've taken both routes using a Sample Superstore as a datasource.

          It has characteristics of an Accumulating Snapshot, namely,

          Order Date and Ship Date for each Order ID.

          So counting Orders to Ship would be very similar to your question.


          The first route (Join with a Calendar dimension) is going via Cross-Join & Filter,

          since there is no Non-equi Join option for Excel / Text datasources.

          I'm using a common technique with the Link ID field (to join on 1 = 1).


          The second route taken is going via Pivot option (for Excel / Text DS).


          Please find the attached.

          Hope it could help.