Filter member

[Former Member]

Hello,

We are using Analysis for office on top of HANA Analytical view. The Analytical view is based on a huge table of about 3B records.When the user try to filter on a member:

The following SQL is generated on the HANA side:

SELECT DISTINCT "AAA" FROM "BBB"."CCC" ORDER BY "AAA"  ASC  LIMIT 10001

For huge dimensions this query doesn't make scene and will never finish..

Is it possible to prevent from the users to create this LOV when filtering a member?

Thanks,

Amir

[former_member151004 · Accepted solution]

Hi Amir Dror,

You must remeber that Analysis for Office is a front end tool designed to analyze data by the end user. 3B records is a huge amount for the Analysis. Please check the wiki page http://wiki.scn.sap.com/wiki/x/EYhRFw where you can see a limitation on the .NET Framework.

Please check also the front end memory consumption.

For all enhancements request for AO, please refer to the SAP note below:

>> 1879237 - Enhancement Request for Analysis, edition for MS

AO development team will move request to their backlog depending on the rating and the feasibility. However, currently they mainly focus on the "most voted" item in the Idea lists. The item with high votes number will be considered as high priority.

Kind regards,
Diego Ferrary

[Former Member]

Hi Amir,

I'm not sure, but have you tried the advance settings, if any change in the Access mode would help here?

Best regards,

Martin

[Former Member]

Hi Martin,

Thanks but because we are running directly on HANA the mater data option is grayed .

Hi Diego,

So it means that it is not recommended to use Analysis For Office with huge tables? We only want to filter specific value from this table. there must be a way to specify the "Where" clause without running select distinct on all the values.

Thanks,

Amir

[former_member151004]

Hi Amir,

You have the Analysis for Office 64bit to run big queries but 3B is quite huge.

Analysis for Office is not meant to be used as a mass extraction tool for data, but rather as a tool for viewing/navigating through data.

For huge queries such as the one you mentioned SAP offers the Open Hub.

Thanks,
Diego Ferrary

[Former Member]

Hi Amir,

I think Diego is right, when it comes to the result set size limit, check his How To:

Analysis for Office Result Limit and Memory Consumption - Business Intelligence (BusinessObjects) - ...

If you just want to circumvent the problem of the filter selection you could use VBA to enable the users filtering on direct Input.

Just define several excel cells and use the input as Text, Key or Input String with the predefined "SAPSetFilter" functionality - check AO help document, chapter "Working with Macros"

Example:

Dim lResult as long

lResult= Application.Run("SAPSetFilter",
"DS_1", "0SOLD_TO__0COUNTRY", "CA;US;DE", "INPUT_STRING")

Best regards,

Martin

[Former Member]

Hi,

The user doesn't like to have report with miliion of rows. He likes to filter a member in the report. The member have milion of distinct values but the user doesnt like to have  a list of values of its members . he only wants to filter by a specific value that he knows and dont need LOV for that.

The only way i could find is to use the "Filter" component from the top menu. This component allowes to filter the data without viewing LOV. but i think there should be a way to cancel the LOV also in the ANALYSIS component - In some cases it doesn't make sense to allow the user to run select distinct on all the report data.

Thanks,

Amir