Getting count of distinct/unique measure elements in a cube

[ViRa]

Hi all,

I need to count the number of unique member numbers in the measure dimension. Cube displays individual claim details for members of an account. The dimensions are –

- Account Number (leaf level is the group number) (eg. G1234)
- YearMonth (eg. 201602)
- Model (eg. ModelA)
- Type (eg. TypeA)
- UniqueDim (This dimension stores unique claim line numbers against which each member’s individual claim details are displayed. If I do not have this dimension, then the cube either displays only ONE claim details of a member (if Store Values is selected in TI) or total of all the claims (if Accumulate Values is selected in TI))
- Measure dimension (Has claim dollar amount and other demographic details including member number)

I've attached cube image.

Cube View.jpg (304.06 KiB) Viewed 13026 times

So, when I generate cube view for an account, the member number is repeated across each line in the measure dimension in case the member has multiple claims). My requirement is to update one of the elements (called DistinctMemNum) of the measure dimension, the count of distinct/unique member numbers only.

To achieve this, once the cube was loaded, I updated DistinctMemNum element through a separate TI which fetched the count of distinct member number for an account using SQL query (Select count (Distinct Mem_Num) from table). In the Data tab, I used CellPutN function to update the cell. However, since I have the UniqueDim element, it by default replaces the count as ‘1’ for each of the record. As a result, the DistinctMemNum element gets ‘1’ for each repeated line of member number. As shown in the attachment, MemB has two claims and it is placing '1' against each line, so the count of mem num is 4 instead of 3.

I also tried using ConsolidatedCountUnique () but although the process completes successfully, it is not loading any value in that cell. The syntax I used is –
UniqueMemCount = ConsolidatedCountUnique(0, 'UniqueDim', ‘Cubename’, e1,e2,e3,e4,e5, 'Mem_Num');

Can you please help me with getting the count of unique element values (member number in my case)?
TIA for your time and help

[tomok]

I also tried using ConsolidatedCountUnique () but although the process completes successfully, it is not loading any value in that cell. The syntax I used is –
UniqueMemCount = ConsolidatedCountUnique(0, 'UniqueDim', ‘Cubename’, e1,e2,e3,e4,e5, 'Mem_Num');

Process completes successfully????? That is a rules function, not a TI function. You need to post your actual code, whether that's a TI or rule, not this el1, el2, el3 crap. There's no way to know if your problem is syntax or theoretical without it.

[ViRa]

Hi Tom,

I did refer to the Reference Guide before using the function. Although, a rules function it can be using even in TI. That's what the guide says. I used it in TI process.
Attached is the screenshot. The cube name and element names do not match my earlier example.

ConsolidatedCountUnique.jpg (522.58 KiB) Viewed 13012 times

[ViRa]

In the screenshot, 'Mem_Unique' is the dimension that store unique elements. Against that dimension, I'm trying to find the count of unique member numbers only. Appreciate your help in getting me appropriate results.

[tomok]

Yes, I am well aware that rules can be used in TI processes. However, just because they can doesn't mean the SHOULD, especially if you don't properly account for the timing of data. The ConsolidatedCountUnique function is only going to return valid results when all of the intersections in the cube for which you are asking for a count have been populated. Using it in a TI is only going to be evaluated against what has been loaded SO FAR.

In order to make sure your logic and synatx are correct you should put the ConsolidatedCountUnique in a rule on the cube, after the load is complete, to see if it pulls what you expect. If not, then your logic and/or syntax is not correct and needs tweaking. Once you are Saure it works then you can put it in a TI.

[ViRa]

Thanks for the reply. I will apply this function in rule and try. I will update the post accordingly with my results.

[ViRa]

Hi Tom,

I tried both the below rules. That is, I tried 'Mem_Unique' dimension as well as the measure dimension in the 'unique-along-dimension-name' parameter. When saved and executed, in both the cases the cube is still not loading values of the count. Is my approach of using the ConsolidatedCountUnique () correct? Appreciate your response.

Rule-ConsolidatedCountUnique.jpg (146.63 KiB) Viewed 13000 times

[ViRa]

Code: Select all

SKIPCHECK;
#['Distinct MemNum' ]   = N: ConsolidatedCountUnique(0, 'Mem_Unique', DB('Account Extracts - Membership', !Process_YYYYMM, !Mem_Model, !Risk Type, !Mem_Unique, !Mem_Account, 'MEM_NUM'));

