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.

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.

Shrink SQL File

A. Shrinking a data file to a specified target size

The following example shrinks the size of a data file named DataFile1 in the UserDB user database to 7 MB.

USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO

B. Shrinking a log file to a specified target size

The following example shrinks the log file in the AdventureWorks2008R2 database to 1 MB. To allow the DBCC SHRINKFILE command to shrink the file, the file is first truncated by setting the database recovery model to SIMPLE.

USE AdventureWorks2008R2;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2008R2_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY FULL;
GO

C. Truncating a data file

The following example truncates the primary data file in the AdventureWorks2008R2 database. The sys.database_files catalog view is queried to obtain the file_id of the data file.

USE AdventureWorks2008R2;
GO
SELECT file_id, name
FROM sys.database_files;
GO
DBCC SHRINKFILE (1, TRUNCATEONLY);

D. Emptying a file

The following example demonstrates the procedure for emptying a file so that it can be removed from the database. For the purposes of this example, a data file is first created and it is assumed that the file contains data.

USE AdventureWorks2008R2;
GO
-- Create a data file and assume it contains data.
ALTER DATABASE AdventureWorks2008R2
ADD FILE (
    NAME = Test1data,
    FILENAME = 'C:\t1data.mdf',
    SIZE = 5MB
    );
GO
-- Empty the data file.
DBCC SHRINKFILE (Test1data, EMPTYFILE);
GO
-- Remove the data file from the database.
ALTER DATABASE AdventureWorks2008R2
REMOVE FILE Test1data;
GO

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:

Dynamics AX: Generating an AIF web service

Now that we have configured the AIF web service in Dynamics AX, we can generate services right from Dynamics AX that will be available for use. Services can be created by developers but Dynamics AX 2009 comes pre-packaged with several services, depending on your licensing scheme. In this section, we will go over the process of generating services.

1. In Dynamics AX, services are specified in the AOT under the Services node.

2. To enable the use of the services in the AOT, go to Basic | Setup |Application Integration Framework | Services.

3. In the AIF Services form, click on the Refresh button. This may take a while because the form will query Dynamics AX for the available services.

4. To choose which services will be available as a web service, select the appropriate service and mark the Enable field.

5. Now that the desired services have been selected to be enabled, the next step
is to automatically generate the web services. To generate these services, click
on the Generate button.

Specifying the authentication method for an AIF web service

Since an AIF web service is a WCF service, all the same rules apply when it comes to specifying authentication methods and many other settings. Such settings allow
greater flexibility when customizing web services to enhance security, performance, and compatibility. When you generate a web service for the AIF, the default method
for authentication is basicHttpBinding. However, in most scenarios, the binding method for authentication should be wsHttpBinding. For more information on
wsHttpBinding, refer to http://msdn.microsoft.com/en-us/library/ms751418.aspx. To change the authentication binding method, we must edit the configuration
file of a web service that was created when the service was generated.

The following steps describe the process of editing the AIF web service configuration file:
1. In Dynamics AX, go to Basic | Setup | Application Integration Framework| Services to load the Services form.

2. Select the service to change the authentication binding method and click on the Configure button. This will load the Microsoft Service
Configuration Editor.

Hint: The Microsoft Service Configuration Editor comes with the Microsoft .NET 3.5 Framework SDK or Windows Server 2008 SDK. This must be
installed in order to properly edit the web service configuration. Although you can use a text editor to make modifications to the configuration file,
it is not best practice, nor is it recommended. The configuration editor ensures that configuration settings are properly formatted.

3. In the Microsoft Service Configuration Editor, collapse the Bindings node to view the current binding. Notice that the default binding method is
basicHttpBinding.

4. We will need to create a wsHttpBinding method for authentication. To create a new binding, right-click on the Bindings folder and click on New Binding
Configuration…. In the Create a New Binding window that pops up, select wsHttpBinding and click on the OK button.

5. A new binding method of type wsHttpBinding, will be created under the Bindings folder. In this example, we will rename it to wsHttpBindingAif.

