Skip to Main Content

Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

FDM Integration script not selecting the FDM PoV to import data fo rgiven Period

2810743Feb 6 2014 — edited May 8 2014

Hi,

I have an issue with the below script when importing data, it's not using the FDM PoV but intstead importing all the data from SQL Table. Do i need to make a change for the Year it is FY14 in the table and FDM expects Apr- 2013. I don't use SQL so any script added to this will be appreciated.

If i have a Where clause = 'Apr' say then i get the data for that month.

SQL Table: Year and Date columns

FY14 | Apr

FY14 | May

FY14 | Jun

Import Script

Oracle Hyperion FDM IMPORT Integration Script:
'Created By:   admin
'Date Created:   2014-01-03 17:06:28
'Purpose:  To import PL and BS Actual data from ODS
'------------------------------------------------------------------

Dim objSS   'ADODB.Connection
Dim strSQL    'SQL string
Dim strSelectPer 'FDM Period
Dim strSelectYear'FDM Year
Dim rs        'SQL Recordset
Dim rsAppend   'FDM tTB table append rs object
Dim recordCount

'Initialize objects
Set cnSS = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set rsAppend = DW.DataAccess.farsTable(strWorkTableName)

'Get Current POV Period And Year And determine HFM Select Year
strSelectPer = Left((RES.PstrPer), 3)
strSelectYear = Right(RES.PstrPer, 4)
Select Case UCase(strSelectPer)
Case "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"
  strSelectYear = strSelectYear + 1
Case "Jan", "Feb", "Mar"
     strSelectYear = strSelectYear
    
End Select

'***************************************
'Watch with this as it can cause looping
'***************************************
On Error Resume Next
Err.clear

'Connect to SQL database
cnSS.Open "Driver=SQL Server;Server=xxxxxxxx\xxxxxxx;Database=ODI_WORK_MARS;UID=xxxxx;pwd=xxx"

'Connect to SQL Server database
cnSS.CommandTimeout = 1200

'Keep the error message handling in for testing but will probably need  to write
'to a log if running in an overnight batch
'*******************************************************************************   
'Error Handling
If  Err.Number <> 0  Then  
'  An exception occurred
RES.PlngActionType = 2
RES.PstrActionValue =  Err.Description & Chr(10) & "Unable to connect to SQL Data Source"
Err.clear
Set cnSS = Nothing
FinAllLocs_Conv = False
Exit Function
End If

'Create query String
strSQL = "Select * "
strSQL = strSQL & " From ODI_WORK_MARS.dbo.TMP_HFM_ACTUAL_DATA_EXTRACT_TIN1 "

'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
'   RecordCount = 0
    'Loop through records and append to FDM tTB table in location's DB
If Not rs.bof And Not rs.eof Then
  Do While Not rs.eof

'Create the record 
  rsAppend.AddNew
   rsAppend.Fields("PartitionKey") = RES.PlngLocKey         ' Location ID
   rsAppend.Fields("CatKey") = RES.PlngCatKey          'Current Category ID
   rsAppend.Fields("PeriodKey") = RES.PdtePerKey         'Current Period ID
      rsAppend.Fields("DataView") = "YTD"            'Data View ID
   rsAppend.Fields("CalcAcctType") = 9            'Input data indicator
   rsAppend.Fields("Entity") = rs.fields("Entity").Value        ' Entity/Genpo ID
   rsAppend.Fields("Account")= rs.fields("Account").Value        'Account ID
      'rsAppend.Fields("UD1") = rs.fields("Account").Value        'Account/ID
      rsAppend.Fields("UD2") = rs.fields("Account").Value        'Account/ID
      rsAppend.Fields("ICP") = rs.fields("ICP").Value         ' Inter-Co/Destination
   rsAppend.Fields("Amount") = rs.fields("Value").Value        ' Data Value ID
  
                          
  rsAppend.Update
  RecordCount = Recordcount + 1
 
 
 
  rs.movenext
  Loop
End If
 

'Records loaded

RES.PlngActionType = 2
    RES.PstrActionValue = "SQL Import successful!   "  & RecordCount

     
       
  
'Assign Return value
SAP_HFM = True

