How do you handle multiple fact tables in a star schema?

I see three approaches through which we can connect fact tables Through Common Dimensions - Common dimensions can support joining fact tables Relationship - Joining two fact tables based on multiple conditions to ensure we get 1 to many, 1 to 1 relation similar to what we do when joining Fact with particular dimensions, Join based on a common primary key in both fact tables along with an additional column E.g A.Customer_key = B.Customer_Key and A.Date_Key = B.Date_Key Base Locking - Another approach would be to lock base from Fact A and then join that based locked table with Fact B to extract insights from Fact B

Star , Snowflake schema are the most utilized design pattern in Datawarehousing .Both are helpful in designing Type 2 , Type 3. As your facts increases with dimensions reporting queries gets heavier and costlier. So it is utmost important how we design the schema to handle complex queries. Some points to consider : 1. Design the ER model first considering business inputs . a. Identify the grains and design fact tables accordingly . b. Have bridge tables in between to resolve many to many relations. c. create and utilize shared dimension eg: time , conformed , demographic d. Be specific on how you generate the keys as they can improve query performance 2. Data Load Strategy : How Fact and historical data is loaded with dimension.

Considering that flexibility is important to adapt to changing requirements over time, and that the design depends on specific needs of the business rules, it can be stated that finding the right grain is a crucial step on the design of the model. It is what determines the correct reporting analysis metrics, and if they can be comparable or not, or if they belong to the same context or not. So, there is not a single right rule for determining the grain of a fact table, but a complex process to be followed, from understanding correctly the questions that should be answered to identifying dimensions and level of details.

First understand why we need multiple fact tables. 1. If we have data at different granular level 2. We want to separate our facts which are un related I am not considering type fact table as one of the reason. That's a different discussion. So if you have multiple fact table then you will have confirmed dimensions too to analyse the data. Some time you must might need rollup conformed dimensions to analyse data across the facts.

It’s always good to be mindful if stakeholders are asking for “all the data” just because. Certainly not a technical comment rather a value of your effort should be weighed against the complexity of simply or not so simply including everything. The KISS principle need be taken into account when planning your approach.

The type of fact tabel chosen for the design of the schema affects directly the possibility of these facts to be addictive or not. Transactional fact table capture events at a granular level, at various details and measures, and may not make sense to add them by nature. Periodic snapshots capture the state of a business process at a given point in time, and there may be no inherent addition logic between the values, so they are typically not additive. Accumulating snapshot are used to capture the lifecycle of a business process over time. It is possible, in this case, to use additive aggregation functions across dimensions and time periods, to make calculations that makes sense for the business metrics.

Choosing the right fact table type is like picking the right tool for a job. Transactional tables are great for detailed insights, while snapshots give a bird's-eye view. I've often used accumulating snapshots for processes with clear start and end points. It's all about understanding the business process you're modeling.

Relationships are the glue in a star schema. Conformed dimensions are lifesavers when you want a single source of truth across fact tables. Role-playing dimensions, on the other hand, offer flexibility. I've often used the date dimension in multiple roles – it's versatile and can represent various facets in different contexts

A conformed dimension allows "drill across" queries and will allow you to support as many fact tables as is warranted by your measures. They differ from roleplaying dimensions in that they generally have a single relationship to a fact table, but are related to multiple fact tables. Customer is a solid example of a dimension that you'd want to conform across numerous fact tables. Beware of chasm/fan trap queries though! Each fact table should be queried independently (think CTE or temp table here) and joined after aggregation has occurred.

Confirmed Dimensions Role in Star Schema : A star schema generally refers to a fact with multiple dimensions connecting to it. However, in a larger Data Warehouse project multiple facts are very common, and it's equally common that not all the dimensions would connect to all the facts. Hence to be able to report data from multiple facts it's important to define common dimensions (known as confirmed dimensions).

Query design is where the rubber meets the road. Efficient queries can make or break user experience. Over the years, I've learned that simplicity often leads to performance. Use indexes wisely, avoid over-complicating joins, and always test your queries against real-world data volumes.

The beauty of a well-designed star schema is that it takes away the pain of writing complex SQL queries. So if are writing very complex SQL queries for most of your reporting requirements, it's time to revisit your star design.

I might be an oddball here, but test everything. If you're creating your DWH with code (it doesn't matter whether it's SQL, or Python), always create tests and a test environment, add all edge cases that come to your mind and you'll be set for quite a long time. You just need a smaller dataset and you're good to go. Another thing that I tend to see is that people don't validate datasets on the aggregation level. Suddenly you have a fact table, a data mart that's an aggregate of it, but the data doesn't match. Try some testing frameworks like Great Expectations or if you're running things with dbt - dbt tests, dbt-expectations. Will make your life way easier.

Star schema is not the data creator and you going to get data loaded on a regular basis. So it's important to write validation scripts that are repeatable and can help validate the regular data load/changes. Automation of the validation is key to mange a large data warehouse system.

In today's data-driven landscape, the choice of data warehousing design is not merely a technical decision but one that can have far-reaching implications for business performance, scalability, and agility. Each design – be it star, snowflake, galaxy, or data vault – comes with its own set of trade-offs. Let’s unpack them: Star Schema: Advantage: The star schema is intuitive, with a central fact table surrounded by dimension tables, making it simpler for users to understand and for developers . Disadvantage: Its simplicity may be a double-edged sword. When business needs grow complex – think multiple hierarchies or diverse data sources – the star schema might fall short. Without careful consideration, data redundancy could creep in.

A well designed data model. Understanding grain is vital or you get into a mess. Consider Views to abstract this and test pre joining these tables and represent as a Reporting artefact?

The Role Playing dimensions approach allows different fact tables to have different dimension tables. This can be more flexible and efficient for certain types of datasets, but it can also make the model more complex and difficult to understand. It may end up in creating multiple relationships between fact tables and their respective dimensions tables.