Analytic view - Filter issue

[Former Member]

Hi All,

I have created an analytic view in that I joined the attribute of the data foundation and  the attribute view. Once I joined the object I can able to give the filter in attribute view only. If not joined I can able to give the filter in data foundation.

So, my question is:

1.       1.Whats the difference between applying filter in attribute view and in the data foundation?

2.       2.How the data will be fetched from those two case?

Please guide me if im wrong.

[Former Member]

Hi Prasath,

A few facts of filters

Filters are defined at design time on a table and the filter applies on the table before the query starts to execute. Their functionality is most relevant in regard to data restriction as when used with different join types.

Inner Join: If you define a filter on a field of the right table (probably your attribute view depending on the way you have joined) then you have to include at least one field of the right table in the output definition of an analytical view. Otherwise ths filter is not taken into account at all.

Referencial Join: A referencial join cannot be used if a filter is set on a field in the right table.

Now, an attempt to answer your questions based on what is know about joins. I suggest you also test out using filters on a simple data model.

Lets say we have 3 tables: Customer and associate state, State and associated description(text), and Sales (Transactional Data) which is keyed on customer ID and also a state association.

Attibute view

If we create a simple attribute view joining Customer and State tables (inner join) we would get an output of of all customers where the associated state is also defined in the state table. Now if we use a filter on the attribute view to restict on state to Michigan for example we would only get records for cutomers associated to Michigan provided Michigan is in both tables. If Michigan was not in both no records would be returned even if in one table. If we changed the join type to left outer where Customer table is the left table we would get all customers associated with Michigan regardless if Michigan was or was not in the state table. If michigan was not in the state table we would just have an unassigned description on the michigan customers.

Analytic View

Recall the results from above.Now if we our data foundation (sales table) to the inner joined attribute view, the output would be determined by the join type. If we joined both based on an inner join then the analytic view output will only retrieve customers which are in both tables. Should the data foundation have customers which are associated with Michigan not in the attribute view these customers would not be part of the output. EG facts lost. This is why left outer joins are usually done in the analytic view. If a left outer join is done then all customers are returned from the data foundation and only Michigan customer details are provided.

If the filter is on the data foundation the output of an inner join would be the same as if the filter where on the attribute view. In the case of a left outer join filtered on michigan, all existing michigan customers with sales would be returned regardless if they existed in the attribute output. You can see this outer join produces slightly different results depending where the filter is.

From this you can see that if a filter is on the attribute view then associated data details can be restricted, whereas if a filter is on the data foundation then the transational data itself is restricted.

If you know the model will use inner joins then it may be best to restrict at the lower levels to improve performance.

Hope this helps with some understanding.

kind regards,

Danielle

.

[Former Member]

Hi Danielle,

I created a analytic view and done a inner join with attribute and applied a filter on the attribute.

Here im getting a prompt message but as per your statement you said we need to include at least one field of the right table in the output definition of an analytical view. Otherwise this filter is not taken into account at all.

Could you please guide how is this possible.

TIA

[Former Member]

Hi Prasath,

After your query I decided to do a small test using a basic model. My model returns filtered data when either and attribute view has a filter or the data foundation right table has a filter itself. This is regardless of whether the field is selected for the analytic output. However, if the filter is on the attribute all fields are automatically included in the output.

Based on this I cant get the output as SAP say you should get. the facts described above are from SAP materials I have from training sessions. The entire description is below. It may be that this was true for earlier versions and now is no longer an issue.

Let me know if you have any more specific questions around the area and Ill try to do some tests. Sorry i cant get the same results as the notes suggest and as your question asks.

****************

Filter - gets executed first Is defined on design time on a table and the filter applies on the table before the query starts to execute. This is normally faster than using a Where Clause as the result set is reduced before proceeding with the query execution plan, e.g. constraints applied before a table join is executed. If you defined a filter on a field of the right table you have to include at least on field of the right table in the output definition of an analytical view. Otherwise the filter is not taken into account at all.

Where Clause - gets executed second is defined on runtime in the SQL query and the filters applies on the results set of a query

Inner Joins and Filters If you have defined a filter and want to reduce the number of records in the result set if the right table does not return a record (referential integrity) you need to at least include one field from the right table into the output of the model. Otherwise (for optimization purposes) the join path is not taken into account at all.

*******

Kind regards,

Danielle