11. Report From Cube Data.docx

Lab 3.11 Create a Report Using Cube Data This lab explains the process of creating a new analysis cube and using data from the cube on a Role Center web part. Note: Before starting this lab, ensure the SQL Server service is running on the demo server. Scenario Isaac, a developer at Contoso, has been asked to add a new graphic to the Sales Managers role center page. The manager wants to see a bar graph of sales amounts for each month. The sales amounts will be based on sales order invoices. Challenge Yourself Use the information that was provided in the scenario to create a report to display on the Sales Manager Role Center. Need a Little Help? 1. 2. 3. 4.

Create a reporting project Define a data source Create a report Add the report to a Role Center web part

Step by Step 5. Open Visual Studio 2010. If this is the first time running Visual Studio, select General Development Settings for the default environment settings. 6. In Visual Studio, click File > New > Project. The New Project dialog box is displayed. 7. In the Installed Templates section, click Microsoft Dynamics AX and then click Report Model. 8. In the Name box, type SalesReport and in the Location box, type a location. 9. Click OK.

1

Microsoft Dynamics AX 2012 Reporting, Chapter 3.

10. In Visual Studio Solution Explorer, right-click the SalesReport project, point to Add, and then click Report Datasource. The model opens in Model Editor. 11. In Model Editor, select the node for the data source. 12. In the Properties window, specify the following values: o Name: SalesReportOLAPData o Provider: OLAP 13. For the Connection String parameter enter the following: Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Data Source=[YourServerName];Initial Catalog=Dynamics AX

14. 15. 16. 17. 18. 19. 20.

21.

22. 23. 24. 25. 26.

Replace [YourServerName] with the name of your server, such as SEA-DEV. In Visual Studio Solution Explorer, right-click the SalesReport project, point to Add, and then click Report. Select the Report1 node. In the Properties window, type "SalesChart" as the name. Expand the node for the report if it is not already expanded. Right-click the Datasets node, and then click Add Dataset. Select the node for the dataset. In the Properties window, specify the following values: o Data Source: SalesReportOLAPData o Data Source Type: Query o Default Layout: ColumnChart o Name: Sales For the Query property, enter the following expression: SELECT {[Measures].[Customer invoice amount - accounting currency]} ON COLUMNS, {[Date].[Year].&[2008-01-01T00:00:00], [Date].[Year].&[2009-01-01T00:00:00], [Date].[Year].&[2010-01-01T00:00:00]} ON ROWS FROM "Sales cube" Click OK to save the query. In Model Editor, drag the Sales dataset onto the Designs node to create an auto design. In Model Editor, select AutoDesign1 node. In the Properties window, set the LayoutTemplate property to ReportLayoutStyleTemplate. In Model Editor, expand the AutoDesign1 node, and then select SalesXYChart node.

27. In the Properties window, set the Style Template property to ColumnChartStypleTemplate. 28. Click Deploy SalesReport on the Build menu. 29. Right-click the SalesReport project and then click Add SalesReport to AOT. 30. Open the development workspace in Microsoft Dynamics AX. If it was already open, close and re-open it to refresh the metadata. 31. Open the AOT. Expand the Menu Items node, right-click Output, and then click New Menu Item. 32. On the properties sheet for the new menu item, set the following values. o Name: SalesReport o Label: Sales Chart o Object Type: SSRSReport o Object: SalesChart o ReportDesign: AutoDesign1 33. In Microsoft Dynamics AX, navigate to System Administration > Common > Users > User Profiles. 34. Select the Sales Manager profile and click View Role Center. 35. On the Site Actions menu, click Edit Page. Locate the Middle Column section and then click Add a Web Part. 36. Select Microsoft Dynamics AX, click Report, and then click Add. 37. On the Edit menu for the web part, click Edit Web Part. The properties for the web part are displayed. 38. Click the lower-right area of the Right Column (where the bottom scroll bar should be) to cause the scroll bars to appear. 39. In the Select a report section, select Sales Chart. 40. Click OK. 41. To view the completed role center page, close the Enterprise Portal and re-open by clicking View Role Center from the Microsoft Dynamics AX User Profile window.

