topic Re: How to split a large QVD into year and Load into Qlikview to handle large volume? in QlikView

How to split a large QVD into year and Load into Qlikview to handle large volume?

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

Hi all,

I have more than 100 million records in one of my fact tables and I am using the incremental load functionality, when I am creating qvd's <-- that is fine and it works. When creating a Datamodel, its taking more time due outer join between 3 big fact table. So,I would like to split the large QVD's and load into Qlikview. Have anyone come across same issues, please advice.

Thanks

Re: How to split a large QVD into year and Load into Qlikview to handle large volume?

santharubban — Thu, 11 May 2017 15:56:14 GMT

Any Advice pls

Re: How to split a large QVD into year and Load into Qlikview to handle large volume?

Anonymous — Thu, 11 May 2017 16:23:18 GMT

TABLE:

load * from your.qvd (qvd) where year = 2017;

store TABLE into 2017.qvd (qvd);

or use EasyQlik QViewer and delete everything except a specific year.

Re: How to split a large QVD into year and Load into Qlikview to handle large volume?

Anil_Babu_Samineni — Thu, 11 May 2017 16:31:31 GMT

May be helpful,

partitioned QVD | QlikView Help, Tips & Hints

And, stevedark‌ marcus_sommer‌ help you reg the same

Re: How to split a large QVD into year and Load into Qlikview to handle large volume?

santharubban — Thu, 11 May 2017 16:49:36 GMT

Hi Anil,

Thanks for the reply,

I have already go though this document, in this document they have mention how to split the date but they didn't tell how to pull data after splitting the partitioned data to Qlikview.

Re: How to split a large QVD into year and Load into Qlikview to handle large volume?

Anonymous — Thu, 11 May 2017 17:13:34 GMT

I still don't know what you exactly trying to achieve,

but let's suppose you have multiple qvds within 1 folder, you could just load them like that:

LOAD * FROM [*.qvd] (qvd);

Re: How to split a large QVD into year and Load into Qlikview to handle large volume?

santharubban — Thu, 11 May 2017 17:16:27 GMT

Hi Robin,

Thanks for the reply.

I would like to have kind of partition the qlikview and load them when they needed.

Re: How to split a large QVD into year and Load into Qlikview to handle large volume?

Digvijay_Singh — Thu, 11 May 2017 17:24:54 GMT

Below script shared within our community only can be used to split qvd into quarter wise qvd, can be modified to make it year wise or half yearly.

//Generate a sample fact table with dates from 2010/01/01 to 2014/12/31

SampleData:

LOAD RecNo() AS ID,

Date(MakeDate(2010,1,1) + recno() - 1) AS Date

AutoGenerate(1826);

//Get all possible quarters and lower/upper date limits

Quarters:

LOAD Year(Date) & '-Q' & ceil(Month(Date)/3) AS Quarter,

Min(Date) AS MinDate,

Max(Date) As MaxDate

Resident SampleData

Group By Year(Date) & '-Q' & ceil(Month(Date)/3);

//Loop table Quarters

for i = 0 to NoOfRows('Quarters') - 1

let vQuarterName = Peek('Quarter', $(i));

let vMinDate = Peek('MinDate', $(i));

let vMaxDate = Peek('MaxDate', $(i));

SampleDataByQuarter:

NoConcatenate

LOAD *

Resident SampleData

Where Date >= $(vMinDate) AND Date <= $(vMaxDate);

STORE SampleDataByQuarter into SampleData_$(vQuarterName).qvd (qvd);

DROP Table SampleDataByQuarter;

Re: How to split a large QVD into year and Load into Qlikview to handle large volume?

santharubban — Thu, 11 May 2017 17:29:27 GMT

Thanks for the reply.

You have mention how to split the date ,can you pls provide me how to pull partitioned (splited) data into datamodel.

Re: How to split a large QVD into year and Load into Qlikview to handle large volume?

Anonymous — Thu, 11 May 2017 17:32:50 GMT

I don't know your data, so it's not possible for me to give you further advise...

You could also build a table with the qvd files you like to load:

