AXUG North Carolina May 2017 AX Reporting Data

AXUG North Carolina – May 2017 AX Reporting Data and Analytics are both important Peter Jennings, Dynamics AX team peterj@jetreports. com

The basis for our discussions today. It is not easy to get data out of AX for either reporting or business analytics What levels of empowerment do you want - Empower IT to support the company team - Enable reporting and analytics for everyone How do you take advantage of the really large investment Microsoft is making in in Power BI. Our goal is to help you and your firm solve your data & reporting needs

Jet Reports: Data & Reporting Experts Data • We are experts at data. • We help by taking complex data and making it simple • Something you can understand use Reporting • We are experts at reporting. Here we believe that matching tools to people is very important. • We support flexibility in how you can view your data.

1 – The back end What are. Challenge you looking for? Current goals: • Want to have great access to company information - fast • Empower the company’s People • ‘Self serve’ pivot tables, reports and queries – governed • IT empowerment to help provide this quickly and affordably • Governance: • Defined measures and metrics so everyone is looking at the same numbers • Strong security for data and access

– The end What two. Challenge realities 1 do youback face? Data Realities • AX is complex, really complex • Users cannot be expected to Query / Report from here

The Data within AX is hard and scattered, and it keeps getting harder Every Report requires that you join an ever increasing and more complex data schema from AX.

AND… What is happening next AX 7 … no D 365 for Operations… no D 365 Operations for Enterprise… Dynamics AX Version Table Count Data Entities AX 4. 0 1500 AX 2009 SP 1 RU 8 1838 AX 2012 RTM 3202 AX 2012 R 2 CU 7 5217 AX 2012 R 3 CU 11 6460 AX 7 6460 24 D 365 Ops 6600 1700 D 365 - No direct access - Data Entities instead - Tools for creating these - Common path is O-Data path - Challenging right now - EVERY release has meant a near complete re-write of most all reports

Simple Name & Address Listing Central Hub Customer Main Record Join Common Name Customer Number Entity Central Hub Customer Name Record Address Record Join Secondary Name Customer Name Entity State Customer. Street Address Name & Address Listing Join Secondary Name Customer State Country? Region? Rep?

Imagine it from 2 -3 systems, more… AX System Central Hub Table 1 Table 3 2 Join NO Second ERP System Join Name Central Table 4 Hub Table 1 Join Add State NO Table 1 Join Name Central Table 4 Hub Join More? Add System 1 Name & Address Listing ID Name Addrs Table 3 2 Join System 2 Sales Force Central Hub Table State

Open up the data base and it might look like this to an expert A lot of Data spread out over 6, 400 tables can be intimidating To complete a PO for instance uses over 20 tables

Ask a ‘normal person’ to use it – and it is closer to this

And real world situations add to the complexity Core AX Data – Built for Transaction Processing Silos of data, heavily manual efforts to combine • Reports run from the raw data sets • For most firms, multiple data sources come into the picture CRM Excel Requires technically skilled Report Programing / SSRS / SQL staffing. $$$$ Live AX PLM Legacy or other ERP

Challenge 1 – The end So you need to solve thisback problem first Data Realities • AX is complex, really complex • Users cannot be expected to Query / Report from here • Simplifying the data has two approaches • Traditional Development • which is very technical and time consuming • Data Warehouse Automation • which is really fast, and very powerful

Challenge Three - The Reality of being “Agile” Being Agile helps in: • Meeting the timing needs of immediate, fast and faster • Keeping people interested and thus involved, adoption • Data relevance – it is important, now “Invest in BI and analytic environments for experimentation and evidence-based data discovery. Then follow up with resilient operationalized analytics solutions where repeatable value is identified. ” – Gartner quote

The second reality adoption Challenge 1 ––The back end Reporting Realities • The best results employ tools that are easy, familiar and readily usable by a wide audience. • • • User Empowered and enabled Reporting Published Reports & Analytics Dash boards & Graphics