6. Now that we have created an appropriate binding method for an AIF service, we must associate the binding to the service that was generated,
so that it may be used as a binding method. In the Services folder, collapse the appropriate service (for example: Microsoft.Dynamics.
IntegrationFramework.Service.CustomerService) and collapse the Endpoints folder. Select the listed endpoint, and change the Binding
property to wsHttpBinding. Then in the Binding Configuration property, select the wsHttpBindingAif that we created.

7. Now that the correct binding is set, close the Microsoft Service Configuration Editor and save the modifications that were made. To ensure
that the settings take immediate effect, open the Windows Command Prompt and run iisreset on the web server.

Setting the appropriate authentication binding method will ensure that developers, external servers, and end users will be able to access the service appropriately.
Additional methods can be implemented to ensure a stronger security implementation such as SSL.

Creating and configuring an AIF website

The processes of installing AIF already explained in another's spots in this blog such Up to this point, the installer will have created and installed the appropriate libraries. However, in Windows Server 2008 and 2008 R2, the permissions and website setup in Dynamics AX may not have completed. The following process is what is required, so that the AIF website is properly set up for use:
• Apply appropriate permissions
• Specify an AIF website
• Generate an AIF web service
• Specifying the authentication method for an AIF web service
• Accessing the AIF web service
Applying appropriate permissionsDuring the installation of the AIF web service extension in the Installing the AIF web service extension post, a Content directory was specified in step 3. That directory was created to store the AIF web service files. The installation wizard went ahead and automatically created a network share of the folder. However, due to imitations of the installers in a Windows Server 2008 or 2008 R2 environment, the permissions will not work. The AOS will need access to this directory in order to create, modify, or delete AIF services. To allow this functionality, we need to permit the AOS service account to be able to do this. The following steps outline this process:
1. In Windows Explorer, navigate to the Content directory that was specified in step 3 of the Installing the AIF Web Service Extension section (for example: C:\Program Files\Microsoft Dynamics AX\50).

2. Right-click on the AifWebServices folder and go to Properties.

2. Go to the Security tab and click on the Edit button to edit the permissions on this directory.

Hint: The installer originally created a group Microsoft Dynamics AX Web Service Administrators on the server and assigned the AOS service account as a member of that group.Regardless, you will still have to manually add the AOS service account and apply the appropriate permissions.
3. In the Permission for AifWebServices window, click on the Add… button and then add the AOS service account and click on the OK button.

4. Now we must specify the permission level for the AOS services account. In the Permissions for AifWebServices window, ensure that the newly added AOS service account is selected and allow Full Control. When complete,
click on the OK button to save the modifications.

Specifying an AIF websiteNow that we have applied the appropriate permissions to the AIF web service directory, the AOS will now be able to access and modify the directory and contents. This is necessary if you want to use AIF for web services. When services are created in AX, the AIF will be able to generate standard WCF web services and place them in the Content directory. In this section, we will cover the process of specifying this directory.
1. In Dynamics AX, go to Basic | Setup | Application Integration Framework | Web sites.

2. In the Web sites form, create a new record and specify the network share location of the AIF Content directory (it may be easier to go into the General tab and browse the folder manually).

Hint: The Web sites form automatically validates the directory upon creating the record. If the validation fails and the record cannot be created, verify that the permissions are correct and that the directory exists and is shared.

3. Now that the record with the AIF Content directory location has been provided, click on the Validate button to ensure that the AOS will be able to access and modify it appropriately. An Infolog window will display whether the validation was successful or not.

You can follow up links below for rest of this post:
• Generate an AIF web service
• Specifying the authentication method for an AIF web service
• Accessing the AIF web service

وصية الربيع بن خثيم لإبنه

نقلت من كتاب روضة العقلاء ونزهة الفضلاء...لأبن حبّان البستي...
هذا ما أوصي به الربيع بن خيثم
وعظ ابنه فقال يا بني اني قد وسمت لك وسما ووضعت لك رسما ان انت حفظته ووعيته وعملت به ملأت أعين الملوك وانقاد لك به الصعلوك ولم تزل مرتجى مشرفا يحتاج اليك ويرغب إلى ما في يديك فأطع اباك واقتصر على وصية ابيك وفرغ لذلك ذهنك واشغل به قلبك ولبك

