The community will be in read-only from Tuesday 11:59pm (PST) to Wednesday 7:30am (PST)
The community will be in read-only from Tuesday 11:59pm (PST) to Wednesday 7:30am (PST)
Service Desk Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Multiple DSN in Data Exchange

SOLVED
Go to solution
Highlighted
Ben Little
Occasional Contributor

Multiple DSN in Data Exchange

We are running SD 4.5 SP20. I need to import data from multiple Oracle tables into Service Desk, and would like to do this from one .ini file to make things a little more managable. Similar to how you can have multiple class and mappings in a .ini file, can you have multiple DSN's (each to its own Oracle Table) ?

I am doing this, because we are importing people into SD from our HR programs. While we might have 50000 people to import, each with a Dept, there are only 2000 Distinct Depts. If I do it with multiple classes, then I have to run thru all 50000 records and try and import multiple duplicates for dept, along with the people I am getting.
5 REPLIES
Vasily Kamenev
Honored Contributor

Re: Multiple DSN in Data Exchange

Hi
As I understood, your problem in double records of Person. The solution of this use unique fields. The easy way use for it use e-mail address, this is always unique value. But if your double person registerd in not one department, so this is a problem for you, because by Service Desk each Person belong to one Organization. So, by this way need to close filed relation to Org. in Person and never don't use a Caller in Service call, just CallerOrg. Person with Org. relate by Generic Relation field, but this way not likes then original.

Vasily
Peter Dent
Frequent Visitor

Re: Multiple DSN in Data Exchange

Ben,

I'm not sure that I understand the problem but, you can simplify things.

Import the dept info on its own first. Then import the people records and at the same time link them to the correct dept.

Two imports yes, but much simpler operation.
Ben Little
Occasional Contributor

Re: Multiple DSN in Data Exchange

Let me clarify a bit.
We are currently using a single table that has 50000 person records. Each person has a dept, a site, and a Business Unit. However, while each person is unique, the Site, BU, and Dept are not, and boil down to about 2000 distinct entries each.

We do a multi-class import, first going through Site, the BU, then Dept, and then finally, importing each person.

The .ini file allows us to do all this in one step:

[CLASSES]
NAME=PS_BUS, PS_SBU, PS_ORG, PS_SITE, PS_NAME_SUFFIX, PS_PERSON

[PS_BUS]
SOURCE=[PS2SD_DATAFEED_GOAREPORTS]
ATT=[BUSINESS_UNIT], \
[BUSINESS_UNIT_DESCRIPTION]
COLUMNS=[PS2SD_DATAFEED_GOAREPORTS].[BUSINESS_UNIT] AS [BUSINESS_UNIT], \
[PS2SD_DATAFEED_GOAREPORTS].[BUSINESS_UNIT_DESCRIPTION] AS [BUSINESS_UNIT_DESCRIPTION]
LOADTABLE=TRUE

[PS_SBU]
SOURCE=[PS2SD_DATAFEED_GOAREPORTS]
ATT=[Q_STRAT_BUS_UNIT], \
[SBU_DESCRIPTION]
COLUMNS=[PS2SD_DATAFEED_GOAREPORTS].[Q_STRAT_BUS_UNIT] AS [Q_STRAT_BUS_UNIT], \
[PS2SD_DATAFEED_GOAREPORTS].[SBU_DESCRIPTION] AS [SBU_DESCRIPTION]
LOADTABLE=TRUE

[PS_SITE]
SOURCE=[PS2SD_DATAFEED_GOAREPORTS]
ATT=[SITE]
COLUMNS=[PS2SD_DATAFEED_GOAREPORTS].[SITE] AS [SITE]
LOADTABLE=TRUE

[PS_PERSON]
SOURCE=[PS2SD_DATAFEED_GOAREPORTS]
ATT=[EMPLID], \
[NORM_NAME], \
[NORM_FIRST_NAME], \
[NORM_MIDDLE_NAME]

and so on.

However, in doing so, since there are 50,000 person records (and therefore 50,000 non-unique sites, BU's and Depts), it has to run through 50,000 imports for Site, 50,000 imports for BU, 50,000 imports for Dept, and then finally 50,000 imports for person. This takes 5 hours

To speed this up, I broke our data warehouse model into multiple Oracle tables, removing duplicates, There is one for Site, one for Dept, etc., so that Site only has 200 lines, Dept only has 2000 lines, and I only would have to import a total of about 55,000 lines instead of 200,000 to get all this data in.

Since each is in its own Oracle table, I need multiple DSN's to call them from the .ini file. With one, it is:
[DSN]
NAME=PS_TO_SD_DATAFEED
USR=XXXX
PWD=XXXXX

I need to see if I can do multiple tables all in this one .ini file.
Vasily Kamenev
Honored Contributor
Solution

Re: Multiple DSN in Data Exchange

Hi
Add to end of the text string Goup by the same as in SQL.
that do a shortly the list of Site and DEP.
CONDITION= .....\
GROUP BY [fieldname]
V
asily
Ben Little
Occasional Contributor

Re: Multiple DSN in Data Exchange

Cool, that last part helped fix it. Since we are not using CONDITION= (because there is no where clause in our query), the answer was to add the 'GROUP BY' after Source=...

SOURCE=[PS2SD_DATAFEED_GOAREPORTS] GROUP BY [BUSINESS_UNIT],[BUSINESS_UNIT_DESCRIPTION]

This way, when the parser creates the SQL Query, it formats it correctly as FROM "PS2SD_DATAFEED_GOAREPORTS" GROUP BY ...

Thanks Visaly
//Add this to "OnDomLoad" event