Walkthrough2: Creating a Cube In this walkthrough, you will create a cube that enables you to analyze data from Microsoft Dynamics AX. You will use the Business Intelligence (BI) properties in Microsoft Dynamics AX to specify measures and attributes for the cube, and then generate an Analysis Services project so that you can work with the cube in SQL Server Business Intelligence Development Studio (BIDS). In this walkthrough you will create an analysis cube called Sales analysis that will allow you to analyze the following data for the Contoso company:   

Sales revenue by quarter, months, and weeks Sales revenue by payment mode Average sales by customer group

This walkthrough includes the following tasks:     

Creating a perspective for a cube Specifying cube measure and attributes Generating an Analysis Services project Deploying a cube Browsing cube data

Prerequisites To complete this walkthrough, you will need:   

Microsoft Dynamics AX with sample data SQL Server Business Intelligence Development Studio Analysis Services extensions for Microsoft Dynamics AX

Creating a Perspective for the Cube

A cube is defined by its measures and dimensions. A measure is quantifiable, like sales, receivables, or item quantities. Dimensions are used to slice measures. For example, sales by quarter, sales by payment group, or sales by customer group. You can specify measures and dimensions to model a cube in the Application Object Tree (AOT). A perspective is a container for the tables and views that contain the measures and dimensions for a cube. For this walkthrough, you will create a perspective, add Microsoft Dynamics AX tables and views to the perspective, and then define measures and dimensions by using the views and table in the perspective.

2

http://msdn.microsoft.com/en-us/library/cc622026.aspx

The following procedures explain how to create a perspective for the cube and how to add views and tables to the perspective.

To create a perspective for the cube 1. 2.

3.

In the AOT, expand the Data Dictionary node. Right-click the Perspectives node, and then click New Perspective. Select the node of the perspective. In the Properties sheet, specify the following property values:

Property

Value

Name

SalesAnalysis

Label

Sales analysis

Usage

OLAP

The Usage property determines how the perspective will be used. Setting the Usage property to OLAP indicates that the perspective will be used to generate an Analysis Services project.

To add views to the perspective 1. 2.

In the AOT, right-click the Data Dictionary node, and then click Open New Window. In the new window, expand the Views node. Drag the following views onto the Views node of the SalesAnalysis perspective:

View

Description

CustTransTotalSales Contains sales invoice details. CustTableCube 3.

Contains master customer data.

In the AOT, right-click the SalesAnalysis perspective, and then click Save.

To add a table to the perspective 1.

In the second AOT, expand the Tables node. Drag the following table onto the Tables node of the SalesAnalysis perspective:

View

Description

CustPaymModeTable Contains payment mode information. 2.

In the AOT, right-click the SalesAnalysis perspective, and then click Save.

Specifying Cube Measures and Attributes You must specify measures and attributes to analyze the desired sales information. To do this, you need to set properties on each view and table in the SalesAnalysis perspective. The following procedures explain how to set BI properties on the views and table.

To set properties on the CustTransTotalSales view 1. 2.

In the SalesAnalysis perspective, select the CustTransTotalSales view. In the Properties sheet, specify the following values.

Property

Value

AnalysisDimensionType Transaction AnalysisDimensionLabel Sales type 3.

In the Properties sheet, set the following values for each field on the CustTransTotalSales view in the SalesAnalysis perspective.

Field

AnalysisUsag Analysis ExchangeRateDateField e Default Total

AmountMST Measure

Sum

TransDate Note The AmountMST value is in the accounting currency of the company. Microsoft Dynamics AX converts that amount to the analysis currency using Microsoft Dynamics AX exchange rates.

TransType

Attribute

Auto

TransDate

Attribute

Auto

All other fields

Auto

Auto

To set properties on the CustTableCube view 1. 2.

In the SalesAnalysis perspective, select the CustTableCube view. In the Properties sheet, specify the following values.

Property

Value

AnalysisDimensionType

Auto

