Oracle Analytics Cloud Data Sync on-prem

Roger Perbo

Summary

Ideas on how to design a custom SQL for Oracle Analytics Cloud Data Sync on-prem

Content

Greetings!
I have just finished installing Oracle Analytics Cloud Data Sync on a machine on-prem which I would like to use for reading Suppliers and account combinations from our ERP Fusion Cloud.

It seem's as if I am only able to use VO's provided by Oracle to use the "SQL" within Oracle Analytics Cloud Data Sync.

Does anybody know how I can design and use a custom raw sql to use with Oracle Analytics Cloud Data Sync.

Please have a look at the query running today on our Ebusiness from 2001!

Version

Fusion ERP 20D

Code Snippet

        SELECT
                PV.SEGMENT1,
                PVS.VENDOR_SITE_CODE,
                PV.VENDOR_TYPE_LOOKUP_CODE,
                PV.VENDOR_NAME,
                PVS.ORG_ID,
                PVS.ADDRESS_LINE1,
                PVS.ADDRESS_LINE2,
                PVS.ADDRESS_LINE3,
                PVS.CITY,
                PVS.STATE,
                PVS.PROVINCE,
                PVS.ZIP,
                PVS.COUNTRY,
                APT.NAME,
                PVS.LAST_UPDATE_DATE,
                PVS.INVOICE_CURRENCY_CODE,
                PVS.INACTIVE_DATE,
                APB.BANK_NAME,
                APB.BANK_BRANCH_NAME,
                APB.BANK_BRANCH_TYPE,
                APBAA.BANK_ACCOUNT_NAME,
                APBAA.BANK_ACCOUNT_NUM,
                APBAA.BANK_ACCOUNT_TYPE,
                APBAA.GLOBAL_ATTRIBUTE_CATEGORY,
                APBAA.GLOBAL_ATTRIBUTE1
                FROM
                   PO.PO_VENDORS PV,
                   PO.PO_VENDOR_SITES_ALL PVS,
                   AP.AP_TERMS_TL APT,
                   AP.AP_BANK_BRANCHES APB,
                   AP.AP_BANK_ACCOUNTS_ALL APBAA,
                   AP.AP_BANK_ACCOUNT_USES_ALL APBAUA
                WHERE
                   PV.VENDOR_ID=PVS.VENDOR_ID
                AND
                   PVS.TERMS_ID = APT.TERM_ID
                AND
                   PVS.VENDOR_ID = APBAUA.VENDOR_ID (+)
                AND
                   PVS.VENDOR_SITE_ID = APBAUA.VENDOR_SITE_ID (+)
                AND
                  APBAUA.EXTERNAL_BANK_ACCOUNT_ID = APBAA.BANK_ACCOUNT_ID (+)
                AND
                  APBAA.BANK_BRANCH_ID = APB.BANK_BRANCH_ID (+)
                AND
                   APT.LANGUAGE = 'S'
                AND
                   ((PV.last_update_date >= p_update_date)
                   OR
                   (PVS.last_update_date >= p_update_date)
                   OR
                   (APT.last_update_date >= p_update_date)
                   OR
                   (APB.last_update_date >= p_update_date)
                   OR
                   (APBAA.last_update_date >= p_update_date));

SantoshKumarBhairi

hi Roger,

Can you try these steps?

DataSync -> Project (either new or existing) -> New -> 'Add new source object' -> OK -> 'Enter object's properties manually - Provide a logical name -> you will see a dialogue box and at the bottom 'Data from :query' -> You will see 'Properties' -> Query Override -> In the value 'enter your custom query'.

Let me know if you face any issues.

Thanks,

Santosh

Roger Perbo

Hi! I don't have the option Query, I have "Day Based Partition Read from SQL", "Day Based Partition Read from Subject Area.Table", "SQL", "Report" AND "Subject Area.Table".

Choosing "SQL" doesn't give me the option Properties nor Query Override.

I am running Oracle Analytics Cloud Data Sync (2.6.1) (OAC-2.6.20191009.1527).

Thanks for the reply!

 

 

SantoshKumarBhairi

 

I have this version and it works fine for me:

 

Oracle Analytics Cloud Data Sync (2.6)

 

Compatible with OAC Data Loader Version

Base version: V2

Major Version: 1

Minor Version: 0

Build: 12.0.0.0.0.20190410.1502

 

Even if you have a slight higher version, it should still work as I think this feature is coming from earlier version of BICS. Maybe if you can share screenshots in word document, it can help to nail down the issue.

Roger Perbo

Hi!

Attached is a series of screen shots from the steps in the project.

I hope you can view them!

Regards,

/Roger

SantoshKumarBhairi

 

thanks Roger. Can you click as shown in the attached image?

Btw, your seems to be slightly different.

 

Roger Perbo

Yes, it will bring up a popup where I today have my "select_physical" against the View Objects.

I am not able to query POZ_SUPPLIERS for example directly from there, it will not work.

I don't know if I am supposed to be able to do that?

The problem persists I will not be able to query the tables and when quering View Objects it easily gets to be 8 or maybe 10 queries for one simple task, for example Suppliers.

I do have a working workaround for the moment, I do 8 VO queries and stash the results in local tables and then a local View (good old fashioned).

At least with that approach I will be able to get incremental data in Data Sync for suppliers, that is not possible with REST API because of missing Last_update_date functionality.

Thanks!

SantoshKumarBhairi

In my version of oac datasync, I am able to enter custom-sql (without any number of objects) I would suggest you to raise an Oracle SR.