sql server analysis services - microsoft dynamics · web viewsql server analysis services (ssas)...

Chapter 3: SQL Server Analysis Services

CHAPTER 3: SQL SERVER ANALYSIS SERVICESObjectives

The objectives are:

Provide an overview of SQL Server Analysis Services features and capabilities.

Describe the SQL Server Analysis Services installation procedure and prerequisites.

Review the process for developing cubes by using Business Intelligence Development Studio.

Show how SQL Server Analysis Services can be used to display data in a Role Center web part.

Define the process for creating a report in Microsoft Dynamics® AX 2012 that uses data from a SQL Server Analysis Services cube.

Explain the options for analyzing cube data.

IntroductionIn addition to the reporting features that SQL Server Reporting Services provides, Microsoft Dynamics AX 2012 provides business intelligence and data analysis features by using SQL Server Analysis Services (SSAS). SSAS provides online analytical processing (OLAP) and data mining capabilities.

OLAP and data mining functions allow the user to aggregate data across multiple dimensions and then to drill down to lower levels of data. This feature is useful for displaying high level trends of selected data with the ability to identify underlying causes of significant changes. For example, SSAS can be used to collect data that shows total sales by month. If a particular month is higher, the data can be analyzed to identify a specific customer or order that contributed to the overall trend.

1

Reporting in Microsoft Dynamics® AX 2012

Analysis OverviewSQL Server Analysis Services (SSAS) provides a way of looking at data that differs from how it is shown in SQL Server Reporting Services (SSRS). SSRS retrieves data from the Microsoft Dynamics AX 2012 database and allows you to sort and filter the data to display the specific records that you want. SSAS aggregates data from Microsoft Dynamics AX 2012 and provides a way to view slices of data. Instead of looking at individual transactions or records, SSAS displays consolidated data and provides multiple ways of analyzing the data.

Analysis Data and Transaction Data

Microsoft Dynamics AX 2012 uses a SQL Server database to store and retrieve data that is related to the transaction. This type of database is called an Online Transaction Processing (OLTP) database because the application is designed to create and display transaction data. To access the Microsoft Dynamics AX 2012 data for analysis, SSAS creates a separate database that is optimized to retrieve large amounts of aggregated data. This type of database is called Online Analysis Processing (OLAP) database because the data is organized to allow analysis of the data.

An OLTP database quickly enters and retrieves a small number of records. It is highly normalized with many tables. An OLAP database aggregates data from multiple sources and queries typically involve a large volume of records. OLAP is typically de-normalized with fewer tables.

FIGURE 3.1 OLTP DATABASE COMPARED TO OLAP DATABASE

The "OLTP Database Compared to OLAP Database" figure shows that the OLAP database cubes allow users to view aggregated data that shows all customers, items, and dates. Alternatively, the user can drill down a level to view quantities of all items that are sold on a particular date, or even lower to view what items were sold on a particular date to a particular customer.

2


Cube Structure

Microsoft Dynamics AX2012 provides the ability to model cubes and dimensions using AX tables and views. Cubes can be extended using Business Intelligence Development Studio (BIDS) in Visual Studio and can use data and objects from the Microsoft Dynamics AX MorphX development environment. MorphX objects include tables, perspectives, views, and fields. BIDS objects include measures, dimensions, attributes, and KPIs to retrieve data from tables and views.

SQL Server Analysis Services Perspectives

A perspective is a subset of cube features. A cube can have multiple tables, measures and dimensions; in addition it can result in more data than needed for a specific requirement. A perspective collects a subset of cube features to display a simpler set of data. A perspective is used to identify the tables and views that contain the measures and dimension attributes for a specific requirement.

Measures and Measure Groups

A measure is a column in a table or view that contains data that can be aggregated. Measures correspond to something that users are interested in analyzing; such as revenue, profit, or the total number of items sold. A cube can have one or more measures.

Measures are collected in a Measure Group. A Measure Group contains measures, dimensions, partitions, and basic information about the measures. Basic information includes the name of the measure group, type of measures, storage mode, processing mode, and others.

In a cube, measures are grouped by their underlying fact tables into measure groups. Measure groups are used to associate dimensions with measures.

