How to configure Microsoft Dynamics AX to work with Microsoft FRx 6.7

Step 1: Validate the Microsoft Dynamics AX data before you install Microsoft FRx

Before you install Microsoft FRx, you must validate the Microsoft Dynamics AX data. To validate the data, follow these steps:

1. Verify that a fiscal period exists in Microsoft Dynamics AX for any date that contains posted transactions.
   
2. In Microsoft Dynamics AX, look for dimensions or dimension codes that contain special characters. If you find a dimension or a dimension code that contains special characters, you must change the dimension or the dimension code so that only letters and numbers are used.
   
   For more information about special characters, click the following article number to view the article in the Microsoft Knowledge Base:
   
   
   941691 The special characters that are not supported in Microsoft FRx
   
   
3. Check for any variable-length dimensions. Microsoft FRx cannot read variable-length dimensions. For example, you may have the following dimensions:
   • Region Dimension 10000 - Western US will be read as follows:
     
     10000
   • 1000 - Canada will be read as follows:
     
     1000_
     
   If you find variable-length dimensions such as these dimensions, you must change the dimensions to be the same length.
   
4. Check the LedgerTrans table for any invalid dates. For example, a date of 10/10/9999 in the table is an invalid date.
   

Step 2: Run the SQL scripts for FRx against the Microsoft Dynamics AX database

Note You must log on to Microsoft SQL Query Analyzer as the Microsoft Dynamics AX table owner in order to run these scripts.


New users, follow these steps:

1. Open and execute the 01a_CreateTables.sql script.
   
2. Open and execute the 02_CreateStoredProcs.sql script.
   
3. Open and execute the 03_CreateTrigger.sql script.
   
4. Open and execute the 04_CreateIndexes.sql script.
   

Existing users, follow these steps:

1. Open and execute the 01b_UpgradeScript.sql script.
   
2. Open and execute the 02_CreateStoredProcs.sql script.
   
3. Open and execute the 03_CreateTrigger.sql script.
   
4. Open and execute the 04_CreateIndexes.sql script.
   

These scripts are included in the FRx service pack. To download the latest service pack for FRx 6.7 for Microsoft Dynamics AX, visit the following Microsoft Web site:Note The 01b_UpgradeScript.sql and 02_CreateStoredProcs.sql scripts add a database role that is named FRx_Role to all Microsoft FRx schema objects that are in the database. Existing permissions to the Public role are revoked when you run the scripts. All Microsoft FRx users must be added to this role before the users can run the Microsoft Dynamics AX Dimension Wizard or Microsoft FRx. Existing Microsoft FRx users cannot run the Microsoft Dynamics AX Dimension Wizard or Microsoft FRx until the users are added to this role.

Step 3: Import the Frxmetadata.xpo file into Microsoft Dynamics AX

1. Log on to Dynamics AX.
   
2. Click the Application Object Tree (AOT) icon on the Microsoft Dynamics AX toolbar.
   
3. Click the Import icon.
   
4. Click Browse, and then locate the Frxmetadata.xpo file. Click OK.
   
5. Click File, point to Open and then click Application Object Tree.
   
6. Expand Jobs, right-click FRxMetaData, and then click Open.
   
7. Select a location in which to save the FRxVirtualMap.xml file, and then click Save.
   
   Note In versions of FRx earlier than Service Pack 8 the job was named DumpVirtualCompanyMap. Make sure that you run the correct job for the correct service pack level if you have old jobs that must be removed.
   

Step 4: Run the Microsoft FRx Dimension Wizard

1. Start the Microsoft FRx Dimension Wizard. You must enter values in the Data source type and Server name boxes. Then, select the Database and Company values. Click Next.
   
2. Click Advanced, click Metadata, and then click Next.
   
3. Click Browse, and then select the FRxVirtualMap.xml file. If the configuration is correct, you receive a message that the file is ready for processing. Click Process.
   
4. When you receive a message that states that the process is complete, click Next.
   
5. Click Standard, and then click Dimension Structures. Click Next.
   
6. A list of the dimensions and the default length of the dimensions appears. If you must increase the dimension length, type a new value in the New Length column. Click Next.
   
7. Click New Task, click Dimension Structures, and then click Next.
   
