Microsoft announced two company acquisitions related to data analytics. It announced that it will acquire Revolution Analytics. Revolution Analytics is the leading commercial provider of software and services for R, the world's most widely used programming language for statistical computing and predictive analytics. This acquisition could help more companies use the power of R and data science to unlock big data insights with advanced analytics.…
Non-clustered columnstore indexes (NCCI) were introduced in SQL Server 2012 to improve the performance of large aggregate queries (common for data warehousing) with the caveat that there were read-only. Consequently, the ETL process has to drop NCCI, load the data, and recreate the columnstore index. NOTE Building an index (columnstore or regular) should be a highly-parallel operation. Building a columnstore index in particular should max…
I'm presenting at the Atlanta.MDF group on Monday, January 12th. I'll be covering a wide range of tips and techniques for analyzing and improving performance of SQL Server-based data analytics solutions. Hope you can make it. Title: Can Your Data Analytics Solution Scale? Abstract: Does your ETL exceed its processing window? Do your users complain about the SSRS spinny? Can your SQL Server database design…
Scenario: You execute a SQL Server 2012 task that uses parallelism, such as index rebuild or a query on a server with more than 20 cores running SQL Server 2012 Enterprise Edition. In the Windows Task Manager, you observe that the task uses only 20 cores. We discovered this scenario during a rebuild of a columnstore index. To confirm this further, you examine the SQL…
One of the biggest strengths of Microsoft self-service BI is the ability to create sophisticated data models on a par with organizational BI models built by professionals. This fact is often overlooked when organizations evaluate self-service tools and the decision is often made based on other factors but not insightful understanding of the data model capabilities. This is unfortunate because most popular tools on the…
Happy New Year! ETL exceeds the processing time window? Optimizing ETL, starts with obtaining task-level execution times? If you use SSIS 2012 project deployment mode, task-level stats are already loaded in the SSIS catalog and you can use the following query: SELECT execution_id, CASE WHEN [status] = 1 THEN 'created' WHEN [status] = 2 THEN 'running' WHEN [status] = 3 THEN 'canceled' WHEN [status] =…