Attributes and Dimensions

You can set properties on tables, views, and fields to create analysis services dimensions and attributes. Attributes are fields or columns in a table or view in the Application Object Tree (AOT); dimensions are groups of attributes.

The defined relationships between dimensions and measure groups in a cube determine how the data in the cube is sliced. Cube relationships are generated based on the relationships between the tables and views. When you open a project in SQL Server Business Intelligence Development Studio (BIDS), you can view the relationships that were generated. You can define additional or modify existing relationships between dimensions and measure groups in Cube Designer.

3


KPIs and Calculated Members

A Key Performance Indicator (KPI) is a collection of calculations that are used to measure business success. A calculated member is a member of a dimension or measure group that is defined based on a combination of cube data, arithmetic operators, numbers, and functions. KPIs are added using Cube Designer in BIDS after a project has been generated. The calculations that comprise a KPI are a combination of Multidimensional Expressions (MDX) and calculated members that are also added using Cube Designer. A KPI is generally comprised of the value achieved, a goal, a status value, and the trend.

Installation and ConfigurationMicrosoft SQL Server Analysis Services (SSAS) is installed as part of the SQL Server installation. The Microsoft Dynamics AX 2012 Setup wizard will configure SSAS for use with Microsoft Dynamics AX 2012. Refer to the Microsoft Dynamics AX 2012 Installation Guide for more information.

Prerequisites

Before running the SSAS configuration, check that the prerequisites are met. Run the Microsoft Dynamics AX prerequisite validation utility to determine whether a computer meets the following requirements to install a Microsoft Dynamics AX component.

Microsoft SQL Server Analysis Services SQL Server Management Studio SQL Server Business Intelligence Development Studio

Refer to the Microsoft Dynamics AX 2012 System Requirements document for more information.

To configure Analysis Services for Microsoft Dynamics AX 2012, you must be a member of the SQL Server db_accessadmin role for the Microsoft Dynamics AX database.

Installation

To configure the Analysis Services components, run the Microsoft Dynamics AX 2012 setup. The installation setup wizard will then perform the following actions:

Verify that prerequisite software has been installed on the server Configure SQL Server Analysis Services Specify the default location for configuration files Connect to the AOS Test the Business Connector Proxy account Connect to the SQL Server database

4


After the wizard configures SSAS, you must perform several steps to complete the Analysis Services integration.

Deploy the default cubes Grant users access to the cubes Automate the processing of cubes

Refer to the Microsoft Dynamics AX 2012 Installation Guide for more information.

Deploy Cubes

Microsoft Dynamics AX 2012 provides default analysis cubes that you can use or modify:

Accounts Payable Accounts Receivable Customer Relationship Management Environmental Sustainability Expense Management General Ledger Production Project Accounting Purchase Sales Workflow

Follow these steps to deploy and process the default analysis cubes.

1. Open the Microsoft Dynamics AX client. 2. Start the SQL Server Analysis Services project wizard (File >

Tools > Business Intelligence (BI) tools > SQL Server Analysis Services project wizard).

3. On the Analysis Services project wizard page, click Next. 4. On the Select an option page, click Deploy and then click Next. 5. On the Select an existing Analysis Services project page, click

Select a project from the AOT. Select the Dynamics AX project from the list. Click Next.

6. On the Deployment options page, do the following:

a. Select the Deploy the project check box. b. Enter the name of the server that is running Analysis Services. c. Click Create new database.

By default, the database will be named Dynamics AX. If you want the database to have a different name, enter a new name.

5


NOTE: If you enter a new name for the database, you must modify the DynamicsAXOLAP data source in the AOT.

d. Select the Process the project after it is successfully deployed check box.

e. Click Next to deploy and process the cubes.

FIGURE 3.2 DEPLOY CUBES

7. On the Deploying page, click Next when the deployment is completed.

8. Click Finish to close the wizard.

Develop CubesThe "Create New Cubes" demonstration will create a cube that enables you to analyze data from Microsoft Dynamics AX 2012. During the demonstration, you will use the Business Intelligence (BI) properties in Microsoft Dynamics AX 2012 to specify measures and attributes for the cube. Additionally you will generate an Analysis Services project so that you can work with the cube in SQL Server Business Intelligence Development Studio (BIDS).

