Splitting dimension into dimension columns in SQL.

Getting dimension into splitted values

For ledger dimension
Select Dimension,MainAccount,Claims,Functions,ItemGroup,CostCenter,Market_IC,MTO,Projects,Vendor,Agreement from
 (
 select DIMENSIONATTRIBUTEVALUECOMBINATION.recid as dimension,DimensionAttribute.name,DimensionAttributeLevelValueAllView.DisplayValue from DIMENSIONATTRIBUTEVALUECOMBINATION
 join DimensionAttributeLevelValueAllView on DimensionAttributeLevelValueAllView.VALUECOMBINATIONRECID = DIMENSIONATTRIBUTEVALUECOMBINATION.RECID
 join DimensionAttribute on DIMENSIONATTRIBUTE.RecId = DimensionAttributeLevelValueAllView.DIMENSIONATTRIBUTE
 where DIMENSIONATTRIBUTEVALUECOMBINATION.MAINACCOUNT!=0
 ) d
PIVOT
(
 MAX(DisplayValue)
 FOR Name
 IN (MainAccount,Claims,Functions,ItemGroup,CostCenter,Market_IC,MTO,Projects,Vendor,Agreement)
)
AS PivotTable

select DefaultDimension,Claims,Functions,ItemGroup,CostCenter,Market_IC,MTO,Projects,Vendor,Agreement from

(

select DIMENSIONATTRIBUTEVALUECOMBINATION.recid as DefaultDimension,DimensionAttribute.name,DimensionAttributeValueSetItem.DisplayValue from DIMENSIONATTRIBUTEVALUECOMBINATION

join DimensionAttributeValueSet on DimensionAttributeValueSet.RecId = DIMENSIONATTRIBUTEVALUECOMBINATION.RECID

join DimensionAttributeValueSetItem on DimensionAttributeValueSetItem.DIMENSIONATTRIBUTEVALUESET = DimensionAttributeValueSet.RecID

join DimensionAttributeValue on DimensionAttributeValue.RECID = DimensionAttributeValueSetItem.DIMENSIONATTRIBUTEVALUE

join DimensionAttribute on DIMENSIONATTRIBUTE.RecId = DimensionAttributeValue.DIMENSIONATTRIBUTE

where DIMENSIONATTRIBUTEVALUECOMBINATION.MAINACCOUNT=0

) d

PIVOT

(

MAX(DisplayValue)

FOR Name

IN (Claims,Functions,ItemGroup,CostCenter,Market_IC,MTO,Projects,Vendor,Agreement)

)

AS PivotTable

Useful X++ functions

//Return the value from the xmlNode
static str getXmlNodeValue(XmlNode _node)
{
    System.Text.RegularExpressions.Regex re =
    // BP deviation documented
    new System.Text.RegularExpressions.Regex('^[ \r\n\t]*((.|\n)*?)[ \r\n\t]*$');
    System.Text.RegularExpressions.Match m = re.Match(_node.text());
    str result;
    ;

    result = m.Result('$1');
    return result;
}

/// <summary>
/// Provides a lookup of dimension values.
/// </summary>
/// <param name="_ctrl">The control to provide a lookup for.</param>
/// <param name="_dimensionName">The dimension attribute to filter by.</param>
public static client void lookupDimension(FormStringControl _ctrl, Name _dimensionName)
{
    SysTableLookup          sysTableLookup = SysTableLookup::newParameters(tablenum(DimensionAttribute),_ctrl);
    Query                   query = new Query();
    QueryBuildDataSource    queryBuildDataSource = query.addDataSource(tablenum(DimensionAttribute));

    sysTableLookup.addLookupfield(fieldnum(DimensionAttribute, Name));

    queryBuildDataSource.addRange(fieldnum(DimensionAttribute, Type)).value(queryNotValue(DimensionAttributeType::MainAccount));

    sysTableLookup.parmQuery(query);
    sysTableLookup.performFormLookup();

}


/// <summary>
/// This method has the complete source file as input and will concatenate the target path with the filename
/// </summary>
/// <param name="_sourceFilename">
///  This is the full filename including the path
/// </param>
/// <param name="_targetPath">
///  the target folder
/// </param>
/// <returns>
///  It will return the target filename.
/// </returns>

static filename getTargetFilename(Filename _sourceFilename, Filepath _targetPath)
{
    Filename    targetFilename;
    ;
    new InteropPermission(InteropKind::ClrInterop).assert();

    targetFilename = System.IO.Path::Combine(_targetPath, System.IO.Path::GetFileName(_sourceFilename));

    return targetFilename;
}


