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

Reporting in Microsoft Dynamics®

AX 2012

3-2

Analysis Overview

SQL Server Analysis Services (SSAS) provides a way of looking at data thatdiffers from how it is shown in SQL Server

Reporting Services (SSRS). SSRSretrieves data from the Microsoft Dynamics AX 2012 database and allows you tosort and filter the data to display the specific records that you want. SSASaggregates data from Microsoft Dynamics AX 2012 and provides a way to viewslices of data. Instead of looking at individual transactions or records, SSASdisplays 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 retrievedata that is related to the transaction. This type of database is called an OnlineTransaction Processing (OLTP) database because the application is designed tocreate and display transaction data. To access the Microsoft Dynamics AX 2012

data for analysis, SSAS creates a separate database that is optimized to retrievelarge amounts of aggregated data. This type of database is called Online AnalysisProcessing (OLAP) database because the data is organized to allow analysis ofthe data.

AnOLTP database quickly enters and retrieves a small number of records. It ishighly normalized with many tables. An OLAP database aggregates data frommultiple sources and queries typically involve a large volume of records. OLAPis typically de-normalizedwith fewer tables.

FIGURE 3.1 OLTP DATABASE COMPARED TO OLAP DATABASE

The "OLTP Database Compared to OLAP Database" figure shows that the OLAPdatabase cubes allow users to view aggregated data that shows all customers,items, and dates. Alternatively,

the user can drill down a level to view quantitiesof all items that are sold on a particular date, or even lower to view what itemswere sold on a particular date to a particular customer.

Chapter 3:

SQL Server Analysis Services

3-3

Cube Structure

Microsoft Dynamics AX2012 provides the ability tomodel cubes and dimensionsusing AX tables and views. Cubes can be extended using Business IntelligenceDevelopment Studio (BIDS) in Visual Studio and can use data and objects fromthe Microsoft Dynamics AX MorphX

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 aspecific requirement. A perspective collects a subset of cube features to display asimpler set of data. A perspective is used to identify the tables and views

thatcontain 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 beaggregated. Measures correspond to something that users are interested inanalyzing; such as revenue, profit, or the total number of items sold. A cube canhave one or more measures.

Measures are collected in a Measure Group. A Measure Group containsmeasures, 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 measuregroups. Measure groups are used to associate dimensions with measures.

Attributes and Dimensions

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

attributes.

The defined relationships between dimensions and measure groups in a cubedetermine how the data in the cube is sliced. Cube relationships are generatedbased on the relationships between the tables and views. When you open aproject in SQL Server Business Intelligence Development Studio (BIDS), youcan view the relationships that were generated. You can define additional ormodify existing relationships between dimensions and measure groups in CubeDesigner.

Reporting in Microsoft Dynamics®

AX 2012

3-4

KPIs and Calculated Members

A Key

Performance Indicator (KPI) is a collection of calculations that are used tomeasure business success. A calculated member is a member of a dimension ormeasure group that is defined based on a combination of cube data, arithmeticoperators, numbers, andfunctions. KPIs are added using Cube Designer in BIDSafter a project has been generated. The calculations that comprise a KPI are acombination of Multidimensional Expressions (MDX) and calculated membersthat are also added using Cube Designer. A KPI isgenerally comprised of thevalue achieved, a goal, a status value, and the trend.

page, clickSelect a project from the AOT. Select the Dynamics AX projectfrom the list. ClickNext.

6.

On theDeployment options

page, do the following:

a.

Select theDeploy the project

check box.

b.

Enter the name of the server that is running Analysis Services.

c.

ClickCreate new database.

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

a new name.

Reporting in Microsoft Dynamics®

AX 2012

3-6

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

d.

Select theProcess the project after it is successfully deployed

check box.

e.

ClickNext

to deploy and process the cubes.

FIGURE 3.2

DEPLOY CUBES

7.

On theDeploying

page, clickNext

when the deployment iscompleted.

8.

ClickFinish

to close the wizard.

Develop Cubes

The "Create New Cubes" demonstration will create a cube that enables you toanalyze data from Microsoft Dynamics AX 2012. During the demonstration, youwill use the Business Intelligence (BI) properties in Microsoft Dynamics AX2012 to specify measures and attributes for the cube. Additionally you willgenerate an Analysis Services project so that you can work with the cube in

