Same Dimensions, multiple Fact tables, different measures, millions of rows

Given your requirements, there are a few potential approaches to consider.

Option 1: Create a star schema with both fact tables

Creating a star schema with both fact tables and shared dimensions is a valid approach. You should connect the fact tables to each dimension, creating a bridge table where necessary. This approach can be more flexible than joining the fact tables together, allowing you to analyze each fact table separately and combine them as needed.

You could also consider partitioning the fact tables by date to improve query performance. This would allow you to query only the relevant partitions for a given time period, rather than scanning the entire table.

Option 2: Join the fact tables into a single table

Joining the fact tables together into a single table is also a valid approach, but it may not be the most efficient option for your data volume. Combining the tables will result in a larger table, which can be slower to query and consume more memory.

If you choose to join the tables, you should carefully consider the join conditions and ensure that the resulting table is still at the appropriate level of granularity for your analysis.

Recommendation

Given the large data volume you're dealing with, we would recommend creating a star schema with both fact tables and shared dimensions. This approach allows you to analyze each fact table separately and combine them as needed. You should also consider partitioning the fact tables by date to improve query performance.

You should test both approaches on a small subset of your data to evaluate performance and ensure that the resulting schema meets your reporting needs. Additionally, you should work with a data modeling expert to ensure that the schema is properly designed and optimized for your specific requirements.