Count Distinct Of Claim Numbers

[mrcool]

Hi Guru's,

Could you please suggest few best options to do a count distinct in transformer.We are using Cognos 8.4
We have claim number in the fact table but the fact has lower granularity than Claim Number.
I want to create a measure Claim Count with distinct Claim Numbers.
I am considering an option of creating a dimension with Claim number as a column and perform category count on that field. Is that a better option?
Appreciate your quick response.

Cheers,
MC

[twlarsen]

Do you have an individual claim line key you could count?

Are you looking at having a count of claims as well as claim lines in the cube?

[Arsenal]

I remember struggling with this issue 3-4 years ago

The problem is count distinct is an aggregation but so is the cube! Hence, you'll always end up getting inflated numbers if you do a calculated measure type of deal.

If I remember correctly, we tried a category count (which does give a count distinct by default) but not only do did we have a huge number of rows so our cube build would take a hit this way but also there's a limit to the categories of a  level. Don't remember it off the top of my head but 64k or 65k or something like that

What we did, again I'm going by memory here, was to create a datasource QS in FM, put in our SQL, add in a dummy value of 1 everytime the check for the item we needed a count distinct for passed and then did a group by on this dummy value.
We put this dummy value in as a measure into the cube and it worked like a charm

[cognostechie]

Don't remember it off the top of my head but 64k or 65k or something like that

The limit is parent:child cannot exceed 65500 categories so if you have Agent as higher level and Customer as the lower level, you cannot have more than 65500 Customers for one Agent.
There is also a limit that the category count cannot exceed 2 million something categories for the entire model.

Yes, you are correct in your approach for distinct count. That's exactly what I did too for an insurance company. Created another QS by including only those dimensions values for which one row would result in one dictinct claim and then used 1 as the measure.

[mrcool]

Thank you all for sharing your experiences..

Do you have an individual claim line key you could count?

Are you looking at having a count of claims as well as claim lines in the cube?

We are looking for count of claims as well as claim lines in the cube..

Thanks,
MC

[mrcool]

Yes, you are correct in your approach for distinct count. That's exactly what I did too for an insurance company. Created another QS by including only those dimensions values for which one row would result in one dictinct claim and then used 1 as the measure.

I have added my claim number in the User hierarchy as the lowest level. After doing this Cube performance in Analysis Studio took a hit. How to improve performance for faster cube access?

Thanks in advance.
MC

[twlarsen]

It depends on how you're setting your cube up.  I did what was suggested below and created a dummy value of 1 when needed.  For example, I created a field called claim count and claim line count and calculated them as 1 for all claim lines, and 1 if it was the first claim line (or something close to that).

I summed this up in the cube without needing to bring claim id or claim line into the cube.

[mrcool]

Thanks twlarsen.. So where did you create this calculation? I mean in FM/IQD?
So your lowest level is claimLine or claim(Sorry we don't have claim line in our model)?My understanading is claimline is lowest level with calculated measure as 1 for all the claimlines and 1 for first record of claim. Correct me If I am wrong.

Thanks,
MC

[twlarsen]

Claim line would be the lowest level.  Claim data might look like this:

Claim     ClaimLine     ClaimCount    ClaimLineCount
505A                  1                   1                   1
505A                  2                   0                   1
505A                  3                   0                   1
520A                  1                   1                   1
520A                  2                   0                   1

The two counts are the added fields.  I've had this added at the database, modeling or report layer, just depends on the situation.

Although you mentioned not having claim line in your model, which I don't understand how you could do any reporting at the line level without having that information.

[mrcool]

Once Again Thank you twlarsen..
At present claim is the lowest granularity in our model.
One more question do you have claim and claimline in the fact table or just claimline?

Thanks,
MC

[twlarsen]

Depends on the information needed, but yes, I had a fact table to the claim line level that I would use when needing claim line information.

[mrcool]

Depends on the information needed, but yes, I had a fact table to the claim line level that I would use when needing claim line information.

Thanks twlarsen.