Forum Discussion
Fact and Dim tables support
Hi kaintxu2 ,
Given the nature of your dataset, it seems more like a descriptive or reference dataset rather than a traditional star schema with clear numerical facts. Water Quality could loosely be treated as a fact (a qualitative one), but since it’s categorical and not aggregated over time or across measures, it doesn’t truly behave like a classic fact table metric. That said, if your goal is to scale the model or join it with additional data later (like temporal water quality readings, pollution levels, or inspection events), it's a good idea to plan for a dimensional model early.
In your current setup, the Single Water Spot Dimension approach might be the simplest and most practical it keeps all relevant attributes (spot name, water source, town, coordinates, report link) together, which makes exploration and reporting easy without overcomplicating the model.
However, if Water Source or Town will be reused across other datasets or reports, breaking them out into separate dimensions (DIM_Water Source and DIM_Town) would make the model more flexible and reusable. So, it really depends on future needs if this is a standalone dataset, keep it simple with one dimension.
If it's part of a larger model, consider normalizing into separate dimension tables and treating water quality as a measure in a Fact_WaterQuality table with foreign keys to your dimensions.
Super UserHi kaintxu2 ,
Given the nature of your dataset, it seems more like a descriptive or reference dataset rather than a traditional star schema with clear numerical facts. Water Quality could loosely be treated as a fact (a qualitative one), but since it’s categorical and not aggregated over time or across measures, it doesn’t truly behave like a classic fact table metric. That said, if your goal is to scale the model or join it with additional data later (like temporal water quality readings, pollution levels, or inspection events), it's a good idea to plan for a dimensional model early.
In your current setup, the Single Water Spot Dimension approach might be the simplest and most practical it keeps all relevant attributes (spot name, water source, town, coordinates, report link) together, which makes exploration and reporting easy without overcomplicating the model.
However, if Water Source or Town will be reused across other datasets or reports, breaking them out into separate dimensions (DIM_Water Source and DIM_Town) would make the model more flexible and reusable. So, it really depends on future needs if this is a standalone dataset, keep it simple with one dimension.
If it's part of a larger model, consider normalizing into separate dimension tables and treating water quality as a measure in a Fact_WaterQuality table with foreign keys to your dimensions.
Thanks rohit1991,
Super good answer, there wont be any expanding form this dataset, nor the DIM will be used for anything else, so best keep it simple like I originally thought and you sugested.
I do have one last question. Taking into account that the ammount of data is very small (13 records per year) and this is for a very simple report where we will just do some ocunts of number of water spots, and numer of each water quality, as well as a map with the locations, is it worth modeling, doing a whole ETL and creating new tables in the DWH, instead of just tackling the table as is?
Many thanks,
Javier