Jeff Pries

Business Intelligence, SQL Server, and other assorted IT miscellany

Category: Business Intelligence (page 2 of 2)

SQL Saturday Roundup: #477 – Atlanta, GA (BI Edition)

SQL Saturday Roundup: #477 – Atlanta, GA (BI Edition)

This past Saturday marked a great landmark for Atlanta — the first “BI Edition” SQL Saturday, and, for many of us, our first SQL Saturday of 2016!  Atlanta has been hosting a regular SQL Saturday event for many years now, always with tremendous attendance.  Based on the amount of interest in the regular SQL Saturday (usually around May of each year), it was great to see a BI focused edition launched.

With the regular event in the Spring, having this event in January was a great way to space out the two Atlanta events probably about as equally as they could be.  And, for a first event, it seemed to be a tremendous success with a registration wait list and around 300 in attendance.

The event was held at the local Microsoft facility in Alpharetta, GA, where the monthly Atlanta MDF user group meetings are held.  All told, the facility was a pretty good choice of venue (and definitely a convenient location), but suffered from some overcrowding.  The facility had about half the sessions in roughly classroom sized rooms and half the sessions in much smaller conference room sized rooms.  While these smaller rooms made for an interesting and more intimate setting, they ultimately filled up very quickly.

Unlike many SQL Saturdays, the event kicked off with an opening keynote and presentation in the large room (multiple rooms joined together technically).  I enjoy it when a SQL Saturday begins with some sort of all-attendee opening remarks, it provides nice symmetry to the event (which always has a final closing remarks session), so hopefully more events will adopt this.


Dandy Weyn presenting the opening remarks at SQL Saturday Atlanta 2016, BI Edition.

Continue reading

Importing Cisco Call Data into SQL Server with SSIS

cisco_phoneRecently, I had the need to analyze phone call data to answer questions such as how many phone calls were received in a given day and how frequently voicemail answered instead of a live person.  In this scenario, I was fortunate enough to have a fairly accessible phone system to work with — a Cisco UC520.  While this guide is specific to working with a Cisco UC520 device, most Cisco phone systems (the UC series or anything utilizing CUE/CME) should be pretty comparable.

So, you want to be able to analyze Cisco call data?  Well, you’re in luck!  There are three major steps to this process:

  1. Extract raw call data from the phone system
  2. Capture the exported data and interpret it
  3. Insert it into SQL Server and perform reporting

This won’t be a complete step-by-step guide, but I’ll try to hit all the high points and am always open to questions.

Continue reading

The Button


The Button.  Originally an April Fools Day joke / prank / experiment / curiosity, this simple and strange creation has become an internet phenomenon with countless resources being invested in it.  On 4/1/2015, Reddit announced a new feature, The Button.  The concept is very simple.  There is a button.  It controls a shared timer which is counts down from 60 seconds.  Pressing the button resets the timer back to 60 seconds (for everyone) and it restarts the countdown.  Anybody with a free Reddit account created before 4/1/2015 is allowed to press the button, but only once, ever.  That’s it.

And that is all there is to it.  A pretty simple concept.  Something that probably should’ve lasted a couple of minutes, maybe an hour or two, and then been gone, right?  Nope!  Instead it’s turned into a phenomenon which has been running perpetually for over two straight weeks with over 750,000 button presses thus far and no end in sight.

The is one additional twist to the button.  When you press the button your one and only time, your account is branded with the time, and a color, indicating what time you pressed it.  And others can see this.  This little twist has allowed for the possibility of an incredible amount of interesting data visualizations surrounding the enigma that is, The Button.


The circumstances above make The Button a really interesting data source.  It produces a constant stream of data (making real-time analytics valuable) and that data is time stamped and color coded based on how much time was remaining at the time of the press making for some very interesting visualizations.

Continue reading

What is Business Intelligence (and Business Analytics)?

bar_chart_sketchThis is a question that seems to come up in one way or another quite frequently. And it makes sense. Being fairly new (on the main stage), these terms are not well defined in the public consciousness (or sometimes even in the heads of those in the field) and so there can be a lot of confusion. There are some out there that think that both are simply creating pretty charts somehow relating to data in some way and there are others that know that the rabbit hole goes far, far deeper…

Business Intelligence and Business Analtyics are both disciplines inside of a wider field of being a Data Professional.  Both Business Intelligence and Business Analytics deal with deriving meaning from data.  The differences between the two tend to stem from the types of questions each attempts to answer and the tools and processes used to answer those questions.


Business Intelligence

Business Intelligence is the infrastructure.  Business Intelligence includes the gathering, storing, and summarizing of data as well as making it available in the forms of reports, dashboards, KPIs, raw queries, and other visualizations to answer questions about the past and present which are commonly used in decision making.  Some of these questions might be:  What happened?  When did it happen?  What did it happen to?  How much did it happen?  Business Intelligence catalogs the past and makes it available for future insight.


Business Analytics

Business Analtyics is the crystal ball.  Business Analytics makes use of existing infrastructure (such as data gathered in Business Intelligence) and aims for deeper insight into the data, frequently dealing with predictive analytics about the future.  Why did it happen?  Will it happen again?  What will happen if we make a change?  What trends can be expected for the future?  Business Analytics makes use of data to provide predictive capabilities.

Continue reading

Choosing the Right Chart Type

Choosing the Right Chart Type:  

