* [[topbar |Table of Contents]] * [[intro:overview:what|Introduction]] * [[install:home|Installation]] * [[install:editions|Product Editions]] * [[install:requirements|System Requirements]] * [[install:platforms|Supported Platforms]] * [[install:guides:home|Installation Guides]] * [[install:licensing:home|Licensing]] * [[install:releases:home|Release Notes]] * [[admin:home|Administration]] * [[admin:admin:home|Administration Basics]] * [[admin:admin:architecture:home|Omnidex Architecture]] * [[admin:admin:applications:home|Building Applications]] * [[admin:basics:home|Omnidex Fundamentals]] * [[admin:basics:environments:home|Omnidex Environments]] * [[admin:basics:sqlengine:home|Omnidex SQL Engine]] * [[admin:basics:updates:home|Omnidex Updates]] * [[admin:basics:network:home|Network Services]] * [[admin:indexing:home|Omnidex Indexing]] * [[admin:indexing:concepts:home|Indexing Concepts]] * [[admin:indexing:indexes:home|Indexing Options]] * [[admin:indexing:creation:home|Index Creation]] * [[admin:indexing:strategies:home|Indexing Strategies]] * [[admin:indexing:activecounts:home|ActiveCounts]] * [[admin:indexing:powersearch:home|PowerSearch]] * [[admin:indexing:autocomplete:home|AutoComplete]] * [[admin:indexing:text:home|Omnidex Text]] * [[admin:features:home|Omnidex Features]] * [[admin:features:snapshots:home|Omnidex Snapshots]] * [[admin:features:grids:home|Omnidex Grids]] * [[admin:features:rollups:home|Rollup Tables]] * [[admin:features:geo:home|Geographic Searches]] * [[admin:features:expressioncols:home|Expression Columns]] * [[admin:features:segments:home|Omnidex Segments]] * [[admin:features:attach:home|Attaching Data]] * [[admin:features:export:home|Exporting Data]] * [[admin:optimization:home|Optimizing Queries]] * [[admin:optimization:plans:home|Query Plans]] * [[admin:optimization:caches:home|Dynamic Caches]] * [[admin:optimization:config:home|Configuring Omnidex]] * [[integration:home|Integration]] * [[integration:rdbms:home|Relational Databases]] * [[integration:rdbms:oracle:home|Oracle]] * [[integration:rdbms:sqlserver:home|SQL Server]] * [[integration:rdbms:mysql:home|MySQL]] * [[integration:rdbms:odbc:home|Generic ODBC]] * [[integration:rawdata:home|Raw Data Files]] * [[integration:rawdata:fixed:home|Fixed-length Files]] * [[integration:rawdata:delimited:home|Delimited Files]] * [[integration:rawdata:ost:home|Standalone Tables]] * [[dev:home|Development]] * [[dev:sql:home|Omnidex SQL]] * [[dev:sql:overview|Overview]] * [[dev:sql:statements:home|Statements]] * [[dev:sql:functions:home|Functions]] * [[dev:sql:examples:home|Examples]] * [[dev:connections:home|Connection Strings]] * [[dev:odbc:home|ODBC Interface]] * [[dev:jdbc:home|JDBC Interface]] * [[dev:debugging:home|Logging/Debugging]] * [[programs:home|Programs]] * [[programs:odxsql:home|OdxSQL]] * [[programs:odxnet:home|OdxNet]] * [[appendix:home|Appendix]] * [[dev:sql:home|Omnidex SQL]] * [[dev:sql:statements:home|Statements]] * [[dev:sql:functions:home|Functions]] * [[dev:sql:examples:home|Examples]] * [[http://www.omnidex.com/download/|Downloads]] * [[support:home|Support]]

~~NOTOC~~ {{page>:top_add&nofooter&noeditbtn}} ====== Administration: Omnidex Features ====== ===== 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]] ---- ==== 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 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> create table "INDIVIDUALS" physical "dat/individuals.dat" ( "INDIVIDUAL" CHARACTER(12), "HOUSEHOLD" CHARACTER(12), "NAME" CHARACTER(50), "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> 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 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> ==== ==== **[[admin:features:rollups:design|Prev]]** | **[[admin:features:rollups:indexing|Next]]** ====== Additional Resources ====== {{page>:admin:features:see_also&nofooter&noeditbtn}} {{page>:bottom_add&nofooter&noeditbtn}}

 
Back to top
admin/features/rollups/create.txt ยท Last modified: 2016/06/28 22:38 (external edit)
 
 
chimeric.de = chi`s home Valid CSS Driven by DokuWiki do yourself a favour and use a real browser - get firefox!! Recent changes RSS feed Valid XHTML 1.0