0 Replies Latest reply: May 9, 2013 7:57 AM by 1008007 RSS

    OBI 10g merge 2 time dimensions on the same table/graph in analysis

    1008007
      Hi,

      My problem is: I have 2 different analyses (working fine) (using OBI 10g), they both use a different time dimension:
      time_dim_1 | fact1
      time_dim_2 | fact2
      I want to merge them to have something like the following:
      time_dim | fact1 | fact2
      What I managed to get so far is something like this:
      time_dim1 | time_dim2 | fact1 | fact2 (with, if we suppose there are 12 rows in time_dim1 and time_dim2, 12*12 = 144 rows instead of just 12)

      Here is some more explanations about how I set my repository:

      My fact table "issues" is made of the following columns:
      issue_id, issue_type, issue_create_date, issue_end_date
      I also have a table "calendar" with year, quarter, month and full_gregorian_date.

      In the physical layer I created 2 aliases for the "calendar" table to join with the fact table using "issue_create_date" and "issue_end_date".

      In the business model I created the 2 related time dimensions.
      Everything is working fine so far, I was able to get 2 different analyses :
      - time dimension (joined with "issue_CREATE_date") | count of issues (with aggregation rule "count" on issue_id)
      to see how many new issues are CREATED through time (are there more issues created or less)
      - time dimension (joined with "issue_END_date") | count of issues (with aggregation rule "count" on issue_id)
      to see how many new issues are ENDED through time (when are more issues ended)

      What I want is a third analysis like this:
      - time dimension | count of created issues | count for ended issues

      Thanks for your help.

      Florence