A snapshot table holds the same transactional data as its source system, with additional fields for tracking the snapshot date. Snapshot tables are populated with those rows of data that are considered effective (i.e., current) in the source table at the time of the extract. We can capture a specific point in time – say inventory or how much they are owed in Accounts Receivable – so that they can refer back to it later. Then you can trend back and compare today vs. last week or last month. This information is very basic in business intelligence because we want to compare today vs. something.
Step-by-step guide
Dense Snapshots - A process for loading data and creating new snapshots for every day. This approach is not very memory efficient and does not scale well for huge data sets.
Let's take an example of how to create a daily snapshot table -
1. Use the Schemas page to select the schema to which you want to add the snapshot.
2. Create a new table and in the select clause add a new column called snapshot_dt with value of current date (sysdate for oracle) and create keys on snapshot_dt and the id field. for eg - select sysdate snapshot_dt, * from <table>
3. Turn on the incremental option and copy the same sql into that. for eg - select B.week_end_date snapshot_dt, A.* fromA, Dates B where b.date = sysdat
Note: To do weekly or monthly snapshots, we can join to the the Dates table and select the week_end_date or month_end_date from the dates table as the snapshot_dt field.
Sparse Snapshots - A process for loading only modified data based on the changed date. This approach is very memory efficient and quite scalable for huge data sets.
1. Use the Schemas page to select the schema to which you want to add the snapshot.
2. Create a new table add the select clause and create keys on last_modified_date (date datatype) and the id field. for eg - select * from <table>
3. Turn on the incremental option and copy the same sql into that with a where clause of last_modified_date > ?.
We can rename the last_modified_date field as 'AsOfDate' This will create a new row for every id whenever the last_update_date changes.
Now in the Dashboard we need to do these steps for reporting on any As Of Date -
1) Create a new Dashboard
2) In the Filters and Prompts section create a presentation variable called AsOfDate.
3) Go to the Analyze mode , select the columns you need and add these filters conditions3) First filter will be on AsOfDate 'Less than Equals' $AsOfDate
4) Second filter will be on the id field using 'Last Version' on AsOfDate
5) The report will now correctly return the rows as of any date chosen in the Prompt.
.png "Employee"){kind=icon}
