2

Creating Snapshot tables with Incorta

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.

16replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Nice article

    Reply Like 2
    • Anurag Malik Nice article Thanks! Will you please let me clear little bit more.

      1. 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> 

      ==>> Here in this option where do I create new table in DB or in Incorta, and where do I add new column called snapshot_dt with the value of current date (sysdate for oracle)?

      2. Turn on the incremental option and copy the same sql into that.
      for eg - select B.week_end_date snapshot_dt, A.* from<table> A, Dates B where b.date = sysdate

      ==>> Please elaborate this.

      ** I want to store my per day's dashboard snapshot for a month, so in the next month I can easily get the no of count of student present per day in a class. 

      Please guide, your's suggestions were very much helpfull for me.

      Kind Regards

      Rudra

      Reply Like
    • Reply Like 1
  • Anurag Malik  thanks for your reply, it seems to be helpful. But here we are facing one issue when we gave  query  "select sysdate Snapshot_date, class_id,class_name from school_data;" and tried to save then we got the attached warning. Should we define a key in our query? It also says that on incremental load the new data will be added to existing data without any updates.

    Does it mean snap_shot data will be overridden? Could you please help us on this point.

    Thanks in advance.

    Reply Like
  • Can we do this for data file also? Like I want to put all days/date updated data in Incorta as a Snapshot.  I tried it but Snapshot Load is not working at schema level. Please suggest.

    Thanks

    Rudra

    Reply Like
    • Rudra How are you adding the sysdate ? Is it in the file column and that is defined as a key field ?

      Reply Like 1
    • Amit Kothari yes, I created sysdate in file column and defined it as a key in Incorta

      Reply Like
    • Amit Kothari  some more questions, please help.

      1. when I'm trying to load my schema with snapshot load, its not getting loaded. Will you please explain.? Should we make any changes to load snapshot data? 

      2. As snapshot load is not working I did incremental load and after that when I'm trying to make report in schema its showing only one data against every snapshot_date. Its not getting previous as well as todays data, only showing one data against every column in report. 

      Please help. 

      Thanks

      Rudra

      Reply Like
    • Rudra Can we do a quick zoom? PM me your email address.

      Reply Like 1
  • Make sure that the scheduled job for this is set to type Incremental

    Reply Like 1
  • Amit Kothari  Thanks for helping us last week. We are able to do an incremental load of data and show the daily snapshot properly. The load is happening fine for last 1 week. Thanks again.

    Also wanted to know few things:

    1. Because of the daily incremental load , data keeps growing. What all would be the impacts of that. Will this impact the performance of the dashboard ?
    2. Is there any way to remove older data ? Suppose we decide a data retention period of 30 days, is there a way to remove data older than 30 days. Also how do we automate that so that after everyday incremental load, it still keeps only last 30 days data.

    Reply Like
    • Rudra  Any chance the steps to accomplish this could be posted? We are trying to snapshot a data file as well with an Incremental Load and are running into problems.

      Reply Like 1
    • Mark Samuels  - Can you send me a mail with the issue and we can have a zoom meeting?

      Reply Like
    • Make sure that the keys are setup correctly.

      Reply Like 1
    • Hi Mark Samuels  make snapshotdate column and the primary column in the table as a KEY function, and load type will be incremental. It will work.

      For more please please follow this video:

      https://drive.google.com/file/d/0ByhzLe5x9-DlQ2hKVGRQTkpkTlE/view?usp=sharing

      Reply Like
  • Thanks Rudra, Amit also called me and walked me through it!

    Reply Like 1
Like2 Follow
  • 2 Likes
  • 5 days agoLast active
  • 16Replies
  • 310Views
  • 4 Following

Welcome!

Welcome to Incorta's user community! Whether you are already an Incorta customer, or interested in becoming one, this is your place to come together and discuss the software, register for webinars, learn about events, learn about new product releases and get support from the community.