DISTINCT Counts in TM1

亚伯拉汉

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.

jsyyrs

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

chenwu521

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

bxmsw

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