Search code examples
ssasolapcubedimensionxmla

Process dimension and cube via XMLA script ignoring Dimension Key errors

In SSAS, there is an option of ignoring Dimension key errors when we manually process dimension from Visual Studio. But I did not see equivalent of it in XMLA script despite lots of binging and googling. If it is possible, kindly help.

Solution

  • XMLA script just mention about the dimenion/fact/database you want to process with options. Rest all the settings of cube (ex: ignore duplicate keys) is inherited from the cube itself. So, if you have set those properties in SSAS cube then it will be taken care. However, you can process each dimension separately to avoid other key related issues via XMLA but it isn't straight forward, you have to get the XMLA script of each dimension Ex:

        <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" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
      <Object>
        <DatabaseID>Database_Name</DatabaseID>
        <DimensionID>Dimension_Name</DimensionID>
      </Object>
      <Type>ProcessFull</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel>
</Batch>

    Basically, you can avoid dimension keys error from SSAS cube itself. For example, you will get duplicate error when you have both NULL and blank in the table.

    Updates

    You can change dimension setting by going to Database > Process > Change setting enter image description here

    Then click on dimension key error tab and set your desired values

    enter image description here

    Once you are done then click OK and click on script to generate relevant XMLA script. enter image description here

    You will notice that now your XMLA will have ErrorConfiguration node with the values you have selected.

    XMLA - ReportAndStop

        <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <ErrorConfiguration 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" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
    <KeyErrorLimit>2</KeyErrorLimit>
    <KeyErrorLimitAction>StopLogging</KeyErrorLimitAction>
    <KeyNotFound>ReportAndStop</KeyNotFound>
    <KeyDuplicate>ReportAndStop</KeyDuplicate>
    <NullKeyConvertedToUnknown>ReportAndStop</NullKeyConvertedToUnknown>
    <NullKeyNotAllowed>ReportAndStop</NullKeyNotAllowed>
  </ErrorConfiguration>
  <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" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
      <Object>
        <DatabaseID>Database_Name</DatabaseID>
      </Object>
      <Type>ProcessFull</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel>
</Batch>

    You can also generate the same by changing all defaults to other value and once you got the XMLA then give it a desired value.