can my dimension tables all have the same primary key?

[emmasd]

I have the below tables:
info: (symbol/ country/ city/ address/ market/ industry...)
balance sheet: (symbol/date/ total debt/ total assets...)
income statement: (symbol/date/ net income...)
statistics:(symbol/ enterprise value/ market cap...)
used technologies; postgresql/talend/power bi
needed: I need to do the database model conception , create a datase( which I did on postgresql and talend) then I need to connect it to power bi and create a dashboard with filters(date/market/industry/symbol)

my questions:
1st scenario: I added IDs to each table on Talend and created a fact table that contains all the other tables' IDs, but that does not work on power bi when it comes to the filters since they do not impact all the tables even when the relationship is active and goes both ways.
2nd scenario: I used the info table as my fact table (without the created tables IDs on talend) and connected all the tables using "symbol", that way all the filters are interacting with all the tables
HOWEVER my issue is I think the second scenario is not correct when it comes to the model conception.
can I keep the 1st scenario and keep the created fact table and add to it the values that I'm goanna need for the filters? will that be correct from a model conception perspective?
or should I only use the symbol to connect everything?
Any other suggestions or examples or resources I can read?

Thank you!

[v-yohua-msft · Accepted solution]

Hi, @emmasd 

In database design, it is often recommended that each dimension table should have a unique primary key. This primary key, usually a surrogate key, is used to track changes in dimension attributes over time. However, it's also common to share natural keys, such as "symbols", between tables. You can check the following link:

Dimension Table: 7 Best Practices | John Levandowski

In the first scenario, you've created a fact table with IDs from all other tables. This approach is common in data warehouses, where fact tables contain measurable quantitative data about the business and dimension tables contain descriptive attributes related to facts. However, if the relationships between tables aren't defined correctly, this model might not work with Power BI. 

In the second scenario, you've used Symbols to join all the tables. This is a more non-normalized approach that works well for reporting and analytics purposes in Power BI. However, it is important to ensure that the "symbols" are unique across all tables in order to work effectively.

Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

As for your question about adding filter values to the fact table, it depends on the nature of those values. If they are attributes of a dimension, it would be more appropriate to keep them in the dimension table. If they are measures or facts, you can add them to the fact table.

The 10 Essential Rules of Dimensional Modeling - Kimball Group

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.