Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am trying to download from a BeX query a huge amount of data. I am trying reducing / slicing data, but timing is still higher than i expect.
I looked at log, and I believe bottleneck could be here:
014-09-17 17:11:03 | Trace | Checking syntax for: SELECT NON EMPTY SUBSET ( |
Crossjoin(
Crossjoin(
{Distinct(Descendants([0SALESORG].[All],[0SALESORG].[LEVEL00],AFTER))},
{[0CALDAY].[20140801]}),
{Distinct(Descendants([ZBILL_NUM].[All],[ZBILL_NUM].[LEVEL00],AFTER))}), 0, 20833) ON AXIS(0),
{[4WGPIQNXW847VHEUTZ6SFU1E0].[4WGPJ90J6VZ6DBVGRSS4GIYYW],
Is is possible to increase 20833, that should be somehow the size of data retrieved ? otherwise, the query is repeated to my understanding and time is lost. Or, do i have other things to look at ?
Massimo
Hi Massimo
I am not sure whether the parameter is changeable. However, it is important to say the OLAP connector is not good for large sets of data (but rather to use the advantages of the Business Logic implemented in BEx queries).
I can suggest to options
1. If the data is stored in a DSO - try to extract the data from the Active table using the SQL connector
2. If you have to use the OLAP connector to extract query results, try to use loops and extract a slice of the data using a query variable.
Thanks
Yaniv
I tried the option 2 (because I wish to keep the logic written in the OLAP query, and not to replicate and maintain it in 2 systems).
I have been able to download more data slicing rows and columns, but still it is a lot slow than what i was expecting.
Massimo
Hi Massimo
The connector Slicing feature is not so effective.
I would suggest to create a loop in QV script and execute the query on a limited selection for each iteration of the loop.
For example, create a loop that will occur 12 times (for each month) and extract the data for a specific month in each iteration using variables.
If you require the code just let know, I will be happy to assist
Thanks
Yaniv