Fact Table

A fact table is a table that contains all the facts, or the business information used in the dimensional model as a central table in a star schema of a data warehouse. Fact table includes fields that outline Facts such as measurements and metrics used for analysis and reporting activities. There can be more than one fact table, depending on the type of data warehouse design.

Let’s understand the types of Fact tables –

1. Transaction Fact table– represents a basic view of any business operation or event occurring at an instantaneous point in time. The transaction fact table loads a row for the occurrence of each event. For example – There must be multiple rows for each customer and product because every product and customer is involved in more than one transaction.
2. Snapshot Fact tables– represent non-additive or semi-additive facts that describe the state of things at a particular point in time. For example, if you want to know the performance of a salesman for a specific period like the previous month. Then with a periodic snapshot, you can take a snapshot of the salesman’s activity at the end of each month.
3. Accumulated Fact table– represents all the activities from the beginning to the end of a well-defined process. For example, An order goes through a series of steps such as order placement, shipping, in-transit, and delivery. The fact table associated with the order gets updated with the completion of each step.

To know a 360-degree view of customer data, you understand the customer’s journey through the entire sales cycle. Since the customer data lives in multiple fact tables, analyzing it becomes challenging. You need to write complex SQL queries that include JOIN on multiple fact tables, which multiply the number of records and result in many-to-many relationships. This, in turn, causes a data explosion.

How does Kyvos Prevents Data Explosion and Handle Complex Data Model?

Kyvos simplifies the deployment of multi-fact physical schemas to deal with complex logical hierarchies. It creates a semantic model directly on source data, and dataset relationships are defined first. Next, the semantic layer simplifies the cube design using an ML-powered recommendation engine that suggests the dimensions, measures, attributes, and hierarchies, based on the querying patterns required to build an optimized OLAP model. Instead of connecting multiple fact tables with each other, Kyvos employs each fact table as a single entity and uses a common dimension table for connecting every fact to its identical key to prevent the multiplication of records during a Join. Once you have built your semantic model, you can query, slice, dice, and drill down through the hierarchy in a consistent manner.

Accurate representation of your business logic is the key to success. Kyvos enables you to transform your complex business requirements into data models and handles the scale, deliver higher performance, and provide actionable insights.