Error - BO on HANA

[Former Member]

Hi All,

I am connecting to HANA views to consume it on Webi through IDT and I am getting bbelow errors:

1. Integrity Check in IDT on HANA View:

          When I try to do an integrity check on the universe(Business layer) , I am getting error under - Business Object Validity (SQL/MDX) as It      was impossible to execute the business object "Attribute/Measure" , here in general I have mentioned as Attribute/measure .

2. Error when trying to access the same above HANA view in webi after publishing the universe by ignoring the above errors:

Database error: SAP DBTech JDBC: [7]: feature not supported: not allowed over OLAP VIEW : search without aggregation or grouping by expression other than view column. (IES 10901) (WIS 10901)

Please let me know if any1 faced similar issues .

Regards,

Nagaraj

[Former Member · Accepted solution]

Hi Nagaraj,

It isn't very clear to me from your question as to whether you have manually converted all real measures in your IDT into Measures instead of Attribtues, just as mentioned in the SAP Note '1609126 - IDT returnes JDBC errror on HANA'. Apologies if you have already done this.

Also, I remember having got the integrity check errors but didn't cause any harm when I choose to ignore them.

Thanks,

Anooj

[former_member184768 · Accepted solution]

Hi Anooj,

Welcome back, you were out of action for a long time. :-). It is nice to see your expert comments on the forum again.

Regards,

Ravi

[Former Member · Accepted solution]

Hi Ravi,

Sorry for the delayed response.

Below is the script from Webi:

SELECT
  "_SYS_BIC"."infy123267/AN_FASHION"."MARGIN",
  "_SYS_BIC"."infy123267/AN_FASHION"."AMOUNT_SOLD",
  "_SYS_BIC"."infy123267/AN_FASHION"."QUANTITY_SOLD",
  "_SYS_BIC"."infy123267/AN_FASHION"."MANAGER",
  "_SYS_BIC"."infy123267/AN_FASHION"."FAMILY_NAME"
FROM
  "_SYS_BIC"."infy123267/AN_FASHION"

Regards,

Nagaraj

[Former Member · Accepted solution]

Hi Anooj,

By default all the columns from the view will appear as Attributes and I have manualy converted the numeric coulmns to measures , but this is not causing the error.

As I explained above that I am getting error while doing a Integrity check on the universe before exporting to Reository and the error occurs only in Business Object Validity (SQL/MDX) rule.

Regards,

Nagaraj

[former_member184768 · Accepted solution]

Hi Nagaraj,

Ideally the generated statement should have SUM for Measures and Group by clause for the attributes.

Can you please try to modify the query manually to add the SUM and GROUP BY clause and see if can be executed.

Please also check the aggregation behavior for the measures defined. It will help if you can add a couple of screenshot of the measure definitions.

Regards,

Ravi

[Former Member · Accepted solution]

Hi Ravindra,

I tried to add SUM to the measures and I was able to resolve the error for measures but I am getting error for Attributes/Objects, below is the snapshot.

Regards,

Nagaraj

[former_member184768 · Accepted solution]

Hi Nagaraj,

Thanks for the sceenshot for the attribute, can you please also add the screenshot for the measure and error.

From the first look of it, the attribute definition looks fine to me.

Regards,

Ravi

[Former Member · Accepted solution]

Hi Ravi,

Below are the snapshots

Regards,

Nagaraj

[Former Member · Accepted solution]

Hi Nagaraj,

Few points:

a. When set up correctly, IDT should automatically add the SUM() and group by clauses to the Webi query dynamically based on the selections. You don't have to add them to the SELECT statement manually.

b. You said you have converted all your numeric attributes into measures. That is wrong - you should only convert your measures from the underlying analytic view in HANA. Looking at your first screenshot, you have converted Article ID, color code etc into measures. However aren't they defined as attributes in your underlying HANA analytic view?

c. After correcting this, if you are still getting integrity check errors,they are most likely warnings and not errors. At least that was the case for me. Also refer to SAP Note 1750026.

d. Lastly if you want to double-check to see if your connection has been set up correclty, refer to this video - http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/e0a7e582-c7ef-2f10-cebd-eaa0c70e1f67&override...

Thanks,

Anooj

[Former Member · Accepted solution]

Thanks Ravi for your kind words been busy at work lately. Hoping to get busy in the forum very soon.

Thanks Again,

Anooj

[former_member184768 · Accepted solution]

Hi Nagaraj,

Kindly check and verify the service pack for the BO client. Please check if the issue can be resolved with SP 04 for the BO client tools.

Regards,

Ravi

[Former Member · Accepted solution]

Thanks Ravi and Anooj.

Regards,

Nagaraj

[former_member184768]

Hi Nagaraj,

Regarding your second point, can you please check if the SQL generated accessing Analytic view has Group by clause for the attributes. If you are using measures with aggregation as sum and using some of the dimensions, then the group by clause should be added automatically.

Please send the generated SQL statement for the Query which you are using in WebI.

Regards,

Ravi

[Former Member]

Hi Ravindra,

Data Preview SQL for the Analytical View :

Generated Data Preview Query: SELECT TOP 12 sum("MARGIN") AS "MARGIN", sum("AMOUNT_SOLD") AS "AMOUNT_SOLD", sum("QUANTITY_SOLD") AS "QUANTITY_SOLD", sum("SHOP_FACTS_ID") AS "SHOP_FACTS_ID", "COLOR_CODE", "COLOR_LABEL", "ARTICLE_COLOR_LOOKUP_ARTICLE_ID", "FAMILY_NAME", "CATEGORY", "ARTICLE_ID", "MTH", "QTR", "YR", "WEEK_ID", "STATE", "CITY", "MANAGER", "SHOP_ID" FROM "_SYS_BIC"."infy123267/AN_FASHION" GROUP BY "COLOR_CODE", "COLOR_LABEL", "ARTICLE_COLOR_LOOKUP_ARTICLE_ID", "FAMILY_NAME", "CATEGORY", "ARTICLE_ID", "MTH", "QTR", "YR", "WEEK_ID", "STATE", "CITY", "MANAGER", "SHOP_ID"

Yes as mentioned by you I could see GroupBy clause for attributes beacuse of sum for some measures.

how to resolve these errors?

Regards,

Nagaraj

[former_member184768]

Hi Nagaraj,

This is the data preview SQL from the HANA studio. Is the WebI query generating same type of SQL? Can you please send the SQL generated by the Web I query.

Regards,

Ravi