اياك وهذر الكلام وكثرة الضحكك والمزا ح ومهازلة الإخوان فإن ذلك يذهب البهاء ويوقع الشحناء
وعليك بالرزانة والتوقر من غير كبر يوصف منك ولا خيلاء تحكي عنك
والق صديقك وعدوك بوجه الرضى وكف الأذى من غير ذلة لهم ولا هيبة منهم
وكن في جميع أمورك في أوسطها فإن خير الأمور أوساطها
وقلل الكلام وأفش السلام وامش متمكنا قصدا ولا تخط برجلك ولا تسحب ذيلك ولا تلو عنقك ولا ردائك ولا تنظر في عطفك ولا تكثر الالتفاف ولا تقف على الجماعات ولا تتخذ السوق مجلسا ولا الحوانيت متحدثا ولا تكثر المراء ولا تنازع السفهاء
فإن تكلمت فاختصر وإن مزحت فاقتصرواذا جلست فتربع وتحفظ من تشبيك أصابعك وتفقيعها والعبث بلحيتك وخاتمك وذؤابة سيفك وكثرة طرد الذباب عنك وكثرة التثاؤب والتمطى وأشباه ذلك مما يستخفه الناس منك ويغتمزون به فيك
وليكن مجلسك هاديا وحديثك مقسوما وأصغ إلى الكلام الحسن ممن حدثك بغير إظهار عجب منك ولا مسألة إعادة
وغض عن الفكاهات من المضاحك والحكايات ولا تحدث عن إعجابك بولدك ولا جاريتك ولا عن فرسك ولا عن سفيك
واياك وأحاديث الرؤيا فإنك إن أظهرت عجبا بشيء منها طمع فيها السفهاء فولدوا لك الأحلام واغتمزوا في عقلك

لا تصنع تصنع المرأة ولا تبذل تبذل العبدولا تهلب لحيتك ولا تبطنها وتوق(يعني احذر) كثرة الحف ونتف الشيب وكثرة الكحل وليكن كحلك غبا ولا تلح في الحاجات ولا تخشع في الطلبات
ولا تعلم أهلك وولدك فضلا عن غيرهم عدد مالك فإنهم إن رأوه قليلا هنت عليهم وإن كان كثيرا لم تبلغ به رضاهم
وأخفهم في غير عنف ولن لهم قي غير ضعف ولا تهازل أمتك
واذا خاصمت فتوقر وتحفظ من جهلك وتجنب عن عجلتك
وتفكر في حجتك وأر الحاكم شيئا من حلمك ولا تكثر الأشارة بيدك ولا تحفز على ركبتيك وتوق حمرة الوجه وعرق الجبين وإن سفه عليك فاحلم وإذا هدأ غضبك فتكلم
وأكرم عرضك وألق الفضول عنك وإن قربك سلطان فكن منه على حد السنان وإن أسترسل اليك فلا تأمن من انقلابه عليك
وارفق به رفقك بالصبي وكلمه بما يشتهي ولا يحملنك ما ترى من الطافه إياك وخاصته بك أن تدخل بينه وبين أحد من ولده وأهله وحشمه وإن كان لذلك منك مستمعا وللقول منك مطيعا فإن سقطه الداخل بين الملك وأهله صرعة لا تنهض وزلة لا تقال
وإذا وعدت فحقق وإذا حدثت فاصدق

