June 11, 2018 at 11:58 am
Here some background:
I am using vs2010 multidimensional cube solution.
My fact table, FactProductionClaims, consists of contract keys and the claims keys if there were claims, a 0 in the claim key field if there are no claims
From this I measure number of distinct contracts, claims per contract, and a reserve amount which is tied to the contract
Here is my problem, I want to sum up the reserve amount for distinct contracts. What happens now is the reserve amount is being counted for every claim the contract has associated with it.
I have tried:
1) Creating a calculated field in the Calculations tab of Sum(Max([Measure].[Reserve] )
2) I have tried Max([Measures].[Reserve]); this gives me the correct number but when used in a pivot table instead of getting a sum of the reserves I get the max of the reserve
3) I have tried creating a 2nd fact table with just the contract key and the reserve, joined that with the DimContact and FactProductionClaims; this results getting too many rows (I think its a row for each contract even though it is filtered by client and date ranges)
4) I have tried putting the reserve on the DinContract table and summing
Nothing seems to work, so now I come to the experts. What is the correct way to do this?
Thanks
June 11, 2018 at 12:44 pm
You didn't state that explicitly, but it looks like the Reserve amount is in your fact table instead of your Contract dimension. If that is the case, then it's a modelling issue. The Reserve amount should be in the dimension, and from there you can create a calculated measure to use the dimension attribute to calculate the sum.
June 11, 2018 at 4:09 pm
I actually have the reserve field in 3 places right now as a result of trying to figure this out. It's in DimContract, FactProductionClaims, and FactContractReserve. I changed the calculated field to:
CREATE MEMBER CURRENTCUBE.[Measures].[Calculated Reserve]
AS [DimContract].[Reserve],
VISIBLE = 1 ;
Now it returns nulls for the reserve.
June 11, 2018 at 4:23 pm
The calculated measure needs some context to be aware of the level of attributes you are returning. The calculated measure should look something like the following:
sum
(
{
descendants
(
[DimContract].[Reserve].currentmember
, [DimContract].[Reserve].[Reserve]
)
}
, [DimContract].[Reserve].membervalue
)
Make sure that the MemberValue property of the dimension attribute is set with the correct value and type.
June 19, 2018 at 7:53 am
Can you elaborate more on "Make sure that the MemberValue property of the dimension attribute is set with the correct value and type. " It currently is not working so something may not be set right. When I try to add the field in the Excel pivot table it just spins until after about 30 minutes I cancel it. In the cube browser in Visual Studio I get "no rows found" when adding just a field from DimContract. Thanks for your time.
June 20, 2018 at 8:18 am
Distinct counts are tricky and generally if not universally require their own fact measure group in a cube. Based on what you are saying, what immediately springs to my mind is a cube with one fact measure group with granularity at claims and another one with granularity at contracts. You talked about creating a separate fact table, but you didn't say that your cube has two different fact measure groups. That you got multiple rows suggest that you joined them behind the scenes. It's not unusual for a cube to have multiple fact measure groups. They should ideally have some degree of dimension sharing between them.
I don't think it matters for what you are trying to accomplish, but telling us the VS version of the solution isn't helpful. Telling us the SQL Server version of Analysis Services is more useful. This can matter as there have been new features in newer versions that I've found very helpful.
June 20, 2018 at 10:43 am
Currently I have 2 fact groups in my measures, Fact Production Claims which has things like claim date, claim amount, etc., Fact Production which has a count distinct ContractKey. Additionally I have 3 calculations, Average Severity, Claims Frequency, and Contracts Net Refunds. I thought summing up the reserve for each distinct contract would have been similar to the Fact Production which counts the distinct contractkey, but I was wrong.
My environment is VS2010 and SQL Server 2014 Standard edition.
June 20, 2018 at 12:09 pm
Why can't one of the fact measure groups have a single contract as the granularity and the measures are the aggregations (whether count or sum)?
June 20, 2018 at 2:23 pm
That sounds like it should work, but I am not sure how to set that up. I tried add New Measure Group, selected DimContract, it added the reserve plus a few other fields to summarize. I deleted the others and re-processed the cube. The result was 1 large number for every contract. What I did get to work was to use the reserve on the FactProductionClaims table and added in the calculations this:
iif(CoalesceEmpty([Measures].[Claim Paid Count],0) = 0
, [Measures].[Reserve]
,[Measures].[Reserve] / [Measures].[Claim Paid Count])
which divides the reserve by the number of claims if any exist. It works but seems like a kludge.
June 20, 2018 at 3:01 pm
iif(CoalesceEmpty([Measures].[Claim Paid Count],0) = 0
, [Measures].[Reserve]
,[Measures].[Reserve] / [Measures].[Claim Paid Count])
That's not particularly "kludgy", but since you are using 2014 you should be able to simply do:DIVIDE([Measures].[Reserve] / [Measures].[Claim Paid Count])
The DIVIDE function will turn a division by zero error into a null value.
July 9, 2018 at 8:57 am
RonKyle - Wednesday, June 20, 2018 12:09 PMWhy can't one of the fact measure groups have a single contract as the granularity and the measures are the aggregations (whether count or sum)?
I added a new measure group based on DimContract and summed Reserve from that table. When I tested it in Excel it came back with and undefined error message after a long wait. DimContract has a primary key ContractKey and the Fact table has a FK ContractKey, and they are joined. Is there a better way to test and diagnose what is going on? Thanks.
July 9, 2018 at 9:17 am
Make sure you cluster on whatever field is aggregated. Otherwise the engine will have to do table scans.
July 9, 2018 at 9:24 am
RonKyle - Monday, July 9, 2018 9:17 AMMake sure you cluster on whatever field is aggregated. Otherwise the engine will have to do table scans.
If that field is subject to modification, that might be a real problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 9, 2018 at 9:52 am
If that field is subject to modification, that might be a real problem.
If that were true, it wouldn't be something he should be aggregating on. I have used these for knowing how many different work orders have been rescheduled an the like. The work order number can't change. But it can be rescheduled. As the metric is a distinct count on the work orders the table is clustered on the work order column. As the work order number can't change, it's not an issue.
Frankly, I can't think of a case where that would be the case. Have you run across one before? Otherwise I think you're locking for a non-problem.
July 9, 2018 at 10:50 am
RonKyle - Monday, July 9, 2018 9:52 AMIf that field is subject to modification, that might be a real problem.
If that were true, it wouldn't be something he should be aggregating on. I have used these for knowing how many different work orders have been rescheduled an the like. The work order number can't change. But it can be rescheduled. As the metric is a distinct count on the work orders the table is clustered on the work order column. As the work order number can't change, it's not an issue.
Frankly, I can't think of a case where that would be the case. Have you run across one before? Otherwise I think you're locking for a non-problem.
Ah... no... I misunderstood what you were talking about. I thought you were saying to add the clustering to a numeric column that would be aggregated with SUM or some such (pun intended). According to the above, that's not what you were suggesting at all.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply