HANA smart data integration and smart data quality questions post there

[swapan_saha]

SAP launched HANA smart data integration (SDI) and HANA smart data quality (SDQ) in HANA SPS09. The blog

Simplifying Sap HANA data integration-landscape states how these new tools simplifies the landscape.  Many customers have started using these tools to bring data into data for various use cases.

We from HANA EIM Product team like to use this widely used SCN to share knowledge in SDI and SDQ. If you have any questions about the capabilities, how to use, when to use or need information to troubleshoot your deployment, please feel free to post any questions here.

Please include SDI as prefix for HANA smart data integration and SDQ as prefix for HANA smart data integration questions. Please also tag your message accordingly (smart_data_integration, smart_data_quality, agile_data_preparation, SDI,SDQ).

If you not familiar with these tools, please start with this introduction video

Thank you,

Swapan Saha

[vivek_vk_83]

I have an error with SFTP server in Quality where I am unable to extract data consisting of a million records. I am getting error, internal error: Remote execution error No free buffer, Data is not read by Hana. We tried splitting the million records into smaller datasets and is working but one large file is not working. The same file we were able to extract data in a single shot in development. We had a check on the DP Agent Monitor and has ample space and memory for Quality system. We are clueless where the problem is. Can you suggest some inputs on how to go about this.

[former_member255525]

Hi,

We are working on the SAP HANA Smart Data Integration using HanaAdapter wrt that. We have created a Flowgraph.

where we have added the Datasource to be a Virtual table and we are using Projection and Union nodes for the Data transformation purposes as shown in the screenshot below

It works perfectly fine. When I run this flowgraph giving me the DataTarget table as expected.

But If I change my Data Source settings to Realtime as shown in the below screenshot

The build is successful, and When I try to execute the flowgraph again. I am getting the following error

1:56:53 PM (/sample_sdi_for_cockpit/poc2_db) Execution Failed : (dberror) [686]: start task error: "POC2_HDI_POC2_UPDATED_DB_1"."POC2.poc2_db::UserSettingsTarget_SP": line 5 col 0 (at pos 107): [140038] Error executing SQL command in task.;Error executing ALTER_REMOTE_SUBSCRIPTION_QUEUE with command: 'ALTER REMOTE SUBSCRIPTION "POC2.poc2_db::UserSettingsTarget_RS" QUEUE'. ,exception 71000256: QUEUE: POC2.poc2_db::UserSettingsTarget_RS: Failed to add subscription for remote subscription POC2.poc2_db::UserSettingsTarget_RS[id = 164649] in remote source SSC_HANA_SRC[id = 159914]. Error: exception 151050: CDC add subscription failed: Failed to add the first subscription. Error: com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [258] (at 17): insufficient privilege: only internal SYS user can modify objects in SYS and _SYS_AUDIT schema: line 1 col 18 (at pos 17)

I even tried adding the ALTER REMOTE SUBSCRIPTION privilege as part of my hdbgrants file

Here is my hdbgrants file

{
  "POC_ACCESS": {
    "object_owner" : {
      "global_object_privileges":[ 
        { 
          "name": "SSC_HANA_SRC",
          "type": "REMOTE SOURCE",
          "privileges" : [ "CREATE VIRTUAL TABLE", "DROP", "CREATE REMOTE SUBSCRIPTION","PROCESS REMOTE SUBSCRIPTION EXCEPTION","ALTER REMOTE SUBSCRIPTION" ]
        }         
      ]
    },
    "application_user" : {
      "global_object_privileges":[ 
        { 
          "name": "SSC_HANA_SRC",
          "type": "REMOTE SOURCE",          
          "privileges" : [ "CREATE VIRTUAL TABLE", "DROP", "CREATE REMOTE SUBSCRIPTION","PROCESS REMOTE SUBSCRIPTION EXCEPTION","ALTER REMOTE SUBSCRIPTION"]
        }         
      ]
    }
  }
}

When I build this file it throws error stating

