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
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.