Some tables are not created and populated

[RU-TC]

I just recently set this process up on a new SQL Server, and everything seems to be working perfectly so far, except there are 6 tables that are not created: course_score_dim, course_score_fact, conference_dim, conference_fact, conference_participant_dim and conference_participant_fact.

The tables are picked up by the process and exist in the CanvasDataDownload and CanvasDataUnzips folders, and they are in the schema definition at https://portal.inshosteddata.com/api/schema/latest .

It looks to me like they need to be initially created in the CanvasDataRawSchema, RAWSchemaVal and CanvasDataTableFields before the refresh process will pick them up. Is this correct? If so, has anyone done this, I am not certain about what exactly needs to be added to get these tables to load.

Thanks for the great app and any help! Robin

[tehcnom]

Hi, Having the same issue

Below are non-populated tables :

communication_channel_dim course_score_dim course_score_fact discussion_entry_dim discussion_topic_dim enrollment_dim enrollment_fact enrollment_rollup_dim file_fact quiz_question_fact quiz_submission_dim quiz_submission_historical_dim requests submission_dim wiki_page_dim submission_fact

Below are tables that are not created but in the CanvasDataDownload folder

grading_period_dim
grading_period_fact
grading_period_group_dim grading_period_score_dim grading_period_score_fact root_file_id score_dim score_fact submission_comment_participant_dim submission_comment_participant_fact

Any help to troubleshoot the cause will be appreciated. So much more if a solution will be provided. Thanks in advance

[tehcnom]

Checking the tables after an overnight update of the DB... all are now populated and the uncreated tables are now created but unpopulated. Checking the unzip folder (CanvasDataUnzips),,,.. the now unpopulated tables doesnt have the equivalent download ZIP files.

[wjones20]

Hello -- Apologies, but we're short-staffed right now and not able to contribute any troubleshooting work on CanvasDataViewer right now. You might be able to get some feedback from other users with the application. Thank you, Bill Jones

---

Bill Jones, MA, MS Director, Instr Tech & Info Mngmt Center for E-Learning ph: (734) 487-9043 Eastern Michigan University fax: (734) 487-6695 Ypsilanti, MI 48197 email: wjones20@emich.edu

---

On Tue, Oct 2, 2018 at 3:14 AM tehcnom notifications@github.com wrote:

Hi, Having the same issue

Below are non-populated tables :

communication_channel_dim course_score_dim course_score_fact discussion_entry_dim discussion_topic_dim enrollment_dim enrollment_fact enrollment_rollup_dim file_fact quiz_question_fact quiz_submission_dim quiz_submission_historical_dim requests submission_dim wiki_page_dim submission_fact

Below are tables that are not created but in the CanvasDataDownload folder

grading_period_dim grading_period_fact grading_period_group_dim grading_period_score_dim grading_period_score_fact root_file_id score_dim score_fact submission_comment_participant_dim submission_comment_participant_fact

Any help to troubleshoot the cause will be appreciated. So much more if a solution will be provided. Thanks in advance

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/EMU-CFE/CanvasDataViewer/issues/20#issuecomment-426172587, or mute the thread https://github.com/notifications/unsubscribe-auth/AS3lq_cYdr9LlhcsvWH0u4tS5uY3fawBks5ugxJZgaJpZM4VTTiu .

[benjamin-hudson]

@tehcnom I'm not sure if you ever resolved your issues with this or if you're using this anymore, but I ran into the same issue, specifically with the score_ tables so I created a new stored procedure that will loop through the unzipped files and rename the assignment_groupscore* files before trying to load them into the database so they match what is trying to be loaded. I also added EXEC [dbo].[CanvasData_Rename_Score_Files] to the CanvasData_General_TableBuild stored procedure on line 30 before the table field creation kicks off. You should be able to slightly modify this to work for renaming other files as well.

I think the disconnect for this issue is on the Canvas side in their schema and file download names, but i'm not 100% sure.

renameFilesStoredProcedure.txt

[tehcnom]

Hi and thank you for the information. The issue somehow seemed to have resolved itself after a few days of repetitive downloads. leading me to assume that it could have been caused by network interruptions during the process. So far the issue has not been experienced again. Thank you for an awesome script.

On Fri, Feb 21, 2020, 9:17 PM benjamin-hudson notifications@github.com wrote:

@tehcnom https://github.com/tehcnom I'm not sure if you ever resolved your issues with this or if you're using this anymore, but I ran into the same issue, specifically with the score_ tables so I created a new stored procedure that will loop through the unzipped files and rename the assignment_groupscore* files before trying to load them into the database so they match what is trying to be loaded. I also added EXEC [dbo].[CanvasData_Rename_Score_Files] to the CanvasData_General_TableBuild stored procedure on line 30 before the table field creation kicks off. You should be able to slightly modify this to work for renaming other files as well.

