1 Reply Latest reply on Jan 31, 2013 10:02 AM by Paul

    two date column in a table

    user1299751
      I have a Sales Data warehouse of Retail shops which operate 24x 7 with 2 shifts of 12 hours each. Time for each shift is 7 to 7.

      I have a Calendar Dimension which has all the dates listed.

      TABLE_CALENDAR
      -DAY_KEY NUMBER
      -DAY_DATE NUMBER

      I have a Sales table which has two dates columns: one is the Sales Date whereas another one is the shift date. So there are two joins (Foreign keys) from Sales Table to the Calendar Table.

      TABLE_SALES
      -SALES_KEY
      -SALES_DAY_KEY
      -SALES_TIME_KEY
      -PRODUCT_KEY
      -SHIFT_KEY
      -SHIFT_SUPERVISOR_KEY
      -SHIFT_CASHIER_KEY
      -SHIFT_DAY_KEY
      -SALES_QTY
      -SALES_AMOUNT
      -COST_AMOUNT


      I need to develop an analysis on these tables where the Area managers look at the sales by day, month and year and Shift Supervisor and Shift Managers want to look at sales by shifts. (Shift Date).

      When I define two joins in OBIEE Administrator, it say that I can have a single join from one dimension. (From Calendar Dimenstion to Sales). this way I can report only by Sales Date.

      How can I report by Shift Date?