THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Master Data Services Team

Blog for the Microsoft SQL Server Master Data Services (MDS) team. Blog posts are contributed by various team members.

  • Master Data Services 2016 Performance Enhancements

    MDS 2016 includes many improvements to performance and capacity. This document shares some early performance test results that compare MDS 2014 with pre-release MDS 2016.

     

    The tests were run on an Azure GS3 Virtual Machine (8 cores, 112 GB memory, 1 TB premium storage). The test data consists of a model with 7 entities and 2 versions. The largest entity contains 7 million members per version and 18 attributes, 6 of which are domain-based.

     

     

    MDS 2014 SP1

    MDS 2016 (pre-release)

    Operation

    Time

    Time

    Versus MDS 2014

    Copy version

    1:01:25.0

    0:29:17.0

    210%

     

     

     

     

    Metadata operations

     

     

     

    Get model details

    0:00:02.9

    0:00:00.6

    483%

    Create entity with 7 attributes

    0:00:03.3

    0:00:01.2

    275%

    Create entity with 200 attributes

    0:01:02.2

    0:00:02.1

    2,962%

     

     

     

     

    Master data operations (using Excel add-in)

     

     

     

    Load 1,000 members (using filter)

    0:00:01.1

    0:00:00.5

    220%

    Load 50,000 members (using filter)

    0:00:11.7

    0:00:06.5

    180%

    Create 1,000 members

    0:00:05.2

    0:00:03.0

    173%

    Create 50,000 members

    0:03:49.9

    0:02:07.1

    180%

    Update 1,000 members

    0:00:05.9

    0:00:03.5

    169%

    Update 50,000 members

    0:03:45.1

    0:02:21.0

    160%

    Delete 1,000 members

    0:00:02.7

    0:00:00.6

    450%

     

     

     

     

    Model deployment operations

     

     

     

    Create Package

    N/A (30+ hours)

    0:35:19.0

     

    Deploy Package (Update)

    N/A

    5:12:40.0

     

    Deploy Package (Clone)

    N/A

    4:19:02.0

     

     

    MDS 2014 could not complete the model deployment tests because the data set was too large for it to handle. The Create Package operation did not complete after running for more than 30 hours.

     

    The release-to-market (RTM) version of MDS 2016 includes even more performance improvements. Stay tuned for details!

  • Master Data Services - SQL2016 RC1(March) is Released

    What is New:

    Added negate operators support for Business Rules.


    Sample Model Deployment Packages Updated.


    Download Links:

    SQL Server 2016 : https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016

    MDS Excel add in : http://go.microsoft.com/fwlink/?LinkID=398159

     

  • Master Data Services - SQL2016 RC0(February) is Released

    What is New:

    Derived Hierarchy Management Experience Improved

    Download Links:

    SQL Server 2016 : https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016

    MDS Excel add in : http://go.microsoft.com/fwlink/?LinkID=398159

  • Hands On Labs for Master Data Services

    Are you curious what Master Data Services is all about? Or are you looking to quickly develop a Master Data Services solution? Or are you interested in trying out the new features in SQL Server 2016? If you answered yes to any of these questions, we now have couple of Hands On Labs published for you to use. They are self-paced, self-sufficient labs that are intuitive and easy to follow. You can follow them to get started on your solution.

    1. Understanding Master Data Services in SQL Server 2016

    In this lab, you will develop a SQL Server 2016 CTP 3.2 Master Data Services solution to store and manage master lists of geographic data. You will use the Master Data Services Add-in for Excel and the Master Data Manager web application to create entities and hierarchies, and to explore and manage the master data. Finally, an existing SQL Server 2016 CTP 3.2 Integration Services solution will be used to populate the data warehouse DimGeography dimension table.

    You can find the lab here.  

    2. Exploring What's New in Master Data Services in SQL Server 2016

    In this lab, you will explore many new features and capabilities delivered with SQL Server 2016 CTP 3.2 Master Data Services.  

    You can find the lab here

    Try them out and provide us with your feedback.  

  • Master Data Services - SQL2016 CTP3.3(January) is Released

    What is New:

    Business rule changes

    • New and easier-to-use web UI administration page
    • Support for NOT conditional operator
    • Support for ELSE section that contains a set of actions to execute if the IF condition is false
    • Removed management UI from Excel add-in

    Added support for purging (hard-deleting soft-deleted members) of an entity version.

    Added to the web explorer page a button to open the current entity view in the Excel add-in.

    Download Links:

    SQL Server 2016 : https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016

    MDS Excel add in : http://go.microsoft.com/fwlink/?LinkID=398159

  • Master Data Services - SQL2016 CTP3.2(December) is Released

     

    In SQL Server 2016 CTP3.2 release, MDS is introducing following features.

    Purge Soft Deleted Members

    You can now purge (permanently delete) all soft-deleted members in a model version. Deleting a member only deactivates, or soft-deletes, the member. For more information, see Purge Version Members (Master Data Services).

    Change Set Email and Management

    In this release, you can now view and manage all changes by model and version. You can also receive email notifications each time a change set status changes for an entity that requires approval. For more information, see View Change Sets (Master Data Services) and Notifications (Master Data Services).

    Edit Many-to-Many Relationships in Derived Hierarchies

    You can edit the many-to-many relationship by modifying the mapping entity members. For more information, see Show Many-to-Many Relationships in Derived Hierarchies (Master Data Services).

    View and Manage Revision History

    You can view and manage revision history, by entity and by member. If you have update permissions, you can roll back a member to a previous version. For more information, see Member Revision History (Master Data Services).

    Download Links:

    SQL Server 2016 : https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016

    MDS Excel add in : http://go.microsoft.com/fwlink/?LinkID=398159

  • Master Data Services - SQL2016 CTP3.1(November) is Released

    SQL Server 2016 Community Technology Preview 3.1 is publicly released.

    In this release we

    You can down load SQL Server 2016 CTP3.1 bits from http://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016.

    Download MDS Excel add in for CTP3.1 from https://www.microsoft.com/en-us/download/details.aspx?id=50015

  • In SQL Server 2016 Master Data Services how to filter Domain Based Attribute drop down lists

    A domain-based attribute (DBA) can have as its value any member of the specified domain entity. In the next release, we add the ability to constrain this list of possible values by filtering on the value of another DBA within the entity.

    Consider the following entity diagram:

    clip_image001[4]

    The Customer entity has a DBA that references the City entity, which references the StateProvince entity. When adding/modifying Customers, it can be inconvenient to select from a potentially large list of Cities from all StateProvinces. One can ameliorate this by using the DBA picker control, in the Web UI’s explorer page, to filter the cities by State, or filter by any other desired combination of City attributes. But some users have expressed the desire to add a State DBA to the Customer entity, and to keep it in sync with and have it filter allowable values for the City DBA:

    clip_image002[4]

    Once could argue that this is a bad way to model the data as the new StateProvince DBA is redundant, denormalized, and makes possible data inconsistency (i.e. Customer.City.StateProvince could mismatch Customer.StateProvince). But since this is a frequent customer request we add support for it, to include preventing data inconsistency.

    Users can now add a filter to a DBA definition. In the above example, the filter would be added to the Customer.City DBA. The filter specifies:

    1. The parent DBA (Customer.StateProvince), and

    2. A Derived Hierarchy (DH) level the parent-child relationship between the two entities (StateProvince and City). In the above example, the user must define a DH that includes a level for the City.StateProvince DBA.

    The below screenshot shows how one selects a Filter when creating/editing a DBA in the Admin Web UI. The Filter drop-down list is populated with all potential parent attributes that meet the above two conditions.

    clip_image003[4]

    The below screenshot shows how the Explorer page for the Customer entity enforces the filter: for any selected Customer, the list of cities only includes those pertaining to the currently selected StateProvince:

    clip_image004[4]

    The Excel add-in does likewise:

    clip_image005[4]

    Note that when a parent attribute has a blank value, the child drop down list will only include members whose DH level parent is blank. (i.e. those members that appear under the “Unused” node in the DH explorer page). In the above example, when Customer.StateProvince is blank, the City drop down list shows Cities where City.StateProvince is likewise blank.

    Entity explorer:

    clip_image006[4]

    Hierarchy explorer:

    clip_image007[4]

    Many-to-Many

    The above City-StateProvince example shows a one-to-many DH level, but a filter may also reference a many-to-many (M2M) DH level. Consider the below example:

    clip_image008[4]

    Each Product has a Model and a Color. But there exists a mapping entity, AllowedColor, that specifies which Colors are valid for which Models. To filter the list of possible values for the Product.Color DBA based on the value of the Product.Model DBA:

    1. Create an M2M DH that groups Colors by Model, mapped by AllowedColor.

    2. Add a filter to the Product.Color DBA that specifies Product.Model as the parent DBA and the M2M DH level as the parent-child relationship.

    Recursive

    A filter may also specify a recursive DH (RDH) level:

    clip_image009[4]

    In the above example, each Customer has a primary and secondary contact person who is an Employee. Each Employee has a Manager. To constrain the SecondaryContact person to be an Employee who is managed by the PrimaryContact person:

    1. Create a RDH that groups Employees by Manager

    2. Add a filter to Customer.SecondaryContact that specifies Customer.PrimaryContact as the parent DBA and the above RDH level as the parent-child relationship.

    Alternately, one could flip the constraint by instead adding the filter to PrimaryContact such that it must be an Employee that is managed by the SecondaryContact.

    Filter Constraints

    · An attribute filter may not specify a DH level that is hidden.

    · Attribute filters can only be added to Leaf attributes, but not deprecated Consolidated or Collection attributes.

    · Attribute filters may be chained. E.g. StateProvince filters City filters PostalCode.

    · Attribute filters may not form a circular dependency. E.g. A filters B filters A.

  • What's New in Master Data Services - SQL2016 CTP3 (Oct) Release

     In SQL Server 2016 CTP3 release, MDS is introducing following features.

    Change Sets

    You can use change sets to save pending changes to an entity, and you can view and modify pending changes. If the entity requires approval for changes, you must save the pending changes into a change set and submit for approval by the administrator. For more information, see Change Sets (Master Data Services).

    Attribute Filters

    For a domain-based attribute, for a leaf member, you can use a filter parent attribute to constrain the allowed values for the domain-based attribute. For more information, see Create a DomainBased Attribute (Master Data Services).

    You can download SQL Server 2016 CTP3 bits from:      http://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016.

    Download MDS Excel addin for CTP3 from :                   https://www.microsoft.com/en-us/download/details.aspx?id=49530

  • What's New in Master Data Services - SQL2016 CTP2.4 (Sep) Release

    SQL Server 2016 Community Technology Preview 2.4 is publicly released and the latest SQL Server bits can be found at http://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016.

    MDS Excel Add-in can be found at : https://www.microsoft.com/en-us/download/details.aspx?id=47343

    In CTP2.4 MDS introduced feature to extend Business Rules conditions and actions.

    Business Rules Extension

    Business rule is a rule that you use to ensure the quality and accuracy of your master data. You can use a business rule to automatically update data, to send email, or to start a business process or workflow. The system only provides a certain number of pre-defined conditions and actions. We are introducing Business Rule Extension in SQL Server 2016, which enables customers to define more flexible and powerful Business Rules. Users can create their own rules for both conditions and actions.

    Create your own conditions

    SQL functions that match following criteria can be used as Business Rule conditions.

    • Must be defined under [usr] schema.
    • The return value type must be BIT.
    • Only following types are supported for parameter types.
      • NVARCHAR
      • DATETIME2
      • DECIMAL (precision, scale)
        • precision must be 38
        • scale must be a value from 0 to 7

    Example

    CREATE FUNCTION [usr].[IsDateEmpty]

    (

    @Value DateTime2

    )

    RETURNS BIT

    AS BEGIN

    SET @Value = NULLIF(LTRIM(RTRIM(@Value)), N'')

    IF @Value IS NULL

    BEGIN

    RETURN 1

    END

    RETURN 0

    END

     

    Create your own actions

    SQL stored procedures that match following criteria can be used as Business Rule actions.

    • Must be defined under [usr] schema.
    • Only contain following parameters
      • @MemberIdList mdm.[MemberId] READONLY
      • @ModelName NVARCHAR(MAX)
      • @VersionName NVARCHAR(MAX)
      • @EntityName NVARCHAR(MAX)
      • @BusinessRuleName NVARCHAR(MAX)

    Example

    CREATE PROCEDURE [usr].[SetCurrentDate]

    (

    @MemberIdList mdm.[MemberId] READONLY,

    @ModelName NVARCHAR(MAX),

    @VersionName NVARCHAR(MAX),

    @EntityName NVARCHAR(MAX),

    @BusinessRuleName NVARCHAR(MAX)

    )

    AS BEGIN

    INSERT INTO stg.Product_Leaf (ImportType, BatchTag,Code,CreatedDTM)

    SELECT 0, N'BR', Code, GETDATE()

    FROM @MemberIdList

    EXEC[stg].[udp_Product_Leaf]

    @VersionName = @VersionName,

    @BatchTag = N'BR'

    END

     Script actions will be executed as mds_br_user which has following permissions  

    Schema

    Permissions

    mdm

    SELECT

    stg

    SELECT, UPDATE, DELETE, EXECUTE, INSERT

    usr

    FULL

     

    Create a Business Rule with script conditions and script actions

    Let’s create a business rule against Product entity that updates the CreatedDTM to current date if the value is not set. You can get the Product entity from Sample Packages.

    Navigate to Business Rule Manage page after create script condition and action using sample code above. Choose Product model and Product entity and then click Add Button, now a new empty Business Rule is created.

    image

    Then click Edit button, we can find IsDateEmpty and SetCurrentDate are shown on the components panel.

    image

    Now let’s drag “ IsDateEmpty” to IF panel on the right, and then drag CreatedDTM from Attributes Panel to Edit Condition Panel. Click Save button to save the condition.

    image

    In order to create the action, just drag SetCurrentDate to Then panel and click save on the Edit Action Panel.

    image

    After creating the Business Rule, click top left Back button to go back to the management page and click “Publish business rules” button to activate the rule.

    Now let’s test the Business Rule we just created.

    1. Choose Product model and an open version in the main page

    2. Click Explorer Task button.

    3. Choose Product entity from Entities menu.

    4. Click Marketing attribute group

    5. Click Add Member and enter “BB-1111” in Code field.

    6. Click Ok button

    7. Now we can see the CreatedDTM for Product “BB-1111” is set to current date.

    image

    For more information, see Business Rules Extension (Master Data Services).

  • What's New in Master Data Services - SQL2016 CTP2.3 (Sep) Release

    We are excited to share the new features which shipped in SQL CTP2.3 public release.  You can download the product here:http://www.microsoft.com/en-us/download/details.aspx?id=48726, and Excel add-in here: http://www.microsoft.com/en-us/download/details.aspx?id=47343

    In this monthly release, we added a few highly asked features.  Please give it try and look forward to hearing your feedback.

    Manage Business Rules (MDS Add-in for Excel)

    In the Master Data Services Add-in for Excel you can manage Business Rules, such as creating and editing business rules. Business rules are used to validate data. For more information, see Manage Business Rules (MDS Add-in for Excel).

    Many-to-Many Relationships in Derived Hierarchies

    You can now create a Derived Hierarchy that displays many-to-many relationships. A many-to-many relationship between two entities may be modeled through the use of a third entity that provides a mapping between them. The mapping entity is an entity that has two or more domain-based attributes referencing other entities.

    For example, entity M has a domain-based attribute that references A and a domain-based attribute that references B. You can create a hierarchy from A to B using the mapping entity.

    For more information, see Show Many-to-Many Relationships in Derived Hierarchies (Master Data Services)

    Merge Conflicts

    If you try to publish data that has been changed by another user, the publish will fail with a conflict error. To resolve this error, you can perform merge conflicts and republish the changes. For more information, see Merge Conflicts (Master Data Services) and Merge Conflicts (MDS Add-in for Excel)

  • What's New in Master Data Services - SQL2016 CTP2.2 (July) Release

    SQL Server 2016 Community Technology Preview 2.2 is publicly released and the latest bits can be found at http://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016

    In CTP2.2 MDS introduced following changes:

    Custom Indexes

    You can create a non-clustered index on one attribute (single index) or on a list of attributes (composite index), in an entity, to help improve the query performance. For more information, see Custom Index (Master Data Services).

    Entity Sync Relationship

    You can share entity data between different models by creating an entity sync relationship. For more information, see Entity Sync Relationship (Master Data Services).

    Member Revision History

    A member revision history is recorded when a member is changed. You can rollback a revision history, as well as view and annotate revisions. Using the Log Retention Days property, you can specify how long historical data is retained. For more information, see Member Revision History (Master Data Services).

  • What's New in Master Data Services - SQL2016 CTP2 (May) Release

    SQL Server 2016 Community Technology Preview 2 is publicly released and the latest SQL Server bits can be found at http://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016.

    Improved Performance and Scale

    We have made significant improvement on performance from backend database, middle tier services to frontend UI including both WebUI and Excel UI. The performance is improved on all scenarios, especially on the master data loading, while the server side CPU and memory will be lower than previous version.

    The default settings are optimized based on our test data, should be good for most of customer scenarios. There are few settings can be tweaks based on individual scenario.

    1. Data Compression

    We introduce the data compression on entity level. Default setting is enabled.

    clip_image001

    When data compression is enabled, all this entity related tables and indexes are compressed with SQL Row Level compression. It will significant reduce the Disk I/O when read or update the master data, especially when master data has millions of rows and/or has lots of NULL value columns.

    But it will slightly increase the CPU usage on the SQL engine side, as well. Since major of the SQL operation is I/O bound, we set default value as True.

    If you are facing CPU bound on the server, you may turn off data compression by Edit the Entity.

    More information can be found at:

    https://msdn.microsoft.com/en-us/library/cc280449.aspx

    2. Dynamic Data Compression

    We enforced the server enable the feature Dynamic Content Compression IIS feature. The default setting in the service is enable dynamic compression.

    clip_image002

    The MDS API is using xml format. Enable dynamic content compression will significant reduce the size the xml response and save the network I/O, while increase the CPU usage.

    If you are facing CPU bound on the server, you may turn off data compression by put following setting in the web config.

    <configuration>
    <system.webServer>
    <urlCompression doStaticCompression="true" doDynamicCompression="false" />
    </system.webServer>
    </configuration>

    More information can be found at:

    http://www.iis.net/configreference/system.webserver/urlcompression

    Although, both of above change will lower I/O usage by trading off CPU usage, we did great improvement on service side CPU as well. So we are expecting the CPU usage is lower than previous in general, even with compression enabled.

    3. Index maintenance.

    We found index fragmentation and increasing transaction log are the two main reasons why performance is degrading over time in many of user cases.

    We introduce two SQL Job to do index maintenance and log maintenance.

    We have a different blog for Log Maintenance.

    clip_image003

    The default schedule for Index Maintenance job is Weekly, Saturday 2AM. You can change/add more schedule in job property.

    You can also manual kick run the index maintenance by execute store procedure udpDefragmentation.

    Exec [mdm].[udpDefragmentation]

    We recommend to run this sproc to maintain the indexes after each massive master data insert or update, or after new version is created from existing version.

    Index with more than 30% fragmentation will be rebuild online. During the rebuild, the performance will be affected on the CRUD operation on the same table. Run the store procedure at off business hours if the performance degrade is a concern.

    More information about index fragmentation problem can be found at:

    https://msdn.microsoft.com/en-us/library/ms189858.aspx

    For related information refer to:

    Create an Entity (Master Data Services)

    What’s New (Master Data Services)

     

    Improved Security

    Super User Functional permission

    In the previous release, whoever install the MDS originally, is the server admin. The user will have user id 0. There is no easy way to transfer server admin from one user to the other. It will require DB admin go the user table and change SID on user id 0. There is no easy to have multiple server admins or give server admin permission to a group.

    New Super User functional permission is introduced in this release. Super user has the same permission as user id 0 in the previous releases. It can be assigned in same way that assign other functional permission. It can be assigned to user or group.

    clip_image001

    Model Admin Permission

    In the previous release, model admin permission is implicitly assigned based on some calculation result. If user only have update permission on the model level and do not have any other permission in the model sub tree, the user is model admin. If someday later, user got another explicit permission assign in the model sub tree, such as entity level, the user will lose model admin permission.

    New explicit Model Admin Permission is introduced in this release. The admin is available on the model level.

    clip_image002

    Granular Access Permission

    In the previous release, we have Read-Only and Update permission. It is similar to R/RW permission, where RW permission means all the permission including Create Update and Delete.

    And we have been seeing more and more user case requires more granular permission than RW, especially spit delete/create from update. So user can only update the master data but cannot create or delete the data.

    So we introduce 4 granular access permission Read, Create, Update and Delete. They can be used together. For example, Create+Update means user can create and update but cannot delete.

    Since it does not make sense user only have Create, Update or Delete without Read permission, so when assign Create, Update and Delete permission to user, Read is given automatically. For example, assign user Update permission, the UI will show user has Update and Read permission.

    clip_image003

    Comparison   

    Previous Release

    Current Release

    User Id 0

    User has Super User functional permission

    User has update permission on model and doesn’t any permissions in the subtree

    User has Admin Permission on model

    Read-Only

    User has Read Access Permission Only

    Update

    User has all 4 Access Permission.

    Shortcut in UI in All

    Deny

    Deny

     

    The user with old permission will be converted to new permission, during upgrade.

    You can find more information at:

    Administrators (Master Data Services)

    Model Object Permissions (Master Data Services)

    Hierarchy Member Permissions (Master Data Services)

     

    Transaction Log Maintenance

    Transactions, Validation Issue and Staging table cleanup

    Master Data Services previous versions didn’t have a supported way to clean the transaction logs, validation issues history and Staging tables. For a MDS system with lot of data changes and ETL processes these tables can grow exponentially and lead to performance degradation and storage space issues.

    In SQL Server 2016 we will provide option to clean these tables on a pre-determined intervals or schedule. Based on the user setting the data in these tables will be truncated.

    What is cleaned?

    All the transaction history older than specified number of days, all the validation issues history older than specified number of days and all the staging batches which ran before specified number of days.

    How:

    There will be settings where user can specify for how many days he wants to retain the logs and data in these tables. He can do this in two places. One using a System Setting (LogRetentionDays) which will apply to all the models in the system. Second ways is to set the property at Model level which will override the system level setting.

    System Setting:

    By default the system setting will be -1 which means do not truncate/clean any tables. If the value is 0 then the tables will retain only today’s data and all the previous days data logs will be truncated. For n > 0 the logs will be retained for n number of days.

    To set this value use the MDS Configuration Manger tool and change the setting shown below.

    clip_image002

    Model level Setting:

    By default the model setting is NULL which means the value will be inherited from the System Setting “Log Retention in Days”. If you want to override system setting and NOT clean any logs then set as -1. If you do not want to retain any transaction logs and validation history and already processed staging data then set as 0. For n > 0 the logs will be retained for n number of days. Acceptable values: -1 to 5000.

    To Set or Update the Model property in the MDS Web UI go to System Administration -> Manage -> Model and change the property shown below

    clip_image003

    Scheduler:

    When you create the database from Configuration Manager, by default it schedules a SQL Agent Job, “MDS_<DB Name>_Log_Maintenance” which triggers every day at 2AM and scans all the models and cleans the table as per the settings above.

    If you want to change the frequency of this job then use the SQL Agent -> Job (MDS_<DB Name>_Log_Maintenance) and update the schedule. You can use SQL Server Management Studio to make any changes to this scheduled job.

    How to manually cleanup the tables without setting a recurring cleanup as above

    You can call the Stored Procedures manually which will clean up the tables. To clean Transaction tables use mdm.udpTransactionsCleanup, to clean Validation History use mdm.udpValidationsCleanup and to clean staging table use mdm.udpEntityStagingBatchTableCleanup.

    Sample:

    DECLARE @CleanupOlderThanDate date = '2014-11-11',

    @ModelID INT = 7

    --Cleanup Transaction Logs

    EXEC mdm.udpTransactionsCleanup @ModelID, @CleanupOlderThanDate;

    --Cleanup Validation History

    EXEC mdm.udpValidationsCleanup @ModelID, @CleanupOlderThanDate;

    --Cleanup EBS tables

    EXEC mdm.udpEntityStagingBatchTableCleanup @ModelID, @CleanupOlderThanDate;

    More related information can be found at:

    Transactions (Master Data Services)

    Create a Model (Master Data Services)

    System Settings (Master Data Services)


    Improved Troubleshooting and Logging

    In the SQL Server 2016 CTP2, we did logging improvements to improve debugging and make the troubleshooting easier.

    1.       Tracing setting in the web.config

        <sources>

          <!-- Adjust the switch value to control the types of messages that should be logged.

               http://msdn.microsoft.com/en-us/library/system.diagnostics.sourcelevels

               Use the a switchValue of Verbose to generate a full log. Please be aware that

               the trace file can get quite large very quickly -->

          <source name="MDS" switchType="System.Diagnostics.SourceSwitch" switchValue="Warning, ActivityTracing">

            <listeners>

              <!-- Set a directory path where the service account you chose while setting up Master Data Services has read and write privileges.

                   Default path is Logs in WebApplication folder, for example C:\Program Files\Microsoft SQL Server\130\Master Data Services\WebApplication

                   New log file will be created everyday or every 10 mb.

                   When directory size hits the 200mb limitation, the oldest file will be deleted.-->

              <add name="FileTraceListener"

                   type="Microsoft.MasterDataServices.Core.Logging.FileTraceListener, Microsoft.MasterDataServices.Core"

                   initializeData="DirectoryPath = Logs; FileSizeInMb = 10; MaxDirectorySizeInMb = 200"/>

              <remove name="Default"/>

            </listeners>

          </source>

        </sources>

         We have a new section in the web.config for tracing setting.

         By default,

    a.       Tracing is enabled for Warning and below level, plus ActivityTracing.

    b.      The Logs are saved in the Logs folder under the WebApplication folder. For example, C:\Program Files\Microsoft SQL Server\130\Master Data Services\WebApplication

    c.       The file file will be created for each day or every 10 mb

    d.      Maximum usage on Logs folder will be 200mb, the oldest log will be deleted.

    e.      The log format is CSV.

    2.       Log format

     

    Time

    When the trace entry happens

    CorrelationId

    One correlation id is assigned for each request. The all the traced trigged by this request will share the same correlation id.

    Operation

    The request operation name. If the request is web ui request, the operation name is the url. If request is API request, the operation name is the service name.

    Level

    The level of this trace entry.

    Message

    The message body of the trace

     

    The log is CSV format and can be opened in Microsoft Excel or other tools for filtering.

     

    3.       Correlation Id

    Each request will be assign a correlation id, the all trace trigger by this request will share the same id.

    When an error happens, in the UI, the correlation id will be shown in the error message.

     

    Web UI:

    clip_image001[6]

     

    Excel Addin UI:

     

    clip_image003[6]

    https://msdn.microsoft.com/en-us/library/system.diagnostics.sourcelevels(v=vs.110).aspx

    Use the a switchValue of Verbose to generate a full log. Please be aware that

    the trace file can get quite large very quickly -->

    <source name="MDS" switchType="System.Diagnostics.SourceSwitch" switchValue="Warning, ActivityTracing">

    <listeners>

    <!-- Set a directory path where the service account you chose while setting up Master Data Services has read and write privileges.

    Default path is Logs in WebApplication folder, for example C:\Program Files\Microsoft SQL Server\130\Master Data Services\WebApplication

    New log file will be created everyday or every 10 mb.

    When directory size hits the 200mb limitation, the oldest file will be deleted.-->

    <add name="FileTraceListener"

    type="Microsoft.MasterDataServices.Core.Logging.FileTraceListener, Microsoft.MasterDataServices.Core"

    initializeData="DirectoryPath = Logs; FileSizeInMb = 10; MaxDirectorySizeInMb = 200"/>

    <remove name="Default"/>

    </listeners>

    </source>

    </sources>

    We have a new section in the web.config for tracing setting.

    By default,

    a. Tracing is enabled for Warning and below level, plus ActivityTracing.

    b. The Logs are saved in the Logs folder under the WebApplication folder. For example, C:\Program Files\Microsoft SQL Server\130\Master Data Services\WebApplication

    c. The file file will be created for each day or every 10 mb

    d. Maximum usage on Logs folder will be 200mb, the oldest log will be deleted.

    e. The log format is CSV.

    2. Log format

    Time

    When the trace entry happens

    CorrelationId

    One correlation id is assigned for each request. The all the traced trigged by this request will share the same correlation id.

    Operation

    The request operation name. If the request is web ui request, the operation name is the url. If request is API request, the operation name is the service name.

    Level

    The level of this trace entry.

    Message

    The message body of the trace

     

    The log is CSV format and can be opened in Microsoft Excel or other tools for filtering.

    3. Correlation Id

    Each request will be assign a correlation id, the all trace trigger by this request will share the same id.

    When an error happens, in the UI, the correlation id will be shown in the error message.

    Web UI:

    clip_image001[4]

    Excel Addin UI:

    clip_image003[4]

    For more information:

    Tracing (Master Data Services)

     

    Improved Manageability

    Improvements in manageability help to lower maintenance costs and positively impact your return on investment (ROI). These improvements include transaction log maintenance and improvements to security, as well as the following new features.

    • Using attribute names that are longer than 50 characters.

    • Renaming and hiding Name and Code attributes.

    For more information, see the following topics.

     


  • Deprecated in SQL 2016: Explicit Hierarchies and Collections


        The SQL Server 2016 version of Master Data Services deprecates Explicit Hierarchies (EH) and Collection and all related components. Members that before were modeled as Consolidated (EH parent) and Collection member types will henceforth be modeled as Leaf members in Derived Hierarchies (DH). This is made possible by new DH features that better enable them to take the place of EHs: 

    1.       Recursive Derived Hierarchies (RDH) can now be used to assign member security permissions

    clip_image002[4]

    In the above example, the simple RDH is comprised of a single recursive level representing an Employee->Manager relationship. RDHs can be more complex, by including level(s) below and/or above a recursive level. The analog of an EH is a RDH with a single non-recursive level beneath the recursive level.

     

    Member duplication: But be aware that when a RDH contains a level above a recursive level, members at the recursive level can appear more than once within the hierarchy (under their recursive parent and under their non-recursive parent). Member duplication can also happen when the topmost level is recursive but does not anchor null recursions, in which case each member appears directly under Root and under its non-null parent. The number of times a member appears in such a hierarchy is equal to its maximum depth within the hierarchy. RDHs that allow member duplication cannot be used for member security. Otherwise, a member could inherit permissions along different inheritance paths, resulting in ambiguous resolution.

     

    2.       The DH Explorer page now shows unassigned (unused) members for each hierarchy level:

    clip_image004[4]

    In the above screenshot, the “MyUnusedSubCategory” member of the SubCategory entity is not assigned to a category. That is, the value of its Category attribute is blank, as show in the grid on the right. Before, such orphaned members could not be easily seen in the DH Explorer. But now they and any of their children are shown under the new “Unused” node. Unused nodes are grouped by hierarchy level. Members can be dragged-and-dropped or cut-and-pasted back and forth between the Unused and Root nodes, at the applicable levels.

     

    The Unused node is likewise visible in the Preview pane when managing DH levels from the System Administration page…

    clip_image006[4] 

     

    … and when managing hierarchy member security permissions:

    clip_image008[4]

    Any member, whether under Root or Unused, can be assigned a permission. The Root, Unused, and Unused Level pseudo members can also be assigned permissions.

     

    To reduce UI clutter, the Unused nodes will only be shown when applicable. For example, a hierarchy with only a single level cannot have unused members, because all members appear under root. So the Unused node will not be shown. Likewise for a simple RDH with no levels above or below the recursive level:

    clip_image010[4]

     

     

    For a hierarchy that can only have unused members at a single level (like a DH with two levels or a RDH with a single level below the recursive level), the level-specific unused nodes will not be shown. Instead, all unused members will appear directly under the top-level Unused node. The below screenshot shows the Employee->Manager RDH with a single level below the recursive level, that indicates the Project Managers (PM) for the Project entity.

    clip_image012[4]

     

    3.       Added a stored procedure that converts EHs to RDHs and Consolidated and Collection members to Leaf members: mdm.udpConvertCollectionAndConsolidatedMembersToLeaf

    For this next release, EHs and non-leaf members are deprecated but still fully supported. The conversion sproc is optional and is only executed manually. But in a future release when support for these deprecated item is removed, the conversion sproc will be mandatory and run as part of the db upgrade process. It is recommended that users of EHs and/or Collection members run this sproc as soon as practicable, to get an earlier start in identifying and resolving any issues. Backup the database before running the sproc! See the sproc’s code header for more details about the conversion process, which as of this writing is thus:

    /*

    ==============================================================================

     Copyright (c) Microsoft Corporation. All Rights Reserved.

    ==============================================================================

     

    Converts deprecated collection members and consolidated members to leaf members. Also converts their corresponding deprecated explicit hierarchies (EH) to recursive derived hierarchies (RDH).

     

    Conversion process:

     

    Converting Consolidated members and EHs

    ----------------------------------------

    Suppose there is an entity named Product that contains the following EHs:

       EH Name          Mandatory EH?

      *********        ***************

       Bundle               Yes

       NonMandatory         No

     

    - Create one new entity per EH. The name of the new entity is "<Leaf entity name>_<EH name>". In the above example, two new entities will be created, "Product_Bundle" and "Product_NonMandatory".

        -- Copy consolidated attributes from original EH's entity to leaf attributes on each new entity.

        -- Copy all consolidated members of the EH to leaf members on the corresponding new entity. For mandatory hierarchies that have leaf members directly under ROOT, add a "Pseudo Root" member. For non-mandatory EHs that have unassigned leaf members, leave them as-is.

        -- Add a new self-referencing DBA, named "Parent", to the new entity and set its value to reflect the original consolidated member's parent in the EH.

    - Create new DBAs on the original entity, one per EH on the entity. The name of each DBA will be the EH name. In the above example, two DBAs will be added to the Product entity: Bundle (which references Product_Bundle as its domain entity) and NonMandatory (which references Product_NonMandatory). The value of these DBAs is set to reflect each leaf member's parent in the EH being copied.

    - Copy transactions. EH parent/sibling move transactions are converted to attribute value change transactions. If the @DeleteOriginals flag is set, the original transaction rows will be updated, rather than copied, for efficiency. Otherwise, transaction annotations are also copied.

    - Create one new RDH per EH. Each RDH will have a single non-recursive level beneath the recursive levels. The name of the RDH will follow the same pattern as the new entity

      names, i.e. "<Leaf entity name>_<EH name>", or "Product_Bundle" and "Product_NonMandatory" in the above example.

    - For each existing DH with an EH cap, create a new RDH with (potentially) multiple levels below. The name of the new hierarchy will be "<EH cap DH name>_NoCap".

    - Copy business rules (BR), both rules that applied to the consolidated members being copied and leaf rules that were referencing consolidated parent attributes. In both cases, the consolidated parent attribute references are converted to instead apply to the attributes of the new entities.

     

    Converting Collections.

    For each entity with collection member(s):

    ----------------------------------------

    - Create a new entity "<entity name>_Col". This corresponds to the deprecated "%_CN" table.

        -- Copy collection attributes from original entity to leaf attributes on the new entity

        -- Copy collection members from original entity to leaf members on then new entity

    - Create new entity "<entity name>_ColMem" to track collection membership info. This corresponds to the deprecated "%_CM" table.

        -- Add parent DBA, "Parent_<entity name>_Col", that will reference the parent "collection"

        -- Add child DBAs, that correspond to collection members (exactly one will have a non-null value, per member):

            -- "Child_<entity name>" for leaf members of the collection. References the original entity.

            -- "Child_<entity name>_Col" for collection members of the collection

            -- "Child_<EH name>", one per EH on the original entity, for consolidated members of the collection

        -- Add attributes for "%_CM" table columns Weight and SortOrder

        -- Create new members, one for each "%_CM" table row

    - Copy collection member transactions. If the @DeleteOriginals flag is set, the original transaction rows will be updated, rather than copied, for efficiency. Otherwise, transaction annotations are also copied.

     

    Additional steps

    ----------------------------------------

    - Copy consolidated and collection attribute groups to leaf attribute groups on their corresponding new entities.

    - Copy consolidated and collection subscription views (SV)

            Old ViewFormat_ID            Deprecated?    New ViewFormat_ID

           *******************          *************  *******************

            1 (Leaf members)                No          unchanged

            2 (Consolidated members)       Yes          1 (Leaf members)

            3 (Collection members)         Yes          1 (Leaf members)

            4 (Collection membership)      Yes          1 (Leaf members)

            5 (Explicit Parent-Child)      Yes          7 (Derived Parent-Child)

            6 (Explicit levels)            Yes          8 (Derived levels)

            7 (Derived Parent-Child)        No          unchanged

            8 (Derived levels)              No          unchanged

        For the consolidated-related SVs (ViewFormat_ID 2, 5, and 6) there is a one-to-many mapping. One new SV is created per EH. To disambiguate them, the EH name is appended to the SV name.

    - Security permissions

        -- Copy model object permissions from old entity and its consolidated and collection member types, attributes, and attribute groups, to their corresponding new entity objects.

        -- Copy member permissions from old EHs to new RDHs (collections and DHs with an EH cap cannot be used for member security, so they will have no permissions to copy).

    - If the @DeleteOriginals flag is set, delete the originals of the copied items.

    - Kick off the service broker to process member security.

     

    Additional notes:

    1. EH child sort order info, as stored in the "%_HR" table's SortOrder column, is lost. Derived Hierarchies do not support child sort order.

    2. All copied/converted BRs are left in an unpublished state. The user must manually publish these rules, if desired, after running this script (the publish process can only be executed from the web service API, not from SQL)

    3. Validation issues (as stored in tables tbl_{MID}_VL, tbl_{MID}_VLH)) are not copied. They can be regenerated after BRs are published.

    4. The copy process checks for and handles any naming conflicts that would violate uniqueness constraints. For example, if the script tries to create a new entity named "Product_Bundle", but that name is already used by another entity within the model, then it will try to use "Product_Bundle1" for the new entity name. If that name is already taken, then it will try "Product_Bundle2", and so on.

    5. The copy process preserves original audit info (i.e. created/lastChanged userID/dateTime, etc) for copied items (i.e. master data, DH levels, security permissions, transactions, business rules, etc).

    */

    CREATE PROCEDURE mdm.udpConvertCollectionAndConsolidatedMembersToLeaf

    (

         @Model_ID          INT = NULL -- Optional filter. When provided, only EHs and Collections within the specified model will be operated on.

        ,@Entity_ID         INT = NULL -- Optional filter. When provided, only EHs and Collections within the specified entity will be operated on.

        ,@DeleteOriginals   BIT = 1 -- When 0, the sproc is non-destructive. When 1, the sproc deletes the original collections, consolidated members, EHs, etc that are converted.

    )

    AS BEGIN ...

     

    4 (Collection membership) Yes 1 (Leaf members)

    5 (Explicit Parent-Child) Yes 7 (Derived Parent-Child)

    6 (Explicit levels) Yes 8 (Derived levels)

    7 (Derived Parent-Child) No unchanged

    8 (Derived levels) No unchanged

    For the consolidated-related SVs (ViewFormat_ID 2, 5, and 6) there is a one-to-many mapping. One new SV is created per EH. To disambiguate them, the EH name is appended to the SV name.

    - Security permissions

    -- Copy model object permissions from old entity and its consolidated and collection member types, attributes, and attribute groups, to their corresponding new entity objects.

    -- Copy member permissions from old EHs to new RDHs (collections and DHs with an EH cap cannot be used for member security, so they will have no permissions to copy).

    - If the @DeleteOriginals flag is set, delete the originals of the copied items.

    - Kick off the service broker to process member security.

    Additional notes:

    1. EH child sort order info, as stored in the "%_HR" table's SortOrder column, is lost. Derived Hierarchies do not support child sort order.

    2. All copied/converted BRs are left in an unpublished state. The user must manually publish these rules, if desired, after running this script (the publish process can only be executed from the web service API, not from SQL)

    3. Validation issues (as stored in tables tbl_{MID}_VL, tbl_{MID}_VLH)) are not copied. They can be regenerated after BRs are published.

    4. The copy process checks for and handles any naming conflicts that would violate uniqueness constraints. For example, if the script tries to create a new entity named "Product_Bundle", but that name is already used by another entity within the model, then it will try to use "Product_Bundle1" for the new entity name. If that name is already taken, then it will try "Product_Bundle2", and so on.

    5. The copy process preserves original audit info (i.e. created/lastChanged userID/dateTime, etc) for copied items (i.e. master data, DH levels, security permissions, transactions, business rules, etc).

    */

    CREATE PROCEDURE mdm.udpConvertCollectionAndConsolidatedMembersToLeaf

    (

    @Model_ID INT = NULL -- Optional filter. When provided, only EHs and Collections within the specified model will be operated on.

    ,@Entity_ID INT = NULL -- Optional filter. When provided, only EHs and Collections within the specified entity will be operated on.

    ,@DeleteOriginals BIT = 1 -- When 0, the sproc is non-destructive. When 1, the sproc deletes the original collections, consolidated members, EHs, etc that are converted.

    )

    AS BEGIN ...

    ----

    For More information refer to:

    Derived Hierarchies (Master Data Services)

    Recursive Hierarchies (Master Data Services)

  • SQL 2012 SP1: How to cleanup transaction logs, Validation issues and Staging tables

    Master Data Services till recently didn’t have a supported way to clean the transaction logs, validation issues history and Staging tables. For a MDS system with lot of data changes and ETL processes over the period these tables can grow exponentially and lead to performance degradation and storage space issues.

    To overcome this problem in “Cumulative update 15 for SQL Server 2012 SP1we are providing some helper Stored Procedures which users can call to clean the tables.

    What is cleaned?

    All the transaction history older than specified date, all the validation issues history older than specified date and all the staging batches which ran before specified date.

    How:

    You can call the Stored Procedures manually which will clean up the tables. To clean Transaction tables use mdm.udpTransactionsCleanup, to clean Validation History use mdm.udpValidationsCleanup and to clean staging table use mdm.udpEntityStagingBatchTableCleanup.

    Sample:

    --@ModelID is the model ID that you clean up the log for.

    --@CLeanupOlderThanDate is the date before that the logs or records are deleted.

    DECLARE @CleanupOlderThanDate date = '2015-01-01',

       @ModelID INT = 7     --You can get this fromtable mdm.tblModel

    --Cleanup Transaction Logs

    EXEC mdm.udpTransactionsCleanup @ModelID, @CleanupOlderThanDate;

     

    --Cleanup Validation History

    EXEC mdm.udpValidationsCleanup @ModelID, @CleanupOlderThanDate;

     

    --Claenup EBS tables

    EXEC mdm.udpEntityStagingBatchTableCleanup @ModelID, @CleanupOlderThanDate;

    ----------------------

     

    You can also run the cleanup on predetermined schedule by setting up a SQL Agent Job which triggers in regular intervals to call the stored procedures to clean up the data.  You can use SQL Server Management Studio to make any changes to this scheduled job.

    Upcoming Releases:

    These stored procedures will also be release as part of the upcoming cumulative update for SQL Server 2014.

More Posts Next page »
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement