CHAPTER 3: SQL SERVER ANALYSIS SERVICES

siberiaskeinData Management

Nov 20, 2013 (3 years and 11 months ago)

898 views

Chapter 3:

SQL Server Analysis Services

3
-
1

CHAPTER 3:

SQL SERVER ANALYSIS
SERVICES

Objectives

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 S
QL Server Analysis Services cube
.



Explain the options for analyzing cube data
.

Introduction

In addition to the reporting features that SQL Server Reporting Services
provides, Microsoft Dynamics AX 2012 provides business intelligence and data
analysis featu
res 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 lev
els 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 mont
h is higher, the
data can be analyzed to identify a specific customer or order that contributed to
the overall trend.



Reporting in Microsoft Dynamics
®

AX 2012

3
-
2

Analysis Overview

SQL 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 t
o 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 s
tore 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 DATA
BASE COMPARED TO OLA
P 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.

Chapter 3:

SQL Server Analysis Services

3
-
3

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 in
clude 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 mu
ltiple 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
an
alyzing; 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.
Basi
c 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 me
asures.

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 Se
rver 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.



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 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 Configuration

Microsoft 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 insta
ll 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 se
tup. 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

Chapter 3:

SQL Server Analysis Services

3
-
5

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



Auto
mate 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



Custom
er 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 th
e 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.

Reporting in Microsoft Dynamics
®

AX 2012

3
-
6

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 Cubes

The "Create New Cubes" demonstration will create a cube that enables you to
analyze data from Microsoft Dynamics AX 2012. D
uring 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).



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 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. Dimen
sions 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
pers
pective, 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.



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 t
he 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 PROP
ERTIES

Setting the Usage property to OLAP indicates that the perspective will be used to
generate 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 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

CustTable
Cube
:

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 AN
D VIEWS ON A PERSPEC
TIVE




Reporting in Microsoft Dynamics
®

AX 2012

3
-
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 pres
ented.



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 att
ribute 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 Cus
tTableCube 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

o

AnalysisMeasureGroupLabel
:
Customer details

Chapter 3:

SQL Server Analysis Services

3
-
11

3.

For the following fie
lds 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




Reporting in Microsoft Dynamics
®

AX 2012

3
-
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 T
YPE 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
.

Chapter 3:

SQL Server Analysis Services

3
-
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
Nex
t
.

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
.



Reporting in Microsoft Dynamics
®

AX 2012

3
-
14

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


FIGURE 3.6 DEPLOY AN
D PROCESS SQL SERVER

ANALYSIS SERVICES
PROJECT

Develop KPI to
Display in Role Center

Cubes 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
al
so 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
\
Dyna
mics 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 CU
BE

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
:

Yo
u 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 Gaug
e from the drop
-
down list.

8.

For the
Status 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 this
expression evaluates to. If total customer sales is greater than or equal to 7
500,
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
t
o 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
auth
orization

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
.



Chapter 3:

SQL Server Analysis Services

3
-
17

7.

Provide

a name for the file, for example, Dynamics AX.odc.

8.

Click
Save

and close Excel.


FIGURE 3.8 ODC CONNE
CTION FILE PROPERTIE
S

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
N
o
.

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.



Reporting in Microsoft Dynamics
®

AX 2012

3
-
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 rol
e 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 D
ata Connection (ODC) file that points to
that database.



Chapter 3:

SQL Server Analysis Services

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



Reporting in Microsoft Dynamics
®

AX 2012

3
-
20

Develop a Report to Display in Microsoft Dynamics AX 2012

SSAS
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 rol
e 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 pro
ject:

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
b
ox, 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 def
ine 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
Re
port
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

o

Provider
:

OLAP



Chapter 3:

SQL Server Analysis Services

3
-
21

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 databa
se.


FIGURE 3.10 DATA SOU
RCE 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 the
n 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 nam
e.

4.

Expand the node for the report if it is not already expanded.

5.

Right
-
click the Datasets node and then click
Add Dataset
.

6.

Select the node for the dataset.



Reporting in Microsoft Dynamics
®

AX 2012

3
-
22

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:0
0],

[Transaction date].[Year].&[2009
-
01
-
01T00:00:00],

[Transaction date].[Year].&[2010
-
01
-
01T00:00:00]}

ON ROWS

FROM "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



Chapter 3:

SQL Server Analysis Services

3
-
23

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 prop
erty 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 Template
s

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 ti
tle for the data region.



Reporting in Microsoft Dynamics
®

AX 2012

3
-
24

5.

In Model Editor, right
-
click the AutoDesign1 node, and then click
Preview

to preview the report.


FIGURE 3.12 REPORT P
REVIEW

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
.



Chapter 3:

SQL Server Analysis Services

3
-
25

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 ITE
M 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
.



Reporting in Microsoft Dynamics
®

AX 2012

3
-
26

Display a Report in an Enterpris
e 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 Data

The 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 Mic
rosoft 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
t
he cube data from your Analysis Services database into Microsoft Office Excel.



Chapter 3:

SQL Server Analysis Services

3
-
27

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 Dat
a 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, a
nd 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 Led
ger 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.



Reporting in Microsoft Dynamics
®

AX 2012

3
-
28

8.

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


FIGURE 3.14 BROWSE C
UBE 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 deploye
d and
processed 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 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.



Reporting in Microsoft Dynamics
®

AX 2012

3
-
30

6.

Browse the data.


FIGURE 3.15 BROWSING

CUBE DATA IN SQL SER
VER MANAGEMENT
STUD
IO




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 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 Cente
r 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 displ
ayed.

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
pro
ject, 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

o

Provider
:

OLAP

Reporting in Microsoft Dynamics
®

AX 2012

3
-
32

9.

For the Conne
ction 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 S
tudio 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 ROWS

FROM "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
ReportLayoutSty
leTemplate.

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.

27.

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

Chapter 3:

SQL Server Analysis Services

3
-
33

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.



Reporting in Microsoft Dynamics
®

AX 2012

3
-
34

Summary

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



Chapter 3:

SQL Server Analysis Services

3
-
35

Test 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



Reporting in Microsoft Dynamics
®

AX 2012

3
-
36

Quick Interaction: Lessons Learned

Take a moment and write down three key points you have learned from this
chapter

1.





2.





3.




Chapter 3:

SQL Server Analysis Services

3
-
37

Solutions

Test 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

(

) Enter
prise Portal

( ) List Pages



Reporting in Microsoft Dynamics
®

AX 2012

3
-
38