Extract All tables from Dynamics AX 2009 Data Dictionary Configurations

• Download Class_ExtractTablesNamesFromConfigs - 5.29 KB
• Download List_of_Dynamics_AX_2009_database_Config_Names.zip - 8.33 KB

Introduction

This article is intended primarily for intermediate Microsoft Dynamics AX users who are familiar with X++ and MorphX. The Goal of this article is to help the users to extract the table names from the configuration keys provided in the Microsoft Dynamics AX 2009 Data dictionary. The code utility will be very handy for intermediate to advanced users to analyse and understand the schema and table design related to the modules. This could be useful in the initial project analysis and design phase.

Microsoft has a range of products under the Microsoft Dynamics umbrella that are more applicable for the small, medium and larger business organizations. Microsoft Dynamics AX (formerly Axapta) is one among the Microsoft Dynamics ERP primary products.

X++ is the language used to built Dynamics AX. MorphX is an integrated development environment in Microsoft Dynamics AX that allows developers to graphically design the GUI. MorphX allows access to application classes that are available in the application, by launching the X++ code editor.

The Configuration Keys in the data dictionary in Dynamics AX 2009 should look like the above image.

Background

XPO is an out put format of Dynamics AX Objects. XPO objects can be imported and exported in Microsoft Dynamics AX.

The use and explanation of Reverse Engineering tool and Cross Reference Tool is out of scope of this Article.

ExtractTablesNamesFromConfigs Class

This class is basically to demonstrate a method of extracting tables information from the configuration keys. It can be used for analysis. Here is the simple class which creates text files on the fly based on the configuration key names from the data dictionary.

These Configuration Keys hold the tables information. It can be identified to which module it is related to by label information in the properties window. The code is written using X++ code using MorphX IDE.

The ExtractTablesNamesFromConfigs class is a utility to extracts all the table names from the configuration keys to text files. The text files will all be created under the folder 'C:\AllTables\Configs' with the relevant config names.

Using the code

Extract the xpo file from the zip file and import the Class_ExtractTablesNamesFromConfigs.xpo object into AOT. Once imported you will be able to see this object at the end in the AOT Class tree. click open to run this Class. After the execution the text files could be found under the folder 'C:\AllTables\Configs'.

For better understanding of the relationships and Data model. Users could use the Cross Reference tool and Reverse Engineering tool.

Cross Reference Tool in Dynamics AX 2009 helps in understanding the object relationships. For more information on this refer online documentation at the following link http://msdn.microsoft.com/en-us/library/aa626961(v=ax.50).aspx

The Reverse Engineering tool enables you to visualize data and classes in Microsoft Dynamics AX by creating UML data models, UML object models, and ERX ER data models. For more information on this refer online documentation at the following link http://msdn.microsoft.com/en-us/library/aa499193(v=ax.50).aspx

Most of the configurations from Dynamics AX are added here, if you want to add a new config then customize it here in the following method.

