Dynamic Band in RDBMS
Our challenge is to reproduce the below example (RDBMS) but leveraging Microsoft SSAS Tabular Model. We have value in a column and we need to group them, so literally we have Student with Marks (any value within a certain range like 0 to 100), and we need to group those Marks into Band/Range and provide distribution on a chart to allow the users to work them out.
But there is a twist, we need to provide many bands so one additional column in Mark Table will not cut it and the customer would like to be able to add more bands in the future, so clearly adding columns is not the way to go.
In the built-in RDBMS example below we have defined 3 different bands being: - 5-Band - BiBand - Tri-Band But we could add as many as required and on various ranges, as this is another requirements, we need to provide banding for a value ranging between 0-100 but also 0-4 and maybe other values in the future.
Before I get in the details of the DB Schema, I thought I would expose first the SQL I would use in a conventional RDBMS which would be :
select studentid, StudentMark, BandRangeName
from StudentMark, Band, BandRange
where Band.BandName = '5-Band' # This is where we would filter at report level
and Studentmark Between BandRange.BandLowRange and BandRange.BandHighRange
and Band.BandID = BandRange.BandID
So all we need the users to do is to choose the Band (“5-Band” above), they would like to work on for that particular report.
Below are the result based on the band chosen at report level:
5-Band BiBand Tri-Band
1 10 Very Low
2 11 Very Low
3 13 Very Low
4 20 Very Low
5 25 Low
6 35 Low
7 40 Low
8 44 Medium
9 50 Medium
10 60 Medium
11 67 High
12 70 High
13 75 High
14 80 High
15 90 Very High
Student Mark Band
1 10 Low
2 11 Low
3 13 Low
4 20 Low
5 25 Low
6 35 Low
7 40 Low
8 44 Low
9 50 Low
10 60 High
11 67 High
12 70 High
13 75 High
14 80 High
15 90 High
1 10 Low
2 11 Low
3 13 Low
4 20 Low
5 25 Low
6 35 Medium
7 40 Medium
8 44 Medium
9 50 Medium
10 60 Medium
11 67 High
12 70 High
13 75 High
14 80 High
15 90 High
RDBMS Schema - Physical Band Table
BandID BandName
1 TriBand
2 Bi-Band
3 5-Band
3 defined, but can define as many as required.
- Physical Band Range Table
BandID BandNumber BandRangeName BandLowRange BandHighRange
1 1 Low 0 30
1 2 Medium 31 60
1 3 High 61 100
2 1 Low 0 50
2 2 High 51 100
3 1 Very Low 0 20
3 2 Low 21 40
3 3 Medium 41 60
3 4 High 61 80
3 5 Very High 81 100
- And finally over-simplistic StudentMark table
StudentID StudentMark
1 10
2 11
3 13
4 20
5 25
6 35
7 40
8 44
9 50
10 60
11 67
12 70
13 75
14 80
15 90