topic Re: Incremental load with Clarity tables in Archived Groups

Incremental load with Clarity tables

neelamsaroha157 — Tue, 22 Jul 2025 14:29:37 GMT

Hi Champs,

Our organization is in process of moving from Meditech to Epic.

Currently most of the QVD generators are using Incremental load based on the last 'Modified Date'.

But from our Epic guys I heard that there is No row modified datetime in clarity tables. They say that they can turn this feature On for some of table but it impacts the performance badly.

Does anyone have solution for this??

Appreciate you help.

Thanks

Qlik Epic Developers Group‌

Re: Incremental load with Clarity tables

Thu, 21 Jul 2016 18:58:30 GMT

That's not quite true. In most of the important tables, there is an "Update Date" column that you can use to determine when the row was most recently extracted. Most of the important tables also have the "row update tracking" feature turned on. (This is the feature that Epic referred to saying that it impacts performance badly.)

The Epic Cogito DW uses this "row update tracking" table (CR_STAT_ALTER) as its mechanism to determine what to extract. So you can rest assured that the most important info is probably already tracked.

Now, having said that, I do not believe that you need to go this route. Here are some other things to think about
1) do you really need to do incremental? You might be surprised how quickly a full refresh goes.

2) do you really need the QVD layer? If you have the warehouse and some good views, can you go against those directly?
3) if you do need incremental, can you do a "reasonable lookback" combined with a weekly or monthly full refresh?

You're headed down a long and expensive path if you start building out an incremental ETL from clarity. Consider alternatives before proceeding.

Dave

Re: Incremental load with Clarity tables

neelamsaroha157 — Thu, 21 Jul 2016 19:45:21 GMT

Hey David,

Thanks for your time and sharing your valuable knowledge.

1. As I said we are in process of implementing Epic, so practically I have no idea how much time it would take to do a full reload. So this could be one of the possibility.

2. Yes we would need QVD layer because the current system has multiple layer of QVDs for extraction & Transformation and we have to map these existing applications to Epic data once Live.

3. Weekly or Monthly refresh is a possibility but then I have to go back to my team and discuss.

I would get back once decision is made.

Thanks a ton...

Re: Incremental load with Clarity tables

dclark0699 — Fri, 22 Jul 2016 12:21:54 GMT

David brings up some good points, but I thought I would share our experience as well.

We've had Epic since 2006, which was before some of the new features that make it easier to track changes in Clarity for the Cogito Data Warehouse. We had a homegrown EDW that needed to incrementally load from the larger tables.

The path they took, was our Clarity admin tacked on a custom column to any tables without an update_date field that simply displayed the date of the extract from Chronicles.

Those fields have been very helpful for us now with Qlikview to drive incrementals from. Once we go live with the Cogito Data Warehouse we plan to investigate using CR_STAT_ALTER, which to our understanding will help with deletes and avoid a costly primary key inner join on tables where rows can be deleted.

If you do go the incremental route, I would suggest using paramaterized external functions to handle your incremental loads rather than building an incremental script for each individual table. It helped make our process much smoother and easy to maintain.

Re: Incremental load with Clarity tables

neelamsaroha157 — Fri, 22 Jul 2016 12:39:54 GMT

Hey Donnie,

Thanks for sharing your experience.

I would surely consider your point of using parameterized external function.

Thanks

Re: Incremental load with Clarity tables

jstemig1 — Thu, 28 Jul 2016 16:33:12 GMT

+1 for David's "other things to think about" comments. And agree, we have had a good experience leveraging CR_STAT_ALTER to keep our objects in sync (even CLARITY_TDL_TRAN).

Re: Incremental load with Clarity tables

Anonymous — Thu, 04 Aug 2016 19:50:36 GMT

We use QVD files as our data layer (Clairty to QlikView QVDs...not other data tool).

We extract tables in their raw form from Clarity and then process them into our staging and final stage environment using QlikView (and more QVD files). We found this to much faster and lower maintenance than views in Clarity (which we tried first).

For the dimensional type data, we do complete extracts daily. For many of the fact based tables, we do not do a FULL reload, but we reload any of the transactions in the last 6 months each night. Financials are the exception as our extracts are incremental based on Posted Dates. Each day of the week, we do a full reload of a past year for all transactional data sets (including financials) to catch any of those changes.

We had issues with the incremental loads as we encountered scenarios where the flags were not set appropriately in Clarity (in fact, we have seen examples where EPIC did not flag correctly to push changes to Clarity). With the above threads, it sounds like these issues may be less prevalent today.

We do not use Cogito Kaboodle as our data warehouse, but as a supplement to it. If we were going live today, we would likely give Kaboodle a serious look...however it is (yet) worth rebuilding our entire data warehouse.