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 nvarchar(max)
fields.
Is there a place to put wide dimensions into my data warehouse that is more correct than the clustered columnstore table?
Joe Obbish informed me that we cannot currently put nvarchar(max)
into CCIs. Would it be the best practice for me to create a table of LOBs as an extension of my "fact" table?
We may add other languages in the future. Currently and for the foreseeable term, the nvarchar
column contains just English.