Invalid Privilege ALTER REMOTE SUBSCRIPTION

I tried googling a lot and went through lot of blogs but I am unable to find anything

Kindly help me

[Former Member]

Hello All,

I am trying to install data provisioning agent on windows desktop machine.

but i am facing one very weird issues.If anyone of you have faced the same issue,Please share your possible fix for this. 

Though i have java 7 / java 8 installed on my system and environment variable is set correctly.

I always end up with below error message.

I also tried getting back the installed java version on my windows machine and i got the below result.

So, Java setup looks correct but still i am facing this issue, any input would be a great help. Thanks

Below is the SDI setup,mentioned by Ben Hofmans -

Step-by-step: Setup SDI for your HCP account (part 2) 

Pankaj

[Former Member]

Hi Swapan,

I am new to SDI/SDQ. We are looking to migrate our BODS systems into SDI/SDQ and I have been tasked with setting up the change and release aspects of the project

In our company, we develop our applications in the DEV environment and promote/transport them to higher environments : DEV -> regression -> preprod -> PROD. In BODS, we used atl files to achieve this. For SDI/SDQ we were told that the SAP recommended mechanism will be CTS+.

Where can I find documentation /demo /details for SDI/SDQ promotions using CTS+ ?

Thanks and regards,

Ben Garcia

[swapan_saha]

Hi Ben,

SDI/SDQ is not a direct replacement of BODS. There is a session at TechEd which recommends when to use which which tool. The presentation should be avaialble at TechEd site. If needed, please contact me off-line.

Thanks,

Swapan

[Former Member]

SDI ABAP Adpater only shows tables from Accounting and Human resources. I am using the HANA Cloud platform Trial connecting to an IDES system on MSSQL EHP7 740 SP4. . Any reason other areas are not showing up here?

[former_member182226]

SDI:

Inconsistency in Smart Data Integration:Aggregation Transformation [SPS09]

Hi,

I am getting inconsistency  error on SDI flowgraph, while doing aggregation on measure of type fixed decimal (13, 2).

Reason: I am applying aggregation type 'SUM' on DOC_CURR_AMT which has data type DECIMAL (13, 2),

but  in OUTPUT_11 anchor of aggregation node, field's data type got changed as DECIMAL( 18 ,2) and it is not editable.

Is there any workaround to remove inconsistency or edit data type in OUTPUT anchor ?

P.S. I can not change the data type in source table.

Que. 2: How to use IF THEN ELSE logic expression in Filter Transform?

Thanks and Regards,

Avinash

[Former Member]

SDI

Hi,

I am trying to connect to my ECC source using ABAP Adapter. I am able to retrieve ABAP tables, however, extractors are giving the following error:

Request your assistance on the same.

Thanks!

[venkat_madireddi]

Hello Mamta,

    Can you check the framework.trc in the <DPAgent Install location>/log folder and provide any additional information logged related to this error.

Thanks

Venkat

[Former Member]

Hi Venkat,

Thank you for your response.

The trace log displays - No BAdI implementation for subscriber type 'HANA_SDI'.

Attached herewith is the detailed error log.

Do I need to change any settings explicitly in the source system as well, prior to proceeding with SDI?

Error Log - SDI.txt.zip

[venkat_madireddi]

Hi Mamta ,

        Can you check the below SDI Product Availability Matrix(PAM)  for ABAPAdapter and confirm that mentioned SAP Notes are implemented on Source system .

https://support.sap.com/content/dam/library/ssp/infopages/pam-essentials/TIP/PAM_HANA_SDI_1_0.pdf

Please refer to ABAPAdapter on Page9.

Thanks

Venkat

[Former Member]

Thanks a lot Swapan..

Would like to know about HVR integration to HANA... is it possible?

Best Regards,

Vidya

[swapan_saha]

Vidya,

Please start a new thread for new question. We don't have out of the box integration here but we have SDK to build custom adapter which few customers are doing to connect to sources which are not yet supported.

Thanks,