6


In this demonstration's sequence of steps, you will create an analysis cube called "Sales analysis" that will allow you to analyze the following data for the Contoso group of companies:

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

The demonstration steps include 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

Before performing the steps in the demonstration, you will need to have the following installed:

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

Create New Cubes

A cube is defined by its measures and dimensions. A measure is quantifiable, such as 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). For the demonstration, 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.

7


Create a New Perspective

Follow these steps to create a new perspective:

1. In the AOT, expand the Data Dictionary node. Right-click the Perspectives node, and then click New Perspective.

2. Select the perspective's node. In the Properties sheet, specify the following property values:

o Name: SalesAnalysis o Label: Sales Analysis o Usage: OLAP

FIGURE 3.3 PERSPECTIVE PROPERTIES

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

8


Add a View to a Perspective

The following procedure shows how to add a view to a perspective:

1. In the AOT, right-click the Data Dictionary node, and then click Open New Window.

2. In the new window, expand the Views node. Drag the following views onto the Views node of the SalesAnalysis perspective: o CustTransTotalSales: contains sales invoice details o CustTableCube: contains master customer data

Add a Table to a Perspective

The following procedure shows how to add a table to a perspective:

1. In the second AOT, expand the Tables node. Drag the CustPaymModeTable table onto the Tables node of the SalesAnalysis perspective. This table contains payment information.

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

FIGURE 3.4 TABLES AND VIEWS ON A PERSPECTIVE

9


10


Specify 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 properties are used to define how the data in the cube is presented.

AnalysisDimensionType - determines the type of join to use when tables are related, or specifies that the table is a transaction table.

AnalysisDimensionLabel - description for the view. AnalysisUsage - determines whether a field is used as an attribute or

a measure. AnalysisDefaultTotal - determines how the values are aggregated. ExchangeRateDateField - selects the date field to use to determine

the exchange rate. Because the cube can display multiple currencies, the exchange rate is used as of a specific date.

Follow these steps to set properties on the CustTransTotalSales view:

1. In the SalesAnalysis perspective, select the CustTransTotalSales view.

2. In the Properties sheet, set the following values: o AnalysisDimensionType: Transaction o AnalysisDimensionLabel: Sales type

3. For the following fields in the view, set the AnalysisUsage to

Attribute: o TransType o TransDate

4. For the AmountMST field, set the following parameters: o AnalysisUsage: Measure o AnalysisDefaultTotal: Sum o ExchangeRateDateField: TransDate. This setting specifies that

the currency exchange will be calculated using the rate on the transaction date.

Follow these steps to set properties on the CustTableCube view:

1. In the SalesAnalysis perspective, select the CustTableCube view. 2. In the Properties sheet, set the following values:

o AnalysisDimensionType: Auto. This uses the setting from the parent table

o AnalysisDimensionLabel: Customer information

11


o AnalysisMeasureGroupLabel: Customer details 3. For the following fields in the view, set the AnalysisUsage to

Attribute: o Blocked o GroupName o City o County o Name o State

4. For the AccountNum field, set the following parameters: o AnalysisUsage: Measure o AnalysisDefaultTotal: Count

Follow these steps to set properties on the CustPaymModeTable table:

1. In the SalesAnalysis perspective, select the CustPaymModeTable table.

2. In the Properties sheet, set the following values: o AnalysisDimensionType: Auto

12


3. For the following fields in the table, set the AnalysisUsage to Attribute:

o Name o PaymMode o TypeOfDraft o AccountType

FIGURE 3.5 ACCOUNT TYPE FIELD PROPERTIES

Create an Analysis Services Project

The cube data can be saved in an Analysis Services project and deployed directly to the Analysis Services server. However, to develop KPIs for a Role center using the cube data, you must create an Analysis Services project and edit the project using BIDS.

The following procedure explains how to create an Analysis Services project.

1. Click Tools > Business Intelligence (BI) tools > SQL Server Analysis Services project wizard. The SQL Server Analysis Services project wizard form appears.