End Function

Comments

Francisco Amores

You are not using variable strSelectYear anywhere in the select.

2810743

Hi,

Thanks, can you direct me to the change required please as i am not very familiar with SQL.

Francisco Amores

If you take a look to your SQL query you selecting all with no WHERE clause.


strSQL = "Select * "

strSQL = strSQL & " From ODI_WORK_MARS.dbo.TMP_HFM_ACTUAL_DATA_EXTRACT_TIN1 "


If you want to get specific year/month from your table you will have to add the where clause to filter the select


SELECT * FROM ODI_WORK_MARS.dbo.TMP_HFM_ACTUAL_DATA_EXTRACT_TIN1

WHERE YEAR = 'FY14' and MONTH = 'Apr'

Above is a fixed SQL query as it is not using any variable.

You would need to make it dynamic so you have to add some variables:

strSQL = "SELECT * FROM ODI_WORK_MARS.dbo.TMP_HFM_ACTUAL_DATA_EXTRACT_TIN1 "

strSQL = strSQL & "WHERE YEAR = 'FY" & strSelectYear  & "'" &  " and MONTH =  '" & strSelectPer  & "'"

This would be the idea.

2810743

Hi,

Thank you for assisting, The Period Column has 12 Month so i need it to be dynamic but after adding not sure why but after adding the below

strSQL = strSQL & "WHERE YEAR = 'FY" & strSelectYear  & "'" &  " and MONTH =  '" & strSelectPer  & "'" it gets to the "No Reocrds to Load" after import in FDM.

Francisco Amores

Review if this code is OK


strSelectPer = Left((RES.PstrPer), 3)

strSelectYear = Right(RES.PstrPer, 4)

Select Case UCase(strSelectPer)

Case "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"

  strSelectYear = strSelectYear + 1

Case "Jan", "Feb", "Mar"

     strSelectYear = strSelectYear


Which POV are you in FDM when importing data from Web Client?

Let's say you are in Jan-2014...

Then your variables will be like:

strSelectPer = Jan

strSelectYear = 2014


IN the SQL query, you have FY & strSelectYear which will be "FY2014". Therefore you need to get the last two chars to make "FY14".

strSelectYear = Right(strSelectYear,2)


You should be able to write your sql query in a file to see what exactly you are querying from the database. Then you will now if your query is doing what you want.

You can find how to write in a file in the accelerators (Workbench). The variable you have to write is strSQL.

2810743

Hi,

i tried the following and on import it's just hanging and not returning to the workflow screen, i stopped FDM process and restarted but it just keeps showning as Connecting. How do i stop the process?

Francisco Amores

I would suggest starting with a simple integration script. Then you learn how it works.

After that you can add any particular requirement. Start from the highest level of detail is a good way of learning the process.

You can restart your FDM server.

SreenuR

Hi Francisco,

Me too having the same issue.I got so may good suggestions from you and I applied thanks for that.here actually my issue is very small but could not  reach see I have same data for original and alternate hierarchy when i selected all the parent member's in POV thorugh Smartview.But for a specific POV the data is there from database I can see but I am not able to import that data thrugh SQL integration  Script.

Here for a particular period in a year I have data showing from DB but when I execute the query through sql script i am not able to import that period data to see so could you give me how i should filter that particular period data to import and to load and I selected in HFM scenario as Zero view Adjst/Non Adjt as YTD and View is also as YTD.Could you tell me why Iam not able to see that data for particular account's even there is a data in DB.

it's an urgent requirement

Thanks for you support

Francisco Amores

Again,

1. Write your SQL query in SQL tool and assign hard-coded values to your filters

2. Translate your SQL query to FDM script using variables for filters

3. Write some debug code to write the SQL query generated in FDM in a flat file

4. Take the SQL query and execute it in the SQL tool

this is the only way of knowing why your data is not imported as you expect.

When you get your data imported as expected then you need to evaluate if data is exported as you expect. For that you can see the .DAT file generated.

Once you get data exported as you expect. If you have any issues related to HFM you would need to work on your HFM configuration.

1 - 9
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 5 2014
Added on Feb 6 2014
9 comments
3,781 views