A star schema built from the lab data for reporting: CSV files under /learn/data/star/, which Power BI Desktop loads by URL. The database architecture track builds the same star in SQL.
A star puts the numbers to add up in fact tables, one row per event at a fixed grain, and the words to filter and group by in dimension tables around them. fact_records has one row per sample record. fact_species_country has no measure at all: a row says that a species has open records in a country, and counting rows is the analysis (a factless fact).
Both share dim_species and dim_country. A dimension two facts share is conformed: one slicer on species or country filters both. The habitat fact, fact_country_habitat, shares only dim_country; it has no species, and its areas are estimates from a habitat map, not measurements.
Every foreign key in a fact is filled. A record with no year points at dim_year's -1 row, and one in no country at dim_country's ZZ row: an unknown member. So every line is "exactly one" on the dimension's side, and no record falls out of a report because a key was empty.
The diagram
PK primary key, FK foreign key; || exactly one, o{ zero or more, with the crow's foot on the fact that holds the foreign key.
Two facts, fact_records (one row per sample record) and fact_species_country (no measure), share the dimensions dim_species and dim_country; fact_records also points at dim_dataset and dim_year. A third fact, fact_country_habitat (estimated area per country and habitat class), shares dim_country and points at dim_habitat_class. Open the diagram alone to zoom in.
The relationships, in words
Each fact_records row points at exactly one dim_species row; a dim_species row has zero or more fact_records rows: “recorded as”.
Each fact_records row points at exactly one dim_country row; a dim_country row has zero or more fact_records rows: “where”.
Each fact_records row points at exactly one dim_dataset row; a dim_dataset row has zero or more fact_records rows: “published”.
Each fact_records row points at exactly one dim_year row; a dim_year row has zero or more fact_records rows: “when”.
Each fact_species_country row points at exactly one dim_species row; a dim_species row has zero or more fact_species_country rows: “recorded in”.
Each fact_species_country row points at exactly one dim_country row; a dim_country row has zero or more fact_species_country rows: “has records of”.
Each fact_country_habitat row points at exactly one dim_country row; a dim_country row has zero or more fact_country_habitat rows: “estimated to hold”.
Each fact_country_habitat row points at exactly one dim_habitat_class row; a dim_habitat_class row has zero or more fact_country_habitat rows: “estimated in”.