2. Click Next.

13


3. Select Create. In the Project name field, type Dynamics AX SalesAnalysis. Click Next.

4. Select Sales analysis from the Available list and move it to the Selected list. Click Next.

5. On the Select Microsoft Dynamics AX dimensions page, click Next.

6. Select Date from the Available list and move it to the Selected list. Click Next.

7. Select Arabic, Chinese (Simplified), and English (United States) from the Available list and move them to the Selected list. Click Next.

8. Select Add foreign currency support to the Analysis Services project and then click Next. After the project is generated, click Next again.

9. Select Save to AOT. Select Dynamics AX from the list, or create a new AOT node. Select Save to disk, type the location to which to save the project, and then click Next.

10. If the project is completed, you can also deploy from this wizard. a. On the Deployment options page, Select Deploy the project.

Specify the server name. b. Select Create new database. Type Dynamics AX

SalesAnalysis. c. Select Process the project after it is successfully deployed and

then click Next. d. Click Next and then Finish.

14


For this demonstration, you will not deploy the project because the it is not yet complete.

FIGURE 3.6 DEPLOY AND PROCESS SQL SERVER ANALYSIS SERVICES PROJECT

Develop KPI to Display in Role CenterCubes that are developed in Business Intelligence Development Studio can be integrated with Microsoft Dynamics AX 2012. A cube can be used to create a KPI that is displayed on a Role Center or Enterprise Portal page. Cube data can also be used in a report in Microsoft Dynamics AX 2012.

Define KPI for a Cube

The following procedure shows how to create a KPI for Inventory Quantity in SQL Server Business Intelligence Studios (BIDS):

1. Open the Analysis Services Project that you previously created by clicking File > Open and browsing to the project file at: C:\Users\Administrator\Documents\AnalysisServicesProjects\Dynamics AX Sales Analysis.

15


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

FIGURE 3.7 SELECT CUBE

3. On the toolbar for the KPIs tab, click New KPI. A form displays that allows you to define the KPI.

4. For the Name field, type "Customer Sales". 5. For the Value Expression field, type the following expression.

[Measures].[Total customer sales count]

NOTE: You can drag measures from the Metadata tab of the Calculation Tools pane to help you create expressions.

6. For the Goal Expression field, enter "7500". This identifies the goal for the total customer sales count.

7. For the Status indicator field, select Gauge from the drop-down list. 8. For the Status expression field, type the following expression:

CaseWhen[Measures].[Total customer sales count] >= 7500Then 1When[Measures].[Total customer sales count] < 7500 AND[Measures].[Total customer sales count] > 6500Then 0Else -1End

16


This expression provides a basis to evaluate progress toward meeting the goal. The graphic that displays for the KPI status depends on what value this expression evaluates to. If total customer sales is greater than or equal to 7500, the expression returns a "1" and the KPI displays a green indicator. If total customer sales is between 6500 and 7500, the expression returns a "0" and the KPI displays a yellow indicator. For all other values (total customer sales less than or equal to 6500), the expression returns a "-1" and the KPI displays a red indicator.

Follow these steps to view the KPI:

1. On the Build menu, click Deploy Dynamics AX SalesAnalysis. 2. On the toolbar for the KPIs tab, click Browser View. The KPI

displays in the list.

For more advanced KPI examples, see the templates that are available in the Templates tab of the Calculation Tools pane.

Create an ODC File

For the Enterprise Portal to access the new cube data, a connection must be made to the new database that was created for the cube. To deploy an ODC file, you must create the file by using the Data Connection Wizard in Microsoft Excel and then add the ODC file to Enterprise Portal.

The following procedure explains how to create an ODC file:

1. Open Microsoft Excel by clicking Data > From Other Sources > From Analysis Services. The Data Connection Wizard is displayed.

2. On the Connect to Database Server page, specify the server that has the Analysis Services database, select Use Windows authorization and then click Next.

3. On the Select Database and Table page, select the Dynamics AX Sales Analysis database. Clear the Connect to a specific cube or table check box, and then click Next.

4. On the Save Data Connection File and Finish page, enter a file name and friendly name, and then click Finish. The Import Data wizard is displayed.

