Unit conversion between Metric and Imperial (US)

[intapiuser]

Analytical Need 

You have an international user base, some who use the Metric system and others who use the Imperial (US) system. You want to display dashboard values and calculations in the units that the user wants, or perhaps pre-defined based on the user’s location.

Modeling Challenge

There are a few options to fulfill this need, but which one should be used? Three options are described below, each identifying the pros and cons and the implementation steps. The best solution will most likely depend on system resources and your dashboard designers' familiarity with your data models.

Solution

Here is a sample fact table with several measurements using the Metric system:

Original Fact table:

**Whichever option you chose below, you must require a single-select filter of one system on the dashboard. Otherwise it will result in a many-to-many which can consume excessive memory and give incorrect results.

Option A

This first option requires duplicating the records in your fact table(s) so that your records are presented in all the measurement systems you need.

Pros:

• Everything is done upfront in the elasticube, which eliminates the risk of mistakes by dashboard designers

Cons:

• Requires custom SQL table which results in additional calculations and doubles the number of fact records. This could increase the build time and the size of the elasticube significantly if you have a very large data set.

To implement this approach:

1. Create a DIM System table that includes a record for each measurement system. All you need is an ID and description.
   
2. Use a custom SQL table to generate a new fact table, which duplicates your fact records – one for the measurements in each system (Metric and Imperial) with the necessary conversions - and includes a foreign key that will link to the DIM System table
   
   Resulting FACT Table
   
3.  
4. Create a relationship between the two tables on the system ID.

When you filter to a single system from the DIM table, only the fact records linked to that system will be included.

Option B

This option requires adding columns instead of rows on the fact table for each system. This is the approach detailed in the community post Switch Between Metrics.

Pros:

• All conversions are performed in the elasticube.
• For dashboard designers, it should be straightforward to incorporate into all the widget value calculations.

Cons:

• Duplicating columns increases memory consumption, thus it may not be ideal if you have a lot of value columns that need to be converted
• Dashboard designers need to be trained to include these factors in the value calculations

To implement this approach:

1. Create a DIM System table that includes a matrix for each measurement system, and a fake key, needed to join with your fact table.
   
2. On your fact table:
   1. Add custom columns for the converted values. For example, to get your weight in oz:
      
   2. Add the fake key to match with the dimension table
      Resulting Fact Table
      
3.  On the dashboard, all value calculations need to be modified. For example, let’s say you wanted to calculate the total weight. Your formula needs to be:

[Total Weight (g)] * [Max Metric] + [Total Weight (oz)] * [Max Imperial]

As you will be forced to filter to one system, one part of the above equation will equal zero and you will get the correct results. (You could use Min or Avg instead of Max¸ as there is only one value it returns the same result.)

With Metric selected:

76000 * 1 + 2681 * 0 = 76000 (g)

With Imperial selected:

76000 * 0 + 2681 * 1 = 2681 (oz)

Option C

This option only requires additional columns on a small dimension table, instead of duplicating information on the fact table.

Pros:

• No need to duplicate records or to create custom columns on the fact table, so no negative impact on performance or size.

Cons:

• Dashboard designer needs to be trained to use the correct conversion factors in all value calculations. Thus, there is a risk of dashboard displaying incorrect results if the calculations are not set correctly.

1. Create a DIM System table that…
   1. Includes a column for each conversion you need. The value should equal 1 for the base unit system (the system in which the facts are stored). The values for the other system(s) should have the conversion factor.
   2. A fake key, needed to link to the fact table so that all records are retained.
      
2. The only modification needed to the fact table is the fake key that matches the one on the dim table.
   
   Resulting FACT Table (all measurements using base units [Metric])
   
3. Create a relationship between the tables on the fake key.
4. On the dashboard, all value calculations need to include these conversions. Again, let’s say you wanted to calculate the total weight, the formula would be:

[Total Weight] * [Max Weight Conversion]

As you will be forced to filter to one system, the above equation will get the correct results. (You could use Min or Avg instead of Max¸ as there is only one value it returns the same result.)

With Metric selected:

76000 * 1 = 76000 (g)

With Imperial selected:

76000 * .035273962 = 2681 (oz)

Things to note with Option C:

• Temperature conversion requires an extra step as it is not a scalar conversion between Celsius and Fahrenheit. Thus, we have an additional column called Temp Offset. This will allow us to use the full conversion F = 1.8*C + 32 in the dashboard calculations. Let’s say you wanted to calculate the average temperature, the formula would be:
  
  [Average Temp] * [Max Temp Conversion] + [Max Temp Offset]
  
  Metric result: 13.4 * 1 + 0 = 13.4 (C)
  Imperial result: 13.4 * 1.8 + 32 = 56.12 (F)
• Be careful with calculations on values such as area, as you will need to multiply by the correct power of the conversion factor.

Please comment if you implement one of these approaches!