Adatis BI Blogs

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 if you want this! (Currently 278 votes). · -          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: 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.

Stretch Databases: The Basics

What Is It? A stretch database is a database where the rarely used portions of the data are stored in the cloud. Offered by SQL Server 2016 alongside Azure, it will allow seamless migration of data between the cloud and your local server databases. Consider data as being warm or cold; warm data is accessed often, cold is not. So for all intents and purposes, cold data is wasting space on the local server most of the time it’s there. The feature was created with historical data and other similar types of data in mind. Data that would not be accessed regularly, but still needs to be available on occasion. A stretch enabled database can have tables that query as if they are local, yet have some of their rows stored in Azure blob storage. The only noticeable difference by the user is that some queries take longer to process. There is a fair amount of control over data migration. Functions can be created to stretch specific rows, based custom filter functions. Migration can be monitored and troubleshooted, or simple paused and resumed at will. Once the data is in the cloud it is backed up automatically, with Azure holding storage snapshots from the last 7 days for easy recovery by the user. Any backups of the stretch enabled database actually only takes the local data – migrated data can only be restored using the Azure storage snapshots.   Requirements And Prerequisites The main requirement is having an Azure account with a billable subscription already set up. Blob storage is set up during the stretch database setup, so nothing further is needed on the Azure side. The Stretch Database Advisor, included in the separately downloaded Data Migration Assistant, can indicate which tables are eligible for stretching to Azure (I’m planning on going through this in a future blog post). They do not mention what incompatibilities the other tables have, however. Any table that is not shown as compatible must be manually examined, to see what incompatibilities there are. I have included a full list of incompatible features, column types, and data types below.   Limitations And Incompatibilities As of writing this, there are still a large number of index, constraint, and data type limitations on tables that use stretch databases according to the MSDN website. Data management operations are also limited – you cannot update or delete rows marked for migration, or that have already been migrated. These rows must be migrated back to the local database in order to be altered. Table Properties · Memory optimised tables cannot become stretch enabled · Replicated tables cannot become stretch enabled. · Tables using change tracking and change data capture cannot become stretch enabled. Column And Data Types · A table cannot be stretch enabled if it has more than 1,023 columns. · FileTables and Filestream data are incompatible. · Column set and computed columns are incompatible. · Text, ntext, image, timestamp, sql_variant, XML and CLR data types are all incompatible. Constraints · Uniqueness is not enforced on migrated Unique and Primary Key constraints. · Default and Check constraints are incompatible. · Foreign Key constraints cannot reference a stretch enabled table Indexes · Indexed views are incompatible. · Fulltext, XML, and Spatial indexes are incompatible. · A table cannot be stretch enabled if it has more than 998 indexes. · Filtered indexes do not include migrated data on stretch enabled tabled.   Further Reading The MSDN website has the full description of stretch databases SQL Data Partners have also done an interview with Anthony van Gemert, one of the product managers for the stretch feature

Introduction to Dynamic Data Masking

