FDI: Timestamp column values are not getting extracted during BI Publisher Data Augmentation

Sathyaprakash M

Hi,

I am trying to utilize the BI Publisher data in FDI. I have created the simple BIP report and trying to load that data using data augmentation. But when I execute the step, the data got loaded successfully. But for the timestamp columns are loaded with null values. Could you please help me on this?

JohnW-Oracle

Hi @Sathyaprakash M,

We will probably need a little more information to answer this.

What is the datatype in the source?

Try entering a Date Format and a different Timestamp format.

Sumanth V -Oracle

@Sathyaprakash M - I could see you have already set CSV Timestamp Format from the screen shot. But, unsure when it was set, before running Refresh metadata or after. Kindly follow below steps in same order and let us know the feedback. Thank you.

1. Navigate Manage Connections > BI Publisher Connection > Edit > Update the CSV Timestamp Format to yyyy-MM-dd'T'HH:mm:ss.SSSZ

Please refer https://docs.oracle.com/en-us/iaas/analytics-for-applications/doc/managing-data-connections.html#FAWAG-GUID-5D394FD0-F9D6-48D0-B9F0-0F8861530CEA

2. Refresh Metadata

3. Reset cache as mentioned in the document https://docs.oracle.com/en-us/iaas/analytics-for-applications/doc/reset-cache.html

4. Refresh data and verify.

Sathyaprakash M

Hi @JohnW-Oracle,

I have attached the screenshots above, from BIP report, I am trying to extract the DATE_FROM column and it is in timestamp format. In the BIP Connection, I had given the timestamp format as yyyy-MM-dd'T'HH:mm:ss.SSSZ and after the data augmentation, the timestamp column values are showing null value and rest of the columns are loaded with actual data.

Screenshot details:

1. BIP Connection details(which includes the timestamp format)
2. Actual BIP report(from Fusion)
3. ADW Table(after dataload)

Sathyaprakash M

@Sumanth V -Oracle - Initially I had set the timestamp format in the BIP Connection and after that I did the metadata refresh and data extraction

JohnW-Oracle

Hi @Sathyaprakash M,

For '2. Actual BIP report(from Fusion)', there is no Timestamp in BIP so you should look back to the datatype in the Fusion source.

Regards,

John

Sathyaprakash M

Hi @JohnW-Oracle,

I have checked the BIP datatype in the data augmentation page and it is in the timestamp format. Could you please help how to load these data type into FDI?

Santosh_V

@Sathyaprakash M Were you able to resolve this issue?

I am facing same issue.

phaniphani

Hi @Sathyaprakash ,

Try setting the CSV format as yyyy-MM-dd'T'hh:mm:ss in manage connections and refresh the metadata, try re-running the augmentation again

Santosh_V

Hi @phaniphani,

The action plan that you provided has worked for me. Thank you so much !

phaniphani

@Sathyaprakash M, you can also test in your instance,