On Monday night, I attended our monthly Atlanta Business Intelligence user group meeting.  The speaker this month was the amazing Jen Underwood  (B | T) presenting on the topic of Data Visualization Best Practices.

As anybody who is familiar with building the presentation layer for a set of data knows, choosing the right method to represent that data can be the most challenging and important part of the entire process.

Whenever I am designing a new report, I will list my requirements and brainstorm a list of what I want to convey with the report.  Once I’ve determined what message is to be conveyed, I’ll sketch out mock reports on paper until I’ve gone through a few iterations and gotten an idea of what I’m going to implement.

During the process of sketching out a mock report, the topic of deciding what types of charts to use comes up.  In Jen’s presentation on Data Visualization Best Practices, she pointed out a very handy resource — a chart of charts by Andrew Abela, to help determine which type of chart to use and when:

A great resource to print out and decorate your office wall!

Another great resource is the Periodic Table of Visualization Methods by Ralph Lengler and Martin J. Eppler which is an interactive resource which shows an example of each visualization method by hovering the mouse over the “element.”

There are many more great resources out there, including many by “the Moses of Data Visualization,” Stephen Few which I’ll cover in future posts.  Happy charting!

Jen Underwood presenting Data Visualization Best Practices at the Atlanta Business Intelligence April Users Group.

SQL Saturday Roundup: #215 – Jacksonville, FL

SQL Saturday #215 – Jacksonville, FL:  

I’d been looking forward to this event for a few months now and it had arrived at last — SQL Saturday Jacksonville!

Part of the reason I’d been looking forward to this event (in addition to the great lineup of speakers and sessions) is the pre-con session for which I attended:   “Building a SQL Server 2012 BI Platform” by Brian Knight (B | T) and Devin Knight (B | T).

Now, a day long session which goes through the entire SQL Server 2012 / Sharepoint 2013 Business Intelligence stack in a whirlwind is a great thing, but when its taught by both Brian and Devin Knight?  Sign me up!

The pre-conference session on Friday started with a discussion of source data — an OLTP data source.  From there, we covered the basics of data warehouse design and then dove into designing a traditional business intelligence solution — a data warehouse fed by an ETL process.  With our data in place, we followed a traditional BI approach and built a multi-dimensional SSAS cube.  To contrast, we also built a new SSAS tabular model based on a PowerPivot Excel workbook.  With our data models in place, we finished out the BI stack by exploring them with SSRS, PowerPivot, PerformancePoint, Power View, and a little bit of Geoflow.  An enormous amount of material, it was great going through the entire stack from start to finish with such knowledgeable teachers.

The next morning, after a quiet evening to rest my brain, the main SQL Saturday event was held on the University of North Florida campus.  The venue for the event provided ample space, with a couple of larger auditoriums as well as a number of standard classrooms.  The weather for the event was a sunny and beautiful spring Florida day — which was important as lunch (which was provided free for the event) as well as mid-session breaks were spent outside.

Continue reading

A Day of Data Warehousing…and Pie!

A Day of Data Warehousing…and Pie!  

What better way to celebrate Pi day than by learning data warehousing fundamentals from two experts in the field?  I can’t think of one!

Audrey Hammonds (B | T) and Julie Smith (B | T) of very generously decided to host a standalone day long class on the subject of Data Warehousing.  As if getting to participate in a day of training for such a low price weren’t great enough, on top of all that they dedicated all proceeds from the class to The Cloverleaf School.

The event was modeled after a SQL Saturday pre-con, however, it was a standalone event not attached to a SQL Saturday.  Even without having a SQL Saturday to bring in the crowds, the attendance was great and far better than I expected!  Just about the right balance of having enough people to form a true class, but not too many people that you’re lost in a sea of faces.

The logistics of the event were very well organized.  The venue (the American Legion Post) was easy to find with easy parking.  Upon arriving, check-in was quick and easy and a nice breakfast was available.  The volunteers from The Cloverleaf School did a great job coordinating everything.

Audrey and Julie basically worked the scenario of a pie business with no infrastructure moving through the process of growth and implementing a data warehouse with regular ETL.  They essentially divided the material into two sections:  database concepts and data warehouse design (Audrey) and ETL/SSIS (Julie).  This format worked out well as a representation of what happens with job roles in the real world and they had great and entertaining banter between the two of them.  I particularly enjoyed Audrey’s exercise in data modeling.

For lunch, what would be more appropriate on Pi Day (3/14) than of course, Pizza Pie and various dessert pies?  They were a hit!

After lunch, we took a moment to pose for a group photo by the tank, because hey, there’s a tank, why not pose for a group photo on it?

All in all, I felt the class was a great experience and I was very happy with the time spent.  Not only was it for a great cause, but I got to meet some new people in the community and of course learned a lot.  While I’ve learned about the various components in a full BI system, this is one of the first resources that has done a good job of tying them together from start to finish and painting a complete picture.

As an organization that currently runs the majority of business functions from an OLTP database with SSRS pulling data directly from the OLTP database, I am very interested in learning how to properly design and implement a data warehouse, ETL data into it, and leverage the data in interesting ways with SSAS and the variety of other tools that can illuminate an OLAP data source (SSRS, PerformancePoint, Power View, etc).  Stay tuned for more as I kick off that adventure!

Newer posts

© 2018 Jeff Pries

Theme by Anders NorenUp ↑