AnalysisDimensionLabel

Customer information

AnalysisMeasureGroupLabel Customer details 3.

In the Properties sheet, set the following values for each field on the CustTableCube view in the SalesAnalysis perspective.

Tip You can select several fields and set their property values at the same time.

Field

AnalysisUsage AnalysisDefaultTotal

AccountNum

Measure

Count

Blocked

Attribute

Auto

GroupName

Attribute

Auto

City

Attribute

Auto

County

Attribute

Auto

Name

Attribute

Auto

State

Attribute

Auto

MainContactWorker

Attribute

Auto

All other fields

Auto

Auto

To set properties on the CustPaymModeTable table 1.

In the SalesAnalysis perspective, select the CustPaymModeTable table.

2.

3.

In the Properties sheet, specify the following values.

Property

Value

IsLookup

No

AnalysisDimensionType

Auto

In the Properties sheet, set the following values for each field on the CustPaymModeTable view in the SalesAnalysis perspective.

Field

AnalysisUsage

AnalysisDefaultTotal

Name

Attribute

Auto

PaymMode

Attribute

Auto

TypeOfDraft Attribute

Auto

AccountType Attribute

Auto

All other fields

Auto

Auto

Generating an Analysis Services Project Now that you have created a perspective and specified the measures and attributes for the cube, generate an Analysis Services project so that you can work with the analysis cube in BIDS. The following procedure explains how to generate an Analysis Services project.

To generate an Analysis Services project 1. 2. 3. 4. 5. 6. 7. 8.

Click Tools > Business Intelligence (BI) tools > SQL Server Analysis Services project wizard. The SQL Server Analysis Services project wizard form appears. Click Next. Select Create. In the Project name field, type Dynamics AX SalesAnalysis. Click Next. Select Sales analysis from the Available list and move it to the Selected list and then click Next. On the Select Microsoft Dynamics AX dimensions page, click Next. Select Date from the Available list and move it to the Selected list and then click Next. Select Arabic, Chinese (Simplified), and English (United States) from the Available list and move them to the Selected list and then click Next. Select Add foreign currency support to the Analysis Services project and then click Next. After the project is generated, clickNext again.

9.

Select Save to AOT. Select [New] from the list to create a new AOT node. Select Save to disk, type the location to which to save the project, and then click Next. 10. On the Deployment options page, Select Deploy the project. Specify the server name. For example, localhost. Select Create new database. Type Dynamics AX SalesAnalysis. Select Process the project after it is successfully deployed and then click Next. ClickNext and then click Finish.

Browsing Cube Data Now that the SalesAnalysis cube has been deployed and processed, you can browse the cube data within the Analysis Services project. The following procedure explains how to browse the cube data.

To browse the analysis cube data 1.

2. 3. 4. 5. 6. 7.

In BIDS, open the Dynamics AX SalesAnalysis Analysis Services project. You can open it from the location you saved it to on your hard drive, or you can open it from the AOT. To open the project from the AOT, right-click Visual Studio Projects > Analysis Services Projects > Dynamics AX SalesAnalysis and then click Edit. In Solution Explorer, double-click SalesAnalysis.cube. Click the Browser tab. Expand the Measures node, expand the Total customer sales node, right-click Amount, and then click Add to Data Area. Expand the Customers node, right-click State, and then click Add to Row Area. Expand the Customers node, right-click Customers - Name, and then click Add to Row Area. 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.

To view cube objects in the generated project 1.

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.

2.

Expand the Data Source Views node, and then double-click Dynamics AX SalesAnalysis. A data source view provides a unified view of the tables and their relationships.

3.

In Solution Explorer, expand the Cubes node, and then double-click SalesAnalysis.cube to display Cube Designer. Cube Designer allows you to 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.

Note Notice that the relationships between the measures and dimensions have been inferred by using the relationships in Microsoft Dynamics AX. Click the Translations tab to view the translations that exist for the cube. The SalesAnalysis cube has three translations, Arabic (Saudi Arabia) English (United States) and Chinese (People’s Republic of China). 4.

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