ETL Informatica

What is Informatica?

Informatica is a Software development company, which offers data integration products. If offers products for ETL, data masking, data Quality, data replica, data virtualization, master data management, etc.

Informatica Powercenter ETL/Data Integration tool is a most widely used tool and in the common term when we say Informatica, it refers to the Informatica PowerCenter tool for ETL.

Informatica Powercenter is used for Data integration. It offers the capability to connect & fetch data from different heterogeneous source and processing of data.

For example, you can connect to an SQL Server Database and Oracle Database both and can integrate the data into a third system.

The latest version of Informatica PowerCenter available is 9.6.0. The different editions for the PowerCenter are

• Standard edition
• Advanced edition
• Premium edition

Typical use cases for Informatica can be

• An organization migrating from existing legacy system like mainframe to a new database system. So the migration of its existing data into a system can be performed.
• Enterprises setting up their Data Warehouse would require an ETL tool to move data from the Production system to Warehouse.
• Integration of data from various heterogeneous systems like multiple databases and file-based systems can be done using Informatica.
• Informatica can be used as a data cleansing tool.

Why do we need Informatica?

Informatica comes to the picture wherever we have a data system available and at the backend we want to perform certain operations on the data. It can be like cleaning up of data, modifying the data, etc. based on certain set of rules or simply loading of bulk data from one system to another.

Informatica offers a rich set of features like operations at row level on data, integration of data from multiple structured, semi-structured or unstructured systems, scheduling of data operation. It also has the feature of metadata, so the information about the process and data operations are also preserved.

AX Export to Excel Sample X++

