1 Reply Latest reply on Feb 3, 2020 3:15 PM by Erik123456

    How to move data from Oracle Fusion Cloud 19D(OLTP) into Autonomous Data Warehouse(OLAP)?


      Hello Friends

      Can someone please give some advice and guidance. I have a client who uses Oracle Fusion Cloud and would like to move the data to Autonomous Data Warehouse for reporting and analysis.

      Is there any documentation that provides information on designing the Autonomous Data Warehouse based on the requirements and then the task of scheduling the data transfer on a daily basis. Thanks


        • 1. Re: How to move data from Oracle Fusion Cloud 19D(OLTP) into Autonomous Data Warehouse(OLAP)?

          Hi. There are two parts to your question.


          (a) Desiging the Schemas in ADW &, (b) Scheduling & Integrating Reports / Data.



          Designing the schema in ADW can be done via the SQL Developer modeller tool available either in SQL Developer or the online version which can be found in the ADW DB Connection page.



          Integrating depends on the types of data you want to bring across. You cannot bring the tables - you could query each table in Data Model, create a report for each table & bring these across but this is incredibly inefficient unless you plan to use Golden Gate Microservices, but even then it is still not advisable.


          If you want to bring data across, build them in Analysis (Subject Areas) OR via Data Model (SQL-based) in OBIEE.


          If your reporting needs can be met just by Analysis then you can follow the info in this link: https://www.ateam-oracle.com/reference-architecture-fusion-saas-data-replication-into-adw-%3A-using-odi-marketplace-and-…


          If you however you do not have the in-house knowledge to use ODI, you can alternatively use OAC (if you have access to it as part of your cloud account). Create a connection in OAC to Fusion & to your ADW instance, create a Data Set pointing to an Analysis report or create via PL/SQL, move the data using Data Flow from Fusion & store it in ADW. Drawback with this is that it may error out if the data is too large but I expect updates to OAC should prevent this issue in the long term.


          If you reporting needs cannot be met via Analysis but only using Data Models, then ODI is highly recommended. You can use OAC Data Sync however UCM will create a new report every time you schedule the report to UCM. This is an issue because scheduling the report will mean it will only pick up the old report not the latest report. You can schedule to a custom UCM folder but the schedule will error-out because it will detect a report with the same name & not overwrite the pre-existing report.


          If you do decide to use ODI, I recommend either going with ODI via the Oracle cloud marketplace or standing one up in a Linux Virtualbox. The Oracle ERP Cloud technology (which is what you will use to connect ODI to Fusion) does not work in ODI on a WIndows machine. Documentation does not tell you this but you will get errors with the LKM 'Oracle ERP Cloud to SQL' as it does not working well with WIndows directories. Follow this link to setup ODI Marketplace (only steps 1 to 3): https://www.ateam-oracle.com/reference-architecture-fusion-saas-data-replication-into-adw-%3A-using-odi-marketplace-and-…


          To setup a connection to ADW in ODI, follow steps in this link: https://www.ateam-oracle.com/connecting-oracle-data-integrator-studio-to-the-autonomous-data-warehouse-cloud


          To setup a connection to Fusion in ODI, follow steps in this link however please be aware of the below clarifications: https://docs.oracle.com/en/middleware/fusion-middleware/data-integrator/…

          • Resource Name is just the name of the file that gets created once extracted from Fusion. Although the documentation says it refers to the report name in UCM, this is simply not the case; i.e. this is NOT the name of the report in OBIEE.
          • BIPReportLocation is NOT the content server location but the BI Report location e.g. /Custom/Finance/TestReport.xdo
          • You cannot reverse engineer the report in OBIEE - this does not work. So you will need to download the report in OBIEE yourself, with the same name as the Resource Name, paste into the Schema folder that you specified in the Physical Schema, & this will reverse engineer the report OR manually create the fields yourself by clicking the 'plus' icon in the Attributes field.


          I hope this is a good start place for you; good luck.