I have SQL Server 2016 environment and I have decided to design a source table with holds some sort of event data as a transactional fact table. The table has about a million rows, and generates about another million per year, for now. One of the columns in the source table is VARCHAR(MAX) and almost all of the values in this column are unique. My questions here are:
- Is the best approach here to store this table as fact table in the first place? Could it be modeled as a dim table?
- Should I store the column as it is in the fact table?
- Should I try to decompose the elements of the XML data (there are about 10 elements) and add them as attributes in a dim table? There could be a problem with this approach if more elements are added into the source table in the future.