Your Federal Quarterly Tax Payments are due April 15th Get Help Now >>

Using ODBC with Excel by xiangpeng

VIEWS: 15 PAGES: 14

									              Using ODBC with
              Microsoft Excel®

                   - Bob Elenbaas
                     Lake Orion HS




Using ODBC with
 Microsoft Excel
Who am I?

 - teacher of computer programming and
 computer applications
 - PowerSchool support for secondary buildings
 (attendance, grading, scheduling)
 - assist with data collection and analysis
 - high school tech support



Using ODBC with
 Microsoft Excel
Lake Orion?

-    Oakland County – just north of Pontiac
-    8300 students / 650 teachers
-    7 elementaries, 3 middle schools, 1 high school
-    on PowerSchool for 2 years (after CIMS and
     Centerpoint)




    Using ODBC with
     Microsoft Excel
Goals:
• To examine PowerSchool’s data structure
• To see an ODBC connection to
  PowerSchool in action
• Discuss advantages and disadvantages to
  using ODBC
• To look at how to use ODBC with Excel
• To look at accessing PowerViews

Using ODBC with
 Microsoft Excel
ODBC = Open Database Connectivity
 provides a standard software method for
 using database management systems.
 The designers of ODBC aimed to make it
 independent of programming languages,
 database systems, and operating systems.


Using ODBC with
 Microsoft Excel
Why use ODBC?
• Making direct connection to the database
  – fast; nothing inbetween
• Allows use of existing tools (like Excel,
  MSQuery, Access) to look at the data
• Allows use of SQL (structured query
  language


Using ODBC with
 Microsoft Excel
• Gives you the control




Using ODBC with
 Microsoft Excel
Data structure of PowerSchool
• To see a quick view of data structure, type
  ?ac=structure after the URL on any
  PowerSchool screen

• Full data dictionary available on
  Powersource


Using ODBC with
 Microsoft Excel
Install ODBC
• Get instructions from PowerSource and
  PowerDataSolutions.org and follow closely




Using ODBC with
 Microsoft Excel
Quick Export, DDE, or ODBC?
• Just a few student fields? QuickExport
• Data from other tables or fields not on
  QuickExport field list?       DDE
• More complicated needs? ODBC




Using ODBC with
 Microsoft Excel
Examples
1) Get student name, gender, and grade
   level for one building

  - using MSQuery in Excel
  - using SQL in MSQuery in Excel




Using ODBC with
 Microsoft Excel
2) Find all students with a D or E.

   Find all seniors with an E.

   Find all students with a D or E in math.



Using ODBC with
 Microsoft Excel
3) Access PowerView tables to see
   distribution of students by grade level
   and ethnicity.




Using ODBC with
 Microsoft Excel
4) Gather attendance data for select date
   range.




Using ODBC with
 Microsoft Excel

								
To top