CREATING AN OLAP CUBE IN MICROSOFT DYNAMICS AX 2009

OLAP cubes can be managed and modified directly in Microsoft Dynamics AX 2009. This is done via the Perspectives node in the Data Dictionary in the AOT. In addition to the Perspectives node in the AOT, there are properties on other AOT objects that are specific to OLAP configuration.
Open the Properties window for a table. You will see the following properties:
• AnalysisVisibility
• AnalysisSelection
• TypicalRowCount
• IsLookup
• AnalysisDimensionType
• Singular Label
• AnalysisIdentifier

These are all OLAP related properties for the table. Here are further descriptions of some of these properties:

Property	Description
IsLookup	Determines whether to generate a consolidated dimension or a distinct dimension. You can specify one of the following values: es – Indicates that attributes from the table are to be consolidated into the parent dimension (Star schema – see balloon below). o – Indicates that a separate dimension is to be generated for the table (Snowflake schema – see balloon below).
AnalysisIdentifier	Specifies the table field that is referenced as the dimension instance identifier.
AnalysisDimensionType	Determines the type of dimension created based on the IsLookup property setting. You can specify one of the following values: • IsLookup property set to Yes: o Auto – Specifies that the table may contain factual as well as dimensional data. The BI Wizard will extract dimensional data and create dimensions and attributes while factual data will be extracted to create measures. One child dimension is created with attributes from the parent table. o MasterInner – Specifies an inner (full) join to create relationships with this table to the child table. Each record combination for this table and the child table are generated in the dimension.One child dimension is created with attributes from the parent table. o MasterLeftOuter – Specifies a left outer join to create relationships with this table to the child table. Dimensions will have additional attributes based on values in this table that can also be empty. One child dimension is created with attributes from the parent table. o Transaction – Specifies that the table should strictly be used to generate factual data (measures). This setting should be used when a table only contains transactional data. One child dimension is created containing only enumeration fields from the table. • IsLookup property set to No: o Auto – Specifies that the table may contain factual as well as dimensional data. The BI Wizard will extract dimensional data and create dimensions and attributes while factual data will be extracted to create measures. One parent and child dimension is created. o MasterInner – Not applicable. Same as Auto. o MasterLeftOuter – Not applicable. Same as Auto. o Transaction – Specifies that the table should strictly be used to generate factual data (measures). This setting should be used when a table only contains transactional data. One child dimension is created containing only enumeration values from the table.
SingularLabel	Specifies the caption for the dimension generated for the table. If you do not specify a value for the SingularLabel property, the Label property setting is used.

 

As well as tables, OLAP properties are found on table fields. On most table fields, you will see the following properties:

• AnalysisVisibility

• AnalysisTotaling

•AnalysisLabel

•AnalysisDefaultTotal

•AnalysisUsage

These are all OLAP related properties for the table field. Here are further descriptions of some of these properties:

Property	Description
AnalysisLabel	Specifies the label for the field when it is used as a dimension attribute or measure. Only specify a label for this property when the label supplied for the Label property is not appropriate.
AnalysisUsage	Identifies the role of the field in the cube. You can specify one of the following values. Attribute – The field is a dimension attribute. Measure – The field is a measure. Both – The field is both a dimension attribute and a measure. None – The field is not a dimension attribute and not a measure. Auto – The value of the AnalysisUsage property for the extended data type or enumeration that the field is based on is to be used.
AnalysisDefaultTotal	Determines the aggregate function for a measure. Use this property when AnalysisUsage is set to Measure. You can specify one of the following values. Sum – Returns the sum of all the values in a set. Count – Returns the number of non‐null items in a set. CountDistinct – Returns the number of distinct non‐null items in a set. Min – Returns the minimum value in a set. Max – Returns the maximum value in a set. None – No aggregate function is applied. Auto – Applies to derived extended data types. The value of the AnalysisUsage property for the parent extended data type is to be used.

Even deeper than table fields, the same OLAP properties can be defined on all Extended Data Types (except Enumerations). Enumerations only have one OLAP property – AnalysisUsage – which can only be defined as Attribute or None.
and now let’s create our new cube in Dynamics ax 2009 as the following:

1. First a new perspective is needed. In the AOT, navigate to Data Dictionary > Perspectives.

2. Right‐click Perspectives node and select New Perspective.

