Facing Problem during Data Load using BEx connector

[vijayaganesh_s]

Hi All,

I'm Facing problem while loading data from SAP BW using BEx connector when there are more than 500000 Rows.

There is no problem while loading the Query which has less than 500000 Rows but the problem occurs when row number exceeds 500000.

In order to limit the data during loading I'm loading data based on filtering Particular feilds like Month,Place,ProgramName this method is working fine for all Except one Month, Place, ProgramName combination of filter which means that particular combination of filter itself having more than 700000 records.

So I want to reduce the number of Records to be loaded / fetched from SAP BW through BEx connector.

Can Anybody please help in this scenario to limit the Number of Records or Any Alternate solution for this problem.

Regards,

Vijayaganesh S

[teiswamsler]

Hi Vijayaganesh

The fasted solution atm is to slice your data extract down, - try with days.

Try read part with

Limitations and suggested workarounds

https://help.qlik.com/en-US/connectors/Subsystems/SAP_Connectors_Help/Content/Connectors_SAP/Clients...

/Teis

[quriouss]

Yes, this is the best way (that I know of).  There are some settings in BW itself where you can increase the 500000 limit, but that would be decision for your SAP basis team (and is you're already at 700000 cells then you risk hitting the new limit soon enough).

Look at looping through days in your query, rather than months.  And if you can, try an incremental load (load only the records since your last load).

These should all increase speed and reliability of the loads.

[vijayaganesh_s]

Hi teiswamsler

Thanks for your reply.

[vijayaganesh_s]

Hi Simon,

I'm doing the same looping for loading data.

But the things there is not only one place there are more than 15 places and 15 program and 12 months each which means 15 * 15 * 12 = 2700 iterations and If I apply for day wise filtering means It will complex the loading. Already it is taking 10 + hours. So I don't want to increase the loading time by filtering by Date.

That's why I asked is there is any SAP BW query to LIMIT the data load like LOAD only first 400000 records and then loading other set.

Like In SQL we use query for limiting

SELECT *

FROM table_name

LIMIT 1000;

If you have any alternate solution means please let me know that would be more helpful.

Regards,

Vijayaganesh S

[quriouss]

There is no solution (that I am aware of) to restrict the volume of data loaded from BEx.

But if your load is already taking 10 hours (??? that doesn't sound good ???) you probably want to look at an incremental load in any case;

1.  Load all data, save to QVD (one-time job)

2.  Look in QVD for most recent date

3.  Load only new/changed records from that date onwards

4. Concatenate with QVD (very, very fast)

5. Save updated QVD

6. GoTo 2.

[vijayaganesh_s]

Hi Simon,

I'm Already doing this only and I'm looking for alternate solution for Loading data from SAP BW other than BEx connector.

Like I'm trying in Composite Provider to load data it is much faster than BEx but QlikSense is not displaying the same Composite Provider Queries in All environments like those Composite providers are visible in EBA,PBA but it is not visible in QBA.

Do you have any idea over this ?

Regards,

Vijayaganesh S

[quriouss]

There are three methods which I use to get data from BEx queries;

1. the OLAP provider from SAP (mdrmsap.dll - installed if you install BEx Query Designer)

2. The SAP BEx extractor from Qlik (needs some system changes on your Bex server to get working)

3. SAP SQL connector - this alllows direct access to the BW tables - I don't recommend this as you'll have to rebuild your business-logic.

*All* of them run at about the same speed, and the only way I've found to get the loads (many tens of GB in my case) running at a decent speed is an incremental load.

(I assume EBA / PBA and QBA are your internal instance names.  That issue sounds like a permissions one. Somebody else is asking exactly the same question; not able to see the all providers )

[vijayaganesh_s]

Hi Simon,

What you have said is absolutely correct ... ie., not able to see all providers.

I will see that in the link which you provided.

Thanks for your reply.

Regards,

Vijayaganesh S

[quriouss]

One more thing to try, which just crossed my mind;

In order to see the providers, I think there must be at least one query built on the provider (and possibly that query needs to be released for OLE DB).

So try building a simple query, release for OLE DB, then save it, and then go back to Qlik and see if it displays the InfoProvider.

(Strangely the query you access in Qlik doesn't necessarily have to be released for OLE DB, I'm not sure if that's an oversight.)