SQLServer Business Intelligence Development Studio (BIDS).

Chapter 3:

SQL Server Analysis Services

3-7

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 Contosogroup 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 thefollowing 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 slicemeasures, for example, sales by quarter, sales by payment group, or sales bycustomer group. You can specify measures and dimensions to model a cube inthe Application Object Tree (AOT). For the demonstration, you will create aperspective, add Microsoft Dynamics AX tables and views to the perspective,and then define measures and dimensions by using the views and table in theperspective.

Reporting in Microsoft Dynamics®

AX 2012

3-8

Create a New Perspective

Follow these steps to create a new perspective:

1.

In the AOT, expand the Data Dictionary node. Right-click thePerspectives node, and then clickNew Perspective.

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

Chapter 3:

SQL Server Analysis Services

3-9

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 clickOpen New Window.

2.

In the new window, expand the Views node. Drag the followingviews 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 theCustPaymModeTable table onto the Tables node of theSalesAnalysis

perspective. This table contains payment information.

2.

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

FIGURE 3.4 TABLES AND VIEWS ON A PERSPECTIVE

Reporting in Microsoft Dynamics®

AX 2012

3-10

Specify Measures and Attributes

You must specify measures and attributes to analyze the desired salesinformation. To do this, you need to set properties on each view and table in theSalesAnalysis perspective.

The following properties are used to define how the data in the cube is presented.



AnalysisDimensionType

-

determines the type of join to use whentables 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 ora measure.



AnalysisDefaultTotal

-

determines how the values are aggregated.



ExchangeRateDateField

-

selects the date field to use to determinethe exchange rate. Because the cube can display multiple currencies,the exchange rate is used as ofa specific date.

Follow these steps to set properties on the CustTransTotalSales view:

1.

In the SalesAnalysis perspective, select the CustTransTotalSalesview.

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 toAttribute:

o

TransType

o

TransDate

4.

For theAmountMST

field, set the following parameters:

o

AnalysisUsage:Measure

o

AnalysisDefaultTotal:Sum

o

ExchangeRateDateField:

TransDate. This setting specifies thatthe currency exchange will be calculated using the rate on thetransaction 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 theparent table

o

AnalysisDimensionLabel:

Customer information

o

AnalysisMeasureGroupLabel:Customer details

Chapter 3:

SQL Server Analysis Services

3-11

3.

For the following fields in the view, set the AnalysisUsage toAttribute:

o

Blocked

o

GroupName

o

City

o

County

o

Name

o

State

4.

For theAccountNum

field, set the following parameters:

o

AnalysisUsage:Measure

o

AnalysisDefaultTotal:

Count

Follow these steps to set properties on theCustPaymModeTable table:

1.

In the SalesAnalysis perspective, select the CustPaymModeTabletable.

2.

In the Properties sheet, set the following values:

o

AnalysisDimensionType:

Auto

Reporting in Microsoft Dynamics®

AX 2012

3-12

3.

For the following fields in the table, set the AnalysisUsage toAttribute:

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 directlyto the Analysis Services server. However, to develop KPIs for a Role centerusing the cube data, you must create an Analysis Services project and edit theproject using BIDS.

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

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

8.

SelectAdd foreign currency support to the AnalysisServicesproject

and then clickNext. After the project is generated, clickNext

again.

9.

SelectSave to AOT. SelectDynamics AX

from the list, or create anew AOT node. SelectSave to disk, type the location to which tosave the project, and then clickNext.

10.

If the project is completed, you can also deploy from this wizard.

a.

On the Deployment options page, SelectDeploy the project.Specify the server name.

b.

SelectCreate new database. Type Dynamics AXSalesAnalysis.

c.

SelectProcess the project after it is

successfully deployed

andthen clickNext.

d.

ClickNext

and thenFinish.

Reporting in Microsoft Dynamics®

AX 2012

3-14

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

FIGURE 3.6 DEPLOY AND PROCESS SQL SERVER

ANALYSIS SERVICESPROJECT

Develop KPI toDisplay in Role Center

