search

EMU-CFE / CanvasDataViewer

CanvasDataViewer is used to automatically download Canvas Data files into a SQL Server database where you can query and analyze the information.
29 stars 8 forks source link

Column data types don't match Canvas' documentation #12

Open lehnertz85 opened 7 years ago

lehnertz85 commented 7 years ago

Thank you for making this project! It was pretty easy to setup with MS SQL server 2014.

I have been working with the file_fact table. I was trying to find the largest file in our instance. I kept getting a really low number when using MAX(). Come to find out the column size is not a BIGINT, but a VARCHAR. This is fine for spitting out data to be displayed, but not for doing any math.

Now that I know, I can adjust my queries accordingly. It looks like everything is a VARCHAR.

wjones20 commented 7 years ago

Hi Bill. I’m really glad to hear feedback that the setup worked well. You’re correct that all the BIGINT fields end up as VARCHAR in the production tables. I’m embarrassed that I can’t remember the exact details of why we chose that. I’m pretty sure it must have thrown an error during conversion, since we used proper SQL Server number datatypes for most of the other numerical fields. You can see how all the datatypes get converted in the dbo.CanvasDataTypeCrosswalk table. If you experiment and find a datatype that works better then please post it here and we’ll experiment if we can update the system with it. Thanks, Bill J.

Bill Jones, MA, MS Director, Instr Tech & Info Mngmt

Extended Programs ph: (734) 487-9043

Eastern Michigan University fax: (734) 487-6695

Ypsilanti, MI 48197 email: wjones20@emich.edu

From: William Lehnertz [mailto:notifications@github.com] Sent: Friday, September 8, 2017 12:00 PM To: EMU-CFE/CanvasDataViewer CanvasDataViewer@noreply.github.com Cc: Subscribed subscribed@noreply.github.com Subject: [EMU-CFE/CanvasDataViewer] Column data types don't match Canvas' documentation (#12)

Thank you for making this project! It was pretty easy to setup with MS SQL server 2014.

I have been working with the file_fact table. I was trying to find the largest file in our instance. I kept getting a really low number when using MAX(). Come to find out the column size is not a BIGINT, but a VARCHAR. This is fine for spitting out data to be displayed, but not for doing any math.

Now that I know, I can adjust my queries accordingly. It looks like everything is a VARCHAR.

— 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/12, or mute the thread https://github.com/notifications/unsubscribe-auth/AS3lq6E_ylybLLJ4kQtaxUfS5ktM4IQ1ks5sgWSQgaJpZM4PRYMw .