3. Open the properties window for the new perspective.

4. Give the perspective a Name (SalesAnalysis), Label (SalesAnalysis) and set its Usage to OLAP.

5. Now tables need to be added to the perspective. Open a new AOT window, and navigate to Data Dictionary > Tables node. Drag the following tables onto the perspective:

• AddressCounty
• AddressState
• CustGroup
• CustInvoiceJour
• CustInvoiceTrans
• CustTable
• InventItemGroup
• InventTable

6. Save the perspective.

 

Next, you will specify the measures and dimensions for the cube. To do this, you need to set BI properties on each table included in the SalesAnalysis perspective.

To set BI properties on the AddressCounty table

1. In the AOT, expand the node for the SalesAnalysis perspective, and then expand the Tables node.

2. Select the AddressCounty table.

3. In the Properties sheet, specify the following values.

Property	Value
IsLookup	Yes
AnalysisIdentifier	Name

 

4. In the AOT, expand the node for the AddressCounty table, expand the Fields node, and then select the Name field.

5. In the Properties sheet, specify the following values.

Property	Value
AnalysisLabel	County
AnalysisUsage	Attribute

To set BI properties on the AddressState table

1. In the AOT, select the AddressState table in the SalesAnalysis perspective.

2. In the Properties sheet, specify the following values.

Property	Value
IsLookup	Yes
AnalysisIdentifier	Name

3. In the AOT, expand the node for the AddressState table, expand the Fields node, and then select the Name field.

4. In the Properties sheet, specify the following values.

Property	Value
AnalysisLabel	State
AnalysisUsage	Attribute

To set BI properties on the CustGroup table

1. In the AOT, select the CustGroup table in the SalesAnalysis perspective.

2. In the Properties sheet, specify the following values.

Property	Value
SingularLabel	Customer group
IsLookup	Yes
AnalysisIdentifier	Name

3. In the AOT, expand the node for the CustGroup table, expand the Fields node, and then select the Name field.

4. In the Properties sheet, specify the following values.

Property	Value
AnalysisLabel	Name
AnalysisUsage	Attribute

To set BI properties on the CustInvoiceJour table

1. In the AOT, select the CustInvoiceJour table in the SalesAnalysis perspective.

2. In the Properties sheet, specify the following values.

Property	Value
SingularLabel	Customer invoice
IsLookup	No
AnalysisDimensionType	Transaction

3. In the AOT, expand the node for the CustInvoiceJour table, expand the Fields node, and then select the DueDate field.

4. In the Properties sheet, specify the following values.

Property	Value
AnalysisLabel	Due date
AnalysisUsage	Attribute

5. In the AOT, select the InvoiceAmount field.

6. In the Properties sheet, specify the following values.

Property	Value
AnalysisUsage	Measure
AnalysisDefaultTotal	Sum

To set BI properties on the CustInvoiceTrans table

1. In the AOT, select the node for the CustInvoiceTrans table in the SalesAnalysis perspective.

2. In the Properties sheet, specify the following values.

Property	Value
SingularLabel	Customer invoice transaction
IsLookup	No
AnalysisDimensionType	Transaction

3. In the AOT, expand the node for the CustInvoiceTrans table, expand the Fields node, and then select the InvoiceDate field.

4. In the Properties sheet, specify the following values.

Property	Value
AnalysisLabel	Invoice date
AnalysisUsage	Attribute

5. In the AOT, select the LineAmount field.

6. In the Properties sheet, specify the following values.

Property	Value
AnalysisLabel	Revenue
AnalysisUsage	Measure
AnalysisDefaultTotal	Sum

7. In the AOT, select the Qty field.

8. In the Properties sheet, specify the following values.

Property	Value
AnalysisLabel	Quantity
AnalysisUsage	Measure
AnalysisDefaultTotal	Sum

9. In the AOT, select the Remain field.

10. In the Properties sheet, specify the following values.

Property	Value
AnalysisLabel	Remaining units
AnalysisUsage	Measure
AnalysisDefaultTotal	Sum

To set BI properties on the CustTable table

1. In the AOT, select the CustTable table in the SalesAnalysis perspective.

2. In the Properties sheet, specify the following values.

Property	Value
IsLookup	No
AnalysisIdentifier	Name

