This discussion is archived
5 Replies Latest reply: May 20, 2013 3:02 AM by Raghu Nagadasari RSS

How to Convert Date to Month in Database which is pointing to EBS, Not DWH.

Raghu Nagadasari Newbie
Currently Being Moderated
Hi Friends,

We are planning to create reports in OBIEE and the Source is EBS. That means, we don’t want to depends on Datawarehouse and Informatica and OBIApps. We just create some reports and they are pointing to EBS database.

For testing purpose, we have taken one HR Report and it containsthe below columns:

Month (Dim)
Revenue Per Employee Headcount (Fact)
Average Cost per Active Workforce Headcount (Fact)

In my little investigation, we found that Revenue column is coming from EBS: GL_BALANCES. And we configured W_Day_D base table in DAC as there is no Month column in EBS (Not sure).

*** Coming to my problem, how can I call Month column in my database pointed to EBS. How to convert Date to Month in my database.

I could be wrong but anyone please look into this issue.. How can I create columns for that exact OBI report in EBS database instead of DwH.

Thanks in Advance..

Raghu Nagadasari
  • 1. Re: How to Convert Date to Month in Database which is pointing to EBS, Not DWH.
    Veeresh Rayan Journeyer
    Currently Being Moderated
    Just confused here.

    Hang on,If I am getting it then you want to create report based on EBS columns directly then using BI apps rt?.

    Then there is no question of using W_DAY_D table then.

    Do follow this,

    As long as you have Date column you can use DB functions to convert them to Month(Simply use SUBSTR string to extract month and year).
    Create your own query instead of using BI APPS tables and trying to bring a hybrid product.

    Sorry I might be wrong in understanding your requirement,If I had got correctly then folllow above steps.

    Mark correct or helpful if it helps,

    Regards,
    Veeresh Rayan
  • 2. Re: How to Convert Date to Month in Database which is pointing to EBS, Not DWH.
    Ahsan Shah Expert
    Currently Being Moderated
    So you are bypassing the DW/ETL process that extracts from EBS to OBIA but you want to use the OOTB OBIA RPD? Not sure how this will work as its not a matter of "repointing"..there is logic in the ETL process that you will have to replicate which comes at a high cost performance wise. You are better off making your own OLTP based custom RPD and NOT the OBIA RPD...if you want to bypass the DW, you may as well bypass the RPD as these are co-dependent.
  • 3. Re: How to Convert Date to Month in Database which is pointing to EBS, Not DWH.
    Raghu Nagadasari Newbie
    Currently Being Moderated
    Hi Veeresh,

    You are right. Here, no need of using W_DAY_D table, but, we need all the stuff from the table and to explain my problem i have taken that table as an example. Now, we ran scripts for W_MONTH_D and W_DAY_D tables with EBS columns. So, we are getting data for the tables. In the same manner we have to create scripts for the below tables.


    W_GL_ACCOUNT_D
    W_GL_OTHER_F
    W_STATUS_D
    W_MCAL_DAY_D
    W_EMPLOYMENT_STAT_CAT_D
    W_WRKFC_BAL_A

    W_DAY_D (Completed)
    W_MONTH_D (Completed)

    But, we are not aware of EBS Tables and their columns much. So, we are back tracing from RpD --> DAC --> Info --> EBS for each and every column and its taking much time.

    So, if anyone of you facing the same issue, please share the EBS tables for the corresponding OBI tables, which are mentioned above.
  • 4. Re: How to Convert Date to Month in Database which is pointing to EBS, Not DWH.
    Raghu Nagadasari Newbie
    Currently Being Moderated
    Yes Ahsan. We are planning to bypass the DW/ETL process and make the logics avilable (which are in Info) in Database level only. And all the Rpd stuff of OBIA also not required, but planning to create similar to OBIA RPD. Finally what is my assumption is my reports also needs to give the results same like results from OBIA. :)

    Please find my prior conversion and give me the suggetions on the EBS tables for the required OBI tables.
  • 5. Re: How to Convert Date to Month in Database which is pointing to EBS, Not DWH.
    Veeresh Rayan Journeyer
    Currently Being Moderated
    You can use Data lineage guide to fasten up the process.

    Here is the note id for the same.Check in Oracle support site.

    Oracle Business Intelligence Applications ETL Data Lineage Guide Release 7.9.6.2 and 7.9.6.3 [ID 1274680.1] .

    mark correct or helpful if it helps,

    Regards,
    Veeresh Rayan

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points