Lot of dupes in SKY but not in ON

Brian Gray:

Joel -

Can you give us some more detail about which reports you're running? What navigation are you following to get to the reports? (I don't know which SKY report what you, or what you mean by “the ON website”)

Hi Brian

The SKY report is: Academics sections by school level
https://developer.sky.blackbaud.com/docs/services/school/operations/V1AcademicsSectionsGet

By “the ON website”, I'm referring to the “Core” interface, which for me is at [schoolname].myschoolapp.com. It's the one with the school color at the top of the screen. (My understanding is that this older/WhippleHill-based API is called “ON”.) From there, I go to Reporting > Manage Lists > Manage basic and advanced lists, and then it's one of “My Lists” that I created.

FWIW: This is part of the SQL that was generated:

SELECT <columns>
FROM dbo.da_course_base(@p1)dcb1 INNER JOIN dbo.da_academic_group(@p1)dag1 ON dag1.course_id = dcb1.course_id INNER JOIN dbo.da_academic_term(@p1)dat1 ON dat1.term_id = dag1.term_id
WHERE dat1.school_year IN (select [value] from dbo.fn_convertlisttosetstr('2021 - 2022',','))

Thanks, -Joel

Brian Gray:

My school has three terms (fall, winter, spring). What we refer to as a section of a year-long English class (the same teacher and same group of students meeting all year together) is represented in the database as three separate sections - one for each term. (This allows a school to modify the meeting schedule or room assignments by term, but keep everything else the same.)

The API call (Academics sections by school level) returns a separate entry for each term/section combination. My year-long English class will return three entries - one each for fall, winter, and spring.

Each of those entries is a separate section within the SIS (with a unique section ID number). One of them is the “lead section” - all three term/section entries in the returned data have an entry to identify the lead section ID. There is another field for the “duration” - the academic term for that section. You can identify which data goes together by looking at the academic year, course code and section identifier fields. Those three fields will be the same in all three entries for my English class.

In the Advanced List, it looks like you have set a filter to include sections for only one academic term, so you're seeing fewer sections.

Thanks for your reply, Brian.

I do understand that there should be a unique row/record for each term/section combination, but I'm getting more than just one row for each.

Our academics have just Fall and Spring Semesters. When I look only at Fall (duration.name = “Fall Semester”), there are multiple rows that are exactly the same - including the same lead_section_id. For example there are:
5 rows of Fall Semester “Beg Span - 1”
3 rows of Fall Semester “Biology - 1”
3 rows of Fall Semester “Biology - 2”

There should be only 1 row of each — which is how they come out in the Advanced List that I created.

Any more thoughts?

Anyone?

Thanks
-Joel

Brian Gray:

The “duplicate” rows that you are seeing only look like they are the same data in the list because the fields that you're displaying are all the same. There are other fields on each record that are different.

For example, if the list is displaying user information and I include the Role object, I will get a row for each user/role combination - even if I don't display the Role field.

When I am looking for the cause of this behavior, I find that adding more fields to the Display List will help me find the field(s) that are different. I can then adjust the filters.

Alternately, I sometimes deal with it in the program that's reading the list. I keep track of the Group IDs that I've seen. When the program reads a new row, if it has already seen and processed the Group ID it can skip processing the “duplicate” row.

The trade-off is that you're pulling more records than you need. If it's the difference between 300 rows and 1500 row, I will often have my program deal with it. On the other hand, if the list is pulling 20,000 records but I only care about 1000 of them, it's probably worth the time to refine the Advanced List.

Hi Brian

The problem is that with the SKY API — as far as I can tell, someone please correct me if I'm wrong — there are fixed sets of columns/fields that get returned with each data call. The Academics sections by school level endpoint returns only 11 fields. I cannot change the fields that get returned, and I cannot filter the data before it gets returned.

I can correctly pull the number of records that I'm expecting when I use an Advanced List, but because my understanding is that the SKY API is faster and ‘preferable(?)’, I'm trying to use that for this data - which I had thought would be pretty straightforward.

Any other thoughts, anyone?

Thanks,
-Joel