8. Click New to configure the dimension structure. In the Available dimensions column, select the dimensions that you want to report on, insert the dimensions into the Selected dimensions column, and then click Next.
   
9. Click Finish to complete the wizard.
   

Note For more information about the FRx Dimension Wizard, see the Help file. In the FRx program folder, open the "Microsoft Dynamics AX Dimension Wizard Help.chm" file.

Step 5: Configure Microsoft FRx Reporter

1. Install Microsoft FRx on the server or on a workstation.
   
   For more information about how to install Microsoft FRx 6.7, click the following article number to view the article in the Microsoft Knowledge Base:
   
   
   940196 How to install Microsoft FRx 6.7
   
   
2. Install the latest Microsoft FRx 6.7 service pack.
   
   
3. Create an ODBC connection for Microsoft FRx Reporter. To do this, follow these steps:
   
   1. In Control Panel, open the Administrative Tools item, and then click Data Sources (ODBC).
      
   2. Click the System DSN tab.
      
      
      
      Note The ODBC connection must have the same name on all the computers that are running Microsoft FRx.
   3. Click Add, click SQL Server, and then click Finish.
      
   4. In the Name box, type FRx. In the Description box, type FRx AX. In the Server box, click the server that hosts the Dynamics AX database, and then click Next.
      
   5. If you use SQL Server Authentication, enter the user name and the password for the sa account, and then click Next.
      
      Note Windows Authentication is also supported.
      
   6. Click to select the Change the default database to check box, click the Dynamics AX database, and then click Next.
      
   7. Make sure that the Use ANSI quoted identifiers check box and the Use ANSI nulls, paddings and warnings check box are selected, and then click Next.
      
   8. Click Next.
      
   9. Click Test, and then click Finish.
      

Step 6: Enter the registration keys in Microsoft FRx

1. Start Microsoft FRx Reporter, and then click Cancel in the logon window.
   
2. Click Admin, and then click Organization.
   
3. Enter the registration keys. In the Accounting System list, make sure that Microsoft Dynamics AX is selected.
   
   
   Note If Microsoft Forecaster is not an available accounting system, click the following article number to view the article in the Microsoft Knowledge Base:
   938573 How to install Microsoft Forecaster 7.0 DirectLink and how to configure DirectLink for Microsoft FRx
   
   

Step 7: Set up a company in Microsoft FRx

1. Start Microsoft FRx Designer.
   
2. Click Cancel in the logon window.
   
3. Click Company, and then click Information.
   
4. Click New.
   
5. In the FRx company code box, type a short code. In the Company name box, type the name of your company.
   
6. Verify that Microsoft Dynamics AX is selected in the Accounting system box.
   
7. On the FRx System Information tab, click the appropriate value in the Amount and date format list.
   
8. Click the System Specific Information tab.
   
9. Select the ODBC connection that was created in the "Step 5: Configure Microsoft FRx Reporter" section.
   
   
10. In the Dimension Structure box, type the name of the dimension structure that was created in step 8 of the "Step 4: Run the Microsoft FRx Dimension Wizard" section.
    
    
11. Leave the COA expiration box blank.
    
12. Click Save, and then click Set as Default.
    
13. Click Close to close the Company Information window.
    

Step 8: Set up users to access Microsoft FRx

1. Start SQL Server Management Studio.
2. Expand Security.
3. Right-click Logins, and then click New Login.
   
4. Type a user name. If you use Integrated Windows Authentication, type the domain name and the user name.
   
5. Click User Mapping, and then click to select the Microsoft Dynamics AX database. In the Database role membership section, select db_datareader, public, and FRx_Role.
6. Click OK.

Step 9: Select a method for keeping Microsoft FRx data updated

Microsoft FRx must continually read the transactions that are posted in Microsoft Dynamics AX to detect any new accounts or any new dimensions that have new postings. To keep this data up to date, use one of the following methods:

• In the Company Information window, click to select the Rebuild Chart of Accounts check box on the System Specific Information tab.
  
• Run the Microsoft Dynamics AX Dimension Wizard, and then click Refresh to manually rebuild this information daily or when the information is required.
  
• Schedule a SQL job to run nightly. To do this, use the following SQL statement:

  Exec csp_BuildAllAccountCodes