5. Click Properties. 6. On the Definition tab, verify that the connection string points to the

correct Analysis Services database. Click Export Connection File.

17


7. Provide a name for the file, for example, Dynamics AX.odc. 8. Click Save and close Excel.

FIGURE 3.8 ODC CONNECTION FILE PROPERTIES

Follow these steps to add the ODC file to Enterprise Portal:

1. Open Enterprise Portal. Make sure that you are accessing the main Enterprise Portal site. From the Site Actions menu, click New Document Library.

2. In the Name field, enter Data Connections. 3. In the Navigation and Document Version History sections, select No. 4. In the Document Template section, select None. Click Create. 5. Click Add document. 6. Click Browse and locate the ODC file that you created: for

example, ..\Documents\My Data Sources\Dynamics AX.odc. Click OK.

When you specify the data connection for a Business Overview web part, use the relative path of My Data Sources/<ODCFileName>, for example, My Data Sources/Dynamics AX.odc.

18


Display a KPI in a Role Center Web Part

Once you have defined KPIs for a cube, you can display the KPIs that you created in a web part on your role center page in Microsoft Dynamics AX or Enterprise Portal.

The following procedure explains how to add the Sales Analysis KPI to a Business Overview web part in the Sales Manager role center page:

1. Navigate to the Sales Manager role center page. 2. On the Site Actions menu, click Edit Page. Locate the Middle

Column section, and then click Add a Web Part. 3. In the Add a Web Part dialog box, select Microsoft Dynamics AX

under Categories, select Business Overview Web Part, and then click Add.

4. Click the drop-down menu for the web part and click Edit Web Part. The web part properties display.

5. For the Select mode property, select KPI List. 6. Expand the Business Overview Setup node.

NOTE: The Business Overview web part points to the default Dynamics AX Analysis Services database. You can use any KPIs that ship with Microsoft Dynamics AX and any KPIs that you add to that database. If you create another database, you must create an Office Data Connection (ODC) file that points to that database.

19


7. For the Title, type "Sales KPIs". Click OK to save the changes.

FIGURE 3.9 SALES WEB PART PROPERTIES

8. In the Sales KPIs web part, click Add KPIs. 9. In the Business Overview - Add KPI dialog box, select the Sales

Analysis cube and select the Customer Sales KPI. For the Display value as field, select Count and then click OK.

10. Click Exit Edit Mode to save the changes made to the page.

20


Develop a Report to Display in Microsoft Dynamics AX 2012SSAS cubes can provide data in various formats. In addition to KPIs that are displayed in a web part, cube data can be used to develop printed reports in Microsoft Dynamics AX 2012.

Create a Reporting Project

Using cube data in a report in Microsoft Dynamics AX 2012 requires the following steps:

Creating a reporting project Defining a data source to connect to an Analysis Services database Creating a report to display cube data Displaying the report in a menu Displaying the report in an Enterprise Portal role center

Microsoft Dynamics AX Visual Studio Tools and Business Intelligence Development Studio are also required.

Use the Report Model template in Visual Studio 2010 to create a new reporting project, and then follow these steps to create a reporting project:

1. In Visual Studio 2010, click File > New > Project. The New Project dialog box is displayed.

2. In the Installed Templates section, click Microsoft Dynamics AX and then click Report Model.

3. In the Name box, type "SampleCubeReport", and in the Location box, type a location.

4. Click OK.

Define a Data Source

A data source contains information about a connection to a database and includes information such as the server name, the database name, and user credentials. The following procedure explains how to define a data source so that you can access the data from the Analysis Services database that contains the data for the default General ledger cube.

1. In Visual Studio Solution Explorer, right-click the SampleCubeReport project, point to Add, and then click Report Datasource. The model opens in Model Editor.

2. In Model Editor, select the node for the data source. 3. In the Properties window, specify the following values:

o Name: ProfitAnalysisOlapData

21


o Provider: OLAP

4. For the Connection String parameter enter the following:

Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Data Source=localhost;Initial Catalog=Dynamics AX