3. In the AOT, expand the node for the CustTable table, expand the Fields node, and then select the Name field.

4. In the Properties sheet, specify the following values.

Property	Value
AnalysisLabel	Customer
AnalysisUsage	Attribute

To set BI properties on the InventItemGroup table

1. In the AOT, select the node for the InventItemGroup table in the SalesAnalysis perspective.

2. In the Properties sheet, specify the following values.

Property	Value
IsLookup	Yes
AnalysisIdentifier	Name

3. In the AOT, expand the node for the InventItemGroup table, expand the Fields node, and then select the Name field.

4. In the Properties sheet, specify the following values.

Property	Value
AnalysisLabel	Item group
AnalysisUsage	Attribute

To set BI properties on the InventTable table

1. In the AOT, select the InventTable table in the SalesAnalysis perspective.

2. In the Properties sheet, specify the following values.

Property	Value
IsLookup	No
AnalysisIdentifier	ItemName

3. In the AOT, expand the node for the InventTable table, expand the Fields node, and then select the ItemName field.

4. In the Properties sheet, specify the following values.

Property	Value
AnalysisLabel	Item
AnalysisUsage	Attribute

Generating a BI Project

Now that you have created a perspective and specified the measures and dimensions for the cube, you will generate a BI project so that you can work with the cube in BIDS. After generating the BI project, you will view several of the cube objects that were generated.

To generate a BI project

1. On the Microsoft Dynamics AX menu, point to Tools, point to Business Intelligence (BI) tools, and then click BI project generation options. The BI project generation options form displays.

2. Click the General tab.

3. In the Datasource type field, specify the type of database you are using.

4. Select the Enable logging check box, and then specify a path and file name for the log file.

5. Click the Time Dimensions tab.

6. Select the Use the standard calendar check box, and specify start and end dates that are appropriate for the data that you are analyzing.

Note

The time interval that you specify for the time dimension should align with existing or expected data for the tables that are used in the cube.

7. Select the following check boxes in the Time periods list for the standard calendar.

o Days

o Year

o Quarter

o Month

8. Click the Translations tab.

9. Select the Create metadata translations check box, and then select the check box next to the following languages.

o English (United States)

o French (Standard)

10. Click the Generate BI project button. The Generate a Business Intelligence project form is displayed.

11. In the Folder field, specify a location for the project. You can click the folder icon to browse and select a folder.

12. In the Project name field, type SalesAnalysis.

13. Select the Open generated project check box. This indicates that the project is to be opened in BIDS after it is generated.

Note

For this walkthrough, it is assumed that Microsoft Dynamics AX and BIDS are installed on the same computer.

14. Select the SalesAnalysis perspective. Be sure that this is the only perspective selected.

15. Click OK. This generates a BI project and opens the BI project in BIDS.

To view cube objects in the generated project

1. In Visual Studio, open Solution Explorer.

2. Expand the Data Sources node. A data source that connects to the Microsoft Dynamics AX OLTP database is displayed.

A data source is used to source and refresh cube data

3. Expand the Data Source Views node, and then double-click SalesAnalysis.

A data source view provides a unified view of the tables and their relationships.

Note: You should verify that the OLTP connection is valid.

4. In Solution Explorer, expand the Cubes node, and then double-click SalesAnalysis.cube to display Cube Designer.

Cube Designer lets you view and edit various properties of a cube. There are several tabs that display different views of the cube. For example, click the Dimension Usage tab to display the mappings between dimensions and measure groups. Click the Translations tab to view the translations that exist for the cube. TheSalesAnalysis cube has two translations, English (United States) and French (France).

5. In Solution Explorer, expand the Dimensions node to view the dimensions for the cube.

6. Double-click the Customers dimension.

The Customers dimension consists of attributes from several tables. These include CustTable, CustGroup, AddressState, and AddressCounty. Hierarchies were created based on the relationships between these tables.

7. In Solution Explorer, double-click the Items dimension.

The Items dimension consists of attributes from several tables. These include InventTable, InventItemGroup, and AddressState. Hierarchies were created based on the relationships between these tables.

8. In Solution Explorer, double-click the Time dimension.

The Time dimension includes attributes for all possible levels. The hierarchies that are in the Time dimension depend on the hierarchy levels that were selected when specifying project generation options in Microsoft Dynamics AX.

9. Review the remaining dimensions for the cube.

The Company, Cost Center, Department, and Purpose system dimensions are automatically added to the generated cube.

Deploying a Cube in a BI Project

Next, you will deploy the cube the BI project. During deployment, cube objects are materialized and processed in an instance of SQL Server Analysis Services. When a cube is processed, data from the data source is extracted and mapped into the cube objects.

To deploy the cube

· In Solution Explorer, right-click the SalesAnalysis project, and then click Deploy.

Browsing Cube Data

Now that the SalesAnalysis cube has been deployed and processed, you can browse the cube data in the BI project. The following procedure explains how to browse the cube data.

To browse the cube data

1. In Visual Studio, open the BI project that you want to browse.

2. In Solution Explorer, double-click SalesAnalysis.cube.

3. Click the Browser tab.

4. Expand the Measures node, expand Customer invoice node, right-click Invoice amount, and then click Add to Data Area.

5. Expand the Time node, right-click the Year - Quarter - Month - Days hierarchy, and then click Add to Column Area.

6. Expand the Customers - Invoice account node, right-click the Customers - Invoice account.Customer groups - Customers hierarchy node, and then click Add to Row Area.

7. Browse the data. You can expand and collapse rows and columns in the table. You can modify the rows and columns that display in the table or add other dimensions to further slice the data.

Creating the Master Company Reporting Currency Dimension

In order to display the KPIs for a cube in a Business Overview Web part in Enterprise Portal, the cube must contain a Master Company Reporting Currency dimension. This dimension facilitates the reporting of financial measures in all the currencies used by the companies implemented in Microsoft Dynamics AX. In Microsoft Dynamics AX 2009, this dimension is not automatically created for you when you generate a BI project for a cube. You must manually create this dimension.

To create a mapping for the Master Company Exchange Rate

1. In Cube Designer, click the Dimension Usage tab.

2. Click the ellipsis button (…) that appears at the intersection of the Time dimension and the Master company exchange rate measure group. The Define Relationship dialog box is displayed.

3. For the Select relationship type field, select Regular.

4. For the Granularity attribute field, select Days.

5. In the relationship table, select DATEKEY in the Measure Group Columns column.

6. Click OK.

To create the Master Company Reporting Currency named query

1. In Solution Explorer, double-click SalesAnalysis located in the Data Source Views folder.

2. Click the New Named Query button.

3. In the Name field, type Master Company Reporting Currency.

4. Replace the empty SQL query with the following query:

  SELECT RC.CURRENCYCODE, RC.ISOCURRENCYCODE, RC.CURRENCYNAME, RC.SYMBOL, DA.ISPIVOT

  FROM (SELECT CURRENCYCODE, ISOCURRENCYCODE, CURRENCYNAME, SYMBOL

    FROM (SELECT CURRENCYCODE, CURRENCYCODEISO AS ISOCURRENCYCODE, TXT AS CURRENCYNAME, SYMBOL

    FROM CURRENCY AS A) AS BICURRENCYDIMENSION

      WHERE (CURRENCYCODE IN

       (SELECT CASE WHEN UPPER(CURRENCYCODE) IS NULL THEN '' ELSE UPPER(CURRENCYCODE) END AS CURRENCYCODE

          FROM COMPANYINFO))) AS RC INNER JOIN

            (SELECT D.ID, D.NAME, D.ISVIRTUAL, (CASE WHEN C.CURRENCYCODE IS NULL THEN '' ELSE UPPER(C.CURRENCYCODE) END)

          AS CURRENCYCODE, (CASE WHEN C.SECONDARYCURRENCYCODE IS NULL THEN '' ELSE UPPER(C.SECONDARYCURRENCYCODE)

           END) AS SECONDARYCURRENCYCODE, (CASE WHEN

          (SELECT TOP 1 UPPER(EXCHANGERATECOMPANY) AS EXPR1

             FROM [DBO].BICONFIGURATION AS B) = UPPER(D .ID) THEN 0 ELSE 1 END) AS ISPIVOT

             FROM DATAAREA AS D LEFT OUTER JOIN

             COMPANYINFO AS C ON C.DATAAREAID = D.ID) AS DA ON RC.CURRENCYCODE = DA.CURRENCYCODE

