4

I need to calculate a measure grouping by date in a range of dates considering a start and end dates in a dimension. The objective is to count the number of students of a specific school that are actives on each day in a period.

The following SQL Code works on Data Warehouse (source of cube).

SELECT 
     t.[date]
    ,count(distinct a.SKStudent) as Students
FROM DM.DimTime t
    CROSS JOIN DM.FactStudents a 
    INNER JOIN DM.DimStudents m ON a.SKStudent = m.SKStudent
    INNER JOIN DM.DimSchool e ON a.SKSchool = e.SKSchool
WHERE t.[date] between '20170502' and '20170512'
    and e.SchoolCode = 123456
    and t.[date] between m.[StartDate] and m.[EndtDate]
GROUP BY t.[data]

The result set is like the following:

+--------------+----------+ 
| date         | Students |
+--------------+----------+ 
| 2017-05-02   |    567   |
| 2017-05-03   |    567   |
| 2017-05-04   |    568   |
| 2017-05-05   |    570   |
| 2017-05-06   |    570   |
| 2017-05-07   |    570   |
| 2017-05-08   |    573   |
| 2017-05-09   |    573   |
| 2017-05-10   |    571   |
| 2017-05-11   |    568   |
| 2017-05-12   |    568   |
+--------------+----------+

I try made the MDX code. The following is what I tried. I don't know how filter the day using the logic in CASE statement (between '???' marks):

WITH SET DateRange AS 
    [Dim Time].[Date].&[20170502]:[Dim Time].[Date].&[20170512]
MEMBER StudentsByDay AS 
    AGGREGATE(DateRange, 
        ???CASE WHEN DateRange.CURRENTMEMBER 
                    BETWEEN [Dim Students].[Start Date] 
                        AND [Dim Students].[End Date] 
                THEN [Measures].[Students Count]
            ELSE NULL END???)
SELECT 
    NON EMPTY { [StudentsByDay] } ON COLUMNS,
    NON EMPTY { [DateRange] } ON ROWS
FROM [Education]
WHERE ( [Dim School].[School Code].&[123456] )
Ricidleiv Tondatto
  • 132
  • 12

1 Answers1

2

MDX isn't that flexible like SQL. I'd recommend to put the following condition into your FactStudents table:

t.[date] between m.[StartDate] and m.[EndtDate]

and use like a separate measure or replace the existing one.

You need the following things:

  1. a fact table with DateID, StudentID fields (see the code below);
  2. a measure group related with Student and Date dims;
  3. a measure with DistinctCount aggregation on StudentID field.

The fact code:

SELECT 
     t.[date] as DateID
    ,a.[SKStudent] as StudentID
FROM DM.DimTime t
    CROSS JOIN DM.FactStudents a 
    INNER JOIN DM.DimStudents m ON a.SKStudent = m.SKStudent
    INNER JOIN DM.DimSchool e ON a.SKSchool = e.SKSchool
WHERE e.SchoolCode = 123456
    and t.[date] between m.[StartDate] and m.[EndtDate]
Danylo Korostil
  • 1,464
  • 2
  • 10
  • 19
  • I can't understand how to apply this. My date (t.date) is unknown. It's informed in runtime. Did you mean build a new fact aggregating FactStudents by DimTime? – Ricidleiv Tondatto Jul 11 '17 at 01:50
  • I understand now. I'm just scared about the quantity of rows in that fact for our reality: 1,3M Students * 365 days (in average). – Ricidleiv Tondatto Jul 11 '17 at 14:55
  • I really will appreciate if anybody could show how to do this in MDX. – Ricidleiv Tondatto Jul 11 '17 at 14:57
  • 1
    Trust me, it's the right way of doing it. You may get some MDX ideas from my answer here: https://stackoverflow.com/questions/44388967/create-a-calculated-measure-in-mdx-that-filters-by-date-range/44410991#44410991 – Danylo Korostil Jul 12 '17 at 11:25