Let's build a Data Warehouse

Our reporting needs have outgrown our existing tools. Actually, that's not true. We have all the right tools but are not using them as well as we could be. It all starts with our data. Right now it all sits in our vendors schema. That works well for the transaction nature of the application, but not so much for reporting.

We have done a lot with what we have. Every night, we take the most recent database backup and load it onto a second server that is used for reporting. I take about a dozen of our core queries and dump them to tables for use the next day. We do the basics like indexes and primary keys. Or issues is that these are designed for specific reports. As the demands and needs of the reports change, we put in a good deal of time reworking the queries.

We started building our reports with Reporting Services and have not expanded our use of the tools that SQL has to offer yet. In the mean time, I have gotten more involved in the SQL community. Attending user groups, SQL Saturdays, and other Microsoft Tech Events. I have been introduced to a lot of features and ideas that I was previously unaware of. I think it's time we built a data warehouse.

I don't think our dataset is large enough for me to truly call what I am going to make a data warehouse. My database sits at 30 some gig in size. I also have a huge maintenance window. The core activity of our business ends by 5:00 pm so I have all night to process whatever I want. So my ETL process can process my entire dataset every time. In the beginning anyway. I'll deal with slowly changing dimensions later.

I want to build a star schema for my data and take advantage of Analysis Services. I want to be able to expose my data to PowerPivot and PowerView. I see a lot of power in these tools and there is no better way to learn than to jump into it. Even if I can't get my user base to use these tools, it will help me parse our data and they will still benefit.

AppLocker Audit Mode Three Months Later

I enabled AppLocker in audit mode about 3 months ago for all of our workstations. I spent about 2 weeks checking the logs and adding rules. I put it on the back burner to take care of some other things and almost forgot about it.  I ran those scripts I posted previously to check up on my workstations and things look fairly clean. Here are a few things that stand out to me.

There are a handful of things that run out of the user's profile and ProgramData that I need to be aware of.  I see a Citrix and WebEx client pop up on a few machines. Spotify also jumps out in the list. I didn't realize how many of our users used that. I also see a few Java updates being ran from the temp internet files folder. Nothing too crazy here that would have impacted much. I expect it would have been a hand full of panic calls from people that could not get some web conferences to work.

I did find a custom app that we wrote sitting on some desktops that would have broke. That would be been a big deal. I think I will just sign those apps and place them in the Program Files folder. I can use these logs to track down these users. This app is just an exe so there is no installer or registry thumbprints to look for.

The last group of findings were just a hand full of special machines that had something installed to a folder on the root of the C: drive. I could guess exactly where these machines were based on the names of those folders. I will handle these case by case. I am tempted to just give them local exceptions instead of baking something into the main policy.

Now that we are aware of these things, we can do things right going forward. Primarily loading everything into the program files would be the most help. I plan on letting this go for another several months and see what else I pick up.