NOTE: In a network with multiple servers, replace "localhost" with the name of the database server. It must be the name of the server that contains the database for the General ledger cube. In this statement, Security Support Provider Interface (SSPI) is using Microsoft Windows user credentials for authentication. Dynamics AX is the default name of the Analysis Services database.

FIGURE 3.10 DATA SOURCE PROPERTIES

Create a Report

You can create a report to display data from the General ledger cube and then create a dataset based on a multidimensional expression (MDX) query to retrieve data from the General ledger cube. Then, create a column chart report based on the dataset and then apply layout and style templates to the report.

Follow these steps to create a report:

1. In Model Editor, right-click SampleCubeReport, point to Add, and then click Report.

2. Select the Report1 node. 3. In the Properties window, type "ProfitChart" as the name. 4. Expand the node for the report if it is not already expanded. 5. Right-click the Datasets node and then click Add Dataset.

22


6. Select the node for the dataset.

23


7. In the Properties window, specify the following values: o Data Source: ProfitAnalysisOLAPData o Data Source Type: Query o Default Layout: ColumnChart o Name: Profit

8. For the Query property, enter the following expression: SELECT {[Measures].[General ledger gross profit - accounting currency]} ON COLUMNS,{[Transaction date].[Year].&[2008-01-01T00:00:00],[Transaction date].[Year].&[2009-01-01T00:00:00],[Transaction date].[Year].&[2010-01-01T00:00:00]}ON ROWSFROM "General ledger cube"

NOTE: You might have to modify the MDX query depending on the data that is available in your cube. For example, you night not have data for all the years that are listed in the query.

FIGURE 3.11 DATASET PROPERTIES

24


9. In Model Editor, select the Year field. 10. In the Properties window, make sure that the Field Type property is

set to Grouping and the Grouping Type property is set to Category. 11. In Model Editor, select the

Measures_General_ledger_gross_profit_ field. 12. In the Properties window, make sure that the Field Type property is

set to Data. Type "Gross profit" in the Caption property. 13. In Model Editor, drag the Profit dataset onto the Designs node for the

report. 14. An auto design is generated for the report. Expand the node for the

auto design and notice that it contains a category based on the Year field.

Apply Style and Layout Templates

The following procedure shows how to apply layout and style templates:

1. In Visual Studio Model Editor, select the AutoDesign1 node. 2. In the Properties window, set the LayoutTemplate property to

ReportLayoutStyleTemplate. Type "Gross profit" for the Title property.

3. In Model Editor, expand the AutoDesign1 node and then select the ProfitXYChart node.

4. In the Properties window, set the Style Template property to ColumnChartStyleTemplate. Delete the text for the Title property so that it does not display a title for the data region.

25


5. In Model Editor, right-click the AutoDesign1 node, and then click Preview to preview the report.

FIGURE 3.12 REPORT PREVIEW

To deploy the report, click Build on the menu bar, then click Deploy SampleCubeReport.

Display a Report in a Menu

You can display the report in a Microsoft Dynamics AX menu by creating an output menu item that generates the report. Then you can add the menu item to the General ledger module.

The following procedure explains how to display the report in a menu:

1. Right-click the SampleCubeReport project and then click Add SampleCubeReport to AOT. Close Visual Studio.

2. Open the development workspace in Microsoft Dynamics AX. If it was already open, close and re-open it to refresh the metadata.

3. Open the AOT. Expand the Menu Items node, right-click Output, and then click New Menu Item.

26


4. On the properties sheet for the new menu item, set the following values:

o Name: SampleCubeReport o Label: Gross Profit o Object Type: SSRSReport o Object: ProfitChart o ReportDesign: AutoDesign1

FIGURE 3.13 MENU ITEM PROPERTIES

5. Save the menu item.

NOTE: Under the Output node, right-click SampleCubeReport to preview the report.

6. Open a second AOT. Expand the Menus node, expand the GeneralLedger node, and then drag the SampleCubeReport menu item from the first AOT onto the Reports node in the second AOT.

7. Save the changes and restart the client. Navigate to the General ledger module and then click Gross profit.

27


Display a Report in an Enterprise Portal Role Center

You can add the report to an Enterprise Portal role center page by adding a web part to the role center.

