Custom data setup tool

Overview

Custom Data Setup is often used for customer or store layers but can be used for any layer including point and polygon layers. An example of use is setting up your sales data for analysis after joining it to a geography polygon using spatial overlay. You could also use your own demographic data, estimates, projections, business statistics, or consumer expenditure information. Any variable in your boundary layer or your joined data will be available for recalculation as a new variable in this tool.

There are many different ways to generate a BDS layer using the Custom Data Setup wizard. The key to using this tool successfully is having a good understanding of what you expect from the BDS layer. Here are some key questions:

• Am I going to run reports on this BDS layer?
• Am I only doing data dumps (use the Append Data Analysis tool)?
• Am I creating a BDS layer from scratch, and if so, do I have the appropriate base geography layer?
• Am I going to incorporate my own data or do I want to create my variables in the BDS layer or in a report?
• Am I going to create a BDS layer using summarized point data (such as point of sales data from customers) and join that data to a base geography layer?

Input Requirements

This tool requires that you have an existing point or polygon shapefile with all your data associated to it or a base shapefile (such as ZIP Codes) that you can append your custom data to.

Using the wizard

A powerful option in this wizard is the ability to create custom variables from your own data or recalculate any existing variables of Business Analyst. Please note that this is only recommended for variables that are summed up such as Total Population. Recalculating variables, such as median, require in-depth knowledge of the calculations of demographic formulas.

Recalculating variables, such as median, require in-depth knowledge of how these demographic formulas are calculated in Business Analyst. All demographic data layers provided with Business Analyst are preset for your use and are read only so the settings are not inadvertently changed. If you try changing the variable settings in these layers without changing the read only setting, you will receive an error identifying a failure to write the metadata (.xml) change. Advanced users who want to change the default settings of these Business Analyst layers can do so.

Note:

When creating a BDS layer using your own custom data, it is recommended to save your custom data in the same directory as the BDS layer you will create. This is critical because this operation doesn't permanently join your custom data to the boundary layer. Moving these files will break the link making the file unavailable in Business Analyst.

To create a BDS layer using your own data follow these steps:

1. Select Create BDS Layer by importing your own data and click Next.
2. Select the Business Analyst Base Geography layer that corresponds to the data you want to build your BDS layer on. For example, if you want to create a new BDS layer with sales at the ZIP Code level then select ZIP Codes as the Base Geography layer and choose ID as the join ID of your base geography layer.

   Note:

   This ID should match the appropriate ID in the data you want to join.

3. Click Add. You can choose from these options to add your custom data.
   • Option 1: Select the layer or table that will be added to the BDS layer. This option creates a BDS layer using custom data stored in a database. With this option, make sure your dataset has a geographic ID that can be used to join it to the base geography layer selected in the previous step.
   • Option 2: Select a point layer to summarize. This option creates a BDS layer using point based data such as point of sales data stored in a point shapefile. This option will spatially join your data in the selected point layer to the base geography layer selected in the previous step. This is recommended only for data that you can sum up such as sales and is not recommended for pre-calculated variables such as medians.

   Note:

   It is recommended to save your BDS layer in the same directory as any custom data you add. This is critical because this operation doesn't permanently join your custom data to the boundary layer. Moving these files will break the link which makes the file unavailable in Business Analyst.

4. Once your data is selected, click Next.
5. Select the desired variables to build your new BDS layer on. You can select any combination of variables from your custom data and/or data contained in the Business Analyst datasets.

   Note:

   When you select Business Analyst variables, you may be prompted to add additional variables that are required to calculate these variables.

   

   • The Apportionment Method determines how a variable is apportioned to a portion of geography. When an analysis cuts across a geography, the analysis includes only the value of the variable that falls inside the analysis area. Depending on the variable, you can choose apportioning by area or one of the three primary demographics at the block point level (Population, Households, or Housing Units). For any standard Esri variable selected, the Apportionment Method is automatically selected. When selecting your own data, be sure to change the Apportionment Method to reflect the source of your data. For example, if your variable was Sales by ZIP Code and was based on household data, select Households 2010.
   • Available Variables displays all available variables including custom variables.
   • Import Custom Data Parameters allows advanced users who build their own datasets to import data that is stored in the form of metadata easily.

   Option	Description
   Variable	Variable stub name with maximum of 10 characters. This field contains unique values.
   Category	The name identifying the group that the variable belongs to (i.e. 2010 Average Household Income is in the "2010 Income" category).
   Variable Description	32 character variable description used to display data in Business Analyst.
   Alternate Variable Description	Alternate 32 character variable description used to display data in Business Analyst. Can be used for second languages in the application.
   Long Description	Detailed description of the variable.
   Aggregation	Indicates how to aggregate data for custom areas. Values can be equal to SUM or CALC. SUM=summary item that can be added (Total Population); CALC=must be calculated (Average Household Income).
   Apportionment Method	For block-to-block group apportionment. Values can be equal to POPWGT_CY, HHWGT_CY, HUWGT_CY, BUSWGT_CY. Use this variable for weighting when creating custom areas such as Population Age 0-4 uses a population weight "POPWGT_CY"; Agriculture Businesses uses a business weight "BUSWGT_CY".
   Used Fields	List of variables required in the associated formula. Use the 10 character variable name. Required if a formula is available.
   Formula	Formula used for calculating custom areas. This is only required for variables with aggregation equal to CALC.
   Weight	Weight the variable based on another variable.
   Decimals	Number of decimals to display.
   Format	Used for formatting the display of data on reports. Values can be equal to COUNT, CURRENCY, PCT, TEXT. Use COUNT to display data without decimals. Use CURRENCY to display data with currency symbol and two decimals. Use PCT to display data with one decimal and percent symbol. Use TEXT to display as written.
   Percent Base	Denominator for percent calculation.
   Index Indicator	Indicates whether a variable can be indexed. Values can be TRUE or FALSE.
   Index Base	Base area values in calculation of the index of the variable. Use the 10 character variable name.

   Advanced users can access additional variable options by right-clicking in the right box and selecting Advanced Mode. These are the three advanced categories:

   

   • Category - Text description that determines how the variables are displayed in variable lists in the Business Analyst wizards.
   • Weight - Weight the variable based on another variable; that is in the block group layer, you could choose to weight consumer expenditures on furniture by Total Households. This provides you with the amount spent per household on furniture in a trade area.
   • Aggregation method - Combines variable values. The options are to sum the value; average the values; use the min value; use the max value; or calculate the median, standard deviation, or variance.

1. Create a new calculated field.

   For example, you can create a population density field by taking the total population and dividing it by area. Be sure that all variables needed to calculate the new field are in the selected fields list in the previous dialog box. When creating custom formulas in this wizard, you must use the VBScript functions.

   The create new variable options allow you to create, edit and import custom variables using the respective buttons in the Create New Variable dialog box below.

   

2. Create your new calculations using the standard Field calculator operations or using VBScripts.

   

Below are two calculated variables samples for creating a BDS layer:

• The first example simply adds two population cohorts together:

  ReturnValue ( GetValue("POPU5_CY") + GetValue("POP5_CY") )

• The second example is a bit more complicated and calculates a penetration rate. This is a simple function that protects against a division by zero error:

  Dim hh
  hh = GetValue("TOTHH_CY")
  Dim sales
  sales = GetValue("SALES")
  if (hh > 0) then
  ReturnValue ( 100 * (sales / hh) )
  else
  ReturnValue(0)
  end if

Feedback on this topic?