UNION

   SELECT DISTINCT N'Local' AS [Local 1], N'Local' AS Local, N'Local' AS [Local 3], N'Local' AS [Local 2], 1 AS [Local 4]

     FROM (SELECT     CURRENCYCODE, CURRENCYCODEISO AS ISOCURRENCYCODE, TXT AS CURRENCYNAME

     FROM CURRENCY AS A) AS BICURRENCYDIMENSION_1

5. Click OK.

To add the Master Company Reporting Currency dimension to the cube

1. In Solution Explorer, right-click Dimension and then click New Dimension. Click Next.

2. Use the Dimension Wizard to add a dimension called Master Company Reporting Currency.

Note: The options that you select depend on which version of BIDS you use.

3. In Solution Explorer, double-click Master Company Reporting Currency.dim located in the Dimensions folder.

4. Click the Dimension Structure tab if it is not already displayed.

5. In the Attributes pane, select the Master Company Reporting Currency dimension.

6. Set the ErrorConfiguration property to (custom).

7. Expand the ErrorConfiguration node, and then set the KeyDuplicate, KeyNotFound, and NullKeyNotAllowed properties to IgnoreError.

8. Set the UnknownMember property to Visible, and then save your changes.

To add the Master Company Reporting Currency dimension

1. In Solution Explorer, double-click SalesAnalysis.cube.

2. Click the Dimension Usage tab.

3. On the Dimension Usage toolbar, click Add Cube Dimension.

4. Select Master Company Reporting Currency, and then click OK.

To deploy the cube

· In Solution Explorer, right-click the SalesAnalysis project, and then click Deploy.

 

Set the .NET Business Connector proxy account

Some components require that the .NET Business Connector be configured to connect to Microsoft Dynamics AX with a proxy account. The use of a proxy enables the .NET Business Connector to connect on behalf of Microsoft Dynamics AX users when authenticating with an AOS instance.

The Business Connector proxy is a Microsoft Windows domain account that is configured from the initialization checklist, or in the Administration > Setup > Security > System accounts form.

Work with a system administrator to create a new account for the Business Connector before you install it. We recommend that the account be set up as follows:

• Must be a Windows domain account

• Must be a dedicated account (used only by Business Connector)

• Must have a password that does not expire

• Must not have interactive logon rights

• Must not be a Microsoft Dynamics AX user.

 

Important

If a malicious user learns the Business Connector proxy credentials (name and password), that user could gain unauthorized access to sensitive information, and potentially damage the Microsoft Dynamics AX application. For this reason, only Microsoft Dynamics AX administrators should know the proxy credentials.

To set up and configure the Business Connector proxy, you must perform the following steps.

1. Create the proxy account in Active Directory.

2. Add the proxy account to the IIS local Windows group.

3. Configure the IIS application pool.

4. Install the .NET Business Connector.

5. Specify the Business Connector proxy user in Microsoft Dynamics AX.

Create the proxy account in Active Directory

---

1. Create a unique user in Active Directory in the form domain\username, for example, domain\bcproxy. This user must not have the same name as an existing Microsoft Dynamics AX user. For the procedure to add a new user, see the Active Directory documentation.

2. Assign a password to the user.

3. Select the Password does not expire option.

4. Select the No interactive logon rights option.

5. Close Active Directory.

Add the proxy account to the IIS local Windows group

---

For Web applications, you must add the Business Connector proxy account to the IIS local Windows group. If you are using Windows SharePoint Services, you must also add the account to the Windows SharePoint Services local Windows group.

1. Open the Computer Management application (Start > Administrative Tools > Computer Management).

2. Expand the Groups folder under Local Users and Groups.

3. Add the Business Connector proxy account to the following groups:

   • IIS_WPG (IIS Worker Process Group)

   • STS_WPG (STS Worker Process Group), if running Windows SharePoint Services

Specify the Business Connector proxy user in Microsoft Dynamics AX

---

1. Start Microsoft Dynamics AX (Start > All Programs > Microsoft Dynamics > Microsoft Dynamics AX).

2. Open the System service accounts form: Administration > Setup > Security > System service accounts.

3. In the Business Connector Proxy section of the form, enter the alias and the domain of the user.

4. Click OK.

INTRODUCTION TO CUBES AND MULTIDIMENSIONAL MODELS

