Advanced Toolware's Solutions
(UMRA) - User Management Resource Administrator: Download
(ESSOM) - Enterprise Single Sign-On Manager: Download
(SSRPM) - Self Service Reset Password Management: Download

It's been awhile since I've talked about how UMRA can integrate with most Student Information Systems (SIS), one in particular is Pearson PowerSchool. I've also noticed I've been getting a lot of hits related to this topic, and how UMRA integrates with it quite easily. So hopefully this post will give you a quick oversight on what are some of the capabilities of UMRA automation your PowerSchool data.

Test Environment
UMRA Version: 10.5 (Build 1633)
OS Version: Server 2008 R2

Now before I begin, keep in mind this blog will just cover some of the features UMRA can do with your PowerSchool database. I will try to focus on some of the most requested item's I've implemented when doing UMRA integrations with PowerSchool. Ok, so some of you might be wondering "How do you connect to your PowerSchool Database?". This is quite simple, UMRA has full ODBC connectivity, so connecting to your PowerSchool database, and getting the data is simple. Now wait, you might be thinking "our PowerSchool database is not hosted with us...so we cant connect directly to it". If this is the case, its still ok. In this instance, who ever hosts your PowerSchool database instance, can provide you with limited connectivity, or even give you a .csv dump of all the student data. Yes, UMRA can hand .csv files as well, great right! Ok, back on topic, below is a a quick overview on how to connect your UMRA to your PowerSchool database instance.

Now keep in mind, your server name, etc. will be different. However, you will notice that the UMRA database wizard is very similar to other database wizards you've gone through before. I am using the "Generate generic table" UMRA action. To get to the UMRA database wizard screen below, follow these steps.

Step 1: Drag and drop a "Generate generic table" UMRA action into your script.
Step 2: Click Configure...
Step 3: Select "Database query" from the Table type drop down menu.
Step 4: Click Database tab, then Configure...
Step 5: Select "All other databases" from drop down menu, then click Database connection tab.
Step 6: Click Configure button.
Step 7: Click on "Microsoft OLE DB Provider for SQL Server", then click Next >> button.
Step 8: Enter in your server name, auth, and database, then hit Test Connection button. If you have correct access etc. you will see a "Test Successfully" prompt, if not, recheck your Database Connection information.



Ok perfect, now that we have the connection to your PowerSchool database within UMRA, we can now setup the query to pull the data you want out. Now, depending on what you what data you want from your PowerSchool database, your SQL query will vary then mine. For my example below I've created a SQL view called "my_view". To get to the screen below, simple press "Ok" button on your Database connection wizard, and it should take you back to the main "Setup generic table" window. Click on the Query tab, and enter in your query.



Alright we are almost there, now we have the connection to your PowerSchool database within UMRA setup, and we have your SQL query setup, next step is to use the "Run test" tab to see if we pull back records. Click on the "Run test" tab, and then click "Test..." button. If your query worked, you should see a table of data, or if you had a problem with your SQL statement, it will pop up the error in an alert box.

So assuming everything worked for you, your now most likely asking "what can i do with this data?". Now, again I wont go into everything you can do with the data, but i will cover some of the most functions I've done when using PowerSchool database information.

MASS User Creates
One of the most requested items I get is "can you create users in bulk with the PowerSchool data?" Yes, we can loop through each of the records one by one, and create the account in Active Directory, Create Home Folders, Create Google E-mail Accounts, Create Exchange Accounts, and Provision Group Permissions. That is just a tad of what UMRA can do, keep in mind that you can also create accounts in other downstream systems such as NutraKids, Lunch Systems, Library Systems and the list goes on and one.

MASS Edits
Now how about if you already have students in Active Directory but you just want to sync there data? Easy, since UMRA can both create, and edit objects in Active Directory, you can check to see if the student is in Active Directory, if they are compare the data from within your PowerSchool Database Data, against what is in Active Directory. For example. If a user student changes his name, or changes grades, UMRA can compare the Active Directory information and see that its not the same as the PowerSchool information, and thus executing a work flow, such as moving a user to a new OU, removing old group memberships and reapplying new group memberships, moving home folders, and so much more.

MASS Disables / Retires
What happens when you have a graduating class, and these users are still in your Active Directory? Again, this is quite simple with UMRA Automation since it can move these students to a particular OU, time/date stamp the account, and even download the students e-mail to a .PST file (if you use exchange),and move it to secure admin share. with he time/date stamp, we can keep these accounts for XX days until you want to fully delete the account.

So there you have it, a quick and simple way to connect to your PowerSchool Student Information Systems database via UMRA. If you have any questions feel free to post, or send an e-mail.