DISTINCT Counts in TM1

恭喜发财1

Hi everyone,

I know this was covered in another topic, but I couldn't work out how to apply it to my cube.

Any help would be greatly appreciated.

I am using TM1 in a university setting to store student data.

We have quite a large number of dimensions, but for simplicity I'll say we have student id, degree, subject, semester, student type.

The measure we use is called load and is a fraction of a full time year. So for example a student may be enrolled in 8 subjects across multiple semesters in a year and each subject has a load of .125 and thus for the year they have a load of 1. But many students are part time and some take on additional subjects so their load will add up to anything from .1 to to over 1 for any given year (a consolidation of the semester dimension).

We want to be able to count distinct students no matter which level we look at.

So I might want to know the count of a particular number of students in a program in a particular semester.

The problem is those students could be in any one or more of dozens of subjects so the distinct count of the students in the individual subject does not equal the distinct count of student in those subjects, as this would count some students more than once.

Similarly across a given year some students will enrol in all semesters, some in only one, and others in more than one but not all. So once again we can't just add the distinct count of the students in the semesters that make up a year because they are overlapping sets.

If anyone can think up a way to count distinct no matter which dimension is consolidated that would be awesome, we have bashed our heads together over it for quite some time.

xdd11

Couple of ideas.  For a snapshot only, create a TI process that runs through the list of students and checks the total of all subjects, all degrees, all whatever to see if the total is not equal to zero.  If so, add one.  Output number at the end.

Alternative two, create a new cube called "Student Count" with the students as one dimension, possible year/semester as another dimension.  Have a rule that says [] = N: if(DB('BigCube', !Student, 'All Subjects', 'All degrees', 'All semesters')<>0, 1, 0);

HTH,
Martin

yzbboy

Thanks Martin.

I think I didn't explain the requirements very well.

The idea of counting students is not a once off or well defined request.

We are constantly getting asked to count at all different levels. A subject coordinator may want to know the count of students enrolled in his upcoming class, a head of faculty wants to know the count across all the degrees his faculty looks after (faculty is a consolidation of the degree dimension), the head of the university may want to know the absolute top level count of how many students we have (this can be answered with your first suggestion).

I was hoping we could have a rule driven measure that is not dependant on first knowing which level we would look at?

Cheers,

Clinton

6235999

Might be best to go with the snapshot suggestion then.  Use TI to cycle through the student dimension and then put all the arguments (faculty, department, whatever) as parameters to the TI process that dictate at which level of consolidation for each dimension you look in the cube.  

Rather than spit out a single number you probably want to export an ascii file so people have got something to put their hands on.

Martin

wquyhytuoy

If you write the rule in your main cube but don't limit it to the N level won't you get the right answer where ever you look in the cube?

['Count']= If ( ['Load']=0,0,1)

#  Doing it like this MAY be "cheaper" ['Count']=Min ( 1, ['Load']);

You'd have to make sure this didn't impact performance.

HTH

wddr

Thanks Steve.

Unless I have mucked something up this just gives me 1 at every level?

jhh6yf

errr, yes.. let me think some more...  

I'm thinking that this cannot be solved with a single Count measure, you probably need to create multiple measures that each work for a consolidations along a specific dimension of the cube?

yseen

Steve Rowe wrote:errr, yes.. let me think some more...  

   I got all excited thinking that it really was that easy!

I could do separate measures at least we can allow users access and they can just look up themselves.

I am starting to think that maybe it is not possible to have a single rule driven measure...

messiahxu

Is it really not possible to do a Distinct Count measure in TM1 ?

xiaomi1984

MarioRubbo wrote:Is it really not possible to do a Distinct Count measure in TM1 ?

I assume you are talking about something equivalent to doing a "SELECT DISTINCT" type query in SQL. It's not a native function of TM1 and if you really think about it, why would it? TM1 is not a relational database and although it can store strings, it really isn't designed for that purpose. It's main purpose is as a tool to analyze financial data and who cares if there are duplicate numbers. So, you've got $10 in more than one month for a particular account or product. That's really not important. You can't have duplicate members in a dimesion so there is no need for a DISTINCT function as it relates to dimensions. If you need to do those type queries put your data in a SQL database.