topic Re: What is the best approach to store historical data for large volume records? in QlikView

What is the best approach to store historical data for large volume records?

RamsunB — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

In my project we are receiving 600 + millions of data in Fact table each month and we are implementing reporting on the same.

Since we want to maintain historical data along with the new or updated records, we are currently planning to create new QVD for each month. This way we would have historical as well as latest records.

Is it possible to access all QVD through single application, so that based on requirement user can connect to respective QVD? Or is there any other approach for this scenario?

Also, What would be the challenges implementing this in QliKSense?

Re: What is the best approach to store historical data for large volume records?

Anil_Babu_Samineni — Tue, 10 Dec 2019 10:08:55 GMT

Approach (1)

1) I would suggest you to trigger first month

2) Use, Join / concatenate concepts for first month and second month etc.

3) Trigger first QVD -> QVD2 -> QVD3 {I assume these are QVD Generators}

4) Finally, Load final QVD's into presentation layer

Approach (2)

1) Load all files

2) Use Incremental Load concepts for historical data

Re: What is the best approach to store historical data for large volume records?

RamsunB — Wed, 11 Dec 2019 09:52:51 GMT

Thank you for the reply.

But considering data volume this approach will not work because loading such huge data in memory and reporting on top of that will be very difficult. we may face time-out issue.

so we are looking for some other efficient approach.

Re: What is the best approach to store historical data for large volume records?

Anil_Babu_Samineni — Wed, 11 Dec 2019 13:14:02 GMT

1) Try to convenience to stakeholder and maintain data historically very less and see?

2) Is that second option also not helping your case?

Re: What is the best approach to store historical data for large volume records?

Brett_Bleess — Tue, 17 Dec 2019 20:59:25 GMT

I would recommend reaching out to your account manager to discuss the following:

https://www.qlik.com/us/products/associative-big-data-index

I believe this may be the best approach to things in the end, but it would likely be better to have a full discussion with the account team about the overall use case in order for them to help you arrive at the best solution given the current tools available, but I wanted to point this one out as an option that may fit in your case.

Regards,
Brett

Re: What is the best approach to store historical data for large volume records?

rwunderlich — Wed, 18 Dec 2019 00:00:24 GMT

There are three basic approaches I can suggest.

1. Build a separate application for each month. This will work if users only need to look at each month in isolation.

2. Build a set of "telescoping" applications.
a) Current application contains 30 days of detail and 1 year of data aggregated to the daily level for trending. You can have additional levels as well, such as 90 days aggregated to the hourly level. Depends on your data and your analysis requirements.
b) If needed, previous versions of the application can be kept that hold last month' 30 days if someone needs to go back in detail.

3. On Demand Application Generation (ODAG). Using ODAG, users filter time periods and or other dimension values in a "selection app" and then submit a reload for an on-demand app that contains just the facts matching the filters. ODAG is better documented in Qlik Sense, but it's possible in QlikView as well.

-Rob