Can a dimension table be a fact table for another data mart? In my project, I have an ER diagram. When I analyze it, a table becomes a dimension table for one data mart and a fact table for another data mart. Is this okay?
There is absolutely no reason why a table cannot be used as a dimension table in one data mart and a fact table in another data mart. In practice we often design the dimension and fact tables so that they have only one function; which means that they are frequently used for only one purpose -- but that is more for our convenience than for anything else. So, what you are suggesting is fine.
In fact, we can go further. There is no reason why the value in a column of a table can't be both a measure and a dimensional member. Think about a value like "Number of Children."
If your analytical process asks for the average number of children that house buyers have (as opposed to people who rent) then you are using it as a measure. If you ask to see the average price paid for a house by those people with:
children respectively, then you are using the number of children to segment the data. So here, 'Price Paid' is the measure and 'Number of Children' is the dimension.
More about dimension and fact tables
- Design recommendations for FACT TABLE and DIMENSIONS
- Referencing a dimension table from a fact table
More about data marts
- Data warehouse appliances go mainstream
- Data mart vs. data warehouse
Dig Deeper on Database management system (DBMS) architecture, design and strategy
Related Q&A from Mark Whitehorn
Here's a guide to primary, super, foreign and candidate keys, what they're used for in relational database management systems and the differences ... Continue Reading
The unstructured data types common in big data systems are often better managed by a NoSQL database than relational software, Mark Whitehorn says. Continue Reading
Expert Mark Whitehorn explains what skills are required for predictive modeling -- and whether business users can do the work of data scientists. Continue Reading