Trouble with Incremental Data Loading into QVD File

[YashodaAnalytic]

Hello community,

I'm having trouble adding incremental data into a QVD file named YH1.qvd. Here's the code I'm using:

TableYH1:
Load *
FROM [lib://DataFiles/YH2.qvd]
(qvd);

concatenate

LOAD
*
FROM [lib://DataFiles/YH1.qvd]
(qvd);

store TableYH1 into [lib://DataFiles/YH1.qvd] (QVD);

The issue is that I'm unable to change YH1.qvd. Can someone help me understand what's wrong?

Regards
Bhavya

[RamanaKumarChintalapati]

Hi @YashodaAnalytic ,

Please find the below sample script for Incremental Loading.

QV_Table:

SQL SELECT PrimaryKey, X, Y FROM DB_TABLE

WHERE ModificationTime >= #$(LastExecTime)#;

Concatenate LOAD PrimaryKey, X, Y FROM File.QVD

WHERE NOT Exists(PrimaryKey);

STORE QV_Table INTO File.QVD;

Thanks,

Ramana Kumar Chintalapati.

[YashodaAnalytic]

Hi 

Thank you for your response. I am currently using the Google Analytics 4 Connector to retrieve incremental data that needs to be added to the YH1.qvd file. Can we achieve incremental loading without using SQL Select? Here's what I have so far:

LIB CONNECT TO 'Google_Analytics_4 - a*************@gmail.com';

QV_Table:
SQL SELECT landingPage,
date,
sessions,
totalUsers,
newUsers
FROM RunReport
WITH PROPERTIES (
propertyName='properties/########',
dimensions='landingPage, date',
metrics='sessions, totalUsers, newUsers',
dateRange='2024-04-26,2024-04-26'
);

Concatenate

LOAD
*
FROM [lib://DataFiles/YH1.qvd]
(qvd)
where [RunReport.date] <> '2024-04-06';

store QV_Table into [lib://DataFiles/YH1.qvd] (QVD);

Can someone please advise on this?

[Or · Accepted solution]

What do you mean, exactly, by "I'm unable to change YH1.qvd"? Is there some sort of error message?

[YashodaAnalytic]

There's no error message; it's just that the new data isn't being added to the existing YH1.qvd.

Can someone please advise on this?

[Or]

I'm kind of confused about what you're doing. There doesn't seem to be a RunReport.date field in your original SQL query, so how is that getting populated in your QVD?

I'd suggest looking at the generated data model in QV and/or clean-loading the QVD to see what it contains. It's hard to try and troubleshoot without seeing the data and behavior.

[YashodaAnalytic]

Hi @Or 

The piece of code I gave you earlier was only a small part, and I forgot to make the necessary changes. Here's the updated version of the code.

QV_Table:

LIB CONNECT TO 'Google_Analytics_4 - *************@gmail.com';

Load landingPage as RunReport.landingPage,

date as RunReport.date,

sessions as RunReport.sessions,

totalUsers as RunReport.totalUsers,

newUsers as RunReport.newUsers;

SQL SELECT landingPage,

date,

sessions,

totalUsers,

newUsers

FROM RunReport

WITH PROPERTIES (

propertyName='properties/************',

dimensions='landingPage, date',

metrics='sessions, totalUsers, newUsers',

dateRange='yesterday,yesterday'

);

QV_Table1:

NoConcatenate

Load

*

Resident QV_Table;

drop table QV_Table;

Concatenate

LOAD

    RunReport.landingPage,

    RunReport.date,

    RunReport.sessions,

    RunReport.totalUsers,

    RunReport.newUsers

FROM [lib://DataFiles/YH1.qvd]

(qvd)

where [RunReport.date] <> Today()-1;

store QV_Table1 into [lib://DataFiles/YH1.qvd] (QVD);

Could you please suggest an approach?

Regards
Bhavya

[Or]

After you do this, what does your data model look like in the app? Does the app contain data for yesterday at all? Are the fields filled out correctly?

This shouldn't be related, but why are you making two passes for the original query? You can just concatenate the QVD to the original SQL load without a second pass.

[YashodaAnalytic]

Hi @Or 

This is how my data model is structured:

Yes, QV_Table1 contains yesterday's data, and data is properly received.

As for the two passes in the original query, there's no specific reason; it can be structured this way also-

QV_Table:

LIB CONNECT TO 'Google_Analytics_4 - analyticsyashoda@gmail.com';

Load landingPage as RunReport.landingPage,

date as RunReport.date,

sessions as RunReport.sessions,

totalUsers as RunReport.totalUsers,

newUsers as RunReport.newUsers;

SQL SELECT landingPage,

date,

sessions,

totalUsers,

newUsers

FROM RunReport

WITH PROPERTIES (

propertyName='properties/276511077',

dimensions='landingPage, date',

metrics='sessions, totalUsers, newUsers',

dateRange='yesterday,yesterday'

);

Concatenate

LOAD

    RunReport.landingPage,

    RunReport.date,

    RunReport.sessions,

    RunReport.totalUsers,

    RunReport.newUsers

FROM [lib://DataFiles/YH1.qvd]

(qvd)

where [RunReport.date] <> Today()-1;

store QV_Table into [lib://DataFiles/YH1.qvd] (QVD);

[Or · Accepted solution]

So, the table in the app contains everything, but the QVD itself does not? And there are no error messages? That is indeed kind of strange...

Things to try for debugging purposes:

1) Try adding a Sleep(5000) command before the Store (In case the QVD isn't being released for writing, though that should typically result in an error)

2) Try writing to a different QVD and see if that works (Like the above, in case it's locked or otherwise problematic)

3) Try clean-reading the QVD in a new app to confirm that it does not contain yesterday's data (To confirm there isn't an issue with the app itself somewhere)