Skip navigation
Computer tablet screen with the word analytics

Analytics in the SQL Server World

Embrace the inevitable analytics market evolution or be left behind

Lately, I'm noticing a lot of SQL Server community questions, confusion, concerns, and even surprising hostility around the term analytics. Without a doubt, analytics is popular throughout the entire data ecosystem and wider technology industry outside of the SQL Server world right now. Microsoft didn't invent or make-up the term analytics as data comedians in our community suggested in social networks. Look around you. Across the data world, all of the major vendors in our space are using this "hot" keyword today. It's time to embrace the inevitable analytics market evolution or be left behind.

How Analytics Relates to SQL Server

I recently wrote about trends and the longer term, ongoing market shift from business intelligence towards business analytics in Business Intelligence Market Shifts. In the article, I shared that analytics is more of a forward looking approach using data to gain business insights. Analytics means interacting with information at the speed of business, continuous iterative data discovery, exploration, and fact-based decision making using statistical and quantitative analysis, explanatory, predictive, and prescriptive modeling. I also compared analytics to the traditional business intelligence (BI) activities that we already know and embrace. What I didn’t specifically spell out in that article was how analytics relates to SQL Server. I assumed it was obvious. After seeing quite a bit of heated chatter on this topic, I'm now thinking that the connection between SQL Server and analytics is not easy for everyone to see. Let me take this opportunity to connect and clarify a few of these concepts.

Analytics is Not Synonymous with Excel

Does analytics mean Excel users? First of all, NO, analytics is not synonymous with Excel. In my mind, analytics is a broad term with varied interpretations. Analytics often includes Excel as an analytical tool, but there are also quite a few other tools including analytical functionalities within SQL Server. I personally like the definition and structure that INFORMS presents for analytics.

Descriptive Analytics

  • Prepares and analyzes historical data
  • Identifies patterns from samples for reporting of trends

Predictive Analytics

  • Predicts future probabilities and trends
  • Finds relationships in data that may not be readily apparent with descriptive analysis

Prescriptive Analytics

  • Evaluates and determines new ways to operate
  • Targets business objectives
  • Balances all constraints

If I take these categories and relate them to SQL Server world, you might be able to relate to analytics a little bit better. Although this list is not complete by any means, I'm going to guess that you'll recognize and probably have even used a few of these tools in your past SQL Server related projects.

Descriptive Analytics

  • Open Source R (Hot button, I know! This one is not directly SQL Server-related, but you      can use ROBDC to exchange SQL Server data with R for much better statistical and analytical capabilities. Someday, SQL Server might wrap in R functions like a few of the other competing database platforms already do today. I'm hoping that will happen sooner than later to be completely transparent.)

Predictive Analytics

  • New Azure ML (Although this one is not directly SQL Server-related, it shouldn't be overlooked if you work with SQL Server and Microsoft’s data platform.)
  • Open Source R (For data mining, Predixion recently added R functions into their existing offerings that are often integrated with SQL Server. R also has a huge library of free data mining packages though often R needs to be supplemented with Revolution Analytics for enterprise use cases due to memory constraints.)

Prescriptive Analytics

  • Excel Solver and Frontline Systems Platform for Office 365, Excel, Azure, SQL Server, and Power Pivot (Ok, this one I confess is a stretch to directly relate analytics to a SQL Server professional! Prescriptive analytics is where you will typically see advanced Excel modeling and a business expert versus a technical expert. I do feel that understanding optimization, simulation, and other prescriptive techniques are relevant for SQL Server business intelligence professionals to comprehend for architecting and referring appropriate analytical solutions.) 

For Further Learning

There are many resources for ramping up on analytics. Coursera and Microsoft Virtual Academy (MVA) free courses include beginning through advanced analytics topics. In future SQL Server Pro articles, we'll dive into different areas of analytics with real-world examples and explanations on when to choose one analytical problem-solving technique over another. By this time next year, I'm hoping that many more folks in our SQL Server community will warmly welcome the analytics shift. It should be enjoyable getting cozy with the meaning of the data that we have been storing and querying throughout our entire careers.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.