Voice of the DBA

Category: Blog

  • Grabbing Fitbit Data

    Recently the T-SQL Tuesday topic was Power BI. A cool topic, and an interesting application.

    I saw Rob Farley build a dashboard based on his Fitbit data. That was intriguing, so I went to get my data. I’ve worn a Fitbit for a couple years, recently having lost it. However, when I went to export my data from Fitbit, I found this.

    2016-02-17 10_46_02-Fitbit Data Raw Export

    Notice two things. One is the top announcement that my data belongs to me. Then notice the bottom left note that I can get 31 days of data.

    Jerks, but feel free to substitute another word yourself.

    Then I found this Fitbit Data Export site. I connected and ran this, which grabbed my data. I appreciate there’s a Github link with the source code, though I have no idea of what’s in Github corresponds to what’s running in Azure. After reading The God’s Eye View, it’s a little nerve wracking to use a service to connect somewhere that I have data stored, but Fitbit exposes some of this publically anyways, and besides, it’s not critical data for me.

    In any case, I had a CSV of my data, which I am now going to upload into Power BI and play with it. Adding a CSV is straightforward.

  • SQL Saturday Headshots

    This past weekend was SQL Saturday #478 – Albuquerque, and I was happy to have the chance to attend again. I was at the first two, missing last year with another event. It was good to go back, and carpool down to Taos for a day of skiing beforehand. Three of us from Colorado went down( myself, Jason Horner, and Reeves Smith), meeting Tim Mitchell, Ginger Grant, and Amy Herold at Taos for a warm day of skiing. Not the best, but good company and a lot of fun.

    Taos2016

    Saturday was another well run event. Thanks to @keith_tate, @coffegrl, @ChrisHyde325 for all the work they put in. The event went off without a hitch, and it seemed that people really enjoyed the day of learning.

    Jamey Johnston was there again, which I was glad to see. Jamey and I were at the Austin event a few weeks ago, and Jamey was taking pictures of the speakers. I got distracted and missed my chance, which I regretted. Fortunately, Jamey set up his equipment in Albuquerque and I got my chance again.

    I took advantage of it.

    funheadshot

    Representing the Denver Broncos, US football champions, and having fun. However, I was serious at least for a few minutes.

    newheadshot

    All in all, a fun weekend and a good time.

    Now I just need to get back to work and tackle a few of the things I learned while watching some Machine Learning, Power BI, Always Encrypted, and more. It’s a good time to be a SQL Server pro.

  • Adding Minutes in Excel

    A short one, but I found myself wasting time recently. I use an Excel sheet to schedule some items out each day. It’s not my choice to do this, but the service I use requires this to not manually set time for each event.

    Tl;dr: Add minutes/1440 to the date.

    The format looks like this:

    2016-02-05 09_42_25-Settings

    I’ve been changing the dates each day to the next day, which involves picking the cell, hitting F2 (or clicking the mouse), moving to the day and editing it to increment. In this case, I’d go to the 10th for the next set of scheduled items.

    Manual, time consuming, and when I saw this cartoon, I knew I needed to do something else. It’s a few minutes of my day, and really, it’s 12 hours over 5 years.

    is_it_worth_the_time

    But it’s incredibly annoying and tedious. It makes my blood pressure rise, and I delay the work because of it.

    I spent 5 minutes considering a few things. I knew I could do any of these, but some would require more work than others, and perhaps not be worth the time.

    • Use a macro to change each entry
    • Get my source system to generate an Excel sheet in this format
    • Write OLE automation to generate a new sheet

    In the end, the simple solution was just learn to add time to a base date. If I take the first entry, I can see that I’m really adding minutes (or hours) to this one. So I decided to do that. In this way, I still need to edit the first cell, but then all the rest will work.

    However I needed to add minutes to an Excel entry. I wasn’t sure how, but a quick Google search gave me the answer from Superuser. Because of how Excel stores dates, I really need to add minutes divided by 1440 to the date.

    So my first date is: 02/09/16 14:05. To get my next entry, which is 02/09/16 14:22, I need to add 17 minutes, but the forumla looks like this:

    2016-02-05 09_50_19-Settings

    As you can see, that’s the right time. I now had to spend about 10 minutes setting the formula for each time, which is a matter of calculating the minutes for each new time. It’s not too hard, and my ten minutes spent here won’t pay off in time for a long time, especially after this blog post, but it will reduce my stress quite a bit.

    Every day.

  • The Next DLM Demo Webinar–Feb 23

    A quick note that I will be presenting a webinar on Feb 23rd at 10:00am MST. That’s noon EST and 5:00pm GMT. You can sign up here if you’d like to watch. This is similar to other DLM webinars I’ve done that look at the Redgate stack of products that help you develop database software in a smoother fashion.

    I am changing things up a bit. This time the stack is:

    • Git – version control
    • Team City – Continuous Integration or Build server
    • Octopus Deploy – Release Management tool.

    As with the other webinars, I’ll show how I track the version of the database in each environment, then show how to make changes in SSMS, commit them to my VCS with SQL Source Control, and then deploy them in an automated fashion across various environments. In this case, I’ll have this flow of environments

    • development
    • integration
    • test
    • acceptance
    • production

    It’s an impressive demonstration. As I’ve watched this grow at Redgate, I am impressed with how well things work. I wish I’d have had these tools when I built database software earlier in my career.