An OLAP (OnLine Analytical Processing) cube is a multi‐dimensional database (ie. more than 2 dimensions) that allows fast analysis of data. OLTP databases, such as the Microsoft Dynamics AX 2009 database, are excellent for storing data, but not very efficient at analyzing large amounts of data. OLAP cubes are designed with analysis in mind, organizing data in a meaningful way, so that it can be accessed and analyzed quickly.
The term Cube comes from the most basic multi‐dimensional model of an OLAP database, ie. a three‐dimensional data structure. For example, Three dimensions of Items, Customers and Time:

This is a simple concept of OLAP data: being able to see items sold to customers on specific dates (or times). Such an OLAP cube would allow very fast analysis of this data, with intelligent filtering. For example: All items sold to a specific customer in last fiscal year; All customers who purchased a specific item in last quarter; Months where a particular item did not sell; Weeks where a particular customer did not purchase; Zip codes where a particular item sold the most in the period after a catalog delivery.
This is just an example cube structure with three dimensions. OLAP cubes can have more than three dimensions, exponentially expanding their data analysis potential. For example, we could add Employee to the previous cube example. Then we could find: Which employees sold a particular item during its promotional period, and to which customers?
When queried, SQL Server Analysis Services “slices and dices” the cubes, to find the data it needs. For example, it slices along the Customer dimension at the position of a particular customer, slices along the Item dimension at the position of a particular item, and it is left with a linear result along on the Time dimension (to see the purchasing history of a particular Customer for a particular Item).

Let’s introduce some common multi‐dimensional model terminology, with examples of their representation in BIDS:
• Dimension – this can be described as a category within the data, which would be reported on. For example: Time, Items, Customers, Employees, Sites, Warehouses, etc. In our BIDS project, there are 12 dimensions, and these can be seen in the Solution Explorer:

• Member – this is one point on a dimension. For example, Wednesday or October on the Time dimension, Customer ABC on the Customer dimension, John Citizen on the Employee dimension. This can be seen in BIDS, only after the cube has been processed (ie. contains data, and is not just a model).
• Calculated member – this is a member that is defined at run time.
• Attribute – this is a complete collection of Members. For example, all the days of the week, or all the months of the year, are attributes on the Time dimension. This can be seen in BIDS using the Dimension Designer. Here is are the attributes for the Time dimension:

• Attribute relationship – this is when one attribute relates to another. For example, the attribute Months on the Time dimension, is related to the attribute Quarters on the Time dimension.
• Tuple – this is a coordinate in the multi‐dimensional space. For example, in our previous three‐dimensional example, ([Item: ABC], [Customer: 111], [Time: October‐2008]) would be a tuple. Wild cards can also be used in tuples, and this is done by simply not including their values in the tuple. For example, ([ABC]) would represent sales of item ABC to all customers over all time. This would also represent a “slice” in the three‐dimensional model, along the ABC member on the Item dimension.

• Dimension hierarchies – these are used when a dimension has different groups of members that could be reported on. For example, on the Time dimension, some analysis may be done by year. Others may be done by Month, by Week, by Quarter, by Date, or by Day of the Week. To facilitate this, different dimension hierarchies can be defined. For example: Year, Quarter, Month, Week, Day; or, Year, Half‐Year, Trimester, Quarter, Month, Week, Date. This can be seen in the BIDS Dimension Designer only after the cube has been processed (since it relies on actual data values). Here are two examples of dimension hierarchies on the Time dimension. First, the hierarchy defined as “Years Quarters Months Weeks Days” (shown as design, then actual members):

Now the hierarchy defined as “FiscalYears FiscalHalfYears FiscalTrimesters FiscalQuarters FiscalMonths FiscalWeeks FiscalDate”:

Note that these hierarchies define different “scales” of members upon the same dimension. Another example would be a “Feet Inches” hierarchy and a “Meters Centimeters” hierarchy on the same Distance dimension.

• Measures – this describes the value, from a fact table, at a particular tuple. For example, at the tuple defined by ([ABC], [111], [October‐2008]) there could exist multiple values, such as quantity sold, amount paid, quantity returned, quantity delivered, etc. To define exactly which value is being analyzed, we use measures. Measures are stored in Measure groups. In BIDS, measures and measure groups can be seen in the Cube designer, like the Amount Settled measure, in the Customer Transactions measure group, shown below (with its property window):