static void AX_ExportToExcel_Project(Args _args)
{
    #AviFiles
    SysOperationProgress    progress = new SysOperationProgress();
    ProjTable               projTable;
    SysExcelWorksheetHelper worksheetHelper;
    SysExcelHelper          sysExcelHelper;
    SysExcelWorksheet       worksheet;
    int                     rowCount = 1;
    int                     sumRow;
    str                     worksheetName;
//    COMVariant              cellValue = new COMVariant(COMVariantInOut::Out);
    str                     fileName;
    str attachmentPath      = "C:\\Temp\\";


    #define.ProjId(1)
    #define.Name(2)
    #define.ProjGroupId(3)
    #define.Type(4)
    #define.Status(5)

    progress.setCaption('Export To Excel');
    progress.setAnimation(#AviTransfer);

    worksheetName = "Project List";

    sysExcelHelper = SysExcelHelper::construct();

    sysExcelHelper.initialize();

    worksheet = sysExcelHelper.addWorksheet(worksheetName);

    worksheetHelper = SysExcelWorksheetHelper::construct(worksheet);

    worksheetHelper.addColumnFromTableField(#ProjId, tablenum(ProjTable), fieldnum(ProjTable, ProjId));
    worksheetHelper.addColumnFromTableField(#Name, tablenum(ProjTable), fieldnum(ProjTable, Name));
    worksheetHelper.addColumnFromTableField(#ProjGroupId, tablenum(ProjTable), fieldnum(ProjTable, ProjGroupId));
    worksheetHelper.addColumnFromTableField(#Type, tablenum(ProjTable), fieldnum(ProjTable, Type));
    worksheetHelper.addColumnFromTableField(#Status, tablenum(ProjTable), fieldnum(ProjTable, Status));

    //worksheetHelper.addColumn(#Dimension, "Dimension", Types::String);

    while select *
        from projTable
    {
        progress.setText(strfmt("Current Project %1", projTable.ProjId));

        rowCount ++;
        worksheetHelper.setCellValue(#ProjId, rowCount, ProjTable.ProjId);
        worksheetHelper.setCellValue(#Name, rowCount, ProjTable.Name);
        worksheetHelper.setCellValue(#ProjGroupId, rowCount, ProjTable.ProjGroupId);
        worksheetHelper.setCellValue(#Type,rowCount, enum2str(ProjTable.Type));
        worksheetHelper.setCellValue(#Status,rowCount, enum2str(ProjTable.Status));
    }

    worksheetHelper.autoFitColumns();
    worksheetHelper.formatWorksheetTableStyle(sysExcelHelper.getOfficeVersion());

    fileName = strFmt('%1_ProjectDetails_%2%3', attachmentPath,today(),sysExcelHelper.getFileExtension());


    worksheet.protect("axProjects");
    sysExcelHelper.save(filename);
    sysExcelHelper.launchExcel();
}

Find query Range exists on Parameter Query in AX, DAX

Boolean hasRangesOnParmFilter;
Query   _query = this.ParmQuery();

hasRangesOnParmFilter = SysQuery::queryHasRangesOrFilters(_query);

Workflow Scope in Dynamics 365 for Finance and Operations

Workflow Scope

Scopes

1) User

2) Business unit

3) Parent: Child business unit

4) Organization

1. User - Choosing this scope means the workflow will run only on the records owned by the same user as the workflow user.

2. Business Unit - Means the workflow will run on all records owned by the users of the same business unit as the workflow user.

3. Parent: Child Business Unit - The workflow will run on the records owned by the users of the same business unit as the workflow user as well as any child business units.

4. Organization - The workflow will run on records owned by any user in Application. Since it will trigger for all records, organization scope is the most used scope option.

Type of Grids and Grid Features, Editable Grids in Dynamics 365 for Finance and Operation

Editable grid

Before release of D365, users needed to open the existing records to update any data in the entity. Even if users want to update single field in multiple records, they had to open the individual record and update the field and save the record. For each record, they had to repeat these steps. This approach of updating data was time consuming and irritating.

With introduction to editable grid, Microsoft has allowed users to update the records right away with all those validations which was applicable on the form.

Editable grid is nothing but a list or view of records having editing capabilities without opening the record.

Type Of Grids

Types of grids

1. Read-only grid – View only, Can't be edit in DAX 365
2. Home grid – Entity’s public views and personal views with editing capabilities are called home grid.
3. Sub grid – Show related records in Same Form. Sub grids can also be made editable.
4. Nested grid – Nested grids are shown when we expand any record in view itself without opening form. This grid also shows related records of expanded record from view. (As of it is applicable only for Mobile and Tablet, Not for web client)
5. Dashboard grid

Features
1) Below types of fields are read only in editable grid:

1. Customer
2. Roll up Fields
3. Calculated Fields
4. Composite Fields
5. Status and Status Reason Field

2) Users don’t have to explicitly click on save button. Records are saved automatically when focus from the record is moved.

3) As its name suggests, only inline editing is possible and users cannot create records through inline editable grids. Users can still use quick create form to quickly create the records.

4) Below grid functionality is available for editable grids:

1. Sorting
2. Searching
3. Grouping
4. Filtering
5. Pagination
6. Column resizing
7. Column movement by simply drag and drop capabilities

// Editable Grids in Dynamics 365

Editable Grid on Entity

Steps:
1) Settings > Customization > Customize The System > Default Solution > Entity
2)  Add Control -> Editable grid option
3) Select Editable Grid and click on Add.
4) Make the requires change in the Control section(Web, Tablet, Phone)
5) Save and Publish

Editable grid on FORM

Steps:
1) Go to your form , Open the Form Editor of that form.
2)  Add Control -> Editable grid option
3) Select Editable Grid and click on Add.
4) Make the requires change in the Control section(Web, Tablet, Phone)
5) Save and Publish

Ledger and Default Dimension combination in Single view

Dimension View Project XPO

SQL Query:

Default dimension:
SELECT
     T1.DefaultDimension AS DefaultDimension,
     T2.DISPLAYVALUE AS Employee,
     T3.DISPLAYVALUE AS CostCenter,
     T4.DISPLAYVALUE AS Division,
     T5.DISPLAYVALUE as Department,
     'Default' as DimType
FROM
     DefaultDimensionVIEW T1
