Data Migration - System Configuration Import

Summary

This article covers the recommended and proper order for importing customer data into EngageIP. When importing large amounts of data it is recommended to use the DataMigration Utility.

Before you can proceed with any import process here please complete the article: Data Migration – Import Methods and Configuration.

Import Examples

1. Culture

2. Language

3. Country

4. TicketCategory

5. TicketContactMethod

6. TicketPriority

7. TicketGroup

8. TicketStatus

9. State

10. County

11. City

The element to add into the xml would be this: SortOrder="1" or any number. 0 being default, and 1,2,3, and so on being the orders where the lowest number floats to the top of the dropdown list in EngageIP

Owners Import

Allows you to setup your owner structure.

Branded indicates if the owner is Branded (presents owner account with a Setup Menu allowing for their own configuration options which will apply to all accounts under them) or Non-branded (inherits the configuration of the branded owner above them).

Country/State/County/City allows you to set the INPUT level of the address. For example, if they are all true, then pick lists will appear within the system. If they are false free form text fields will appear.

Owner Page Extension Import

Invoice Templates Import

Invoice templates available to the owner.

Your invoice template will go in the InvoiceTemplate element, however before you import, you need to use ascii characters in place of 5 key characters to allow the import. Example:

Bill Groups Import

BillGroups available to the owner.

Country Imports

Owner’s Countries

Full Country Listings

Country Code Format

Long Name Format

State Imports

Owner’s States.

XML for abbreviations listed below for US states. Long Name is the full state name where as the 'State' value is often the abbreviated value.

State Listings

State Code Format

Long Name Format

Full Province Listing

County Imports

Owner’s Counties. Note the links to State and County below.

City Imports

Owner’s Cities. Note the links between State, County and City. A city is associated with a county. If a city has more than 1 county it must be entered twice.

DisableReason Imports

Owner’s Disable Reasons

Service Imports

Defines Service offerings.

NOTE: Setup fee if zero doesn't do anything when importing, its held over from the migration from EngageIP 6 to EngageIP 8

NOTE: This is the CORE service.

ServiceTypes Import

Service Components

Each service can have one or more component. Refer to the XML samples for more details.

Provisioning Component

To import the provisioning component, a table in the EngageIP database will need to be manually updated (BOSS). Open the table ServerTypeAttributeProvisionSet, add a new row by entering a ServerTypeID, for example '1', then add a name,for example 'test', hit enter to save the row. Then you can import them with the below XML replacing the ServerTypeProvisionSet element with the name that you specified in the table.

ServiceAttributes

ServiceAttributeCost

ServiceAttributeGeneralLedger

ServiceAttributeInvoiceDetail

ServiceAttributePrepaid

ServiceAttributeProfileQuestion

ServiceAttributeProfileAnswer

Import Generic method

(NOTE: Update the reference as the two lines are associated. Two lines per profile question)

Generic Method with List Provider

Here is the generic xml to Import ServiceAttributeProfileQuestion with ProfileQuestionListprovider, in this example we are assuming a ProfileQuestionListProvider exists with the name “TestListProvider”

ServiceAttributeRadius

ServiceAttributeTaxCode

ServiceAttributeTaxExempt

TaxExemptTypes

XML Import file:

Open

ServiceAttributeTaxType - NOTE: This is the Service Tax Category on the Setup page

ServiceAttributeTelecom

ServiceAttributeTelecomBucket

ServiceAttributeTransactionCode

Service Invoice Group

Package Imports

Defines Package Offerings. A package is made up of services and therefore you always sell packages. The services within the package will convert up to the Package type and Length. Each package can have components. Refer to the XML samples for details.

PackageAttributeContract

PackageAttributeCreditCardAuthOnly

PackageAttributeDiscount

PackageAttributeProfileAnswer

PackageProfileQuestion

PackageAttributeEquipment

PackageAttributeResource

PackageAttributeContract

Package Attribute Bulk Package

Main Package Import XML:

Generic:

NOTE: The 'code' element above in RED is not required, this is a value used in the migration from EngageIP 6 to EngageIP 8. Packages can be imported without this field.

Package Quantity Import

Allows the import of package quantity for services if no other quantity component exists on the package for specified service.

Package Upgrade Import

Allows importing of the upgrade component onto packages. Multiple upgrade components are allowed here. Monthly Package in the example is the package on which the upgrade component will be placed and the package that you would like to allow an upgrade to would be the 'Quarterly Package' in the below. Quarterly service is the one to which we will tag any GL codes if they exist when a debit is added do to an upgrade charge.

PackageService Imports

This is the connection between the packages and services known as the Package Service Connector. Basically, the collection of services that make up the packages.

Profile Question Imports

Account Profiles Questions.

Driven from the Data Types table using the REGEX values defined here.

• Text

• Integer

• Double

• Date/Time

• Date

• Password

• Address

• Postal Code

• Email

• Phone

• CreditCard Number

• CVV

• CreditCard Expiry Date

• Full Name

• Bank Routing Number

• Bank Account Number

