Business Intelligence, R, and Big Data

For data analysis, SQL Server 2016 can access different data sources. In addition to traditional relational databases, you can query non-relational databases for analysis and reports with Transact-SQL (T-SQL). T-SQL also easily integrates Hadoop and queries Hadoop data. In this way, you can create data lakes in which all your data is stored. Power Query for Excel is also a good option for analyzing and generating reports. Here again, the requested data can be obtained from different sources and be integrated in different data models. Therefore, if you have created integration projects using Power Query, you can continue to use them in the integration services.

Those who use T-SQL or applications that rely on T-SQL can continue to use SQL Server 2016 and, thanks to the Hadoop connection, receive much more data that can continue to be processed efficiently by SQL tools. BI analyses can also be performed on the go in SQL Server 2016. The new server particularly supports Windows Mobile 10, but apps are also available for Android and iOS devices.

In addition to access via smartphones and tablets, you can evaluate data from a browser. In this case, communication between the end device and the SQL server occurs via HMTL5. You can create dashboards and, once created and saved, they are accessible on all end devices and can be touch optimized. The ability to optimize dashboards for various device classes is an advantage of the new environment, allowing you to create analyses and dashboards on a desktop and then process them using a tablet or smartphone on the go.

Microsoft has integrated the R language in SQL Server 2016 for data analysis with in-database R Services. You can use existing R models or download R scripts via the Azure Marketplace [1] and run them in SQL Server 2016. You can also offer your own R scripts in this way. If scientists at your company analyze their data with R, you can provide them with corresponding interfaces in which they can continue to use the data with SQL tools.

Master Data Services (MDS) have also been updated. Power was increased, according to Microsoft, and permissions can be assigned in greater detail. You can also specify various archiving options for transaction logs in the new version and use entities in MDS on different models. Finally, SQL Server 2016 now supports native JSON as a data format. This means JSON files can be created directly from databases and continue to be used in other applications.

Power BI Analyses

Power BI [2] provides a newly developed analysis platform that lets you create your own dashboards without the need for an experienced developer (Figure 2). The cloud service supports the use of data both on the cloud or with Azure, and local SQL servers can be connected. Power BI allows real-time data analysis. Using agents and connectors, businesses can easily make databases, enterprise resource planning (ERP) systems, and other server solutions accessible on the internal network. SQL Server 2016 provides an advantage in this case, because the new version provides good support for Power BI, particularly with regard to data encryption and an improved connection to Azure that makes the cloud more secure and more efficient. SQL Server 2016 itself, however, does not have any Power BI functions.

Figure 2: A custom Power BI dashboard. (Microsoft image)

Even without Power BI, the SQL Server 2016 Reporting Services and Analysis Services provide comprehensive analysis functions. Microsoft has improved performance and support for common browsers. To create an optimal web interface for Reporting Services, you can also use CSS stylesheets that allow visually appealing websites. Applications that access the reporting services can be reused, and Reporting Services can even access local data from SQL servers or data in Microsoft Azure.

Local and Cloud Tables

As with previous versions of SQL Server, Microsoft has further improved cooperation with Microsoft Azure. The focus of the new SQL version is on companies that use hybrid cloud environments – that is, companies that want to operate local database servers and cloud databases or database servers. The wizard that effects the cooperation between local servers and the cloud was enhanced and simplified. Only a few steps are required to migrate SQL databases into the cloud. In SQL Server 2016, you can run tables locally and in the cloud, and you can expand local tables into the cloud on servers running SQL Server 2016. The table is stored locally and in the cloud and can be accessed from local applications. This is particularly useful for data that is not needed continuously and for which high performance is not required. The use of tables in Azure is transparent for applications; no adjustments are required.