Duplicate Key Error on dimension column that expects duplicates

Question

"Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'bi_assets_dim_site', Column: 'postcode', Value: 'LE3 9LD'.

I get this duplicate key error on the processing of my SSAS cube. The column itself is not a key. There can be multiple postcodes assigned to a site.

When I process the cube, dimensions first and then then measures (the cube itself). It processes successfully. However, once it runs via the SQL job activity monitor/scheduler. It fails on this error again.

the command called

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Parallel>
    <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200">
     <Object>
        <DatabaseID>Site Cube</DatabaseID>
      </Object>
      <Type>ProcessFull</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel>
</Batch>

FrankPl FrankPl · Accepted Answer · 2021-07-14T19:17:34

The error message sounds as if you have an attribute relationship defined from some attribute to the postcode attribute in your site dimension. Defining an attribute relationship tells Analysis Services that there is a many-to-one relationship from one attribute to the other. If - as you write, there can be more than one postcode per site, then you cannot have an attribute relationship from site to postcode. Removing this from the dimension should get rid of the processing error.

Duplicate Key Error on dimension column that expects duplicates

1 Answers