• My opinion: At this point, you're just trying to make a less than ideal back-end design work. I've been there a few times, and it really doesn't matter how you decide to jump through that hoop.

    Whether you create a derived table in the DSV, include all the attributes in one dimension or create multiple different dimensions from the same dimension table, the "select distinct..." query executed against your data source is still the same.

    Creating separate dimensions may be easier to navigate from an end-user point of view, and you may benefit a little from the reduced size in data store of that dimension. Aggregates may also help out a little in that scenario, from a performance point of view.

    Ultimately performance will be impacted by the underlying design, but I don't see too many other options. I'd definitely recommend that you test the behavior of the one v. multiple dimensions, but I don't expect it to be too different as the granularity to the fact will still be the same.

    I'd love to be proven wrong, but an inadequate back-end dimensional model will always come back to haunt you in some way...