I'm working on a dataset that's going to be a master shared dataset for asset data - it's basically intended to be a dimension used by analysts for reports using other sources for facts (usage hours, job costs, etc). There will be a different datasource for the transactions involving the asset. But there will also be significant reporting on just the assets themselves.
Our source data has an asset number and then dozens of attributes (>80). A few (maybe 5-8) of these attributes are dollar figures that might become measures for some users, but are really just a bit of information for the asset (original cost, YTD depreciation, age, etc). There are then a few others that are key/desc pairs (key 1, key1Desc, key2, key2Desc, etc). The rest are a bunch of values that are unique to that asset or don't really have a key/desc pattern (serial #, manufacturer, description, key dates, etc).
How much should we split out to separate dimensions that will almost all be 1-2 column dimension? "Dimension all the things" just seems like overkill when there are so many fields that are random pieces of data and most don't cluster together. I don't think the users of the dataset would appreciate having these attributes scattered across 40+ dimension tables.
On a side note, many of our users are excel-oriented and the first thing they often do with quasi-dimensional model is add a bunch of DAX calculated tables and columns to pull everything they want for reporting into one big table. So efforts to create dimensional models are often wasted. We have this same pattern on several other data domains, so I'm looking for some guidelines on how to approach this kind of data in a way that's efficient for Power BI and useful to users who think in Excel.
Thank you!
