Business Intelligence Environment Integration with Dynamics NAV Rogers

Business Intelligence Environment Integration with Dynamics NAV Rogers Family Company Matthew Mc. Ginley Devraj Ghosh Dominic Miller

Introduction and Background Matthew Mc. Ginley – IT Director Devraj Ghosh – Senior NAV Manager Dominic Miller – Data Warehouse & Business Intelligence Rogers Family Company’s NAV/BI Background

Current Business Intelligence Environment IBM Cognos Full enterprise-level BI platform Jet Reports Ad hoc reporting tool

Version: Jet Essentials 2013 Works as an Excel Add-in: Connects directly to your Server, Database and Company Can switch between databases and companies, which allows the same report to be run and used in multiple companies

Jet Reports Continued Customize to allow various report filters that the user can change. Report data from multiple tables. Example: Report on G/L Entry table, but pull in more detail from other tables, such as Vendor name. Sort, group, subtotal. Can still use Excel functions in combination with Jet formulas (such as IF statements, or even basic math formulas, such as addition, subtraction, division, etc. ) Example: to calculate Percentages, like percent change, or percent of total.

IBM Cognos Enterprise-level BI solution Integrates with various data sources, including Microsoft SQL Server Data is presented in a dimensional model, with data facts and dimensions, built from data organized into a star schema Cognos Power. Cubes contain calculated and aggregated data, enabling quick analysis

Star Schema Resembles star-like structure Fact tables are related to dimension tables by keys Unlimited number of dimension tables All data in fact table is at the same grain (all daily, monthly, same unit of measure, etc. ) One level of dimension tables (not a snowflake)

Dimensional Modeling Facts are business measures, such as sales dollars, cases, quantity on hand, etc. Dimensions are the “who, what, where, when, how, and why” associated with the event. In Cognos, Power. Cubes are built on data that is organized into a Star Schema.

Cognos Components Cognos Framework Manager Modeling tool that combines data from multiple sources Data connection to source database configured here Import Layer, Business Layer, Presentation Layer Cognos Transformer Consume presentation layer from framework Define dimensions and facts Build and deliver Power. Cubes Cognos Connection Web based interface Analysis tools available to users/developers include: Analysis Studio, Workspace Advanced, Report Studio

BI Process Flow

BI Process Flow – With Forecasting

Demonstration of Analysis Studio Sales/Forecast cube Item sales by customer by month Item forecasted cases versus actual cases by customer by month Downtime dashboard

Cognos Transformer & Framework Manager Sales/Forecast Models

Cubes in our Environment Sales/Forecast (Monthly and Daily Aggregation) Purchasing/Raw Material Production Machine Downtime Profit and Loss Inventory – Solochain (Relational Reporting)

Questions? Contact information: Devraj Ghosh dghosh@rogersfamilyco. com Dominic Miller dmiller@rogersfamilyco. com