Upgrade Your SQL Server and Improve Your Business Intelligence

Leaders of growing companies know they must do more than simply manage the high volume of business data flowing into their organizations. To reap the rewards of business intelligence (BI) — gaining a competitive edge and identifying new market opportunities — small and medium-sized businesses (SMBs) must unlock insights hidden within their databases.

Microsoft SQL Server 2014 delivers a wide range of advanced BI capabilities to support that quest. The new tools not only support enhanced business analyses, they also make it easier for business people to perform their own analyses without requiring help from IT data specialists. The result: faster slicing and dicing of data for better-informed decisions.

Users upgrading from SQL Server 2005 will find that access to an enhanced data model is one of the most important BI features. The new tabular data model in the 2014 edition complements a multidimensional model first made available in earlier SQL Server versions. Although multidimensional views of data are essential for certain types of analyses, they require end users to format information within specific schemas, such as a star or snowflake. This may be challenging for all but data analysis specialists, says Brian Larson, chief creative officer at Superior Consulting Services and author of Delivering Business Intelligence with Microsoft SQL Server.

“As the name implies, a tabular model uses tables with columns and rows, so business users have a more comfortable structure and nomenclature to work with, compared with the dimensions and cubes of the multidimensional model,” he says.

In another nod to accessibility, the tabular model’s scripting language is familiar to users of Visual Basic for Applications, the programming language used for Microsoft Excel, Larson adds.

For SMBs, the bottom line is that the new tabular format lets users perform many ad hoc queries on their own, without needing to learn arcane data analysis techniques or rely on IT specialists to generate reports.

“Business experts can do some of their own report authoring, data exploration and data visualization,” Larson says. “So if the company has a unique calculation for cost of goods sold, for example, it can load the algorithm into the model for business people to use and be sure that everybody is running the same calculation to determine those costs.”

Fueling Faster Results

The tabular model gets a power boost from another feature available in newer versions of SQL Server: in-memory processing. Storing data in high-speed memory enables companies to query and explore data faster than if it were held on slower hard drives. SQL Server 2014’s efficient data compression further enhances analytical speed. “Users can load millions of rows into memory and then query them very quickly,” Larson says.

Users who upgrade also can take advantage of another key BI feature: SQL Server Integration Services. This tool lets organizations move information into the extract, transform, and load (ETL) process required to massage data. “The usability improvements simplify the job of moving data from a line-of-business system to a data warehouse,” Larson explains.

The Lure of Azure

SQL Server 2014 also plays well with Microsoft Azure’s cloud-based database resources, which let companies extend their on-premises databases. For example, IT managers could keep the information most frequently accessed for analysis in on-premises SQL Server instances, but send older data to the cloud to reduce demand on internal resources. “As business users query the database, it’s going to look like everything is still coming from one table, even though some information may be in the cloud,” Larson says.

Platform for the Future

For SMBs already planning their next move, SQL Server 2014 provides a solid foundation for the advanced BI tools in SQL Server 2016. That will make for a smoother transition when upgrading to that edition makes sense.

For example, the 2016 edition enhances the tabular data model so that in addition to one-to-one relationships, users can analyze many-to-many relationships. That lets users pick one product and see all the customers who purchased it within a selected time period, for example.

The Report Manager interface also has undergone a facelift. In addition to generating reports, companies can create dashboards highlighting key performance indicators as users open the Report Manager portal. “People can get a valuable overview of vital data for their organization before they execute a single report,” Larson says.

For more on upgrade options for SQL Server 2005, check out this post on the CDW Solutions Blog.