I have a Safety Incident reporting app that I am building a cube on. The fact table is the Incident table, one incident per record. We track several aspects of the injury that have a many to one relationship to the fact table. I want these "many" to be dimensions of the cube without counting an incident more than once. One of the items we track is the area of the body that is affected; however multiple body areas should not inflate the number of incidents. So if an incident records an injury to the Hand and elbow, I only want to count one incident, but know that I had an injury to both the hand and elbow. Can anyone point me in the correct direction?

Hi there,

You can do this by putting a distinct count measure on patient. Slicing on
one or more body areas will only count once even if a single incident
involved multiple.

Regards,
Richard

--
This posting is provided 'AS IS' with no warranties, and confers no rights.

"TJSully" <tjsully (AT) bellsouth (DOT) net> wrote

aspects of the injury that have a many to one relationship to the fact
table. I want these "many" to be dimensions of the cube without counting an
incident more than once. One of the items we track is the area of the body
that is affected; however multiple body areas should not inflate the number
of incidents. So if an incident records an injury to the Hand and elbow, I
only want to count one incident, but know that I had an injury to both the
hand and elbow. Can anyone point me in the correct direction?

We had that but it did not work. Actually it was on the Incident ID, key field of the fact table. We expose our cubes on Share point portal using Excel Office Web components and Decision support panel widgets. The Excel Pivot table reports "The query could not be processed: o Formula error - aggregations are not supported for the DISTINCT COUNT measure "Incidents" - in the Aggregate function" and in the DSP widgets we get #value in our measure cells.

If you want to filter by multiple items you won't be able to use the
Distinct Count aggregate. Instead, you'll need to follow the solution
outlined in the following whitepaper.

http://msdn.microsoft.com/library/de.../distinct2.asp

It's a different topic, but the approach will work.

Sean

--
Sean

--
Sean Boon
SQL Server BI Product Unit

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

"TJSully" <tjsully (AT) bellsouth (DOT) net> wrote

Excel Office Web components and Decision support panel widgets. The Excel
Pivot table reports "The query could not be processed: o Formula error -
aggregations are not supported for the DISTINCT COUNT measure "Incidents" -
in the Aggregate function" and in the DSP widgets we get #value in our
measure cells.

BTW, this limitation is gone in Yukon! Aggregating distinct count measures
will work as you'd expect.

--
This posting is provided 'AS IS' with no warranties, and confers no rights.

"Sean Boon [MS]" <seanboon (AT) online (DOT) microsoft.com> wrote