ETL

https://data.commoditymarkets.com/<Your Company Feed Name>/Value_Annual?$filter=LastModified gt 2019-05-27T23:59:59.99Z&$format=json

• RecordID is the primary key to the table.
• ProductID is the obsolete name for the field SeriesID. It will be removed after January 1, 2020
• 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. All data in the Value.Annual table has ReportPeriod in the format YYYY-01-01, where YYYY indicates the year for which the data 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.
• SourceID is the foreign key to UDM_Catalog_Sources. Since each SourceID belongs to only one ReportID, this field determines to which report the record belongs.

CREATE TABLE [Value].[Annual](
[RecordID] [int] NOT NULL,
[SeriesID] [int] NOT NULL,
[AreaID] [int] NOT NULL,
[ReportPeriod] [date] NOT NULL,
[Value] [numeric](18, 6) NOT NULL,
[CollectionDate] [date] NOT NULL,
[SourceID] [int] NOT NULL,
[LastModified] [datetime2](6) NOT NULL,
[Active] INT NOT NULL, 
CONSTRAINT [PK_Value_Annual] PRIMARY KEY CLUSTERED 
(
[RecordID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON),
CONSTRAINT [Unique_Value_Annual] UNIQUE NONCLUSTERED 
(
[ProductID] ASC,
[AreaID] ASC,
[ReportPeriod] ASC,
[CollectionDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

ALTER TABLE [Value].[Annual] ADD CONSTRAINT [FK_AreaID_Value_Annual] FOREIGN KEY([AreaID])
REFERENCES [UDM].[Catalog.Areas] ([AreaID])
GO

ALTER TABLE [Value].[Annual] ADD CONSTRAINT [FK_ProductID_Value_Annual] FOREIGN KEY([ProductID])
REFERENCES [UDM].[Catalog.Series] ([SeriesID])
GO

ALTER TABLE [Value].[Annual] ADD CONSTRAINT [FK_SourceID_Value_Annual] FOREIGN KEY([SourceID])
REFERENCES [UDM].[Catalog.Sources] ([SourceID])
GO