Cubes that are developed in Business Intelligence Development Studio can beintegrated with Microsoft Dynamics AX 2012. A cube can be used to create aKPI that is displayed on a Role Center or Enterprise Portal page. Cube data canalso 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 inSQL Server Business Intelligence Studios (BIDS):

1.

Open the Analysis Services Project that you previouslycreated byclickingFile > Open

and browsing to the project file at:C:\Users\Administrator\Documents\AnalysisServicesProjects\Dynamics AX Sales Analysis.

Chapter 3:

SQL Server Analysis Services

3-15

2.

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

FIGURE 3.7 SELECT CUBE

3.

On the toolbar for theKPIs

tab, clickNew KPI. A form displaysthat allows you to define the KPI.

4.

For theName

field, type "Customer Sales".

5.

For theValue Expression

field, type the following expression.

[Measures].[Total customer sales count]

NOTE:

You can drag measures from the Metadata tab of the Calculation Toolspane to help you create expressions.

6.

For theGoal Expression

field, enter "7500".

This identifies the goal for the total customer sales count.

7.

For theStatus indicator

field, select Gauge from the drop-down list.

8.

For theStatus expression

field, type the following expression:

Case

When

[Measures].[Total customer sales count] >= 7500

Then 1

When

[Measures].[Total customer sales count] < 7500

AND

[Measures].[Total customer sales count] >6500

Then 0

Else-1

End

Reporting in Microsoft Dynamics®

AX 2012

3-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 thisexpression 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 totalcustomer sales is between 6500 and 7500, the expression returns a "0" and theKPI displays a yellow indicator. For all other values (total customer sales lessthan or equalto 6500), the expression returns a "-1" and the KPI displays a redindicator.

Follow these steps to view the KPI:

1.

On the Build menu, clickDeploy Dynamics AX SalesAnalysis.

2.

On the toolbar for theKPIs

tab, clickBrowser View. The KPIdisplays in the list.

For more advanced KPI examples, see the templates that are available in theTemplates

tab of the Calculation Tools pane.

Create an ODC File

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

The following procedure explains how to create an ODCfile:

1.

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

Click the drop-down menu for the web part and clickEdit WebPart. 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 AXAnalysis Services database. You can use any KPIs that ship with MicrosoftDynamics AX and any KPIs that you add to that database. If you create anotherdatabase, you must create an Office Data Connection (ODC) file that points tothat database.

Chapter 3:

SQL Server Analysis Services

3-19

7.

For the Title, type "Sales KPIs". ClickOK

to save the changes.

FIGURE 3.9 SALES WEB

PART PROPERTIES

8.

In the Sales KPIs web part, clickAdd KPIs.

9.

In theBusiness Overview-

Add KPI

dialog box,select the SalesAnalysis cube and select the Customer Sales KPI. For theDisplayvalue as

field, select Count and then click OK.

10.

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

Reporting in Microsoft Dynamics®

AX 2012

3-20

Develop a Report to Display in Microsoft Dynamics AX 2012

SSAScubes can provide data in various formats. In addition to KPIs that aredisplayed in a web part, cube data can be used to develop printed reports inMicrosoft Dynamics AX 2012.

Create a Reporting Project

Using cube data in a report in Microsoft Dynamics AX

2012 requires thefollowing 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 IntelligenceDevelopment Studio are also required.

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

1.

In Visual Studio 2010, clickFile > New > Project. TheNew Project

dialog box is displayed.

2.

In the Installed Templates section, clickMicrosoft Dynamics AX

and then clickReport Model.

3.

In theName

box, type "SampleCubeReport", and in the Locationbox, type a location.

4.

ClickOK.

Define a Data Source

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

1.

In Visual Studio Solution Explorer, right-click theSampleCubeReport project, point toAdd, and then clickReportDatasource. The model opens in Model Editor.

