On Late May when CTP 2.0 of SQL Server 2016 published, I’ve wrote a blog post about What’s New in MDS 2016. Recently CTP 2.2 has been published, and I’ve seen some new features again added to MDS. In this post I’ll explain new features of MDS 2016 CTP 2.2 which are;

• Entity Sync
• SCD Type 2 Support
• Compound Key Index Support

Entity Sync

With this feature you can Sync an entity between two models. One model will be the source, and the other one Target. You can create the entity in target model if it is not exists, or you can sync it with existing entity.

For choosing Entity Sync, go to System Administration, and then Under Manage menu choose Entity Sync

Then you can Add an entity sync operation. You have to set Source (Model, Version, and Entity), and Target (Model, Version, and entity if exists). You can choose to create new entity or sync with an existing entity.

There are two methods of sync; Sync on Demand (showed in image above), and auto sync (image below).

Auto sync will schedule a sync operation (you can specify number of days/hours to schedule), while On demand will only sync with pressing the Execute button in Entity Sync window for the Sync operation

This feature will help to have better control of data management.

SCD Type 2 Support

If you come from Data Warehousing world, then you are familiar with SCD Type 2 terminology. If you don’t know what is SCD; SCD Stands for Slowly Changing Dimension, and it is actually the behavior that our data structure takes to deal with value changes in attributes of a dimension. SCD Type 2 means the previous value of the attribute will be recorded with start date and end date. SCD has many types and detailed description of that won’t fit in this post. I’ll refer you to this great Wikipedia page about SCD.

For having SCD Type 2 the main important feature is to keep track of changes in entity, members, and attributes. MDS keeps this track (even MDS versions before 2016 did that). Here is an example of detailed historical view of transactions that you can achieve with View Transaction on entities (from Excel Add-In or Web UI):

However this transactional view was not easily achievable through subscription views so far. and subscription views only shows the latest values in the data set. the transaction log type in MDS was attribute transaction type, and as you can see in screenshot above all changes mentioned as name/value changes on attributes.

In MDS 2016 CTP 2.2 there is a new transaction log type for Member. you can specify this transaction type in Administration page of entity

After setting this type to Member, you can fetch SCD Type 2 view from Subscription views page. There are two new options here: “Include soft-deleted members” and “Leaf Members SCD Type 2” format.

Well! the result however is not what I was expecting! I was expecting first record to have an end date, and the new record which is current record to have start date from the end date of previous record. It seems that it is a bug here. But don’t worry this product is still on CTP stage, and Microsoft MDS team will definitely resolve that. The main important thing is that this feature is added.

If you choose the option “Leaf Members History” then you will only see historical values as below:

Note that you can get the normal view of the data (the current data rows, with no historical or SCD Type 2) with choosing only “Leaf Members” format.

Also note that “Include Soft-Deleted members option will show the members that has been deleted (from Excel Add-In or Web UI) in the result set;

Compound Key Index Support

You can set a compound index, this will help the performance. To set a custom index go to admin page of entity

and then set custom index as you want

Thanks to Microsoft MDS team for their great effort to make this product better and better everyday.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.