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

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;
}

AX2012 Import CostCenter Owner

static void Krishh_ImportCostCenterOwner(Args _args)
{
    #File
    #AviFiles
    #OCCRetryCount

    Dialog                      dialog = new dialog("Import cost center owners");
    DialogField                 dlgFileNameOpen, dlgFirstRowsIsHeader;
    Filename                    fileName;
    NoYesId                     firstRowIsHeader;
    container                   readcon;
    CommaIO                     file;
    FileIoPermission            permission;
    SysOperationProgress        progressbar = new SysOperationProgress();
    Counter                     fileRowsRead;
    OMOperatingUnit             oMOperatingUnit;
    DimensionAttribute          dimensionAttributeValue;
    HcmWorkerRecId              hcmWorkerRecId;

    dlgFileNameOpen         = dialog.addField(extendedTypeStr(FilenameOpen));
    dlgFirstRowsIsHeader    = dialog.addField(extendedTypeStr(NoYesId), "First row is header");

    if (dialog.run())
    {
        fileName            = dlgFileNameOpen.value();
        firstRowIsHeader    = dlgFirstRowsIsHeader.value();

        if (!fileName)
            throw error("File name must be specified");

        permission = new FileIoPermission(filename,'r');
        permission.assert();

        file = new CommaIo(filename, 'r');
        file.inFieldDelimiter(';');

        if (!file)
            throw error(strFmt("Error opening file", fileName));

        progressbar.setCaption("Importing cost center owners");
        progressbar.setAnimation(#AviUpdate);

        try
        {
            ttsBegin;

            while (file.status() == IO_Status::OK)
            {
                readCon = file.read();
                fileRowsRead++;

                if (fileRowsRead > firstRowIsHeader && readCon && conPeek(readcon, 1))
                {
                    oMOperatingUnit = OMOperatingUnit::findName(conPeek(readcon, 1), OMOperatingUnitType::OMCostCenter);

                    if (!oMOperatingUnit)
                        warning(strFmt("Cost center %1 not found."));
                    else
                    {
                        // Find worker from user id
                        hcmWorkerRecId = HcmWorker::userId2Worker(conPeek(readcon, 2));

                        if (!hcmWorkerRecId)
                            warning(strFmt("User id %1 not found as worker in AX."));
                        else
                        {
                            // Find and update dimensionAttributeValue with worker as owner
                        }

                    }
                }

                progressbar.setText(strfmt("@SYS136776", fileRowsRead));
                progressbar.update();
            }

            ttsCommit;

            info(strFmt("%1 rows read from file", fileRowsRead));

        }
        catch (Exception::Error)
        {
            error("Error");
        }
        catch (Exception::Deadlock)
        {
            retry;
        }
        catch (Exception::UpdateConflict)
        {
            if (appl.ttsLevel() == 0)
            {
                if (xSession::currentRetryCount() >= #RetryNum)
                {
                    throw Exception::UpdateConflictNotRecovered;
                }
                else
                {
                    retry;
                }
            }
            else
            {
                throw Exception::UpdateConflict;
            }
        }
    }
}

AX2012 Export Account Structures

static void Krishh_AcctStructureExport(Args _args)
{
    DimensionHierarchy                  dimensionHierarchy;
    DimensionHierarchyLevel             dimensionHierarchyLevel;
    DimensionAttribute                  dimensionAttribute;
    DimensionConstraintNode             dimensionConstraintNode;
    DimensionConstraintNodeCriteria     dimensionConstraintNodeCriteria;
    FileIOPermission                    fileIoPermission;
    CommaIO                             csvFile;
    container                           writecon;
    Dialog                              dialog;
    DialogField                         dfFileName;
    FileName                            fileName;
    boolean                             firstRow = true;
    Counter                             countRows;
    #File

    dialog = new Dialog("Export account structure text file");
    dfFileName = dialog.addField(extendedTypeStr(FileNameSave));
    dialog.filenameLookupFilter(["CSV files", #AllFilesName+#CSV, "All files", #AllFiles]);

    if (dialog.run())
        filename =  dfFileName.value();
    else
        throw error("Export cancelled");

    fileIoPermission = new FileIOPermission(filename ,'W');
    fileIoPermission.assert();

    csvFile = new CommaIO(filename, 'w');
    csvFile.outFieldDelimiter(';');

    while select dimensionHierarchy order by dimensionHierarchy.Name, dimensionHierarchyLevel.Level, dimensionConstraintNodeCriteria.RangeFrom, dimensionConstraintNodeCriteria.RangeTo, dimensionConstraintNodeCriteria.WildCardString
        where !dimensionHierarchy.IsSystemGenerated
        join dimensionHierarchyLevel
            where   dimensionHierarchyLevel.DimensionHierarchy == dimensionHierarchy.RecId
            join dimensionAttribute
                where   dimensionAttribute.RecId == dimensionHierarchylevel.DimensionAttribute
            join dimensionConstraintNode
                where   dimensionConstraintNode.DimensionHierarchyLevel == dimensionHierarchyLevel.RecId
                join dimensionConstraintNodeCriteria
                    where   dimensionConstraintNodeCriteria.DimensionConstraintNode == dimensionConstraintNode.RecId
    {
        if (firstRow)
        {
            writeCon = [    "Account structure",
                            "Level",
                            "Dimension",
                            "From",
                            "To",
                            "Wildcard"];

            csvFile.write(writecon);
            firstRow = false;
        }

        writeCon = [    dimensionHierarchy.Name,
                        int2str(dimensionHierarchyLevel.Level),
                        dimensionAttribute.Name,
                        dimensionConstraintNodeCriteria.RangeFrom,
                        dimensionConstraintNodeCriteria.RangeTo,
                        dimensionConstraintNodeCriteria.WildCardString];

        csvFile.write(writecon);
        countRows++;
    }

    CodeAccessPermission::revertAssert();

    info(strFmt("%1 rows written to %2.", countRows, fileName));
}

AX2012 Financial Dimension Service classes.

Explaining some financial dimension Classes that used to validate,Build dimensions.

Validate LedgerAccount against account structure.
FinancialDimensionService class:- ValidateLedgerAccount(LedgerAccountValidationContract)

DimensionServiceProvider: This class provides multiple methods to build DimensionStorage object based on our dimension values list with different combinations ex: buildDimensionStorageAttributesAndValues(List _values, Map _dimensionSpecifiers),buildDimensionStorageForLedgerAccount( LedgerAccountContract   _ledgerAccount,    boolean                 _createForDefaultAccount = false)

DimensionValidation: this class manages the validation for dimension combinations.ex: validateByTreeForCombination(Validates the specified combination against the constraint trees and also validates the ledger    account rules).validateByTree(dimcombinationRecid).

AX2012 Get dimension Values

To get the master Data for Dimension attributes we can use two service classes.
1. DimensionService
2. DimenisionValueService

we will see what Dimension Service class is providing
1. getActiveDimensionsFromLedger()- Gets active dimensions in current company which will return the DimensionContract class collection List.
2. getDimensions(AccountStructureContract _accountStructureContract)-Processes an incoming account structure contract and return a list of financial dimensions for a specific account structure.
3. getDimensionsAll()-Gets a list of all financial dimensions, which returns a A String list of dimension values using DimensionContract class.

we will see what DimensionValue Service class is providing
using this class we can create/Get DimensionValues for the specific dimension attribute.

Methods:
1. createDimensionValue(DimensionValueContract _dimensionValueContract)-Processes an incoming dimension value contract and creates a financial dimension value for a specific financial dimension.
2. getDimensionValues(DimensionContract _dimensionContract)- processes an incoming dimension contract and returns a list of financial dimension values for a specific financial dimension. and returns the list of the dimension values for that specific dimension attribute.

we can use this service classes and consume in any external application and call this service methods.


by using these two services we can get the dimension values easily.


static void Krishh_getDimensionValues(Args _args)
{

    List                            dimensionAttributeList;
    ListEnumerator                  listEnumerator;

    DimensionService                dimensionService;
    DimensionContract               dimensionContract;
    AccountStructureContract        accountStructureContract;

   
    dimensionService            = new dimensionService();

    accountStructureContract    = new AccountStructureContract();
    accountStructureContract.parmName("Account Structure - P&L");

    dimensionAttributeList      = dimensionService.getDimensions(accountStructureContract);

    listEnumerator  = dimensionAttributeList.getEnumerator();
    while (listEnumerator.moveNext())
    {
        dimensionContract   = listEnumerator.current();
        info(strFmt("%1, %2",dimensionAttributeList.elements(), dimensionContract.parmDimensionName()));
    }

}

Read Dimension combination values

These functions used to read the individual ledger dimension combinatio values.

private void Krishh_getDimensionValues(LedgerDimensionAccount _dimensionRecid)
{
   
    DimensionAttributeLevelValueAllView dimAttrView;
    DimensionAttribute                  dimAttr;
 

        while select DisplayValue from dimAttrView  
            where dimAttrView.ValueCombinationRecId == _dimensionRecid
            join BackingEntityType from dimAttr
                where dimAttr.RecId == dimAttrView.DimensionAttribute
        {
            switch (dimAttr.BackingEntityType)
            {
                case tableNum(DimAttributeMainAccount):
                    info(strFmt(“Main Account: %1″, dimAttrView.DisplayValue));
                    break;
             
                case tableNum(DimAttributeOMBusinessUnit):
                    info(strFmt(“Business Unit: %1″, dimAttrView.DisplayValue));
                    break;

                case tableNum(DimAttributeCustTable):
                    info(strFmt(“Customer: %1″, dimAttrView.DisplayValue));
                    break;

                case tableNum(DimAttributeOMDepartment):
                    info(strFmt(“Department: %1″, dimAttrView.DisplayValue));
                    break;

                case tableNum(DimAttributeHcmWorker):
                    info(strFmt(“Worker: %1″, dimAttrView.DisplayValue));
                    break;
            }  
        }
   
 
}

private DimensionValue krishh_getDimensionValueByName (LedgerDimensionAccount _dimensionRecid, Name _name)
{
    DimensionAttributeLevelValueAllView dimAttrView;
    DimensionAttribute                  dimAttr;

 
        Select DisplayValue from dimAttrView  
            where dimAttrView.ValueCombinationRecId == _dimensionRecid
            join BackingEntityType from dimAttr
                where dimAttr.RecId == dimAttrView.DimensionAttribute
&& dimattr.Name==_name;


return dimAttrView.DisplayValue;

}