In a network with multiple servers, replace "localhost" with the name ofthe database server. It must be the name ofthe server that contains the databasefor the General ledger cube. In this statement, Security Support ProviderInterface (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 thencreate a dataset based on a multidimensional expression (MDX) query to retrievedata from the General ledger cube. Then, create a column chart report based onthe dataset and then apply layout and style templates to the report.

On the Site Actions menu, clickEdit Page. Locate the Footersection, and then click

Add Web Part.

3.

SelectMicrosoft Dynamics AX, clickReport, and then clickAdd.

4.

On the Edit menu for the web part, clickEdit Web Part. Theproperties for the web part is displayed.

5.

In theSelect a report

section, selectGross profit.

6.

For theToolbarsize

andReport drill through target toolbar size

properties, selectNone.

7.

For theShould the Web Part have a fixed height?

property, selectYes

and set the height to 6 Inches. ClickOK.

8.

ClickStop Editing.

Analyze Cube Data

The power of the SQL ServerAnalysis cube comes from the ability to selectvarious parameters to summarize the data. The interactive features allow you tocompare cube data by using different parameters.

Cube data can be browsed, sorted, and summarized by using Microsoft OfficeExcel

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 aKPI or a report to display on a Role Center. You can quickly analyze data byusing Microsoft Office Excel to connect to a Microsoft Dynamics AX analysiscube. For example, you might want to view the current balance of severaldifferent general ledger accounts.

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

page, enter "localhost" for theserver and use Windows Authentication. Then clickNext.

3.

On theSelect Database and Table

page, select the Dynamics AXdatabase, select theGeneral ledger

cube, and then clickNext.

4.

On theSave Data Connection File and Finish

page, enter a filename and friendly name, and then clickFinish.

NOTE:

After you have set up a data connection to a cube, that connection can bereused to connect to the cube in the future.

5.

In theImport Data

dialog box, selectPivotTable Report, specifythe location for the report within the spreadsheet, and then clickOK.

6.

In the Pivot Table Field List pane, selectGeneral ledger amount-

accounting currency

which is located under the Ledger transactionsnode. This adds the measure to the data region area in the pivot table.

7.

SelectAccount type

andNumber

located under the Chart ofaccounts node.

NOTE:

The available dimensions will vary depending on your Analysis Servicesproject.

Reporting in Microsoft Dynamics®

AX 2012

3-28

8.

Browse the data in the pivot table. Use the column filter to selectspecific 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 toconnect to a Microsoft Dynamics AX OLAP cube.

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

Chapter 3:

SQL Server Analysis Services

3-29

Follow these steps to analyze cube data:

1.

Open Microsoft SQL Server Management Studio.

2.

On theConnect to Server

page, for theServer type

field, selectAnalysis Services. Enter the nameof the server that contains theAnalysis Services database for the General Ledger cube, enter thecredentials used to access the server, and then clickConnect.

3.

Right-clickDatabases > Dynamics AX > Cubes > General ledgercube

and then clickBrowse.

NOTE:

The name of the database that contains the General Ledger cube canvary.

4.

In theGeneral ledger cube [Browse]

window, dragMeasures >Ledger transactions > General ledger amount-

transactioncurrency

onto the data region area.

5.

In theGeneral ledger

cube [Browse]

window, dragChart ofaccounts > Main Account

onto the row field area.

NOTE:

The available dimensions will vary depending on your Analysis ServicesProject.

Reporting in Microsoft Dynamics®

AX 2012

3-30

6.

Browse the data.

FIGURE 3.15 BROWSING

CUBE DATA IN SQL SERVER MANAGEMENTSTUDIO

Chapter 3:

SQL Server Analysis Services

3-31

Lab 3.1-

Create a Report Using Cube Data

This lab explains the process of creating a new analysis cube and using data fromthe cube on a Role Center web part.

Note: Before starting this lab, ensure the SQL Server service is running on thedemoserver.

Scenario

Isaac, a developer at Contoso, has been asked to add a new graphic to theSalesManagers

role center page. The manager wants to see a bar graph of salesamounts for each month. The sales amounts will be based on sales orderinvoices.

Challenge Yourself!

Use the information that was provided in the scenario to create a report to displayon 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 VisualStudio, select General Development Settings for the defaultenvironment settings.

2.

In Visual Studio, clickFile > New > Project. TheNew Project

dialog box is displayed.

3.

In the Installed Templates section, clickMicrosoft Dynamics AX

and then clickReport Model.

4.

In theName

box, typeSalesReport

and in theLocation

box, type alocation.

5.

ClickOK.

6.

In Visual Studio Solution Explorer, right-click the SalesReportproject, point toAdd, and then clickReport Datasource. The modelopens in Model Editor.