Follow these steps to display the report in an Enterprise Portal role center

1. Navigate to the CEO role center page. In Microsoft Dynamics AX navigate to System Administration > Users > User Profiles and select the CEO profile. Click View Role Center to view the role center in the Enterprise Portal.

2. On the Site Actions menu, click Edit Page. Locate the Footer section, and then click Add Web Part.

3. Select Microsoft Dynamics AX, click Report, and then click Add. 4. On the Edit menu for the web part, click Edit Web Part. The

properties for the web part is displayed. 5. In the Select a report section, select Gross profit. 6. For the Toolbar size and Report drill through target toolbar size

properties, select None. 7. For the Should the Web Part have a fixed height? property, select

Yes and set the height to 6 Inches. Click OK. 8. Click Stop Editing.

Analyze Cube DataThe power of the SQL Server Analysis cube comes from the ability to select various parameters to summarize the data. The interactive features allow you to compare cube data by using different parameters.

Cube data can be browsed, sorted, and summarized by using Microsoft Office Excel and management tools such as SQL Server Management Studio.

Analyze Cube Data with Excel

In some cases you might want to view cube data without having to create either a KPI or a report to display on a Role Center. You can quickly analyze data by using Microsoft Office Excel to connect to a Microsoft Dynamics AX analysis cube. For example, you might want to view the current balance of several different general ledger accounts.

To analyze the General Ledger cube data through a pivot table, you must import the cube data from your Analysis Services database into Microsoft Office Excel.

28


Follow these steps to analyze cube data in a Microsoft Office Excel pivot table:

1. Open Microsoft Office Excel. Click Data > From Other Sources > From Analysis Services. The Data Connection Wizard opens.

2. On the Connect to Database Server page, enter "localhost" for the server and use Windows Authentication. Then click Next.

3. On the Select Database and Table page, select the Dynamics AX database, select the General ledger cube, and then click Next.

4. On the Save Data Connection File and Finish page, enter a file name and friendly name, and then click Finish.

NOTE: After you have set up a data connection to a cube, that connection can be reused to connect to the cube in the future.

5. In the Import Data dialog box, select PivotTable Report, specify the location for the report within the spreadsheet, and then click OK.

6. In the Pivot Table Field List pane, select General ledger amount - accounting currency which is located under the Ledger transactions node. This adds the measure to the data region area in the pivot table.

7. Select Account type and Number located under the Chart of accounts node.

NOTE: The available dimensions will vary depending on your Analysis Services project.

29


8. Browse the data in the pivot table. Use the column filter to select specific accounts to view.

FIGURE 3.14 BROWSE CUBE DATA IN EXCEL

Analyze Cube Data with Management Studio

Data can be analyzed by using Microsoft SQL Server Management Studio to connect to a Microsoft Dynamics AX OLAP cube.

To analyze the General Ledger cube data by using Microsoft SQL Server Management Studio you must connect to the cube data that is deployed and processed in a SQL Server Analysis Services (SSAS) database.

30


Follow these steps to analyze cube data:

1. Open Microsoft SQL Server Management Studio. 2. On the Connect to Server page, for the Server type field, select

Analysis Services. Enter the name of the server that contains the Analysis Services database for the General Ledger cube, enter the credentials used to access the server, and then click Connect.

3. Right-click Databases > Dynamics AX > Cubes > General ledger cube and then click Browse.

NOTE: The name of the database that contains the General Ledger cube can vary.

4. In the General ledger cube [Browse] window, drag Measures > Ledger transactions > General ledger amount - transaction currency onto the data region area.

5. In the General ledger cube [Browse] window, drag Chart of accounts > Main Account onto the row field area.

NOTE: The available dimensions will vary depending on your Analysis Services Project.

31


6. Browse the data.

FIGURE 3.15 BROWSING CUBE DATA IN SQL SERVER MANAGEMENT STUDIO

32


Lab 3.1 - Create a Report Using Cube DataThis 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. Create a reporting project 2. Define a data source 3. Create a report 4. Add the report to a Role Center web part

Step by Step

1. Open Visual Studio 2010. If this is the first time running Visual Studio, select General Development Settings for the default environment settings.

