|
|
|
.Demo file Loader, Documentation
Also see:
The tables described below can be in dBase, .csv (with a header row) or Access(Tables and Queries) format. Table references to dBase tables should include the ".dbf" extension. Table references to CSV tables should include the ".csv" extension. Table references for Access tables should be "FileName.mdb,TableName". There is no limit to the number of fields in a CSV file, so it is a very good choice if you are going to use the Allocate engine to produce data from polygons that you are going to want to add into a .demo file.
For an application to use the Allocate engine it must build its application-specific data into a Demo File. Data must be in either the general format "Geography, DemoVar1, DemoVar2, ..." OR "Geography, DemoVars, Values". In both formats Geography is one of those built into the Allocate dataset with which you are working (e.g. BlockGroup, Tract, zip, county, state, MSA, or DMA). DemoVar# is a column of demographic information for that geography (example: the population for the zip code). However in the first example, the data is crosstabed (each GeoID occurs only once) and in the second, there is a row for every GeoID/Var combination. You specify which kind of table you have with arguments in the INI file.
You will almost always need tables of the same demographic information for each level of geography. These are all built into the same demo file. If you don't have a table for a geography, the Allocate engine will fail if asked to report a demographic variable at that level of geography.
This whole process is driven by the DemoKeys file. Any geography that is included in the DemoKeys file can be built into a demo file. Each demographic data file requires that it has the exact same keys as the DemoKeys and that they be in sorted order.
Here's an example of the US geographies:
The following keys (char fields) are required for the tables of demographic information at each level of geography:
For the Demo File to Build Data the Geographies must be in the Following Order:
| LevelNum |
Geography
(TableName) |
ShortName |
Name |
Key columns (can be separate columns combined during load) |
| 0 |
BlockGroup |
BG |
BlockGroups |
County FIPs+Tract+BlockGroup codes |
| 1 |
County |
County |
Counties |
County FIPs code |
| 2 |
DMA |
DMA |
Designated Market Areas |
3-digit DMA code |
| 3 |
MSA |
MSA |
Metropolitan Statistical Areas |
4-digit MSA code |
| 4 |
ZIP |
ZIP |
ZIP Codes |
Zip code |
| 5 |
State |
State |
States |
State FIPs or normal 2-ltr state code. |
| 6 |
Tract |
TR |
Census Tracts |
County FIPs+Tract codes |
| 7 |
United States |
FullUS |
Entire US |
static "00" |
Tips
- Absolute values are generally preferable to percentages. However, if the percentages have "Units" of "Population" or "Households" they can be accurately averaged in reports.
- Don't include values that are simply calculated from other columns. You can achieve these calculations by adding a Formula to the Virtual Variable File or be specifying the formula in a report. Unless there are issues of privacy/copyright, the Virtual Variable File is recommended for any formulas that can be derived from the values in the Demo file.
To build a demo file you must create a table describing the fields, units, and summary methods for each demographic variable you want to build into the demo file. The data type for these columns need to be all 'Text'.
The structure of the table is:
| Column |
Type |
Description |
| LEVELX |
Text |
Where X is the level # in the Demokeys file [Header] table. This specifies the name of the table. This works for any DemoKeysFile. Note: If you choose you can have the BuildDemo.mdb contain links to each of the source tables or queries. If you do this, you must proved a Select statement: "BuildDemo.mdb,select * from McLaganMSA". |
| FIELDNAME |
Text |
Name of output field. Also, column name checked in the input tables.
Notes:
- FieldName must be all upper case.
- Length must be <=10 if you want to be able to export to .dbf format. All the "official" .demo files distributed w/ the shrink-wrap product follow this.
- Length must be <=59 for the field to be used via the Allocate ODBC driver (ODBC buffers are 64 - 1 null terminator - 4 for leading "IDX_" = 59). This is not enforced during load.
|
| FIELDNAMELONG |
Text |
Long Name of output field. Optional. For outputs that support longer field names, this field name fill be used instead of the short one. Probably should be <32 chars, because many output formats (including .TAB) have a 32 character field name limit. |
| ALT_NAME1 |
Text |
Alternate column name checked in input tables. Optional (but column must exist). |
| ALT_NAME2 |
Text |
Alternate column name checked in input tables. Optional (but column must exist). |
| FORMULA |
Text |
Formula based on FIELDNAMEs previously described in the database table. The formula is stored directly in the demo file and calculated on the fly when used in a report. The purpose is to provide something that acts like the other demographic fields and only take a small amount of storage. Ex: "TOT_POP_PJ/CSQ_MILES". If this is filled in, the Table name fields and Alt column name fields must be blank. The column is required, contents are optional. Again, note that if the formula is not proprietary, consider using the Virtual Variable File for more flexibility. |
| CATEGORY |
Text |
Top-Level grouping. Ex. "Population".
Note: Length of this file + length of the internal demo file name must be <=62. Note this is NOT checked during the .demo file build. The problem w/ a longer name is that the ODBC driver will not be able to access the table DemoFileInternalName_Category (however you can still get to the data via the DemoFileInternalName_All or Theme_All tables). Required |
| VINTAGE |
Text |
Next-level grouping. Specifies the source or style of the data. Ex. "5 Year Projections", "Census". Required. |
| YEAR |
Text |
Year of data vintage. Usually displayed in parens after Vintage. Ex. "2003". Note: Must be 4-digit (the builder assumes this and your data will appear incorrectly in Allocate if you use anything else). Required |
| SUBCAT |
Text |
Further categorization. Ex. "Male By Age". Optional. |
| DESC |
Text |
Description of item displayed at the end of the above categorizations. Ex. "Age 25 - 34". Required |
| UNITS |
Text |
The units of the value being stored. Determine the precision of the storage and precision of output.
| "Units" |
Description |
Fractional or Whole Number |
| "Dollars" |
Dollars. |
Whole |
| "Dollars in (000's)" |
Dollars already divided by $1,000. |
Whole |
| Dollars Round 1,000 |
Dollars that will be rounded for storage to $1,000. See Round 10. |
Whole, rounded to 1000's |
| "Households" |
Number of households. |
Whole |
| "Persons" |
Average number of people ("2.4 children"). |
Fractional |
| "Physical2" |
Square miles. 2 digits of precision. |
Fractional |
| "Population" |
Number of people. |
Whole |
| "Years" |
Years, ex. Age. |
Fractional |
| "Percent" |
Percentage. |
Fractional |
| "Index" |
Indexed value. |
Fractional |
| "Integer" |
Integer. |
Whole |
| "Round 10" |
Input number magnitude may exceed MAX_LONG. Input will be rounded to nearest 10. Handles numbers up to up to 20 billion. On retrieval will have a zero in the 10's place. Allocate stores all input numbers as long, so we need to lose a place of accuracy to store numbers greater than MAX_LONG (2 billion). |
Whole, rounded to 10's |
| "Round 100" |
Same as "Round 10", handles numbers to 200 billion and rounds last two digits to zero. |
Whole, rounded to 100's |
| "Round 1,000" |
Same as "Round 10", handles numbers to 2,000 billion and rounds last three digits to zero. |
Whole, rounded to 1,000's |
| "Round 10,000" |
Same as "Round 10", handles numbers to 20,000 billion and rounds last four digits to zero. |
Whole, rounded to 10,000's |
| Integer64 |
Numbers up to 264 |
Whole |
| String |
Text Strings smaller than 256 characters |
|
| Dollars64 |
Numbers up to 264 (values will be displayed w/'$') |
Whole |
| Dollars2_64 |
Same as above, but with change (values will be displayed w/2 decimal places of precision) |
Fractional |
Fixed2_64
Fixed4_64
Fixed6_64 |
Numbers up to 264/2 or 264/4 or 264/6 |
Fractional | |
| ACTION |
Text |
How to summarize the values when a report is grouping multiple geographies.
| "Action" |
Description |
Example |
| "Sum" |
Values for each geography are summed. Use this when the value is an absolute number.
A test that should fail for values to be summed: "can the value for an individual geography be compared to the US-wide number". |
"Total Population" should be summed since it is an absolute value.
In odd cases this could be a percentage: "state's percent of GNP"). |
| "Average" |
Values for each geography are averaged. The averaging is weighted based on the type in the Weight column. Use this when the value is a relative number.
A test that should succeed for values to be averaged: "can the value for an individual geography be compared to the US-wide number". |
Demo vars that area percentages, indexed, averages, or (as a fudge) medians (you can't really average median values). |
| "Min" |
The minimum value is returned when geographies are summarized. |
We haven't used this. |
| "Max" |
The maximum value is returned when geographies are summarized. |
We haven't used this. |
|
| WEIGHT |
Text |
Determines how to weight values that are being summarized. If the Unit is Household or Population, then it's usually (always) the same for the Weight.
This is used for reports that summarize using the Action "Average".
It's also used for variables using the Action "Sum" when the summation is for a custom polygon or radius (note: this type of summation requires that there is BlockGroup data in the demo file). When the polygon or radius cuts across a BlockGroup Allocate determines the Block centroids that fall within the polygon or radius and weights the BlockGroup''s demographic value based on the percent of the Population/NumHouseholds for the BlockGroup that are in that Block. (The Population and NumHousehold data for Blocks is part of Allocate from 2000 census figures.)
Note that due to lack of data at the block level, and variables with a weighting assignment of BusEmpCount or BusEstCount will default to "Count".
THESE WEIGHTS MUST RELATE TO VARIABLES IN THE "BASE" demographic file. See below for further clarification.
| "Weight" |
Description |
Example |
| "Population" |
Weight based on population. |
PerCapitaIncome, "Units" are "Population" and Action is "Average". We want to weight based on the population in the Block. |
| "Households" |
Weight based on Households. This is used for all consumer expenditure data even when this data may seem based on Population (like "Spending on shoes"). |
NumHousingUnitsVacant, "Units" are "Households" and Action is "Sum"/ We want to weight based on the households in the Block. |
| "Count" |
No weighting is used. |
SquareMiles, Units are "Physical" and Action is "Sum". There should be no special weighting of this number for the BlockGroups that are included in the polygon or radius, just treat them equally as one of the X Blocks in the BlockGroup. |
| "BusEstCount" |
Weight based on Business Establishments |
Number of Establishments, 100,000 Employess and greater |
| BusEmpCount |
Weight Base on Employee Count |
Employees by Occupation: Clerical | |
Before running the loader you must create a BuildDemoFile.INI file that points to all file locations, sets key field names, and sets a few options.
The ini file keys (e.g., tablePath=.\, etc.) are:
| Key |
Description |
| tablePath |
Location of source databases referenced in the definition table. You can include multiple paths delimited with ";". Be sure paths end with "\" |
| fieldInfoFile |
Location and name of the definition table. For Access this is the pathname for the .mdb, comma, the name of the table. |
| outputFileName |
Location and name of the output demo file (.demo). |
| blockGroupFileName |
Location and name of the DemoKeys file that contains lists of all the known geographies. The demo file builder uses these lists to query the source tables for the specific geographies. |
| CompressVersion2 |
T/F. Use the newer fancier MUCH better compression. Builds run 1/2 the speed. File will only be compatible with version 3.5 and above. This should generally be on for larger (>100MB) files. Recommend that all full builds be set to True |
| |
The following three parameters are used for testing/diagnostic purposes only. |
| isTest |
T/F. When True will only load the first 64 items for each level of geography (which is all states). The demo file that's built is completely useable and can be tested from Allocate. |
| numTestRecords |
The number of records at each level of geo. to test. Works with isTest. Default=64. |
| skipToLevel |
Another diagnostic. Skips loading any data until it gets to the specified level from the demoKeys file. |
| |
FOR TESTING PURPOSES, the most common settings are commenting OUT the CompressVersion2, and comment IN isTest. numTestRecords and skipToLevel are rarely used. |
| LevelIDNameX |
where X is the level # in the DemoKeys [Header] table. The field name (or names) to look for the ID field for the geographic level. Note: All field names can be combined with "+" and comma seperated to look for more than one column name. |
| |
The following 4 parameters are largely intended for use with "Primary" or "Base" Demographic files |
| PopField |
The name of the demo field assigned in the defintion table for total population. Required for Primary Demo files. Optional for supplemental files. |
| HHField |
The name of the demo field assigned in the defintion table for total households. Required for Primary Demo files. Optional for supplemental files. |
BUSESTCOUNTFIELD
|
The name of the demo field assigned in the defintion table for Business Establishments. This is only needed if business variables are referencing (within the demo file) the BusEstCount variable for Weighting |
| BUSESTCOUNTFIELD |
The name of the demo field assigned in the defintion table for Business Employees. This is only needed if business variables are referencing (within the demo file) the BusEmpCount variable for Weighting |
| |
Three column source data tables are not the norm. The following two parameters must be included when building a three column-based demo build. |
| VerticalFieldIdent |
If using three column source data tables, this specifies the name of the column that holds the fieldnames referred to in the template. (Note: Because of a bug in the BuildDemoFile program, you must terminate a "three column" input CSV file with an invalid, dummy, record to force processing of the final valid data group. pct, 2001/03/02) |
| ValueFieldIdent |
If using three column source data tables, this specifies the name of the column that holds the values for the corresponding fieldname/geoID combination. |
| |
|
| username |
SRC supplied username for authentication. |
| password |
SRC supplied password for authentication. |
| |
|
| vendorDesc |
Internal name of demo file. Can be used to dot-specify variables on reports. This is necessary to disambiguate variable names when more than one demo file contains the same variable name (try to avoid that!). If there is a space in the name it will be converted to a "_". Do NOT include any commas here (the file will build but Allocate will not be able to build or use an index base b/c the comma will be incorrectly parsed). Note: The length of this name + length of the Category must be less than 59 characters, so keep this concise |
| vendorCopyright |
Output in reports. |
| |
Additional, optional parameters (not often used) |
| dependentDemoFilen= |
Where n is an integer from 1 to n. If a formula in the demo file being built is referencing a fieldname in ANOTHER demo file, that referenced demo file must be listed here. This is convenient when a build is too large for a single demo file, and there are numerous formulas being built in. By "dot-specifiying" the demo file within the formula field in the template, the demo file can be broken into multiple files that will reference each other seamlessly. |
| TestOnly1Field |
Builds the specified variable only. TestOnly1Field=TOTALPOP. Never Used |
| testFirst10 |
Builds the first 10 fieldnames in the demo file. testFirst10=True. Never used |
| |
Any additional parameters in the log file are not currently supported |
(Note) All field names can be combined with "+" and comma separated to look for more than one column name. Note on BuildDemoFile.ini:
- Comments are semicolon;
- Logical "OR" for fields of LevelID# is comma;
- All files used for data must be sorted on geography key (the LevelIDName# column);
The Following is an example of a demo file ini and its corresponding geographies:
tablePath=\\bobcat\e\AGS\2003_01\ExtractedData\
fieldInfoFile=\\bobcat\e\AGS\2003_01\Base_Demographics\Template.mdb,Template_2000_Base
outputFileName=\\bobcat\e\AGS\2003_01\SRC_US_03A_AGS_Base_Demographics_03B.demo
demokeysFileName=\\Zappa\w\Data_Master_Copy\SRCData\SRC_US_03A\Base\SRC_US_03A_DemoKeys_B.src
;istest=True
CompressVersion2=True
;skiptolevel=7
LevelIdName0=key,id,blockgroup
LevelIdName1=key,id,tract
LevelIdName2=key,id,COUNTY
LevelIdName3=key,id,STATE
Username=mike
password=#$%^
venderDesc=AGS_Base_Demographics
venderCopyright=(C) 2002 Applied Geographic Solutions, Inc. All Rights Reserved, SRC, LLC.
dataVersion=2002
HHField=HHDCY
PopField=POPCY
BUSESTCOUNTFIELD=BUSCYEST
BUSEMPCOUNTFIELD=BUSCYEMP
|
Example of a Demo File (Source) Template For DemoBuilds:
| Level0 |
Level1 |
Level2 |
Level3 |
FIELDNAME |
FORMULA |
CATEGORY |
VINTAGE |
YEAR |
SUBCAT |
DESCRIPTION |
ACTION |
WEIGHT |
UNITS |
ALT_NAME1 |
ALT_NAME2 |
| BGCEX.DBF |
TRCEX.DBF |
COCEX.DBF |
STCEX.DBF |
XCYHF1 |
|
Consumer Expenditures (Ave Hhld Exp) |
Current Year Estimates |
2000 |
|
Household Textiles |
Average |
Households |
Dollars |
|
|
| BGCEX.DBF |
TRCEX.DBF |
COCEX.DBF |
STCEX.DBF |
XPYFB1 |
|
Consumer Expenditures (Ave Hhld Exp) |
Five Year Projections |
2005 |
|
Food At Home |
Average |
Households |
Dollars |
|
|
| BGCEN2.DBF |
TRCEN2.DBF |
COCEN2.DBF |
STCEN2.DBF |
HOU90RENT |
|
Housing Units |
US Census |
1990 |
|
Renter-Occupied |
Sum |
Households |
Households |
|
|
| BGDEMPY1.DBF |
TRDEMPY1.DBF |
CODEMPY1.DBF |
STDEMPY1.DBF |
DWLPYRENT |
|
Housing Units |
Five Year Projections |
2005 |
|
Renter-Occupied |
Sum |
Households |
Households |
|
|
| BGDEMCY2.DBF |
TRDEMCY2.DBF |
CODEMCY2.DBF |
STDEMCY2.DBF |
DWLCYRENT |
|
Housing Units |
Current Year Estimates |
2000 |
|
Renter-Occupied |
Sum |
Households |
Households |
|
|
For an additional template example, click here.
Drag the demo file to the icon for Allocate to register the demo file (if you only have one of the applications installed you can double-click the demo file, the extension is registered to start the Allocate application).
Once registered, the demo file appears as a folder in the list of variables. The other top level folders include the Categories from the demo file that contains the "standard" demo vars.
To unregister a demo file, either drag it to the application icon (or dbl-click it) or delete it (you'll be prompted to unregister it the next time the application is run).
While you can view the demo variables in the demo file in Allocate as soon as you have registered the demo file, they will all appear as "unlicensed". To build a license file:
- Run AllocateBuildLicenseXX.exe application
- Choose the Database Set that you want to build a license for
- Select the demo file, or specific variables you want to license.
- You are presented with a dialog of choices that allow a fine degree of licensing. If you want to license everything:
- Select the "*" for geographies.
- Click the "Select All" button for the geography levels.
- Select "Polygon" checkbox.
- Select "All" checkbox.
- Enter the name of the license file (.alc extension).
The license file is tied to the name (not the contents) of the associated demo file. To register the license file, double click it.
|
Last modified at 8/19/2008 4:18 PM by Ned Harding
|
|
|
|