Hi, I am using Master Data Service for couple of months now. I can load, update, merge and soft delete data in MDS. Occasionally we even have to hard delete data from MDS. If we keep on soft deleting records in a MDS table eventually there will be huge number of soft deleted records. You can hard delete members through Entity Based.
I have come across a number of tips while using MDS. These are tips not big enough for their own blog post, so I list them all here:
  • To “hide” an attribute in the MDS Excel add-in, set the pixel width to “0″
  • If you make a change outside of the Master Data Manager Web User Interface “UI” (i.e. Master Data Services Configuration Manager, MDSModelDeploy tool, etc), you should go to the home page of the UI and click “Refresh cached information” on the top right of the screen
  • The Master Data add-in for Excel allows you to modify an attributes type and length, but the MDM web UI does not allow this (those boxes are grayed out). So just fire up your Excel if you find you need to change an attributes type and length
  • There is not a way in the MDM web UI to delete a version. You must manually delete it: Delete a Version (Master Data Services)
  • To get data into and out of the system and communicate with other applications, Master Data Services uses staging tables for the incoming data flow and subscription views for the outgoing data flow
  • In MDS, to track history of data changes (called Change Tracking), in the MDS web UI go to Version Management -> Transactions (see Transactions). It’s not at the level of your typical Type 2 slowly changing dimension, but you can reverse transactions and add comments
  • In a subscription view, each domain-based entity shows as three fields: 1) EntityName_Code – This equals the “Code” field in the referenced domain, 2) EntityName_Name – This equals the “Name” field in the referenced domain, 3) EntityName_ID – Internal system key for the member
  • In the MDS web UI, in Explorer, a delete sets a member to de-activated (a soft delete). You can reverse this via the Transaction screen (on my project, instead of doing soft deletes, we created a field called MasterDataStatus that contains “Active” and “Deprecated”). You can hard delete members through the Entity Based Staging feature in MDS via SSIS…you just need to set the ImportType to 4 or 6 when you prepare the staging table. This is probably going to be enhanced in the first service pack of SQL 2012 by adding a new stored procedure that will hard-delete old soft-deleted members from a given model so you don’t have to use Entity Based Staging
  • There is no easy way via the MDS web UI to do mass soft deletes to members in a entity – they can only be done one-by-one. So, you can use the Entity Based Staging feature in MDS via SSIS to do a mass soft delete (by setting the ImportType to 3), or even easier use the Master Data Services add-in to Excel to quickly do a mass soft delete. A soft delete de-activates a member
  • In the MDS web UI, “Integration Management -> Import Data” shows all the batches In mdm.tblStgBatch with a status_id: 1 (QueuedToRun), 3 (Running), 4 (QueuedToClear), 5 (Cleared), 7 (Completed). You will see batches with a status of “NotRunning” if there are records in any of the staging tables with a batch_ID of NULL (mdm.tblStgBatch is not used for those). So to remove those batches from this screen, delete those records from the staging table, such as: “delete from stg.FMD_TablArea_Leaf where batch_ID is null and BatchTag = ‘Area’;”
  • MDS Staging was vastly improved in the SQL Server 2012 MDS release and is called Entity Based Staging. Watch this video intro and refer to the product documentation on the new 2012 MDS staging concepts. Also become familiar with Leaf Member Staging Table
  • You should routinely clear the completed batches via the MDS web UI: Integration Management -> Clear Batches. Otherwise, these tables will grow large and batching the stage data will be slow
  • To view staging errors, run SQL such as (see Staging Process Errors for error details): SELECT * FROM [MDS2012].[stg].[FMD_TblFacility_Leaf] stg LEFT OUTER JOIN [MDS2012].[mdm].[tblErrorCodesMapping] ecm on stg.ErrorCode = ecm.Bitmask LEFT OUTER JOIN mdm.tblDBErrors dbe ON dbe.ID = ecm.UniqueErrorCode LEFT OUTER JOIN mdm.tblStgErrorDetail sed ON sed.Batch_ID = stg.Batch_ID AND sed.Code = stg.Code AND ecm.UniqueErrorCode = sed.UniqueErrorCode where ImportStatus_ID = 2 and stg.Batch_ID = 1197
  • The “Create Code values automatically” checkbox that is available when creating an entity automatically generates values for an entity’s Code attribute when you want an integer to be automatically assigned to the Code value each time a new member is created. In the staging process set the Code field to NULL (or in the Excel add-in leave it blank) to have it automatically generate the next integer value. This is similar to the IDENTITY column in SQL Server. Note that you are not prevented from entering other values for codes; rather an initial value is automatically set.. See Automatic Code Creation. This feature is new to SQL Server 2012. In SQL Server 2008 you needed to use a business rule to automatically generate values for the Code field: See Auto-Generating a Master Data Member Code
  • MDS has a trace logging feature that is useful for error diagnostics and support requests as well as monitoring of application usage and performance. More info is at How to enable Trace Logging in SQL Server 2008 R2 Master Data Services (NOTE: make sure the directory where the log file will be has write access on it for everyone)