• Aggregation function – this is a function used on measures. For example, sum, count, average. In the previous example, you can see that the Amount Settled measure is using the Sum aggregate function, meaning its values will be summed when calculating total amounts. Here is another example. This time we look at the Customer transactions Count measure, which uses the Count aggregation function. So its values will be counted, not summed.

SYNCHRONIZE THE OLAP Dynamics AX DATABASE

1. Make a backup of the entire OLAP database.
2. Open the Microsoft Dynamics AX 2009 client.
3. Open the OLAP Administration form (Administration > Setup > Business analysis > OLAP > OLAP Administration).
4. Click the OLAP servers tab.

5. Enter the name and a description of the server that is running Analysis Services. (This information may be already entered for you.)
6. Select the check box for the OLAP server.
7. Click the OLAP databases tab.

8. In the Database name field, enter Dynamics AX. (This information may be already entered for you.) By default, the OLAP database that stores the Microsoft Dynamics AX 2009 cubes is named Dynamics AX.
9. Select the check box for the OLAP database.
10. Click the Advanced tab.

11. Select the Synchronize OLAP Database with OLTP Schema check box.
12. Select the Enable logging check box. Data will be recorded in a log file. Specify an existing location to save the Log file to. You will need to refer to this log file when completing many of the following procedures.
13. Click Update Databases to synchronize the OLAP database with the schema from the OLTP database. This process may take some time to complete.

Hint: This synchronization will compare the schema in the OLTP database with the schema in the OLAP database. It will remove tables from the data source view if any of the tables do not exist in the OLTP database (due to configuration keys being turned off). It will also remove invalid dimensions, dimension attributes, measures and measure groups. It will NOT fix named queries that contain references to columns or tables that are not in the OLTP database – these must be fixed manually. Follow the next post to fix these issues.

Dynamics AX security model

Unlike other Microsoft server technologies, user security in Dynamics AX is not controlled in Active Directory. Instead, Dynamics AX implements its own security model to control access in the environment. This security model consists of:

• Licensing
• Configuration keys.
• Security keys

The following lists explain the constituent parts of the Dynamics AX security model:

• Licensing: Licenses are distributed by Microsoft for Dynamics AX or by vendors of third-party modules and features. Licensing can be modified in the License information form by going to Administration | Setup | System | License information, as shown in the following screenshot:

• Configuration Keys: The administrator can enable or disable certain features in Dynamics AX through configuration keys. Even though a security key can unlock a certain feature, it may still be hidden because a configuration key is not active. To enable or disable configuration settings in Dynamics AX, go to the Configuration form in Administration | Setup | System |
Configuration, as shown in the following screenshot:

• Security Keys: The administrator can control access to specific elements in Dynamics AX such as Forms, Tables, Menus, Buttons, Fields, Web menus, Web content in Dynamics AX through security keys. The administrator can control whether the environment will have a specific feature enabled or not. Security keys can be enabled or disabled for a specific user group in the User groups form located in Administration | Setup | User groups. After selecting the appropriate user group in User groups form, you can modify its permissions by selecting it then clicking on the Permissions button, as shown in the following screenshot:

Accessing the AIF web service

Now that the AIF web services were generated and the appropriate authentication methods have been specified, the next step is to verify that they can be accessed. In this section, we will cover the process of testing the web services that were created.
1. On the server in which the AIF web service extension was installed on, go to Administrative Tools | Internet Information Services (IIS) Manager.

2. In the IIS manager, collapse the web server instance node and navigate all the way to the AIF web service that was created when you installed the AIF web service extension.

3. In order to simplify the process of validating that the AIF web services are accessible, make the  MicrosoftDynamicsAXAif50 virtual directory browsing. To do this, select the MicrosoftDynamicsAXAif50 directory and under IIS, open Directory Browsing.

4. Once the Directory Browsing view opens, click on Enable on the right pane to enable Directory Browsing.

5. Now that browsing has been enabled, right-click on the MicrosoftDynamicsAXAif50 node on the left, a go to Manage Application |Browse to open the site.

6. In the directory list page view, click on any *.svc to view the service and the available methods.

Now that the AIF web services have been validated to be accessible, developers can access the web services to exchange documents with Dynamics AX