Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

How to load Flat Files from MS Office 365 to BW/4HANA using SDI / DP-Agent

wounky
Participant
‎09-20-2022 8:41 AM


1. Introduction


This simple tutorial should give you an idea of how to extract the flat files from MS Sharepoint 365 to BW/4HANA. Many thanks to Andrei Paul Andreianu for co-authoring this blog!

2. Use-case



  • Load the flat files from MS Sharepoint 365 to BW/4HANA.

  • Store the Flat File changelog and snapshots in BW/4HANA.

  • Enhance existing BW models with Flat File data managed in MS Sharepoint 365.


3. Prerequisites



4. Limitations



  • MS Sharepoint 365 source connection does not support wildcard characters in the file name (file_name_%). Each file has to have a different name.

  • MS Sharepoint 365 source connection does not support real-time data acquisition (replication tasks).

  • The developer has to create a file configuration individually for each system in the landscape.


5. Implementation


5.1 MS Sharepoint 365


5.1.1 Application


Create a new SharePoint Site for all your uploads to BW/4HANA. Follow the two links below to register a new app for the created Site. It will be consumed by the DP-Agent. The App will use Microsoft Graph API in order to expose the data.

Save the client's values for secret, application ID, and tenant ID.

5.1.2 Portal Library


Create a new library in MS Sharepoint 365. Set your Business Representative / Product Owner as the Library owner and the person who will maintain the authorizations to the folders within the library. You can set custom library authorizations for each user assigned. You will be granted full access as the Site owner.

5.2 HANA


5.2.1 Set the File Adapter Token


Follow the note by downloading the instruction 2558538 - How to set SDI FileAdapter Access Token from Command Lineto set the file adapter access to... to set the token. The password to the token will be used in the Remote Source connection configuration. Note it down.

Additional documentation: 2554427 - How to set a new access token for SDI FileAdapter via command line tool.

5.2.2 Create DP-Agent directories


You will need the token from the previous point. Follow the note 2559277 - How to set SDI FileAdapter Root Directory and File Format Directory from Command Line - SA... to create the DP-Agent directories which will be used to store the configuration files and set the root directory. The directories will also be used as parameters for the new remote source. Save the directories.

5.2.3 Create Remote Source


Create a new Remote Source System. Now, this part is a bit tricky. There is the following documentation available from SAP; https://help.sap.com/docs/...File Adapter Remote Source
Configuration, Connect to a SharePoint Remote Source.


Below are two snapshots with the essential parameters.




You need to execute the configuration from the HANA SQL console, please find a sample below. Replace what needs to be replaced with your config.

Parameter values for rootdir and fileformatdir are the ones set in paragraph 5.2.2. In the code below, shown are only examples. If fileformatdir is not provided, a root directory will be used.
The password for a token is the one that was set in paragraph 5.2.1.
The MS365 client secret is the one that was set up in paragraph 5.1.1.
The sp365dir is the directory where the files are downloaded to the DP-Agent server. It should be created before. The full path is rootdir+sp365dir. The sp365dir needs to be created before.
Create REMOTE SOURCE "<name_of_your_source_sys>" ADAPTER "FileAdapter" AT LOCATION AGENT "<dp_agent_location_based_on_landscape>"

CONFIGURATION

'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<ConnectionProperties>

<PropertyEntry name="rootdir">/usr/sap/dataprovagent</PropertyEntry>

<PropertyEntry name="fileformatdir">/usr/sap/dataprovagent/<your_directory_with_file_formats></PropertyEntry>

<PropertyEntry name="source_options">SharePoint365</PropertyEntry>

<PropertyEntry name="target_options">local</PropertyEntry>

<PropertyGroup name="SharePoint365">

<PropertyEntry name="authenticationMode">ClientCredential</PropertyEntry>

<PropertyEntry name="siteUrl"><link_to_your_sharepoint_site></PropertyEntry>

<PropertyEntry name="applicationId"><MS_365_application_id></PropertyEntry>

<PropertyEntry name="tenantId"><MS_365_tenant_id></PropertyEntry>

<PropertyEntry name="sp365dir">download/sharepoint</PropertyEntry>

</PropertyGroup>

</ConnectionProperties>

' WITH CREDENTIAL TYPE 'PASSWORD' USING

'<CredentialEntry name="AccessTokenEntry">

<password><File_Adapter_Token_Pass></password>

</CredentialEntry>

<CredentialEntry name="sharePoint365ClientCredential">

<password><MS_365_Client_secret></password>

</CredentialEntry>';

More about the table definition from my favorite author: https://blogs.sap.com/2016/01/06/hana-smart-data-integration-the-file-adapter/

