modifying and creating ssas cubes for dynamics ax2012 linked in edit

Post on 13-Apr-2017

50 Views

Category:

Documents

1 Downloads

Preview:

Click to see full reader

TRANSCRIPT

Modifying and Creating SSAS Cubes for Dynamics AX2012

NIGEL MURDOCH

AXUG Summit | Tampa, Florida | October 11–14, 2016 | #CountOnSummit #AXUGSummit

Session OverviewLearning Objectives • Understand what is available in the standard Dynamics AX 2012 cubes. • Use AX tools to Configure, Customize and Extend the out of the box BI solution. • Learn how to connect to standard cubes with Excel and other tools.

Audience• Anyone who wishes to understand the functional aspects of the AX 2012 BI solution.

Prerequisites• Basic understanding of the AOT and its data related nodes.• Familiarity with Visual Studio and OLAP Projects • Intermediate understanding of Excel and PivotTables.

OOTB Business Intelligence in AX2012

AXUG Summit | Tampa, Florida | October 11–14, 2016 | #CountOnSummit #AXUGSummit

Dynamics AX Data Architecture

OLTP vs OLAP

AX DATA

OLAPCUBES

SQL Server Analysis Services

KPIs, Charts & Reports

SQL Reporting ServicesSharePoint Role Centers

AXUG Summit | Tampa, Florida | October 11–14, 2016 | #CountOnSummit #AXUGSummit

Dynamics AX OLAP Database Cubes

Microsoft Dynamics AX 2012 +R2 +R3 +Demand Planner

Accounts Payable

Accounts Receivable

Env. Sustainability

Expense Mgt.

GeneralLedger Production

Purchase Sales and Marketing Sales

ProjectAccounting

Workflow

Budget control

Budget plan

Inventoryvalue

Retail

Human Resources

Payroll

TAM

Demand Forecast

Forecast Accuracy

AXUG Summit | Tampa, Florida | October 11–14, 2016 | #CountOnSummit #AXUGSummit

Dynamics AX System Requirements

AXUG Summit | Tampa, Florida | October 11–14, 2016 | #CountOnSummit #AXUGSummit

Application Integration• Currency Conversion• Date Dimensions• General Ledger

• Fiscal Calendar• Main Account Categories

• Time Periods

More about Time Periods

CurrentMonth - TemplateSTRTOMEMBER('[|DateDim|].[Year - Quarter - Month - Week - Date].[Month].&[' + vba!format(vba![date](), 'yyyy-MM-01') + 'T00:00:00]')

Month_LastMonth - Example

AXUG Summit | Tampa, Florida | October 11–14, 2016 | #CountOnSummit #AXUGSummit

AOT Integration• Perspectives• Tables, Views & Queries• EDT’s and Fields• View Methods• Visual Studio Project

Perspective

Fact

Table/View Usage

Measure

Group

Field Usage

Measure

AXUG Summit | Tampa, Florida | October 11–14, 2016 | #CountOnSummit #AXUGSummit

Analysis Services Project Wizard• Deploy • Configure• Update• Create

Advanced functional use of standard cubes

AXUG Summit | Tampa, Florida | October 11–14, 2016 | #CountOnSummit #AXUGSummit

Dynamics AX Role Centers

• Cues• Business Overviews• Charts• KPIs• Links• Workflows

AXUG Summit | Tampa, Florida | October 11–14, 2016 | #CountOnSummit #AXUGSummit

Presentation Layer Integration

• Profiles• Company• Currency • Localization

Role Center

Webpart Pages

Dynamics AX Enterprise Portal

AXUG Summit | Tampa, Florida | October 11–14, 2016 | #CountOnSummit #AXUGSummit

Webpart Integration

• Cues• Charts & Reports• Business

Overview & KPI’sAX

DATAOLAP

CUBES

SQL Server Analysis Services

Cues Charts KPI’s

Query Time PeriodParameters

Application

Role Center

Data

Users

Developer

AXUG Summit | Tampa, Florida | October 11–14, 2016 | #CountOnSummit #AXUGSummit

Using Excel for Exploration• Connection uses AS Role Security• Leverage familiar Pivot Table/Chart tools to Explore and

Visualize Cube data• Use Excel Addins – Power Query, Power Pivot

AXUG Summit | Tampa, Florida | October 11–14, 2016 | #CountOnSummit #AXUGSummit

Using Excel for Development• Use OLAP Tools to create and test

• MDX Calculated Measures• MDX Calculated Members• MDX Sets

AXUG Summit | Tampa, Florida | October 11–14, 2016 | #CountOnSummit #AXUGSummit

Connecting PowerBI.com• Power BI Desktop• Connect, Develop & Design• Publish to PowerBI.com• Enrich & Share

AXUG Summit | Tampa, Florida | October 11–14, 2016 | #CountOnSummit #AXUGSummit

Connecting Power BI Desktop• Use Import data – for OLAP Cubes• Access to:• Measures & Calculated Measures• Dimension Attributes & Hierarchies• KPI Data

AXUG Summit | Tampa, Florida | October 11–14, 2016 | #CountOnSummit #AXUGSummit

Data Gateway - Versions

AXUG Summit | Tampa, Florida | October 11–14, 2016 | #CountOnSummit #AXUGSummit

Data Gateway - Installing• Download from PowerBI.com• Install• Manage

AXUG Summit | Tampa, Florida | October 11–14, 2016 | #CountOnSummit #AXUGSummit

Data Gateway - Managing• Gateway Settings & Administrators• Add Data Source & Users

AXUG Summit | Tampa, Florida | October 11–14, 2016 | #CountOnSummit #AXUGSummit

Contact InfoNigel Murdoch MCSE, MCSA, MCDBA, MCITP – Business Intelligence

Technical Solution Architect Edgewater/Fullscope

LinkedIn: http://www.linkedin.com/in/nigelmurdochEmail: nigel.murdoch@fullscope.com

© 2016 Dynamic Communities. All rights reserved.

top related