3 Replies Latest reply: Aug 12, 2014 7:48 AM by Nathan P. RSS

    Question about last function in snapshot table in obiee repository

    2726233

      This question is regarding using a snapshot table within OBIEE. We have an inventory table that shows the quantity on hand for each product by day. This table gets a new row for each product every day. An example dataset for this is:

       

      Product

      SnapshotDate

            Quantity

      Product A

      29-Jun-14

      10

      Product B

      29-Jun-14

      1

      Product A

      30-Jun-14

      8

      Product C

      30-Jun-14

      3

       

       

      In our OBIEE reports, we need to be able to report on the inventory levels at the end of the month or any given snapshot date. Within the repository for the quantity metric, we are using Last function to get the metric LAST(SUM(QUANTITY)) and specifying that “based on the dimension” we are going to get the LAST using our calendar dimension.  And the result of the report is:

       

      Product

      Quantity

      Product B

      1

      Product A

      8

      Product C

      3

       

      We are showing “Product B” because in June month that was the “Last” snapshot OBIEE found however, we only want quantities on the last day of the month, not the last snapshot available. The report should only show Product A and C because they correspond to the snapshot on the last day of the month.

       

      I’m looking for help on how to define this within the repository to get the quantity on hand for the last day of the month, not the last snapshot available. Has anyone had experience with a similar snapshot table within OBIEE?

       

      Thanks for your help.

        • 1. Re: Question about last function in snapshot table in obiee repository
          Nathan P.

          You shouldn't need to do any repository work to get your desired results. Prompt your report on the snapshot date (either via a dashboard prompt or local report prompt). Set your default selected value to either a created repository variable that gets you the last day of last month (if you don't have one, it's very handy...see this documentation to create one: Using Variables in the Oracle BI Repository - 11g Release 1 (11.1.1)), or set your default value using logical SQL. I'm going to make an assumption that may not be valid and assume the only field you have in Date is a filed called Calendar Date. The following Logical SQL will get you the last day of a month with that assumption:

           

          SELECT

               MAX("Date"."Calendar Date")

          FROM

               "Subject Area"

          WHERE

               YEAR("Date"."Calendar Date") = YEAR(TIMESTAMPADD(SQL_TSI_MONTH, -1, current_date))

               AND MONTH("Date"."Calendar Date") = MONTH(TIMESTAMPADD(SQL_TSI_MONTH, -1, current_date))

          • 2. Re: Question about last function in snapshot table in obiee repository
            2726233

            Thanks Nathan, but what happens is I need to get the last snapshot when I select a range or a group of dates.  Because I will combine the Snapshots Fact with a Accumulative  Fact.  For example, I have snapshots for Inventory and Accumulative for Sales;  If I choose two weeks, I will want to get the SUM(Sales) for those two weeks and the SUM(QTY) for last day.

             

            In SQL statement, we can get the report using something similar to this:

             

            SELECT SUM(Sale) AS Sales

                          , SUM(Qty) AS QtyInventory

            FROM FACT_SALES S

                      INNER JOIN DATES D

                                ON D.DATE_ID = S.DATE_ID

                                         AND DATE_WEEK BETWEEN (week1 AND week2)

                       FULL JOIN INVENTORY I

                       INNER JOIN (SELECT MAX(SNAPSHOT_DATE) SNAPSHOT_DATE_MAX

                                                    FROM INVENTORY I

                                                              INNER JOIN DATES D

                                                                    ON D.DATE_ID = I.DATE_ID

                                                                       AND DATE_WEEK BETWEEN (week1 AND week2)

                                                    ) II

                                ON I.SNAPSHOT_DATE = II.SNAPSHOT_DATE_MAX

            • 3. Re: Question about last function in snapshot table in obiee repository
              Nathan P.

              So, you can do that with Logical SQL, but it does the joins on the application server. Also, the last time I used Logical SQL for a dashboard report, it behaved very strangely with prompts (you had to refresh the page each time after applying).

               

              I think the best we can do is to use a presentation variable from a dashboard prompt and substring the last date from it. The risk you run there is no data will return if there isn't any data returned for that date. You can usually control users' expectation by sticking a text view on the dashboard to let them know what the ranges of an acceptable end date (only select business days, last day of previous week, etc.).

               

              If you create or modify an existing dashboard prompt for Dates.Date, you can set the operator to IS BETWEEN and set it to drive a  presentation variable, we'll call it 'selected_dates'. When you apply it, it will send a value to the page like this:

               

              2014-01-01 00:00:00,2014-04-03 00:00:00

               

              Set a filter for date to 'is prompted' on your report and then define the formula for your facts:

               

              Sales: Just use the raw aggregated definition of this field. It will listen to the prompts and get the value by the date range

               

              Quantity: FILTER("Inventory Fact"."Inventory Quantity (Sum)" USING "Dates"."Date" = CAST(REPLACE(SUBSTRING('@{selected_dates}{2014-01-01 00:00:00,2014-04-03 00:00:00}',21,10),'-', '/') AS DATE))

               

              Assuming your facts aren't in the same table and "Dates" is conformed to each fact, I think this should get you what you're after. I have to replace the dashes with forward slashes in my timestamps...your mileage may vary.