Managing changes to the data stored on dimension tables over time is an important factor to consider and be handled by designing slowly changing dimensions (SCD). Recognize that the data on the fact table will contain a large amount of data. Making an update to data on that scale is not a prudent approach to manage changes that happen while running a business. Perhaps the model of BCI changes, or either the ELECTRODE name or its LOCATION on your head changes. How would you manage the dimension table data if the AF3 electrode were renamed to AF3i and its location were changed from Front Left to Top Left? Because it might not be feasible to update all the data in the fact table, you could instead keep track of the changes—for example, by adding two columns to the DimELECTRODE table. The first column would store the date when initially inserted, and the second column would store when the data has been modified—something like the following:
+—————+
| DimELECTRODE |
+—————+
| ELECTRODE_ID |
| ELECTRODE |
| LOCATION |
| INSERT_DATE |
| MODIFIED_DATE |
+—————+
You might be wondering what happens when the data in the ELECTRODE and LOCATION data is changed. Do you lose that data? How would you then know that before AF3i and Top Left, existed AF3 and Front Left? This is accomplished by choosing which slowly changing dimension type is required for the given scenario (see Table 3.5).
TABLE 3.5 Slowly changing dimension types
| Type | Description |
| Type 1 SCD | Reflects latest values, overwrites historical data |
| Type 2 SCD | Performs versioning of dimensional data |
| Type 3 SCD | Stores both current and original data in separate columns |
| Type 4 SCD | Creates a new table to store dimensional table data history |
| Type 5 SCD | A combination of Type 1 and 4 |
| Type 6 SCD | A combination of Type 1, 2, and 3 |
The most common SCD types are 1, 2, 3, and 6.
Type 1 SCD
The two tables in Figure 3.15 represent the change in data in a Type 1 SCD scenario. Notice the values on the top table—specifically, ELECTRODE and LOCATION—which are the values that change in the update. Also note the values in the INSERT_DATE and MODIFIED_DATE columns. They are the same, which means there has been no change to these values.
FIGUER 3.15 Type 1 SCD
In the second table, note that the ELECTRODE and LOCATION values have changed, and the MODIFIED_DATE has been updated with a new date. You can see that there is no history of what the value was before the update. If you do not need this history in your scenario, then use this type. In the brainjammer scenario, is it important to know what the electrode was named before it got changed? Not really, from an analytics perspective.