7 Replies Latest reply: Apr 21, 2014 3:17 AM by Francisco Amores RSS

    How to Connect source oracle erp with FDQM without using ODI

    1050864

      How to Connect source oracle erp with FDQM without using Oracle Data Integrator

       

      Since i am using the EPM 11.1.2.3 version with FDQM ,

        • 1. Re: How to Connect source oracle erp with FDQM without using ODI
          Francisco Amores

          Hi

          in Classic FDM you can extract data from your ERP by using an integration script (import script) which reads data from either table(s)/view(s) and imports into FDM.


          If you are using 11.1.2.3, I would suggest moving to FDMEE as this is the last release for Classic FDM.

           

          Regards

          • 2. Re: How to Connect source oracle erp with FDQM without using ODI
            1050864

            I Searched Integration script but can't get right idea ..Can u send me the sample script for connecting oracle database as source in FDM.....

            • 3. Re: How to Connect source oracle erp with FDQM without using ODI
              Francisco Amores

              You have one integration script in the FDM admin guide.

              Regards

              • 4. Re: How to Connect source oracle erp with FDQM without using ODI
                1050864

                Hi,

                I am new to integration scripts , guide me to use these details in this script....

                 

                Details:

                db Server name , Instance name , port no , schema username and password in the below script

                 

                Script :

                Integration Script Example

                 

                The following integration script uses an ADO connection to log on to a RDBMS database and

                appends the ledger data to the trial balance table of the FDM location.

                Function SQLIntegration(strLoc, lngCatKey, dblPerKey, strWorkTableName)

                ‘------------------------------------------------------------------

                ‘FDM Integration Import Script:

                ‘Created By: FDM_Admin

                ‘Date Created: 04/19/2004 2:18:39 PM

                ‘Purpose: Pull data directly from SQL DB

                ‘------------------------------------------------------------------

                Dim objSS ‘ADODB.Connection

                Dim strSQL ‘SQL string

                Dim rs ‘Recordset

                 

                Dim rsAppend ‘tTB table append rs object

                ‘Initialize objects

                Set cnSS = CreateObject(“ADODB.Connection”)

                Set rs = CreateObject(“ADODB.Recordset”)

                Set rsAppend = DW.DataAccess.farsTable(strWorkTableName)

                ‘Connect to SQL Server database

                cnss.open “Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security

                Info=False;Initial Catalog=WLDemo;Data Source=DBServerName;”

                ‘Create query string

                strSQL = “Select * “

                strSQL = strSQL & “FROM tGL “

                ‘Get data

                rs.Open strSQL, cnSS

                ‘Check for data

                If rs.bof And rs.eof Then

                RES.PlngActionType = 2

                RES.PstrActionValue = “No Records to load!”

                Exit Function

                End If

                ‘Loop through records and append to tTB table in location’s DB

                If Not rs.bof And Not rs.eof Then

                Do While Not rs.eof

                rsAppend.AddNew

                rsAppend.Fields(“PartitionKey”) = RES.PlngLocKey

                rsAppend.Fields(“CatKey”) = RES.PlngCatKey

                rsAppend.Fields(“PeriodKey”) = RES.PdtePerKey

                rsAppend.Fields(“DataView”) = “YTD”

                rsAppend.Fields(“CalcAcctType”) = 9

                rsAppend.Fields(“Amount”) = rs.fields(“dblAmt”).Value

                rsAppend.Fields(“Desc1”) = rs.fields(“txtAcctDes”).Value

                rsAppend.Fields(“Account”) = rs.fields(“txtAcct”).Value

                rsAppend.Fields(“Entity”) = rs.fields(“txtCenter”).Value

                rsAppend.Update

                rs.movenext

                Loop

                End If

                ‘Records loaded

                RES.PlngActionType = 6

                RES.PstrActionValue = “SQL Import successful!”

                ‘Assign Return value

                SQLIntegration = True

                End Function

                • 5. Re: How to Connect source oracle erp with FDQM without using ODI
                  Francisco Amores

                  First of all you need to identify which data you want to extract and from where:

                  - Which database? oracle , mssql?

                  - Have you defined the SQL statement to extract your data?

                   

                  Then you need to adapt the script to your needs:

                  - Adjust the SQL connection string

                  - Adjust the SQL statement

                   

                  And finally you have to configure your import format to use the script.

                  • 6. Re: How to Connect source oracle erp with FDQM without using ODI
                    1050864

                    Database : oracle

                    sql : select currency_code,period_year from gl_balances where rownum < 11;

                     

                    connection details:

                    un:xx

                    pwd:yy

                    hostname: 11.11.1.0

                    port:1111

                    sid:orcl

                     

                    how to use these details in script ?...

                    Note: epm server is one machine with sql server db , but we need to connect oracle db which is in another machine

                    • 7. Re: How to Connect source oracle erp with FDQM without using ODI
                      Francisco Amores

                      For your connection string you could use:

                       

                      cnss.open "Provider=OraOLEDB.Oracle;User ID=xx;Password=yy;Data Source=TNSENTRY;"

                       

                      where TNSENTRY must be defined in your tnsnames.ora file.

                       

                      Regarding your SQL Query:

                      sql : select currency_code,period_year from gl_balances where rownum < 11;


                      what about your entity, account, and other dimension members?