A point of note is that I am not a database expert, just a knowledgeable developer and db user. We are having an internal debate here regarding the value of using dimensions or not for numerical data. Right now we have a hybrid of numerical values being stored directly in the fact table, and the domain of varchar values being stored in dimensions with an id stored in the fact table.
My colleague was arguing that it is easier to have one or the other for the purpose of writing queries that rely on a hierarchy (dimension), or not, but not both. Dimensions for varchar-based data, and storing the numeric data without dimensions in the fact table.
Could it possibly make sense to store numerical values in a separate dimension? For double precision, you would have possibly millions of entries, so are you really saving anything? It seems the execution complexity of joining on a bunch of numerical fields far outweighs any simplicity gained from consistency in writing queries.
What do you think?
p.s. we are using a Vertica database -- a columnar db