Recently, one of my employees was working to automate an excel based report. The old school was to Excel automation was involving heavy use of macros and VBA. This would execute queries or copy data around between sheets spread out on network shares and accomplish some data task. From my experience this was very brittle and prone to failures. This time around, I told by employee to use Power Pivot to accomplish the automation. There is no VBA and only SQL statements that get executed. The visualization is accomplished by using Pivot Tables and Pivot Charts. Power Pivot is awesome in that it can pull data from many different types of data sources, SQL, Oracle, SSRS, SSAS, MySQL and Excel Sheets. The trick is that in order to make your data model work well for reporting you have to think a little like a data warehouse architect. THis will enable a power user to create self refreshing data sets that can be analyzed in Pivot Tables and Pivot Charts. Let me walk you through a simple Power Pivot model that I created to track some of my personal fitness goals and health metrics.
Let’s start with the all important time table. In Data Warehousing, almost all measurable data ties to a point in time, and we model that with a Time Dimension. In my example, I have a sheet with one column a date and time. I increment each row by one hour and copied it down about 10,000 times. You then click “Add to Data Model” and you created a Power Pivot Table. To make this more useful and interesting for slicing data you need to add some columns. For Example, add the day of week with this expression: FORMAT([DateTime], “ddd”). Finally, create a key column. I learned this trick from a former colleague, you can create a easy to read key for dates and times with a little addition and multiplication. You could easily exclude that or expand in out to minutes or seconds even: (YEAR ([DateTime]) *10000) + (Month ([DateTime]) * 100) + DAY ([DateTime]).
Adding a Sheet to Power Pivot
Once you have your time situated, you are ready to start creating some tables with data to measure, fact tables in the Data Warehouse lingo. I wanted to start with three measures: calories, exercise, and weight. All of these tables started out very simple, there was a column for the date and column for the value. In the Power Pivot designer I would then repeat the creation of the Date Key on each table. Final step is to create an association between the fact tables and the time dimension using the calculated DateKey column in Power Pivot; the easiest way to do that is by clicking and dragging in the Power Pivot designer. Once that is complete you are ready to start adding Pivot Tables or Pivot Charts in your spread sheet.
A Pivot Chart
The Completed Model
Immediately after getting my first node project up and running, I started to ask how do I write and test my code? My corporate experience has taught me the importance of automated testing, how to create them in C# with visual studio and how to automate the testing with TFS, but node and VS Code is totally different. Visual Studio is an IDE and it will take care of almost everything for you. You simply need to create a new project (a ‘test’ project) in you code and you are good to start writing tests. Executing your test is as easy as a file menu click or a key board short cut. You don’t have to think about the testing framework, how your code is built or executed because the IDE will take care of that for you.
Once you have these in place you can run your tests with the test command in the command pallet or the keyboard shortcut.
This first post will be very short because Microsoft has already done a good job of writing it 🙂 The first step in learning any new tool/framework/language is simply saying “hello” or more specifically, “hello world.” Microsoft has provided an excellent tutorial
for getting up and running with Node.
npm install -g express-generator
You are then free to write in a hello world message…
you can then fire up the debugger and point a browser to http://localhost:3000 to see the hello world
From here I added in boot strap into the views and put together a nice starting point to branch out into future learnings. You can see what I have done on my GitHub
For many years I have used Dependency Injection in the projects I have worked on professionally. At work we have standardized on Microsoft’s Unity package. Unity is great; I have nothing against it, but I wanted to learn more about how these IoC packages work under the hood and the dependency injection really takes. Turns out, with C#, it really doesn’t take much. The .NET framework includes reflection and allows you to dynamically create objects without much effort. To build an object, all you need to do is reflect into the constructors, pick one to use (in my case I did a greedy search finding the largest constructor I could use) and build the object. You have to recursively build the parameters to the constructor. Because recursion is happening, it doesn’t matter how complex you object is, it can have zero or a hundred constructor parameters, and each parameter can have their own dependencies. Recursion makes for a simple and elegant solution.
My next challenge, and this is something that I have used at work many times, was to make this work in the MVC framework. MVC relies on reflection and naming convention to build your controller objects with the HTTP requests, but the magic is that MVC knows exactly where to send the request and how to build the controller object it needs. But, have you ever said, gee, I really need a database connection in this controller, so I’ll just add it to the constructor because that will make testing really easy. MVC will puke on you because it is also using a dependency injection tool under the hood, a very very simple one and only builds parameter-less constructors. Good news, almost every piece (at least everything I have ever worked with) of MVC is extendable, including its simple DI code. MVC defines the IDependencyResolver
interface for resolving dependencies, including controllers.
I recently an email from the Big Brothers Big Sisters of the Ozarks. In case you don’t know, there is a shortage of willing big brothers to be good mentors and role models to the boys in the program. In it, they identified one of the main reasons why men don’t volunteer is that “[they] have never been asked.” Well, count this as me asking the men out there to step up, volunteer, and be a good mentor and role model to these children. Poverty is a real, and often overlooked, problem in our community. For the city of Springfield over 25% of this residents live below the poverty line, and it is even worse for our children. For the Springfield public schools, over 50% (yes half) of the students qualify the free and reduced lunch program. Don’t think that the surrounding communities fair much better. For Nixa, 30%, for Ozark 30%, for Republic 37%, for Rogersville 30%, for Strafford 30%, for Willard 34%, of the high school students qualify for free and reduce lunches. Those numbers are just for the high schools, the elementary and middle schools (and the districts as a whole) are higher. In my life, education is what has opened up opportunities for me and it is education that will help reduce and eliminate the poverty problems that we have in our region. Littles that go through the mentoring program are less likely to skip school, more likely to graduate high school and more likely to attend college. I have the privilege of working for any employer that values helping the local communities that is has a presence in and backs that value up with corporate charitable giving and by encouraging employees to participate in an annual United Way payroll deduction. Concern for Community is one the seven cooperative principles. I will tell you that these things alone are not enough to solve the problems facing our community. If they were, our community’s poverty rates would not be worse than state averages. I would like to ask you to read the attached letter from the Big Brothers Big Sisters of the Ozarks; then, I would ask you to join me in helping make this community better for the next generation.
I hear this all the time