Visual Crossing Weather historical weather data and weather forecast import into any flex.bi cube

Visual Crossing Weather provides API that can be used to get historical weather data and weather forecast data. Using this flex.bi integration it’s possible to import this data and link it together with existing data using any flex.bi data cube. The setup process is described below.

Instructions for using the weather import module

1. To enable the weather import procedure, define the following regular job in the the .toml file

   CODE

   job = "WeatherDataImporter.perform_weather_data_import"

2. In the .toml file, the following parameters should be defined with your own visual crossing api key:

   CODE

   visual_crossing_api_key = "QDGKI65VM23DC9LIF5DYNI03D"

3. In the account plan parameters the following parameters should be defined:

   Mandatory:
   The name of an existing dimension that contains the properties with geographical latitude and longitude coordinates.

   CODE

   weather_data_import_location_dimension = "Some dimension name"

   The names of the properties that contain the geographical latitude and longitude values in format “xxx.xxxxxx“

   CODE

   weather_data_import_lat_long_properties = ["Latitude property name", "Longitude property name"]

   Optional:

   The name of the cube for which the weather data import should be performed. This is mandatory in case if the account contains multiple cubes.

   CODE

   weather_data_import_source_cube = "Some cube name"

   Enable additional logging, including the information about each request made and each line updated or inserted.

   CODE

   debug_weather_data_import = true

4. The structure for weather data import should be created, using field mapping or custom fields, depending on the application type. All of the created fields should be empty.

   The weather data imported is directly controlled by the additional fields and dimensions created. The process is controlled by the column names in the database, not the property, dimension or measure display names, so that the solution is more flexible in terms of the created weather data structure.

   Mandatory columns in the measures table:
   w_d_imported - (data type: integer) - this will be used for marking the lines that have already been processed.

   For other column naming you should use the names of the fields returned my the weather service, for instance, temp, humidity, etc. The full list can be found here: https://www.visualcrossing.com/resources/documentation/weather-api/timeline-weather-api/ under the heading “Response weather data elements”.

   Columns in the measures table, that control what measures is actually imported:
   (if nothing of the following is defined, then nothing is imported)

   - To import historical and forecast weather data, you should create a measure with column name that is formed like this: <returned_element_name>_wh - (data type: decimal)

   Example: to import historical temperature and humidity (temp and humidity elements in the returned data set), you should create 2 new measures with column names temp_wh and humidity_wh.

   - To import statistical weather data, you should create a measure with column name that is formed like this: <returned_element_name>_ws - (data type: decimal)

   Example: to import statistical temperature and humidity (temp and humidity elements in the returned data set), you should create 2 new measures with column names temp_ws and humidity_ws.

   Columns in a dimension table, that control which dimensions are created from weather data.

   With dimensions the logic is similar to the measures.

   - To populate a dimension with weather data and link that dimension to all of your measures, create a new dimension with key field that has column name formed like this: <returned_element_name>_key_wh - (data type: string)

   Example: to create a Temperature dimension, you should define a dimension with a key field column name temp_key_wh.

   - If the dimension key is created from a numeric value, the dimension elements will be sorted in a wrong order, like strings, so for these kinds of dimensions you can define an optional ordinal field (that will be stored as integer and used for dimension element sorting) with a column name formed like this: <returned_element_name>_ordinal_wh - (data type: integer)

   Example: to add an ordinal field to the Temperature dimension, you should define an ordinal field with column name temp_ordinal_wh.

   This is an example of the field mapping done for importing temperature and humidity historical values, statistical values, forecast values and also historical dimensions.
   

The workflow

Each time, the method is ran:

1. Time and location dimension ids that need weather data are selected from measures table.

2. We look for existing weather information in the measures table for the selected ids. If there is existing information, we use that. If not, we request the historical and statistical weather data.

3. The weather dimension information is inserted for all of the rows in the measure table, that have not been processed already, and the appropriate historic and statistical weather measures are imported.

4. The previous forecast measures are deleted from the measures table.

5. Weather forecast of 15 days is requested and the data is imported in the measures table.

6. Statistical data is requested for the next 15 days is requested and the data is imported or updated in the measures table.