TABLE:
LOAD * INLINE [
MYQVDS
1
2
AB
CDE
];

for i=1 to NoOfRows('TABLE')

LET var=Peek('MYQVDS',$(i)-1,'TABLE');
LOAD * FROM [$(var).qvd] (qvd);

NEXT

Re: How to split a large QVD into year and Load into Qlikview to handle large volume?

Digvijay_Singh — Thu, 11 May 2017 17:40:43 GMT

Loading can be done altogether using scripts like as below, but if you get all the data back in your application altogether, not sure how it is different than your current situation? If you can share script/data model, it will help.

For each vFileName in Filelist ('<Path>*.qvd')

Load *,

From [$(vFileName)];

Next vFileName

Re: How to split a large QVD into year and Load into Qlikview to handle large volume?

Anonymous — Thu, 11 May 2017 17:43:19 GMT

Hi Digvijay, is there a difference between your suggestion and

LOAD * FROM [*.qvd] (qvd);

Re: How to split a large QVD into year and Load into Qlikview to handle large volume?

santharubban — Thu, 11 May 2017 17:56:19 GMT

I have 2 table which has 100mil records till now and its growing daily.In current datamodel I am join these 2 table and creating a fact table, so loading time more and application is also slow. Due to this huge volume I would like to split the applications/QVD's, 1 with current set of data and history with respectively Qvd's \QVW's. Please advice me

Re: How to split a large QVD into year and Load into Qlikview to handle large volume?

stevedark — Thu, 11 May 2017 17:59:55 GMT

All of my thoughts on incremental load and splitting QVDs are in this blog post:

https://www.quickintelligence.co.uk/qlikview-incremental-load/

I hope that there is something that may help in there.

If you can segment your data into separate QVDs, perhaps by date or region, then you can load from just matching QVDs to get more limited data, e.g.

LOAD
*

FROM InvoiceHeader_201705.qvd (qvd);

LOAD
*

FROM InvoiceLine_201705.qvd (qvd);

This only works however if the grouped QVDs correspond.

If you need to get to the point where all the data is loaded in together, check the complexity of your keys between the tables. Make sure that you have no synthetic ones (these will kill a data model if you have many many rows). Keep with simple integer keys if you can.

You mention doing joins. If that is a JOIN (Table) LOAD in your load script then you probably want to avoid doing those anyway. Associations in the data model tend to perform better.

Hope that helps.

Steve

Re: How to split a large QVD into year and Load into Qlikview to handle large volume?

Digvijay_Singh — Thu, 11 May 2017 18:08:38 GMT

Both works but I think yours one is simpler, better.

Re: How to split a large QVD into year and Load into Qlikview to handle large volume?

Digvijay_Singh — Thu, 11 May 2017 18:18:40 GMT

- If there is any possibility you may think of implementing star schema to reduce no of hops( Currently you need to hop through 4-5 tables to associate information.)

- You can have current QVW with latest data and history QVW with old data, you can implementing document chaining to open history QVW in case required in some situations from the current QVW using triggers.

Re: How to split a large QVD into year and Load into Qlikview to handle large volume?

markp201 — Fri, 12 May 2017 00:14:05 GMT

Some of the guidelines I've used when dealing with large (50 million+) data models. These are guidelines, not hard rules but they've helped out in the past.

1. Avoid synthetic keys (looks like you did - well done with all those table). We had one synthetic and the size of the QVW doubled and the response time was minutes - not seconds.

2. Associations with numeric fields. Autonumber is very handy for this but the drawback is having to create them all in the same script.

3. Limit fact table to numeric - measures and associations

4. Push for star schema over snowflake but if you have to snowflake, the further from the fact table, the fewer rows. I'll admit - I've broken this one a few times.

Our project was also well over 100 million and we cut the time with incremental loading on the backend to QVDs then a subsequent script would build the star schema. Load time went from hours to minutes and response time was down 90% in some cases.

Can some of the outer tables be joined? For example, locations and countries? I'm no expert but all those associations could be dragging performance. Interesting model. Also, check your key coverage in the tables.

HIC has a good article on table hops. A Myth about the Number of Hops