What is it SQL Server Dynamic Data Masking is a new feature which has been released with SQL Server 2016. It is designed to allow the creation of a pre-defined rule which can be applied to the data in a column limiting the exposure of the actual data. For example, if you have the following password column in a table which contains user passwords and has a datatype of nvarchar; Password Kingston123 Sh@r0na you can apply a data masking rule to the column which will make is appear as the following to any unauthorised user; Password XXXX XXXX Masking Options Dynamic Data Masking currently has 4 possible masking options which can be applied to a column; Default This will mask the full value depending on the column’s data type. For string data types; char, nchar, varchar, nvarchar, text and ntext the value will be replaced with XXXX. If the length of the field is less than 4 characters, then that number of X’s will be used. For numeric data types; bigint, bit, decimal, int, money, numeric, smallint, smallmoney, tinyint, float and real the value will be replaced with 0. For date and time data types; date, datetime2, datetime, datetimeoffset, smalldatetime and time the value will be replaced with 01.01.2000 00:00:00.0000000. For binary date types; binary, varbinary and image the value will be replaced with 0. Email This will mask the full value exposing the first letter of the string and masks the rest with For example, ‘’ will be masked as ‘’. Custom String This will mask part of a value exposing a number of characters at the start of end of the string based on a prefix and suffix padding value. For example, ‘abcdefghij’ when given a prefix padding of 2 and a suffix padding of 4 will be masked as ‘abXXXXghij’. Random This can be used with any numeric data type and will mask the original value with a random value based on the supplied range. For example, ‘123456789’ when given a range of 1-5 will be masked as either 1, 2, 3, 4 or 5. Uses and Limitations Dynamic data masking is designed to restrict the exposure of sensitive to non-privileged users with minimal impact on the application layer. As its application only effects the result set of a query over designated database fields while keeping the actual data in the database the same, it is perfect for reporting or Business Intelligence uses; this also means that it can be incorporated without modifying pre-existing queries. This feature is implemented by running Transact-SQL commands in SQL 2016 or by using the Azure portal for Azure SQL Databases. It is important to note that dynamic data masking is not designed with the purpose of extensive database security and will not be able to prevent database users from running intrusive queries to expose extra pieces of the sensitive data by connecting directly to the database. The feature can however be used in conjunction with other SQL security features such as encryption, row level security and auditing. There are a number of other restrictions\applications that should be noted; · Dynamic data masking will not work with; encrypted columns using Always Encrypted, FILESTREAM column types, COLUMN_SET or a sparse column that is part of a column set, computed columns (if the computed column depends on a masked column then the result will be masked data), keys for a FULLTEXT index. · Updates can still be made to a masked column, even though when queried the user will see masked data. · Using SELECT INTO or INSERT INTO to copy data from a masked column into another table will result in masked data in the target table. · When using SQL Server import and export functionality on a database containing masked data the resulting backup file or imported table will contain masked data. I will be posting a future blog showing in-depth real-life applications of dynamic data masking with examples, applied permissions and further applications for both on-premises SQL 2016 and Azure SQL Databases. Further Reading I will be posting a future blog showing in-depth real-life applications of dynamic data masking with examples, applied permissions and further applications for both on-premises SQL 2016 and Azure SQL Databases. For more detailed information see the following links;        MSDN Dynamic Data Masking, Get started with SQL Database Dynamic Data Masking (Azure Portal)

Master Data Services Many to Many Relationships

Many to Many Relationships (M2M) are possible in the current version of Master Data Services, but they come with a few workarounds. Thankfully, many to many hierarchies are one of the many new features that can be found in Master Data Services 2016. This post will contrast the M2M approach in the current version of MDS, before showing how its greatly improved in the current MDS 2016 CTP. Customer Example – MDS 2014 The simple example that I’m going to show is Customers and Bank Accounts. That is, a customer can have more than one bank account, but a bank account can also have more than one customer, e.g. a joint account or a business account. First, to show the MDS 2014 approach, there are a few entities to look at, namely Customer and Account: Then there is an other entity that relates to both Customer and Account, called CustomerAccount, which essentially acts as a bridge table, allowing the M2M relationship to naturally exist in the data: Moving away from the entities, I’ve created two derived hierarchies so that we can look at the data by account or by customer. So here we can see, for example, that Bob is keeping his own personal account, as well as the account with his wife…hmmm: Or looking at this another way, we can see the inverted version of the hierarchy, where we see the Customers as the top level, with some accounts belonging to more than one customer: As you may have spotted, there’s an issue with this approach. Whilst we do see the name of the Customer in the first hierarchy, its pointing at a member in the CustomerAccount entity, which is why the code is different each time. Enter SQL Server 2016… Master Data Services 2016 MDS 2016 does a far better job at visualising the M2M relationship. You still need the link entity, but it will allow you to show the actual Customers and Accounts with their correct codes. I’ve used the exact same entity setup as MDS 2014, so lets begin by looking at how we can build a hierarchy that shows customers by accounts. When creating a new Derived Hierarchy, we can see that the initial available levels list is just the same as it would be in MDS 2014: The first step is to drag over the entity called Customer to the Current levels section. Once we perform this action, the Available Entities pane looks very different in SQL 2016 that in would in the current version. Now MDS is allowing us to pick the Account entity, but via another entity. Hence it says “mapped via” on the left hand side: Once we finished the hierarchy build, and explore the hierarchy, we can see the same data as before, but this time its the actual correct customer codes that are displayed: The derived hierarchy by Customer also displays the correct customer and account codes: Summary A derived hierarchy in Master Data Services 2016 now allows the correct display of many to many relationships between two entities. This is one of many new features in MDS 2016, offering quite a number of improvements over the SQL Server 2014 version.