2. In Visual Studio, click File > New > Project. The New Project dialog box is displayed.

3. In the Installed Templates section, click Microsoft Dynamics AX and then click Report Model.

4. In the Name box, type SalesReport and in the Location box, type a location.

5. Click OK. 6. In Visual Studio Solution Explorer, right-click the SalesReport

project, point to Add, and then click Report Datasource. The model opens in Model Editor.

7. In Model Editor, select the node for the data source. 8. In the Properties window, specify the following values:

o Name: SalesReportOLAPData

33


o Provider: OLAP 9. 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

Replace [YourServerName] with the name of your server, such as SEA-DEV.

10. In Visual Studio Solution Explorer, right-click the SalesReport project, point to Add, and then click Report.

11. Select the Report1 node. 12. In the Properties window, type "SalesChart" as the name. 13. Expand the node for the report if it is not already expanded. 14. Right-click the Datasets node, and then click Add Dataset. 15. Select the node for the dataset. 16. In the Properties window, specify the following values:

o Data Source: SalesReportOLAPData o Data Source Type: Query o Default Layout: ColumnChart o Name: Sales

17. 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 ROWSFROM "Sales cube"

18. Click OK to save the query. 19. In Model Editor, drag the Sales dataset onto the Designs node to

create an auto design. 20. In Model Editor, select AutoDesign1 node. 21. In the Properties window, set the LayoutTemplate property to

ReportLayoutStyleTemplate. 22. In Model Editor, expand the AutoDesign1 node, and then select

SalesXYChart node. 23. In the Properties window, set the Style Template property to

ColumnChartStypleTemplate. 24. Click Deploy SalesReport on the Build menu. 25. Right-click the SalesReport project and then click Add SalesReport

to AOT. 26. Open the development workspace in Microsoft Dynamics AX. If it

was already open, close and re-open it to refresh the metadata.

34


27. Open the AOT. Expand the Menu Items node, right-click Output, and then click New Menu Item.

28. 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

29. In Microsoft Dynamics AX, navigate to System Administration > Common > Users > User Profiles.

30. Select the Sales Manager profile and click View Role Center. 31. On the Site Actions menu, click Edit Page. Locate the Middle

Column section and then click Add a Web Part. 32. Select Microsoft Dynamics AX, click Report, and then click Add. 33. On the Edit menu for the web part, click Edit Web Part. The

properties for the web part are displayed. 34. Click the lower-right area of the Right Column (where the bottom

scroll bar should be) to cause the scroll bars to appear. 35. In the Select a report section, select Sales Chart. 36. Click OK. 37. 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.

35


SummarySQL Server Analysis Services uses an OLAP database to analyze data from Microsoft Dynamics AX. SSAS cubes provide a framework for analyzing data. Data from SSAS cubes can be viewed from different perspectives and displayed in multiple formats.

36


Test Your Knowledge1. Which of the following statements is true?

( ) An OLAP database contains individual transactions.( ) Records in an OLTP database are updated frequently.( ) Cubes are stored in an OLTP database.( ) An OLAP database typically has more tables than an OLTP database.

2. Which data sources are used to provide data for a perspective? Choose two answers.

( ) Tables( ) Extended Data Types( ) Views( ) OLTP database

3. Where can cube data be used in Microsoft Dynamics AX 2012? (Select all that apply)

( ) Role Centers( ) Reports( ) Enterprise Portal( ) List Pages

37


Quick Interaction: Lessons LearnedTake a moment and write down three key points you have learned from this chapter

1.

2.

3.

38


SolutionsTest Your Knowledge

1. Which of the following statements is true?

( ) An OLAP database contains individual transactions.() Records in an OLTP database are updated frequently.( ) Cubes are stored in an OLTP database.( ) An OLAP database typically has more tables than an OLTP database.

2. Which data sources are used to provide data for a perspective? Choose two answers.

() Tables( ) Extended Data Types() Views( ) OLTP database

3. Where can cube data be used in Microsoft Dynamics AX 2012? (Select all that apply)

() Role Centers() Reports() Enterprise Portal( ) List Pages

39


40