1

I am working with factual data that contains over 30+ attributes that can be used as dimension.

These attributes are basically one dimensional -> ID, VALUE, SORTORDER. The source data from the source system also comes from a single table called Reference There are no hierarchies, no additional descriptive attributes. So creating and maintaining 30+ different Dimension table seems to overkill.

Can one for instance create a single Dimension table to contain all these records with a fields that specify the type of record. Then when the data is exposed through a tabular model a view is created for each type of record instead.

enter image description here

LordRofticus
  • 181
  • 1
  • 2
  • 13
  • 1
    Yes, you can. It's no different to having role-playing dimensions where you might have a single Person Dim table and then views on top of it for different roles e.g. Employee, Customer, etc. – NickW Oct 18 '22 at 09:24

1 Answers1

1

Obviously you can do whatever you want. But if your goal is to transform the source data into a dimensional model, then the fact table should have separate columns for each attribute Status, Source, Type, ...

If there is no additional data associated with a, say Status, you wouldn't necessarily create a dimension for it. It's probably a good practice, and may be required by your reporting tool to efficiently generate a list of available values. But having categorical attribute modeled as a column on a fact table without a related dimension table isn't the end of the world.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67