5.2.4 Authorizations


Grant the appropriate authorizations. I would recommend creating a new Schema and adding appropriate authorizations to it so that the files are managed as a new source system in BW.

New Remote Source System authorizations:

  • Add the SELECT "<your_flatfile_schema>" authorizations to the SAPHANADB user

  • Add the CREATE ANY authorizations to _SYS_REPO on that Schema

  • Enhance the developer role with "<schema>" authorizations of CREATE ANY, EXECUTE, INDEX, INSERT, SELECT, UPDATE the remote source <remote_source>


GRANT CREATE VIRTUAL TABLE ON REMOTE SOURCE <your_connection_name> TO SAPHANADB;

GRANT REMOTE TABLE ADMIN ON REMOTE SOURCE <your_connection_name> TO SAPHANADB;

GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA <your_schema_name> TO _SYS_REPO WITH GRANT OPTION;

Grant CREATE VIRTUAL TABLE on remote source <your_connection_name> TO _SYS_REPO WITH GRANT OPTION;

Grant CREATE VIRTUAL TABLE on remote source <your_connection_name> to SYSTEM with grant option;

Grant DROP on remote source <your_connection_name> to SYSTEM with grant option;

5.2.5 Table definition


Create a template definition of the flat file table you would like to load. This will be stored as a configuration file in the directory which was created in paragraph 5.2.2.

FORCE_DIRECTORY_PATTERN parameter needs to have exactly the following values concatenated together:

  1. <rootdir> set in paragraph 5.2.2

  2. <sp365dir> set in paragraph 5.2.3 - this is where the temporary files will be downloaded

  3. <folder_on_sharepoint_with_files> this folder would be /Documents by default.
    If you created a site library, then it will correspond to its folder directory.


Set the correct Locale.
A locale for en_AU is used to support the dot-decimal separator.
Use de_DE for comma-decimal separator.
The file format has to be CSV (comma separated).

The official SAP documentation can be found here.
-- drop table "<schema>".<virtual_table> cascade

create virtual table "<schema>".<virtual_table> at <remote_connection>."<NULL>"."

<NULL>"."<file_name>"

REMOTE PROPERTY 'dataprovisioning_parameters'=

'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<Parameters>

<Parameter name="COMMENT">First line must be a comment</Parameter>

<Parameter name="FORMAT">CSV</Parameter>

<Parameter name="FORCE_DIRECTORY_PATTERN">/usr/sap/dataprovagent/download/sharepoint/<library_directory><

/Parameter>

<Parameter name="FORCE_FILENAME_PATTERN"><your_file_name>.csv</Parameter>

<Parameter name="CODEPAGE">UTF-8</Parameter>

<Parameter name="LOCALE">en_AU</Parameter>

<Parameter name="COLUMN_DELIMITER">,</Parameter>

<Parameter name="ROW_DELIMITER">\r\n</Parameter>

<Parameter name="ESCAPE_CHAR"></Parameter>

<Parameter name="TEXT_QUOTES">"</Parameter>

<Parameter name="TEXT_QUOTES_ESCAPE_CHAR"></Parameter>

<Parameter name="SKIP_HEADER_LINES">1</Parameter>

<Parameter name="QUOTED_TEXT_CONTAIN_ROW_DELIMITER">false</Parameter>

<Parameter name="DATEFORMAT">yyyy.MM.dd HH:mm:ss</Parameter>

<Parameter name="COLUMN">SAMPLE_COLUMN1;Varchar(30)</Parameter>

<Parameter name="COLUMN">SAMPLE_FIGURE1;decimal(17,2)</Parameter>

</Parameters>';

5.3 BW/4HANA


Add the appropriate schema authorizations to the ~BWREMOTE user. Create a DataSource on top of the virtual table, and load the data to the staging ADSO with an inbound table only. Add a load timestamp for the first transformation from the Data Source. It is PSA-like, so you can extract the data from it by using a full load and adding the latest timestamp into the DTP filter routine. You can also delete old requests. Then process it to the staging ADSO with a snapshot function. Optionally, you can add one more ADSO to store e.g. monthly snapshots on top. Don’t forget to add the housekeeping tasks.

6. Notes


Create an MS Site and App per BW/4HANA system in the landscape. It will enable you to run independent tests and configurations.

Create a new HANA role that will have access to specific SCHEMAs built for this solution.

You can move the files on DP-Agent server by using virtual procedures as explained in https://blogs.sap.com/2016/01/06/hana-smart-data-integration-the-file-adapter/#comment-393023.

You cannot manipulate files on SP365.
1 Comment
Labels in this area
Popular Blog Posts