Swapan

[Former Member]

Thanks Swapan..

[Former Member]

Hi Swapan,

We are carrying out a data replication using Flowgraph from HIVE to HANA.

I tried replicating a single table from default Hive schema, table name : "retail_client".

I created a target table(ex. TR_retail_client) with same structure as the Virtual retail_client table ( ex: VT_retail_client) and created a dataflow with the data sink option in the flowgraph.

but, when I try to activate the flow graph, i get the following error:

column not allowed: "id" in select clause"

Kindly help!

Thanks,

Vidy

[swapan_saha]

Hi Vidy,

The error often is related to permission issue. The user used to connect to the Hive may not have select permission for the tables?

If this is a customer project, please use BCP so that our support can help here promptly as you may need to share more information here.

Thanks,

Swapan

[Former Member]

Thanks Swapan.

Just wanted to convey that when we carried out a batch load the error disappeared but when we tried to do a real time replication, this error appears.

We couldn't insert a primary key in the Hive system for the corresponding table which was giving the error!

Hence, how do we over come this?

Also, would like to know if we have HVR adapter connection to Hana?

Thanks,

Vidy

[swapan_saha]

Hi Vidy,

Please check the capability of Hive Adapter in SDI

http://help.sap.com/saphelp_hana_options_eim/helpdata/en/1f/4651f2474240c982084fd8c6df438b/content.h...

It says Hive Adapter does not support real-time replication.

Thanks,
Swapan

[glen_spalding]

SDI

dear all

"can an SDI PIVOT transformation be used without materializing a table"

with the new SDI transformation features, the PIVOT function requires the results to be written to a table, or at least that what the error message says when i try to save the flowgraph as a procedure.

so, i was just wondering if there is any workaround, so that the table is not created, and i could run an SQL on the fly, using the flowgraph, without a table having to be created with the transformation results.

thanks

g

[Former Member]

Adding to above question by Murthy,

How does SDQ support application level data quality like address validation and duplicate checks, do we still need to use data services DQM. Does SDQ offer this feature if no, how can we achieve this functionality in HANA environment.

Can you please provide some information or point to any reference document which describes these features.

[Former Member]

SDQ

As per the understanding I have about the functionality of SDQ, it performs the data cleansing and validation of data such as duplicate check etc.

Can SDQ handle UI applications error message handling(During validation).

For Eg: Using a FIORI application we are trying to create a Vendor number which already exists in the Database. This check is done at SDQ level and creation will be blocked, will this validation failure reflect as a message automatically or it has to interpreted using the SQL error code numbers.

Regards,

Murthy

[Former Member]

SDI:

Good afternoon, gentlemen,

here is another question. We are running EIM/SDI (SP01 Patch 3 for HANA DP Agent 1.0, dpagent.version=1.1.3, repagent.version=1.1.7) and SAP HANA 1.0 Rev. 102.04.

We are running a three-tiered system environment. We would like to install the Data Provisioning Agents for development, test and production on one dedicated Linux server instead on the corresponding Oracle database servers. The Administration Guide tells me that this is basically possible but does not reveal the details.

Please deliver some information on how to install multiple agents on one Linux server (which is not the database server).

Thank you.

Regards

Johannes Bruns

[swapan_saha]

Hi Johannes,

For the optimum performance, we recommend to have DP Agent at the data source itself. If this is not feasible, DP Agent can be installed in the same network of the data source. If you want to have test and dev together, you can install multiple DP agents on the same Linux server. What you need to do to choose different file system location for each DP Agent binaries and choose different ports to avoid port conflict.  Each DP Agent runs as a Java process. So, you need to make sure Linux server hosting multiple DP Agents have sufficient computing power (RAM and CPU core) which can be verified running your work load.

Thanks,

Swapan

[Former Member]

SDI:

Good afternoon, we are running EIM/SDI (SP01 Patch 3 for HANA DP Agent 1.0, dpagent.version=1.1.3, repagent.version=1.1.7) and SAP HANA 1.0 Rev. 102.04.