“Master” Master Data: Entity Sync in Master Data Services 2016

When I heard that “entities could be shared” between different models in Master Data Services 2016, my curiosity was piqued.  There is a nice video of this feature and hierarchy management here. The concept of sharing entities is called Entity Sync in Master Data Services 2016. You may ask the question as to why entities need to be shared between models? This is something I have thought about a lot with deciding how to split up the subject areas into different models in Master Data Services.  The conclusion I have come to is that is a better to keep subject areas separated rather than having one very large model in master Data Services with many entities.  Keeping the models separated makes it much easier to manage when working in teams, deployment, versioning etc.  However, there are some use cases where having a single entity with the same definition and data might be needed in different models.  For example… Say different departments have their own data stewards for master data in an organisation maintain their own master data in different models and one needs to secure those models such that different departments cannot see or modify each others data.  Then a department entity may be needed in each of those models Some master data may change very infrequently, but need to be used in multiple models e.g. currency, location, title.  In the ‘old days’ of Master Data Services 2014 and prior one would have to create replica entities and make sure that data is loaded into each of the entities in each of the models.  I have always thought it would be nice to have “Master” master data in Master Data Services.  This is where Entity Sync in Master Data Services 2016 comes in… Setting up a new Entity Sync Relationship Using the ChartOfAccounts, Customer and Product sample Master Data Services models I created a new entity called Disney Characters in the ChartOfAccounts model and want to sync this to the other two models i.e. Customer and Product. What is nice with Master Data Services 2016 is that one can add descriptions to the Models, Entities and Attributes, which is a nice feature to ‘document as you develop’. To set up Entity Sync, go to System Administration, and select ‘Entity Sync’ from the ‘Manage’ menu Create a new Entity Sync and select the Source Model, Entity and Version.  Then select the Target Model and Version.  You can even change the name of the target entity to something different.  I’m thinking ‘role playing’ entities.  You can set a schedule for when the entity gets synced, or ‘Sync On Demand’. So the below screenshot shows the setup of the Entity Sync for the Disney Characters entity from ChartOfAccounts model to the Customer model and renaming it along the way to Cartoon Characters. The entity will not sync from the Source Entity (ChartOfAccounts) to the Target Entity (Customer) until the sync is run.  You can’t seem to select more than one Entity Sync to sync at a time through the Master Data Services web UI. On going to the Customer model and viewing the synced Cartoon Characters entity, all the metadata (including all those descriptions) and data appears.  However it is not possible to add or modify members.  And we cannot add any new attributes to the synced entity.  This is what we want, right?  “Master” master data with a single source in the source entity and model. In the management of entities it is also indicated that this entity is a ‘Sync Target’. Nice. Now that the entity is synced I can use it like I would any other entity in the Customer model.  I can add domain based dependencies and use it in other entities in the model, and I can apply security onto it.  I can make changes to the source entity and re-sync and the new metadata and data will be propagated to the target entities. Deleting Entity Sync Relationship But what if you delete the entity sync relationship? Will the target table get deleted too?  So trying this I removed the Entity Sync for the Disney Characters entity from ChartOfAccounts model to Cartoon Characters entity in the Customer model. Nope. The entity is still there, but now you can edit it (I’ve added ‘Road Runner’), and carry on modelling it like you would any other entity in the model.  Although I did note that it lost it’s setting of ‘Create Code Values Automatically’.  Not sure if this is a feature? I can turn the entity sync relationship back on but specify ‘Existing Entity’.  But this threw an error, it seemed to have a problem with the new member ‘Road Runner’ that I had created.  I deleted ‘Road Runner’ and set up the Entity Sync relationship no problem.  I guess one could also drop and recreate the entity using the Entity Sync. So what happens if I try to synchronise and entity that has dependencies on other domain entities e.g. Trying this out with the SubClass entity from the ChartOfAccounts model gives the following error “Domain based attribute(s) in the source entity will not be synced”. This is what the SubClass source entity looks like with a domain based attribute to the Class entity in the ChartOfAccounts model. This is now what the SubClass target entity looks like in the Customer model.  It has lost all of it’s domain attributes! So I thought I would try to first synchronise the Class domain entity first, then synchronise the SubClass entity. Same “Domain based attribute(s) in the source entity will not be synced” as before and the domain based attributes of SubClass will not be synched. Deployment Considerations With the current SQL Server 2016 CTP 3.2 it does not appear that Entity Sync relationships will be packaged up with creation of packages either from the Master Data Services web UI or using the MDSModelDeploy command line utility.  Although I guess they might not be? As one would have to consider the order of deployment of models with the dependencies that the Entity Sync relationships create.  They are very easy to set up, but then again there is a new meta data table in the Master data Services database called [mdm].[tblSyncRelationship]. There are also corresponding stored procedures that operate on this table. So I guess one could populate the required metadata in [mdm].[tblSyncRelationship] and regenerate the Entity Sync relationships as part of a deployment process. In Conclusion The introduction of the Entity Sync functionality in Master Data Services for SQL Server 2016 is a welcome new feature and opens up all sorts of possibilities with creating “Master” master data.  The new Master Data Services web UI for managing these is great.  So long as the entities you want to sync to other models do not contain any domain based entities. I will definitely be using it, as: It will definitely speed up development – goodbye to all those SSIS packages to load the same data into the same entity in different models! Add value to the end user as everything will be in perfect sync

