Best Practice Data Model Design with several Fact Tables

[fab_incher]

Hi,

i got some best practice design problems with my Data model, that consists of several fact and dimension tables. There is a screenshot with my current design considerations. I also thought about some possible solutions, but maybe you can help me to decide or give me some additional solutions/thoughts 🙂 

1. Problem: try to avoid Snowflake-Hierachy because Star Schema seems to be best practice generally. In my case I need two identical Columns „Symbol“ in Dim_Symbol and Dim_Options. I need to show Option-Data from e.g. Fact_OptionTransactions also per Stock Symbol (e.g. how many Transactions for „AAP“)

• Solution 1: Build a common table (flatten the table). That means i have to map every Optionname to every Stock Symbol. That means the Symbol Column wouldnt be a 1:n relation anymore, because there are several Optionnames for every Stock Symbol.
• Solution 2: I give a go for the Snowflake Schema. What are the disadvantages?

2. Problem: Some calculated fields are needed as Dimension for filtering or in the legend of a visual. E.g. „Status“ of the trade (closed/open) or a Flag if it was a WinnerTrade. Further more there could be the same Trades (OptionNames) but from different Users.

• Solution 1: Build a new and Separate Dimension Table with every Optionname-User combination and the „Status“ Column (calculated Column or Power Query)
• Solution 2: Build a DAX-Measure „Status“, but are there some restrictions for my needed use cases as filter etc.? 

3. Problem: What is the best way to implement RLS in my case?

• Solution 1: Restrict Fact_OptionTransactions, because this is the only user-related Fact Table (at the Moment)
• Solution 2: Is it possible to restrict the Dimension Table „Dim_User“, that will restrict all related tables too?

Thank you very much!