Callum

Callum Green's Blog

Microsoft BI – Coming Soon to SQL Server 2016

After attending the Pass Summit 2016 a couple of weeks ago, I attended a number of sessions that provided an insight into the direction Microsoft are heading with BI.  I thought I’d share this with the community.

Looking back to October 2015, the official Reporting Roadmap blog from Microsoft stated their intent in dramatically improving the visualisations, integration and harmonisation of both on-prem (SSRS) and cloud based services (Power BI).  Whilst reporting appeared to be a renewed focus, they are constantly driving other areas of BI - such as analysis services and database/data warehousing development in the cloud.

Now, for the interesting bit.  Here is what we can expect in the SQL Server 20916 BI Stack in the near future:

-          SQL Server 2018 CTP (to be released within the next month).

o   This seems very early, considering 2016 has only just been released!  Let’s wait for an official Microsoft announcement.

-          Azure DW

o   Auto Create Statistics

§  Currently statistics have to be generated and updated on the fly.

o   Replicated Distributed Table

§  One that will excite any cloud based SQL Server developer.

§  This will reduce data transfer between distributions/nodes and consequently improve performance.

-          Azure Data Lake

o   General Availability (GA) is imminent.

o   Future Features:

§  Polybase, so that you can connect the two big MMP platforms (ADL and DW).

§  SSIS connectors (released with GA) for Store.

§  Python and R in U-SQL.

-          SSIS

o   Lots of new connectors, including:

§  ADLS.

§  CRM.

-          SSAS

o   Migrating Power BI Models into Tabular.

§  This is coming very soon apparently, but will be developed in Azure SSAS first.

o   Object Level Security in Tabular

§  We currently have row level, but there are talk to secure a physical object, not just a row.

§  Even better news - Microsoft want to integrate the two together, which will make security awesome in Tabular.

-          SSRS

o   Supporting (not pinning) Excel reports in RS report.

§  This will come, but Power BI is the focus right now and we may have to wait a while.

-          Power BI

o   Additional and better Pivot Table functionality.

o   Integrating Active Directory dynamically.

o   Potential to use Direct Query and Imported modes together – as a hybrid.

§  Functionality is possible, but performance needs to be weighed up by Microsoft before anything will emerge.

o   Quick Calcs.

§  Only ‘Percent of Current Total’ currently available.

§  Potential is to offer lots more – such as YTD, MAT, Current Year vs. Previous Year, etc.

§  This is for the users who aren’t familiar with DAX.

o   Template organisational Content Packs.

§  The ability to give the user the ability to personalise colours, fonts, etc. within a structured (organisational) content pack.

-          Power BI Embed

o   Application developer will be able to limit user sessions and therefore, reduce the charge per 1 hour costs that come with it.

 

There are some features/issues Microsoft do not plan to change.  Although, the good thing about Microsoft is that they are community driven, so if you feel strongly about anything (and get support from your peers), they may change their minds.

-          SSRS

o   Q&A, Query Editor (Power Query), R integration, etc. not being developed.

§  Pretty obvious really.  Whilst they are going to introduce almost everything from Power BI, some elements of functionality are just not needed for on pre purposes.

§  R Scripts may come one day, but not a focus right now.

-          Power BI

o   Source Control

§  No immediate plans to integrate with TFS or modularise the pbix files (for a more developer based solution)

§  Not surprising as this is a self-service tool, not a development team.

§  Work around is to upload pbix files into OneDrive and use the versioning as an element of Source Control or add a file into Visual Studio.

§  Keep Voting on PowerBI.com if you want this! (Currently 278 votes).

·         https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/9677517-source-control

-          Power BI Embed

o   Licence model

§  ‘Speak to Marketing’ is what we were told.

 

Is everyone excited?  I certainly am.  Microsoft are openly admitting that any new BI related feature will be developed in the cloud first, but this was pretty obvious.  For all you on-prem fanatics (me included), at least the bugs/undocumented features will be ironed out before we get to use them!  My personal favourite has to be the direction SSRS is going.  It was recently labelled a ‘mature’ product, but now RS has suddenly become a cool and current tool to use.  The fact Power BI and SSRS will become almost the same product, tells us all that on-prem is still an important part of the Microsoft licensing strategy.

 

I am sure there are lots of other features coming into the BI stack over the next 6 months to a year and these were just the ones I had heard about.  Feel free to comment if you have additional ones to share.

October 28th Technical Preview for SSRS – Including Power BI in SSRS

