The fasted solution atm is to slice your data extract down, - try with days.
Try read part with
Limitations and suggested workarounds
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.
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
If you have any alternate solution means please let me know that would be more helpful.
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.
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 ?
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 )
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.)
Yes, I think that's correct- you need at least one query built and released for the IinforProvider to be visible to third-parties;