LEFT OUTER JOIN DefaultDimensionVIEW T2
ON T1.DefaultDimension=T2.DefaultDimension
and T2.Name = 'Employee'
LEFT OUTER JOIN DefaultDimensionVIEW T3
ON T1.DefaultDimension=T3.DefaultDimension
and T3.Name = 'CostCenter'
LEFT OUTER JOIN DefaultDimensionVIEW T4
ON T1.DefaultDimension=T4.DefaultDimension
and T4.Name = 'Division'
LEFT OUTER JOIN DefaultDimensionVIEW T5
ON T1.DefaultDimension=T5.DefaultDimension
and T5.Name = 'Department'
GROUP BY T1.DefaultDimension,
T2.DISPLAYVALUE,
T3.DISPLAYVALUE,
 T4.DISPLAYVALUE,
T5.DISPLAYVALUE


Ledger Dimension:

//View 1 staging

CREATE VIEW [dbo].[vDIMENSIONATTRIBUTELEVELVALUEALLVIEW_stag]   

As  

SELECT 

T1.DIMENSIONATTRIBUTEVALUECOMBINATION AS VALUECOMBINATIONRECID,  

 T1.ORDINAL AS GROUPORDINAL, 

 T2.DISPLAYVALUE AS DISPLAYVALUE,  

 T2.RECID AS LEVELVALUERECID,  

 T2.ORDINAL AS VALUEORDINAL,  

 T2.DIMENSIONATTRIBUTEVALUEGROUP AS DIMENSIONATTRIBUTEVALUEGROUP,  

 T3.DIMENSIONATTRIBUTE AS DIMENSIONATTRIBUTE,  

 T3.RECID AS ATTRIBUTEVALUERECID,  

 T3.ENTITYINSTANCE AS ENTITYINSTANCE,  

 T3.HASHKEY AS ATTRIBUTEVALUEHASHKEY,  

 T4.Name as DimensionName,

 1010 AS RECID 

FROM DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION T1   

  

CROSS JOIN DIMENSIONATTRIBUTELEVELVALUE T2   

CROSS JOIN DIMENSIONATTRIBUTEVALUE T3   

CROSS JOIN DIMENSIONATTRIBUTE T4   

  

WHERE (T1.DIMENSIONATTRIBUTEVALUEGROUP=T2.DIMENSIONATTRIBUTEVALUEGROUP   

 AND t4.RecId =t3.DIMENSIONATTRIBUTE  

 AND T2.DIMENSIONATTRIBUTEVALUE=T3.RECID) 

  

GROUP BY T1.DIMENSIONATTRIBUTEVALUECOMBINATION,T1.ORDINAL,T2.DISPLAYVALUE,T2.RECID,T3.RECID,

T2.ORDINAL,T2.DIMENSIONATTRIBUTEVALUEGROUP, T3.DIMENSIONATTRIBUTE,T3.ENTITYINSTANCE,T3.HASHKEY,T4.NAME

GO

//view 2

CREATE VIEW [dbo].[vDIMENSIONATTRIBUTELEVELVALUEALLVIEW]   

As  

SELECT

     T1.VALUECOMBINATIONRECID   as LedgerDimension,

     T2.DISPLAYVALUE as AccountNum,

     T3.DISPLAYVALUE as BusinessUnit,

     T4.DISPLAYVALUE as CostCenter,

     T5.DISPLAYVALUE as Project,

T6.DISPLAYVALUE

as OnsiteOffshore,

T7.DISPLAYVALUE

as Worker,

T8.DISPLAYVALUE

as Geo,

'LedgerDimension'

as Type

FROM

     DIMENSIONATTRIBUTELEVELVALUEALLVIEW T1

     LEFT OUTER JOIN vDIMENSIONATTRIBUTELEVELVALUEALLVIEW_stag T2 

ON T1.VALUECOMBINATIONRECID=T2.VALUECOMBINATIONRECID

and T2.DimensionName = 'MainAccount' 

     LEFT OUTER JOIN vDIMENSIONATTRIBUTELEVELVALUEALLVIEW_stag T3 

ON T1.VALUECOMBINATIONRECID=T3.VALUECOMBINATIONRECID

and T3.DimensionName = 'BusinessUnit' 

     LEFT OUTER JOIN vDIMENSIONATTRIBUTELEVELVALUEALLVIEW_stag T4 

ON T1.VALUECOMBINATIONRECID=T4.VALUECOMBINATIONRECID

