Sum does not work

Question

Currenlty, two measurements are excuted. One is "district count" and the other is "Sum."

Issue here is that "Sum" does not give output when checking with "Browser" within Analysis Service.

Yet "Data Source View" does show raw data. Futhermore, when checked with "drill through" aggregated values were present.

When some restrictions were applied to values drived from "Data Source View," no values were output after processiing with "Sum".

Please note when  no restrictions were applied to "Data Source View", and before the structure of Integration Services changed,   "Sum" did work and gave desired output.

So I tried  to apply no restriction to values drived from "Data Source View", but  "Sum" does not output any value.

Could you tell me why so?

OS: Windows Server 2008 R2
DB:SQL Server 2008 R2

Answer 1

My dimensions and facts of DWH are as following.

Dimension
DimTime:TimeKey(PK),TimeAppKey,Year,Month,Day,Hour,Minutes

DimProducts:ProductsKey(PK), ProductsName, StartDateTime, EndDateTime

DimOrginazation:OrganizaionKey(PK),OrganizaionName, StartDateTime, EndDateTime

DimUser:UserKey(PK), ProductsKey(FK1), OrganizationKey(FK2), MemberID, Last Name, FirstName, StartDateTime, EndDateTime

Facts
FactsCountProducts
TimeKey(PK), ProductsKey(PK), UserKey(PK), MemberID(→Distinct Count)

FactsTotalTime
TimeKey(PK), ProductsKey(PK), UserKey(PK), TotalTime(→Sum)

The Isuue is that "TotalTime" does not show output when processing with sum.
TotalTime is decimal value.
When I applied "distinct count" to "TotalTime", the results of it came up.

Although I thought whether this problem would be related to this bug(http://support.microsoft.com/kb/957814),  my environment is SQL SERVER 2008 R2 and differs from the environment of it.

Is there any method of something avoiding?

Answer 2

Do you have a single measure group with two different measures? One defined with a Distinct Count aggregation function and the other defined as Sum? That would be a design problem that I didn't realize SSAS still allowed you to do. A Distinct Count measure should be isolated in it's own measure group. There's a whitepaper that you may want to consult that discusses optimizing Distinct Count measures. http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=891

HTH, Martin

---

http://martinmason.wordpress.com

Answer 3

Martin,
Thanks for your reply.

No, I have two diffrent meaure group.
I didn't know about "whitepaper", so I would take a look at it.

---

Answer 4

Hi HHWWA,

"Issue here is that "Sum" does not give output when checking with "Browser" wthin Analysis Service. " -

Seem to be empy values in browser. You can right on the browser and select "Show Empty Cells" to verify it. Does the sum aggregation have effective values in underlying datasource? If yes, check the relationship of dimension and fact table in Datasource view and their dimension relationship in Dimension Usage of cube designer. If these relationships are correct, check relative options of the aggregation Properties.

For futher troubleshooting, provide more information mentioned above here.

Thanks,
Jerry

Answer 5

Jerry,
Thanks for your reply.
1) You can right on the browser and select "Show Empty Cells" to verify it.
I tried to select "Show Empty Cells", but the value of the sum aggregation was still empty.

2) Does the sum aggregation have effective values in underlying datasource?
No, it does not.

3) If these relationships are correct, check relative options of the aggregation Properties.
I checked that the relation was correct. Could you tell me what is the relative options?

Regards,
HHWWA