Factless tables simply mean the key available in the fact that no remedies are available. Factless fact tables are only used to establish relationships between elements of different dimensions. And are also useful for describing events and coverage, meaning tables contain information that nothing has happened. It often represents many-to-many relationships.
The only thing they have is an abbreviated key. They still represent a focal phenomenon that is identified by the combination referenced in the dimension tables.
A Factless Fact Table
There are two types of factless table :
1. Event Tracking Tables –
Use a factless fact table to track events of interest to the organization. For example, attendance at a cultural event can be tracked by creating a fact table that contains the following foreign keys (i.e. links to dimension tables) event identifier speaker/entertainment identifier, participant identifier, event type; Date. This table can then be searched for information, such as the most popular ones. Which cultural program or program type. The following example shows a factless fact table that records each time a student attends a course or which class has the maximum attendance? Or what is the average number of attendance of a given course? All questions are based on COUNT () with group BY questions. So we can first count and then implement other aggregate functions like Aggress, Max, Min.
Attendance fact
2. Coverage Tables –
The second type of factless fact table is called a coverage table by Ralph. It is used to support negative analysis reports. For example, to create a report that a store did not sell a product for a certain period of time, you should have a fact table to capture all possible combinations. Then you can find out what is missing.
Common examples of factless fact table:
Ex-Visitors to the office.
List of people for the web click.
Tracking student attendance or registration events.
Update to Dimension Table :
- Every day, more and more sales take place, so more and more rows are added to the fact table.
- Updating due to the change in fact table happens very rarely.
- Dimension tables are more stable as compared to the fact tables.
- Dimension table changes due to the change in attributes themselves, but not because of an increase in the number of rows.
Slowly Changing Dimensions :
- Dimensions are generally constant over time, but if not constant, then they change slowly. The customer ID of the record remains the same but the marital status or location of the customer may change over time.
- In the OLTP system, whenever such a change in attribute values happens, the old values replace the new values by overwriting the old ones.
- But in a data warehouse, overwriting of attributes is not the solution as historical data for analysis is always required.
- So making such changes in attributes has 3 different types –
- Type 1 Changes
- Type 2 Changes
- Type 3 Changes
Large Dimension Tables :
- Large dimension tables are very deep and wide.
- Deep means it has a very large number of rows and wide means it may have many attributes or columns.
- To handle large dimensions, one can take out some mini dimensions from a large dimension as per the interest. These mini-dimensions can be represented in the form of a star schema.
- For example, the above-mentioned order analysis star schema is one of the mini-dimensions of a manufacturing company in which the marketing department of the company is interested.
- Customers and products are generally large in dimensions.
- Large dimensions are generally slow and inefficient due to their size. They tend to have multiple hierarchies to perform various OLAP operations like drill down or roll-up.
Rapidly Changing or Large Slowly Changing Dimensions :
- In type 2 changes, a new row is created with the new value of the changed attribute. This preserves the history or old values of attributes.
- If there is a change again in some attribute, then again a new dimension table row is created within the new value.
- This is feasible if the dimension changes infrequently, like once or twice a year. For example, the product dimension, which has rows in thousands, changes rarely so it is manageable.
- But in the case of customer dimensions, where a number of rows are millions and changes infrequently, then type 2 changes are feasible and not very difficult. If customer dimensions change rapidly, then Type 2 changes are problematic and difficult.
- If the dimension table is rapidly changing and large, then break that dimension table into one or more smaller dimension tables.