Process dimension and cube via XMLA script ignoring Dimension Key errors

Question

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.

Anuj Tripathi · Accepted Answer · 2015-09-25T08:23:24.757

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

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

Once you are done then click OK and click on script to generate relevant XMLA script.

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.

Hi Anuj, I appreciate your valuable input. I know how to get the XMLA script for a dimension and cube and I also know how to process multiple dimensions and cube in one single script. Could you please write the not straight forward steps here that would include the ignore error options. Thanks. — Merin Nakarmi, Sep 24 '15 at 15:16
I followed all the steps you have listed. However the deployed dimension and cube did not generate ErrorConfiguration element on the XMLA Script. According to this article, it looks like we can explicitly add ErrorConfiguration element on the XMLA. https://msdn.microsoft.com/en-us/library/ms186610(v=sql.110).aspx. I will try this one. — Merin Nakarmi, Sep 24 '15 at 21:07
@MerinNakarmi If in left the default value in errorconfiguration tab then it won't be appeared in the XMLA script. However, try to change every value to "reportAndStop" and ErrorLimitAction to "StopLogging" then you will see ErrorConfiguration parent node will all the child nodes. Try this step and then change the value to your desired one. I am updating my answer with "reportAndError" XMLA script which I got after doing above steps. — Anuj Tripathi, Sep 25 '15 at 08:21
I have seen the Change Settings only from the Visual Studio side. Today I saw it on SSMS side also. Changing Settings on SSMS side and generating script does add ErrorConfiguration tag on the XMLA script. Thanks a lot Anuj. I accepted as answer and upvoted. Cheers. — Merin Nakarmi, Sep 25 '15 at 15:52
@MerinNakarmi I am glad it solved your issue. Happy coding :) — Anuj Tripathi, Sep 25 '15 at 17:16

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

0

Two easy approaches here.

A. From Visual Studio

B. From SQL Server Management Studio

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.

edited Jun 20 '20 at 09:12

Community

1
1

answered Sep 24 '15 at 21:43

Merin Nakarmi

3,148
3
35
42

Process dimension and cube via XMLA script ignoring Dimension Key errors

2 Answers2