ETL

UDM metadata feeds all start with UDM_Catalog_*

UDM feeds containing data records start with Value_*. We currently maintain nine Value feeds:

1. Value_Daily for daily data.
2. Value_Weekly for weekly data.
3. Value_Monthly for monthly data.
4. Value_Quarterly for quarterly data.
5. Value_Annual for annual data.
6. Value_Special_CMEDairySpotMarket for detailed, transaction-level CME spot dairy market data.
7. Value_Futures for daily end-of-day futures data.
8. Value_Options for daily end-of-day options data.
9. Value_Special_InternationalTrade_Monthly for international trade data.

The list of all Value Feeds suitable for ETL data pulls is available at UDM_Catalog_ValueFeeds:

The fields in this feed are:

• ValueFeedID (int, not null, primary key)
• ValueFeedName (nvarchar(400), not null)
• ValueFeedDescription (nvarchar(400), not null)
• LastModified (datetime2(6), not null)
• Active (bit, not null)

ValueFeedID is the primary key to this feed. ValueFeedName corresponds to the API endpoint where the value records can be accessed. ValueFeedDescription describes particular features of a value feed, and lists all catalog feeds specialized just for this value feed. For example, we store daily, transaction-level data from CME Dairy Spot Market in Value_Special_CMEDairySpotMarket. The record in UDM_Catalog_ValueFeeds:

https://udmdirect.dairymarkets.com/Universal/UDM_Catalog_ValueFeeds

It will be helpful to examine the typical structure of the value record using Value_Daily as an example.

Value_Daily feed has the following fields:

• RecordID (int, not null, primary key)
• SeriesID (int, not null, foreign key: UDM_Catalog_Series, field: SeriesID)
• AreaID (int, not null, foreign key: UDM_Catalog_Areas, field: AreaID)
• ReportPeriod (date, not null)
• CollectionDate (date, not null)
• High (numeric(18,6), null)
• Low (numeric(18,6), null)
• Value (numeric(18,6), not null)
• SourceID (int, not null, foreign key: UDM_Catalog_Sources, field: SourceID)
• LastModified (datetime2(6), not null)
• Active (bit, not null)

RecordID is the primary key to the table. SeriesID is the foreign key to UDM_Catalog_Series, i.e. it uniquely identifies which time series is the value for. AreaID is the foreign key to UDM_Catalog_Areas, i.e. it uniquely identifies which geographic area is the value for. ReportPeriod is the date for which the value is published. CollectionDate is the date on which the report containing the data point has been published. Value is the numeric value of the record. Where publishers publish only Low – High range rather than a single value, we capture both. Otherwise these fields are null.

To select top ten records from Value_Daily, we call the URL:

http://udmdirect.dairymarkets.com/<your company feed name>/Value_Daily$top=10

Consider the following example:

From UDM_Catalog_Series, we can identify Series # 3357 as Barrows and Gilts, Swine or Pork Market Formula, Carcass Base Price. From UDM_Catalog_Units we can identify units as $/cwt. From UDM_Catalog_Areas, we can identify Area # 1 as United States. From UDM_Catalog_Sources, we can establish that  Source # 188 corresponds to URL https://mpr.datamart.ams.usda.gov/menu.do?path=\Products\Hogs and linking to UDM_Catalog_Reports and UDM_Catalog_Publishers we can identify that this value was published by United States Department of Agriculture, Agricultural Marketing Service, Livestock, Poultry and Seed Program, Livestock, Poultry and Grain Market News Division in the report LM_HG201 National Daily Direct Hog Prior Day – Purchased Swine.