ولا تجهر بمنطقك كمنازع الأصم ولا تخافت به كتخافت الأخرس
وتخير محاسن القول بالحديث المقبول وإذا حدثت بسماع فانسبه إلى أهله وإياك الأحاديث العابرة المشنعه التي تنكرها القلوب وتفق لها الجلود وإياك ومضعف الكلام مثل نعم نعم ولا لا وعجل عجل وما أشبه ذلك
ولا تكثر الاستسقاء على مائدة الملك
ولا تعبث بالمشاش ولا تعب شيئا مما يقرب اليك على مائدة بقلة خل أو تابل أو عسل
فإن السحابة قد صيرت لنفسها مهابة
ولا تمسك إمساك المثبور ولا تبذر تبذير السفيه المغرور
واعرف في مالك واجب الحقوق
وحرمة الصديق واستغن عن الناس يحتاجوا اليك
واعلم ان الجشع يدعو إلى الطبع والرغبة كما قيل تدق الرقبة ورب أكله تمنع أكلات
والتعفف مال جسيم وخلق كريم ومعرفة الرجل قدره تشرف ذكره
ومن تعدى القدر هوى في بعيد القعر
والصدق زين والكذب شين ولصدق يسرع عطب صاحبه أحسن عاقبة من كذب يسلم عليه قائله ومعاداة الحليم خير من مصادقة الأحمق ولزوم الكريم على الهوان خير من صحبة اللئيم على الإحسان ولقرب ملك جواده خير من مجاورة بحر طراد
وزوجة السوء الداء العضال ونكاح العجوز يذهب بماء الوجه
وطاعة النساء تزرى بالعقلاء
تشبه بأهل العقل تكن منهم وتصنع للشرف تدركه
وأعلم أن كل امريء حيث وضع نفسه
وإنما ينسب الصانع إلى صناعته والمرء يعرف بقرينه
وإياك وإخوان السوء فإنهم يخونون من رافقهم ويحزنون من صادقهم وقربهم أعدى من الجرب ورفضهم من استكمال الأدب واستخفار المستجير لؤم والعجله شؤم
وسوء التدبير وهن
والإخوان اثنان فمحافظ عليك عند البلاء وصديق لك في الرخاء فاحفظ صديق البلاء وتجنب صديق العافية فإنهم أعدى الأعداء ومن اتبع الهوى مال به الردى

