-1

Can you please let me know the best approach for designing Data ware house and dimension modelling (SSAS cube) based on below requirement. Requirement here is, I have to get the student count which are active as of that month, if the user selects year (2015) from drop down which is displayed in the image. Catch here there is no option to select enrollstartdate and enrollenddate as two different dates (no role play dimension) , only one filter i.e Year.

Requirement to get the active student count as of that month

talonmies
  • 70,661
  • 34
  • 192
  • 269
Rathnam
  • 1

1 Answers1

0

There are a couple of possible approaches that come to mind. The first is a periodic snapshot fact table and another is a timespan accumulating snapshot fact table.

In my opinion, the first is easier to implement, so I've provided some detail below that I hope you will find useful.

CREATE TABLE FactEnrollmentSnapshot
(
    DateKey INT NOT NULL -- Reference to Date dimension table
    , StudentKey INT NOT NULL -- Reference to Student dimension table
);

CREATE TABLE DimStudent
(
    StudentKey INT NOT NULL
    StudentId ?
    ...Other Student Attributes...
);

CREATE TABLE DimDate
(
    DateKey INT NOT NULL
    , FullDate DATETIME NOT NULL
    , Year SMALLINT
);

Assuming your date dimension is at the day grain, you could either store daily snapshots, or just store snapshots on the 15th of each month.

Depending on whether you need to get a count of unique students during 2015 or the most recent count of students in 2015 you could use the DISTINCT COUNT aggregation or the LastChild aggregation in SSAS. If you use LastChild, make sure your Date dimension is marked as a Time type.

Note that a snapshot style fact table results in semi-additive facts.

You could get the raw data to populate the fact table from your example source data by using a CROSS JOIN between you source data and the Date dimension

SELECT
    StudentTable.StudentID
    , DimDate.FullDate
FROM
    StudentTable
    INNER JOIN DimDate ON (DimDate.FullDate BETWEEN StudentTable.EnrollDate AND ISNULL(StudentTable.DisenrollDate,'9999-12-31'));

I didn't include the lookups for surrogate keys for simplicity

You can then get the answer for your business users be filtering on the Year attribute in the Date dimension.

I hope this is useful in getting you started with a possible approach.

Regards, Jesse Dyson

Jesse Dyson
  • 71
  • 3