I am designing the ERD for a new data warehouse.
- SQL Server 2016
- Clustered Columnstore Indexes for the "fact" tables
- Loose star schema
I put "fact" in quotes and say "loose star schema" because, with clustered columnstore, I can put many dimensions right into the "fact" table without the typical concerns over row width. Many of my dimensions are going into the "fact" tables. I am creating some dimension tables and surrogate keys, but only if the dim has attributes in addition to the dim description itself.
This brings me to some very wide, high cardinality, fields that are
nvarchar(max). Without going too in-depth, think of these fields as denormalized lists. I need the list denormalized for the grain of one of my data sources. I do have it normalized in another fact table, but not one I am surfacing in this data source.
Users need these fields to search for keywords in the data mart I am surfacing. In my current design they are in the clustered columnstore "fact" table. Users will frequently query the fact tables without touching the
Is there a place to put wide dimensions into my data warehouse that is more correct than the clustered columnstore table?
We may add other languages in the future. Currently and for the foreseeable term, the
nvarchar column contains just English.