ولا يعجبنك الجهم من الرجال ولا تحقر ضئيلا كالخلال فإنما المرء بأصغريه قلبه ولسانه ولا ينتفع به بأكثر من أصغريه
وتوق الفساد وإن كنت في بلاد الأعادي ولا تفرش عرضك لمن دونك
ولا تجعل مالك أكرم عليك من عرضك
ولا تكثر الكلام فتثقل على الأقوام
وامنح البشر جليسك والقبول ممن لاقاك
وإياك وكثرة التبريق والتزليق فإن ظاهر ذلك ينسب إلى التأنيث
وإياك والتصنع لمغازلة النساء وكن متقربا متعززا منتهزا في فرصتك رفيقا في حاجتك متثبتا في حملتك
والبس لكل دهر ثيابه ومع كل قوم شكلهم
واحذر ما يلزمك اللائمة في آخرتك ولا تعجل في امر حتى تنظر في عاقبته
ولا ترد حتى ترى وجه المصدر
ومنازعتك اللئيم تطمعه فيك
ومن أكرم عرضه أكرمه الناس وذم الجاهل إياك أفضل من ثنائه عليك ومعرفة الحق من أخلاق الصدق
والرفيق الصالح ابن عم
ومن أيسر أكبر
ومن افتقر احتقر
قصر في المقالة مخافة الإجابة
والساعي إليك غالب عليك وطول السفر ملالة
وكثرة المنى ضلالة وليس للغائب صديق
ولا على الميت شفيق وأدب الشيخ عناء وتأديب الغلام شقاء
والفاحش أمير والوقاح وزير والحليم مطية الأحمق
والحمق داء لا شفاء له
والحلم خير وزير والدين أزين الأمور
والسماجه سفاهة والسكران شيطان وكلامه هذيان
والشعر من السحر
والتهدد هجر والشح شقاء والشجاعة بقاء
والهدية من الأخلاق السرية
وهي تورث المحبة
ومن ابتدأ المعروف صار دينا
ومن المعروف ابتداء من غير مسألة
وصاحب الرياء يرجع إلى السخاء ولرياء بخير خير من معالنة بشر
والعادة طبيعة لازمة إن خير فخير وإن شرا فشر
ومن حل عقدا احتمل حقدا
ومراجعة السلطان خرق بالإنسان والفرار عار
والتقدم مخاطرة وأعجل منفعه إيسار في دعة
وكثرة العلل من البخل وشر الرجال الكثير الاعتلال
وحسن اللقاء يذهب بالشحناء
ولين الكلام من أخلاق الكرام
يا بني إن زوجة الرجل سكنة ولا عيش له مع خلافها فإذا هممت بنكاح امرأة فسل عن أهلها
فإن العروق الطيبة تنبت الثمار الحلوة
واعلم أن النساء أشد اختلافا من أصابع الكف
فتوق منهن كل ذات بذا مجبولة على الأذى
فمنهن المعجبة بنفسها المزرية ببعلها إن أكرمها رأته لفضلها عليه لا تشكر على جميل
ولا ترضى منه بقليل لسانها عليه سيف صقيل قد كشفت القحة ستر الحياء عن وجهها
فلا تستحي من إعوارها ولا تستحي من جارها مهارشة عقارة فوجه زوجها مكلوم
وعرضه مشتوم ولا ترعى عليه لدين ولا الدنيا ولا تحفظه لصحبة ولا لكثرة بنين حجابه
مهتوك وستره منشور وخيره مدفون يصبح كئيبا ويمسي عاتبا شرابه مر وطعامه غيظ
وولده ضياع وبيته مستهلك وثوبه وسخ ورأسه شعث
إن ضحك فواهن وإن تكلم فمتكاره نهاره ليل وليله ويل تلدغه مثل الحية العقارة
وتلسعه مثل العقرب الجرارة
ومنهن شفشليق شعشع سلفع ذات سم منقع وإبراق واختلاق تهب مع الرياح وتطير مع كل ذي جناح
إن قال لا قالت نعم وإن قال نعم قالت لا مولدة لمخازيه محتقرة لما في يديه تضرب له الأمثال
وتقصر به دون الرجال وتنقله من حال إلى حال حتى قلا بيته ومل ولده
وغث عيشه وهانت عليه نفسه وحتى أنكره إخوانه ورحمه جيرانه
ومنهن الورهاء الحمقاء ذات الدل في غير موضعها الماضغه للسانها الآخذة في غير شأنها
قد قنعت بحبه ورضيت بكسبه تنتشر الشمس ولما يسمع لها صوت
ولم يكنس لها بيت طعامها بائت وإناؤها وضر وعجينها حامض وماؤها فاتر ومتاعها
مزروع وماعونها ممنوع وخادمها مضروب وجارها محروب
ومنهن العطوف الودود المباركة
الولود المأمونه على غيبها المحبوبة في جيرانها المحمودة في سرها وإعلانها الكريمة
التبعل الكثيرة التفضل الخافضة صوتا النظيفة بيتا خادمها مسمن وابنها مزين وخيرها دائم
وزوجها ناعم موموقه مالوفه وبالعفاف والخيرات موصوفة
جعلك الله يا بني ممن يقتدي بالهدى ويأتم بالتقى ويجتنب السخط ويحب الرضى والله خليفتي عليك والمتولي لأمرك ولا حول ولا قوة إلا بالله العلي العظيم وصلى الله على محمد نبي الهدى وعلى آله وسلم تسليما كثيرا

Setting up AIF to use web services

In some cases, setting up a filesystem transport adapter, as we did in the previous post, can satisfy simple document exchange needs. However, most document exchange methods utilize web services to transfer documents. Most web services utilize the Simple Object Access Protocol (SOAP) to transfer XML messages.
For more information on web services and what they are, refer to: http://en.wikipedia.org/wiki/Web_service.
This post provides the process of how to set up AIF to exchange documents using web services. Much of the process is the same as using and setting up the filesystem adapter since we must also specify both local and external endpoints and channels. We can even reuse the settings we already created for
the filesystem adapter without having to reconfigure for web services. However, additional steps are required for specifically using web services as a means of document exchange.
Creating an AIF website
Before we install the AIF web service extension, a website in IIS must be created to successfully run the installer. This allows you to also determine which port the AIF web service will listen on. The process of creating a website for the AIF is no different than creating a generic website in IIS. For more information on creating websites in IIS,
refer to: http://technet.microsoft.com/en-us/library/cc772350(WS.10).aspx.
Installing the AIF web service extensionBy now you should be accustomed to the process of installing the extended components for Dynamics AX. The following steps will guide you through the process of installing the AIF integration component:
1. Run the Microsoft Dynamics AX Setup wizard to add new components. In the Add or modify components screen of the wizard, mark the AIF Web services checkbox, as shown in the following screenshot and then click on the Next button.