• Currency

• IP Address

• Memo

• Encrypt

• Domain Name

• MAC Address

• Port

• Script

• Time

• Terms

• Day Of The Month

• Path

• URL

• Usage Phone Number

• File

GeneralLedger Imports

Defines General Ledger Codes

RestrictedUserName Imports

Restricted user names for an owner.

Inventory Importing

InventoryItemDefinitionCategory

Defines the Inventory Item’s Category.

InventoryItemDefinition

Defines the Inventory Item’s description and details.

InventoryImports InventoryItem

Defines the Inventory Item

InventoryLocation

Defines Inventory Item Locations

Resource Importing

ResourceItem

Defines the Resource Management Item.

ResourceType

Defines the Resource Item Type.

ResourceTypeCategory

Defines the Resource Item’s Category Type.

ResourceTypeCategoryOwner

Defines the Resource Item’s Owner.

ResourceItemPool

Defines the Resource Item’s Pool.

ResourceTypeProvider

Defines the Resource Item’s Provider.

Resource Profile Question and Answer

To import just the profile question, use the first two data rows. note that the ReferenceName needs to be unique per line for importing

CardType

Owner’s Credit Card Types

TaxCode Importing

Owner’s Tax Codes which are made up of Tax Rates.

TaxType = Service Tax Category
TaxClass = Account Tax Category (read further down for XML here)

Generic Taxcode Import

TaxRate Imports

Owner’s Tax Rates

TaxCodeRate Imports

Owner’s Tax Code Rates. This represents the link between tax codes and tax rates.

TaxType Importing

(AKA Service Tax Category)This is the tax grouping for services used in conjunction with the tax codes.

TaxClass Importing

This is Account Tax Category in the AdminPortal UI.

TransactionCode Importing

Service’s Transaction Code for grouping.

TicketCategory Importing

Owner’s Ticket Categories

TicketGroups Importing

TicketTypeCategoryConnector

Owner Ticket Email Template

Ticket Extended Attribute Import

Here is for one with a select list:

TicketSubCategory

Owners Sub Categories

The migrator format here is the 'generic' format (where there is no specific import format to import the specific piece of data). It requires that you enter the owner as shown below in a string with the category. (The string of data actually then searches through the tables for the right owner to find where to import the data to)

Ticket Resolution

Ticket resolutions attached to Categories / Subcategories

TicketStatus

Type = "Open" or "Closed" (system statuses)
Name = your custom status

TicketPriority

Owner’s Ticket Priority

ContactType Imports

Owner’s Contact Types. It is important that you define the contact types required with the appropriate contact points.

User Extended Attributes 

ContactPointType Imports

Owner’s Contact Points related to the Contact Type. These are the contact points available to Contact Types and MUST be well defined.

Languages Import

Allows the importing of language translations. You must have entered the language name already on the setup page for each owner you need to import translations for.

Steps to import translation xml files into EngageIP

1. locate the language ID from the language table

2. delete * from translations where languageID = X

3. use DB migrator to import the xml.

For generic translation xml files be sure to find and replace the owner with the correct owner for import.

For generic import which is used if special language characters and accents will not work is the below XML format where language ID corresponds to the ID of the language in the database:

Role Imports

Specifies the roles available to each owner.

Role Permissions

Role Attribute Profile Question

Update the 'x' there in the bottom sections with the ID of the role for each owner.

Same as above but this provides a select list instead of a text input box:

UserStatusTypes Import

• OwnerID must = the ID of the owner in the 'owner' table

• StatusTypeID must equal the id of the status type in 'StatusType' table

• ImageURL - this is the URL pointing to the image you want displayed on the account for the specified status. A cancelled stamp or suspended stamp

• ShowInAccountStatistics - this defines whether you want this named status to show in the account statistics at the bottom left of each page

Telecom Imports

TelecomRatePlan

The sample below provides a way to import rate plans. Note that if Inbound = true, it means you are importing an inbound rateplan. If its outbound, set that flag as false.

TelecomBuckets

The telecom buckets available within the system. These can be assigned to services using the Telecom Bucket service attribute.

• Import UDR Rate Plan

• Attached UDR Rateplan to a Package/ Service

• To import UDRClass, and UDRRateGroups the XML import works out of the box using the following format(s).

Notes

The opening and closing tags are plural (just add s (UDRClasss is correct))
Name = “the UDRClass Name you want to add” UDRRater = “Class” or “Geo Tree”
/Owner = The Owner Name (/ in front tells Generic import to convert to OwnerID to store in table)

For UDRRateGroups, they need UDRRates to be useful, and the stored procedure takes care of copying both, so you may want to continue having that as part of your procedure, but this will be useful for any new (System-wide) rategroups, add them to one Owner (ie: Logisense Corporation), import rates and then use the SP to copy to all.

Notes

The opening and closing tags are plural (UDRRateGroups)

/Owner = The Owner Name (/ in front tells Generic import to convert to OwnerID to store in table)

