topic Re: Salesforce data incremental load issue in QlikView

Salesforce data incremental load issue

Wed, 23 Jan 2013 10:40:27 GMT

Hi All,

In my dashboard I am using the below code to incremental load Salesforce.com data. This code is taken from the demo app that is available on QlikView web site.

I am not able to understand how this code handles the Accounts that get deleted within SFDC? After running the below code for 30 days, I now have a around 100 accounts in my QVD which are not in SFDC (deleted ones). I can't shift to full load as its a huge data and refresh cycle is every 30mins.

Can any one had similar problems?

Account_SQL:

SQL SELECT

*

FROM Account

WHERE LastModifiedDate >= $(vLastExecTime) and LastModifiedDate < $(vExecTime);

// For the 1st reload, this section will be skipped.

if Not $(FirstReload) and not isnull(QvdCreateTime('$(vQVDPath)Account_SQL.QVD'))then

Load *

FROM $(vQVDPath)Account_SQL.qvd (qvd)

WHERE NOT EXISTS(Id);

end if

if NoOfRows('Account_SQL') > 0 then

STORE Account_SQL INTO $(vQVDPath)Account_SQL.qvd;

Account:

Load

*

RESIDENT Account_SQL;

DROP TABLE Account_SQL;

End if

Regards,

Murali

Re: Salesforce data incremental load issue

jagan — Wed, 23 Jan 2013 11:12:36 GMT

Hi Muralidhar,

If you want to remove the deleted records from the QVD then you need to fetch the ids of the users and do the inner join to the qvd. Please check the script below from Help file

Let ThisExecTime = Now( );

//Load modified records from Database

QV_Table:

SQL SELECT PrimaryKey, X, Y FROM DB_TABLE

WHERE ModificationTime >= #$(LastExecTime)#

AND ModificationTime < #$(ThisExecTime)#;

//Concatenate already existing records in QVD to the records modified/inserted in the database

Concatenate LOAD PrimaryKey, X, Y FROM File.QVD

WHERE NOT EXISTS(PrimaryKey);

//Do inner join to remove the duplicates

Inner Join SQL SELECT PrimaryKey FROM DB_TABLE;

Hope this helps you.

Regards,

Jagan.

Re: Salesforce data incremental load issue

Wed, 23 Jan 2013 11:19:11 GMT

I have done this but the final Inner join is taking long time.

Are there any other alternate options available?

Re: Salesforce data incremental load issue

jagan — Wed, 23 Jan 2013 11:42:21 GMT

Hi Muralidhar,

I think there is no other option you have to do the inner join then only the deleted records are eliminated.

Are you getting only the primary value or retrieving all the values?

ie., Inner Join SQL SELECT PrimaryKey FROM DB_TABLE;

or using

Inner Join SQL SELECT * FROM DB_TABLE;

Since Select * takes long time to load.

Regards,

Jagan.

Re: Salesforce data incremental load issue

Wed, 23 Jan 2013 12:55:08 GMT

Yes. I did only give the primary key column in the inner join select.

The incremental load without inner join finished in 8 mins for 9 tables. where as

The incremental load with inner join is running from past 90 mins and still 3 tables are left to complete.

So I am sure I can't go with this Logic.

Re: Salesforce data incremental load issue

jagan — Wed, 23 Jan 2013 13:22:55 GMT

Hi Muralidhar,

Instead of joining directly fetch data and store in resident table and then inner join.

Temporary:

SQL SELECT PrimaryKey FROM DB_TABLE;

Inner Join LOAD * RESIDENT Temporary;

DROP TABLE Temporary;

Hope this helps you.

Regards,

Jagan.

Re: Salesforce data incremental load issue

Fri, 01 Feb 2013 03:39:40 GMT

Even the resident load didn't give much improvement with respect to load times.

The work around I have used is (in agreement with business) to run the deleted records logic (inner join) only during the first reload of the day. Rest all reloads follow normal incremental load.

The only downside of this is, the deleted records of today will only reflect tomorrow in the dashboard. Business are kind of ok with it.

Thanks all for your valuable inputs.

Re: Salesforce data incremental load issue

Fri, 08 Mar 2013 01:09:21 GMT

Hi Muralidhar,

I notice that you had successful connecting and extracting data from Salesforce.com table using the demo app from qlikview site.

I was using the same demo app, however connection tested successful but when run the scripts, it return me zero record. I try create select statement but no tables display on my screen.

I am using SalesForceDLL.dll version 11 and Qlikview version 11.

Is there additonal requirements to be setup in Salesforce.com?

Thanks and appreciates your expert advise.

Roslan.

my email is rlan1308@gmail.com

Re: Salesforce data incremental load issue

Fri, 08 Mar 2013 06:59:11 GMT

Roslan,

Please double check the user id that you are using to connect to Salesforce.com has sufficient previllages to read data.

Regards,

Murali