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.