How to create a separate measure group for the distinct count
I am adding distinct order count measure to the cube. I have to place it in a separate measure group for better performance When I click on "new measure group" button, I am presented with a dialog box "select a table from the source view" . My Facts_sales table is not on the list. because the table is already used by other measure group "Facts_sales".
How could I create a new measure group for the distinct count measure?
Answer
Just create a new measure in the group referencing the fact table of interest. As long as you select Distinct Count as the aggregation function, the new measure will auto-magically be placed in its own separate measure group.
EDIT: http://msdn.microsoft.com/en-us/library/ms166573.aspx
13.In the Measures pane, right-click the Sales Quotas measure group, and then click New Measure. For more information, see Defining Measures.
The New Measure dialog box opens, containing the available source columns for a measure with a usage type of Sum.
14.In the New Measure dialog box, select Distinct count in the Usage list, verify that SalesQuotas is selected in the Source table list, select EmployeeKey in the Source column list, and then click OK.
> Notice that the measure is created in a new measure group named Sales Quotas 1. Distinct count measures in SQL Server are created in their own measure groups to maximize processing performance.