Unit 3: Defining and Configuring Fact Tables


Based on the requirements of metrics, this unit defines and configures two fact tables of Solar_Generation_Fact_Table and Temperature_Radiation_Fact_Table, configure the data of device-level solar generation and the data of ambient temperature and horizontal radiance to fact tables, and associates with dimension tables. The relationship between fact tables and data tables are as follows.


Fact Table

Data Table

Solar_Generation_Fact_Table

solar_device_data.csv

Temperature_Radiation_Fact_Table

weather_sensor_data.xlsx

Step 1: Defining the Fact Table of Solar Generation


  1. Select Fact Tables from the left navigation pane and go to the Fact table page.

  2. Select New in the Fact Table Definition tab, go to the New Fact Table page, and enter the following information:

    • Basic Information

      • Fact Table ID: Enter fact_solarpower

      • Fact Table Name: Enter Solar_Generation_Fact_Table

      • Fact Table Name: Select Data Source Registration

      • Select Data Source

        • Data Source Type: Select MySQL

        • Data Source Name: Select tutorial_solar

      • Description: Enter Generation of solar devices

    • Fact Table Logic Information

      • Associated Dimension

        • Attribute ID: Enter source_key

        • Description: Enter Device ID

        • Data Type: Select STRING

        • Associated Dimension: Select dim_solarplant.guid

      • Fact Attribute

        • Attribute ID: Enter date_time

        • Description: Enter Time field

        • Data Type: Select TIMESTAMP

      • Measurement

        • Measurement 1

          • Attribute ID: Enter dc_power

          • Description: Enter DC

          • Data Type: Select STRING

        • Measurement 2

          • Attribute ID: Enter ac_power

          • Description: Enter AC

          • Data Type: Select STRING

        • Measurement 3

          • Attribute ID: Enter daily_yield

          • Description: Enter Daily generation

          • Data Type: Select STRING

        • Measurement 4

          • Attribute ID: Enter total_yield

          • Description: Enter Generation

          • Data Type: Select STRING


      ../_images/fac_definition.png


  3. Select Publish to save and publish this fact table.

Step 2: Configuring the Fact Table of Solar Generation


  1. Select the Fact Table Configuration tab, select New, and select the fact table ID of fact_solarpower.

    • Data Source Type: Import from data table

    • Table Name: solar_device_data

    • Field Mappings

      • source_key select SOURCE_KEY as the mapping field

      • date_time select DATE_TIME as the mapping field

      • dc_power select DC_POWER as the mapping field

      • ac_power select AC_POWER as the mapping field

      • daily_yield select DAILY_YIELD as the mapping field

      • total_yield select TOTAL_YIELD as the mapping field

  2. Select Next to preview the configuration.

  3. Select Publish to finish the configuration of this fact table

Step 3: Defining the Fact Table of Temperature and Radiation


  1. Select New in the Fact Table Definition tab, go to the New Fact Table page, and enter the following information:

    • Basic Information

      • Fact Table ID: Enter fact_sensor

      • Fact Table Name: Enter Temperature_Radiation_Fact_Table

      • Data Connection Type: Select Data Source Registration

      • Select Data Source

        • Data Source Type: Select MySQL

        • Data Source Name: Select tutorial_solar

      • Description: Enter Data of temperature and radiation

    • Fact Table Logic Information

      • Associated Dimension

        • Attribute ID: Enter source_key

        • Description: Enter Sensor ID

        • Data Type: Select STRING

        • Associated Dimension: Select dim_sensor.guid

      • Fact Attribute

        • Attribute ID: Enter date_time

        • Description: Enter Time field

        • Data Type: Select TIMESTAMP

      • Measurement

        • Measurement 1

          • Attribute ID: Enter ambient_temperature

          • Description: Enter Ambient temperature

          • Data Type: Select STRING

        • Measurement 2

          • Attribute ID: Enter module_temperature

          • Description: Enter Module temperature

          • Data Type: Select STRING

        • Measurement 3

          • Attribute ID: Enter irradiation

          • Description: Enter Radiation

          • Data Type: Select STRING

  2. Select Publish to save and publish this fact table.

Step 4: Configuring the Fact Table of Temperature and Radiation


  1. Select the Fact Table Configuration tab, select New, and select the fact table ID of fact_sensor.

    • Data Source Type: Select Import from data table

    • Table Name: Select weather_sensor_data

    • Field Mappings

      • source_key select SOURCE_KEY as the mapping field

      • date_time select DATE_TIME as the mapping field

      • ambient_temperature select AMBIENT_TEMPERATURE as the mapping field

      • module_temperature select MODULE_TEMPERATURE as the mapping field

      • irradiation select IRRADIATION as the mapping field

  2. Select Next to preview the configuration.

  3. Select Publish to finish the configuration of this fact table