2. In the following screen of the wizard, specify the password for the .NET Business Connector, and then click on the Next button when complete.

3. In the next section, you can select which website you want to install the AIF web service component into. Select the website that you recently created the AIF web service for. Additionally, you have the option to specify the application pool and virtual directory name if desired, but its not required.
When the parameters have been set with the appropriate settings, click on the Next button.

Hint:
During this step, the installer prepares to update Dynamics AX with the settings provided. However, this may not fully complete in Windows Server 2008 or 2008 R2.
4. In the following screen, you ensure that the AOS service account is provided in order for the wizard to properly assign permissions to allow the AOS to access the AIF web service.

5. In the following step, you are prompted to complete the installation of the AIF by clicking on the Finish button.

6. Once installed, you will be prompted with the final screen. The final screen will display the result of the installation of the Dynamics AX 2009 AIF system. If the installation was successful, you will see a green box next to
the installed component. Otherwise, if the box is orange or red, you will want to open the log file after you close the wizard by marking the checkbox at the bottom. It is recommended that IIS be restarted after AIF has been
successfully installed.

If any warnings or errors appeared during the installation of the AIF, review the log file that was generated to find and isolate the issue. In Windows Server 2008 and 2008 R2, it is possible that permissions were not appropriately set on folders that were generated in Dynamics AX. Additionally, the website for the AIF may have not
been updated in Dynamics AX. In the following Link, we will cover the process to manually set these up.

Managing and troubleshooting AIF document exchanges

We will cover the process of common management and troubleshooting tasks on the AIF.
Managing AIF document exchanges
In Dynamics AX, we can check document logs, view document messages, or manually import messages. We can also peak at which documents are in the pipeline
for Dynamics AX to process. This can all be accomplished using the AIF Queue manager form. To access the Queue manager form in Dynamics AX, go to Basic |
Periodic | Application Integration Framework | Queue manager.

To view a history of document exchanges in Dynamics AX, go to Basic | Periodic | Application Integration Framework | Document history. The Document history
form contains detailed information on documents and how they are to be processed.

Methods for troubleshootingWhen attempting to diagnose issues with the AIF, there are a couple of methods at your disposal that will assist in isolating issues. The following is a list of possible
methods:
• Consult the AIF Exception Log
• Consult the Windows Event Log
• Consult endpoint's logs
The AIF exception log can be accessed in Dynamics AX by going to Basic | Periodic | Application Integration Framework | Exceptions.

For additional AIF troubleshooting tips refer to: http://technet.microsoft.com/en-us/library/aa548693.aspx

Managing and troubleshooting AIF document exchanges

For additional AIF troubleshooting tips refer to: http://technet.microsoft.com/en-us/library/aa548693.aspx

Saber Mahdy

Labels

CREATING AN OLAP CUBE IN MICROSOFT DYNAMICS AX 2009

To set BI properties on the AddressCounty table

To set BI properties on the AddressState table

To set BI properties on the CustGroup table

To set BI properties on the CustInvoiceJour table

To set BI properties on the CustInvoiceTrans table

To set BI properties on the CustTable table

To set BI properties on the InventItemGroup table

To set BI properties on the InventTable table

To generate a BI project

To view cube objects in the generated project

To deploy the cube

To browse the cube data

To create a mapping for the Master Company Exchange Rate

To create the Master Company Reporting Currency named query

To add the Master Company Reporting Currency dimension to the cube

To add the Master Company Reporting Currency dimension

To deploy the cube

INTRODUCTION TO CUBES AND MULTIDIMENSIONAL MODELS

SYNCHRONIZE THE OLAP Dynamics AX DATABASE

Shrink SQL File

A. Shrinking a data file to a specified target size

B. Shrinking a log file to a specified target size

C. Truncating a data file

D. Emptying a file

Dynamics AX security model

Dynamics AX: Generating an AIF web service

Specifying the authentication method for an AIF web service

Creating and configuring an AIF website

وصية الربيع بن خثيم لإبنه

Setting up AIF to use web services

Managing and troubleshooting AIF document exchanges

Managing and troubleshooting AIF document exchanges