Data preview issue in Analytic View

[prafful_agrawal]

Hi All,

When i am executing below mentioned SQL statement on the Analytic view ,  I am able to find required data in SQL view but it is not shown same data  via Datapreview on HANA nor in the explorer view.

SQL STATEMENT

SELECT "VBELN", "CALQUARTER",STATUS,

      sum("SALE_FIGURE"),

      FROM "_SYS_BIC"."sales/ANALYTICAL_VIEW_TEST"

WHERE "VBELN " IN ( '0400930807' )

GROUP BY "VBELN", "CALQUARTER",IC_STATUS_TXT

For data preview in HANA --> I have tried few options:-

1.Increased max row limit.

2.Filter criteria on VBELN field

Kindly Suggest

Regards

PA

[Former Member · Accepted solution]

Hi Prafful,

Can you try including all your attributes & measures into the select statement of yours and see if that works? My guess is the analytic view might have a problem with one of the fields which could be the reason for data preview not bringing back anything.

Otherwise, worth check if you have necessary authorisations.

Which revision are you on?

Thanks,

Anooj

[prafful_agrawal · Accepted solution]

Hi Anooj,

Thanks for your reply,

As suggested ,after including all the attributes in the sql then i am getting 0 records .

Also when i am using select * in below Sql then 0 records back

SELECT  *   FROM "_SYS_BIC"."sales/ANALYTICAL_VIEW_TEST"

WHERE "VBELN " IN ( '0400930807' )

i just notice that if there no calculated measure in my SQL statement then i am getting below error :-

SAP DBTech JDBC: [2048]: column store error: search table error: [2601] Error executing physical plan: executor: cannot create plan data item (producer or consumer not found in execution plan);in executor::Executor in cube: _SYS_BIC:sales/ANALYTICAL_VIEW_TEST/olap

Do you have any advise .

Regards

PA

[former_member184768 · Accepted solution]

Hi Prafful,

Not sure if it is typo, but you have a blank space at the end of VBELN ("VBELN "). Can you please check if it is a typo or your column is defined with a blank space at the end.

Regards,

Ravi

[former_member184768 · Accepted solution]

Can you also try the following and let is know if it works:

SELECT  distinct "VBELN "   FROM  "_SYS_BIC"."sales/ANALYTICAL_VIEW_TEST"

WHERE "VBELN " like '%0400930807%';

SELECT  distinct "VBELN"   FROM  "_SYS_BIC"."sales/ANALYTICAL_VIEW_TEST"

WHERE "VBELN" like '%0400930807%';

Regards,

Ravi

[Former Member · Accepted solution]

Suggest you try Ravis recommendation above.

Also, in your prev response you had mentioned an issue around a calc measure. I suggest you delete the calc measure, activate the view and see if the data preview works. That way you have at least narrowed down the issue. Then, recreate the calc measure and if the problem persists, copy paste your calc measure formula here.

Thanks,

Anooj

[prafful_agrawal · Accepted solution]

Hi Anooj,

Finally i find the root cause of data preview issue . The issue is because of one of the attribute .

When i removed then i am getting data back but when i add then i am getting 0 records.

Still i am not sure what is the reason behind the same . The field is of type char1

Anyway thanks

Regards

PA

[Former Member]

Hi Prafful,

Can you attach the Generated SQL of data preview of you analytic view.

I guess there should have been more attributes in group by clause, when you are doing data preview on your view, hence it would have been resulting you the matching records, where in the SQL statement you are executing you are specifying some of the attributes in group by and so you are able to see the expected data.

You can also try selecting the required attributes and measures in the 'Analysis' tab of data preview, which would result you the expected data.

Regards,

Bala

[Former Member]

Hi Prafful,

What revision are you on? I've seen some funny results on Revision 50 regarding data preview on analytic views. I can explain the details if you'd like.

Also, select count(*) or select * will never work on 'vanilla' analytic views (and in my opinion shouldn't work on calcview-wrapped analytic views, created in most circumstances when calculated attributes/measures are part of the analytic view). You'll need to some kind of aggregate SQL against the analytic view. I.e. SELECT SUM(M), D FROM AN_VIEW GROUP BY D