MDS 2016: What’s New in CTP 2.2

Posted by on Aug 4, 2015 in MDS, SQL Server 2016 | One Comment
Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

1

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

1

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.

2

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

3

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

4

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):

5

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

6

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.

7

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.

8

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

11

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;

12

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

9

and then set custom index as you want

10

 

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

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">