loading multiple QVD files to the same table

[#1]

So I am here with some usage data fetched from database and stroed in QVD files (one per month) and want to read the data back to a QVW.

I tried to read them in a schedule fashion where I only want the three latest months data:

UserUsage:
LOAD * FROM statData\$(firstyear)-$(firstmonth)-UserUsage.qvd (qvd);
LOAD * FROM statData\$(secondyear)-$(secondmonth)-UserUsage.qvd (qvd);
LOAD * FROM statData\$(thirdyear)-*-UserUsage.qvd (qvd);

(firstyear, firstmonth and so on are populated with the actual years and months according to a calculation earlier in the script)

When executed I get this in the log:
UserUsage << 2009-05-UserUsage 152242 lines fetched
UserUsage << 2009-06-UserUsage 181930 lines fetched
UserUsage << 2009-07-UserUsage 211618 lines fetched

It reads the files alright but only 211618 lines are noted in document proporties, more in the line of 545000 was my wish.

So I thought I change the code a bit.

UserUsage:
LOAD * FROM statData\$(thirdyear)-*-UserUsage.qvd (qvd) WHERE usageType = 1 OR usageType = 3 OR usageType = 15;

here I load the last years Usage data, will not be too useful the first month in a year but that's another problem. The log looks more or less as the one for three months moving but starting with 01, 02 and so on. The result is however the same.

How do I append the data from multiple QVD files to the same table? I can't do that? Then I have to reread the complete database every day and that doesn't seem like a good idea at all.

[#2 · Accepted solution]

oooooh!

This is one of those times where you ask the question and just when you submit the answer pops up.

The numbers next to the lines is not actually number of lines from the files, it's the number of lines read in to the table. It accumulates as the load progresses. So my first solution was indeed correct and there were no issue 😃

[#3]

Hello,

I need to load in qvw from multiple QVD files stored in a folder.

the number of QVD files is variable, so I need to do a loop for each QVD file stored in that folder.

Can you help me?

Thanks in advance.

[#4]

I'm a newbie in this but I understand you can use the execute command to execute cmd and so you could use the move functionality

Execute cmd /c move /y Dax.qvd Dax_Old.qvd;

[andy]

One way is to use a * as a wildcard

Load

*

From thefolder/*.qvd

[AbhijitBansode]

Hello,

I need to implement somewhat like you have implemented.

My datawarehouse fact contains near about 70 million records .

Each month fact is loaded with nearly 1.2 million data and is always contains data of 5 years (current+last 4 years).

I want to create architecture to load this data in QVW application.

So i decided to go with QVD files for fetching data from database and QVD files will be source of my QVW application. I want to know which are the best prectices to created QVD file. Below are some of my queries

1. How may QVD files to create?

2. In case if i choose to go with multiple QVD option. How shold i implemet inceramental load in this case?

Any help on this will really help me to proceed with robust architecture.