Data warehouse split dimension

Question

In our data warehouse we have 6 customer groups. Each group has between 5000 to 10000 customers.

Our star schema:

dimCustomer               fact
-----------               ------------
CustomerKey               CustomerKey
CustomerName              Measure 
CustomerGroupKey
CustomerGroup

We often query for customer group only. Can I add an additional dimension:

    dimCustomer               fact                  dimCustomerGroup
    -----------               ------------          --------------
    CustomerKey               CustomerKey           CustomerGroupKey
    CustomerName              CustomerGroupKey      CustomerGroup
    CustomerGroupKey          Measure
    CustomerGroup

Is this possible in Microsoft SQL Server and also in SSAS cubes?

Answer 1

I would add the customergroup as an attribute and hierarchy on the customer dimension as it's clearly a property of the customer.

If you add some attribute relationships inside the dimension it should be helping your performance.
If you create it as a new dimension the query processor will really need to execute the nonempty, if it's a dimension attribute with an attribute relation it can use the bitmap index to determine if the results will be empty or not. Since you don't have that many groups it would be extremely beneficial.

As you state it's queried on very often I think this is the way to go.

I don't see what benefit you would get from adding an extra dimension.