Power BI Blog: De-duplication Rules for Composite Models

Welcome back to
this week’s edition of the Power BI blog series.  This week, we review whether Microsoft should
make up words such as “de-duplication”. 
Or maybe that isn’t what this article is about – perhaps it’s about
preventing duplication for composite models on Power BI Datasets and Analysis
Services.

Tables and measures
in your model must have unique names.  If
you use composite models on Power BI Datasets and Analysis Services, it’s easy
to get into a situation where tables and measure names have duplicate names and
are not unique.  Up to this point, when
that happened one of the tables or measures would be renamed for you.  

For example, if you
created a composite model from two sources and both sources defined a table
called ‘Customers’, one of the tables would be renamed ‘Customer 2’.  This resulted in confusing situations as it
was not clear which source the ‘Customer 2’ table came from.  The same applies to measures: if you had two
sources that both contained a measure called ‘Total Sales’ one would be renamed
‘Total Sales 2’ in the composite model.

Now, Microsoft is
handing (some) control back.  You can now
apply a name disambiguation rule to a source in a composite model when you
anticipate name conflicts with tables or measures from another source.  You can set up a text to be added as a prefix
or suffix to table names, measure names or both.  Additionally, you can choose to add that text
only when a duplication occurs or if you prefer to have it added all the time.  

Going back to the
example above, let’s say that one of the sources you are combining is about
marketing and the other is about sales.  You
can now set up a de-duplication rule on the source connection so the ‘Customer’
table from the marketing source is named ‘Customer (marketing)’:

You will find these
options under Settings in the dialog that shows when you set up the composite model
connection to a Power BI dataset or Analysis Services model:

After you make the
connections and set up the de-duplication rule, your Field list will show both
‘Customer’ and ‘Customer (marketing)’ according to the de-duplication rule you
set up:

It should be noted
that you can now:

• specify
  whether you want the text to be added to the table or measure name as a prefix
  or a suffix
• apply the
  de-duplication rule to tables, measures or both
• choose to
  apply the de-duplication rule only when a name conflict occurs or apply it all
  the time.  The default is to apply the
  rule only when duplication occurs.  In the
  example above, any table or measure from the marketing source that does not
  have a duplicate in the sales source will not receive a name change.

Finally, it would
be remiss is if we didn’t mention that if you do not specify a de-duplication
rule or the de-duplication rules you specified do not resolve the name conflict,
the standard deduplication actions will still be applied.

In the meantime, please remember we offer training in Power BI which you can find out more about here.  If you wish to catch up on past articles, you can find all of our past Power BI blogs here.