and T4.DimensionName = 'CostCenter'

     LEFT OUTER JOIN vDIMENSIONATTRIBUTELEVELVALUEALLVIEW_stag T5 

ON T1.VALUECOMBINATIONRECID=T5.VALUECOMBINATIONRECID

and T5.DimensionName = 'Project'

LEFT OUTER JOIN vDIMENSIONATTRIBUTELEVELVALUEALLVIEW_stag T6 

ON T1.VALUECOMBINATIONRECID=T6.VALUECOMBINATIONRECID

and T6.DimensionName = 'OnsiteOffshore'

LEFT OUTER JOIN vDIMENSIONATTRIBUTELEVELVALUEALLVIEW_stag T7 

ON T1.VALUECOMBINATIONRECID=T7.VALUECOMBINATIONRECID

and T7.DimensionName = 'Worker'

LEFT OUTER JOIN vDIMENSIONATTRIBUTELEVELVALUEALLVIEW_stag T8 

ON T1.VALUECOMBINATIONRECID=T8.VALUECOMBINATIONRECID

and T8.DimensionName = 'Geo'

GROUP BY T1.VALUECOMBINATIONRECID, T2.DISPLAYVALUE,

T3.DISPLAYVALUE,

T4.DISPLAYVALUE,

T5.DISPLAYVALUE, T6.DISPLAYVALUE,

T7.DISPLAYVALUE,

T8.DISPLAYVALUE

GO

Get Current User running report list in Ax 2012 from SQL DB

--Query

SELECT UserInfo.[Name],
ElementName AS [Report Name],
UserInfo.[Enable] AS [Active Employee]
INTO #temp
FROM SysLastValue WITH(NOLOCK)
INNER JOIN UserInfo WITH(NOLOCK)
                ON UserInfo.ID = SysLastValue.UserID
WHERE UserID NOT IN ('Admin', '')
     AND RecordType = 18 /* Report */



SELECT t.[Report Name],
(SELECT COUNT(DISTINCT [Name])
   FROM #temp sub
WHERE sub.[Active Employee] = 1
AND sub.[End User] = 1
AND sub.[Report Name] = t.[Report Name]) AS [# of Active Employees Who Ran The Report]
FROM #temp t
GROUP BY t.[Report Name]
ORDER BY t.[Report Name]

DROP TABLE #temp

Change default Source and Target layers when comparing code in Ax 2012

Change default layers when comparing code

Classes- --- SysCompare.initContext() method

    if (comboBox1.getText(comboBox1.selection()) == comboBox2.getText(comboBox2.selection()) && comboBox2.items() > comboBox2.selection() + 1 )
    {
        comboBox2.selection(comboBox2.selection()+1);

       
        if (comboBox1.items() == comboBox2.items() - 1)
        {
            if (comboBox1.items() > 2 && Global::strEndsWith(comboBox2.getText(comboBox2.items() - 1), ' (SYS)'))
            {
                comboBox1.selection(comboBox1.items() - 2);
                comboBox2.selection(comboBox2.items() - 2);
            }
        }
    }
 
    else if (comboBox1.items() > 1 && comboBox2.items() == 1 && Global::strEndsWith(comboBox2.getText(1 - 1), ' (xpo)'))
    {
        // Compare last layer to the imported XPO
        comboBox1.selection(comboBox1.items() - 1); // Set first drop down to last possible option
    }

Gets the AX table ID for the table name in SQL Function

ALTER FUNCTION  fnGetAXTableID
(
    @tableName nvarchar(100)
)
RETURNS int
AS
BEGIN
    DECLARE @tableNum int
 
    SELECT @tableNum = TableID
    FROM SQLDictionary
    WHERE [Name] = @tableName
        AND FieldID = 0
        AND Array = 0

    RETURN @tableNum

END

//Call Scalar Function in SQL Query

select  dbo.fnGetAXTableID('MyTable') as [TableId]

Get the next unique file name

//Get the next unique file name

Input:
fileOriginalsPath (path folder)
fileName

fileNameTemp = Global::fileNameNext(fileOriginalsPath + fileName);