Data On Wheels – Merging Two Great Minds

I am very excited to announce that my daughter, Kristyna Hughes, will be joining the Data On Wheels team. Our fates and paths to this point seem parallel though not sequential. Both of us went to college and studied cultural anthropology. This turned out to be a great foundation for working with customers in our respective technology fields. If you have followed me for a while, you know that my career has focused on data and analytics for the past 20 years. During that time I worked for XATA Corporation which was a transportation management company. We were a cutting edge, SaaS technology company that specialized in electronic driver logs. I created my blog while working there, thus Data On Wheels. I have since went back into consulting but kept the name. Fast forward to today, Kristyna started her career in logistics which led her to her current employer, GlobalTranz. While there, she created a couple of reports which were well received and got noticed by the business intelligence team. She has since helped them grow their Power BI practice and deliver awesome reports. I am very proud of the work she has done and look forward to her joining Data On Wheels as a co-contributor. Now, let’s hear from her.

Kristyna and Steve at Kristyna’s Graduation from Biola University

Kristyna here, I am thrilled to be joining my dad in the BI world and in writing up the lessons learned along the way. Like he said, I have been deep in the world of Power BI from administration to building the executive morning report. I believe people in the business are our best assets as data analysts, so I appreciate learning my logistics knowledge through experience and continue to learn from my colleagues in the field. Our mutual background with cultural anthropology allows us to get to the heart of problems efficiently through discussions with stakeholders to bring them valuable insights from their data. While not all companies literally have their data on wheels like logistics, data can turn the wheels of profitable change for any company.

Buffalo Trace Distillery, Frankfort KY

One other thing my dad and I enjoy together is good whiskey from rye to bourbon to scotch. Look for some of our favorites in the Fun Info page as we sip and savor some soul-warming whiskeys.

Azure Every Day, Azure Data Week, and More Azure

As you likely know, I am a Principal Consultant at Pragmatic Works. This year we have been doing some great things with Azure for our customers. Beyond that we have produced a lot of content and have an event coming up that I want to talk about. So, one step at a time.

Azure Every Day

We are producing daily videos covering a variety of Azure topics. They are typically 3-5 minutes long and you can read the content in the Pragmatic Works blog as well.

Check out the YouTube channel here if you want to see all the videos we have published so far. I am going to republish my Azure Every Day blogs and videos here as a lead up to the next exciting event.

Azure Data Week

Get ready for the only 100% virtual Azure Conference – October 8 – 12. Five days, 25 sessions, all on Azure. Join Pragmatic Works  as we launch this new conference. The cost is only $29 and you will have access to the sessions for year. Microsoft and Pragmatic Works will be speaking on topics such as Power BI, Azure Data Factory, Security in Azure, and Azure roadmaps.

More Azure

So if you want to check out what we are doing in Azure, have a look at Pragmatic Works and our team. We are doing more events with Microsoft over the next few months including regional training sessions on SQL Managed Instances, Azure Data Bricks, and Azure migration strategy. Ping me directly or reach out to Pragmatic Works if you are interested in our events or joining our team.

SQL Saturday #492 Follow Up – A Window into Your Data

Thanks for attending my session on window functions in TSQL. I hope you learned something you can take back and use in your projects or at your work. You will find an link to the session and code I used below. If you have any questions about the session post them in comments and I will try to get you the answers.

Questions and Comments

1. Does RATIO_TO_REPORT exist in SQL Server? It is in Oracle.
   • Currently this function is not available in SQL Server
   • Here is the equivalent functionality using existing functions in SQL Server:

     • OrderAmt / SUM(OrderAmt) OVER (PARTITION BY OrderDate)

     • This example can use the source code I have referenced below. It uses the current value as the numerator and the sum by partition as the denominator. While not a simple function, the equivalent is still fairly simple using window functions to help.
2. Demo issues with Azure SQL Database
   • During the session I ran into an issue with Azure SQL Database. It turns out that the following two functions are not supported there.
     • PERCENTILE_CONT
     • PERCENTILE_DISC

Slides, Code, and Follow Up Posts

The presentation can be found here: A Window into Your Data

The code was put into a Word document that you can get here: TSQL Window Function Code

This session is also backed by an existing blog series I have written.

T-SQL Window Functions – Part 1- The OVER() Clause

T-SQL Window Functions – Part 2- Ranking Functions

T-SQL Window Functions – Part 3: Aggregate Functions

T-SQL Window Functions – Part 4- Analytic Functions

Microsoft Resources:

• OVER Clause: http://msdn.microsoft.com/en-us/library/ms189461(v=SQL.110).aspx
• Analytic Functions: http://msdn.microsoft.com/en-us/library/hh213234(v=sql.110).aspx

Five Years, A Quiet Quarter, A Look Ahead to 2016

Five Years of Blogging

From

to

My blogging story started on December 7, 2010. I have now had a blog for over 5 years. I want to thank all of you who have read my blog and interacted with me through it. You have seen me change the theme once and do a number of series. Here are some highlights from the past five years:

Top 5 Posts All Time

