Attachment_3_Current_NASS_Quick_Stats_Database_Model.pdf

III. Attachment 3

Appendix A. Current NASS Quick Stats Database Model This appendix describes the current data model used by the existing NASS Quick Stats database. This model may or may not be sufficient to support a proposed new user interface. Proposed changes to the model or an alternative model are requested in this RFI.

Aggregate Commodity Metadata

Main Features of the Commodity Metadata Model

The model basically combines and reorganizes the models from previous Quick Stats versions. It introduced the following features:

1. It incorporates a high level commodity hierarchy. Each commodity item is categorized into sector, group and subgroup. The commodity is also broken down into commodity classes (sub‐ classification) when applicable.

2. It breaks down some of the legacy attributes into more detailed ones. For example, the current commodity code may include information about not only about the commodity, but also production, marketing or utilization practices. In the new model, this information is detached into five different attributes; commodity, class (commodity sub‐classification), production, marketing and utilization practices.

3. It establishes more strict rules on how to use attributes to ensure consistency. For example, attribute values such as “irrigated”, “on‐feed”, etc. are always placed under production practices.

Using the new model, each commodity item is defined in terms of individual attributes. Table 1 below demonstrates how “harvested irrigated acres of corn for grain” is defined as a commodity item. At a minimum, a commodity item must be defined in terms of sector, group, commodity, statistic category and unit. Each attribute has two components: a description and an associated code. The descriptions, not the codes, are the most important part of the model because that is all an average user should need to query data. Notice in the example that the non‐applicable attributes have zero‐filled codes.

Table 1: Metadata for harvested irrigated acres of corn for grain.

ATTRIBUTE DESCRIPTION CODE

SECTOR Crops 01

GROUP Field crops 11

SUBGROUP 00

COMMODITY Corn 063

CLASS 0000

STATISTIC CATEGORY Harvested 077

UNIT Acres 001

PRODUCTION PRACTICE Irrigated 002

MARKETING PRACTICE 000

UTILIZATION PRACTICE Grain 051

DOMAIN 0000

DOMAIN CATEGORY 00000

After the individual attributes have been defined, the corresponding codes are then concatenated to create a unique identifier for the entire commodity item, namely the commodity ID. The commodity ID corresponding to the table 1 example is:

Commodity ID = “01‐11‐00‐063‐0000‐077‐001‐002‐000‐051‐0000‐00000”

This ID might look a bit intimidating, but because the model was designed to make use of individual attributes to query and process data, it will be highly unlikely that a user would ever have to manually key the commodity ID anywhere.

Lastly, the attribute descriptions are concatenated programmatically to create a unique (long) description for the item. This is a more complex concatenation than the one done with the codes because we need to optimize readability. This automated description creation is important because it ensures that they are created consistently across all commodity items. The long description corresponding to the table 1 example is:

Commodity Long Description = “CORN, GRAIN, IRRIGATED ‐ ACRES HARVESTED”

The model provides data analysts in NASS a great deal data querying power. Let’s say for example that we want to query estimates for all harvested irrigated acres, not only for corn, but for ALL field crops.

Using the hierarchy along with the multiple attributes provided, we can define a simple query with the following selection criteria: sector =”CROPS”, group = “FIELD CROPS”, production practice = “IRRIGATED” and statistic category = “HARVESTED”. As a point of comparison, if we were going to build a comparable query using the varname model currently used for reporter level data, in most cases we would have to individually select every single varname from a long list, which can be a cumbersome.

In addition to more querying power, we also gain analytical power. Because a commodity item is defined in terms of multiple attributes, we are not limited to using the complete long description. All attributes are available individually for querying or grouping. For example, if we query acreage, yield and production data for multiple field crops, we can pivot and present the results in tabular form as shown in figure 1:

Figure 1: Commodity attribute tabulation example.

And this is just one of many possibilities. Again, as a point of comparison, the tabulation options with varnames are limited. In fact, we could only do something like that shown in figure 2.

Figure 2: Varname tabulation example.

Although the model was designed to describe agricultural statistics (e.g. corn for grain harvested, cattle inventory), it could be adapted to describe administrative survey data such as response rates and interview time for example.

Mapping varnames to commodity items

For an analyst to be able to query estimates along with their corresponding indications, we must create varname‐to‐commodity relationships and store them in a database as shown in table 2.

Table 2: Estimate and indications relationship (one‐to‐many).

1 DE = Direct Expansion, CC = Item‐to‐item ratio, CH = Survey‐to‐survey ratio, CB = Survey‐to‐base ratio 1 Not an SPS summary