Name = “the UDRRateGroup Name you want to add” Start="2010-01-01 00:00:00.000"   --Start Date, any CDR before this date will not be rated by this Rate Group (so use “2010-01-01 00:00:00.000” , unless a specific date is required) End="9999-12-31 23:59:59.997"  -- End Date, any CDR after this date will not be rated by this Rate Group(so use “9999-12-31 23:59:59.997”, unless a specific date is required) Retired="0"    0=active 1=retired  - Informational only (if rate group is retired End Date should be set to the specific date of retirement – no CDR will process after End Date ) UseForCost="0" --  0=Not a Cost plan 1=Cost Plan

UDR Rate Groups to RatePlans

UDR Custom Field Imports

Note Types Imports

Financial Periods Import

FinancialPeriod

Server Imports - Server and Profile Answers

You need to replace the following in the code:

• Logisense Corporation = owner name

• 1.1.1.1 - IP of server or URL

• test = name of server

• Username and Password -  labels of the profile questions existing

• answer1 and answer2 - the profile question answers to import

Tier Sync Imports

In order to get it working a TierSyncOwner and TierSyncPackage has to be present already in the database and make sure you reference the correct package and servers by name.

Rate Importer Tool

Introduction

The LogiSense Rate Importer is a tool that has been designed to make importing new rates and updating existing rates easy.  The Importer can take text files, map the data to rates, and automatically determine if the rates are new/updated/same compared to existing rates.

Starting the Importer

Run the .exe named “Logisense.RateImporter.exe”.  You will be greeted with a window that looks like this:

Open

Click the Next button.

Now you are presented with the following options:

Open

From here you need to tell the Importer what kind of file you will be importing, and the name of the file.  Note that there is also a “View History” button which will show you a history of previous imports.

Click the Import Profile drop-down to select a file type that you will be importing:

After you have selected what you need, select the import file by clicking the Browse button and then the Next button to continue.

Next you will be presented with the following options.  Please select the appropriate values for these rates from the drop-downs and click Next.

Now you will be shown the Data Verification screen (Note: there may be a slight lag before this loads).  This shows the data from the file, and how it is being mapped.  Note in our example, the first column is being used for Pattern, the second for the rate Amount, and the third having the name ColumnX (in this case 2), tells us the importer will not use it.

If the data here looks like the correct file that you wish to import, and the mappings look correct, click Next.

The next screen is not interactive, and merely indicates the progress of the Importer as it validates the new rates:

When the validation is completed, you will be presented with the results:

In this example, a somewhat incomplete GeoTree was used (and the first row was intentionally invalid), hence it found 6712 errors.  At this point we can click Cancel, if we wish to try and fix things (update the Geotree, use the correct rates file if this one is wrong, etc.)  We can also choose to click Next and continue importing the valid rates that were found.  Note that before we select either option, we also have the “Save Errors” button on the left, where it will save a CSV file with all the ‘bad’ rates out.

Suppose in the scenario above we click Next and import the good rates, but later we wish that we had a list of the bad rates.  If we re-run the importer and load the same rate file, we should end up at this screen with the message “Out of the 146966 rates in the import file, there were 0 new rates, 0 updated rates, and the following 6712 errors.”  Now we can once again view the errors.  In the event that we fixed the problems in the Geotree, then we could now import the once problematic rates.

So if there are errors, it is your choice if you want to try fixing them before importing any rates, or import the valid ones first, and import the rest later.

After clicking Next from the Validation step, you will be presented with the following step:

This will be displayed and show progress while the rates are created/updated.  Note that when it says “this may take a while”, it’s telling the truth.  Except a couple minutes at the very least, for a US NPANXX import, possibly longer.

Finally we are here:

Rates are now imported.  You can check the rates in EngageIP to see that they look correct.  You may also fix any problems that existed and re-import if you opted to ignore validation errors.

File formats supported:

Direct Pattern Match (i.e. NPANXX)

CSV With:

• NPANXX in the first column, no leading 1

• Amount in the second column (floating point number)

International w/WorldZone
CSV With:

• Pattern in the first column (does not include country code)

• WorldZone in the second column (1 = NANPA)

• DestinationCode in the third column (country code if international)

• Amount in the fourth column (floating point number)

International w/Full Dial Pattern
CSV With:

• Country

• Full pattern in the first column (country code included)

• Amount in the second column (floating point number)

Lata/OCN
CSV With:

• OCN in the first column (three digits, and one alpha/digit)

• Lata in the second column (three digits)

• Amount in the third column (floating point number)

Troubleshooting - Common File Errors

• Make sure your opening and closing tags are pluralized, ie add an 's' to the end. <tests> <test name="asdf"> </tests>

• Make sure elements are spelled correctly and capitalized correctly

• Make sure the data referenced exists in EngageIP. i.e. for things like taxcode being applied to customers, the taxcode specified must exist exactly as you have it typed in the file

• If you are trying to open an XML file in Excel, make sure to have more than one line of data, two lines is the minimum otherwise Excel won't recognize the format correctly (this does not include opening and closing tags)