// Generate nodes into container.
static container containerFromXMLNode(XmlNode n)
{
    container retval, containerToInsert;
    Common recordToInsert;
    XmlNamedNodeMap attrs;
    XmlNode typeAttr, element, recordNode, containerNode;
    int elemNo = 1;
    ;
    //validate arguments before using them
    if (n == null)
        return retval;

    // Collect all the elements ...
    element = n.firstChild();
    if (element.name() == 'container')
    {
        element = element.firstChild();
    }
    while (element)
    {
        attrs = element.attributes();
        if (attrs)
        {
            typeAttr = attrs.getNamedItem('type');

            switch (typeAttr.nodeValue())
            {
                case 'int'      :
                    retval = conins(retval, elemNo, str2int(BisTools::getXmlNodeValue(element)));
                    break;

                case 'str'      :
                    retval = conins(retval, elemNo, BisTools::getXmlNodeValue(element));
                    break;

                case 'real'     :
                    retval = conins(retval, elemNo, str2num(BisTools::getXmlNodeValue(element)));
                    break;

                case 'enum':
                    retval = conins(retval, elemNo, str2int(BisTools::getXmlNodeValue(element)));
                    break;

                case 'date'     :
                    retval = conins(retval, elemNo, str2date(BisTools::getXmlNodeValue(element), 321));
                    break;

                case 'record'   :
                    recordNode = element.firstChild();
                    recordToInsert = Global::recordFromXMLNode(recordNode);
                    retval = conins(retval, elemNo, recordToInsert);
                    break;

                case 'container':
                    containerNode = element.firstChild();
                    containerToInsert = BisTools::containerFromXMLNode(containerNode);
                    retval = conins(retval, elemNo, containerToInsert);
                    break;

                case 'blob':
                    containerToInsert = BinData::stringToData(BisTools::getXmlNodeValue(element));
                    retval = conins(retval, elemNo, containerToInsert);
                    break;
            }
        }

        element = element.nextSibling();
        elemNo += 1;
    }

    return retval;
}




//Generate html from Record
static str record2html(Common record)
{
    str html;
    DictTable   dictTable = new dictTable(record.TableId);
    DictField   dictField;
    fieldId     fieldId;


    void addField2html(str fieldName, anyType fieldValue, str htmlCode, str fieldLabel)
    {
        str newValue = strFmt('%1',fieldValue);
        if (newValue == '')
        {
            newValue = '-';
        }
        html += strFmt('<tr><td width=30%>%3%4</td><td width=50%>%3%2</td><td width=20%>%3%1</td></tr>',fieldName, newValue , htmlCode, fieldLabel);
    }
    ;
    html = '<html><b>Record values<table border width=100%>';
    addField2html('Field name','Value','<b>','Label');

    fieldId = dictTable.fieldNext(0);
    while (fieldId)
    {
        dictField = dictTable.fieldObject(fieldId);
        if (dictField.arraySize() == 1 && dictField.baseType() != Types::Container && dictField.baseType() != Types::BLOB)
        {
            addField2html(dictField.name(), record.(fieldId),"", dictField.label());
        }
        fieldId = dictTable.fieldNext(fieldId);
    }


    html+='</table></html>';
    return html;
}




//Generate record to Text
static str record2text(Common record)
{
    str         text;
    DictTable   dictTable = new dictTable(record.TableId);
    DictField   dictField;
    fieldId     fieldId;


    void addField2text(str fieldName, anyType fieldValue, str fieldLabel)
    {
        str newValue = strFmt('%1',fieldValue);
        if (newValue == '')
        {
            newValue = '-';
        }
        text += strFmt('Field \'%1\' = \'%2\', Label: \'%3\'\n',fieldName, newValue, fieldLabel);
    }
    ;
    text = strFmt("@BIS635", tableid2name(record.tableId));
    addField2text('Field name','Value', 'Label');

    fieldId = dictTable.fieldNext(0);
    while (fieldId)
    {
        dictField = dictTable.fieldObject(fieldId);
        if (dictField.arraySize() == 1)
        {
            addField2text(dictField.name(), record.(fieldId), dictField.label());
        }
        fieldId = dictTable.fieldNext(fieldId);
    }


    return text;
}


