expanding the use of the connector with advance presented by: fareed audi analyst/programmer becky...

Expanding the Use of the Connector

with Advance

Presented by:

Fareed AudiAnalyst/Programmer

Becky JohnsonSystem Analyst

2

Agenda

• Setup• Connector Overview• Profile Setup Demo• Expanding Connector for• Complete Demo• Excludes• Best Practices

3

Setup

• Decide fields• Single query• Upload

– FTP site– Import facility– Web service

4

Connector Overview• Data Mapping

– Mapping Entries• Encompass Column Name• Encompass Friendly Name• Encompass Category• Advance Table Name• Advance Column Name

– Advance Categories• Category Name• From Expression• Filter Expression• Order By Expression

5

Connector Overview

• Profiles• Initiation

– Manual Initiation– Scheduler– Run History

6

Profile Setup Demo

www.qa.advance.ud.purdue.edu

7

Expanding Connector - Activities• Adding to existing baseline mapping

entries– Acitvity_code6 …15

• Encompass name activitiy_code14• Encompass Category - ACT• Advance table name - Activity• Advance Column Name- activity_code

– Tech Columns• Row index - 14• Advance Category - Activity

8

Expanding Connector - Activities• Adding to existing baseline mapping

entries– Acitvity_code6 …15

• Encompass name activitiy_code14• Encompass Category - ACT• Advance table name - Activity• Advance Column Name- activity_code

– Tech Columns• Row index - 14• Advance Category - Activity

9

Expanding Connector - Activities

10

Expanding Connector - Sports• Adding new mappings with new category and indexing

– Advance Category• Category Name - Sport Activity • Order By Expression – x.date_modified,

x.xsequence desc• Active box - checked

– Encompass name sport_code1• Encompass Category - ACT• Advance table name - Sport• Advance Column Name- sport_code

– Tech Columns• Row index - 1• Advance Category – Sport_Activity

11

Expanding Connector - Sports

12

Expanding Connector - Sports

13

Expanding Connector – Sport Start Year

• Adding new mappings with new category and indexing with conditions– Encompass name - sport_start_year1

• Encompass Category - ACT• Advance table name - Sport• Advance Column Name- sport_dt

– Tech Columns• Column Push Expression -

decode(substr(x.start_dt,1,4),'0000','',substr(x.start_dt,1,4))• Row index - 1• Advance Category – Sport_Activity

14

Expanding Connector – Sport Start Year

15

Expanding Connector – Birthday Pieces

• Adding additional fields from entity table– Birth_day, Birth_month, Birth_year– Encompass name - birth_day

• Encompass Category - Memb• Advance table name - Entity• Advance Column Name- birth_dt

– Tech Columns• Column Push Expression – substr(birth_dt,7,2)

16

Expanding Connector – Birthday Pieces

17

Expanding Connector – Cell Phone

• Telephone – cellphone– Advance Category

• Category Name - Telephone – cellphone• Filter Expression - x.telephone_type_code= 'C' and

x.telephone_status_code = 'A'• Order By Expression – x.status_change_date desc, x.date_modified

desc, x.xsequence desc• Active box - checked

– Encompass name – cell_phone_number• Encompass Category - Home• Advance table name - telephone• Advance Column Name- telephone_number

– Tech Columns• Row index - 999• Advance Category – Telephone - cellphone

18

Expanding Connector – Cell Phone

19

Expanding Connector – Date Format• Converting varchar to Date fields

– Encompass name - last_payment_date• Encompass Category - Memb• Advance table name - memb_purdue• Advance Column Name- last_payment_date

– Tech Columns• Column Push Expression –

to_char(adv_data_transfer.convert_partial_date( decode(x.last_payment_date,‘00000000',null,x.last_payment_date)),'yyyy-mm-dd hh24:mi:ss')

20

Expanding Connector – Date Format

21

Demo

• http://purdue-advtest.imodules.com/s/-1461/index.aspx

• https://qa.advance.ud.purdue.edu/

22

Excludes

• Complete Excludes (FERPA)– Create a view from a query that yields all

id_numbers that need to be excluded– Create mapping id_number– Add mapping to all profiles being used– Add in the profile

• Entity Filter Value - not in (select id_number from tms_encompass_excludes)

23

Excludes – No Email Solicitation

• Advance Category add filter mail_list_code = ‘19EML’

24

Excludes – No Email Solicitation

25

Best Practices

• Email– Update the view advance.tms_email_type

• Personal_email_type_ind = ‘Y’• Business_email_type_ind = ‘Y’

– Changes iModules username• Advance Id number should be the primary key when

setting up the query.• Dates need to be in the format ‘yyyymmdd• Setup the field names and data structure at iModules

to be the same as or as close as possible to the field names in Advance.

26

Best Practices

– Create different profiles for updates coming from same field

• Birth day - Birth Month - Birth Year

– Profile Auto Scheduling• No overlapping – unique constraint errors

– Tomcat• Auto-restart weekly

27

Summary

• Connector is very versatile.

• Many different ways to transfer the data.

28

Question & Answer

29

Thank you!

Fareed Audiaudi@purdue.edu

Becky Johnsonrjohnson@purdue.edu