Skip to Content
avatar image
Former Member

Data Services with Amazon Redshift.

Have seen a lot of posts on this not to many good answers.

Since I am starting a project on this I thought i would detail the project as it happens as it may help others.

We are migrating our Data Warehose to Amazon Redshift. We are using Data Services to load the data and Webbi to run reports from it.

I will start by what I have already done.

First trying to load data into Redshift directly is very slow and I don't recommend it, nor is it the suggested way to do it. You want to incorporate Amason S3 as your starting point.

The latest version of DS 4.2 has a native driver for S3. Set up a bucket then start your load there. We use CDC so we are loading our Changed data to S3. The different between this and the odbc into Redshift was as follow. Maybe a 100 rows a second if your lucky into the odbc. 300,000 to 400,000 per second into S3. Once the data is there you can set it up to copy over to Redshift from within Amazon. If all you want to do is get the data into Redshift then you are done here and it works great. The S3 is not very expensive either.

If you are using Redshift as your Data Warehouse then continue to read.

Once the data is in S3 then copy it to Redshift. We have done this it works well.

I am in the process of getting the SAP ODBC that comes with the BI tools as the native redshift ODBC is not 100% completely compliant(meaning not everything in DS works through it)

Hopefully with the SAP ODBC this will be better. My goal is to be able to push down all the sql so I dont have to host DS on Amazon. I will add to this after I have tested this part.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Aug 07, 2017 at 06:33 PM

    Hello Mark,

    Thank you for starting this thread. Can you a provide details on how to set up connectivity between DS and S3. You mentioned we have a native driver for S3 in latest DS? Can you confirm what version of DS ?

    I was only able to find out Redshift odbc drivers and establish connectivity with DS

    Thanks,
    Sarath

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Sorry I did not come back to this. As S3 is not really a database but a file system you will see the connection for it under formats and then create a new file location then select amazon s3 cloud storage under file locations. I believe this has been available at least since 4.2 sp6 I think. I know its been out for over a year.

  • avatar image
    Former Member
    Oct 11, 2017 at 06:14 PM

    Hi Mark

    I am working on a POC to pull data from S/4 Hana to Red Shift. Was there any reason you did not consider Matillion for extraction. Was it because you had SAP BODS already up an running and you wanted to avoid redeveloping the extraction on Matillion.

    Can you share the learnings from your project. If you have a part 2 of this integration then, please share it with me.

    Poonam

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Yes one of the great benefits of using Data Services is all the connectors that come out for free. So when you decide you want to change and put your data warehouse on another technology you don't have to rebuild it all over again. That would be very painful. As we already had out Data Warehouse on SQL Server in DS moving it to redshift through S3 was very easy and not a big project.

  • avatar image
    Former Member
    Mar 06 at 11:59 PM

    Sorry I did not come back to this. As S3 is not really a database but a file system you will see the connection for it under formats and then create a new file location then select amazon s3 cloud storage under file locations. I believe this has been available at least since 4.2 sp6 I think. I know its been out for over a year.

    Add comment
    10|10000 characters needed characters exceeded