//Create the table record from xmlnode
// Input is <Record table="name"> <Field:field1> value </Field:field1> ... </Record>
// it's the opposite from record.xml() method
static Common xml2record(XmlNode n, Common c = null)
{
    tableId table;
    fieldId field;
    DictTable dt;
    DictField df;
    XmlNode fieldNode;
    str fieldName;
    Struct content;
    str tableName;
    Types t;

    //validate arguments before using them
    if (n == null)
        return null;
    // If we don't have an incoming buffer, one is created
    if (prmisdefault(c))
    {
        // Create a buffer of the correct type
        tableName = n.name();
       // tableName = n.attributes().getNamedItem('name');

        // Check if table node can be accessed
        if (tableName != '')
        {
            table = tablename2id(tableName);
            dt = new DictTable(table);
            c = dt.makeRecord();
        }
    }
    else
    {
        table = c.TableId;
        dt = new DictTable(table);
    }

    fieldNode = n.firstChild();
    while (fieldNode)
    {
        fieldName = fieldNode.attributes().getNamedItem('name').text();
        field = dt.fieldName2Id(fieldName);
        df = new DictField(table, field);

        t = df.baseType();
        if (t == Types::Enum) //Enums need to be treated as Integers. By defult, Enums are getting treated as a boolean and hence loosing its value.
            t = Types::Integer;

        content = BisTools::valueFromXMLNode (t, fieldNode);

        c.(field) = content.value('value');

        fieldNode = fieldNode.nextSibling();
    }
    return c;
}

//Get next working date from the date .
public static date GetNextWorkDay(date _fromDate)
{
    WorkCalendarDate    workCalendar;
    ;

    select firstOnly TransDate from workCalendar
    order by TransDate ASC
        where workCalendar.TransDate > _fromDate
           && workCalendar.WorkTimeControl == WorkTimeControl::Open;

    return workCalendar.TransDate;
}


/// <summary>
///     Attempts to find executatble for file type specified, and open file with it.
/// </summary>
/// <param name="_filename">
///     File name to show
/// </param>
public static client void showFile(Filename _filename)
{
    str filePath, fileName, fileExt, executable;
    if (System.IO.File::Exists(_filename))
    {
        [filePath, filename, fileExt] = fileNameSplit(_filename);
        executable = WinAPI::findExecutable(_filename);
        if (!executable)
        {
            error(strFmt("Windows were not able to find executable for file %1", _filename));
            return;
        }
        if (fileExt == '.msg')
            WinAPI::shellExecute(strFmt('"%1"', executable),strFmt('/f "%1"', _filename),strFmt('"%1"', filePath)); // outlook requires extra parameter  /f
        else
            WinAPI::shellExecute(strFmt('"%1"', executable),strFmt('"%1"', _filename),strFmt('"%1"', filePath));
    }
    else
    {
        error("File not found");
    }
}





/// <summary>
///     Converts enum value to enum label in language specified
/// </summary>
/// <param name="enumId">
///     Enum Id
/// </param>
/// <param name="value">
///     Enum value
/// </param>
/// <param name="languageId">
///     Language for label
/// </param>
/// <returns>
///     Label of enum value in language specified
/// </returns>
public static str enum2Label(EnumId enumId, int value, LanguageId languageId = SrsReportRunUtil::getCurrentUserLangId())
{
    DictEnum dictEnum;
    int      valueIndex, valueBuf;
    ;
    dictEnum = new DictEnum(enumId);
    if (dictEnum)
    {

        for (valueIndex = 0 ; valueIndex < dictEnum.values(); valueIndex++)
        {
            valueBuf = dictEnum.index2Value(valueIndex);
            if (valueBuf == value)
                return SysLabel::labelId2String(dictEnum.index2LabelId(valueIndex), languageId);
        }
    }
    return '';
}


/// <summary>
///     Converts time value into string with hours and minutes. Mostly used when generating file names
/// </summary>
/// <param name="time">
///     Time as integer
/// </param>
/// <returns>
///     Time converted to string in format HHMM
/// </returns>
public static TempStr time2StrHM(int time)
{
    return num2str( time div 3600       ,2,0,0,0) + num2Str0(time mod 3600 div 60,2,0,0,0);
}