Reflections On PASS Summit

Well it’s been quite a week and this has certainly been a summit with many announcements for the wider BI community. Whilst I really enjoyed my previous visit to PASS and the sessions were certainly very interesting, aside from a few announcements around Power BI there wasn’t much new in the BI landscape. This summit has been quite a different experience - it really feels that BI features are again high in the priority agenda with SQL Server 2016. We have seen the resurrection of SSRS and significant enhancements across the MS BI Stack. As usual, as well as the new technology announcements there were some excellent sessions from community members I particularly enjoyed the session from Rob Farley’s on Query Plan Operators and Jason Strate’s talk on the Plan Cache. Both of which contained techniques I will certainly be using in future. I thought I would write up a summary of the changes which have been announced and my thoughts on them split by feature: SSRS Probably the most unexpected announcement were changes to SSRS. There has been such little discussion of SSRS since release of the 2008 R2 edition of SQL Server I had started to suspect SSRS was going to be left to go the same way as Performance Point. This would have been a great shame as SSRS has been a great tool for providing on-premise reporting and fits nicely in a gap which other parts of the BI Stack don’t cover. The following new features were announced: - The SSRS Portal has been rewritten in HTML 5 thus making it cross browser compatible and will work across devices (tablets, phones etc). It  also means the portal looks much newer and more in line with other offerings from MS such as the Power BI Portal. - It will now be possible to setup the positioning of and grouping of parameters in SSRS reports. This will make quite a significant change to the usability of reports and was something which could not be done without building a custom wrapper to display the reports. - The portal will support hosting of DataZen reports, the functionality of DataZen will remain and reports hosted on the site will be separate from traditional SSRS reports, however this additional report type will be rolled into SSRS and the current DataZen product will not exist in it’s current form. - SSRS Report parts can now be pinned to Power BI dashboards. - Reports can now be exported directly to PowerPoint without requiring the additional use of third party tools. - Changes have been made to the default charts and report objects which should mean that off the shelf reports immediately look nicer than  previously. This seems like an immediate win as all those who have worked with SSRS will know, it can take some time in order to produce nice looking reports using SSRS and much of this work is down to changing the current default settings. - The Power BI Mobile App will now support connections to RS, allowing on the move users to view on premise SSRS reports. - Tree Map and Sunburst chart types have been added providing additional visualisation types. - Printing no longer is dependant upon ActiveX. - Support for .Net 4 There were also a couple of things I noticed which were not quite so positive in the list of announcements: - When asked about the licensing model MS could not confirm how SSRS would fit into the licensing model in SQL 2016. This could be because this has yet to be decided, however it did make me wonder that with all these enhancements is this something which could potentially incurincurr an additional fee. - Reports deployed to SharePoint will not benefit from all of the new features, for example they will not benefit from any of the parameter changes and I am unsure of the other enhancements such as HTML 5 and new chart types will be supported in reports deployed to SharePoint. Basically don’t deploy your reports to SP unless you have to. Overall though the picture is fairly positive and certainly far more than I had expected to be announced. The HTML 5 compliance and changes to parameters will certainly appeal to clients. My only criticism is that functionally the navigation and user experience of the reports will be quite similar for a desktop user. Certainly the HTML 5 will improve the look, and changing the parameters will also make some improvements but the reports will still essentially be static parameterised reports – there is certainly no announcement of any new features such as AJAX style interaction with different report parts in the way that is currently supported by Power BI. SSIS Whilst perhaps not quite as major as the changes announced to SSRS, SSIS has some pretty nifty improvements. - Reusable (parameterised) control flow templates have been added to enable re-use of SSIS logic. Whilst I will have to try this out the implementation of this for myself certainly there are often many similar actions performed using the ETL process which could certainly benefit from this. - Column names now appear in the data viewer in event of an error! This is a great change and whilst it has always amazed me that this has not been present in previous releases (one might think this is pretty fundamental!) this will be a great enhancement for many people working with SSIS on a daily basis.  - The latest version of VS 2015 (available now in preview!) will support all versions of SSIS, allowing all SSIS development to take place in the same version of Visual Studio. On a side note, ‘Data tools’ has been rolled into one product allowing for development of DB Projects, SSIS packages, SSRS and SSAS. Whilst this may not sound groundbreaking these are long overdue features which I think will have many people very happy. -  New connectors allow direct connectivity to Azure storage and Hadoop. - Packages can now be deployed incrementally to the SSIS catalog, rather than required entire projects to deployed at a time. - A new role ‘SSIS_logreader’ will be added which will allow access to read logs without requiring Admin permissions. - Foreach loop will now be able to iterate over blobs in Azure Storage. - A new property on SSIS packages AutoAdjustBufferSize will, when set to true try to calculate the optimum buffer size for the package. - Support for Excel 2013 as a datasource. - ODataSource and Connection managers now support v3 & v4 protocols enabling support for JSON and ATOM. These features certainly will make some improvements to what is already an excellent product. Fixing some of the major bugbears such as VS support for different versions and showing column names in the event of an error rather then leaving the developer to guess will further improve the product and the additional support for connections to Azure will be very useful for those hosting data in the cloud. Unfortunately, when I asked if the issues of source control around SSIS packages will be addressed (difficulties to collaboratively work & add packages when several people are working on the same project) this is not something we can expect for this release. SSAS Last but not least there were a number of changes to SSAS in both the new Tabular models and the traditional multidimensional cubes. - Tabular models will have proper support for many-to-many relationships and bi-directional cross filtering. Whilst for many this was an expected feature already being available in the Power BI Desktop app, this is a major improvement. I think that currently one of the major reasons people choose to implement the traditional multidimensional model over Tabular is the complexity around these measure calculations in Tabular. - Improved designed for Tabular models – this was shown in the demonstration and included some major improvements such as colour highlighting of DAX code, better intellisense and the ability to add comments to measure definitions. Whilst from an end user perspective there will be no change, certainly from a productivity and developer happiness side these features will be hugely appreciated and help make the Tabular model a more mature product. Again I think this is another feature which is likely to make people who had previously rejected the Tabular model reconsider it’s viability. - Tabular models will support parallel partition processing and the translations, which for Tabular has only been available with BIDS helper as an unsupported feature will become fully supported. - Direct Query has a number of enhancements. Firstly the queries generated from it are far more efficient than previously (they used to be very inefficient, so I will wait tentatively to see how much of an improvement this is). It also supports calculated columns (which must be used with caution!) and perhaps the largest change is that it has better MDX support meaning that Excel can now be used to connect to the model and query it directly. - The DAX language has some improvements - it now supports variables and can return multiple result sets from a single query. - A new Tabular JSON document representing the model will become available and can be used when compatibility level is set to the new 1200 value. From the demo it looked like this format should be far more readable to the eye as well as making changes to the model itself quicker due to an improved structure of the metadata for the model. - Support has been added to track extended events in AS graphically. - Power BI Will support connections to Multidimensional models. - Issues around inefficiencies with MDX queries generated by Excel have been resolved leading to significantly faster performance in the 2016 release of Excel. Overall this is a really nice set of improvements. We see the Tabular model becoming a more mature product and also some enhancements which will give great benefit to users of the traditional MD models, such as integration with Power BI and faster performance in Excel. There are also some significant improvements both to Power BI and the Database engine which I am looking forward to. I should also note that Microsoft have announced their BI Roadmap which gives an overview of their future direction here: Anyways that’s it for now, I’ll leave you with this picture from earlier in the week. When me and colleague Simon took a trip to Mt St Helens :)  

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) - ·        T.K. Ranga Rengarajan’s SQL Server Blog - ·        Jen Underwood SQL Server BI Blog -