Since we have been testing for quite a while, many log entries have piled up in the internal system tables. When I open the EIM Dashboard, the Data Provisioning Task Monitor and the Data Provisioning Subscription Monitor are flooded with entries. Sure, setting filters helps focusing on a specific item. However, I wish you could offer a tool, scripts or simply deletion rules that I can apply to clean up the monitoring tables permanently.

Can you offer some advice?

Will you supply monitoring clean-up tools in future versions of EIM/SDI?

Thank you.

Best regards

Johannes Bruns

[josko_poljak]

Hi,

when replicating SAP ECC tables with SDI replication task (initial load) I am getting error related to tables TCURX and T008T which states it cannot get metadata from 'SYSTEM.DD16S'. Other tables are being regularly replicated. Adding DD16S as one of the tables to be replicated did not help.

Here is log extract:

...

[20694]{216395}[34/3239381] 2016-02-18 23:54:38.728613 e Executor         PlanExecutor.cpp(00862) : internal error: Error opening the cursor for the remote database Cannot get metadata from 'SYSTEM.DD16S'. Detailed error: invalid table name:  Could not find table/view DD16S in schema SYSTEM:

...

Any suggestions?

Kind regards, Josko.

[former_member312159]

Hi Josko Can you check if you have select privileges on that table for the user. You can do so by grant select on SAPSR3.DD16S  to LR_USER; Regards Hitesh

[swapan_saha]

Hello All,

Please create a new message for all your HANA smart data integration and HANA smart data quality questions here. Use the same convention having SDI and/or SDQ in your subject or a prefix. Also use tags properly so that they can be searched accordingly.

Thank you,

Swapan

[josko_poljak]

HI Hitesh,

thanks for your reply. We have added all authorizations to our ECC data provisioning db user. However, issue remains. Our ECC uses MS SQL db and only existing schema containing DD16S is native SAP schema RAZ (which is ECC SID), There is no SYSTEM schema.

It seems that HANA somehow gets information (or concludes) that table DD16S resides in SYSTEM schema.

Kind regards, Josko.

[marco_antoniazzi]

hello,  i'm new in SAP HANA SDI. Is it possible to connect to Ibm Informix as a source to collect data in real time or near real time mode?

Thanks Marco

[swapan_saha]

Hi Marco,

If you look into SDI PAM's pages 8-11, it provides you the latest supported sources and targets in SDI. Informix is not yet supported. However, if any particular source is not supported out of the box, customers and partners can use SDK to build any custom adapter.

https://support.sap.com/content/dam/library/ssp/infopages/pam-essentials/TIP/PAM_HANA_SDI_1_0.pdf

Thanks,
Swapan

[Sven]

SDI Hello, when calling the Procedure from SQL Console from HANA Studio in SPS11 I get the following error:

Could not execute 'CALL "SVEN"."SVEN::RealTime_ECC_SVEN_SP"' in 265 ms 184 µs .

SAP DBTech JDBC: [256]: sql processing error: "SVEN"."SVEN::RealTime_ECC_SVEN_SP": line 6 col 1 (at pos 181): [256] (range 3) sql processing error exception: sql processing error: QUEUE: SVEN::RealTime_ECC_SVEN_RS: Failed to add subscription for remote subscription SVEN::RealTime_ECC_SVEN_RS.Error: exception 151050: CDC add subscription failed: RepAgent is not started.

I found a few SDN discussions but not the answer. Please advise. Many thanks!

Also to add, the vidio SPS09 is not mentioning the "Writer Type" insert for COLUMN without which Error is generated activating Flowgraph Model.

column store error: task framework:  [140004] scenario -> DATA_2 - No primary keys were found in target table "SVEN"."REALTIME_KNA1". If no primary keys exist in the target table the writer type must be set to 'insert'.

[former_member312159]

Hi Sven, Without looking at the logs its little difficult to pin point the cause. Can you please do the following

