CODEWITHSUNDEEP
ssasolapcubedimensionxmla
Merin Nakarmi
Merin Nakarmi

Reputation: 3418

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.

Upvotes: 0

Views: 5516

Answers (2)

Anuj Tripathi
Anuj Tripathi

Reputation: 2281

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.

Upvotes: 2

Merin Nakarmi
Merin Nakarmi

Reputation: 3418

Two easy approaches here.

A. From Visual Studio

enter image description here

B. From SQL Server Management Studio enter image description here

The generated XMLA Script does not show ErrorConfiguration element in the XMLA Script, however it does automatically take care of Ignore Errors that you configured. You can use these XMLA Script any where in SQL Server Agent or Services to process cubes/dimensions automatically.

Upvotes: 1

Related Questions