If the data you want to filter requires complex criteria (such as Type = 'Produce' OR Salesperson = 'Davolio'), you can use the Advanced Filter dialog box.

To open the Advanced Filter dialog box, click Data > Advanced.

Example
Salesperson = 'Davolio' OR Salesperson = 'Buchanan'
Type = 'Produce' AND Sales > 1000
Type = 'Produce' OR Salesperson = 'Buchanan'
(Sales > 6000 AND Sales < 6500 ) OR (Sales < 500)
(Salesperson = 'Davolio' AND Sales >3000) OR
(Salesperson = 'Buchanan' AND Sales > 1500)
Salesperson = a name with 'u' as the second letter

Overview

The Advanced command works differently from the Filter command in several important ways.

  • It displays the Advanced Filter dialog box instead of the AutoFilter menu.
  • You type the advanced criteria in a separate criteria range on the worksheet and above the range of cells or table that you want to filter. Microsoft Office Excel uses the separate criteria range in the Advanced Filter dialog box as the source for the advanced criteria.

Sample data

The following sample data is used for all procedures in this article.

The data includes four blank rows above the list range that will be used as a criteria range (A1:C4) and a list range (A6:C10). The criteria range has column labels and includes at least one blank row between the criteria values and the list range.

To work with this data, select it in the following table, copy it, and then paste it in cell A1 of a new Excel worksheet.

Type
Salesperson
Sales
Type
Salesperson
Sales
Beverages
Suyama
$5122
Meat
Davolio
$450
produce
Buchanan
$6328
Produce
Davolio
$6544

Comparison operators

You can compare two values by using the following operators. When two values are compared by using these operators, the result is a logical value—either TRUE or FALSE.

Comparison operator
Meaning
Example
= (equal sign)
Equal to
A1=B1
> (greater than sign)
Greater than
A1>B1
< (less than sign)
Less than
A1<B1
>= (greater than or equal to sign)
Greater than or equal to
A1>=B1
<= (less than or equal to sign)
Less than or equal to
A1<=B1
<> (not equal to sign)
Not equal to
A1<>B1

Using the equal sign to type text or a value

Mds excel

Because the equal sign (=) is used to indicate a formula when you type text or a value in a cell, Excel evaluates what you type; however, this may cause unexpected filter results. To indicate an equality comparison operator for either text or a value, type the criteria as a string expression in the appropriate cell in the criteria range:

='=entry'

Where entry is the text or value you want to find. For example:

What you type in the cell
What Excel evaluates and displays
='=Davolio'
=Davolio
='=3000'
=3000

Considering case-sensitivity

When filtering text data, Excel doesn't distinguish between uppercase and lowercase characters. However, you can use a formula to perform a case-sensitive search. For an example, see the section Wildcard criteria.

Using pre-defined names

You can name a range Criteria, and the reference for the range will appear automatically in the Criteria range box. You can also define the name Database for the list range to be filtered and define the name Extract for the area where you want to paste the rows, and these ranges will appear automatically in the List range and Copy to boxes, respectively.

Creating criteria by using a formula

You can use a calculated value that is the result of a formula as your criterion. Remember the following important points:

Multiple criteria, one column, any criteria true

Boolean logic: (Salesperson = 'Davolio' OR Salesperson = 'Buchanan')

  1. Insert at least three blank rows above the list range that can be used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the list range.
  2. To find rows that meet multiple criteria for one column, type the criteria directly below each other in separate rows of the criteria range. Using the example, enter:
    Type
    Salesperson
    Sales
    ='=Davolio'
    ='=Buchanan'
  3. Click a cell in the list range. Using the example, click any cell in the range A6:C10.
  4. On the Data tab, in the Sort & Filter group, click Advanced.
  5. Do one of the following:
  6. In the Criteria range box, enter the reference for the criteria range, including the criteria labels. Using the example, enter $A$1:$C$3.
    To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog .
  7. Using the example, the filtered result for the list range is:
    Type
    Salesperson
    Sales
    Meat
    Davolio
    $450
    produce
    Buchanan
    $6,328
    Produce
    Davolio
    $6,544

Multiple criteria, multiple columns, all criteria true

