Insight (OBIEE) Tips: SQL quick tip

Otilia Antipa-Oracle

For more Eloqua Insight Resources, please visit: https://community.oracle.com/topliners/discussion/4470627/eloqua-insight-resource-page

Recently, I was looking into a blind form submit challenge where anti-malware protections were causing a lot of fake form submits. We wanted to make sure that in form submission data reporting, we were only getting the valid data.

Based on the email recipients and the use case, we decided to only report on the last form submit from a given contact.

OBIEE SQL filter to achieve this:

"Form Data"."User Form Submit ID" = MAX( "Form Data"."User Form Submit ID" By "Contact"."Eloqua Contact ID")

Got any SQL tips for Insight users?

rnordin

Hi @Alexa Kalapaca-Oracle ,

I am creating a consolidated Form Submissions Report, and there will definitely be instance where [email protected] submits more than one form.

With this SQL filter, i might only get one row of submission from [email protected] even though he has submitted more than one form. Can you advise on this, please.

Thank you,

Alexa Kalapaca-Oracle

Hi, are you looking to only get the latest per form?

rnordin

@Alexa Kalapaca-Oracle yes.

If [email protected] completes 4 form submission (e.g. below)

My report should only be reporting 3 form submission - based on the latest form submit done per form

Alexa Kalapaca-Oracle

@rnordin In the Form Submission subject area, try

"Form"."Form Submission Date" = (MAX( "Form"."Form Submission Date" By "Form"."Form ID","Contact"."Eloqua Contact ID"))

rnordin

@Alexa Kalapaca-Oracle unfortunately no, i am still seeing duplicate entries made by the same contact to the same form. I.e. the example i shared above.

Alexa Kalapaca-Oracle

what columns are you including in the results? If you're using Form Submission Date/Time, that is probably too granular for the filter suggestion I gave. Adjust the filter to use the same form submission date column you're using in your criteria pane.

And just a suggestion on your filters above - filtering on the Date/Time attribute is very granular - i.e down to the minute and could slow down your report. Adjust it to use a higher level like an attribute from the Form Submission Calendar (i.e Day) or even the Form Submission Date (rolls up to the hour)

rnordin

@Alexa Kalapaca-Oracle , got it. My current report is based on Form Submission Date, i will try to roll it up to Form Submission Calendar (i.e Day)