1. Check of the source data base, HANA version, DPAgent version, JDBC client etc are supported as per SDI PAM https://support.sap.com/content/dam/library/ssp/infopages/pam-essentials/TIP/PAM_HANA_SDI_1_0.pdf

2. Follow instructions in EIM Admin Guide Section 6.12 (assuming you are using SAP ECC) or similar instructions for other Adapters

3. Check if the time zone file configuration is done if required. Details in Admin Guide.

4. Check ports used are unique. Make sure to keep plus and minus one port free for every port used in creating remote source

5. If the above are OK, then try to reset the remote subscription you created. Alter remote subscription "" reset

6. Go to DPAgent install directory /usr/sap/dataprovagent/LogReader/ and see if the folder with "instance name" is deleted. If not then delete it.

7. Log in as configured replication user in the source data base and execute the applicable clean up script avaialable in /usr/sap/dataprovagent/LogReader/scripts

8. Do a QUEUE and DISTRIBUTE of remote subscription from HANA studio

Alter remote subscription "<remote subscription name>" QUEUE

Alter remote subscription "<remote subscriptioin name>" DISTRIBUTE

If this does not resolve your problem, please open up an incident with SAP (internal incident if SAP internal, BCP for external customer incident). Once your problem is resolved, we can share the solution in this forum to benefit of others.

Thanks Hitesh

[Sven]

Please explain how and where, where to find: "<remote subscription name>":

5. ... then try to reset the remote subscription you created. Alter remote subscription "" reset

Concerning:

6. Go to DPAgent install directory /usr/sap/dataprovagent/LogReader/ and see if the folder with "instance name" is deleted. If not then delete it.

It looks like this:

[former_member312159]

Hi You can find the remote subscriptions from the follownig query: SELECT * FROM M_REMOTE_SUBSCRIPTIONS; Your LogReader directory looks clean. Do step7 and Step8 and see if it works Thanks Hitesh

[Sven]

Hi, thanks  for that, unfortunately again "RepAgent is not started", so Executing:

Alter remote subscription "SVEN::RealTime_ECC_SVEN_RS" QUEUE;

Alter remote subscription "SVEN::RealTime_ECC_SVEN_RS" DISTRIBUTE;

Gives the following Errors:

Could not execute 'Alter remote subscription "SVEN::RealTime_ECC_SVEN_RS" QUEUE' in 647 ms 696 µs .

SAP DBTech JDBC: [256]: sql processing error: QUEUE: SVEN::RealTime_ECC_SVEN_RS: Failed to add subscription for remote subscription SVEN::RealTime_ECC_SVEN_RS.Error: exception 151050: CDC add subscription failed: RepAgent is not started.

Could not execute 'Alter remote subscription "SVEN::RealTime_ECC_SVEN_RS" DISTRIBUTE' in 153 ms 604 µs .

SAP DBTech JDBC: [256]: sql processing error: DISTRIBUTE: SVEN::RealTime_ECC_SVEN_RS: Cannot execute distribute on remote subscription SVEN::RealTime_ECC_SVEN_RS .It is not in MAT_START_BEG_MARKER/MAT_START_END_MARKER state.

[former_member312159]

Hi Please open an incident with SAP and attach the framework.trc there. I will take a look. If you have already logged an incident, please let me know the number Regards Hitesh

[Sven]

epilogue, for the ones interested in the resolution of this subject and you must be if you are here

with the great help from SAP support, and by checking logs:

1.Framework log: /usr/sap/dataprovagent/log/framework.trc

2.Instance Log: /usr/sap/dataprovagent/LogReader/<instancename>/log/<instance name>.log

support found that Port number 49159 that was used in configuring the MSSQLLogReader Adapter was already used by some other application, another issue was that instance name was specified as an SQL SERVER instance name and should be a rep agent instance name and could be anything, instance administration port is a rep agent admin port and not an Sql Server instance port (not obvious from the guide), now we have some new issues, but that is another discussion

Thank you Hitesh!