I think the disconnect for this issue is on the Canvas side in their schema and file download names, but i'm not 100% sure.

renameFilesStoredProcedure.txt https://github.com/EMU-CFE/CanvasDataViewer/files/4236180/renameFilesStoredProcedure.txt

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/EMU-CFE/CanvasDataViewer/issues/20?email_source=notifications&email_token=AKN7CO55X5BCISOFXO3WTA3RD7H5RA5CNFSM4FKNHCXKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEMSVINI#issuecomment-589648949, or unsubscribe https://github.com/notifications/unsubscribe-auth/AKN7CO5ZTFYVEYOGKSOXIYTRD7H5RANCNFSM4FKNHCXA .

[gbeach90]

I just recently set this process up on a new SQL Server, and everything seems to be working perfectly so far, except there are 6 tables that are not created: course_score_dim, course_score_fact, conference_dim, conference_fact, conference_participant_dim and conference_participant_fact.

The tables are picked up by the process and exist in the CanvasDataDownload and CanvasDataUnzips folders, and they are in the schema definition at https://portal.inshosteddata.com/api/schema/latest .

It looks to me like they need to be initially created in the CanvasDataRawSchema, RAWSchemaVal and CanvasDataTableFields before the refresh process will pick them up. Is this correct? If so, has anyone done this, I am not certain about what exactly needs to be added to get these tables to load.

Thanks for the great app and any help! Robin

Robin,

I'm wondering the same thing, and experiencing this for some tables as well- the conference tables among them (these are my main interest for mining remote-learning data). Did you ever confirm/deny your hypothesis?

With respect, Greg

[gbeach90]

@tehcnom I'm not sure if you ever resolved your issues with this or if you're using this anymore, but I ran into the same issue, specifically with the score_ tables so I created a new stored procedure that will loop through the unzipped files and rename the assignment_groupscore* files before trying to load them into the database so they match what is trying to be loaded. I also added EXEC [dbo].[CanvasData_Rename_Score_Files] to the CanvasData_General_TableBuild stored procedure on line 30 before the table field creation kicks off. You should be able to slightly modify this to work for renaming other files as well.

I think the disconnect for this issue is on the Canvas side in their schema and file download names, but i'm not 100% sure.

renameFilesStoredProcedure.txt

_Benjamin,

Would you please show me how to append this to the stored procedures in the DB? I cannot figure this out. I've edited it for the tables that aren't showing for me, but don't know how to proceed. Thanks for your consideration.

• Greg_ renameFilesStoredProcedure.txt

[benjamin-hudson]

@gbeach90 Looking at changes you made, I want to confirm that the Unzipped file location is the same as mine: C:\CanvasDataUnzips For the initial creation of the stored procedure you'll need to change ALTER PROCEDURE to CREATE PROCEDURE Lastly comes the actual renaming piece of this, the current replace function you have will simply rename the file to the same thing. Using my original file as an example:

Select @oldFileName=oldName, @NewFileName=REPLACE(oldName,'assignment_group_score_','score_') from @nameTable 
            WHERE oldName LIKE 'assignment_group_score_%'
                AND RowID=@RowsToProcess
            GROUP BY oldName

My select statement is looking for any files that begin with 'assignment_groupscore' then the replace function is replacing 'assignment_groupscore' with 'score_'.

So if the file was initially assignment_group_score_dim1 then this whole procedure would change that name to score_dim1 and so on for all files beginning with assignment_groupscore

In your case we need to know what to change conference to and that would go where the second conference was.

Select @oldFileName=oldName, @NewFileName=REPLACE(oldName,'conference_','') from @nameTable 
            WHERE oldName LIKE 'conference_%'
                AND RowID=@RowsToProcess
            GROUP BY oldName

I'm trying to find why I knew that I needed to adjust the names of the scores files. I think it was because the tables were called score_* and not assignment_groupscore In your case, I don't see any tables in my instance that are conference related.

So in my case the score tables were present in the database, but they were empty. In your case if your setup looks like mine, the conference tables are missing, so i'm not sure my process will work to fix things. You could definitely use my process to create one that would work, but need to find where the disconnect is here...not sure I have the time currently to dig into that too much.

Also out of curiosity you mentioned using the conference tables to mine data from remote-learning. What are you looking to gain from the conference data, it doesn't seem that beneficial from what i'm reading. I'm doing some things for my K-12 district and have written some SQL queries to determine attendance from counting submissions per student per class, time spent in Canvas using the requests table and then a general "who logged in today".

Thanks, Ben