Why use multiple tools – within a single data and security set No single tool is a fit to everything a company needs. • • I want analytics for trends and directions I want details on customer business, orders and shipments I want to send the field information I want professional presentations for outside (banks or customers) No single tool is a fit for all situations. • I want to drill to audit details behind postings – numbers No single tool is a fit for all people. • My reps all know excel, all have it on their computers already • I want a combo charts to visualize big pictures with • Top management doesn’t need to be bogged down by too much detail • I need to back my charts with numbers, I need both types of views • I need something I can just open and look at, I am not a programmer

The Elephant in the Room – Power BI Microsoft Quote on Power BI “Power BI is a suite of business analytics tools to analyze data and share insights. Monitor your business and get answers quickly with rich dashboards available on every device. ”

The Good Elephant Front End +++ Good set of GUI chart and reporting tools included in AX Supports drills down capabilities, within, into AX details Easy, complete, flexible formatting supported Extensions into Excel supports Excel use Mobility device and auto-sizing Workflow and alert integration No open to use third party BI front end tools Behind in distribution and scheduling automation Investment Outstripping Competition by huge amounts Requires AX license for use Security dependent on AX security

Storing the Data ++ Using BI Cubes only, no data warehouse or data cloud Result is Cube silos Maintains AX Security only – security loss for non AX/CRM data Requires AX License for access if you use AX security Strong inside Power BI, closed to many outside viewers ETL – Getting the data into usable format Comes with a set of pre-defined Core AX BI Cubes Manual Data programming and development scripting AX Source engine, not aimed at competing solutions (e. g. Oracle, SAP, Salesforce…) Uses the common Microsoft Stack (SS Stack) for ETL Slow, old fashioned, expensive and error prone The Bad Elephant

ETL ‘Native’ to Power BI inside Dynamics Open, Industry Scripting, but highly complex and time consuming

What most people think when I say Power BI 12/13/2021

Complete package - really much more than just the front end tools DATA SOURCES ETL PROCESS How you build it ERP Data optimized for entry DATA WAREHOUSE DATA CLOUD How you store it DATA WAREHOUSE Data optimized for reporting BUSINESS INTELLIGENCE And yes, how you see it CUBE Data organized for top level analysis by role Jet Enterprise

So, what should you consider in order to avoid the pitfalls of traditional BI, and what about the evolving Power BI products First – the Business Guidelines • Cost Effective • Needs to be something useful, up and down the line • Familiar already or easy to learn and use • Must support fiduciary and regulatory requirements • Must be timely and accurate, and it should be Agile • Must be flexible, must be adoptable, and include choices

For Extract Transform and Load 1. Solution Specific Scripting – programming code unique to the Business Intelligence provider – e. g. Qlikview • Unique skill set and learning curve required. Tailored to the BI data storage and tools. It is costly, time consuming and hard. 2. Open Industry Scripting – using SQL, Oracle, or other common programming sets – e. g. Power BI • High level technical resource. More common techie skill. It is costly, time consuming and hard. 3. Automation tools, little to no programming or scripting • Evolving technology to speed the work, standardize, and minimize risk. Strong time to value results.

For Adoption & Use 1. Out of the box starting points 2. Choice in viewers 3. Agility & Speed means higher user involvement • Fast, cost effective, accurate and raises the buy in • Familiar, and easy raises the buy in • Being involved insures buy in 4. Cost decisions impact user population • Being cost effective widens the audience and user base Your Choice for Licensing

Having both will mean you are agile, with cost and risk advantages Agile Sprints BI Project RISK • Initially up and running overnight • Sprints for Design & Development • Automated Back end • Hours or Days, not weeks/months • Deliver Fast and Deliver Often TIME COMPANY CONFIDENTIAL

Jet is strong enough to deliver this solution Over 11, 000 customers world-wide. Offices and operations in 12 countries around the world, over 120 person team No Debt, Strong R&D investment, close strategic relationship with Microsoft Our goal is to move ahead with you and your firm towards solving your data & reporting needs.