Product and service reviews are conducted independently by our editorial team, but we sometimes make money when you click on links. Learn more.

SQL Server 2014 & the Promise of Better Analytics

SQL Server 2014 & the Promise of Better Analytics

Business intelligence and analytics professionals have access to a wide array of tools for building BI reports and predictive models. The recent SQL Server 2014 Community Technology Preview (CTP) advances the Microsoft toolset for end users, database architects and administrators. End users will find more support for self-service data analysis using Data Explorer. For those grappling with increasingly large volumes of data, big data features of SQL Server 2014 can help bridge the relational and NoSQL approaches to data management.

Analysis for the Non-Analyst: Data Explorer & Self-Service BI

One of the keys to successful self-service data analysis is bringing data to users, and that means leveraging tools they already use. Microsoft is clearly at an advantage here with widely used tools like Excel and SharePoint. Data Explorer for Excel is a power query tool that allows users to query and analyze data from SQL Server data and other sources from an Excel spreadsheet.

Data Explorer allows users to work with data that is stored in a variety of formats, both structured and unstructured. Text based sources include XML files, Web pages and APIs, and Microsoft Access databases. Of course, relational sources include SQL Server and Windows Azure SQL, as well as other major databases such as Oracle, DB2 and Teradata. In addition to these conventional data sources, Data Explorer can work with Hadoop and Facebook data as well.

Since self-service BI analysts have so many data sources to work with it is important to support integration across data sources. Fortunately, users don't have to be knowledgeable of the ins and outs of SQL join syntax because Data Explorer includes a GUI interface for merging data from multiple sources and specifying columns to join on.

Data analysts often need to transform data; Data Explorer includes a GUI that makes it easy to filter, sort and apply transformation functions to data. The tool includes the Data Explorer Formula Language so users can create more complex custom transformations if needed.


Dan Sullivan is an author, systems architect, and consultant with over 20 years of IT experience with engagements in systems architecture, enterprise security, advanced analytics and business intelligence. He has worked in a broad range of industries, including financial services, manufacturing, pharmaceuticals, software development, government, retail, gas and oil production, power generation, life sciences, and education.  Dan has written 16 books and numerous articles and white papers about topics ranging from data warehousing, cloud computing and advanced analytics to security management, collaboration, and text mining.

See here for all of Dan's Tom's IT Pro articles.