Master Data Services SQL Server 2016

Unless you’ve been living under a rock for the last week or so, you will have noticed that CTP2 for SQL Server 2016 has been released and is now available for download. I finally got round to downloading the CTP myself yesterday and my first port of call was to take a look at the new version of Master Data Services, as it’s been given a several improvements. Included in this list of improvements is a revamped front end, so I thought it would be worth making a start by exploring the front end improvements in this blog post. New Look and Feel The first thing that you will notice is the new look and feel that will greet you in the web UI. Here’s the new look: To recap - here’s the equivalent in SQL Server 2014 MDS: The have been a few changes to the actual Explorer grid interface. One is that you now get a Copy Member button and also there is the ability to export your current view to an Excel MDS Add-In favourites file: Another useful change is that on each entity you now get told who last updated the member and who created the member. In the previous version, MDS logged who last updated the member, but it wasn’t displayed to the end users. I’ve spoken to users who need this feature so its good to see it implemented: Entity Dependencies If you go into the Explorer, the main grid is still present and looks the same, but there are a few changes to the menu. There used to be a Explorer menu but this is now gone – after all you can get to the same thing via the other menus. There is a new Entity Dependencies menu, which will list all of the entities within the model, as you can see here: After each entity is some brackets, which indicates the number of dependencies that the entity has. If you click on the entity (e.g. the Region entity), then you will get the opportunity on the Entity Dependencies view to drill down on an entity member in order to locate its dependencies at different levels: Hierarchies and Collections There have been some major changes to MDS hierarchies as covered on the MDS Team blog. In summary, Explicit Hierarchies and collections are now depreciated with the idea that they can be replaced with some of the new features of derived hierarchies. One such feature is the “Unused” node, which shows any members of the Derived Hierarchy that are missing a parent. E.g. in the example below I’ve created a new Area member, but its not been assigned a value for its Big Area attribute: A small but useful change that I’ve noticed is that there is a setting for hierarchies that will auto-collapse a hierarchy branch if you expand a sibling branch: For example, looking back at the screenshot I’ve included above, if North America was expanded, then turning this setting on would mean that that North America would collapse if I expanded the International branch of the hierarchy. Summary These UI improvements to the web interface make a welcomed change to a product that is now in its fourth SQL Server version. It’s great to see Microsoft investing in MDS, this will no doubt help increase the uptake of a product that (aside from in SQL 2014) has now seen steady improvement.