SAP HANA: How to Expand Time Series Data Frequencies

[Former Member]

Hi All,

I have a project with time series datasets that have different frequencies. Some tables have data that is only available monthly and most daily. I will like to ask if there is anyway that I can expand the time series data that is in monthly frequency into daily frequency.

All contributions are greatly appreciated.

Regards

Kayode

[Former Member]

Hi Kayode,

This answer would probably be too late for your question. You can actually do that, below example would show you a glimpse of how it can be done using SERIES_DISAGGREGATE function.

You have a table where the data is stored in monthly basis :

CREATE COLUMN TABLE sourceseries(id  INT, ts TIMESTAMP, val DECIMAL(8,2))

    SERIES(SERIES KEY(id ) EQUIDISTANT INCREMENT BY INTERVAL 1 MONTH

        MINVALUE '1999-01-01'

        MAXVALUE '2003-01-01'

        PERIOD FOR SERIES (ts));

Now the data has to be store in target table which is of daily basis :

CREATE COLUMN TABLE targetseries(id  INT, ts TIMESTAMP, val DECIMAL(8,2))

    SERIES(SERIES KEY(id ) EQUIDISTANT INCREMENT BY INTERVAL 1 DAY

        MINVALUE '1999-01-01'

        MAXVALUE '2003-01-01'

        PERIOD FOR SERIES (ts));

Assuming the data in the sorceseries table is as below :

INSERT INTO sourceseries values (1, '1999-01-01', '18.67');

It would be dis-aggregated on daily basis using below query and can be inserted to target series using SERIES_DISAGGREGATE function.

INSERT INTO targetseries(id , ts, val)

    SELECT id, GENERATED_PERIOD_START AS ts, val * FRACTION_OF_SOURCE_PERIOD AS val

    FROM SERIES_DISAGGREGATE(

        SERIES TABLE sourceseries, SERIES TABLE targetseries)

    SD JOIN sourceseries S ON source_period_start = ts ORDER BY id, ts;

Regards

-Anil