DISTINCT Counts in TM1

[Bris_Clinton]

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.

[Martin Ryan]

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

[Bris_Clinton]

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

[Martin Ryan]

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

[Steve Rowe]

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

[Bris_Clinton]

Thanks Steve.

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

[Steve Rowe]

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?

[Bris_Clinton]

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...

[MarioRubbo]

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

[tomok]

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.

[LoadzaGrunt]

I assume you are talking about something equivalent to doing a "SELECT DISTINCT" type query in SQL

Actually I think the OP's problem is more to do with the fact he is trying to resolve a headcount from adding up FTEs.

I would advise him to explore the option of a separate cube, with a separate source query.

[MarioRubbo]

@tomok: all OLAP solutions that I know of have multiple rollup calculations available for measures. There are many types of analytical questions that revolve around counting things: # of customers that bought last month, etc.

[jimshen]

I would consider the dynamic subset (driven by MDX) in student ID dimension with creteria as load >0.

[jydell]

A solution to this may be to build an alternative hierachy in the dimension that holds "subject" information. This alternative hierachy would consist of all 'N' elements and no 'C' elements.

The alternative N elements would look like.
Count_Class123 => Representing N element (Class123) in normal structure
Count_SchoolABC => Representing C element (SchoolABC) in normal structure
Count_Uni => Representingto C element (Uni) in normal structure

Then when you load your students data via TI, add a procedure for every student ID in the month populate "1" where they exist in Count_class123 etc. Then use some logic to determine the parent school of Class123 and populate "1" in the Count_SchoolABC then again for Count_Uni.

By doing the above every time a student re-appears in a school it will overwrite the "1" (or maybe "1/12" to represent enrolled for this 1 month out of 12) not add to it and make "2".

Ideally the result would be alternative "N" elements that hold accurate counts by months of unique students at any level you wish to query. The downside of this approach may be its a little hard to read as the structure would consist of only N elements and doesnt facilitate easy drilldown from Uni --> school --> class.

Hopefully this could be added to your existing TI process utilising your existing cubes / dimensions.

Let me know what you think. (Not a rule based answer but maybe workable solution)

Regards

Jeremy