/// <summary>
///     Converts any value to string. This is hardcoded conversion without using culture context
/// </summary>
/// <param name="_value">
///     The value to convert
/// </param>
/// <returns>
///     Converted value as string
/// </returns>
public static str any2String(anytype _value)
{
    switch(typeOf(_value))
    {
        // BP deviation documented
        case Types::Date: return date2str(_value, -1,-1,-1,-1,-1,-1,DateFlags::None);
        case Types::Time: return KrishhUtils::time2StrHM(_value);
        case Types::Int64, Types::Integer: return int2str(_value);
        case Types::Real: return strFmt('%1', _value);
        case Types::Enum: return strFmt('%1', _value);
        case Types::UtcDateTime: return DateTimeUtil::toStr(DateTimeUtil::applyTimeZoneOffset(_value, DateTimeUtil::getUserPreferredTimeZone())); // Hotfix 52
        default: return strFmt('%1', _value);
    }
    return "";
}


/// <summary>
///     Checks full path, and if \ is missing at the end - adds it
/// </summary>
/// <param name="path">
///     Full path to analyse
/// </param>
/// <returns>
///     Full path with \ at the end
/// </returns>
public static str fixPath(str path)
{
    str buf = strLRTrim(path);
    if (!path)
        return "";
    if (subStr(buf, strLen(buf),1) != '\\')
        buf = buf + '\\';
    return buf;
}

