This shows you the differences between two versions of the page.
| Next revision | Previous revision | ||
|
admin:features:rollups:create [2011/01/10 22:41] els created |
admin:features:rollups:create [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| + | ~~NOTOC~~ | ||
| + | |||
| {{page>:top_add&nofooter&noeditbtn}} | {{page>:top_add&nofooter&noeditbtn}} | ||
| Line 5: | Line 7: | ||
| ===== Rollup Tables ===== | ===== Rollup Tables ===== | ||
| - | [[admin:features:rollups:home|Overview]] -> [[admin:features:rollups:design|Design]] -> **[[admin:features:rollups:create|Creation]]** -> [[admin:features:rollups:indexing|Indexing]] -> [[admin:features:rollups:optimization|Optimization]] | + | [[admin:features:rollups:home|Overview]] | [[admin:features:rollups:design|Design]] | **[[admin:features:rollups:create|Creation]]** | [[admin:features:rollups:indexing|Indexing]] | [[admin:features:rollups:optimization|Optimization]] |
| ---- | ---- | ||
| ==== Creating Rollup Tables ==== | ==== Creating Rollup Tables ==== | ||
| - | A rollup table is quite easy to create. The rollup tables are first created in the Omnidex Environment File, complete with the SQL statement that represents the rollup data. Then the UPDATE ROLLUPS command is issued in ODXSQL to populate the rollup tables. Here is a simple example of creating a rollup that provides counts of people based on geographic regions: | + | A rollup table is quite easy to create. The rollup tables are first created in the Omnidex Environment File, complete with the SQL statement that represents the rollup data. Then the UPDATE ROLLUPS statement is issued in ODXSQL to populate the rollup tables. Here is a simple example of declaring a regular table, followed by the creation of two rollup tables: |
| <code> | <code> | ||
| - | create table "LIST_GEO" | + | create table "INDIVIDUALS" |
| - | type ROLLUP | + | physical "dat/individuals.dat" |
| - | physical "dat\list.geo" | + | ( |
| - | as "select COUNTRY, REGION, STATE, COUNTY, CITY, ZIP, MSA_CSA, PMSA, | + | "INDIVIDUAL" CHARACTER(12), |
| - | count(*) NUM_INDIVIDUALS | + | "HOUSEHOLD" CHARACTER(12), |
| - | from LIST | + | "NAME" CHARACTER(50), |
| - | group by COUNTRY, REGION, STATE, COUNTY, CITY, ZIP, MSA_CSA, PMSA" | + | "GENDER" CHARACTER(1), |
| + | "BIRTHDATE" ANSI DATE, | ||
| + | "PHONE" CHARACTER(14), | ||
| + | "EMAIL" CHARACTER(60), | ||
| + | constraint INDIVIDUALS_INDIVIDUAL_PK primary ("INDIVIDUAL"), | ||
| + | constraint INDIVIDUALS_HOUSEHOLD_FK foreign ("HOUSEHOLD") references "HOUSEHOLDS", | ||
| + | constraint INDIVIDUALS_GENDER_FK foreign ("GENDER") references "GENDERS", | ||
| + | ) | ||
| + | in "simple.xml"; | ||
| + | |||
| + | create table "INDIVIDUALS_BY_HOUSEHOLD" | ||
| + | physical "dat/individuals_by_household.dat" | ||
| + | ( | ||
| + | "HOUSEHOLD" CHARACTER(12), | ||
| + | "GENDER" CHARACTER(1), | ||
| + | "BIRTHDATE" ANSI DATE, | ||
| + | "NUM_INDIVIDUALS" UNSIGNED INTEGER, | ||
| + | constraint INDIVIDUALS_HOUSEHOLD_FK foreign ("HOUSEHOLD") references "HOUSEHOLDS", | ||
| + | constraint INDIVIDUALS_GENDER_FK foreign ("GENDER") references "GENDERS", | ||
| + | ) | ||
| + | as "select HOUSEHOLD, | ||
| + | GENDER, | ||
| + | BIRTHDATE, | ||
| + | count(*) NUM_INDIVIDUALS | ||
| + | from INDIVIDUALS | ||
| + | group by HOUSEHOLD, | ||
| + | GENDER, | ||
| + | BIRTHDATE" | ||
| + | in "simple.xml"; | ||
| + | |||
| + | |||
| + | create table "INDIVIDUALS_BY_DEMO" | ||
| + | physical "dat/individuals_by_demo.dat" | ||
| + | ( | ||
| + | "GENDER" CHARACTER(1), | ||
| + | "BIRTHDATE" ANSI DATE, | ||
| + | "NUM_INDIVIDUALS" UNSIGNED INTEGER, | ||
| + | constraint INDIVIDUALS_GENDER_FK foreign ("GENDER") references "GENDERS", | ||
| + | ) | ||
| + | as "select GENDER, | ||
| + | BIRTHDATE, | ||
| + | count(*) NUM_INDIVIDUALS | ||
| + | from INDIVIDUALS | ||
| + | group by GENDER, | ||
| + | BIRTHDATE" | ||
| + | in "simple.xml"; | ||
| </code> | </code> | ||
| + | IMPORTANT: Note that the aggregation has a column alias of NUM_INDIVIDUALS. It is required that all aggregation functions have column aliases as the alias becomes the column name in the table. | ||
| - | In ODXSQL, ODBC or JDBC, the following command will populate the rollup tables in the Omnidex Environment: | ||
| - | update rollups | + | |
| + | In ODXSQL, ODBC or JDBC, the following statement will populate the rollup tables in the Omnidex Environment: | ||
| + | |||
| + | <code> | ||
| + | > update rollups | ||
| + | Database | ||
| + | Table Rows CPU Elapsed | ||
| + | ---------------------------------------------------------------------------- | ||
| + | SIMPLE | ||
| + | INDIVIDUALS_BY_HOUSEHOLD 5,000 0:00 0:00 | ||
| + | INDIVIDUALS_BY_DEMO 4,793 0:00 0:00 | ||
| + | ---------------------------------------------------------------------------- | ||
| + | Total 0:00 0:00 | ||
| + | |||
| + | Rollup tables updated | ||
| + | </code> | ||
| ==== ==== | ==== ==== | ||