1

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
2

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.