/// <summary>
///     Generates temporrary file with extension. Also creates file and saves it.
/// </summary>
/// <param name="filePrefix">
///     File prefix to use
/// </param>
/// <param name="extension">
///     Extension to use
/// </param>
/// <param name="filePath">
///     File path to use
/// </param>
/// <returns>
///     Created temporrary file name
/// </returns>
/// <remarks>
///     Creates empty file with unique name. Safe to use with multiple threads
/// </remarks>
/// <exception cref="Exception::Error">
///     When 10 attempts to create file failed
/// </exception>
/// <exception cref="Exception::Error">
///     When 10 attempts to create file failed
/// </exception>
public static str getTempFile(str filePrefix, str extension, str filePath = '')
{
    #File
    str                  tmpPath;
    str                  fileName;
    System.IO.FileStream fileStream;
    boolean              ok;
    int                  i;

    if (filePath)
        tmpPath = KrishhUtils::fixPath(filePath);
    else
    {
        new InteropPermission(InteropKind::ClrInterop).assert();
        tmpPath = System.IO.Path::GetTempPath();
        CodeAccessPermission::revertAssert();
    }
    while(!ok)
    {
        fileName = tmpPath + (filePrefix?filePrefix:filePrefix + guid2str(newGuid())) + extension; // First attempt direct
        new InteropPermission(InteropKind::ClrInterop).assert();
        while(System.IO.File::Exists(fileName))
        {
            fileName = tmpPath + filePrefix + guid2str(newGuid()) + extension;
        }
        CodeAccessPermission::revertAssert();
        try
        {
            i++;
            if (i>10)
                throw Exception::Error;
            new FileIOPermission(fileName, #IO_WRITE).assert();
            fileStream = System.IO.File::Create(fileName);
            fileStream.Dispose();
            CodeAccessPermission::revertAssert();
            ok = true;
        }
        catch (Exception::Error)
        {
            throw error("@SYS326095");
        }
        catch
        {
            exceptionTextFallThrough();
            CodeAccessPermission::revertAssert();
        }
    }

    return fileName;
}

/// <summary>
///     Returns the number associated with the specified month name.
/// </summary>
/// <param name="_monthName">
/// The name of the month.
/// </param>
/// <returns>
/// The number associated with the month name.
/// </returns>
public static int monthUSName2Num(str _monthName)
{
    int ret;
    str monthName = _monthName;
    #Define.month1('january')
    #Define.jan('jan')
    #Define.month2('february')
    #Define.feb('feb')
    #Define.month3('march')
    #Define.mar('mar')
    #Define.month4('april')
    #Define.apr('apr')
    #Define.month5('may')
    #Define.month6('june')
    #Define.jun('jun')
    #Define.month7('july')
    #Define.jul('jul')
    #Define.month8('august')
    #Define.aug('aug')
    #Define.month9('september')
    #Define.sep('sep')
    #Define.month10('october')
    #Define.oct('oct')
    #Define.month11('november')
    #Define.nov('nov')
    #Define.month12('december')
    #Define.dec('dec');

    monthName = strLwr(_monthName);

    switch(monthName)
    {
        case #month1:
        case #jan:
            ret = 1;
            break;
        case #month2:
        case #feb:
            ret = 2;
            break;
        case  #month3:
        case #mar:
            ret = 3;
            break;
        case #month4:
        case #apr:
            ret = 4;
            break;
        case #month5:
            ret = 5;
            break;
        case #month6:
        case #jun:
            ret = 6;
            break;
        case #month7:
        case #jul:
            ret = 7;
            break;
        case #month8:
        case #aug:
            ret = 8;
            break;
        case #month9:
        case #sep:
            ret = 9;
            break;
        case #month10:
        case #oct:
            ret = 10;
            break;
        case #month11:
        case #nov:
            ret = 11;
            break;
        case #month12:
        case #dec:
            ret = 12;
            break;
        default:
            ret = 0;
            break;
    }
    return ret;
}

Get DimensionAttribute by providing the dimensionnumber

public static dimensionAttribute getDimensionByNumber(int _dimensionNumber)
{
    RecId companyLedger =  Ledger::findByLegalEntity(CompanyInfo::findDataArea(curext()).RecId).RecId;
    DimensionAttribute dimensionAttribute;
    Query query = new Query();
    QueryBuildDataSource qbd;
    QueryRun qr;
    ;
    if (! _dimensionNumber)
    {
        return dimensionAttribute;
    }
    qbd = query.addDataSource(tableNum(dimensionAttribute));
    qbd.fields().dynamic(false);
    qbd.fields().addField(fieldNum(dimensionAttribute, Name));
    qbd.addOrderByField(fieldNum(dimensionAttribute, Name));
    qbd.addGroupByField(fieldNum(dimensionAttribute, Name));
    qbd.addRange(fieldNum(dimensionAttribute, RecId)).value(queryNotValue(DimensionAttribute::getMainAccountDimensionAttribute()));
    qbd = qbd.addDataSource(tableNum(dimensionHierarchyLevel));
    qbd.fields().dynamic(true);
    qbd.relations(true);

    qbd = qbd.addDataSource(tableNum(ledgerStructure));
    qbd.relations(false);
    qbd.addLink(fieldNum(dimensionHierarchyLevel, DimensionHierarchy), fieldNum(ledgerStructure, DimensionHierarchy));
    qbd.addRange(fieldNum(ledgerStructure, Ledger)).value(queryvalue(companyLedger));

    qr = new QueryRun(query);
    qr.enablePositionPaging(true);
    qr.addPageRange(_dimensionNumber,1);
    if (qr.next())
    {
        dimensionAttribute = qr.getNo(1);
    }
    return dimensionAttribute;
}

Create Non clusterindex in Ax with include keyword.

Create non clustered index with include keyword suggestion given by query plan

 Index with Included Columns
You can extend the functionality of nonclustered indexes by adding nonkey columns to the leaf level of the nonclustered index. By including nonkey columns, you can create nonclustered indexes that cover more queries. This is because the nonkey columns have the following benefits:
•    They can be data types not allowed as index key columns.
•    They are not considered by the Database Engine when calculating the number of index key columns or index key size.
An index with included nonkey columns can significantly improve query performance when all columns in the query are included in the index either as key or nonkey columns. Performance gains are achieved because the query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations.


Issue

Whenever a synchronization is done, AX overwrites the indexes which we put through SQL

Resolution

a. Created the index via AX as a table method


public static server void createSqlIndexes()
{

SqlStatementExecutePermission SqlStatementExecutePermission;
str createIndexSql;

Connection connection=new Connection(); Statement statement=connection.createStatement();
createIndexSql=
@"IF EXISTS(select * from sys.indexes where object_id=OBJECT_ID(N'[dbo].[InventDIM]')

AND NAME=N'sonIdx' )
DROP INDEX [sonIdx] on [dbo].[InventDIM] WITH (ONLINE=OFF)
CREATE NONCLUSTERED INDEX [sonIdx] ON [dbo].[INVENTDIM] ([INVENTBATCHID],[WMSLOCATIONID],[INVENTSERIALID],[INVENTSIZEID],[INVENTCOLORID],[INVENTGTDID_RU],[INVENTPROFILEID_RU],[INVENTOWNERID_RU],[INVENTSTYLEID],[LICENSEPLATEID],[INVENTSTATUSID],[DATAAREAID],[PARTITION],[INVENTLOCATIONID],[CONFIGID],[INVENTSITEID])
INCLUDE ([INVENTDIMID])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)";


SqlStatementExecutePermission=new SqlStatementExecutePermission(createIndexSql);
SqlStatementExecutePermission.assert();
statement.executeUpdate(createIndexSql);
CodeAccessPermission::revertAssert();
}

Execute this code in the application class dbsynchronize method at the end of the method after standard dbsynch done. so that we will have an sql index.