You might be wondering which distribution model you should use. The answer has to do with the table category to which the table you are creating belongs; see Table 2.3.
TABLE 2.3 Table category distribution matrix
| Category | Distribution model |
| Staging | ROUND_ROBIN |
| Fact | HASH |
| Dimension (small table) | REPLICATED |
| Dimension (large table) | HASH |
STAGING TABLE
A staging table can also be referred to as an integration table. The purpose or use for this kind of table is in the name itself. This is an intermediate table where you store data that won’t be used for production purposes. Perhaps you store some brainwave readings from a different BCI in a staging table. You can then run some transformation queries on the table so that it gets into a desired format. A desired format would be structured in a way that the same existing intelligence gathering logic can be used on data coming from multiple BCIs after transformation.
FACT TABLE
There has long been the concept of a transactional database. This database is one that is used to accept incoming INSERT, UPDATE, and DELETE statements. It did not take long to realize that performing reporting or data analysis on the transactional database caused resource constraints and the latent processing of transactions. The reporting and analysis queries took a long time to render results as well, due to the live transactions. You do not want your analysis of data to impact the gathering and storage of data. This is the purpose of a fact table. A fact table is one that is loaded into an Azure Synapse Analytics dedicated SQL pool with a copy of data from a transactional database. Once the data is loaded, that is the place where your intelligence gathering takes place.
Online transactional processing (OLTP) is a transactional database that captures and maintains data in real time. Online analytical processing (OLAP) is a database that stores historical data for analysis and intelligence gathering. Hybrid transaction/analytical processing (HTAP) is a mixture of both OLTP and OLAP.
DIMENSION TABLE
A dimension table is one that contains data that doesn’t change very often. Suppose you have a fact table that is very large and you want to reduce its size. You could remove parts of the data that do not change much and place that data into a dimension table. For example, the type of electrodes used to capture brainwaves will not change often (i.e., AF3, AF4, T7, T8, or Pz); therefore, you could remove that information from the fact table and store it in a dimension table. The same goes for the frequencies (i.e., THETA, ALPHA, BETA_L, BETA_H, and GAMMA). Pulling this information out of the fact table would also reduce the fact table size. The information between the dimension and fact tables would be maintained using an ID and combined using a JOIN statement. You executed a JOIN operation in Exercise 2.1, and there is more on this operation later in this chapter.