1. Adding Top 10 Charts to Power View Which Honor Filters
2. Simple batch script to generate XMLA and deploy SSAS DB
3. T-SQL Window Functions – Part 1- The OVER() Clause
4. Exploring Excel 2013 for BI Tip #14- Sparklines and Pivot Tables
5. O, There’s the Data- Using OData in SSIS

Top Series All Time

The Excel BI Tips series has changed it name a couple of times. However, this tip series still rings true even today even as Microsoft invests in other tools. Look for some more Power BI content this year, but this series will continue to have updates. Also, look to see some Excel 2016 topics added to the list as that release becomes available. Here are the top ten tips from the series:

1. Exploring Excel 2013 for BI Tip #14- Sparklines and Pivot Tables
2. Exploring Excel 2013 for BI Tip #15- Locking Slicer Position
3. Exploring Excel 2013 for BI Tip #10- The Data Bar
4. Exploring Excel 2013 for BI Tip #4- Adding a Slicer
5. Exploring Excel 2013 for BI Tip #5- Cleaning Up Slicers
6. Exploring Excel 2013 for BI Tip #9- Adding Calculated Members
7. Exploring Excel 2013 for BI Tip #8- Adding Calculated Measures
8. Exploring Excel 2013 for BI Tip #13- Connecting Slicers in Pivot Tables Sourced from a Power Pivot Model
9. Excel BI Tip #18- Using CUBE Functions to Break Out of Pivot Tables
10. Excel Tip #21- Hiding Scrollbars in Excel Services Web Part

Tributes

A tribute is an expression of gratitude or praise. A couple of years ago, I started a series about individuals who have impacted my career. I do this as a tribute to my father-in-law, Ed Jankowski who passed away in December 2009. Check out my original post about him and his impact on me being in software development today.

• 2011: How I Got Started in Software Development-A Tribute to Ed
• 2012: Cultural Anthropology and Business Intelligence–A Tribute to Dr. Thomas C. Correll
• 2013: Work Ethic and Family – A Tribute to My Parents
• 2014: She Taught Me to Code–A Tribute to Sheila, My Wife

Some Stats

I want to thank everyone again for taking time to check out my blog. Here are some stats that I thought were cool and decided to brag about here:

• 2011 daily average: 9 – 2015 daily average: 162
• 156 posts
• 135,000 views
• Best ever views in a day: 584

Thanks again for checking out my “help” library. As I noted in one of my posts, I blog to not forget and to pass along what I have learned. The key for me is that I do it when I can about topics that interest me.

A Quiet Quarter

The last statement holds true here. I have had a very quiet end of the year. I had blogs which followed up sessions, a practice that I intend to continue, and one BI Tip. November and December were quiet as my job and family took precedence as Pragmatic Works closed out the year strong and we had holiday activities at home including getting my two kids in college home. Well, the dust has settled so I am getting a few more posts published now. Look for the Minnesota SQL Server User Group and Minnesota BI User Group follow up posts this week.

Looking ahead to 2016

After a busy year last year, I am looking forward to having some new opportunities to write about Azure, SQL Server 2016, and other technologies I have not even seen yet. Are you excited for what is coming? Let’s have a great year working with data and analytics.

Boston BI User Group Meeting–Dashboard Design with Microsoft: Power BI vs Datazen (10/13/15)

Thanks for joining Anthony Martin (@SQLMartini) and I at the Boston BI User Group Meeting in October. During the session, we demo’d and built dashboards in Power BI Desktop and Datazen Publisher.

Power BI

www.powerbi.com

Couple of thoughts from our demo:

• Power BI is a way to get data, model data, and visualize it
• Power BI Desktop allows you to work with data on your PC
• Power BI is experiencing a lot of change – for example over 40 changes were applied in September 2015
• Power BI has an open API that allows you to customize the experience

Datazen

www.datazen.com

Couple of thoughts from our demo:

• Design first scenario – make it look good, then shape data to match
• Datazen publisher allows us to create dashboard for many different profiles
• Datazen handles custom shapes

Additional Training from Pragmatic Works

• Free Power BI Webinars
  • Creating Solutions with the New Power BI Desktop
  • Where Does Power BI Fit in Your Data Architecture-
  • Automating Power BI Creations
  • Introduction to the Power BI Designer
  • Power BI- Data Refresh and SSAS On-Premises Connectivity
  • Power Pivot 102- Advanced Power Pivot Modeling
  • Powering Up HDInsight with Power BI
• Free Datazen Webinars
  • Intro to Datazen- Dashboard Design and Development
  • Intro to Datazen- Setup Your Datazen Environment

Questions from the Session

Can you use links in Datazen to support drillthrough?

Yes. You can find more information here: Drill-throughs to Other Dashboards or Custom URLs.

Power BI API Development

You have the ability customize Power BI. Check out the contest winners to get some ideas of what is possible.

Details about the solutions can be found on the Power BI blog: http://community.powerbi.com/t5/Best-Visual-Contest/con-p/best_visual_contest/tab/entries.

You can find more about custom visuals here: https://powerbi.microsoft.com/en-us/custom-visuals.

Thanks again for joining us.