public void execMethod()
{
     int i;
     str sConKeyName[];
        sConKeyName[1]="AIF";         //Application Integration Framework
        sConKeyName[2]="AIFWebService";         //Application Integration Framework Web Services
        sConKeyName[3]="Asset";         //Fixed assets
        sConKeyName[4]="AssetDepBook";         //Fixed asset depreciation books
        sConKeyName[5]="Bank";         //Bank
        sConKeyName[6]="BankCheque";         //Check
        sConKeyName[7]="BankDeposit";         //Desposit slip
        sConKeyName[8]="BankElectronicBanking";         //Electronic Banking
        sConKeyName[9]="BAS";         //Business analysis
        sConKeyName[10]="BOM";         //Bill of materials
        sConKeyName[11]="BOMApprove";         //BOM approval
        sConKeyName[12]="BOMCostBreakdown";         //Allow cost breakdown activation
        sConKeyName[13]="BOMVersion";         //BOM versions
        sConKeyName[14]="Commission";         //Commission
        sConKeyName[15]="ComplianceMgmt";         //Compliance Management II
        sConKeyName[16]="Config";         //Item dimension - configuration
        sConKeyName[17]="COSAllocation";         //Allocation
        sConKeyName[18]="COSBaseModule";         //Cost accounting
        sConKeyName[19]="COSBudget";         //Budgeting
        sConKeyName[20]="COSCalculation";         //Calculation
        sConKeyName[21]="COSCostDistribution";         //Cost distribution
        sConKeyName[22]="COSImportCAC";         //Import Transaction from previous version
        sConKeyName[23]="COSOpenDimension";         //Unlimited dimensions
        sConKeyName[24]="COSPlanCostCalc";         //Flexible Budgeting
        sConKeyName[25]="COSWorkAllocation";         //Internal cost allocation
        sConKeyName[26]="COSWorkDistribution";         //Service distribution
        sConKeyName[27]="COSWorkUnits";         //Service categories
        sConKeyName[28]="CreditCard";         //Credit card
        sConKeyName[29]="CRSEMultiple";         //Multiple countries/regions
        sConKeyName[30]="CSS";         //Customer Self-Service
        sConKeyName[31]="Currency";         //
        sConKeyName[32]="CurrencyEuro";         //Euro
        sConKeyName[33]="CurrencySecondaryCurrency";         //Secondary currency
        sConKeyName[34]="CustBillOfExchange";         //Bill of exchange
        sConKeyName[35]="CustCentralizedCustStatement";         //Centralized Customer Statement
        sConKeyName[36]="CustCollectionLetter";         //Collection Letter
        sConKeyName[37]="CustGiro";         //Giro
        sConKeyName[38]="CustInterest ";         //Interest
        sConKeyName[39]="CustMultipleInterestRates ";         //Multiple Interest Rates
        sConKeyName[40]="CustVendCreditInvoicing ";         //Credit Invoicing
        sConKeyName[41]="CustVendNegotiableInstrument";         //Negotiable Instrument
        sConKeyName[42]="DataExport";         //Data Export
        sConKeyName[43]="DimensionSetHierarchy ";         //Dimension Set Hierarchy
        sConKeyName[44]="EMS ";         //Environmental Sustainability
        sConKeyName[45]="EP";         //Enterprise Portal
        sConKeyName[46]="EPCompliance";         //Compliance site
        sConKeyName[47]="EPSalesRep";         //Sales Representative
        sConKeyName[48]="ESS";         //Employee Self-Service
        sConKeyName[49]="Event";         //Event
        sConKeyName[50]="Forecast";         //Inventory forecast
        sConKeyName[51]="ForecastDMPBase";         //Demand Planning Base
        sConKeyName[52]="ForeignTrade";         //Foreign trade
        sConKeyName[53]="FullSettlement ";         //Full Settlement
        sConKeyName[54]="HRMADA";         //ADA
        sConKeyName[55]="HRMAdministration";         //Human Resource I
        sConKeyName[56]="HRMAdministrationCompensation";         //Compensation
        sConKeyName[57]="HRMAdministrationPayrollInfo ";         //Payroll Information
        sConKeyName[58]="HRMCollaborative";         //Human Resource III
        sConKeyName[59]="HRMCollaborativeCourse";         //Course
        sConKeyName[60]="HRMEPEmployeeServices";         //Employee Services Role
        sConKeyName[61]="HRMi9";         //I-9
        sConKeyName[62]="HRMManagement ";         //Human Resource II
        sConKeyName[63]="HRMManagementAbsence";         //Absence
        sConKeyName[64]="HRMManagementRecruiting";         //Recruiting
        sConKeyName[65]="InventItemDimColor";         //Item dimension - Color
        sConKeyName[66]="InventItemDimSize ";         //Item dimension - Size
        sConKeyName[67]="InventPackaging ";         //Packaging duty
        sConKeyName[68]="InventQualityManagement ";         //Quality Management
        sConKeyName[69]="InventTransferOrders ";         //Transfer orders
        sConKeyName[70]="Jmg ";         //Shop Floor Control
        sConKeyName[71]="JmgEPSFCEmployee";         //Enterprise portal SFC employee role
        sConKeyName[72]="JmgJob";         //Shop Floor Control - Job registration
        sConKeyName[73]="JmgPayroll";         //Shop Floor Control - Pay generation
        sConKeyName[74]="KMBPM";         //Business Process Management
        sConKeyName[75]="KMBPMActionPlan";         //Action plans
        sConKeyName[76]="KMBPMGameplan";         //Strategic plan
        sConKeyName[77]="KMBSC";         //Balanced Scorecard
        sConKeyName[78]="KMEPPerformanceManagement";         //Performance Management Role
        sConKeyName[79]="KMEPQuestionnaire ";         //Questionnaire Role
        sConKeyName[80]="KMQuestionnaireAdvanced ";         //Questionnaire II
        sConKeyName[81]="KMQuestionnaireBasic ";         //Questionnaire I
        sConKeyName[82]="LedgerAdvAllocation ";         //Allocation
        sConKeyName[83]="LedgerAdvanced";         //General ledger - advanced
        sConKeyName[84]="LedgerAdvanced2";         //General ledger - advanced II
        sConKeyName[85]="LedgerAdvCashflowForecast ";         //Cash flow forecast
        sConKeyName[86]="LedgerAdvConsolidations ";         //Consolidate
        sConKeyName[87]="LedgerAdvIntercompanyAccounting ";         //Intercompany accounting
        sConKeyName[88]="LedgerAllocationRules ";         //Generated allocation
        sConKeyName[89]="LedgerBasic ";         //General ledger
        sConKeyName[90]="LedgerBasicBudget";         //Budget
        sConKeyName[91]="LedgerBasicJournalizeTransactions";         //Journalizing
        sConKeyName[92]="LedgerBasicReversingEntries";         //Reversing entries
        sConKeyName[93]="LedgerBasicSalesTax";         //Sales tax
        sConKeyName[94]="LedgerBasicSalesTaxJurisdictions";         //Sales tax jurisdictions
        sConKeyName[95]="LedgerBasicWithholdingTax";         //Withholding tax
        sConKeyName[96]="LogisticsAdvanced";         //Logistics
        sConKeyName[97]="LogisticsBasic";         //Trade
        sConKeyName[98]="LogisticsOverUnderDelivery";         //Over/underDelivery
        sConKeyName[99]="LogisticsRegistration ";         //Registration/picking
        sConKeyName[100]="Markup";         //Misc. charges
        sConKeyName[101]="PaymMan ";         //Payment management
        sConKeyName[102]="PaymSched";         //Payment schedule
        sConKeyName[103]="PBA_ProductBuilder";         //Product Builder
        sConKeyName[104]="PBA_ProductBuildingWebApp";         //Product Building WebApp
        sConKeyName[105]="PHRPayroll ";         //Payroll
        sConKeyName[106]="PHRPayrollJurisdiction";         //Payroll jurisdiction
        sConKeyName[107]="PHRPayrollJurisdictionCanada";         //Canada
        sConKeyName[108]="PHRPayrollJurisdictionUnitedStates";         //United States
        sConKeyName[109]="PHRSystem";         //System update utilities
        sConKeyName[110]="Presence";         //Presence Support
        sConKeyName[111]="PriceDisc";         //Price/Discount
        sConKeyName[112]="Prod";         //Production I
        sConKeyName[113]="ProdRouting";         //Production II
        sConKeyName[114]="ProdShop ";         //Production III
        sConKeyName[115]="ProjAdvanced ";         //Project - advanced
        sConKeyName[116]="ProjAdvCashFlow ";         //Cash flow
        sConKeyName[117]="ProjAdvCostControl";         //Cost control
        sConKeyName[118]="ProjAdvEstimate ";         //Estimate projects
        sConKeyName[119]="ProjAdvEstimateFixedPrice";         //Fixed-price
        sConKeyName[120]="ProjAdvEstimateInvestment";         //Investment
        sConKeyName[121]="ProjAdvWorkInProgress";         //Work In Process
        sConKeyName[122]="ProjBasic";         //Project
        sConKeyName[123]="ProjBasicCost";         //Expense
        sConKeyName[124]="ProjBasicForecast";         //Forecast
        sConKeyName[125]="ProjBasicHour";         //Hours
        sConKeyName[126]="ProjBasicInternalProject";         //Cost and internal project
        sConKeyName[127]="ProjBasicOnAcc";         //On account
        sConKeyName[128]="ProjBasicRevenue";         //Fee
        sConKeyName[129]="ProjBasicTimeMaterial";         //Time and material
        sConKeyName[130]="ProjQuotation";         //Project quotation
        sConKeyName[131]="ProjServerIntegration";         //Project Server Integration
        sConKeyName[132]="PurchInvoiceMatching ";         //Invoice matching
        sConKeyName[133]="PurchReq";         //Purchase Requisition
        sConKeyName[134]="QuotationBasic";         //Quotations
        sConKeyName[135]="ReportingServices";         //Reporting Services
        sConKeyName[136]="Req";         //Master planning
        sConKeyName[137]="ReqSchedAction ";         //Action message
        sConKeyName[138]="ReturnItem ";         //Return orders
        sConKeyName[139]="RFID";         //RFID
        sConKeyName[140]="RFIDBusinessProcessing";         //RFID Business processing
        sConKeyName[141]="RFIDTagging";         //RFID tagging
        sConKeyName[142]="RouteApprove";         //Approval of routes
        sConKeyName[143]="RuntimeBUS";         //BUS Runtime
        sConKeyName[144]="RuntimeVAR";         //VAR Runtime
        sConKeyName[145]="SalesDeliveryDateControl";         //Delevery date control
        sConKeyName[146]="SalesQuotation";         //Sales quotation
        sConKeyName[147]="SalesShipments";         //Shipments
        sConKeyName[148]="ShipCarrier";         //Shiping Carrier
        sConKeyName[149]="SIG ";         //Electronic signature
        sConKeyName[150]="SMA";         //Service
        sConKeyName[151]="SMABom";         //Service Management - BOM
        sConKeyName[152]="SMAManagement";         //Service management
        sConKeyName[153]="SMARepair";         //Service Management - Repair
        sConKeyName[154]="SMASubscription";         //Subscription
        sConKeyName[155]="SMASubscription_Indexation";         //Subscription indexation
        sConKeyName[156]="SmmCRM";         //Customer Relation Manager Series
        sConKeyName[157]="SmmLead";         //Leads
        sConKeyName[158]="SmmMA";         //Marketing automation
        sConKeyName[159]="smmMACampaignProcess";         //Campaign process
        sConKeyName[160]="smmOutlook";         //MS Office Outlook synchronization
        sConKeyName[161]="smmPhone";         //Telephony integration
        sConKeyName[162]="SmmSM";         //Sales management
        sConKeyName[163]="smmSMLeadProcess";         //Lead qualifying process
        sConKeyName[164]="smmSMSalesProcess";         //Sales process
        sConKeyName[165]="SmmTM";         //Telemarketing
        sConKeyName[166]="SuppItem";         //Supplementary item
        sConKeyName[167]="SysAdmin";         //Administration
        sConKeyName[168]="SysAPI";         //Optional APIs
        sConKeyName[169]="SysAPIDDE";         //Dynamic Data Exchange API
        sConKeyName[170]="SysAPIPipeClient";         //Pipe Client API
        sConKeyName[171]="SysCOM";         //Business Connector status
        sConKeyName[172]="SysCompanyInfo";         //Company accounts
        sConKeyName[173]="SysDatabaseLog";         //Database Log
        sConKeyName[174]="SysDeletedObjects40";         //Keep update objects 4.0
        sConKeyName[175]="SysDeletedObjects41";         //Keep update objects 4.1
        sConKeyName[176]="SysDevelopmentMorphX";         //Development
        sConKeyName[177]="SysDevelopmentXPP";         //X++ development
        sConKeyName[178]="SysDomain";         //Domains
        sConKeyName[179]="SysFillUtility";         //Fill utility
        sConKeyName[180]="SysMSSQL";         //Microsoft SQL Server
        sConKeyName[181]="SysOracle ";         //Oracle
        sConKeyName[182]="SysRecordLevelSecurity";         //Record level security
        sConKeyName[183]="SysSign";         //Life Science Electronic signature
        sConKeyName[184]="SysTest ";         //Test framework
        sConKeyName[185]="SysUserLog";         //User log
        sConKeyName[186]="TaxUnrealized";         //Unrealized sales tax
        sConKeyName[187]="TradeAgreements";         //Trade agreements
        sConKeyName[188]="TradeBlanketOrder";         //Blanket order
        sConKeyName[189]="TradeInterCompany";         //Intercompany
        sConKeyName[190]="TradeMultiShipTo";         //Multiple ship-to
        sConKeyName[191]="TradePrintManagement";         //Print management
        sConKeyName[192]="TransactionReversal_Cust";         //Customer transaction reversal
        sConKeyName[193]="TransactionReversal_Ledger";         //Ledger transaction reversal
        sConKeyName[194]="TransactionReversal_Vend";  //Vendor transaction reversal
        sConKeyName[195]="Trv ";         //Expense management
        sConKeyName[196]="TRVAdvance";         //Cash advance
        sConKeyName[197]="TrvElectronicPayment";         //Credit Card Import
        sConKeyName[198]="TrvPerDiem ";         //Per diems
        sConKeyName[199]="VendInvoicePool";         //Invoice pool excl. posting
        sConKeyName[200]="VendInvoiceRegister";         //Invoice journal
        sConKeyName[201]="VendInvoiceRegisterApproval";  //Invoice register and invoice approval journal
        sConKeyName[202]="VendPromissoryNote";         //Promissory note
        sConKeyName[203]="VSS";         //Vendor Self-Service
        sConKeyName[204]="WMSAdvanced";         //Warehouse Management II
        sConKeyName[205]="WMSBasic";         //Warehouse Management I
        sConKeyName[206]="WMSPallet";         //Pallets
        sConKeyName[207]="WrkCtr";         //Resources

    for (i=1; i<=207; ++ i)
    {
        this.FindAllTablesFromConfigKey(sConKeyName[i]);
    }
     info(strfmt("Execution finished sucessfully, Check the folder for files under 'C:\AllTables\Configs'."));
}  

This would help the functional and technical analysts and users to analyse the schema and tables related to sub-modules and modules, as it retrieves all the tables from each configuration.

Following are few configuration files from Dynamics AX 2009. The names, labels, license and parent description helps in understanding the configuration to which module it belongs.

Few CRSE related configurations have been skipped from the above list.

The code has been tested in Microsoft Dynamics AX 2009.

There are approximately around 4584 tables in Microsoft Dynamics AX 2012. For more information download the complete list of tables from the link here http://www.microsoft.com/download/en/details.aspx?id=17093

References

http://msdn.microsoft.com/en-us/library/aa626961(v=ax.50).aspx

http://msdn.microsoft.com/en-us/library/aa499193(v=ax.50).aspx

http://www.microsoft.com/download/en/details.aspx?id=17093

http://stackoverflow.com/users/624368/10p

History

28 Jan 2012