I am starting a series of posts about the PowerSchool SIS, which I plan to also cross-post these on PowerSource (their support site). I have found that I have a love/hate relationship with PowerSchool. It does some things REALLY well, and allows far more customization than nearly any other SIS system. But it also does a lot of things in a strange way, at times has poor documentation, and is built on a legacy system that has some design flaws (which was OK when the database was small, but now causes problems). These posts will share what I have learned from my experience with PowerSchool, hoping that it will help out other schools.
I will probably post only every so often, as I don’t have a lot of spare time. But I figure, I need to document what I find someplace, so that I can reference it in the future for myself; and so I mine as well share it with the world.
I often work with really big Excel spreadsheets (>500,000 rows) to do various types of data analysis. And often I’m grabbing data from databases (how else are you going to get such big spreadsheets???) And a common thing that can happen, is that numbers will be copied in as being in a text format, and many math and lookup formulas won’t work on them. One tempting way to convert these is to use a “Smart Tag” where, when you select all the numbers that are being treated as text, you get an ! in a diamond, that you can click on, and it gives you the “nice” option to just “Convert to Number”. If your spreadsheet is over 10,000 rows, DON’T DO THIS. It will take forever to complete… (ok, not really forever, but possibly several days to complete.) Instead you should do the following:
Yesterday I shared about the problem with too many politicians being sociopaths, or at least being power hungry. Some people may tell me I was just stating the obvious. But, I also talked about some of the elected representatives that I have worked with who I know are good people. And this week, I met in person Delaine Eastin, and had a chance to talk with her briefly, and also talk with several people who have known her for a long time, and I can say confidently that she is running for governor for the right reasons, and is not a corrupt politician.
The Onion’s satire often hits close to the truth, and when it had the headline “Authorities On Alert As Hundreds Of Crazed Sociopaths Enter Congressional Chambers“, it was fairly close to the truth. I say this because over the past several years, I have worked with and been around a fair number of elected officials. And there is the simple fact that power draws many people who want power to it, hence how we got our president (although I’m not sure he is a sociopath, but he has clear psychological and ethical issues).
But there are exceptions.
There are real people who are working to do good in the world who believe in America’s system of representative democracy and the ideals set out in our Constitution and Declaration of Independence. And I’ve had the honor to work with elected representatives like Rebecca Sandoval, Bob Bastian, and Darrell Steinberg. And now I have another person to add to my list of truly good people: Delaine Eastin. And I will post more tomorrow about this.
Why any Emergency Alert System that has a Smartphone App Panic Button that only uses the Internet is a Death waiting to Happen
Our school has been researching various emergency alert systems so that if an emergency happens, we have a manner that staff can quickly and reliably communicate to our administration that there is a problem, and that we can then quickly send out messages as appropriate to our students.
Most emergency alert systems have a smartphone app with some form of panic button. This is great, except for the fact that these systems all only use the phone’s internet connection, and if that internet connection is not working, then they fail.
Here are two quick scenarios that demonstrate the problem. The first is very real. On our campus, we are in a neighborhood known to have drive-by shootings. My smartphone most of the time is connected to our wi-fi, but as I move around the campus, there are places where my phone has such a poor wi-fi connection that nothing goes through, but the connection is not so bad that it switches to my phone carrier’s internet. And one of these parts of campus is where we have had students get into a verbal altercation with some neighbors, and there were threats of gun violence. While nothing happened at that time, we know it could. And if I was there, trying to de-escalate the situation, but I wanted to have something I could quickly do on my phone to say there was a problem, the panic button would fail me with any system that only used the Internet as its communication.
Another scenario that is quite real is when someone is not by any wi-fi and they have no Internet connection at all, and they have a problem that they need to hit a panic button on. Again, this would put that person’s life at risk, because no matter how good the rest of the system is, this weakest link of the communication chain would cause huge problems.
So what is the solution? It is actually rather simple. Emergency Alert Systems should have a backup of using SMS (text messaging). We know that during Hurricane Katrina, while most cell functions were not working, SMS still was. This is described well in the article “SMS does SOS” (https://fcw.com/articles/2006/04/03/sms-does-sos.aspx)
SMS only allows for short messages, but I’m sure a well-engineered solution could encode a client to server set of messages that could be sent out (probably looking like gibberish to humans), if the internet connection fails. It could even send out a human readable version also, if the system was well designed. And it could include GPS data, as long as the app was well designed.
Yesterday, Delaine Eastin officially announced her candidacy for governor. In an era of short-term thinking, her candidacy is more important than ever.
Why? Because as the first President Bush once said “Think about every problem, every challenge, we face. The solution to each starts with education.” And Delaine is the only candidate to see that the problems of today, which are real, can only be solved through long-term thinking, which focuses on education.
One of the great new features of Excel 2016, is that it has Power Query and Power Pivot built-in. This makes connecting to a database a LOT easier, where a database table, view, or query will get imported into an Excel table, and then new columns can be added to that table with formulas. For folks like myself who are good with Excel formulas, this is often easier (and sometimes more powerful) than trying to have the SQL query itself do calculations.
But, I had run into a problem, which was that on one of my spreadsheets, every time I would refresh a query, all the new data from the query would not have the formula column automatically fill in like I was expecting. And since I had a fairly complex spreadsheet, if I forgot about this problem, I would get bad data and reports…. So I needed to fix it.
When Franklin D. Roosevelt said “The only thing we have to fear, is fear itself” in his first inaugural speech, he was talking about how fear can paralyze us. But, during this election, it seems that fear has led to a different primal reaction, that of tribalism, anger, and in some cases, even hate.
While I believe that my fear is “rational”, in that Trump shows many signs of having the temperament of a despot, and that it is scary how most Republicans have just gone along, and that if he wins today, there is little backstop to him being able to abuse his powers.
So which presidential candidate would more likely be dictatorial in their presidency? Evidence shows that the Clintons overall followed the rule of law, and worked with the legislature during Bill’s term in office. Trump on the other hand continually breaks or skirts the law (like his dealings with Fidel Castro in Cuba, right after saying he would not do anything in Cuba), says “I alone can fix this problem”, and has all the personality traits of a dictator, as Trevor Noah has humorously, yet scarily pointed out. His father told trump as a kid that he was a “king and a killer”. Will we let this come true? Our founding fathers fought to be free from a King. We don’t need King Trump. (And for the Hillary haters: I respect your freedom of speech, but I need evidence and not anger to change my mind)
Using Match and Index on a Pivot Table: Sometimes Excel has better Performance with “Old School” Formulas
I am a huge fan of pivot tables, and often use them to help transform raw data into a different table, by “mirroring” data from a pivot table, where I have a table to the left of the pivot table, and I want to have the table show the same value the pivot table has, or possibly transform it with a formula. Often you can just choose the cell reference that the pivot table has and you are done… But sometimes the pivot table may have multiple columns, that vary, and you wish to do some sort of lookup on the pivot table… Traditionally, to do this, it would require creating a GETPIVOTDATA formula. These have always been a bit complex, and now with PowerPivot being built-in to Excel 2016, they are even MORE complex. This is why some people choose to use cube functions instead, which can now be done on PowerPivot tables…. But I have tested both of these, and they are very slow when you have a lot of rows. So, I have gone back to an old school method of using the MATCH function (with absolute references) to determine which column a particular heading is in, and then use the INDEX function to select the value from that heading. This old school trick turns out to be a LOT more efficient than using GETPIVOTDATA, and thus I thought I should share this with any other Excel nerds who read my blog.