Today at the Pass Summit, Microsoft announced some cool new features that are coming into SSRS 2016.   I attended the ‘SQL Server Reporting Services: What’s New and What’s Next’ session (presented by Chris Finlan and Riccardo Muti and there was certainly some very exciting news for anyone in the BI sector.

There will be a technical preview available from 28th October, in which you can download a pre-configured Virtual Machine in the Azure Marketplace.  This includes sample reports and data, enabling you to try the features whilst Microsoft work on a complete preview for on premise R&D. This blog lists the new feature from a high level.  I will announce more information as and when it is available.

Features

 

1.       Power BI reports in SQL Server Reporting Services.

a.       I won’t steal Microsoft’s thunder and instead, redirect you to their blog written on October 25th:

https://blogs.msdn.microsoft.com/sqlrsteamblog/2016/10/25/announcing-a-technical-preview-of-power-bi-reports-in-sql-server-reporting-services/

b.      In essence, you can create a Power BI desktop report and hook it straight into your on premise SSRS reporting solution. Amazing, right?!

2.       Report Manager.

a.       List View is back!  You can again view reports the old fashioned way.

b.      Show Hidden Items is now unchecked by default.  Sounds insignificant, but it is the little things that sometimes matter.

3.       Report Comments Section

a.       This is really cool.  A user can write a comment against a given report and even ‘snapshot’ the report as an image and upload it.  This is a good way to start an internal company discussion, as well as trace how a visualization has changed over time.

b.      All comments and images are stored in the Report Server db, which helps for auditing or plugging the data into an external tool/text file.

4.       Other – More information to be announced soon

a.       Direct URL link from a KPI.  If you click a KPI, it will take you to a specified link, without the need to select it form an option.

b.      Mobile Reporting auditing.

c.       Better support for generated MDX.  The current date workaround will no longer be needed and make everyone’s life easier. 

d.      General performance.

Current Preview Limitations

 

As this is an interim SSRS Preview release, there are some current limitations.  These are more specific to the Power BI reports in SSRS:

-          Power BI reports that connect “live” to Analysis Services models – both Tabular and

        Multidimensional  (cubes). No other data sources are currently available.

-         Custom Visuals not supported.

Coming Soon

 

As soon as I get back from the Pass Summit (and over my jet lag), I will be downloading the preview and trying out the cool features.  Please come back to my blog page to check out my findings and more importantly, feel free to comment on any quirks/issues/limitations that you have come across yourself.

It is certainly exciting times for the on premise enthusiast out there.  I had lost all hope for SSRS, but with 2016, Microsoft have rekindled my love for enterprise data visualisation.

New Features in SQL Server 2016 – Part 1: Database Engine

In early May 2015, SQL Server 2016 was made available in Preview.  To download the CTP2 version, click here. 

This blog will list all of the new Database Engine features coming next year.  In futures posts, I will pick a couple of the enhancements and demonstrate how they will benefit DBA’s, Developers and BI specialists.  In the meantime, I am aiming to give the SQL community a high level understanding of what is new in 2016 and help people think about how some of them could benefit their clients and stakeholders.

Database Engine Feature Enhancements

There are lots of new, exciting additions to the database engine in SQL Server 2016.  They can be found below.  I have picked out my favourite five and provided additional detail on them.

· ·        Transact-SQL Enhancements

o   Columnstore Indexes

o   In-Memory OLTP

o   Live Query Statistics

o   Query Store

o   Temporal Tables

o   Backup to Microsoft Azure

o   Managed Backup

o   Trace Flag 4199

o   FOR JSON

o   Always Encrypted

o   PolyBase

o   Stretch Database

·        Transact-SQL Enhancements

·        System View Enhancements

·        Security Enhancements

o   Row-Level Security

o   Dynamic Data Masking

o   New Permissions

o   Transparent Data Encryption (TDE)

·        High Availability Enhancements

·        Tools Enhancements

Columnstore Indexes

Whilst SQL Server 2014 offered some improvements on 2012, the 2016 version is taking Columnstore indexes to the next level.  In addition, Azure SQL Databases can incorporate almost every feature in a standard on premise database.

1.       A table can have one updateable nonclustered columnstore index.

2.       The nonclustered columnstore index definition now supports filtered conditions.  As a result, the performance impact pm am OLTP table will be minimal.

3.       An in-memory table can have one columnstore index. Previously, only a disk-based table could have a columnstore index.

4.       A clustered columnstore index can have one or more nonclustered indexes. Previously, the columnstore index did not support nonclustered indexes.

5.       Supports primary and foreign keys by using a btree index to enforce these constraints on a clustered columnstore index.

In-Memory OLTP

The current In-Memory-OLTP offers up to 30x transactions.  In SQL Server 2016, you will also be able to apply it to more applications and benefit from increased concurrency.  In addition, you can use the new in-memory columnstore with in-memory OLTP, delivering 100x faster queries.   

There are other benefits to the refined In-memory OLTP:

1.       Support ALTER operations for memory-optimized tables and natively compiled stored procedures.

2.       Support for natively compiled, scalar user-defined functions.

3.       Support for all Collations.

4.       Storage Improvements.

a.       Ability to estimate memory requirements for memory optimised tables.

b.       Once you determine the size, you need to provide disk space that is four times the size of durable, in-memory tables.

5.       Enhancements to transaction performance analysis reports.

a.       Transaction performance collector in SQL Server Management Studio helps you evaluate if In-Memory OLTP.

b.       Use the ‘Memory Optimization Advisor’ to help migrate table to in memory.

6.       Support for subqueries and query surface area in natively compiled stored procedures

a.       E.g. BETWEEN, GROUP BY, ORDER BY, TOP, UPDATE, TRY/CATCH, etc.

b.       The usual DISTINCT and ORDER BY do not work together.

c.       Neither does combining TOP and PERCENT in a Select statement.

System View Enhancements

There are a number of views that will aid a developer, especially around security and query stats.  The enhancements are grouped into 3 sections.

1.       Row Level Security

a.       sys.security_predicates – Returns a row for each security predicate in a database.

b.       sys.security_policies – Returns a row for each security policy in the database.

2.       Query Store Catalog Views

a.       7 new Query Store support views

                                                               i.      sys.database_query_store_options

                                                             ii.      sys.query_context_settings

                                                           iii.      sys.query_store_plan

                                                           iv.      sys.query_store_query

                                                             v.      sys.query_store_query_text

                                                           vi.      sys.query_store_runtime_stats

                                                          vii.      sys.query_store_runtime_stats_interval

3.       Query Hints

a.       MIN_GRANT_PERCENT

b.      MAX_GRANT_PERCENT

Row-Level Security (RLS)

Microsoft’s explanation of row-level security reads:

“Row level security (RLS) introduces a flexible, centralised, predicate-based evaluation that considers metadata or any other criteria the administrator determines as appropriate. This is used as a criterion to determine whether or not the user has the appropriate access to the data based on user attributes. “

This can be translated to something a little easier to ingest:

1.       Ability to control access to rows in a database table based on the characteristics of the user executing a query.

a.       E.g. Group Memberships, such as company department or job status.

2.       Simplifies the design and coding of application security.

3.       Restriction logic is found in the database layer, as opposed to an application.  It helps limit the various levels of required security and therefore, increases reliability.

4.       Use ‘CREATE SECURITY POLICY’ T-SQL to set up RLS.

 

High Availability Enhancements

If you are not familiar with High Availability in SQL Server 2012/14, click here for a quick introduction.  For everyone else, the below enhancements greatly enrich this feature:

1.       Load-balancing is now possible across a set of read-only replicas.  Previously, it used to always direct connections to the first available read-only replica.

2.       There are now 3 replicas that support automatic failover cluster.  It used to be 2.

3.       Group Managed Service Accounts now facilitate AlwaysOn Failover Clusters. *

4.       AlwaysOn Availability Groups can be configured to failover when a database goes offline.  You must change the setting ‘DB_FAILOVER’ option to ON.

 

*             You will need to update Windows Server R2 to avoid any downtime after a password change.

Future Blogs

As the Database Engine contains the majority of changes, I have included the other features in a series of other blog posts.  Click the links below to access them.

1.       Part 2 - Analysis Services - TBC

2.       Part 3 - Reporting Services - TBC

3.       Part 4 - Integration Services - TBC

References

For more information on all of the new SQL Server 2016 features, the below resources/blogs are highly recommended.

·        What’s New in SQL Server 2016 (Official Microsoft) -
https://msdn.microsoft.com/en-us/library/bb500435(v=sql.130).aspx

·        T.K. Ranga Rengarajan’s SQL Server Blog - http://blogs.technet.com/b/dataplatforminsider/archive/2015/05/27/sql-server-2016-first-public-preview-now-available.aspx

·        Jen Underwood SQL Server BI Blog -
http://sqlmag.com/blog/what-coming-sql-server-2016-business-intelligence