UDM_Catalog_XRef captures all many-to-many relationships among UDM catalog dimensions. The fields on this feed are:
- XRefID (int, not null, primary key)
- SeriesID (int, not null, foreign key to UDM_Catalog_Series)
- AreaID (int, not null, foreign key to UDM_Catalog_Areas)
- SourceID (int, not null, foreign key to UDM_Catalog_Sources)
- ValueFeedID (int, not null, foreign key to UDM_Catalog_ValueFeeds)
- ReportID (int, not null, foreign key to UDM_Catalog_Reports)
- PublisherID (int, not null, foreign key to UDM_Catalog_Publishers)
- CategoryID (int, not null, foreign key to UDM_Catalog_Categories)
- MeasureID (int, not null, foreign key TO UDM_Catalog_Categories)
For example: http://udmdirect.dairymarkets.com/Universal/UDM_Catalog_XRef?$filter=SeriesID eq 26
What we learn from these two records is that Series Butter, Stocks in All Warehouses (UDM_Catalog_Series) is available only for a single area – United States (UDM_Catalog_Area). It is only available on a single Value feed – Value_Monthly (UDM_Catalog_ValueFeeds). It is published by United States Department of Agriculture, National Agricultural Statistics Service (UDM_Catalog_Publishers) in their report Cold Storage (UDM_Catalog_Reports).This series belongs to category Butter (UDM_Catalog_Categories), and measure Stocks (UDM_Catalog_Measures).
To extract information on unique pairs, e.g. Series – Areas pairs, the suggested approach is to first import the entire UDM_Catalog_XRef feed into a single table, then use:
SELECT DISTINCT SeriesID, AreaID FROM UDM.[Catalog.XRef].