['Distinct MemNum' ]   = N: ConsolidatedCountUnique(0, 'Mem_Measures', 'Account Extracts - Membership', !Process_YYYYMM, !Mem_Model, !Risk Type, !Mem_Unique, !Mem_Account, 'MEM_NUM');

Both Mem_Num and DistinctMemNum are numeric elements of measure dimension.

[gtonkin]

From the screenshots, it does not look like you are feeding. Try feed 'Distinct MemNum' from 'MEM_NUM' as a start.
Also concerned about the use of N: Data looks to be consolidating in same cube so one dimension must be a C level-expect some interesting results.
May be useful if you post a cube view showing what you are expecting to count as there may be a different way to achieve this. Need to see member and unique member etc.

[ViRa]

Thanks Gtonkin for your reply.

Try feed 'Distinct MemNum' from 'MEM_NUM' as a start.

I tried adding feeders as you suggested. The cube is still not loading expected results. I agree and doubt my approach of using ConsolidatedCountUnique () in the first place to achieve the results! Appreciate if you could direct me.

Below is the cube view. Sorry I had to mask many fields in the screenshot! It is showing member demographic and claim details against time, type, model, group number dimensions. However, since members can have multiple claims, in order for the cube to show them all, I have another dimension (called Mem_Unique) which has unique keys for each claims.

CubeView.jpg (185.31 KiB) Viewed 12983 times

The requirement is to get the count of unique member numbers. To achieve this, I thought of having an element called 'DistinctMemNum' in the measure dimension that should place '1' against each distinct member number only, so that when I get the consolidated cube view (bringing in consolidated element from each of the dimensions), I can then get the sum of the distinct member numbers. In my above screenshot, it will place 1 against each line of record until a duplicate entry (such as highlighted member) where it will place 1 and 0.

Please let me know how can I get this count. TIA for your time and help.

[ViRa]

Can anybody please assist me with my request? It'll be really helpful. Thanks

[gtonkin]

Based on the cube view, it may be easier to set your flag (0,1) when you do your data load. In your data section you can do a read against the unique member and totals on other dimensions and the count measure-if the count measure is zero, write a 1 to the count for the current record, otherwise do nothing.

[BariAbdul]

Hi Vira,It might help:
http://www.tm1forum.com/viewtopic.php?f ... 9799#p9775 Thanks

[ViRa]

Thanks gtonkin and Badri Abdul. I will surely try both your suggestions and update the post with my results.

[BariAbdul]

How did you go Vira? Any success.Thanks

[ViRa]

Thank you Badri Abdul for following up. Really appreciate it.
I did not get much success and had to look into other issue before working on the current one. So far, I tried Gtonkin's suggestion of putting '1' when the data is loading. Also, from the link you shared I got the idea of creating another cube which stores the unique member number count and load the number in the master cube.

In Gtonkin's suggestion - since I have a dimension which has unique elements, it updates each and every cell (irrespective of unique count or not) with 1. As a result, I'm not able to get the desired result.

As per your suggestion - The new cube loads and displays the count perfectly. However, when I use rule to fetch data from the source (new cube) into the target group, it is repeating the unique count for each and every cell (again because of that dimension storing unique keys). And hence the total (at consolidation level) is incorrect. Here is the screenshot and rules/feeders in source and target cube.

Updated UniqueMemNum count.jpg (564.17 KiB) Viewed 12825 times

#Feeders in source cube -

Code: Select all

FEEDERS;
['UniqueMemNumCount' ]  => DB('Target Cube', 'UniqueMemNumCount',!Process_YYYYMM, !Mem_Model, !Risk Type, !Mem_Account);

#Rule in target cube -

Code: Select all

SKIPCHECK;

['201602','UniqueMemNumCount' ]  = N: DB('Source Cube', '201602', !Mem_Model, !Risk Type, !Mem_Account, 'UniqueMemNumCount');
FEEDERS;
['UniqueMemNumCount' ]  => DB('Target Cube', !Process_YYYYMM, !Mem_Model, !Risk Type, !Mem_Unique, !Mem_Account, 'UniqueMemNumCount');

I'd really appreciate if you could guide me in getting the desired result. In the meantime, I'm looking into other requirement (again on which I'm facing issues and hope I get it resolved without bothering anybody in this forum).

Thanks