Boolean logic: (Type = 'Produce' AND Sales > 1000)

  1. Insert at least three blank rows above the list range that can be used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the list range.
  2. To find rows that meet multiple criteria in multiple columns, type all the criteria in the same row of the criteria range. Using the example, enter:
    Type
    Salesperson
    Sales
    ='=Produce'
    >1000
  3. Click a cell in the list range. Using the example, click any cell in the range A6:C10.
  4. On the Data tab, in the Sort & Filter group, click Advanced.
  5. Do one of the following:
  6. In the Criteria range box, enter the reference for the criteria range, including the criteria labels. Using the example, enter $A$1:$C$2.
    To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog .
  7. Using the example, the filtered result for the list range is:
    Type
    Salesperson
    Sales
    produce
    Buchanan
    $6,328
    Produce
    Davolio
    $6,544

Multiple criteria, multiple columns, any criteria true

Boolean logic: (Type = 'Produce' OR Salesperson = 'Buchanan')

  1. Insert at least three blank rows above the list range that can be used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the list range.
  2. To find rows that meet multiple criteria in multiple columns where any criteria can be true, type the criteria in the different columns and rows of the criteria range. Using the example, enter:
    Type
    Salesperson
    Sales
    ='=Produce'
    ='=Buchanan'
  3. Click a cell in the list range. Using the example, click any cell in the list range A6:C10.
  4. On the Data tab, in the Sort & Filter group, click Advanced.
  5. Do one of the following:
    Tip: When you copy filtered rows to another location, you can specify which columns to include in the copy operation. Before filtering, copy the column labels for the columns that you want to the first row of the area where you plan to paste the filtered rows. When you filter, enter a reference to the copied column labels in the Copy to box. The copied rows will then include only the columns for which you copied the labels.
  6. In the Criteria range box, enter the reference for the criteria range, including the criteria labels. Using the example, enter $A$1:$B$3.
    To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog .
  7. Using the example, the filtered result for the list range is:
    Type
    Salesperson
    Sales
    produce
    Buchanan
    $6,328
    Produce
    Davolio
    $6,544

Multiple sets of criteria, one column in all sets

Boolean logic: ( (Sales > 6000 AND Sales < 6500 ) OR (Sales < 500) )

  1. Insert at least three blank rows above the list range that can be used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the list range.
  2. To find rows that meet multiple sets of criteria where each set includes criteria for one column, include multiple columns with the same column heading. Using the example, enter:
    Type
    Salesperson
    Sales
    Sales
    >6000
    <6500
    <500
  3. Click a cell in the list range. Using the example, click any cell in the list range A6:C10.
  4. On the Data tab, in the Sort & Filter group, click Advanced.
  5. Do one of the following:
  6. In the Criteria range box, enter the reference for the criteria range, including the criteria labels. Using the example, enter $A$1:$D$3.
    To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog .
  7. Using the example, the filtered result for the list range is:
    Type
    Salesperson
    Sales
    Meat
    Davolio
    $450
    produce
    Buchanan
    $6,328
Excel Mds Table

Multiple sets of criteria, multiple columns in each set

Boolean logic: ( (Salesperson = 'Davolio' AND Sales >3000) OR (Salesperson = 'Buchanan' AND Sales > 1500) )

  1. Insert at least three blank rows above the list range that can be used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the list range.
  2. To find rows that meet multiple sets of criteria, where each set includes criteria for multiple columns, type each set of criteria in separate columns and rows. Using the example, enter:
    Type
    Salesperson
    Sales
    ='=Davolio'
    >3000
    ='=Buchanan'
    >1500
  3. Click a cell in the list range. Using the example, click any cell in the list range A6:C10.
  4. On the Data tab, in the Sort & Filter group, click Advanced.
  5. Do one of the following:
  6. In the Criteria range box, enter the reference for the criteria range, including the criteria labels. Using the example, enter $A$1:$C$3.To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog .
  7. Using the example, the filtered result for the list range would be:
    Type
    Salesperson
    Sales
    produce
    Buchanan
    $6,328
    Produce
    Davolio
    $6,544

Wildcard criteria

Boolean logic: Salesperson = a name with 'u' as the second letter

  1. To find text values that share some characters but not others, do one or more of the following:
  2. Insert at least three blank rows above the list range that can be used as a criteria range. The criteria range must have column labels. Make sure that there is at least one blank row between the criteria values and the list range.
  3. In the rows below the column labels, type the criteria that you want to match. Using the example, enter:
    Type
    Salesperson
    Sales
    ='=Me*'
    ='=?u*'
  4. Click a cell in the list range. Using the example, click any cell in the list range A6:C10.
  5. On the Data tab, in the Sort & Filter group, click Advanced.
  6. Do one of the following:
  7. In the Criteria range box, enter the reference for the criteria range, including the criteria labels. Using the example, enter $A$1:$B$3.
    To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog .
  8. Using the example, the filtered result for the list range is:
    Type
    Salesperson
    Sales
    Beverages
    Suyama
    $5,122
    Meat
    Davolio
    $450
    produce
    Buchanan
    $6,328

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.