4

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?

user2374904
  • 41
  • 1
  • 3

1 Answers1

4

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.

Bill Anton
  • 2,920
  • 17
  • 23
  • Magic doesn't happen. It creates distinct meausere in the group, referencing the fact table of interest. With a warning "Break distinct count in a separate measure group". – user2374904 Jun 04 '13 at 03:06
  • 3
    @user2374904 Page 196 of the Wrox professional SSAS 2012 book shows your situation. I haven't got an instance available to play with at the moment but are you sure you didn't add the measure with a default aggregation originally then change it to distinct count in the properties window after the measure was added? – Martin Smith Apr 13 '14 at 20:37