Sunday, April 29, 2012 11:51 AM
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
Sunday, April 29, 2012 8:07 PM
My dimensions and facts of DWH are as following.
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
TimeKey(PK), ProductsKey(PK), UserKey(PK), MemberID(→Distinct Count)
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?
Sunday, April 29, 2012 8:45 PM
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
Wednesday, May 02, 2012 11:16 AM
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.
Friday, May 04, 2012 8:48 AMModerator
"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.
Friday, May 04, 2012 12:37 PM
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?
- Edited by HHWWA Friday, May 04, 2012 12:37 PM