This is an old revision of the document!
Omnidex Snapshots are usually created by exporting data from an underlying database; however, they can also be created by combining data from any source. Omnidex Snapshots can be created using the Omnidex EXPORT statement, or they can be create using any database or ETL tool that can create a raw data file or a delimited file.
The process of creating an Omnidex Snapshot begins with creating an Omnidex Environment File that references the underlying relational database. This will allow Omnidex to access the underlying database. There is no need to add Omnidex indexing to this particular Omnidex Environment since it will only be used to export data. Refer to the documentation for Oracle, SQL Server, or MySQL for instructions to create this Omnidex Environment.
Once Omnidex can access the underlying relational database, use the EXPORT statement to export data. This can be as simple as exporting the entire table …
export (select * from INDIVIDUALS) to individuals.dat; export (select * from INDIVIDUALS) to individuals.dat;
… or it can use more complex statements that denormalize, aggregate, join or filter data. The following example creates a denormalized table that includes columns from many tables, as well as new columns generated from SQL expressions.
export ( select INDIVIDUALS.INDIVIDUAL, HOUSEHOLDS.HOUSEHOLD, HOUSEHOLDS.ADDRESS, HOUSEHOLDS.CITY, HOUSEHOLDS.STATE, STATES.REGION, STATES.TAX_RATE, HOUSEHOLDS.ZIP, HOUSEHOLDS.COUNTRY, INDIVIDUALS.NAME, INDIVIDUALS.GENDER, INDIVIDUALS.BIRTHDATE, cast($compare_dates(INDIVIDUALS.BIRTHDATE, current_date, 'YY') as integer) AGE, cast(case when $compare_dates(INDIVIDUALS.BIRTHDATE, current_date, 'YY') between 0 and 17 then '0-17' when $compare_dates(INDIVIDUALS.BIRTHDATE, current_date, 'YY') between 18 and 29 then '18-29' when $compare_dates(INDIVIDUALS.BIRTHDATE, current_date, 'YY') between 30 and 39 then '30-39' when $compare_dates(INDIVIDUALS.BIRTHDATE, current_date, 'YY') between 40 and 49 then '40-49' when $compare_dates(INDIVIDUALS.BIRTHDATE, current_date, 'YY') between 50 and 59 then '50-59' else '60+' end as character(10)) AGE_GROUP, INDIVIDUALS.PHONE, cast(substring(INDIVIDUALS.PHONE from 2 for 3) as character(3)) PHONE_AREACODE, cast(substring(INDIVIDUALS.PHONE from 7 for 3) as character(3)) PHONE_PREFIX, cast(substring(INDIVIDUALS.PHONE from 11 for 4) as character(4)) PHONE_SUFFIX, INDIVIDUALS.EMAIL, cast(case when position('@' in INDIVIDUALS.EMAIL) > 0 then substring(INDIVIDUALS.EMAIL from 1 for position('@' in INDIVIDUALS.EMAIL) - 1) else '' end as character(60)) EMAIL_MAILBOX, cast(case when position('@' in INDIVIDUALS.EMAIL) > 0 then substring(INDIVIDUALS.EMAIL from position('@' in INDIVIDUALS.EMAIL) + 1) else '' end as character(60)) EMAIL_DOMAIN from INDIVIDUALS join HOUSEHOLDS on INDIVIDUALS.HOUSEHOLD = HOUSEHOLDS.HOUSEHOLD join STATES on HOUSEHOLDS.STATE = STATES.STATE join COUNTRIES on STATES.COUNTRY = COUNTRIES.COUNTRY join GENDERS on INDIVIDUALS.GENDER = GENDERS.GENDER where HOUSEHOLDS.COUNTRY = 'US' and INDIVIDUALS.BIRTHDATE <= 'December 31, 1999' ) to simple_view.dat;
When creating an Omnidex Snapshot using a database or ETL tool, simply request that the data be extracted into a raw data file or a delimited file. A raw data file is a binary stream of data, with fixed-length records using native datatypes. A delimited file uses delimiters to identify when one column ends and another begins, and similarly when one row ends and the next row begins. Refer to the section on Raw Data Files for detailed documentation.
One of the most common ways to create an Omnidex Snapshot is to expobe done using Omnidex's EXPORT command, or it can be done using any database tool that can generate a raw data file or a delimited file.