Forum Discussion
Fact and Dim tables support
Hi everyone,
I have a quick question regarding a table I have, which I am not sure if it has a fact per se or not, and how to tackle getting it ready. Below you have the columns the table has.
Its pretty mach a table about bathing locations which has.
- Name of swimiming spot
- Water Source (same water source can be used by different spots)
- Town
- Link to a water quality report
- Longitude
- Latitude
- Water Quality (excelent, good, aproved)
This is the RAW table, being such a simple table, is it worth creating Dimensions and Fact tables for it on an ETL before using it for analysis?
Would water quality be considered the fact in a table of this kind?
If so which approach would you take for dimensions?
- Multiple Dimensions
- DIM_SPOT (name, report,longitude, latitude)
- DIM_Water source
- DIM_Town
- Fact_Water Quality (which has the dimensions and the quality)
- Single Water Spot Dimension where we have the water source and town dimension thrown in as attributes.
A hand sorting this out would be super helpful.
Kind regards
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.
2 Replies
rohit1991Super User
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.
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
