microstrategy sales and distribution analysis module reference

Sales and Distribution Analysis Module Reference

Version: 9.0.2Document Number: 09970902

Sixteenth Edition, October 2010, version 9.0.2To ensure that you are using the documentation that corresponds to the software you are licensed to use, compare this version number with the software version shown in “About MicroStrategy...” in the Help menu of your software.

Document number: 09970902

Copyright © 2010 by MicroStrategy Incorporated. All rights reserved.

If you have not executed a written or electronic agreement with MicroStrategy or any authorized MicroStrategy distributor, the following terms apply:This software and documentation are the proprietary and confidential information of MicroStrategy Incorporated and may not be provided to any other person. Copyright © 2001-2010 by MicroStrategy Incorporated. All rights reserved.THIS SOFTWARE AND DOCUMENTATION ARE PROVIDED “AS IS” AND WITHOUT EXPRESS OR LIMITED WARRANTY OF ANY KIND BY EITHER MICROSTRATEGY INCORPORATED OR ANYONE WHO HAS BEEN INVOLVED IN THE CREATION, PRODUCTION, OR DISTRIBUTION OF THE SOFTWARE OR DOCUMENTATION, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE, GOOD TITLE AND NONINFRINGMENT, QUALITY OR ACCURACY. THE ENTIRE RISK AS TO THE QUALITY AND PERFORMANCE OF THE SOFTWARE AND DOCUMENTATION IS WITH YOU. SHOULD THE SOFTWARE OR DOCUMENTATION PROVE DEFECTIVE, YOU (AND NOT MICROSTRATEGY, INC. OR ANYONE ELSE WHO HAS BEEN INVOLVED WITH THE CREATION, PRODUCTION, OR DISTRIBUTION OF THE SOFTWARE OR DOCUMENTATION) ASSUME THE ENTIRE COST OF ALL NECESSARY SERVICING, REPAIR, OR CORRECTION. SOME STATES DO NOT ALLOW THE EXCLUSION OF IMPLIED WARRANTIES, SO THE ABOVE EXCLUSION MAY NOT APPLY TO YOU.In no event will MicroStrategy, Inc. or any other person involved with the creation, production, or distribution of the Software be liable to you on account of any claim for damage, including any lost profits, lost savings, or other special, incidental, consequential, or exemplary damages, including but not limited to any damages assessed against or paid by you to any third party, arising from the use, inability to use, quality, or performance of such Software and Documentation, even if MicroStrategy, Inc. or any such other person or entity has been advised of the possibility of such damages, or for the claim by any other party. In addition, MicroStrategy, Inc. or any other person involved in the creation, production, or distribution of the Software shall not be liable for any claim by you or any other party for damages arising from the use, inability to use, quality, or performance of such Software and Documentation, based upon principles of contract warranty, negligence, strict liability for the negligence of indemnity or contribution, the failure of any remedy to achieve its essential purpose, or otherwise. The entire liability of MicroStrategy, Inc. and your exclusive remedy shall not exceed, at the option of MicroStrategy, Inc., either a full refund of the price paid, or replacement of the Software. No oral or written information given out expands the liability of MicroStrategy, Inc. beyond that specified in the above limitation of liability. Some states do not allow the limitation or exclusion of liability for incidental or consequential damages, so the above limitation may not apply to you.The information contained in this manual (the Documentation) and the Software are copyrighted and all rights are reserved by MicroStrategy, Inc. MicroStrategy, Inc. reserves the right to make periodic modifications to the Software or the Documentation without obligation to notify any person or entity of such revision. Copying, duplicating, selling, or otherwise distributing any part of the Software or Documentation without prior written consent of an authorized representative of MicroStrategy, Inc. are prohibited. U.S. Government Restricted Rights. It is acknowledged that the Software and Documentation were developed at private expense, that no part is public domain, and that the Software and Documentation are Commercial Computer Software provided with RESTRICTED RIGHTS under Federal Acquisition Regulations and agency supplements to them. Use, duplication, or disclosure by the U.S. Government is subject to restrictions as set forth in subparagraph (c)(1)(ii) of the Rights in Technical Data and Computer Software clause at DFAR 252.227-7013 et. seq. or subparagraphs (c)(1) and (2) of the Commercial Computer Software—Restricted Rights at FAR 52.227-19, as applicable. Contractor is MicroStrategy, Inc., 1850 Towers Crescent Plaza, Vienna, VA 22182. Rights are reserved under copyright laws of the United States with respect to unpublished portions of the Software.The following are either trademarks or registered trademarks of MicroStrategy Incorporated in the United States and certain other countries:

MicroStrategy, MicroStrategy 6, MicroStrategy 7, MicroStrategy 7i, MicroStrategy 7i Evaluation Edition, MicroStrategy 7i Olap Services, MicroStrategy 8, MicroStrategy 9, MicroStrategy Distribution Services, MicroStrategy MultiSource Option, MicroStrategy Command Manager, MicroStrategy Enterprise Manager, MicroStrategy Object Manager, MicroStrategy Reporting Suite, MicroStrategy Power User, MicroStrategy Analyst, MicroStrategy Consumer, MicroStrategy Email Delivery, MicroStrategy BI Author, MicroStrategy BI Modeler, MicroStrategy Evaluation Edition, MicroStrategy Administrator, MicroStrategy Agent, MicroStrategy Architect, MicroStrategy BI Developer Kit, MicroStrategy Broadcast Server, MicroStrategy Broadcaster, MicroStrategy Broadcaster Server, MicroStrategy Business Intelligence Platform, MicroStrategy Consulting, MicroStrategy CRM Applications, MicroStrategy Customer Analyzer, MicroStrategy Desktop, MicroStrategy Desktop Analyst, MicroStrategy Desktop Designer, MicroStrategy eCRM 7, MicroStrategy Education, MicroStrategy eTrainer, MicroStrategy Executive, MicroStrategy Infocenter, MicroStrategy Intelligence Server, MicroStrategy Intelligence Server Universal Edition, MicroStrategy MDX Adapter, MicroStrategy Narrowcast Server, MicroStrategy Objects, MicroStrategy OLAP Provider, MicroStrategy SDK, MicroStrategy Support, MicroStrategy Telecaster, MicroStrategy Transactor, MicroStrategy Web, MicroStrategy Web Business Analyzer, MicroStrategy World, Application Development and Sophisticated Analysis, Best In Business Intelligence, Centralized Application Management, Information Like Water, Insight Is Everything, Intelligence Through Every Phone, Intelligence To Every Decision Maker, Intelligent E-Business, Personalized Intelligence Portal, Query Tone, Rapid Application Development, Strategy.com, MicroStrategy Intelligent Cubes, The Foundation For Intelligent E-Business, The Integrated Business Intelligence Platform Built For The Enterprise, The Intelligence Company, The Platform For Intelligent E-Business, The Scalable Business Intelligence Platform Built For The Internet, Industrial-Strength Business Intelligence, Office Intelligence, MicroStrategy Office, MicroStrategy Report Services, MicroStrategy Web MMT, MicroStrategy Web Services, Pixel Perfect, MicroStrategy Mobile, MicroStrategy Integrity Manager and MicroStrategy Data Mining Services are all registered trademarks or trademarks of MicroStrategy Incorporated.

All other products are trademarks of their respective holders. Specifications subject to change without notice. MicroStrategy is not responsible for errors or omissions. MicroStrategy makes no warranties or commitments concerning the availability of future products or versions that may be planned or under development.

Patent InformationThis product is patented. One or more of the following patents may apply to the product sold herein: U.S. Patent Nos. 6,154,766, 6,173,310, 6,260,050, 6,263,051, 6,269,393, 6,279,033, 6,501,832, 6,567,796, 6,587,547, 6,606,596, 6,658,093, 6,658,432, 6,662,195, 6,671,715, 6,691,100, 6,694,316, 6,697,808, 6,704,723, 6,707,889, 6,741,980, 6,765,997, 6,768,788, 6,772,137, 6,788,768, 6,792,086, 6,798,867, 6,801,910, 6,820,073, 6,829,334, 6,836,537, 6,850,603, 6,859,798, 6,873,693, 6,885,734, 6,888,929, 6,895,084, 6,940,953, 6,964,012, 6,977,992, 6,996,568, 6,996,569, 7,003,512, 7,010,518, 7,016,480, 7,020,251, 7,039,165, 7,082,422, 7,113,993, 7,181,417, 7,127,403, 7,174,349, 7,194,457, 7,197,461, 7,228,303, 7,260,577, 7,266,181, 7,272,212, 7,302,639, 7,324,942, 7,330,847, 7,340,040, 7,356,758, 7,356,840, 7,415,438, 7,428,302, 7,430,562, 7,440,898, 7,457,397, 7,486,780, 7,509,671, 7,516,181, 7,559,048, 7,574,376, 7,617,201, 7,725,811, and 7,801,967. Other patent applications are pending.

Various MicroStrategy products contain the copyrighted technology of third parties. This product may contain one or more of the following copyrighted technologies:Graph Generation Engine Copyright © 1998-2010. Three D Graphics, Inc. All rights reserved.Actuate® Formula One. Copyright © 1993-2010 Actuate Corporation. All rights reserved.XML parser Copyright © 2003-2010 Microsoft Corporation. All rights reserved.Xalan XSLT processor. Copyright © 1999-2010. The Apache Software Foundation. All rights reserved.Xerces XML parser. Copyright © 1999-2010. The Apache Software Foundation. All rights reserved.FOP XSL formatting objects. Copyright © 2004-2010. The Apache Software Foundation. All rights reserved.Portions of Intelligence Server memory management Copyright © 1991-2010 Compuware Corporation. All rights reserved.This product includes software developed by the OpenSSL Project for use in the OpenSSL Toolkit. (http://www.openssl.org/)International Components for UnicodeCopyright © 1999-2010 Compaq Computer CorporationCopyright © 1999-2010 Hewlett-Packard CompanyCopyright © 1999-2010 IBM CorporationCopyright © 1999-2010 Hummingbird Communications Ltd.Copyright © 1999-2010 Silicon Graphics, Inc.Copyright © 1999-2010 Sun Microsystems, Inc.Copyright © 1999-2010 The Open GroupAll rights reserved.Real Player and RealJukebox are included under license from Real Networks, Inc. Copyright © 1999-2010. All rights reserved.

© 2010 MicroStrategy, Inc. v

CONTENTS

Preface Description of Guide .................................................................. xiAbout the Analytics Modules ..................................................xii

About this book ............................................................................ xiiiHow to find business scenarios and examples ...................... xiiiPrerequisites ..........................................................................xivWho should use this guide.....................................................xiv

Resources.....................................................................................xvDocumentation........................................................................xvEducation.............................................................................. xxiiConsulting............................................................................. xxiiInternational support ............................................................. xxiiTechnical Support ................................................................ xxiii

Feedback ..................................................................................xxviii

1. Introduction to the Sales & Distribution Analysis Module

Business questions that SDAM reports can answer...................... 2Business analysis areas for SDAM reports.............................. 3SDAM analysis areas............................................................... 4

SDAM report features .................................................................... 5

Introduction to reports, dashboards, and documents .................... 6Accessing reports .................................................................... 7About scorecards and dashboards .......................................... 8About Report Services documents .......................................... 9

About the definitions for objects on reports ................................. 11

About the structure of SDAM’s business logic............................. 11Business concepts and measurements ................................. 11Relationships among business concepts............................... 12

Contents Sales & Distribution Analysis Module Reference

vi © 2010 MicroStrategy, Inc.

Porting an analytics module to your data source................... 12

Introduction to SDAM’s physical data storage ............................. 13

2. SDAM Scorecards and Documents

Introduction................................................................................ 15Scorecards and dashboards........................................................ 16

Executive Sales Scorecard.................................................... 16Inquiry to Shipment Process Document................................. 18Outbound Delivery Activity Scorecard ................................... 20Purchase Order Document .................................................... 22Sales and Delivery Report for Material Scorecard................. 24Sales and Distribution Dashboard ......................................... 26

3. Inquiry Analysis Introduction................................................................................ 29Inquiry Analysis area ................................................................... 29

Open Inquiries reports ................................................................. 30Summary Open Inquiries by Organization ............................. 30Summary Open Inquiries by Material..................................... 31List of Open Inquiries by Sales Office.................................... 32

Inquiry Analysis reports ............................................................... 33Quarterly Inquiry Activity by Organization.............................. 33Monthly Inquiry Activity Trends by Material ........................... 34Monthly Inquiry Distribution by Customer Region.................. 36

4. Quotation Analysis Introduction................................................................................ 37Quotation Analysis area............................................................... 37

Product hierarchy example .................................................... 38

Open Quotations reports ............................................................. 39Summary Open Quotations by Organization ......................... 40Summary Open Quotations by Product Hierarchy................. 41List of Open Quotations by Customer.................................... 43

Quotation Analysis reports........................................................... 44Quotation Activity Summary................................................... 45Quarterly Quotation Activity Summary by Organization......... 46Quarterly Quotation Activity Summary by Material ................ 47Monthly Quotation Activity Trends by Material....................... 48Quotations Distribution by Customer Region......................... 50Monthly Quotation Contribution and Trends by Distribution Channel ................................................................................. 51Material Contribution to Product Hierarchy Quotation Amounts

Sales & Distribution Analysis Module Reference Contents

© 2010 MicroStrategy, Inc. vii

52Quarterly Rejected Quotation Items by Organization ............ 53

Inquiry to Quotation Conversion reports ...................................... 54Quarterly Conversion Summary............................................. 55Quarterly Conversion Activity by Material .............................. 56Quarterly Conversion Activity by Organization ...................... 57

Customer Quotation History reports ............................................ 59Customer Quarterly Quotation Activity Summary .................. 59Customer Quotation History Detail ........................................ 60

5. Sales Order Analysis Introduction................................................................................ 63Sales Order Analysis area ........................................................... 63

Open Sales Orders reports.......................................................... 64Summary Open Sales Orders by Organization...................... 65Summary Open Sales Orders by Material ............................. 66List of Open Sales Orders by Customer ................................ 67

Sales Analysis reports ................................................................. 68Sales Activity Summary ......................................................... 68Quarterly Sales Summary...................................................... 70Monthly Sales Activity by Organization.................................. 71Sales Group Contribution to Sales Office Activity.................. 72Quarterly Profitability Summary by Sales Office .................... 73Top 5 Sales Orders by Net Sales Amount and Quarter......... 75

Customer Sales Analysis reports................................................. 76Quarterly Customer Base Sales Activity ................................ 76Customer Loyalty - Orders Two Consecutive Quarters ......... 77Monthly Sales Activity by Customer Characteristics.............. 78Top 5 Customers by Net Sales Amount and Quarter ............ 79

Material Sales reports.................................................................. 81Quarterly Sales Summary by Material ................................... 81Monthly Sales Activity by Material Attributes ......................... 82Monthly Sales Trends by Material.......................................... 83Quarterly Sales Summary by Product Hierarchy ................... 85Sales Organization Contribution to Material Division Sales... 86Material Division Contribution to Monthly Sales..................... 87Quarterly Product Hierarchy Sales by Material...................... 89Margin and Price Monthly Trends by Material ....................... 90Top 5 Materials by Net Sales Amount and Quarter ............... 91Bottom 3 Materials by % Net Margin and Quarter ................. 93

Quotation-to-Sales Order Conversion reports ............................. 94


viii © 2010 MicroStrategy, Inc.

Quarterly Conversion Summary............................................. 95Quarterly Conversion Activity by Organization ...................... 96Quarterly Conversion Activity by Material .............................. 97Top 5 Sales Orders from Quotations by Net Amount and Quarter................................................................................... 98

Customer Sales Order History reports....................................... 100Customer Quarterly Sales Orders Activity Summary........... 100Customer Sales Order History Detail................................... 101

6. Delivery Analysis Introduction.............................................................................. 103Delivery Analysis area ............................................................... 103

Open Deliveries reports ............................................................. 104List of Open Deliveries (Pending Material Availability) ........ 105List of Open Deliveries (Pending Goods Issue) ................... 105List of Open Deliveries (Issued and Pending Delivery)........ 106

Outbound Delivery Analysis reports .......................................... 107Quarterly Outbound Delivery Activity Summary................... 108Monthly Outbound Delivery Activity by Organization ........... 109Outbound Delivery Processing Times by Shipping Point..... 110Monthly Outbound Delivery Activity by Shipping Point ........ 112Shipping Point Contribution to Material Deliveries............... 113Material Processing Times by Shipping Point...................... 114Outbound Delivery Processing by Customer Region........... 115

Customer Delivery History reports............................................. 117Customer Quarterly Outbound Delivery Activity Summary .. 117Customer Outbound Deliveries History Detail ..................... 118Customer Delivery History (by Reference Sales Document)119

7. Sales and Delivery Service Analysis

Introduction.............................................................................. 121Sales and Delivery Service Analysis area ................................. 121

Backlog Analysis reports ........................................................... 122Backlog Summary by Material ............................................. 123Backlog and Fulfillment Rates by Organization ................... 124List of Backlogged Sales Orders by Customer .................... 125

Delivery Service reports............................................................. 125Outbound Delivery Service Summary by Shipping Point..... 126Outbound Delivery Service Quality by Organization............ 127Incorrect Deliveries Summary by Material and Shipping Point ..128List of Incorrectly Delivered Items by Customer .................. 130

Sales & Distribution Analysis Module Reference Contents

© 2010 MicroStrategy, Inc. ix

Delivered vs. Ordered Quantity by Sales Organization ....... 131

Returns Analysis reports............................................................ 132Quarterly Returns Summary ................................................ 133Quarterly Accepted Returns by Organization ...................... 134Quarterly Accepted Returns by Material .............................. 135List of Returns by Customer ................................................ 136Quarterly Shipping Point Returns Delivery Activity .............. 137

A. Definitions for Objects on Reports: Object Glossaries

Introduction.............................................................................. 139Metrics glossary......................................................................... 139

Base Formula metrics .......................................................... 140Inquiry metrics ..................................................................... 143Quotation metrics................................................................. 145Sales Order metrics ............................................................. 148Delivery metrics ................................................................... 154Sales and Delivery Service metrics ..................................... 157Contribution metrics ported to a database........................... 160

Filters glossary........................................................................... 161Filters for conditional metrics ............................................... 161Filters embedded in reports ................................................. 164

Prompts glossary ....................................................................... 165

B. Structure of SDAM’s Business Logic: Logical Data Model

Introduction.............................................................................. 167Prerequisites.............................................................................. 168

SDAM logical schema................................................................ 168

Business hierarchies.................................................................. 170Organization hierarchy......................................................... 170Material hierarchy ................................................................ 175Customer hierarchy.............................................................. 181Sales Document hierarchy................................................... 185Reference Document hierarchy ........................................... 189Delivery Document hierarchy............................................... 191Shipping Point hierarchy...................................................... 195Time hierarchy ..................................................................... 196

Transformations......................................................................... 201

User hierarchies......................................................................... 202

Facts .......................................................................................... 204Sales Document facts .......................................................... 205Delivery Document facts ...................................................... 207


x © 2010 MicroStrategy, Inc.

C. Data Storage: Physical Schema and Data Dictionary

Introduction.............................................................................. 213Prerequisites.............................................................................. 213

SDAM physical schema............................................................. 214

Table information ....................................................................... 216

Table column information .......................................................... 219

Glossary ................................................................................... 235

Index ......................................................................................... 247

© 2010 MicroStrategy, Inc. xi

PREFACE

Description of Guide

This guide is a reference for the MicroStrategy Sales and Distribution Analysis Module (SDAM), part of the MicroStrategy Analytics Modules that come with MicroStrategy Architect. This guide provides a description, usage scenarios, and screen shots for all the packaged reports for SDAM. It also provides descriptions of the module’s logical data model and physical schema tables and columns, as well as glossaries for the public objects (metrics, prompts, and filters) and schema objects (attributes).

• Chapter 1, Introduction to the Sales & Distribution Analysis Module presents an introduction to SDAM and provides descriptions and basic procedures to help you understand and work with SDAM.

• Chapter 2, SDAM Scorecards and Documents presents the SDAM scorecards and provides scorecard usage information and screen shots.

• Chapter 3, Inquiry Analysis presents the reports in the Inquiry analysis area.

• Chapter 4, Quotation Analysis presents the reports in the Quotation analysis area.

• Chapter 5, Sales Order Analysis presents the reports in the Sales Order analysis area.

• Chapter 6, Delivery Analysis presents the reports in the Delivery analysis area.

• Chapter 7, Sales and Delivery Service Analysis presents the reports in the Sales and Delivery Services analysis area.

Preface Sales & Distribution Analysis Module Reference

xii © 2010 MicroStrategy, Inc.

• Appendix A, Definitions for Objects on Reports: Object Glossaries presents the SDAM object glossaries, which provide definitions for all of the attributes and metrics used in the module.

• Appendix B, Structure of SDAM’s Business Logic: Logical Data Model presents SDAM’s logical data model, including a diagram and details for working with attributes and facts.

• Appendix C, Data Storage: Physical Schema and Data Dictionary presents SDAM’s default physical schema, including a diagram and details on the schema’s tables and columns.

Consult the Installation and Porting Guide for steps to install and configure your Analytics Modules, and for information on setting up (porting) an Analysis Module to work with your existing data warehouse. The Installation and Porting Guide also provides best practices concepts and procedures for designing and building your own analytical applications using the Analytics Modules as templates.

About the Analytics Modules

MicroStrategy helps you build analytical applications by offering a rapid application development framework consisting of analytical starter kits, development products, and design and development methodologies. There are five Analytics Modules that are built to be portable. You can choose to deploy the Analytics Modules against your existing data warehouse, use one of the packaged physical schemas as the basis of a new data warehouse, or use the modules as templates to build analytical applications.

The components are:

• Analytics Modules

Prepackaged metadata: Best practices reports, scorecards, dashboards, key performance indicators, attributes, business metrics, filters, and custom groups

Default physical and logical data model: Analytics that are designed to work with your physical schemas and data model or with the module’s packaged data warehouse schema

Reference guides: Documentation on each Analysis Module’s data model, the individual analysis areas, metadata object definitions, data dictionary, and individual report use scenarios

• Implementation methodology

Sales & Distribution Analysis Module Reference Preface

© 2010 MicroStrategy, Inc. About this book xiii

Documentation that guides you step-by-step through implementing Analytics Modules against existing data warehouses (known as porting)

Design rules and tenets for designing and developing portable analytical applications

MicroStrategy Architect: A development tool that allows you to map Analytics Modules to existing data warehouses

Documentation for MicroStrategy Desktop and MicroStrategy Architect is available on your MicroStrategy disk, as described fully in the Resources section in this chapter. See the Installation and Porting Guide for a detailed discussion of the Analytics Modules. The Installation and Porting Guide also presents procedures and methodologies to make full use of the Analytics Modules.

About this bookThis book is divided into chapters and reference appendices. Each chapter begins with a brief overview of the chapter’s content.

The following sections provide the location of examples, list prerequisites for using this book, and describe the user roles the information in this book was designed for.

Dates in the MicroStrategy Tutorial project are updated to reflect the current year. The sample documents and images in this guide, as well as the procedures, were created with dates that may no longer be available in the Tutorial project. Replace them with the first year of data in your Tutorial project.

How to find business scenarios and examples

For examples of report functionality and a basic introduction to the MicroStrategy business intelligence system, use the MicroStrategy Tutorial, which is MicroStrategy’s sample warehouse, metadata, and project. Information about the MicroStrategy Tutorial can be found in the MicroStrategy Basic Reporting Guide.

For extensive examples of metrics, filters, and other report objects, see the MicroStrategy Advanced Reporting Guide.


xiv About this book © 2010 MicroStrategy, Inc.

Prerequisites

How you use this document depends on the type of user you are and on your goals for working with the Analytics Modules. See Who should use this guide in this chapter for a description of the various approaches to the Analytics Modules product.

If you intend to evaluate the business value of the modules, you should have:

• Experience with MicroStrategy reports and metrics using MicroStrategy technology

If you intend to implement and customize the modules, you should have:

• Experience with logical data modeling and creating business intelligence applications using MicroStrategy technology

• A basic understanding of relational database management system (RDBMS) concepts and data modeling

Who should use this guide

This document is designed to support the analytical needs of:

• End users working with the packaged reports that come with the Analytics Modules. This can include, for example:

Executives who need summarized information about sales and distribution activities

Managers who need summarized information about sales and distribution activities for a given organizational unit, product line, delivery area, and so on. This may include analysis of historical trends and contributions to overall sales and distribution activities.

Analysts who need insight about the company’s activities to identify patterns driving successful behavior. This can include analyzing purchasing patterns by time or geographical area, identifying top products, segmenting the customer base to identify more profitable or loyal customers, measuring delivery activity volumes and processing times to determine bottlenecks in the delivery chain, and so on.

Operational users, who track day-to-day sales and distribution activities and individual customer activities. This might include monitoring open transactions, or tracking the complete sales activity history for an individual customer.


© 2010 MicroStrategy, Inc. Resources xv

• Advanced users and administrators evaluating the business value of the Analytics Modules

• Consultants and developers implementing and customizing the Analytics Modules

Resources

Documentation

MicroStrategy provides both manuals and online help; these two information sources provide different types of information, as described below.

Manuals: In general, MicroStrategy manuals provide:

• Introductory information and concepts

• Examples

• Checklists and high-level procedures to get started

Help: In general, MicroStrategy help provides:

• Detailed steps to perform procedures

• Descriptions of each option on every software screen

Manuals

The following manuals are available from your MicroStrategy disk or the machine where MicroStrategy was installed. The steps to access them are below.

Adobe Acrobat Reader is required to view these manuals. If you do not have Acrobat Reader installed on your computer, you can download it from www.adobe.com/products/acrobat/readstep2_allversions.html.

The best place for all users to begin is with the MicroStrategy Basic Reporting Guide.

http://www.adobe.com/products/acrobat/readstep2_allversions.html

http://www.adobe.com/products/acrobat/readstep2_allversions.html


xvi Resources © 2010 MicroStrategy, Inc.

MicroStrategy Overview

• Introduction to MicroStrategy: Evaluation Guide

Instructions for installing, configuring, and using the MicroStrategy Evaluation Edition of the software. This guide also includes a detailed, step-by-step evaluation process of MicroStrategy features, where you perform reporting with the MicroStrategy Tutorial project and its sample business data.

• MicroStrategy Quick Start Guide

Overview of the installation and evaluation process, and additional resources.

• Evaluate MicroStrategy for Linux Guide

Evaluate MicroStrategy for Linux, in a Microsoft Windows or Linux environment, with the MicroStrategy Evaluation Edition Virtual Appliance. This guide provides all details to download, activate, and evaluate MicroStrategy software running in a Linux environment.

• MicroStrategy Reporting Suite Quick Start Guide

Evaluate MicroStrategy as a departmental solution. Provides detailed information to download, install, configure, and use the MicroStrategy Reporting Suite.

• MicroStrategy Jump-Start Project Guide

Create a starter Business Intelligence project with pre-built reports and dashboards available with the Jump-Start project. This project can also be used to create proof-of-concept projects using your own data.

Manuals for Query, Reporting, and Analysis

• MicroStrategy Installation and Configuration Guide

Information to install and configure MicroStrategy products on Windows, UNIX, Linux, and HP platforms, as well as basic maintenance guidelines.

• MicroStrategy Upgrade Guide

Instructions to upgrade existing MicroStrategy products.


© 2010 MicroStrategy, Inc. Resources xvii

• MicroStrategy Project Design Guide

Information to create and modify MicroStrategy projects, and understand facts, attributes, hierarchies, transformations, advanced schemas, and project optimization.

• MicroStrategy Basic Reporting Guide

Instructions to get started with MicroStrategy Desktop and MicroStrategy Web, and how to analyze data in a report. Includes the basics for creating reports, metrics, filters, and prompts.

• MicroStrategy Advanced Reporting Guide

Instructions for advanced topics in the MicroStrategy system, building on information in the Basic Reporting Guide. Topics include reports, Freeform SQL reports, Query Builder reports, filters, metrics, Data Mining Services, custom groups, consolidations, and prompts.

• MicroStrategy Report Services Document Creation Guide

Instructions to design and create Report Services documents, building on information in the Basic Reporting Guide and Advanced Reporting Guide.

• MicroStrategy OLAP Services Guide

Information on MicroStrategy OLAP Services, which is an extension of MicroStrategy Intelligence Server. OLAP Services features include Intelligent Cubes, derived metrics, derived elements, dynamic aggregation, view filters, and dynamic sourcing.

• MicroStrategy Office User Guide

Instructions for using MicroStrategy Office to work with MicroStrategy reports and documents in Microsoft® Excel, PowerPoint, Word, and Outlook, to analyze, format, and distribute business data.

• MicroStrategy Mobile User Guide

Instructions for using MicroStrategy Mobile to view and analyze data, and perform other business tasks with MicroStrategy reports and documents on a mobile device. Covers installation and configuration of MicroStrategy Mobile and how a designer working in MicroStrategy Desktop or MicroStrategy Web can create effective reports and documents for use with MicroStrategy Mobile.


xviii Resources © 2010 MicroStrategy, Inc.

• MicroStrategy System Administration Guide Volume 1

Concepts and high-level steps to implement, deploy, maintain, tune, and troubleshoot a MicroStrategy business intelligence system.

• MicroStrategy System Administration Guide Volume 2

Concepts and high-level steps for using various administrative tools such as MicroStrategy Command Manager, MicroStrategy Enterprise Manager, MicroStrategy Integrity Manager, and MicroStrategy Health Center.

• MicroStrategy Functions Reference

Function syntax and formula components; instructions to use functions in metrics, filters, attribute forms; examples of functions in business scenarios.

• MicroStrategy MDX Cube Reporting Guide

Information to integrate MicroStrategy with MDX cube sources. You can integrate data from MDX cube sources such as SAP BW, Microsoft Analysis Services, and Hyperion Essbase into your MicroStrategy projects and applications.

• MicroStrategy Web Services Administration Guide

Concepts and tasks to install, configure, tune, and troubleshoot MicroStrategy Web Services.

Manuals for Analytics Modules

• Analytics Modules Installation and Porting Guide

• Customer Analysis Module Reference

• Sales Force Analysis Module Reference

• Financial Reporting Analysis Module Reference

• Sales and Distribution Analysis Module Reference

• Human Resources Analysis Module Reference


© 2010 MicroStrategy, Inc. Resources xix

Manuals for Information Delivery and Alerting Products

• MicroStrategy Narrowcast Server Getting Started Guide

Instructions to work with the tutorial to learn Narrowcast Server interfaces and features.

• MicroStrategy Narrowcast Server Installation and Configuration Guide

Information to install and configure Narrowcast Server.

• MicroStrategy Narrowcast Server Application Designer Guide

Fundamentals of designing Narrowcast Server applications.

• MicroStrategy Narrowcast Server System Administrator Guide

Concepts and high-level steps to implement, maintain, tune, and troubleshoot Narrowcast Server.

• MicroStrategy Narrowcast Server Upgrade Guide

Instructions to upgrade an existing Narrowcast Server.

Software Development Kits

• MicroStrategy Developer Library (MSDL)

Information to understand the MicroStrategy SDK, including details about architecture, object models, customization scenarios, code samples, and so on.

• MicroStrategy Web SDK

The Web SDK is available in the MicroStrategy Developer Library, which is sold as part of the MicroStrategy SDK.

• Narrowcast Server SDK Guide

Instructions to customize Narrowcast Server functionality, integrate Narrowcast Server with other systems, and embed Narrowcast Server functionality within other applications. Documents the Narrowcast Server Delivery Engine and Subscription Portal APIs, and the Narrowcast Server SPI.

To access the installed manuals and other documentation sources, see the following procedures:

• To access installed manuals on Windows, page xx


xx Resources © 2010 MicroStrategy, Inc.

• To access installed manuals on UNIX and Linux, page xx

To access installed manuals on Windows

1 From the Windows Start menu, choose Programs (or All Programs), MicroStrategy, then Product Manuals. A page opens in your browser showing a list of available manuals in PDF format and other documentation sources.

2 Click the link for the desired manual or other documentation source.

3 The Narrowcast Services SDK Guide must be downloaded. When you select this guide, the File Download dialog box opens. Select Open this file from its current location, and click OK.

If bookmarks are not visible on the left side of an Acrobat (PDF) manual, from the View menu click Bookmarks and Page. This step varies slightly depending on your version of Adobe Acrobat Reader.

To access installed manuals on UNIX and Linux

1 Within your UNIX or Linux machine, navigate to the directory where you installed MicroStrategy. The default location is /opt/MicroStrategy, or $HOME/MicroStrategy/install if you do not have write access to /opt/MicroStrategy.

2 From the MicroStrategy installation directory, open the Documentation folder.

3 Open the Product_Manuals.htm file in a web browser. A page opens in your browser showing a list of available manuals in PDF format and other documentation sources.

4 Click the link for the desired manual or other documentation source.

5 The Narrowcast Services SDK Guide must be downloaded. When you select this guide, the File Download dialog box opens. Select Open this file from its current location, and click OK.

If bookmarks are not visible on the left side of an Acrobat (PDF) manual, from the View menu click Bookmarks and Page. This step varies slightly depending on your version of Adobe Acrobat Reader.


© 2010 MicroStrategy, Inc. Resources xxi

Help

MicroStrategy provides several ways to access help:

• Help button: Use the Help button or ? (question mark) icon on most software windows to see help for that window.

• Help menu: From the Help menu or link at the top of any screen, select MicroStrategy Help to see the table of contents, the Search field, and the index for the help system.

• F1 key: Press F1 to see context-sensitive help that describes each option in the software window you are currently viewing.

Documentation standards

MicroStrategy online help and PDF manuals (available both online and in printed format) use standards to help you identify certain types of content. The following table lists these standards.

These standards may differ depending on the language of this manual; some languages have rules that supersede the table below.

Type Indicates

bold • button names, check boxes, options, lists, and menus that are the focus of actions or part of a list of such GUI elements and their definitions

• text to be entered by the userExample: Click Select Warehouse.Example: Type cmdmgr -f scriptfile.scp and press ENTER.

italic • new terms defined within the text and in the glossary• names of other product manuals• when part of a command syntax, indicates variable information to be replaced by the

userExample: The aggregation level is the level of calculation for the metric.Example: Type copy c:\filename d:\foldername\filename

Courier font

• calculations• code samples• registry keys• path and file names• URLs• messages displayed in the screen

Example: Sum(revenue)/number of months.


xxii Resources © 2010 MicroStrategy, Inc.

Education

MicroStrategy Education Services provides a comprehensive curriculum and highly skilled education consultants. Many customers and partners from over 800 different organizations have benefited from MicroStrategy instruction. For a detailed description of education offerings and course curriculums, visit www.microstrategy.com/Education.

Consulting

MicroStrategy Consulting Services provides proven methods for delivering leading-edge technology solutions. Offerings include complex security architecture designs, performance and tuning, project and testing strategies and recommendations, strategic planning, and more. For a detailed description of consulting offerings, visit www.microstrategy.com/Consulting.

International support

MicroStrategy supports several locales. Support for a locale typically includes native database and operating system support, support for date formats, numeric formats, currency symbols, and availability of translated interfaces and certain documentation.

MicroStrategy is certified in homogeneous configurations (where all the components lie in the same locale) in the following languages—English (US), French, German, Italian, Japanese, Korean, Portuguese (Brazilian), Spanish, Chinese (Simplified), Chinese (Traditional), Danish, and Swedish. For information on specific languages supported by individual MicroStrategy system components, see the MicroStrategy readme.

+ A keyboard command that calls for the use of more than one key (for example, SHIFT+F1)

A note icon indicates helpful information for specific situations.

A warning icon alerts you to important information such as potential security risks; these should be read before continuing.

Type Indicates

www.microstrategy.com/Education

www.microstrategy.com/Consulting


© 2010 MicroStrategy, Inc. Resources xxiii

MicroStrategy also provides limited support for heterogeneous configurations (where some of the components may lie in different locales). Please contact MicroStrategy Technical Support for more details.

A translated user interface is available in each of the above languages.

Technical Support

If you have questions about a specific MicroStrategy product, you should:

1 Consult the product guides, Help, and readme files. Locations to access each are described above.

2 Consult the MicroStrategy Knowledge Base online at https://resource.microstrategy.com/support.

A technical administrator in your organization may be able to help you resolve your issues immediately.

3 If the resources listed in the steps above do not provide a solution, contact MicroStrategy Technical Support directly. To ensure the most productive relationship with MicroStrategy Technical Support, review the Policies and Procedures document in your language, posted at http://www.microstrategy.com/Support/ Policies. Refer to the terms of your purchase agreement to determine the type of support available to you.

MicroStrategy Technical Support can be contacted by your company’s Support Liaison. A Support Liaison is a person whom your company has designated as a point-of-contact with MicroStrategy’s support personnel. All customer inquiries and case communications must come through these named individuals. Your company may designate two employees to serve as their Support Liaisons, and can request to change their Support Liaisons two times per year with prior written notice to MicroStrategy Technical Support.

It is recommended that you designate Support Liaisons who have MicroStrategy Administrator privileges. This can eliminate security conflicts and improve case resolution time. When troubleshooting and researching issues, MicroStrategy Technical Support personnel may make recommendations that require administrative privileges within MicroStrategy, or that assume that the designated Support Liaison has a security level that permits them to fully manipulate the MicroStrategy projects and has access to potentially sensitive project data such as security filter definitions.

https://resource.microstrategy.com/support

http://www.microstrategy.com/Support/Policies


xxiv Resources © 2010 MicroStrategy, Inc.

Ensure issues are resolved quickly

Before logging a case with MicroStrategy Technical Support, the Support Liaison may follow the steps below to ensure that issues are resolved quickly:

1 Verify that the issue is with MicroStrategy software and not a third party software.

2 Verify that the system is using a currently supported version of MicroStrategy software by checking the Product Support Expiration Schedule at http://www.microstrategy.com/Support/ Expiration.asp.

3 Attempt to reproduce the issue and determine whether it occurs consistently.

4 Minimize the complexity of the system or project object definition to isolate the cause.

5 Determine whether the issue occurs on a local machine or on multiple machines in the customer environment.

6 Discuss the issue with other users by posting a question about the issue on the MicroStrategy Customer Forum at https://resource.microstrategy.com/forum/.

The following table shows where, when, and how to contact MicroStrategy Technical Support. If your Support Liaison is unable to reach MicroStrategy Technical Support by phone during the hours of operation, they can leave a voicemail message, send email or fax, or log a case using the Online Support

http://www.microstrategy.com/Support/Expiration.asp




https://resource.microstrategy.com/forum/


© 2010 MicroStrategy, Inc. Resources xxv

Interface. The individual Technical Support Centers are closed on certain public holidays.

North America Email: [email protected] Web: https://resource.microstrategy.com/support Fax: (703) 842–8709 Phone: (703) 848–8700 Hours: 9:00 A.M.–7:00 P.M. Eastern Time, Monday–Friday except holidays

EMEA:EuropeThe Middle EastAfrica

Email: [email protected] Web: https://resource.microstrategy.com/support Fax: +44 (0) 208 711 2525 The European Technical Support Centre is closed on national public holidays in each country.Phone:

• Belgium: + 32 2792 0436• France: +33 17 099 4737• Germany: +49 22 16501 0609• Ireland: +353 1436 0916• Italy: +39 023626 9668• Poland: +48 22 321 8680• Scandinavia & Finland: +46 8505 20421• Spain: +34 91788 9852• The Netherlands: +31 20 794 8425• UK: +44 (0) 208 080 2182• International distributors: +44 (0) 208 080 2183

Hours:• United Kingdom: 9:00 A.M.–6:00 P.M. GMT, Monday-Friday except holidays• EMEA (except UK): 9:00 A.M.–6:00 P.M. CET, Monday-Friday except holidays

Asia Pacific Email: [email protected] Web: https://resource.microstrategy.com/support Phone:

• Australia: +61 2 9333 6499• Korea: +82 2 560 6565 Fax: +82 2 560 6555• Japan: +81 3 3511 6720 Fax: +81 3 3511 6740• Singapore: +65 6303 8969 Fax: +65 6303 8999• Asia Pacific (except Australia, Japan, Korea, and Singapore): +86 571 8526 8067 Fax: +86

571 8848 0977Hours:

• Japan and Korea: 9:00 A.M.–6:00 P.M. JST (Tokyo), Monday-Friday except holidays• Asia Pacific (except Japan and Korea): 7 A.M.-6 P.M. (Singapore) Monday-Friday except

holidays

Latin America Email: [email protected] Web: https://resource.microstrategy.com/support Phone:

• LATAM (except Brazil and Argentina): +54 11 5222 9360 Fax: +54 11 5222 9355• Argentina: 0 800 444 MSTR Fax: +54 11 5222 9355• Brazil: +55 11 3054 1010 Fax: +55 11 3044 4088

Hours: • Latin America (except Brazil): 9:00 A.M.–7:00 P.M. (Buenos Aires), Monday-Friday except

holidays • Brazil: 9 A.M. - 6 P.M. (São Paulo), Monday–Friday except holidays

Web: https://resource.microstrategy.com/support





xxvi Resources © 2010 MicroStrategy, Inc.

Support Liaisons should contact the Technical Support Center from which they obtained their MicroStrategy software licenses or the Technical Support Center to which they have been designated.

Required information when calling

When contacting MicroStrategy Technical Support, please provide the following information:

• Personal information:

Name (first and last)

Company and customer site (if different from company)

Contact information (phone and fax numbers, e-mail addresses)

• Case details:

Configuration information, including MicroStrategy software product(s) and versions

Full description of the case including symptoms, error messages(s), and steps taken to troubleshoot the case thus far

• Business/system impact

If this is the Support Liaison’s first call, they should also be prepared to provide the following:

• Street address

• Phone number

• Fax number

• Email address

To help the Technical Support representative resolve the problem promptly and effectively, be prepared to provide the following additional information:

• Case number: Please keep a record of the number assigned to each case logged with MicroStrategy Technical Support, and be ready to provide it when inquiring about an existing case

• Software version and product registration numbers of the MicroStrategy software products you are using


© 2010 MicroStrategy, Inc. Resources xxvii

• Case description:

What causes the condition to occur?

Does the condition occur sporadically or each time a certain action is performed?

Does the condition occur on all machines or just on one?

When did the condition first occur?

What events took place immediately prior to the first occurrence of the condition (for example, a major database load, a database move, or a software upgrade)?

If there was an error message, what was its exact wording?

What steps have you taken to isolate and resolve the issue? What were the results?

• System configuration (the information needed depends on the nature of the problem; not all items listed below may be necessary):

Computer hardware specifications (processor speed, RAM, disk space, and so on)

Network protocol used

ODBC driver manufacturer and version

Database gateway software version

(For MicroStrategy Web-related problems) browser manufacturer and version

(For MicroStrategy Web-related problems) Web server manufacturer and version

If the issue requires additional investigation or testing, the Support Liaison and the MicroStrategy Technical Support representative should agree on certain action items to be performed. The Support Liaison should perform any agreed-upon actions before contacting MicroStrategy Technical Support again regarding the issue. If the Technical Support representative is responsible for an action item, the Support Liaison may call MicroStrategy Technical Support at any time to inquire about the status of the issue.


xxviii Feedback © 2010 MicroStrategy, Inc.

FeedbackPlease send any comments or suggestions about user documentation for MicroStrategy products to:

[email protected]

Send suggestions for product enhancements to:

[email protected]

When you provide feedback to us, please include the name and version of the products you are currently using. Your feedback is important to us as we prepare for future releases.

© 2010 MicroStrategy, Inc. 1

11.INTRODUCTION TO THE SALES & DISTRIBUTION ANALYSIS MODULE

Introduction

Recognizing the patterns and motivating forces behind your organization’s sales and distribution processes is vital to maintaining a customer-focused business environment. To succeed, organizations must quickly identify problematic trends and trouble spots, and act to improve the overall customer experience. The MicroStrategy Sales and Distribution Analysis Module (SDAM) addresses the analytical requirements of typical sales and distribution business processes and subprocesses.

The in-depth analysis available with SDAM allows you to gain insight into your company’s activities to identify patterns that drive successful behavior. It allows you to summarize information about sales and distribution activities for a given organizational unit, product line, or delivery area, to study historical trends and contributions to overall sales and distribution activities.

SDAM assists analysts, managers, and executives to obtain important insight into the various factors that drive the sales and distribution process.

Introduction to the Sales & Distribution Analysis Module Sales & Distribution Analysis Module Reference1

2 Business questions that SDAM reports can answer © 2010 MicroStrategy, Inc.

Business questions that SDAM reports can answer

Knowledge about the sales and distribution process can be gleaned from packaged MicroStrategy analytics which include reports, scorecards (a specific collection of related reports), and dashboards (a collection of related reports customized for optimum display). In addition, some reports show summarized information to help you see trends, while others display very specific, detailed data about your customers.

The analytics in SDAM can be used to answer questions such as the following:

• How are inquiries allocated across distribution channels and the sales organization?

• What are my sales volumes, sales trends, and top materials and customers for the year?

• How are sales growing for a product? What is its effect on my sales overall?

• Which products and product lines show the highest activity, positive monthly trends, and highest net margins?

• Which sales offices are performing best and worst, in terms of profitability, number of sales orders, and net margin? Which sales offices show the most improvement?

• How is my customer loyalty? What percentage of my customer base currently has open sales orders?

• What are my top five sales orders by net sales amount, for each quarter?

• How can I bundle products to best initiate quotation-to-sales order conversions?

• How can I improve delivery management? Where in the delivery process are the longest delays and from which shipping points? Which customers and products are usually involved?

• Which distribution centers process the most orders? Are they more efficient than others? Which regions have the worst processing times?

• Which products create the highest number of backlogs?

Sales & Distribution Analysis Module Reference Introduction to the Sales & Distribution Analysis Module 1

© 2010 MicroStrategy, Inc. Business questions that SDAM reports can answer 3

• Do our pending deliveries represent fulfillment of old orders or a backlog from new orders?

• Which areas of the company have more returns? Which customers and products are usually involved?

Business analysis areas for SDAM reports

SDAM covers the analytical needs of major company business functions, as well as many important subprocesses:

• Sales: Analyze all pre-sales and sales activities, to identify sales activity volumes based on the number of transactions, amounts, and quantities. This includes a focus on historical trends, profitability, and organizational contributions to overall activity.

• Customer management: Analyze sales activity based on customer characteristics. This includes identifying customer segments with certain purchasing patterns and understanding customer loyalty and profitability.

• Product management: Analyze sales and delivery activity from the point of view of the product. This includes identifying material (products and services) purchasing patterns by geographical area, top products, product profitability, contributions to product line activity, delivery activity by product, historical trends, and so on.

• Delivery: Analyze delivery activities to identify activity volumes based on the number of transactions and quantities, and to measure delivery processing times.

• Service: Analyze how transactions are fulfilled to customers. This includes identifying anomalies in service functions, tracking open transactions, and measuring organizational efficiency in delivering materials.

• Open sales orders (backlog): Monitor how open sales orders are fulfilled after product orders are placed.

• Shipping: Monitor the tasks required to fulfill orders. Tasks include obtaining ordered materials and packing materials, loading them on the transportation mechanism, and issuing them from the shipping location.

• Returns: Monitor the tasks required to process product returns. This includes the tasks required to handle the return itself and, if necessary, deliver materials to replace the returned ones.


4 Business questions that SDAM reports can answer © 2010 MicroStrategy, Inc.

SDAM analysis areas

SDAM is broken down into the following general analysis areas:

• Inquiry analysis

Inquiry analysis looks at the initial stage of the business process, measuring inquiry activity through the organization, materials, and the customer base.

For inquiry reports, see Chapter 3, Inquiry Analysis.

• Quotation analysis

Quotation analysis focuses on quotation processing, including measuring activity based on the different business hierarchies and efficiency converting inquiries into quotations.

For quotation reports, see Chapter 4, Quotation Analysis.

• Sales Order analysis

Sales Order analysis focuses on sales transactions from different points of view. It includes measuring overall sales activity, understanding sales through the customer base to identify customer segments, analyzing materials sales, and measuring efficiency converting quotations into sales orders.

For sales order reports, see Chapter 5, Sales Order Analysis.

• Delivery analysis

Delivery analysis looks at how goods are delivered to customers, including tracking open deliveries and analyzing delivery activity and processing times.

For delivery reports, see Chapter 6, Delivery Analysis.


© 2010 MicroStrategy, Inc. SDAM report features 5

• Sales and Delivery Service analysis

Sales and Delivery Service analysis focuses on all the service activities related to the fulfillment of sales and delivery transactions. This includes analyzing backlog, quality of delivery service, and returns.

For sales and delivery service reports, see Chapter 7, Sales and Delivery Service Analysis.

SDAM report featuresSDAM is designed so that you can use the packaged reports with data coming from ERP systems, such as SAP, PeopleSoft, and JD Edwards. SDAM is built using the MicroStrategy business intelligence platform and takes advantage of the rich reporting and analytical features of MicroStrategy products.

• Data breadth and depth: The SDAM reports model all pre-sales, sales, post-sales, and delivery activities covering the complete sales and distribution business process. They allow you to track activity at the transaction level, including the item detail for each transaction. For example, sales order reports track the different materials sold as well as facts such as item quantity, item amount, and so on. Outbound delivery reports track the different materials sold, facts such as item delivered and quantity, and attributes such as shipping date, delivery date, and so on.

• Modularity: SDAM divides the analytical domain into two main areas, one tracking all pre-sales and sales activities and the other tracking all delivery activities. These areas are independent and can be implemented separately. Since reporting areas are aligned with one of the data areas, only the corresponding data area is required to take full advantage of SDAM reports.

• Reporting flexibility: Prompts allow you to define report templates dynamically and select filter conditions to be used at run time.

• Rich formatting: Reports use advanced MicroStrategy formatting features, such as thresholds to highlight important trends, page-by to move quickly to summary information, graphs to display results three-dimensionally or as lines and bars, and so on.

• Segmentation and custom grouping capabilities: You can define sets of elements (segments) that meet certain conditions and view data on the defined segments. For example, you can identify customers that bought material A and customers that did not buy material B, and then identify a set of customers that meets both criteria for a cross sell campaign.


6 Introduction to reports, dashboards, and documents © 2010 MicroStrategy, Inc.

• Ad hoc query: You can create additional reports based on existing attributes or metrics, or create new reports to address specific reporting needs.

• Dashboards: You can combine data from different sources into one HTML document and display the data graphically at a high level, making it visually appealing and easy to understand. You can combine grids, graphs, text, and special dashboard images to create a quick overview of key performance indicators (KPIs).

• Scorecards: You can combine several reports into one HTML document, including different display modes such as graph or grid. See About scorecards and dashboards in this chapter for more information.

• Report Services documents: A document is a set of data with extensive formatting properties applied. When creating a document, you can specify what data appears, control how it will be laid out and formatted, and how it should be grouped. You can insert pictures and draw borders in the document. All of these capabilities allow you to create reports that are suitable for presentation to management or printing for boardroom quality material.

• Advanced metrics: Advanced metrics include, for example, Historical Trends which compares sales activity from one month to the previous month, or Contributions which measures the weight of a group of materials (products and services) on overall material sales.

• Advanced drilling: You can navigate (or drill) from highly summarized information down to attributes and other data details.

For a more in-depth introduction to MicroStrategy query and reporting features as well as an interactive tutorial, see the MicroStrategy Basic Reporting Guide.

Introduction to reports, dashboards, and documents

This reference guide describes the reports and documents that summarize the SDAM analysis areas. This guide also describes the general analysis areas within SDAM and presents the reports within each analysis area that come with SDAM.


© 2010 MicroStrategy, Inc. Introduction to reports, dashboards, and documents 7

Each general analysis area provides a short introduction to the area, explaining the analysis challenges and presenting the list of packaged reports that belong to that general reporting area.

For each report, scorecard, or document, this guide provides:

• A description of its business value

• Typical usage scenarios

• A screen shot with sample data

The sample data warehouse that supplies information for the sample reports is updated regularly. As a result, data that appears in images in this guide may not always coincide exactly with the data returned for a sample report in the software.

• A description of how data is qualified

• Typical drill paths

• Tips about how the report, scorecard, or document can be customized and extended to fit user requirements

Report Services documents can only be displayed in a server connection (a three-tier environment). You must be connected to MicroStrategy Intelligence Server. Additionally, to display a Report Services document, you must have MicroStrategy Report Services. See the MicroStrategy Installation and Configuration Guide for information on connecting to Intelligence Server and the MicroStrategy Report Services Document Creation Guide for details on Report Services documents.

Accessing reports

All the SDAM reports and scorecards are stored in the Reports folder of the Sales and Distribution Analysis Module project.

To access reports

1 From the Windows Start button, point to Programs, then MicroStrategy, then Desktop, and select Desktop.



2 Enter your user name and password, and click OK. The default user name is Administrator with the password field left blank. If your system administrator has changed this, contact that person to get a user name and password.

3 From the Folder List area, expand the Analytics Modules.

4 Expand the Sales and Distribution Analysis Module.

5 Expand Public Objects, and expand Reports. All SDAM reports and scorecards are located in this Reports folder.

About scorecards and dashboards

Scorecards and dashboards are popular ways to display and distribute data from business intelligence projects. In MicroStrategy, dashboards and scorecards are Report Services documents.

A Report Services document is a set of data and formatting properties that, when executed, can produce an attractive presentation-quality report. For more information on Report Services documents, see About Report Services documents, page 9.

The information in this section is intended as a brief overview of scorecards and dashboards.

Scorecards

Scorecards provide you with a high-level summary of key performance indicators (KPIs) for a given analysis area. They accomplish this by displaying data of actual performance compared to planned targets and benchmarks.

Scorecards are made up of several combined base reports. Scorecards are a good starting point for any Analysis Module because scorecards comprise the most common or most important reports within a given analysis area. From the data returned on a scorecard, you can formulate ideas for further, more detailed exploration. See Accessing Reports in this chapter for steps to open an SDAM scorecard.


© 2010 MicroStrategy, Inc. Introduction to reports, dashboards, and documents 9

Scorecards are laid out to help you with the information-gathering process. The most general information is found at the top left of the scorecard, and the reports get more detailed as you move left to right, top to bottom. SDAM comes with several scorecards, which are described in chapter 2.

Dashboards

Dashboards, like scorecards, are made up of a group of reports and metrics that are tied together by business logic. Scorecards typically follow a specific methodology and focus on key metrics within a business area. Dashboards, on the other hand, also provide key metrics but tend to summarize business intelligence information more widely.

Dashboards allow you to display information customized to your audience. Audiences might include executives, managers, or particular groups within the company such as Marketing or Sales. In some cases, you may choose to provide information to partners or customers. Among other things, dashboards let you:

• Add interactivity to a document, so that analysts can select various options directly on the document to display the specific data they are interested in

• Choose a uniform coloring scheme or display information using your company’s standard Web page style

• Add links wherever necessary

• Choose the appropriate graphs, images, and colors for better presentation and to highlight specific data, trends, and deviation from certain trends, as well as to provide visual cues

• Share the dashboard using Desktop, Web, or via e-mail using Narrowcast Server

About Report Services documents

A MicroStrategy Report Services document is a set of data and formatting properties that, when executed, can produce an attractive presentation-quality report. There are several types of Report Services documents, including the following:



• Scorecards and dashboards: Designed for visual impact, these documents provide a distilled view of the business, organized in adaptive sections or zones.

• Managed metrics reports: With a quantitative focus, these documents present lists of metrics or process-driven tabular views of the business.

• Production and operational reports: Designed for production operations management, these documents present data in hierarchical categories or bands and can span across hundreds of printed pages.

• Invoices and statements: These documents use the transaction and sub-transaction level data necessary for billing, collection, and customer service.

• Business reports: Designed for financial and other statutory business reporting, these documents present comprehensive data in print and electronic forms.

See the other MicroStrategy Analytics Modules and the MicroStrategy Tutorial for examples of other types of Report Services documents.

Accessing documents

To open a document that has already been created

1 From a project in MicroStrategy Desktop or Web, navigate to the folder containing the document.

2 Do one of the following depending on which interface you are using:

• In MicroStrategy Web, click the document name or icon. The document displays.

• In MicroStrategy Desktop, double-click the document name or icon. It opens in PDF View.

For complete details on viewing, creating, and working with documents, see the MicroStrategy Document Creation Guide.


© 2010 MicroStrategy, Inc. About the definitions for objects on reports 11

About the definitions for objects on reportsThe objects used in the SDAM reports are presented in glossaries in Appendix A of this reference guide. The glossaries include descriptions of the public objects (metrics, filters, and prompts) that make up SDAM. For details, see Definitions for Objects on Reports: Object Glossaries, page 139 in Appendix A.

For additional details on any SDAM object, see the MicroStrategy project definitions in the Schema Objects and Public Objects folders.

About the structure of SDAM’s business logicSDAM’s logical data model (sometimes also called the multidimensional model) is presented in Appendix B of this reference guide. The logical data model is the group of attributes (business concepts) and facts (measures, or logical pieces of data that can be analyzed) that make up an Analysis Module, arranged in specific sequences to reflect a business structure or business logic. A logical data model also shows how that business logic maps to the physical schema (data elements in a data warehouse). For details on logical data modeling, see the data modeling section of the MicroStrategy Installation and Configuration Guide.

The logical schema diagram is available in an Erwin file, located in Program Files\MicroStrategy\Analytics Modules\Sdam\SDAM.er1. For additional details, see Structure of SDAM’s Business Logic: Logical Data Model, page 167 in Appendix B.

The documentation assumes you have prior experience with logical data modeling and creating business intelligence applications using MicroStrategy technology.

Business concepts and measurements

Business concepts, such as year or customer, are generally represented in MicroStrategy as attributes. Measurements, such as profit or revenue, are usually represented as facts.


12 About the structure of SDAM’s business logic © 2010 MicroStrategy, Inc.

To get full details about any attribute or fact in any Analysis Module, use the Attribute Editor. You can view such information as definitions, properties, mapping methods, source tables, and so on.

To view attribute or fact details

1 From MicroStrategy Desktop, open the Sales and Distribution Analysis Module.

2 Open the Schema Objects folder.

• Attributes can be found in the Attributes folder.

• Facts can be found in the Facts folder.

3 Double-click the individual attribute or fact. The Attribute Editor automatically opens and displays details about the selected object.

Relationships among business concepts

The attributes and facts in the logical data model are arranged in a specific sequence to reflect business logic. This arranged structure is called a hierarchy (sometimes also called a dimension).

The analysis hierarchies for each module include a number of hierarchies through which data can be summarized, such as organization, material (product or service), time, and customer.

Default analysis hierarchies can be easily customized and extended to support analysis structures required for each company. See the Logical Data Model appendix and Physical Schema and Data Dictionary appendix in this guide for additional details.

Porting an analytics module to your data source

The MicroStrategy Analytics Modules are designed around the concept of portability. Portability is the ability of an analytical application to be integrated into an existing data warehouse. Porting a given analytical application involves remapping the application’s logical data model to an existing physical data warehouse schema while retaining the logical data model definition.


© 2010 MicroStrategy, Inc. Introduction to SDAM’s physical data storage 13

The MicroStrategy Analytics Modules are designed to be independent from data structures. SDAM’s logical data model and reports can work with the physical data schema they come with, or with almost any other physical schema (such as your own data warehouse) containing the same or similar data elements. See the Installation and Porting Guide for more information on porting and detailed steps to use this feature.

Introduction to SDAM’s physical data storageThe physical schema for SDAM’s default data warehouse is presented in Appendix C, Data Storage: Physical Schema and Data Dictionary of this reference guide:

• You can use this default warehouse as a starting point for creating your own data warehouse in the sales and distribution analysis area.

• You can use it as a template for building analytical applications.

• You can port SDAM’s logical data model to your own existing data warehouse.

See the Installation and Porting Guide for details on any of these three approaches to the Analytics Modules.

This reference guide contains the physical schema diagram for the default data warehouse and also provides descriptions of all the tables and columns in the default data warehouse, as well as their purposes.

The default physical schema definition is also available in an Erwin file, located in Program Files\MicroStrategy\Analytics Modules\ Sdam\SDAM.er1.

The documentation assumes you are familiar with basic RDBMS concepts and Erwin data modeling.


22.SDAM SCORECARDS AND DOCUMENTS

Introduction

Scorecards provide a high-level summary of key performance indicators (KPIs) for a given analysis area. They accomplish this by displaying actual performance data compared to planned targets and benchmarks. Scorecards are made up of several base reports (called datasets) that, when combined, provide you with a general overview of a given analysis area. These datasets are displayed in a MicroStrategy Report Services document.

A document is a set of data and formatting properties that, when executed, display an attractive, presentation-quality report. When creating a document, you can specify what data appears, control its layout and formatting, and how is grouped. You can insert pictures and draw borders in the document. All of these capabilities allow you to create reports that are suitable to present to management or to print as boardroom quality material. A dashboard is a type of document in MicroStrategy.

SDAM Scorecards and Documents Sales & Distribution Analysis Module Reference2

16 Scorecards and dashboards © 2010 MicroStrategy, Inc.

This chapter provides information, usage scenarios, screen shots, and details for the Sales and Distribution Analysis Module’s (SDAM) scorecards and documents. The scorecards and documents are available in the SDAM Reports folder; see Accessing reports, page 7 and Accessing documents, page 10.

Report Services documents can only be displayed in a server connection (a three-tier or four-tier environment). You must be connected to MicroStrategy Intelligence Server. Additionally, to display a Report Services document, you must have the MicroStrategy Report Services product. See the MicroStrategy Installation and Configuration Guide for information on connecting to Intelligence Server and the MicroStrategy Report Services Document Creation Guide for details on Report Services documents.

Scorecards and dashboards

Executive Sales Scorecard

This scorecard provides a summary of sales activity for a given year, including measuring volumes, monthly trends, and identifying top customers and materials.

Sales & Distribution Analysis Module Reference SDAM Scorecards and Documents 2

© 2010 MicroStrategy, Inc. Scorecards and dashboards 17

Scorecard layout and display

The datasets included in this scorecard are:

• Quarterly Sales by Sales Organization

• Sales Activity Summary

• Top 5 Customers by Net Sales Amount

• Top 5 Materials by Net Sales Amount



Usage scenarios

Executives and sales managers can use this scorecard to see a general overview of sales activity including sales volumes, historical trends, and the top products and customers for the selected year. Users are prompted to select a year for which to analyze data.

Scorecard details

• Qualification: You are prompted to select a year to analyze. The scorecard displays sales activity data for the selected year. This selection applies to all datasets that make up the scorecard.

• Other options: You can create scorecards similar to this one to summarize sales activity for a given sales organization, sales office, or distribution channel, instead of summarizing data for the whole company.

Inquiry to Shipment Process Document

This document helps you analyze sales deals and sales amounts for different stages of the inquiry-to-shipment process. The document presents a visual representation of the sales and distribution process to help you understand how each stage relates to the others. It also includes a number of metrics that measure overall performance for captured and lost sales.




This document includes the following datasets:

• Inquiry to Shipment (Amounts)

• Inquiry to Shipment (Amounts) - YTQ

• Inquiry to Shipment (Counts)



Usage scenarios

Executives can use this document to gain an understanding of overall sales activity, from generating inquiries to delivering goods to customers. They can also use the data to analyze KPIs that measure the performance of the sales organization:

• Qualifications: This document includes a prompt that allows you to select a specific quarter for which to display data. You can modify the prompt to compare data at different time levels, such as current month vs. year-to-month, or month-to-date vs. year-to-date.

• Dataset details and metric definitions: For details about the datasets and metrics used in this document, see the MicroStrategy project folder Public Objects\Reports\Scorecards and Dashboards\ Datasets.

• Additional details: The document also includes a Notes section with definitions for the KPIs that appear in the document.

Outbound Delivery Activity Scorecard

This scorecard summarizes outbound delivery activity for a given year, including delivery summary, activity by shipping point, and quality of service. (The image below is cut short; the graph at the bottom is fully displayed in Desktop and Web.)





• Outbound Delivery Activity by Shipping Point

• Outbound Delivery Activity Summary

• Outbound Delivery Service Summary by Shipping Point



Usage scenarios

Executives and delivery managers can use this scorecard to get a general overview of delivery activity, including delivery volumes, processing times, historical trends, contributions, and quality of delivery service.

Scorecard details

Users are prompted to select a year to analyze. The scorecard displays delivery activity for the selected year. This selection applies to all datasets that are part of the scorecard.

Purchase Order Document

The Purchase Order document displays a generated purchase order, ready to be printed and sent to customers. Each purchase order displays all available information about an order, including item quantities and total amounts. It also includes a form for customers to insert their authorization information and shipment requirements.



Document display

This document includes the following datasets:

• Purchase Orders

• Totals - Purchase Orders



Usage scenarios

Sales representatives can generate this document as needed and send it electronically to customers. Customers can easily return the document by fax to accept the conditions and to initiate the shipping and billing processes.

Document details

• Qualifications: This document includes a static filter that selects limited data for the purpose of creating the example above. You should modify the filter to allow users to select dynamic options such as time periods, specific customers, a specific order, and so on.

• Dataset details and metric definitions: For details about the datasets and metrics used in this document, see the MicroStrategy project folder Public Objects\Reports\Scorecards and Dashboards\ Datasets.

• Additional details:

This document provides a high-quality printed purchase order.

The document also includes a form for gathering customer purchase, authorization, and shipping information.

Sales and Delivery Report for Material Scorecard

This scorecard summarizes sales and delivery activity for a given material.





• Material Sales by Sales Organization

• Outbound Delivery Summary by Shipping Point

• Quarterly Sales Summary



Usage scenarios

Product managers can use this scorecard for a complete overview of sales and delivery activity for a specific material, including sales volumes, distribution by sales organization, and delivery activity by shipping point. You are prompted to select a material for which to see data.

Scorecard details

You are prompted to select a material to analyze. These selections apply to all datasets that are part of the scorecard.

Sales and Distribution Dashboard

This dashboard provides a high level overview of sales and outbound delivery activities for a given year and material. The dashboard is organized into three tabs; each tab contains a scorecard that covers a specific area of sales and distribution activity. Click any of the tabs to view a scorecard. The scorecard details are as follows:

• Sales: This scorecard provides a summary of sales activity for a given year, including measuring volumes and monthly trends, and identifies top customers and materials.

• Material: This scorecard summarizes sales and delivery activity for a given material.

• Delivery: This scorecard summarizes outbound delivery activity for a given year, including delivery summary, activity by shipping point, and quality of service.



Dashboard layout and display

The datasets included in this dashboard are:

• Quarterly Sales by Sales Organization


• Top 5 Customers by Net Sales Amount

• Top 5 Materials by Net Sales Amount



• Outbound Delivery Activity by Shipping Point

• Outbound Delivery Activity Summary


• Material Sales by Sales Organization

• Outbound Delivery Summary by Shipping Point


Usage scenarios

Use this interactive dashboard to view summary data related to sales, material, and outbound delivery. Click any of the tabs at the top to view the relevant data. For example, if the Sales tab is selected, the executives and sales managers can view the sales activities, sales volumes, historical trends, and top products and customers for the selected year.

Dashboard details

You are prompted to select a year and material to view the summarized data. These selections are applicable to all datasets that are part of the scorecard.

© 2010 MicroStrategy, Inc. Inquiry Analysis area 29

33.INQUIRY ANALYSIS

Introduction

This chapter provides reference information for the reports that come with the Sales and Distribution Analysis Module (SDAM) as part of SDAM’s Inquiry analysis area. It presents detailed information on each report in this analysis area, including usage scenarios, screen shots, and reporting details. The reports are available in the SDAM Reports folder; see Accessing reports, page 7.

For information on the attributes, metrics, prompts, and filters included in the reports, see Appendix A, Definitions for Objects on Reports: Object Glossaries.

Inquiry Analysis areaInquiry analysis focuses on understanding information related to the initial stage of the business process, customer inquiries.

The Inquiry analysis area is divided into the following subsections, each with its own focused reports:

Inquiry Analysis Sales & Distribution Analysis Module Reference3

30 Open Inquiries reports © 2010 MicroStrategy, Inc.

• Open Inquiries analysis monitors inquiries with an open status.

• Inquiry Analysis focuses on inquiry processing through the organization, material, and customer hierarchies.

Open Inquiries reportsThe Open Inquiries area includes reports that monitor inquiries that are being qualified and may become a quotation. These reports include summaries that measure the number of open inquiries and inquiry items, and a report that tracks individual inquiry details.

Managers and operational users can use these reports to continuously track the inquiry process.

The reports included in this subsection are:

• Summary Open Inquiries by Organization

• Summary Open Inquiries by Material

• List of Open Inquiries by Sales Office

Summary Open Inquiries by Organization

This report displays open inquiries, and groups them by any of the organization’s units.

Usage scenarios

Sales managers can use this report to determine which organizational units have a larger number of open inquiries.

Report layout and display

Sales & Distribution Analysis Module Reference Inquiry Analysis 3

© 2010 MicroStrategy, Inc. Open Inquiries reports 31

Report details

• Qualification:

The report includes an embedded filter named Open Status (Sales Document), and only displays inquiries that have a sales document status of Open.

You are prompted to select one or more organization attributes to be displayed in the report.

• Drill path: You can drill to other organizational units. You can also drill to Material attributes to identify which material is the most requested in each organizational unit.

• Other options: You can create a similar report that analyzes by customer characteristics; substitute the object prompt with one selecting Customer attributes.

Summary Open Inquiries by Material

This report displays open inquiries grouped by material.

Usage scenarios

Product managers can use this report to identify the most requested materials, as well as total and average requested quantities by material.



32 Open Inquiries reports © 2010 MicroStrategy, Inc.

Report details

• Qualification: The report includes an embedded filter named Open Status (Sales Document), and only displays inquiries that have a sales document status of Open.

• Drill path: You can drill down to identify customers and/or inquiries for a specific material.

• Other options: You can add a prompt qualification to display materials by Product hierarchy or Material division.

List of Open Inquiries by Sales Office

This report displays all open inquiries, grouped by sales office and sales group.

Usage scenarios

Sales managers and salespeople can use this report to identify all details for open inquiries, including the associated sales group and customer, and the materials and quantities requested.


The page-by Sales Office option displays open inquiries corresponding to the selected sales office, all on a single page.

Report details

• Qualification: The report includes an embedded filter named Open Status (Sales Document), and only displays inquiries that have a sales document status of Open.


© 2010 MicroStrategy, Inc. Inquiry Analysis reports 33

• Drill path: Because this is a detailed report at the transaction level, no drill path is recommended.

• Other options: You can add additional details to the report, such as document item status.

Inquiry Analysis reportsThe Inquiry analysis subsection presents all reports that analyze inquiry activity, including a summary report measuring inquiry activity by organization, a report analyzing historical trends by material, and a contribution analysis to identify elements with greater weight on overall activity.

Executives and managers can use these reports to analyze inquiry activity. Analysts can use the reports to understand what drives inquiry generation.

The reports in this subsection are:

• Quarterly Inquiry Activity by Organization

• Monthly Inquiry Activity Trends by Material

• Monthly Inquiry Distribution by Customer Region

Quarterly Inquiry Activity by Organization

This report summarizes quarterly inquiry activity by any of the organization units, such as sales office, sales group, or distribution channel.

Usage scenarios

Sales managers can use this report to measure inquiry activity throughout the different organizational units, and identify the most active ones.


34 Inquiry Analysis reports © 2010 MicroStrategy, Inc.


The page-by Quarter option summarizes inquiry activity for the selected quarter.

Report details

• Qualification:

You are prompted to select a year. The inquiries displayed are those that were created during the selected year.

You are prompted to select one or more organization attributes to be displayed in the report.

• Drill path: You can drill to month, other organization units, or Material attributes to display the breakdown by material.

• Other options:

You can create a report that analyzes open inquiries by customer characteristics; substitute the object prompt with a prompt selecting customer attributes.

You can replace Quarter by Organization attributes in the page-by option. Then you can display the report using a graph to analyze quarterly trends in inquiry activity for the different organizational units.

Monthly Inquiry Activity Trends by Material

This report displays inquiry activity by month, and measures inquiry trends based on material.


© 2010 MicroStrategy, Inc. Inquiry Analysis reports 35

Usage scenarios

Product managers can use this report to identify the most requested materials, and to evaluate inquiry trends by comparing the change in the number of times a material is requested against the change in requested quantities.


The page-by Month option summarizes monthly activity for different materials.

Both of the % Change Inquiries columns include a threshold that highlights data when a trend is negative. These values appear with a red background.

Report details

• Qualification: You are prompted to select a quarter and a product to analyze.

• Drill path: You can drill to any of the Organization or Customer attributes to display the breakdown. For example, to analyze inquiry distribution of a material by customer region, you would select a material to analyze and drill on Customer Region.

• Other options: You can display information grouped by any of the Material attributes, such as the Product hierarchy, instead of displaying information at the material level, which is the lowest level in the Material hierarchy.


36 Inquiry Analysis reports © 2010 MicroStrategy, Inc.

Monthly Inquiry Distribution by Customer Region

This report displays the distribution of inquiries based on a customer’s geographical region.

Usage scenarios

Sales managers can use this report to identify which geographical areas have larger inquiry activity and contribute more to overall inquiries.


Report details

• Qualification: You are prompted to select a quarter to analyze.

• Drill path: You can drill to any other Customer attributes to identify customer profiles with larger inquiry activity.

• Other options: You can display information grouped by any of the other Customer attributes. To do this you must define a Contribution metric to support contribution analysis by any of the customer characteristics.

© 2010 MicroStrategy, Inc. Quotation Analysis area 37

44.QUOTATION ANALYSIS

Introduction

This chapter provides reference information for the reports that come with the Sales and Distribution Analysis Module (SDAM) as part of SDAM’s Quotation analysis area. It presents detailed information on each report in this analysis area, including usage scenarios, screen shots, and reporting details. The reports are available in the SDAM Reports folder; see Accessing reports, page 7.


Quotation Analysis areaThe Quotation Analysis area focuses on the quotation process, which involves creating a price quote for a customer by offering materials with certain price conditions.

Quotation Analysis Sales & Distribution Analysis Module Reference4

38 Quotation Analysis area © 2010 MicroStrategy, Inc.

Quotations can be processed based on a preceding customer inquiry, if the customer requested information (an inquiry) about the company’s materials before a quotation was prepared. The inquiry request is then qualified as a possible sales opportunity and becomes the source of a price quotation. In SDAM, this type of quotation includes a reference to the preceding inquiry. The preceding inquiry is called a reference document. Quotations can also be processed without a prior inquiry. In this case, a salesperson generates a quotation based on their knowledge of the customer or a customer directly requests a quotation.

The Quotation analysis area is divided into the following subsections, each with its own focused reports:

• Open Quotations analysis: This area helps you monitor all quotations with an open status.

• Quotation analysis: This area helps you understand quotation-processing performance by organization, material, and customer, including analyzing historical trends and geographical contributions.

• Inquiry to Quotation Conversion analysis: This area helps you measure the organizational performance of converting customer inquiries into quotations.

• Customer Quotation History analysis: This area helps you track quotation histories for individual customers.

Product hierarchy example

As part of the material hierarchy, the product hierarchy exists to group products with similar features. For example, the Summary Open Quotations by Product Hierarchy report in this chapter displays open quotations based on product. The product hierarchy levels allow you to expand this report so you can see more specific lists of products, or to collapse the report to see broader, more general lists of products.

The product hierarchy contains the following levels:

• Zero (0): The lowest, most specific, or detailed level

• One (1): The intermediate level

• Two (2): The highest, most general, or most inclusive level

Sales & Distribution Analysis Module Reference Quotation Analysis 4

© 2010 MicroStrategy, Inc. Open Quotations reports 39

Using a computer store as an example, as shown in the following figure, Product Hierarchy Level 2 might include PCs, Servers, Storage, Printers, and Peripherals and Supplies as elements of level 2.

Each of these level 2 elements contains a more specific list of products at the next lowest product hierarchy level. For example, Product Hierarchy Level 1 under Servers might include Tower Servers, Rack Servers, and Server Solutions.

Each element of level 1 contains the most specific list of products at the very lowest level. For example, Product Hierarchy Level 0 under Tower Servers might include High-Performance Tower Servers and Standard Tower Servers.

The following figure shows the example described above:

Open Quotations reportsThis subsection focuses on analyzing quotations that have been sent to customers and are awaiting customer response. This includes all reports with a status of “Open,” including two summary reports measuring the number of open quotations and quotation amounts by organization or materials, and a report at the transaction level to track quotation details.

Managers and operational users can use these reports to continuously track the quotation process, and to initiate actions that drive further sales orders.



40 Open Quotations reports © 2010 MicroStrategy, Inc.

• Summary Open Quotations by Organization

• Summary Open Quotations by Product Hierarchy

• List of Open Quotations by Customer

Summary Open Quotations by Organization

This report displays the distribution of open quotations throughout the organization, sorted by sales organization, sales office, and sales group.

Usage scenarios

Sales managers can use this report to determine which sales offices have a larger pipeline, as measured by the open quotations net amount, and can allocate resources accordingly.


Report details

• Qualification: This report includes an embedded filter named Open Status (Sales Document), and only displays quotations with an Open status.

• Drill path:

You can drill to customer characteristics, such as customer region which allows you to identify more active regions.

You can drill down to transaction details, identifying individual customers and quotations assigned to a specific sales group.



• Other options:

You can add contribution metrics to identify which organizational units have the biggest weight in the overall sales pipeline.

You can create a report to analyze open quotations based on customer characteristics, such as customer region.

Summary Open Quotations by Product Hierarchy

This report displays open quotations based on product.

Usage scenarios

Product managers can use this report to analyze open quotations by product. They can drill to materials to identify those materials with a larger pipeline.


42 Open Quotations reports © 2010 MicroStrategy, Inc.


This report appears in Outline mode, which lets you view summarized information by the highest product hierarchy level. You can also expand one or more levels in the product hierarchy to see the Material detail. The screen shots here show examples of the reports with and without the levels expanded. See Product hierarchy example above for a detailed explanation of the product hierarchy.

Report details

• Qualification: This report includes an embedded filter named Open Status (Sales Document), and only displays quotations with an Open status.

• Drill path: Because this report allows you to explore data at the Material level by expanding report elements, no drill path is recommended.



• Other options: You can add a prompt to filter materials by Product hierarchy or Material Division.

List of Open Quotations by Customer

This report displays open quotations for a specific customer or a group of customers.

Usage scenarios

Salespeople can use this report to track open quotations within their customer portfolios.


Report details

• Qualification:

You are prompted to select one or more customers to analyze.

This report includes an embedded filter named Open Status (Sales Document), and only displays quotations with an Open status.

• Drill path: This is a detailed report at the transaction level, so no drill path is recommended.

• Other options:

You can add additional details to the report, such as Reference Document to display quotations that have an associated inquiry, or Document Item Status to display quotations with a specific status such as Open, Complete, and so on.


44 Quotation Analysis reports © 2010 MicroStrategy, Inc.

You can create a report that identifies all open quotations for a sales office or sales organization. For example, you can add Sales Office to the report with the page-by option, and replace the Customer prompt with a prompt that selects one or more sales offices.

Quotation Analysis reportsThis area focuses on analyzing quotation-processing performance by organization, material, and customer, including analyzing historical trends and geographical contributions.

The reports in this area help you:

• Summarize quotation activity over time, based on organization units or materials

• Analyze historical trends

• Analyze contributions to identify those elements that contribute a greater weight to overall quotation activity

Executives and managers can use these reports to monitor inquiry activity and identify historical trends. Analysts can use the reports to evaluate which organizational units, customer segments, or materials have greater quotation activity, and to identify patterns to explain it.


• Quotation Activity Summary

• Quarterly Quotation Activity Summary by Organization

• Quarterly Quotation Activity Summary by Material

• Monthly Quotation Activity Trends by Material

• Quotations Distribution by Customer Region

• Monthly Quotation Contribution and Trends by Distribution Channel

• Material Contribution to Product Hierarchy Quotation Amounts

• Quarterly Rejected Quotation Items by Organization


© 2010 MicroStrategy, Inc. Quotation Analysis reports 45

Quotation Activity Summary

This graphical report displays a summary of quotation activity for a given year.

Usage scenarios

Sales executives and managers can use this report to gain an overview of quotation activities over time, and to identify historical trends.


This report appears as a graph with split axes. The two axes display the results of metrics using different scales.

Report details

• Qualification: You are prompted to select a year to analyze. The quotations displayed reflect only those quotations created during the selected year.



• Drill path: If you display the report in grid mode, you can drill to any organizational, material, or customer attribute to display the breakdown in monthly trends.

Quarterly Quotation Activity Summary by Organization

This report displays a summary of quotation activity, based on a selected organization unit, such as sales group, sales office, or distribution channel.

Usage scenarios

Sales managers can use this report to analyze a complete set of metrics measuring quotation activity, such as the number of quotations, the net quotation amount, the average amount per quotation, and so on.


The page-by Quarter option consolidates information for a selected quarter.

Report details

• Qualification:

You are prompted to select one or more organization units to be displayed in the report.

You are prompted to select the year to analyze.

• Drill path: You can drill to any of the other organization units, or to customer characteristics.



• Other options:

You can display a monthly summary by substituting Quarter with Month in the page-by option, and by changing the prompt to select a different time period.

You can create a report using customer attributes instead of organization attributes to group the information; add an object prompt to select customer attributes at run time.

Quarterly Quotation Activity Summary by Material

This report displays a summary of quotation activity based on a selected group of materials.

Usage scenarios

Product managers can use this report to analyze a complete set of metrics measuring activity at the material level, such as the number of quotation items, the average net amount per quotation item, or the quotation item quantity.


The page-by Quarter option consolidates information for the selected quarter.

Report details

• Qualification:

You are prompted to select one or more material groups to be displayed in the report.



You are prompted to select a year to analyze. The report displays only those quotations created during the selected year.

In this report, the number of quotations identifies the number of times a material is included in a quotation. Because a single quotation may include several materials, the total can reflect double-counting of quotations.

• Drill path:

You can drill down to other levels in the product hierarchy, or to Month.

You can drill down to Material to identify activity at the lowest level.

• Other options: You can use Month instead of Quarter to analyze monthly activity.

Monthly Quotation Activity Trends by Material

This report displays a detailed view of quotation activity at the material level.

Usage scenarios

Product managers can use this report to identify which materials are more actively quoted, and to analyze historical trends.




The % Change columns include a threshold that highlights data when values are greater than zero. These values appear with a green background.

Report details

• Qualification:

You are prompted to select a quarter to analyze. The report displays those quotations created during the selected quarter.

You are prompted to select the materials to analyze.

• Drill path: You can drill to organizational units to display the breakdown by organization.

• Other options: You can add material grouping attributes to the report, such as material division or category, instead of displaying the report at the material level.



Quotations Distribution by Customer Region

This report provides a graphical representation of the relative weight of each customer region on the total quotation amounts for each quarter.

Usage scenarios

Sales managers can use this report to identify how sales are distributed throughout the different customer regions.

In a graph that uses percentage representation, the columns do not represent absolute values. Each segment represents the percentage weight of one customer region compared to the total.




Report details

• Qualification: You are prompted to select a year to analyze. The report displays those quotations created during the selected year.

• Drill path: You can drill to other customer or organization attributes.

• Other options: Similar reports can be created using organizational attributes, such as sales organization, distribution channel, or sales office.

Monthly Quotation Contribution and Trends by Distribution Channel

This report displays the contribution of each distribution channel, such as direct sales, wholesalers, and so on, to the overall net quotation amount.

Usage scenarios

Sales managers and distribution channel managers can use this report to identify distribution channels with larger contributions, and to analyze monthly activity trends


The page-by Month option consolidates information for the selected month.

Report details

• Qualification: You are prompted to select a year to analyze. The report displays those quotations created during the selected year.



• Drill path: Because this report calculates contributions at the distribution channel level, no drill path is recommended.

• Other options:

You can create similar reports by calculating contributions by other organizational attributes, such as sales organization and sales office. To do this you must create a contribution metric for the new attribute.

Contributions can also be measured based on the number of quotations instead of quotation amounts.

Material Contribution to Product Hierarchy Quotation Amounts

This report displays how materials contribute to the overall quotation amount for their corresponding product hierarchies.

Usage scenarios

Product managers can use this report to identify which materials contribute a higher quotation amount percentage to their corresponding product hierarchies.




Report details

• Qualification: You are prompted to select a year to analyze. The report displays those quotations created during the year selected.

• Drill path: No drill path is recommended.

• Other options: You can create similar reports calculating contributions by other material grouping attributes, such as material type, material group, material pricing group, and so on. To do this you must create a contribution metric for the new attribute.

Quarterly Rejected Quotation Items by Organization

This report analyzes quotation rejections for a selected year in both absolute and relative values.

Usage scenarios

Sales managers can use this report to analyze quotation rejection numbers, and to see the quarterly trend. Higher rejection rates can identify areas where sales strategy does not match customer needs, for example, when there are issues with price or material demand.



54 Inquiry to Quotation Conversion reports © 2010 MicroStrategy, Inc.

Report details

• Qualification:

You are prompted to select one or more organizational units to be displayed in the report.

You are prompted to select the year to analyze. The reports reflects those quotations that were created during the selected year.

• Drill path: You can drill to other organization or customer attributes.

• Attributes: Rejected Quotation Items are those quotation items within a quotation that a customer rejected.

• Other options: A similar report can be created to analyze rejections by material, allowing you to identify materials with higher rejection rates.

Inquiry to Quotation Conversion reportsThis area helps you measure organizational performance in converting customer inquiries into quotations. This analysis area includes summary reports evaluating conversion performance based on organization and material attributes.

Quotations can be created directly or processed from an earlier inquiry. Conversion rates measure those quotations generated from inquiries, compared to the total number of inquiries.

Executives and managers can use the reports in this area to monitor conversion activity. Analysts can use these reports to identify patterns driving conversion.


• Quarterly Conversion Summary

• Quarterly Conversion Activity by Material

• Quarterly Conversion Activity by Organization


© 2010 MicroStrategy, Inc. Inquiry to Quotation Conversion reports 55

Quarterly Conversion Summary

This report displays the number of customer inquiries converted into quotations, and the conversion rate percentage.

Usage scenarios

Sales managers can use this report to analyze overall conversion success, and to see historical trends.



Report details

• Qualification: You are prompted to select a year to analyze. The report displays those inquiries and quotations processed during the year selected.



For example, suppose you select the year 2005 to analyze. If an inquiry was processed in 2005 (sales document creation date in 2005) and the corresponding quotation was processed in 2006 (sales document creation date in 2006), the report does not include that quotation to calculate the conversion rate.

• Drill path: You can drill to organizational attributes, such as sales organization or distribution channel, and you can drill to material attributes, such as material type.

• Other options: You can include additional attributes to analyze activity trends by organization, material, or customer characteristics.

Quarterly Conversion Activity by Material

This report displays inquiry-to-quotation conversion performance for a selected year based on material.

Usage scenarios

Product managers can use this report to analyze material conversions, identifying which materials drive conversion from inquiries to quotations.


© 2010 MicroStrategy, Inc. Inquiry to Quotation Conversion reports 57



Report details

• Qualification: You are prompted to select a year to analyze. The report includes those inquiries and quotations processed during the selected year.

• Drill path: You can drill to Month or Organization attributes to display the breakdown for the different materials.

• Other options: You can use other Materials groupings instead of Material.

Quarterly Conversion Activity by Organization

This report displays inquiry-to-quotation conversion efficiency based on a selected organizational unit.



Usage scenarios

Sales managers can use this report to analyze conversion performance for a selected year, and to identify organizational units with better conversion rates.



Report details

• Qualification: You are prompted to select one or more organization attributes to be displayed in the report. The report only includes those inquiries and quotations processed during the selected year.

• Drill path: You can drill to customer characteristics to identify which customer segments have a greater conversion rate, and to determine patterns.

• Other options:

You can create a similar report to analyze rejection by customer attributes. To do this, add an object prompt with customer attributes.

You can add quarter-to-quarter comparison metrics to highlight historical trends.


© 2010 MicroStrategy, Inc. Customer Quotation History reports 59

Customer Quotation History reportsThis analysis area helps you track quotation histories for individual customers. This area includes a report summarizing all the quotation activity for an individual customer, and a report identifying details for all quotations processed for a customer.

Salespeople and other users can use these reports to monitor historical activity for a customer or group of customers.

The reports included in this subsection are:

• Customer Quarterly Quotation Activity Summary

• Customer Quotation History Detail

Customer Quarterly Quotation Activity Summary

This report displays a summary of customer quotation activity for a selected year.

Usage scenarios

Sales managers and salespeople can use this report to view a summary of customer quotation activity for one or more customers. You can analyze customer activity and identify follow-up actions.


The page-by Customer option consolidates information for the selected customer.


60 Customer Quotation History reports © 2010 MicroStrategy, Inc.

Report details

• Qualification: You are prompted to select a customer and a year to analyze.

• Drill path: Because this is a summary report, no drill path is recommended.

• Other options: You can add sales order metrics to analyze quotations and sales activities together.

Customer Quotation History Detail

This report displays a list of all the quotations generated for a customer or group of customers.

Usage scenarios

Salespeople can use this report to track quotation histories for their customer portfolio, and to understand their customers’ future needs by analyzing the types of materials and quantities requested in the past.



© 2010 MicroStrategy, Inc. Customer Quotation History reports 61

Report details

• Qualification: You are prompted to select one or more customers to analyze.

• Drill path: Because this report displays data at the lowest level possible, no drill path is recommended.

• Other options:

You can add additional details to the report such as net price or net margin, a document status such as Open or Complete, reference document (a related, earlier inquiry), and so on.

You can add a filter qualification using Time to limit the quotations listed to those processed during a selected time period.

© 2010 MicroStrategy, Inc. Sales Order Analysis area 63

55.SALES ORDER ANALYSIS

Introduction

This chapter provides reference information for the reports that come with the Sales and Distribution Analysis Module (SDAM) as part of SDAM’s Sales Order analysis area. It presents detailed information on each report in this analysis area, including usage scenarios, screen shots, and reporting details. The reports are available in the SDAM Reports folder; see Accessing reports, page 7.


Sales Order Analysis areaThis analysis area helps you analyze the sales order process, which involves customers placing a sales order to purchase materials.

Sales Order Analysis Sales & Distribution Analysis Module Reference5

64 Open Sales Orders reports © 2010 MicroStrategy, Inc.

Sales orders can be processed based on a preceding quotation. If so, the sales order has a reference to the preceding quotation, so the quotation becomes a reference document for the sales order document. A sales order can also be placed directly into the system without a prior quotation.

Sales order transactions are automatically updated to reflect the fulfillment status based on ongoing deliveries. For a diagram of the sales order and sales order item structure, see Sales Document hierarchy, page 185 in Appendix B: Logical Data Model.

The Sales Order Analysis area is divided into the following subsections, each with its own focused reports:

• Open Sales Orders: This area monitors sales orders in process.

• Sales Analysis: This area focuses on sales activity based on the different organizational units.

• Customer Sales Analysis: This area analyzes sales activity throughout the existing customer base and identifies segments of customers with similar purchasing patterns.

• Material Sales Analysis: This analysis area focuses on sales activity based on material, including profitability analysis, historical trends, and contributions.

• Quotation to Sales Order Conversion Analysis: This area measures the efficiency of converting quotations into sales orders.

• Customer Sales Order History: This area tracks sales order histories for individual customers.

Open Sales Orders reportsThis analysis subsection presents reports that monitor sales orders being processed and pending delivery. This includes reports summarizing open sales orders by organization or material attributes, and a report that tracks sales order details at the customer level.

The delivery status of an entire order depends on the delivery status of each item in the order. Until all the items are fully delivered, the order retains a status of Open; once all the items are delivered, the order status changes to Complete (fully delivered).

Sales & Distribution Analysis Module Reference Sales Order Analysis 5

© 2010 MicroStrategy, Inc. Open Sales Orders reports 65

Managers and operational users can use the reports in this area to monitor the sales order process and take actions to ensure the future fulfillment of sales orders.

The reports in this area are

• Summary Open Sales Orders by Organization

• Summary Open Sales Orders by Material

• List of Open Sales Orders by Customer

Summary Open Sales Orders by Organization

This report displays a summary of open orders, listed by selected organizational units, such as by sales group, sales office, or distribution channel.

Usage scenarios

Sales managers can use this report to track how many open orders exist in each organizational unit, as well as view the amounts pending delivery.


Report details

• Qualification:


This report includes an embedded filter named Open Status (Sales Document), so the report displays only those orders with a Open status.

• Drill path: You can drill to other Organization attributes.


66 Open Sales Orders reports © 2010 MicroStrategy, Inc.

• Other options: You can create similar reports by replacing Sales Organization with Sales Office, Distribution Channel, or Customer Region to identify the geographical breakdown of open sales orders.

Summary Open Sales Orders by Material

This report displays a summary of currently open sales orders, by material.

Usage scenarios

Product managers can use this report to track open orders by different materials, and can view details such as quantity ordered and average net amount per item.


Report details

• Qualification: This report includes an embedded filter named Open Status (Sales Document), so the report displays only those orders with an Open status.

• Drill path: You can drill down to Organization or Customer Characteristics attributes to identify customer segments with certain buying behavior.


© 2010 MicroStrategy, Inc. Open Sales Orders reports 67

• Other options: You can change the report display to summarize information by any other of the Material attributes, such as material division or product hierarchy. This provides a summary and lets you drill to Material to analyze detail.

List of Open Sales Orders by Customer

This report displays a list of all the open sales orders, and groups them by customer.

Usage scenarios

Salespeople can use this report to identify all open orders and follow up to ensure successful completion.



Report details

• Qualification: This report includes an embedded filter named Open Status (Sales Document), so the report displays only those orders with an Open status.


• Other options: You can add a Sales Office or Sales Group prompt to allow salespeople to select only open orders within a specific geographical unit.


68 Sales Analysis reports © 2010 MicroStrategy, Inc.

Sales Analysis reportsThis analysis subsection focuses on sales activity based on the different organizational units, such as by sales office, sales group, distribution channel, and so on. Reports include summaries measuring sales activity; reports analyzing profitability, historical trends, and contributions; and a report identifying top sales orders.

Executives and managers can use the reports in this subsection to monitor sales order activity. Analysts can use the reports to understand historical trends and contributions to overall sales.




• Monthly Sales Activity by Organization

• Sales Group Contribution to Sales Office Activity

• Quarterly Profitability Summary by Sales Office

• Top 5 Sales Orders by Net Sales Amount and Quarter

Sales Activity Summary

This graphical report displays sales activity by month.

Usage scenarios

Executives and sales managers can use this report to analyze the company’s sales activity history and identify trends.


© 2010 MicroStrategy, Inc. Sales Analysis reports 69


Net Sales Order Amount is displayed in a column graph. Sales Orders is displayed in a line graph.

Report details

• Qualification: You are prompted to select a year to analyze. The report displays those sales orders created during the selected year.

• Drill path: You can drill to any of the Organization attributes to display the breakdown.

• Other options: You can add additional metrics such as Sales Order Items or Average Sales Order Amount.



Quarterly Sales Summary

This report displays a general overview of company sales activity for a selected year.

Usage scenarios

Executives and sales managers can use this report to measure sales activity using a complete set of metrics, and can also compare quarterly activity to identify historical trends.


Report details

• Qualification: You are prompted to select a year to analyze. The report displays those sales orders created during the selected year.

• Drill path: You can drill to any of the Organization attributes to display the breakdown by sales organization, distribution channel, or material division.

Drilling to Month is not recommended because the % Change Amount row is calculated at the quarter level.

• Other options:

You can create a similar report at the Month level. To do this you must replace the Quarter Comparison metrics with others calculated at the Month level.

You can add additional prompt qualifications to analyze the summary for a specific material division, sales office, distribution channel, and so on.



Monthly Sales Activity by Organization

This report displays a sales summary by month for any of the organizational units, such as sales organization, distribution channel, sales office, and so on.

Usage scenarios

Executives and sales managers can use this report to analyze sales activity for any organizational unit, including measuring monthly trends.


The page-by Month option consolidates information for the selected month.

Report details

• Qualification:

You are prompted to select one or more Organization attributes to be displayed in the report.

You are prompted to select a quarter to analyze. The report displays those sales orders created during the selected quarter.

• Drill path: Drill to any of the other Organization attributes or to customer characteristics to identify key segments in the customer base.

• Other options:

You can add prompt qualifications to segment results. For example, you can provide prompts to select a specific customer region or distribution channel.



You can create a report to analyze activity by customer characteristics, by replacing the object prompt with one selecting Customer Characteristic attributes.

Sales Group Contribution to Sales Office Activity

This report displays the contribution of each sales group to overall sales office activity.

Usage scenarios

Sales office managers can use this report to determine which sales groups contribute more to overall sales office activity, and can see the contribution trend to identify those sales groups that are improving their contributions.


The page-by Month option summarizes information based on the month selected.

The % Contribution column includes a threshold that highlights data when a sales group contribution is more than 50%. These values appear with an orange background.

The Contribution Trends column includes a threshold that highlights positive (+) or negative (-) contribution trends.



Report details

• Qualification: You are prompted to select a quarter to analyze. The report displays those sales orders created during the selected quarter.


• Other options: You can create reports analyzing contribution by other Organization attributes, such as Distribution Channel.

Quarterly Profitability Summary by Sales Office

This report displays profitability by sales office, in absolute and relative values.

Usage scenarios

Sales managers can use this report to identify which sales offices have achieved larger absolute and percentage margins, and can evaluate historical trends.




Report details

• Qualification: You are prompted to select a year to analyze.

• Drill path: You can drill to Month to display the monthly breakdown, or to any Organization attributes.

• Other options:

You can substitute Sales Office with a dynamic prompt to select any of the Organization attributes.

You can create a similar report using Customer attributes to analyze profitability by customer segment.



Top 5 Sales Orders by Net Sales Amount and Quarter

This report displays the top five sales orders generated each quarter, based on sales order net amount.

Usage scenarios

Executives and sales managers can use this report to identify the company’s top sales orders, and to compare historical trends.


Report details

• Qualification:

You are prompted to select the year for which orders are analyzed.

This report includes an embedded filter named Rank Net Sales Order Amount – Top 5 Sales Orders by Quarter.

• Drill path: You can drill to display sales order details, such as the materials in an order.


76 Customer Sales Analysis reports © 2010 MicroStrategy, Inc.

• Other options: You can add additional prompt qualifications, such as distribution channel or sales organization, to identify top orders for selected organizational units.

Customer Sales Analysis reportsThis analysis subsection focuses on sales activity throughout the existing customer base and identifies segments of customers with similar purchasing patterns. Reports offer analysis of sales activity based on customer attributes to identify customer segments with certain behaviors. One report analyzes customer loyalty and another identifies top customers.

Analysts can use these reports to identify customer segments with certain purchasing patterns, and to measure customer base loyalty.


• Quarterly Customer Base Sales Activity

• Customer Loyalty - Orders Two Consecutive Quarters

• Monthly Sales Activity by Customer Characteristics

• Top 5 Customers by Net Sales Amount and Quarter

Quarterly Customer Base Sales Activity

This report displays sales activity for your existing customer base.

Usage scenarios

Sales managers can use this report to analyze how many customers had sales orders, and to see that percentage over the total customer base.


© 2010 MicroStrategy, Inc. Customer Sales Analysis reports 77


Report details


• Drill path: You can drill to Month to display the monthly breakdown, or to customer characteristics to identify buying patterns.

• Attributes: The Registered Customers column includes all customers registered in the database, no matter when they became customers.

• Other options: You can add any Customer Characteristics attributes to the report, or provide a prompt to limit analysis to a specific customer segment.

Customer Loyalty - Orders Two Consecutive Quarters

This report displays measurements relating to the loyalty of the customer base over time.

Usage scenarios

Sales managers can use this report to analyze purchasing loyalty by identifying those customers with orders in two consecutive quarters. The report measures the weight of this type of customer compared to the total number of sales orders and total sales amounts.




Report details


• Drill path: You can drill to Organization or Customer Characteristics attributes to display the breakdown and identify loyalty patterns.

Drilling to Month is not allowed because some of the metrics are defined at the Quarter level.

• Other options: You can add prompts to filter data by Customer Characteristics or Organization attributes.

Monthly Sales Activity by Customer Characteristics

This report displays a summary of sales activity based on selected customer characteristics.

Usage scenarios

Sales managers can use this report to analyze sales distribution by customer segments, and to determine typical customer profiles.


© 2010 MicroStrategy, Inc. Customer Sales Analysis reports 79


The page-by Month option summarizes sales activity for the selected month.

Report details

• Qualification:

You are prompted to select one or more customer characteristics to be displayed in the report.

You are prompted to select the quarter for which sales orders will be analyzed.

• Drill path: You can drill to other customer characteristics or to organizational units.

• Other options: You can add prompt qualifications using Customer attributes to segment the customer base.

Top 5 Customers by Net Sales Amount and Quarter

This report displays the top five customers by net sales order amount, for each quarter in a given year.

Usage scenarios

Sales managers and executives can use this report to identify the company’s top customers.




Report details

• Qualification:

You are prompted to select a year to analyze. The report displays those sales orders created during the selected year.

This report includes an embedded filter named Rank Net Sales Order Amount – Top 5 by Quarter.

• Drill path: You can drill down to Sales Document to track different sales transactions for top customers.

• Other options: You can add additional filter conditions to identify top customers for a specific sales organization or material division.


© 2010 MicroStrategy, Inc. Material Sales reports 81

Material Sales reportsThis analysis subsection focuses on sales activity based on material, including profitability analysis, historical trends, and contributions. The reports in this analysis area include summary reports measuring sales activity by material attributes, reports analyzing historical trends, contribution analyses to identify material with greater weight in sales activity, reports evaluating material profitability, and reports identifying top or bottom products.

Analysts can use these reports to measure material sales performance, identify top products, and understand purchasing patterns and historical trends.


• Quarterly Sales Summary by Material

• Monthly Sales Activity by Material Attributes

• Monthly Sales Trends by Material

• Quarterly Sales Summary by Product Hierarchy

• Sales Organization Contribution to Material Division Sales

• Material Division Contribution to Monthly Sales

• Quarterly Product Hierarchy Sales by Material

• Margin and Price Monthly Trends by Material

• Top 5 Materials by Net Sales Amount and Quarter

• Bottom 3 Materials by % Net Margin and Quarter

Quarterly Sales Summary by Material

This report provides a complete summary of all sales activity for a selected material.

Usage scenarios

Product managers can use this report to measure material activity and its quarterly trends, using a complete set of metrics.


82 Material Sales reports © 2010 MicroStrategy, Inc.


The page-by Material option provides a summary for the selected material element.

Report details

• Qualification:


You are prompted to select one or more materials, which are elements in any level of the product hierarchy.


• Other options: You can consolidate the data based on Material attributes other than at the Material level, such as Product hierarchy or Material Division. However, be aware that some metrics, such as Net Price or Quantity, only make sense at the Material level.

Monthly Sales Activity by Material Attributes

This report provides a summary of sales performance based on any of the material groups, such as Material Type, Material Industry Sector, Material Pricing Group, and so on.

Usage scenarios

Product managers can use this report to evaluate overall sales performance for specific groups of materials, and to identify those groups of materials with higher activity, positive monthly trends, and higher net margin.




The page-by Month option summarizes sales performance information for the selected month.

Report details

• Qualification:

You are prompted to select one or more material groups to be displayed in the report.

You are prompted to select the quarter for which sales activity is analyzed.

• Drill path: You can drill to other Material attributes such as Product Hierarchy.

• Other options: You can add additional prompt qualifications using Organization attributes or customer characteristics, to analyze material activity for those specific elements.

Monthly Sales Trends by Material

This graphical report displays a summary of monthly sales activity for a selected material.

Usage scenarios

Product managers can use this report to analyze a particular material’s sales performance over time, including the number of sales orders and the total sales amount.




The page-by Material option displays sales activity for the selected material element.

Report details

• Qualification: You are prompted to select a year to analyze. The report displays those sales orders that were created during the selected year.

• Drill path: You can drill to Organization attributes to display the breakdown by organizational unit.

• Other options: You can add prompt qualifications to segment analysis for a group of materials, based on Material Division or Product hierarchy selections.



Quarterly Sales Summary by Product Hierarchy

This report displays the sales performance of selected materials, summarized based on the Product hierarchy.

Usage scenarios

Product managers can use this report to analyze sales activity and identify product lines with better performance. You can identify historical trends by comparing data for different quarters.




This report appears in Outline mode, which lets you view summarized information by the highest Product hierarchy level. You can also expand one or more levels in the Product hierarchy and get to the Material detail. The screen shots here show examples of the reports with and without the levels expanded.

The page-by Quarter option summarizes data for the selected quarter.

Report details


• Drill path: You can drill to Month or drill to Organization attributes to display the material sales breakdown by time or organizational unit.

• Other options: You can add a prompt qualification for Material attributes to filter material analyzed by Material Division, Material Category, and so on.

Sales Organization Contribution to Material Division Sales

This graphical report displays the sales organizations that contribute more to material division quarterly sales.

Usage scenarios

Division managers and sales managers can use this report to identify which sales organization contributes a greater weight to overall division sales. The report can also help to identify drivers that explain why various material divisions are more or less successful in the different markets.




Report details


• Drill path: You can drill to other organizational units or Material attributes.

• Other options: You can change the sales organization to any other Organization attributes, such as sales office or distribution channel.

Material Division Contribution to Monthly Sales

This report displays measurements of the contribution of each company’s material divisions to overall sales.



Usage scenarios

Executives and division managers can use this report to identify which material divisions contribute more to overall sales, and to see the monthly trends.


Report details

• Qualification: You are prompted to select a quarter to analyze.


• Other options:

You can create similar reports to analyze contribution by Customer or Organization attributes. For example, you can create a report to calculate customer region contribution to material sales. This requires you to create additional contribution metrics to calculate contribution by those attributes.

You can also eliminate Material from the report to summarize contributions at the material division level; then you can drill down to Material if detail is required.



Quarterly Product Hierarchy Sales by Material

This graphical report displays quarterly sales for all materials in a given product hierarchy. The figure here displays an example based the most general level in the product hierarchy. See the Product hierarchy example, page 38 for a detailed explanation of product hierarchy levels.

Usage scenarios

Product managers can use this report to analyze sales trends for a selected product hierarchy. They can also measure the weight of each material on overall sales and any related trends.


The page-by Product Hierarchy Level option consolidates information for the selected hierarchy level.



The report appears as a graph using stacked columns. In this graph, each column section represents a material, while the whole column represents the product hierarchy element.

Report details


• Drill path: You can drill to Month or Organization attributes to display, for example, the breakdown by sales organization.

• Other options: You can use other Material Grouping attributes, such as Material Division, instead of Product Hierarchy.

Margin and Price Monthly Trends by Material

This report displays related trends in the net price and net margin of selected materials.

Usage scenarios

Products managers can use this report to analyze how net price variance influences the material net margin.




The % Change Net Price and % Change Net Margin columns include thresholds that highlight data to emphasize positive (green) or negative (red) changes.

Report details

• Qualification: You are prompted to select a quarter and a material division to analyze.


• Other options: You can add absolute values for net price and net margin to analyze absolute variance.

Top 5 Materials by Net Sales Amount and Quarter

This report displays the top five materials by sales order net amount, for each quarter of a selected year.



Usage scenarios

Product managers can use this report to identify top materials each quarter, including details such as the number of transactions and total quantities.


Report details

• Qualification:


The report ranks all materials by sales and identifies the top five. The report includes an embedded filter named Rank Net Sales Order Amount – Top 5 by Quarter.




• Other options:

You can create similar reports to identify top materials by division, sales office, distribution channel, and so on.

The report filter can be changed to display ten or twenty top products, instead of five. You can also add a prompt to select the number of top products to display.

Bottom 3 Materials by % Net Margin and Quarter

This report displays the least profitable materials based on percentage of net margin, by quarter.

Usage scenarios

Product managers can use this report to identify which materials have a smaller percentage of net margin, and can take action to improve margins.


Report details

• Qualification:

You are prompted to select a year to analyze. The report displays those sales order items created during the selected year.


94 Quotation-to-Sales Order Conversion reports © 2010 MicroStrategy, Inc.

This report ranks all materials by the percentage of net margin, and identifies the bottom three. The report includes an embedded filter named Rank % Net Margin – Bottom 3 by Quarter.


• Other options:

You can create similar reports to identify least profitable (bottom) materials by division, sales office, distribution channel, and so on.

You can change the filter so you can select the number of bottom materials to display.

You can add an additional prompt qualification to segment materials by product hierarchy or material division.

Quotation-to-Sales Order Conversion reportsThis analysis subsection measures the efficiency of converting quotations into sales orders. The reports in this area include summary reports measuring conversion efficiency by organization or material attributes, a report identifying top sales orders from quotations, and a summary report analyzing historical conversion trends.

Managers can use these reports to monitor conversion activity. Analysts can use the reports to identify patterns driving conversion.

Sales orders can be created directly or processed from an earlier quotation. Conversion rates measure those sales orders generated from quotations, compared to the total number of quotations.


• Quarterly Conversion Summary

• Quarterly Conversion Activity by Organization

• Quarterly Conversion Activity by Material

• Top 5 Sales Orders from Quotations by Net Amount and Quarter


© 2010 MicroStrategy, Inc. Quotation-to-Sales Order Conversion reports 95

Quarterly Conversion Summary

This report presents a general overview of the quotation-to-sales order conversion performance for a given year.

Usage scenarios

Executives can use this report to identify overall performance trends for quotation-to-sales order conversions.





Report details

• Qualification: You are prompted to select a year. The report includes only those quotations and sales orders processed during the selected year.

For example, suppose you select the year 2005 to analyze. If a quotation was processed in 2005 (sales document creation date in year 2005) and the corresponding sales order was processed in 2006 (sales document creation date in year 2006), the report will not count that sales order to calculate the conversion rate.


• Other options: You can analyze conversion at the item level by substituting the existing metrics with others, such as Sales Order Items from Quotations Items, and Conversion Rate Items (%).

Quarterly Conversion Activity by Organization

This report displays an analysis of the efficiency of converting quotations into sales orders. It groups information by the selected organizational unit, such as sales office, sales group, distribution channel, and so on.

Usage scenarios

Sales managers can use this report to identify which organizational units are more effective and which less effective at converting quotations into sales orders.





The Conversion Rate column includes thresholds that highlight conversion rates greater than 50%. These values appear in red, with cells outlined in green.

Report details

• Qualification:


You are prompted to select a year to analyze. The report compares quotations and sales orders processed during a given quarter of the selected year, based on the sales document creation date.

• Drill path: You can drill to other organizational units or customer characteristics.

• Other options:

You can include Quarter in the report to evaluate quarterly conversion trends.

You can create a similar report to analyze conversions based on customer characteristics, by replacing the object prompt with one allowing the selection of Customer attributes.

Quarterly Conversion Activity by Material

This report displays measurements for quarterly quotation-to-sales order conversions (absolute and relative), by material.

Usage scenarios

Product managers can use this report to identify which materials have greater conversion rates, and to identify trends. Actions can include creating material bundles to group those materials with greater conversion rates to drive conversion of sales orders, and to group the materials with smaller conversion rates. You can then rerun the report later to evaluate the success of any initiatives, and to determine how much the conversion rates improved.





Report details

• Qualification: You are prompted to select a year to analyze. The report compares only those quotations and sales orders processed during a given quarter of the selected year, based on the sales document creation date.

• Drill path: You can drill to Organization to display the breakdown by, for example, sales organization or sales office.

• Other options: You can change the report to group data by material division or product hierarchy instead of by material.

Top 5 Sales Orders from Quotations by Net Amount and Quarter

This report displays the top five sales orders generated from quotations, based on net amount.



Usage scenarios

Sales managers can use this report to identify top orders from quotations, and to determine the best practices used to convert each quotation into a sales order.


Report details

• Qualification:

You are prompted to select a year to analyze. The report displays only those sales order items that had an earlier quotation and were created during the selected year.

The report ranks sales orders and selects the top five by net amount. The report includes an embedded filter named Rank Net Sales Order Amount – Top 5 Sales Orders from Quotations by Quarter.

• Drill path: You can drill to display sales transaction details.

• Other options: You can add other dynamic selections to segment top orders by attributes such as customer region, material division, or distribution channel.


100 Customer Sales Order History reports © 2010 MicroStrategy, Inc.

Customer Sales Order History reportsThis analysis subsection tracks sales order histories for individual customers. Reports include a summary of sales order activity for individual customers, and a report detailing all sales order transactions.

Salespeople can use these reports to monitor historical activity for a customer or group of customers.


• Customer Quarterly Sales Orders Activity Summary

• Customer Sales Order History Detail

Customer Quarterly Sales Orders Activity Summary

This report displays a summary of all sales activity for a selected customer or group of customers, during a given year.

Usage scenarios

Salespeople can use this report to see a summary of sales activity for their customer portfolio.


The page-by Customer option summarizes sales activity for the selected customer.


© 2010 MicroStrategy, Inc. Customer Sales Order History reports 101

The % Net Margin row includes a threshold that highlights data when the net margin for a customer is greater than 15%. These values appear with a green background.

Report details

• Qualification: You are prompted to select a year and one or more customers to analyze.


• Other options: You can add additional metrics to evaluate quotation activity, and compare it with sales activity.

Customer Sales Order History Detail

This report presents a complete customer sales history.

Usage scenarios

Salespeople can use this report to track sales histories, and to identify customer buying patterns and cross-selling opportunities for further sales activities.


Report details

• Qualification: You are prompted to select a customer to analyze.


102 Customer Sales Order History reports © 2010 MicroStrategy, Inc.

• Drill path: Because this report is at the lowest level possible, no drill path is recommended.

• Other options: You can add additional details to this report, such as processing dates and sales order (document) statuses.

© 2010 MicroStrategy, Inc. Delivery Analysis area 103

66.DELIVERY ANALYSIS

Introduction

This chapter provides reference information for the reports that come with the Sales and Distribution Analysis Module (SDAM) as part of SDAM’s Delivery analysis area. It presents detailed information on each report in this analysis area, including usage scenarios, screen shots, and reporting details. The reports are available in the SDAM Reports folder; see Accessing reports, page 7.


Delivery Analysis areaThis analysis area focuses on delivery processing that is related to fulfilling sales orders for customers. Delivery processing includes a number of stages, such as material availability, packing and loading, the issuing of goods, and final delivery to the customer site. Delivery transactions are automatically updated as different stages are completed.

Delivery Analysis Sales & Distribution Analysis Module Reference6

104 Open Deliveries reports © 2010 MicroStrategy, Inc.

The following information explains some delivery processing details as they relate to the Delivery analysis reports:

• Outbound deliveries are those deliveries scheduled to fulfill one or more sales orders. An order may be fulfilled with one or more outbound deliveries, depending on when different materials from the same order are available and whether they are shipped from the same location.

• Materials from different sales orders might be grouped into a single delivery, for example, when they have a similar schedule and are shipped from the same location.

• A delivery may include one or more delivery items; each of these items corresponds to an individual material in the delivery. For each item, the transaction tracks material quantities and dates corresponding to the different stages in the delivery process.

For a diagram of the sales order-to-delivery process, see Delivery Document hierarchy, page 191 in Appendix B: Logical Data Model.

This analysis area is divided into the following subsections, each with its own focused reports:

• Open Deliveries analysis: This area monitors deliveries being processed.

• Outbound Delivery analysis: This area analyzes outbound delivery processing, including measuring volumes and processing times.

• Customer Delivery History analysis: This area tracks delivery histories for individual customers.

Open Deliveries reportsThis area monitors deliveries being processed through the many delivery stages. The reports in this area help you track details for different types of pending deliveries.

Analysts and service managers can use these reports to monitor delivery processing and take action to ensure correct and timely delivery.


• List of Open Deliveries (Pending Material Availability)

• List of Open Deliveries (Pending Goods Issue)

Sales & Distribution Analysis Module Reference Delivery Analysis 6

© 2010 MicroStrategy, Inc. Open Deliveries reports 105

• List of Open Deliveries (Issued and Pending Delivery)

List of Open Deliveries (Pending Material Availability)

This report lists all current open deliveries that are waiting for materials to become available for packing and shipping.

Usage scenarios

Service managers can use this report to track open deliveries, and to monitor planned quantities and delivery dates.


Report details

• Qualification: This report displays those deliveries that have a delivery document item status of Open – Pending Material Availability.


• Other options: You can add additional details to the report such as delivery status, reference document, and so on. A reference document is an earlier, related document. For example, a quotation may lead to a sales order; the quotation becomes the reference document for the sales order document.

List of Open Deliveries (Pending Goods Issue)

This report presents a list of open deliveries for which goods have been packed, but which are waiting to be issued to customers.


106 Open Deliveries reports © 2010 MicroStrategy, Inc.

Usage scenarios

Service managers can use this report to track open deliveries, and to monitor planned quantities and delivery dates.


Report details

• Qualification: The report displays those deliveries that have a delivery document item status of Open – Pending Goods Issue.

• Drill path: Because this report presents data at the lowest level possible, no drill path is recommended.

• Other options: You can add additional details to the report such as status, reference document, and so on.

List of Open Deliveries (Issued and Pending Delivery)

This report displays a list of all open deliveries that have been shipped and are in transit to the customer site.

Usage scenarios

Service managers can use this report to track open deliveries, and to monitor delivery dates.


© 2010 MicroStrategy, Inc. Outbound Delivery Analysis reports 107


Report details

• Qualification: This report displays those deliveries that have a delivery document item status of Open – Pending Deliver Customer.


• Other options: You can add additional details to the report such as status, reference document, and so on.

Outbound Delivery Analysis reportsThis area analyzes outbound delivery processing, including measurements of volumes and processing times. The reports include summaries measuring activity by shipping point, organization, and material attributes; a report analyzing each shipping point’s contribution to overall deliveries; reports analyzing historical trends; and reports analyzing delivery processing times and delays.

Executives and managers can use these reports to monitor outbound delivery activity. Analysts can use the reports to identify inefficiencies in the delivery process.


• Quarterly Outbound Delivery Activity Summary

• Monthly Outbound Delivery Activity by Organization

• Outbound Delivery Processing Times by Shipping Point


108 Outbound Delivery Analysis reports © 2010 MicroStrategy, Inc.

• Monthly Outbound Delivery Activity by Shipping Point

• Shipping Point Contribution to Material Deliveries

• Material Processing Times by Shipping Point

• Outbound Delivery Processing by Customer Region

Quarterly Outbound Delivery Activity Summary

This report provides a summary of outbound delivery activity for a selected quarter.

Usage scenarios

Service managers can use this report to monitor outbound deliveries and to identify quarterly trends.


Report details


• Drill path: You can drill to Month to evaluate monthly activity, or to Shipping Point to analyze activity based on the different locations from where products are shipped.

• Other options: You can add additional metrics such as Average Delivery Delays.



Monthly Outbound Delivery Activity by Organization

This report displays a summary of outbound delivery activity based on selected organizational units. An organizational unit is assigned to each delivery transaction.

Usage scenarios

Service managers and sales managers can use this report to analyze delivery activity throughout the organization, and to identify more active and less active organizational units.


The page-by Month option summarizes delivery activity for the selected month.

Report details

• Qualification:


You are prompted to select the quarter for which outbound deliveries will be analyzed.

• Drill path: You can drill to any of the other organizational units.

• Other options: You can change the attribute prompt to select customer characteristics or Material attributes.



Outbound Delivery Processing Times by Shipping Point

This report presents a graphical view of historical trends for delivery processing times. It includes data on delays for each of the company’s shipping points.

Usage scenarios

Service managers can use this report to analyze processing times for outbound deliveries for each shipping point, and to identify historical trends.




The report appears as two graphs, one for processing times and one for delivery delays. Scroll down to display the second graph.



The page-by Shipping Point option summarizes trends for the selected shipping point.

When viewed in grid mode, the report displays a complete set of processing times and delays, measured in days.

Report details

• Qualification: You are prompted to select a year for which outbound deliveries will be analyzed.

• Drill path: You can drill to Material attributes to analyze shipping point processing times for different materials.

• Other options: You can add additional metrics to measure processing times and delivery delays compared to planned dates.

Monthly Outbound Delivery Activity by Shipping Point

This report displays monthly delivery activity volumes, based on shipping point.

Usage scenarios

Service managers can use this report to monitor shipping point activity, to identify the most active shipping points, and to identify shipping points showing a positive or negative trend in activity.




The % Change Outbound Deliveries column includes a threshold that highlights negative data, reflecting shipping points with a negative trend in the number of deliveries processed compared to the previous month. These values appear with a red background.

The % Change Total Delivery Time column includes a threshold that highlights negative data, reflecting shipping points that are reducing the total delivery processing time compared to the previous month. These values appear with a green background.

The page-by Month option summarizes activity volumes for the selected month.

Report details

• Qualification: You are prompted to select a quarter for which outbound deliveries will be analyzed.

• Drill path: You can drill to Organization, Customer Characteristics, or Material attributes to display the activity breakdown by those attributes.

• Other options: You can add prompt qualifications using Material attributes to analyze activity volumes by groups of materials.

Shipping Point Contribution to Material Deliveries

This report displays the contributions made by each shipping point to outbound material deliveries, by quarter.

Usage scenarios

Product managers can use this report to identify which shipping points have a greater contribution to overall outbound deliveries for a specific material.




The page-by Material option summarizes contributions for the selected material.

Each of the % Contribution to Material Outbound Delivery Quantities columns includes a threshold that highlights data for those shipping points contributing more than 50% to overall material outbound deliveries. These values appear in green.

Report details



• Other options: You can use a Material attribute such as Product Hierarchy or Material Division in place of Material to display a summary at that level. You can add filters to limit the number of materials analyzed by material division or product hierarchy.

Material Processing Times by Shipping Point

This report displays a summary of processing times by material and shipping point.

Usage scenarios

Product managers and service managers can use this report to analyze outbound delivery performance by material, identifying which shipping points perform better when fulfilling material deliveries.




The page-by Month option summarizes processing times for the selected month.

Report details

• Qualification: You are prompted to select a quarter and a material in the product hierarchy for which outbound deliveries will be analyzed.

• Drill path: You can drill to Organization or Customer attributes.

• Other options: You can group information at the Product Hierarchy level instead of at the Material level.

Outbound Delivery Processing by Customer Region

This report presents a graphical view of outbound delivery processing times by customer region.

Usage scenarios

Service managers and sales managers can use this report to evaluate delivery service quality to different customer regions. This analysis identifies possible



bottlenecks delivering goods to customer regions, and can trigger actions to reduce the time required to deliver goods and to eliminate delays.


The page-by Customer Region option summarizes processing times for the selected customer region.

Report details



© 2010 MicroStrategy, Inc. Customer Delivery History reports 117

• Drill path: You can drill to Month or customer characteristics.

• Other options: You can analyze data at the Month level instead of the Quarter level.

Customer Delivery History reportsThis area tracks delivery histories for individual customers. Reports in this area include a summary of all outbound delivery activity for individual customers, a report detailing delivery transactions, and a report detailing deliveries and their corresponding sales transactions.

Salespeople can use these reports to monitor historical activity for a customer or group of customers.


• Customer Quarterly Outbound Delivery Activity Summary

• Customer Outbound Deliveries History Detail

• Customer Delivery History (by Reference Sales Document)

Customer Quarterly Outbound Delivery Activity Summary

This report presents a summary of all outbound deliveries for a customer in a given year.

Usage scenarios

Salespeople can use this report to get an overall view of the outbound delivery activity for a specific customer or group of customers, and can analyze quarterly trends.


118 Customer Delivery History reports © 2010 MicroStrategy, Inc.



Report details

• Qualification: You are prompted to select a year and one or more customers for whom outbound deliveries will be analyzed.


• Other options: You can add additional metrics to measure other types of deliveries such as return deliveries or free subsequent deliveries.

Customer Outbound Deliveries History Detail

This report displays a list of all outbound deliveries for a selected customer.

Usage scenarios

Salespeople can use this report to track the outbound delivery history for a customer or group of customers.


© 2010 MicroStrategy, Inc. Customer Delivery History reports 119


The page-by Customer option summarizes outbound deliveries for the selected customer.

Report details

• Qualification: You are prompted to select one or more customers for whom outbound deliveries will be analyzed.


• Other options: You can add additional details to the report such as Reference Document, Document Item Status, or other delivery processing dates.

Customer Delivery History (by Reference Sales Document)

This report displays a list of all deliveries and related sales documents for a customer.


120 Customer Delivery History reports © 2010 MicroStrategy, Inc.

Usage scenarios

Salespeople can use this report to track how a customer’s sales transactions were fulfilled.


This report includes the option to page by Sales Document Type. This option lets you display information for a specific type of sales document. For example, you can display only sales orders and their corresponding deliveries, or display only returns.

Report details

• Qualification: You are prompted to select a customer for whom deliveries will be analyzed.


• Other options: You can add additional details to this report such as delivery dates or document status.

© 2010 MicroStrategy, Inc. Sales and Delivery Service Analysis area 121

77.SALES AND DELIVERY SERVICE ANALYSIS

Introduction

This chapter provides reference information for the reports that come with the Sales and Distribution Analysis Module (SDAM) as part of SDAM’s Sales and Delivery Service analysis area. It presents detailed information on each report in this analysis area, including usage scenarios, screen shots, and reporting details. The reports are available in the SDAM Reports folder; see Accessing reports, page 7.


Sales and Delivery Service Analysis areaThe Sales and Delivery Service Analysis area helps you understand information related to company service activities, such as sales order fulfillment, delivery service, and returns.

Sales and Delivery Service Analysis Sales & Distribution Analysis Module Reference7

122 Backlog Analysis reports © 2010 MicroStrategy, Inc.

The following information explains some service activity details as they relate to the Sales and Delivery Service reports:

• After sales orders are placed, they are fulfilled through delivery. While orders are being fulfilled, they are part of the backlog.

• Each delivery has a confirmed quantity of materials to be delivered and a date when materials are expected for delivery at the customer site. Delivery service quality is measured based on the number of deliveries with incorrect quantity and the number of late deliveries.

• Once returned materials are accepted, the company processes the return as well as a subsequent delivery to replace the returned materials.

The Sales and Delivery Service analysis area is divided into the following subsections, each with its own focused reports:

• Backlog analysis: This analysis area monitors unfulfilled sales orders, and tracks materials pending delivery and fulfillment rates.

• Delivery Service analysis: This analysis area focuses on the quality of the delivery process, identifying the volumes of incorrect and late deliveries.

• Returns analysis: This analysis area focuses on returns activity and identifies patterns based on material or shipping point.

Backlog Analysis reportsThis analysis subsection monitors unfulfilled sales orders, and tracks materials pending delivery and fulfillment rates. The reports in this area include summaries of backlog status by organization and material attributes, and a report identifying details on all orders in the backlog.

Managers and others can use these reports to monitor the status of the backlog to ensure correct order fulfillment.


• Backlog Summary by Material

• Backlog and Fulfillment Rates by Organization

• List of Backlogged Sales Orders by Customer

Sales & Distribution Analysis Module Reference Sales and Delivery Service Analysis 7

© 2010 MicroStrategy, Inc. Backlog Analysis reports 123

Backlog Summary by Material

This report provides a summary of the current backlog, by material.

Usage scenarios

Product managers can use this report to monitor material backlogs, to identify materials with a higher number of backlogs, and to track fulfillment rates.


Report details

• Qualification: This report displays those sales orders with a status of Open (Pending Delivery). The report includes an embedded filter named Open Status (Sales Document).

• Drill path: You can drill to sales order detail to identify backlogged sales orders by material.

• Other options:

You can create a similar report by analyzing backlog quantities at other Material levels such as product hierarchy or material division.

You can add a prompt to limit the material analyzed by selecting a product hierarchy level or a group of materials.


124 Backlog Analysis reports © 2010 MicroStrategy, Inc.

Backlog and Fulfillment Rates by Organization

This report displays backlog and fulfillment rates by selected organizational units, such as sales office or distribution channel.

Usage scenarios

Sales managers can use this report to identify backlogs based on organizational units, and to analyze fulfillment rates.


Report details

• Qualification:


The report displays those sales orders that have a status of Open (Pending Delivery). The report includes an embedded filter named Open Status (Sales Document).

• Drill path:

You can drill to other Organization, Material, or Customer attributes.

You can drill to transaction details to identify backlogged orders.

• Other options: A similar report can be created analyzing fulfillment rates by Material attributes such as material division, product hierarchy, or material.


© 2010 MicroStrategy, Inc. Delivery Service reports 125

List of Backlogged Sales Orders by Customer

This report displays a list of all existing backlogged sales orders for a selected customer.

Usage scenarios

Salespeople can use this report to identify any unfilled orders for a specific customer or group of customers.


Report details

• Qualification:

You can limit the list of backlogged sales orders by selecting one or more customers at report run time.

The report displays those sales orders that have a status of Open (Pending Delivery). The report includes an embedded filter named Open Status (Sales Document).

• Drill path: This report displays data at the lowest level possible, so no drill path is recommended.

• Other options: You can add additional metrics such as Quantity Delivered, Fulfillment Rate, and so on.

Delivery Service reportsThis analysis subsection focuses on the quality of the delivery process, identifying the volumes of incorrect and late deliveries. The reports in this area include summaries of delivery service quality by organization, material, and shipping point attributes; a report identifying details of those deliveries with incorrect quantities; and a report comparing the sales activity to the delivery activity, that evaluates the company’s capacity to fulfill orders.


126 Delivery Service reports © 2010 MicroStrategy, Inc.

Managers can use these reports to evaluate the quality of delivery service. Analysts can use the reports to identify issues and patterns explaining service quality.



• Outbound Delivery Service Quality by Organization

• Incorrect Deliveries Summary by Material and Shipping Point

• List of Incorrectly Delivered Items by Quarter and Customer

• Delivered vs. Ordered Quantity by Sales Organization

Outbound Delivery Service Summary by Shipping Point

This report displays a summary of the quality of delivery service by identifying late and incorrect deliveries.

Usage scenarios

Service managers can use this report to identify which shipping points provide better delivery service, and to measure late and incorrect deliveries and see the percentage compared to the total number of deliveries.


The page-by Quarter option summarizes data for the selected quarter.



Report details

• Qualification:

You are prompted to select a year to analyze. The report includes only those completed outbound deliveries that were created during the selected year.

This report includes two embedded filters named Complete Status (Delivery Document) and Outbound Deliveries.

• Drill path: You can drill to Month or Material attributes to display the breakdown.

• Other options: You can add some time trend metrics to compare performance to the previous month, such as % Change Items with Incorrect Quantity.

Outbound Delivery Service Quality by Organization

This report displays measurements for deliveries with some service quality issue, such as incorrect quantity or late delivery, and determines the effect on quality (the weight) compared to overall delivery service.

Usage scenarios

Service managers and sales managers can use this report to identify those organizational units with a greater number of incorrect and late deliveries.




The page-by Quarter option summarizes the service quality issues for the selected quarter.

Report details

• Qualification:


You are prompted to select a year to analyze. The report includes only completed outbound deliveries that were created during the selected year.

This report includes two embedded filters named Complete Status (Delivery Document) and Outbound Deliveries.

• Drill path: You can drill to Month or Shipping Point.

• Other options: You can create a similar report by replacing the Organization prompt with a Material attributes prompt, to analyze service quality by material division, product hierarchy, and so on.

Incorrect Deliveries Summary by Material and Shipping Point

This report displays a summary of incorrect material deliveries, based on shipping point.

Usage scenarios

Shipping point managers and product managers can use this report to identify which materials have a higher number of delivery issues, and to measure the effect on quality (the weight) of incorrect deliveries compared to the quantity delivered.




The page-by Quarter option summarizes incorrect material deliveries for the selected quarter.



Report details

• Qualification:

You are prompted to select a year to analyze. The report includes those deliveries created during the selected year.

This report includes two embedded filters, All Types of Outbound Deliveries and Complete Status (Delivery Document).

• Drill path: You can drill to Month.

• Other options:

You can add a prompt to select a specific shipping point.

You can replace Material with other Material attributes to summarize the report at the material division or product hierarchy level.

List of Incorrectly Delivered Items by Customer

This report displays a list of those delivered items that had incorrect delivery quantities, along with the name of the affected customer.

Usage scenarios

Service managers can use this report to track incorrect deliveries for a selected quarter. Actions can include identifying common patterns in incorrect deliveries, such as customers affected, materials involved, or shipping points responsible.


Report details

• Qualification:



You are prompted to select a quarter to analyze. The report lists those delivery items that were created during the selected quarter, had a status of Complete, and had a confirmed quantity different from the delivered quantity.

This report includes two embedded filters, named Complete Status (Delivery Document) and Set of Items – Incorrectly Delivered Quantities.


• Other options: You can add additional details to the report such as delivery dates or item creation date.

Delivered vs. Ordered Quantity by Sales Organization

This report compares sales activity to delivery activity.

Usage scenarios

Sales managers and service managers can use this report to measure synchronization between sales and delivery activities by calculating the percentage between delivered quantity and ordered quantity:

• If the percentage is close to 100%, it indicates that the company delivers materials as soon as they are ordered.

• If the percentage is lower, sales are good but the company is not able to fulfill the demand in a timely fashion and a backlog is being created for future quarters.

• If the percentage is higher, the company is fulfilling old orders but new sales are decreasing.


132 Returns Analysis reports © 2010 MicroStrategy, Inc.


Report details

• Qualification: You are prompted to select a year to analyze. The report analyzes the quantity ordered in a quarter compared to the quantity delivered in the same quarter, although quantities may not refer to the same transactions.

• Drill path: You can drill to Month, to other organizational units, or to Material attributes.

• Other options: You can create a report to compare sales and delivery activities by material.

Returns Analysis reportsThis analysis area focuses on returns activity and identifies patterns based on material or shipping point.


© 2010 MicroStrategy, Inc. Returns Analysis reports 133

The following information explains some returns details as they relate to the Returns analysis reports:

• A return transaction is processed when a customer complains about delivered materials, for example, due to poor quality or damage.

• The company can accept or reject a return.

• If a return is accepted, the company processes a number of transactions in the system, including a return delivery to return materials to the shipping location, a sales transaction to account for new material being shipped to replace returned ones, and a subsequent free-of-charge delivery to deliver replacement materials to the customer.

Managers can use the reports in this analysis area to monitor returns activity. Analysts can use the reports to identify trends explaining returns.


• Quarterly Returns Summary

• Quarterly Accepted Returns by Organization

• Quarterly Accepted Returns by Material

• List of Returns by Customer

• Quarterly Shipping Point Returns Delivery Activity

Quarterly Returns Summary

This report displays a summary of the processing of returns over time.



Usage scenarios

Service managers can use this report to evaluate returns processing, and can use the data to compare the total number of returns, the number of rejected/accepted returns, and absolute amount values.


Report details

• Qualification: You are prompted to select a year to analyze. The report displays those returns that were processed during the selected year.

• Drill path: You can drill down to a month.

• Other options: You can add organizational units or Material attributes to evaluate return processing by attributes such as material division, product hierarchy, sales office, and so on.

Quarterly Accepted Returns by Organization

This report presents a summary of accepted returns, listed by organizational units, and compares accepted returns to sales orders.

Usage scenarios

Sales managers and service managers can use this report to identify which organizational units have a greater number of returns, and to see their return trends over time.




Report details

• Qualification:


You are prompted to select a year to analyze. The report displays those returns that were processed and accepted during the year selected.

• Drill path: You can drill down to a month, or to other Organization attributes.

• Other options: You can add additional metrics to measure quarter-to-quarter trends.

Quarterly Accepted Returns by Material

This report displays a summary of accepted returns for a given year, listed by material, and compares the returns to material sales.

Usage scenarios

Product managers can use this report to identify which materials have a higher number of returns, and to see their trends over time. Actions can include identifying quality issues with certain materials.




Report details

• Qualification: You are prompted to select a year to analyze. The report displays those returns that were processed and accepted during the year selected.

• Drill path: You can drill to Month to display the breakdown.

• Other options: You can use Material attributes other than Material, and summarize returns activity at the material division or Product hierarchy levels.

List of Returns by Customer

This report displays returns, listed by customer.

Usage scenarios

Salespeople can use this report to track the return history for a given customer or group of customers, and can see the status of the return.




Report details

• Qualification: You are prompted to select one or more customers to analyze. The report displays only those returns corresponding to the customers selected.


• Other options: You can add additional details to the return, such as Reference Document. A reference document is a related, earlier document. For example, a quotation can lead to a sales order; the quotation becomes the reference document for the sales order document.

Quarterly Shipping Point Returns Delivery Activity

This report displays a summary of deliveries that originated from returns, and compares these deliveries to total outbound deliveries.

Usage scenarios

Service managers can use this report to identify shipping points with the highest number of returns that result in new deliveries, and can determine whether there are issues with the quality of delivery service.




Report details

• Qualification: You are prompted to select a year to analyze. The report displays those returns and outbound deliveries that were processed during the selected year.

• Drill path: You can drill to Month or Material attributes to identify which materials are responsible for a greater percentage of return deliveries.

• Other options: You can add Material or Organization attributes to the report layout.

© 2010 MicroStrategy, Inc. Metrics glossary 139

AA.DEFINITIONS FOR OBJECTS ON REPORTS: OBJECT GLOSSARIES

Introduction

This appendix presents glossaries for all the Sales and Distribution Analysis Module (SDAM) objects used in the SDAM reports. The glossaries include descriptions of the public objects (metrics, filters, and prompts) that comprise SDAM.

Metrics glossaryThe following metrics or key performance indicators (KPIs) are used in SDAM. All the definitions can be found in the Public Objects\Metrics folder of the MicroStrategy project.

Metrics are organized based on the reporting areas in which they are used:

• Inquiry metrics

• Quotation metrics

• Sales Order metrics

Definitions for Objects on Reports: Object Glossaries Sales & Distribution Analysis Module ReferenceA

140 Metrics glossary © 2010 MicroStrategy, Inc.

• Delivery metrics

• Sales and Delivery Service metrics

An additional category, Base Formula metrics, contains basic calculations that the other metrics are based on.

Base Formula metrics

All metrics formulas are defined based on base formulas. In the SDAM project, base formulas are organized according to the fact type, that is, sales document facts or delivery document facts.

Sales Document base formulas

The following basic formulas define the base calculations used for sales document metrics.

Base Formula Formula

Sales Documents Calculates sales documents by counting the distinct sales documents existing in the sales document fact table.

• Defined as Count<Distinct=True>([SALES_DOC_ID]) where [SALES_DOC_ID] is a fact.

Sales Document Items Calculates the number of sales document items by counting the sales document items existing in the sales document fact table.

• Defined as Count([SALES_DOC_ITEM_ID]) where [SALES_DOC_ITEM_ID] is a fact.

Customers with Sales Documents

Calculates customers with sales documents by counting the distinct customers existing in the sales document fact table.

• Defined as Count<Distinct=True>([CUST_ID from Sales Doc Fact Table]) where [CUST_ID from Sales Doc Fact Table] is a fact.

Net Item Amount Calculates net amounts by aggregating net amount for each sales document item.

• Defined as Sum([Net Item Amount]) where [Net Item Amount] is a fact.• Not valid for inquiries because the fact does not apply to this type of sales

document.

Item Cost Calculates costs by aggregating the item cost for each sales document item.• Defined as SUM([Item Cost]) where [Cost] is a fact.• Not valid for inquiries because the fact does not apply to this type of sales

document.

Sales & Distribution Analysis Module Reference Definitions for Objects on Reports: Object Glossaries A


Item Quantity (Base Units)

Calculates quantities by aggregating the quantity for each sales document item.

• Defined as SUM ([Quantity (Base Units)]) where [Quantity (Base Units)] is a fact.

• Measured in material base units.

Net Item Price (Base Units)

Calculates item net prices (measured based on material base units) by calculating the average of fact “Net Price (Base Units),” where the fact is calculated for each sales document item dividing the net amount of the items by the item material quantity.

• Defined as AVG([Net Price (Base Units)]) where [Net Price (Base Units)] is a fact.

• Not valid for inquiries because the fact does not apply to this type of sales document.

Item Ordered Quantity Delivered (Base Units)

Calculates delivered quantities by aggregating the quantity for each sales document item.

• Defined as SUM([Ordered Quantity Delivered (Base Units)]) where [Ordered Quantity Delivered (Base Units)] is a fact.

• Valid only for sales orders.• Measured in material base units.

Backlog Item Quantity (Base Units)

Calculates backlog quantities by aggregating the backlog quantity for each sales document item.

• Defined as SUM([Backlog Quantity (Base Units)]) where [Backlog Quantity (Base Units)] is a fact.

• Valid only for sales orders.• Measured in material base units.

Backlog Net Item Amount

Calculates backlog amounts by aggregating the backlog amount for each sales document item.

• Defined as SUM([Backlog Net Item Amount]) where [Backlog Net Item Amount] is a fact.

• Valid only for sales orders.




Delivery Document base formulas

The following base formulas define the base calculations used for delivery document metrics.


Delivery Documents Calculates the number of delivery documents by counting distinct delivery documents in the delivery fact table.

• Defined as Count<Distinct=True>([DELIV_DOC_ID]) where [DELIV_DOC_ID] is a fact.

Delivery Document Items Calculates the number of delivery document items by counting delivery documents items in the delivery fact table.

• Defined as COUNT ([DELIV_DOC_ITEM_ID]) where [DELIV_DOC_ITEM_ID] is a fact.

Confirmed Delivery Quantity (Base Units)

Calculates the confirmed delivery quantity by aggregating it for each delivery document item.

• Defined as SUM ([Confirmed Delivery Quantity (Base Units)]) where [Confirmed Delivery Quantity (Base Units)] is a fact.


Delivered Quantity (Base Units)

Calculates the delivered quantity by aggregating it for each delivery document item.

• Defined as SUM ([Delivered Quantity (Base Units)]) where [Delivered Quantity (Base Units)] is a fact.


Average Transportation Time (Days)

Calculates the average number of days required to transport materials from the company’s shipping point to the customer site.

• Defined as AVG ([Difference between Delivery and Issue Date]) where [Difference between Delivery and Issue Date] is a fact.

Average Total Delivery Time (Days)

Calculates the average total delivery time from when materials are available for delivery until they reach the customer site; includes processing times required to pack, load, and transport goods.

• Defined as AVG ([Difference between Delivery and Material Availability Date]) where [Difference between Delivery and Material Availability Date] is a fact.

• Measured in days.

Average Shipping Processing Time (Days)

Calculates the average time from when materials are available until they are shipped; includes all the processing time required to pack and load goods.

• Defined as AVG ([Difference between Issue and Material Availability Date]) where [Difference between Issue and Material Availability Date] is a fact.


Average Packing Time (Days)

Calculates the average packing time from when materials are available until they are ready for loading.

• Defined as AVG ([Difference between Loading and Material Availability Date]) where [Difference between Loading and Material Availability Date] is a fact.




Inquiry metrics

This section lists all the metrics used to define Inquiry reports.

Average Loading Time (Days)

Calculates the average time from the start of loading until the materials are issued.

• Defined as AVG ([Difference between Issue and Loading Date]) where [Difference between Issue and Loading Date] is a fact.


Average Delay between Confirmed and Actual Delivery Date (Days)

Calculates the average delay between dates when goods were confirmed to arrive and the date when they actually arrive (in days).

• Defined as AVG ([Delay between Confirmed and Actual Delivery Date]) where [Delay between Confirmed and Actual Delivery Date] is a fact.

Average Delay between Requested and Confirmed Delivery Date (Days)

Calculates the average delay between the date when the customer requested to receive goods compared to the date when goods are confirmed for arrival.

• Defined as AVG ([Delay between Requested and Confirmed Delivery Date]) where [Delay between Requested and Confirmed Delivery Date] is a fact.


Average Delay between Requested and Actual Delivery Date (Days)

Calculates the average delay between the date when the customer requested to receive goods and the actual date when goods arrived.

• Defined as AVG ([Delay between Requested and Actual Delivery Date]) where [Delay between Requested and Actual Delivery Date] is a fact.

Average Delay between Planned and Issued Date (Days)

Calculates the average delay between the date when goods are planned for issue and they date they are actually issued (in days).

• Defined as AVG ([Delay between Planned and Issued Date]) where [Delay between Planned and Issued Date] is a fact.


Metric/KPI Description Formula

Inquiries Number of customer inquiries

[Sales Documents] {~} <Inquiries>• Conditional metric with base formula [Sales

Documents] and filter Inquiries

Inquiries Previous Month

Same as Inquiries but calculated for the month prior to the one selected

[Sales Documents] {~} <Inquiries> | [Previous Month] |

• Conditional-transformation metric with base formula [Sales Documents], filter Inquiries and transformation Previous Month

% Change Inquiries vs. Previous Month

Inquiries generated one month compared to the previous month, and the percentage change

(1.0001 * Inquiries) - ((1.0001 * [Inquiries Previous Month]) / (1.0001 * [Inquiries Previous Month])))

• Compound metric



Inquiry Items Number of items for an inquiry

[Sales Document Items] {~} <Inquiries>• Conditional metric with base formula [Sales

Document Items] and filter Inquiries

Inquiry Items Previous Month

Same as Inquiry Items but calculated for the month prior to the one selected

[Sales Document Items] {~} <Inquiries> | [Previous Month] |

• Conditional-transformation metric with base formula [Sales Document Items], filter Inquiries, and transformation Previous Month

% Change Inquiries Items vs. Previous Month

Inquiry items generated one month compared to the previous month, and the percentage change

(((1.0001 * [Inquiry Items]) - (1.0001 * [Inquiry Items Previous Month])) / (1.0001 * [Inquiry Items Previous Month]))

• Compound metric

Average Number of Items per Inquiry

Average number of items per inquiry

([Sales Document Items] {~} <Inquiries> / [Sales Documents] {~} <Inquiries>)

• Compound metric

Inquiries Quantity (Base Units)

Quantity in sales units for an inquiry item

[Item Quantity (Base Units)] {~} <Inquiries>• Conditional metric with base formula [Item

Quantity (Base Units)] and filter Inquiries

Inquiries Quantity (Base Units) Previous Month

Same as Inquiries Quantity (Base Units) but calculated for the month prior to the one selected

[Item Quantity (Base Units)] {~} <Inquiries> | [Previous Month] |

• Conditional-transformation metric with base formula [Item Quantity (Base Units)], filter Inquiries, and transformation Previous Month

% Change Inquiry Quantity vs. Previous Month

Inquiry quantity for one month compared to the previous month, and the percentage change

(((1.0001 * [Inquiries Quantity (Base Units)]) - (1.0001 * [Inquiries Quantity (Base Units) Previous Month])) / (1.0001 * [Inquiries Quantity (Base Units) Previous Month]))

• Compound metric

Average Quantity per Inquiry Item

Average quantity per inquiry item

([Inquiries Quantity (Base Units)] / [Inquiry Items])• Compound metric

Inquiries (Total for All Customer Regions)

Total number of inquiries for all the customer regions

[Sales Documents] {~, [Customer Region]} <Inquiries>

• Conditional-dimensional metric with base formula [Sales Documents], filter Inquiries, and metric level (dimensionality) of Report Level (standard) and Customer Region (filtering absolute, grouping none).

% Contribution to Total Inquiries for All Customer Regions

Weight of each customer region on the total number of inquiries for all regions.Note: See Contribution metrics ported to a database, page 160 for modifying metrics for different databases.

((1.0001 * Inquiries) / (1.0001 * [Inquiries (Total for All Customer Regions)]))

• Compound metric




Quotation metrics

This section lists all the metrics used to define Quotation reports.


Quotations Number of quotations (sales documents with type equal to “Quotation”)

[Sales Documents] {~} <Quotations>• Conditional metric with base formula [Sales

Documents] and filter Quotations

Quotations Previous Month

Same as Quotations but calculated for the month prior to the one selected

[Sales Documents] {~} <Quotations> | [Previous Month] |

• Conditional-transformation metric with base formula [Sales Documents], filter Quotations, and transformation Previous Month

% Change Quotations vs. Previous Month

Quotations generated for a month compared to the previous month, and the percentage change

(((1.0001 * Quotations) - (1.0001 * [Quotations Previous Month])) / (1.0001 * [Quotations Previous Month]))

• Compound metric

Quotation Items Number of items for a quotation [Sales Document Items] {~} <Quotations>• Conditional metric with base formula [Sales

Document Items] and filter Quotations

Average Number of Items per Quotation

Average number of items per quotation

([Sales Document Items] {~} <Quotations> / [Sales Documents] {~} <Quotations>)

• Compound metric

Quotations from Inquiries

Number of quotations that originate from an inquiry

[Sales Documents] {~} <[Quotations from Inquiries]>

• Conditional metric with base formula [Sales Documents] and filter Quotations from Inquiries

Quotations Not from Inquiries

Number of quotations that do not have an inquiry as a preceding document

(Quotations - [Quotations from Inquiries])• Compound metric

% Quotations from Inquiries vs. Total Quotations

Percentage of all quotations that originate from inquiries

((1.0001 * [Quotations from Inquiries]) / (1.0001 * Quotations))

• Compound metric

Conversion Rate (Inquiry to Quotation)

Percentage of all inquiries that are converted into quotations for a given time period

((1.0001 * [Quotations from Inquiries]) / (1.0001 * Inquiries))

• Compound metric

Quotations Items from Inquiries

Number of quotation items that originate from inquiries

[Sales Document Items] {~} <[Quotations from Inquiries]>

• Conditional metric with base formula [Sales Document Items] and filter Quotations from Inquiries



Conversion Rate Items (Inquiry to Quotation)

Percentage of all inquiry items that are converted into quotation items for a given time period

((1.0001 * [Quotation Items from Inquiries]) / (1.0001 * [Inquiry Items]))

• Compound metric

Quotations Quantity (Base Units)

Quantity associated with a quotation item

[Item Quantity (Base Units)] {~} <Quotations>• Conditional metric with base formula [Item

Quantity (Base Units)] and filter Quotations

Average Quantity (Base Units) per Quotation Item

Average quantity per quotation item

([Quotations Quantity (Base Units)] / [Quotation Items])

• Compound metric

Net Quotation Amount

Net amount for quotations [Net Item Amount] {~} <Quotations>• Conditional metric with base formula [Net

Item Amount] and filter Quotations

Average Net Amount per Quotation

Average net amount per quotation

([Net Quotation Amount] / Quotations)• Compound metric

Average Net Amount per Quotation Item

Average net amount per quotation item

([Net Quotation Amount] / [Quotation Items])• Compound metric

Net Quotation Amount Previous Month

Same as Net Quotation Amount but calculated for the month prior to the one selected

[Net Item Amount] {~} <Quotations> | [Previous Month] |

• Conditional-transformation metric with base formula [Net Item Amount], filter Quotations, and transformation Previous Month

% Change Net Amount Quotations vs. Previous Month

Net amount for one month compared to the previous month, and the percentage change

(([Net Quotation Amount] - [Net Quotation Amount Previous Month]) / [Net Quotation Amount Previous Month])

• Compound metric

Net Quotation Amount (Total for All Distribution Channels)

Net quotation amount aggregated for all distribution channels

[Net Item Amount] {~, [Distribution Channel]} <Quotations>

• Conditional-dimensional metric with base formula [Net Item Amount], filter Quotations, and metric level (dimensionality) of Report Level (standard) and Distribution Channel (filtering absolute, grouping none)

% Contribution to Total Net Quotation Amount for All Distribution Channels

Net quotation amount compared to the aggregated amount for all the distribution channels.Note: See Contribution metrics ported to a database, page 160 for modifying metrics for different databases.

([Net Quotation Amount] / [Net Quotation Amount (Total for All Distribution Channels)])

• Compound metric




Net Quotation Amount (Total for Product Hierarchy Level 1)

Net quotation amount aggregated for all elements of Product Hierarchy Level 1 that are children of a given Level 2 element

[Net Item Amount] {~, [Product Hierarchy Level 1]*} <Quotations>

• Conditional-dimensional metric with base formula [Net Item Amount], filter Quotations, and metric level (dimensionality) of Report Level (standard) and Product Hierarchy Level 1 (filtering absolute, grouping none)

% Contribution to Product Level 1 Net Quotation Amount

Net quotation amount compared to the aggregated amount for all elements of Product Hierarchy Level 1 that are children of a given Level 2 element.Note: See the Contribution metrics ported to a database, page 160 for modifying metrics for different databases.

([Net Quotation Amount] / [Net Quotation Amount (Total for Product Hierarchy Level 1)])

• Compound metric

Net Quotation Amount (Total for Product Hierarchy Level 2)

Net quotation amount aggregated for all elements in level 2

[Net Item Amount] {~, [Product Hierarchy Level 2]*} <Quotations>

• Conditional-dimensional metric with base formula [Net Item Amount], filter Quotations, and metric level (dimensionality) of Report Level (standard) and Product Hierarchy Level 2 (filtering absolute, grouping none)

% Contribution to Product Level 2 Net Quotation Amount

Net quotation amount compared to the aggregated amount for all the elements in product level 2.Note: See the Contribution metrics ported to a database, page 160 for modifying metrics for different databases.

([Net Quotation Amount] / [Net Quotation Amount (Total for Product Hierarchy Level 2)])

• Compound metric

Rejected Quotation Items

Number of quotation items with an item status of “Rejected”

[Sales Document Items] {~} <[Rejected Quotation Items]>

• Conditional metric with base formula [Sales Document Items] and filter Rejected Quotation Items

% Rejected Items vs. Quotation Items

Percentage of rejected items compared to the total number of quotation items

((1.0001 * [Rejected Quotation Items]) / (1.0001 * [Quotation Items]))

• Compound metric

Net Amount Rejected Quotation Items

Net amount for those quotation items that have been rejected

[Net Item Amount] {~} <[Rejected Quotation Items]>

• Conditional metric with base formula [Net Item Amount] and filter Rejected Quotation Items




Sales Order metrics

This section lists all the metrics used to define Sales Order reports. This set of metrics is divided into the following subsections:

• Counts metrics

• Amounts and Quantities metrics

• Customer metrics

Counts metrics


Sales Orders Number of sales orders [Sales Documents] {~} <[Sales Orders]>• Conditional metric with base formula [Sales

Documents] and filter Sales Orders

Sales Orders Previous Month

Number of sales orders, calculated for the month prior to the one selected

[Sales Documents] {~} <[Sales Orders]> | [Previous Month] |

• Conditional-transformation metric with base formula [Sales Documents], filter Sales Orders, and transformation Previous Month

% Change Sales Orders vs. Previous Month

Sales orders generated one month compared to the previous month, and the percentage change

(((1.0001 * [Sales Orders]) - (1.0001 * [Sales Orders Previous Month])) / (1.0001 * [Sales Orders Previous Month])

• Compound metric

Sales Orders Previous Quarter

Number of sales orders, calculated for the quarter prior to the one selected

[Sales Documents] {~} <[Sales Orders]> | [Previous Quarter] |

• Conditional-transformation metric with base formula [Sales Documents], filter Sales Orders, and transformation Previous Quarter

% Change Sales Orders vs. Previous Quarter

Sales orders generated one quarter compared to the previous quarter, and the percentage change

(((1.0001 * [Sales Orders]) - (1.0001 * [Sales Orders Previous Quarter])) / (1.0001 * [Sales Orders Previous Quarter]))

• Compound metric

Sales Orders from Quotations

Number of sales orders generated from a quotation

[Sales Documents] {~} <[Sales Orders from Quotations]>

• Conditional metric with base formula [Sales Documents] and filter Sales Orders from Quotations

Sales Orders Not from Quotations

Number of sales orders that do not have a quotation as the preceding document

([Sales Orders] - [Sales Orders from Quotations])

• Compound metric



Amounts and Quantities metrics

Conversion Rate (Quotation to Sales Order)

Percentage of all quotations that are converted into sales orders for a given time period

([Sales Orders from Quotations] / Quotations)• Compound metric

% Sales Orders from Quotations vs. Total Sales Orders

Percentage of sales orders from quotations compared to all sales orders

((1.0001 * [Sales Orders from Quotations]) / (1.0001 * [Sales Orders]))

• Compound metric

Sales Order Items Number of sales order items [Sales Document Items] {~} <[Sales Orders]>• Conditional metric with base formula [Sales

Document Items] and filter Sales Orders

Sales Order Items Previous Month

Number of sales order items, calculated for the month prior to the one selected

[Sales Document Items] {~} <[Sales Orders]> | [Previous Month] |

• Conditional-transformation metric with base formula [Sales Document Items], filter Sales Orders and transformation Previous Month

% Change Sales Order Items vs. Previous Month

Sales orders items generated one month compared to the previous month, and the percentage change

(((1.0001 * [Sales Order Items]) - (1.0001 * [Sales Order Items Previous Month])) / (1.0001 * [Sales Order Items Previous Month]))

• Compound metric

Sales Order Items from Quotations

Number of sales order items generated from a quotation item

[Sales Document Items] {~} <[Sales Orders from Quotations]>

• Conditional metric with base formula [Sales Document Items] and filter Sales Orders from Quotations

Conversion Rate Items (Quotation to Sales Order)

Percentage of all quotation items that are converted into sales order items for a given time period

((1.0001 * [Sales Order Items from Quotations]) / (1.0001 * [Quotation Items]))

• Compound metric

Average Number of Items per Sales Order

Average number of sales order items per sales order

([Sales Document Items] {~} <[Sales Orders]> / [Sales Documents] {~} <[Sales Orders]>)

• Compound metric



Sales Orders Quantity (Base Units)

Sales order quantity measured in base units

[Item Quantity (Base Units)] {~} <[Sales Orders]>

• Conditional metric with base formula [Sales Documents] and filter Sales Orders

Average Quantity per Sales Order Item

Average quantity per sales order item

([Sales Orders Quantity (Base Units)] / [Sales Order Items])

• Compound metric



Net Price Sales Orders (Base Units)

Average net unit price (based on base units); calculated in the database for each sales order item as the net item amount divided by the item quantity and then aggregated using average

[Net Item Price (Base Units)] {~} <[Sales Orders]>

• Conditional metric with base formula [Net Item Price (Base Units)] and filter Sales Orders

Net Price Sales Orders (Base Units) Previous Month

Same as Net Price Sales Orders (Base Units) but calculated for the month prior to the one selected

[Net Item Price (Base Units)] {~} <[Sales Orders]> | [Previous Month] |

• Conditional-transformation metric with base formula [Net Item Price (Base Units)], filter Sales Orders, and transformation Previous Month

% Change Net Price vs. Previous Month

Sales orders net price for one month compared to the previous month, and the percentage change

(([Net Price Sales Orders (Base Units)] - [Net Price Sales Orders (Base Units) Previous Month]) / [Net Price Sales Orders (Base Units) Previous Month])

• Compound metric

Net Sales Order Amount

Net amount for sales orders [Net Item Amount] {~} <[Sales Orders]>• Conditional metric with base formula [Net

Item Amount] and filter Sales Orders

Net Sales Order Amount Previous Month

Net amount for sales orders but calculated for the month prior to the one selected

[Net Item Amount] {~} <[Sales Orders]> | [Previous Month] |

• Conditional-transformation metric with base formula [Net Item Amount], filter Sales Orders, and transformation Previous Month

% Change Net Sales Order Amount vs. Previous Month

Sales orders net amount for one month compared to the previous month, and the percentage change

(([Net Sales Order Amount] - [Net Sales Order Amount Previous Month]) / [Net Sales Order Amount Previous Month])

• Compound metric

Net Sales Order Amount Previous Quarter

Net amount for sales orders but calculated for the quarter prior to the one selected

[Net Item Amount] {~} <[Sales Orders]> | [Previous Quarter] |

• Conditional-transformation metric with base formula [Net Item Amount], filter Sales Orders and transformation Previous Quarter

% Change Net Sales Order Amount vs. Previous Quarter

Sales orders net amount for one quarter compared to the previous quarter, and the percentage change

(([Net Sales Order Amount] - [Net Sales Order Amount Previous Quarter]) / [Net Sales Order Amount Previous Quarter])

• Compound metric

Average Net Amount per Sales Order

Average net amount per sales order

([Net Sales Order Amount] / [Sales Orders])• Compound metric

Average Net Amount per Sales Order Previous Month

Average net amount per sales order, calculated for the month prior to the one selected

([Net Sales Order Amount Previous Month] / [Sales Orders Previous Month])

• Compound metric




% Change Average Net Sales Order Amount vs. Previous Month

Average net amount per sales order for one month compared to the previous month, and the percentage change

(([Average Net Amount per Sales Order] - [Average Net Amount per Sales Order Previous Month]) / [Average Net Amount per Sales Order Previous Month])

• Compound metric

Average Net Amount per Sales Order Item

Average net amount per sales order item

([Net Sales Order Amount] / [Sales Order Items])

• Compound metric

Net Sales Order Amount from Quotations

Net amount for those sales orders that have a quotation as the preceding document

[Net Item Amount] {~} <[Sales Orders from Quotations]>

• Conditional metric with base formula [Net Item Amount] and filter Sales Orders from Quotations

Conversion Rate Net Amount (Quotation to Sales Order)

Percentage of all quotation amounts that are converted into sales order amounts for a given time period

([Net Sales Order Amount from Quotations] / [Net Quotation Amount])

• Compound metric

Net Sales Order Amount (Total for All Material Divisions)

Net amount summarized for all material divisions

[Net Item Amount] {~, ![Material Division]*} <[Sales Orders]>

• Conditional-dimensional metric with base formula [Net Item Amount], filter Sales Orders, and metric level (dimensionality) of Report Level (standard) and Material Division (filtering absolute, grouping none)

% Contribution to Total Net Sales Order Amount for All Material Divisions

Contribution of each material division to the overall net amount of all material divisions.Note: See the Contribution metrics ported to a database section below for modifying metrics for different databases.

([Net Sales Order Amount] / [Net Sales Order Amount (Total for All Material Divisions)])

• Compound metric

Net Sales Order Amount (Total for Sales Office)

Net amount summarized for all sales offices

[Net Item Amount] {Month, [Sales Office]} <[Sales Orders]>

• Conditional-dimensional metric with base formula [Net Item Amount], filter Sales Orders, and metric level (dimensionality) of Month (standard) and Sales Office (standard)

% Contribution to Sales Office Net Sales Order Amount

Contribution of each sales office to the overall net amount of all sales offices.Note: See the Contribution metrics ported to a database section below for modifying metrics for different databases.

([Net Sales Order Amount] / [Net Sales Order Amount (Total for Sales Office)])

• Compound metric




Net Sales Order Amount (Total for Sales Office) Previous Month

Net amount summarized for all sales offices, calculated for the month prior to the one selected

[Net Item Amount] {Month, [Sales Office]} <[Sales Orders]> | [Previous Month] |

• Conditional-transformation-dimensional metric with base formula [Net Item Amount], filter Sales Orders, transformation Previous Month, and metric level (dimensionality) of Month (standard) and Sales Office (standard)

% Contribution to Sales Office Net Sales Order Amount Previous Month

Contribution of each sales office to the overall net amount of all sales offices, calculated for the month prior to the one selected.Note: See the Contribution metrics ported to a database section below for modifying metrics for different databases.

([Net Sales Order Amount Previous Month] / [Net Sales Order Amount (Total for Sales Office) Previous Month])

• Compound metric

Contribution Monthly Trend

Sales office contributions compared month to month.Note: See the Contribution metrics ported to a database section below for modifying metrics for different databases.

(([% Contribution to Sales Office Net Sales Order Amount] - [% Contribution to Sales Office Net Sales Order Amount Previous Month]) / [% Contribution to Sales Office Net Sales Order Amount Previous Month])

Cost Sales Orders Cost associated with sales orders

[Item Cost] {~} <[Sales Orders]>• Conditional metric with base formula [Item

Cost] and filter Sales Orders

Cost Sales Orders Previous Month

Cost associated with sales orders, calculated for the month prior to the one selected

[Item Cost] {~} <[Sales Orders]> | [Previous Month] |

• Conditional-transformation metric with base formula [Item Cost], filter Sales Orders, and transformation Previous Month

Net Margin Sales Orders

Net margin associated with sales order

([Net Sales Order Amount] - [Cost Sales Orders])

• Compound metric

% Net Margin Sales Orders

Net margin percentage calculated by dividing net margin by cost sales orders

(([Net Sales Order Amount] - [Cost Sales Orders]) / [Net Sales Order Amount])

• Compound metric

% Net Margin Sales Orders Previous Month

Same as % Net Margin Sales Orders but calculated for the month prior to the one selected

(([Net Sales Order Amount Previous Month] - [Cost Sales Orders Previous Month]) / [Net Sales Order Amount Previous Month])

• Compound metric




Customer metrics

% Change Net Margin vs. Previous Month

Percentage margin compared month to month

(([% Net Margin Sales Orders] - [% Net Margin Sales Orders Previous Month]) / [% Net Margin Sales Orders Previous Month])

• Compound metric

Average Net Margin per Sales Order

Average margin (absolute values) by sales order

([Net Margin Sales Orders] / [Sales Orders])• Compound metric



Customers with Sales Orders

Number of customers with sales orders for a given time period

[Customers with Sales Documents] {~} <[Sales Orders]>

• Conditional metric with base formula [Customers with Sales Documents] and filter Sales Orders

Customers with Sales Orders Two Quarters

Number of customers that had sales orders during two consecutive quarters

[Customers with Sales Documents] {~} <[Set of Customers with Sales in two consecutive quarters]>

• Conditional metric with base formula [Customers with Sales Documents] and filter Set of customers with sales in two consecutive quarters

% Customers with Sales Orders Two Quarters

Percentage of customers with sales orders that also had sales orders the previous quarter

([Customers with Sales Orders two Quarters] / [Customers with Sales Orders])

• Compound metric

Registered Customers

Number of customers in the table L_CUSTOMER (this metric does not consider when they become customers)

Count(Customer) {~}

% Customers with Sales Orders vs. Registered Customers

Percentage of customers that had sales orders compared to the entire customer base

([Customers with Sales Orders] / [Registered Customers])

• Compound metric

Net Sales Order Amount from Customers with Sales Orders Two Quarters

Sales amounts from customers with sales in two consecutive quarters

[Net Item Amount] {~} <[Sales Orders from Set of Customers with Sales in two consecutive quarters]>

• Conditional metric with base formula [Net Item Amount] and filter Sales orders from set of customers with sales in two consecutive quarters



Delivery metrics

This section lists all the metrics used to define Delivery reports.

% Net Amount from Customers with Sales Orders Two Quarters

Percentage representing the net amount from customers with sales in two consecutive quarters compared to total sales in the quarter

([Net Sales Order Amount from Customers with Sales Orders two Quarters] / [Net Sales Order Amount])

• Compound metric

Average Number of Orders per Customer

Average number of orders for those customers with sales orders

([Sales Orders] / [Customers with Sales Orders])

• Compound metric

Average Net Sales Order Amount per Customer

Average net amount for those customers with sales orders

([Net Sales Order Amount] / [Customers with Sales Orders])

• Compound metric



Outbound Deliveries Number of outbound deliveries

[Delivery Documents] {~} <[Outbound Deliveries]>

• Conditional metric with base formula [Delivery Documents] and filter Outbound Deliveries

Outbound Deliveries Previous Month

Same as Outbound Deliveries but calculated for the month prior to the one selected

[Delivery Documents] {~} <[Outbound Deliveries]> | [Previous Month] |

• Conditional-transformation metric with base formula [Delivery Documents], filter Outbound Deliveries, and transformation Previous Month

% Change Outbound Deliveries vs. Previous Month

Number of outbound deliveries month to month

(([Outbound Deliveries] - [Outbound Deliveries Previous Month]) / [Outbound Deliveries Previous Month])

• Compound metric

Outbound Delivery Items Number of outbound delivery items

[Delivery Document Items] {~} <[Outbound Deliveries]>

• Conditional metric with base formula [Delivery Document Items] and filter Outbound Deliveries



Average Delay Confirmed to Actual Delivery for Outbound Deliveries (Days)

Average delay for outbound deliveries between the confirmed date for delivery and the actual date when goods arrive at the customer site (measured in days)

[Average Delay between Confirmed and Actual Delivery Date (Days)] {~} <[Outbound Deliveries]>

• Conditional metric with base formula [Average Delay between Confirmed and Actual Delivery date (days)] and filter Outbound Deliveries

Average Delay Planned to Actual Goods Issue for Outbound Deliveries (Days)

Average delay for outbound deliveries between the date when goods are planned for issue and the actual issue date (in days)

[Average Delay between Planned and Issue Date (Days)] {~} <[Outbound Deliveries]>

• Conditional metric with base formula [Average Delay between Planned and Actual Issue Date (days)] and filter Outbound Deliveries

Average Delay Requested to Actual Delivery for Outbound Deliveries (Days)

Average delay for outbound deliveries between the requested day for delivery and the actual delivery date (in days)

[Average Delay between Requested and Actual Delivery Date (Days)] {~} <[Outbound Deliveries]>

• Conditional metric with base formula [Average Delay between Requested and Actual Delivery Date (days)] and filter Outbound Deliveries

Average Delay Requested to Confirmed Delivery for Outbound Deliveries (Days)

Average delay for outbound deliveries between the requested date and the date when goods are confirmed for arrival (measured in days)

[Average Delay between Requested and Confirmed Delivery Date (Days)] {~} <[Outbound Deliveries]>

• Conditional metric with base formula [Average Delay between Requested and Confirmed Delivery Date (days)] and filter Outbound Deliveries

Average Loading Time for Outbound Deliveries (Days)

Average time from when materials are packaged until they are issued (measured in days)

[Average Loading Time (Days)] {~} <[Outbound Deliveries]>

• Conditional metric with base formula [Average Loading Time (Days)] and filter Outbound Deliveries

Average Shipping Processing Time for Outbound Deliveries (Days)

Average time from when materials are available until they are issued (measured in days)

[Average Shipping Processing Time (Days)] {~} <[Outbound Deliveries]>

• Conditional metric with base formula [Average Shipping Processing Time (Days)] and filter Outbound Deliveries

Average Transportation Time for Outbound Deliveries (Days)

Average time from when materials are issued until they reach the customer site (measured in days)

[Average Transportation Time (Days)] {~} <[Outbound Deliveries]>

• Conditional metric with base formula [Average Transportation Time (Days)] and filter Outbound Deliveries




Average Total Delivery Time for Outbound Deliveries (Days)

Total delivery time from when materials are available until they reach the customer site (measured in days)

[Average Total Delivery Time (Days)] {~} <[Outbound Deliveries]>

• Conditional metric with base formula [Average Total Delivery Time (Days)] and filter Outbound Deliveries

Average Total Delivery Time for Outbound Deliveries Previous Month (Days)

Same as Average Total Delivery Time for Outbound Deliveries (Days) but calculated for the month prior to the one selected

[Average Total Delivery Time (Days)] {~} <[Outbound Deliveries]> | [Previous Month] |

• Conditional-transformation metric with base formula [Average Total Delivery Time (Days)], filter Outbound Deliveries, and transformation Previous Month

% Change Total Delivery Time (Outbound Deliveries) vs. Previous Month

Number of outbound deliveries month to month

(([Average Total Delivery Time for Outbound Deliveries (Days)] - [Average Total Delivery Time for Outbound Deliveries Previous Month (Days)]) / [Average Total Delivery Time for Outbound Deliveries Previous Month (Days)])

• Compound metric

Delivered Quantity (Base Units) for Outbound Deliveries

Delivered quantity for outbound deliveries (measured in material base units)

[Delivered Quantity (Base Units)] {~} <[Outbound Deliveries]>

• Conditional metric with base formula [Delivered Quantity (Base Units)] and filter Outbound Deliveries

Delivered Quantity (Base Units) for Outbound Deliveries (Total for Material)

Total outbound delivery quantity for a specific material

[Delivered Quantity (Base Units)] {Material, Quarter} <[Outbound Deliveries]>

• Conditional-dimensional metric with base formula [Delivered Quantity (Base Units)], filter Outbound Deliveries, and hierarchy Material

% Contribution to Material Outbound Delivery Quantities

Material’s contribution to total quantities delivered.Note: See the Contribution metrics ported to a database section below for modifying metrics for different databases.

([Delivered Quantity (Base Units) for Outbound Deliveries] / [Delivered Quantity (Base Units) for Outbound Deliveries (Total for Material)])

• Compound metric

Average Delivered Quantity per Outbound Delivery Item

Average quantity delivered per outbound delivery item

([Delivered Quantity (Base Units) for Outbound Deliveries] / [Outbound Delivery Items])

• Compound metric

Confirmed Delivery Quantity (Base Units) for Outbound Deliveries

Confirmed delivery quantity for outbound deliveries (measured in material base units)

[Confirmed Delivery Quantity (Base Units)] {~} <[Outbound Deliveries]>

• Conditional metric with base formula [Confirmed Delivery Quantity (Base Units)] and filter Outbound Deliveries




Sales and Delivery Service metrics

This section lists all the metrics used to define Sales and Delivery Service reports.


Quantity delivered; generic for all types of delivery documents

[Delivered Quantity (Base Units)] {~}


Confirmed quantity delivered; generic for all types of delivery documents

[Confirmed Delivery Quantity (Base Units)] {~}



Delivery Items Number of delivery items for any type of delivery document

[Delivery Document Items] {~}

Delivery Items (with Correct Quantity Delivered)

Number of delivery items for any type of delivery document where delivery and confirmed quantity is equal

[Delivery Document Items] {~} <[Set of Items - Correctly Delivered Quantities]>

• Conditional metric with base formula [Delivery Document Items] and filter Set of Items - Correctly Delivered Quantities

Delivery Items (with Incorrect Quantity Delivered)

Number of delivery items for any type of delivery document where delivery and confirmed quantity is different

[Delivery Document Items] {~} <[Set of Items - Incorrectly Delivered Quantities]>

• Conditional metric with base formula [Delivery Document Items] and filter Set of Items - Incorrectly Delivered Quantities

% Delivery Items with Incorrect Quantity Delivered vs.Total

Percentage of delivery items with incorrect quantity compared to the total number of delivery items

((1.0001 * [Delivery Items (with Incorrect Quantity Delivered)]) / ((1.0001 * [Delivery Items (with Correct Quantity Delivered)]) + (1.0001 * [Delivery Items (with Incorrect Quantity Delivered)])))

• Compound metric

Delivery Items (Delivered On Time)

Number of delivery items for any type of delivery document where items were delivered on the confirmed delivery date

[Delivery Document Items] {~} <[Set of Items - Delivered On Time]>

• Conditional metric with base formula [Delivery Document Items] and filter Set of Items - Delivered On Time



Delivery Items (Delivered Late)

Number of delivery items for any type of delivery document where items were delivered later than the confirmed delivery date

[Delivery Document Items] {~} <[Set of Items - Delivered Late]>

• Conditional metric with base formula [Delivery Document Items] and filter Set of Items - Delivered Late

% Delivery Items Delivered Late vs.Total

Percentage of the total delivery items that were delivered late

([Delivery Items (Delivered Late)] / [Delivery Items])

• Compound metric

Difference Confirmed to Delivery Quantity

Difference between confirmed delivery quantity and actual delivery quantity

([Confirmed Delivery Quantity (Base Units)] {~} - [Delivered Quantity (Base Units)] {~} )

• Compound metric

Returns Number of sales documents of type “returns”

[Sales Documents] {~} <Returns>• Conditional metric with base

formula [Sales Documents] and filter Returns

Return Items Number of sales document items of type “returns”

[Sales Document Items] {~} <Returns>

• Conditional metric with base formula [Sales Document Items] and filter Returns

Rejected Return Items Number of sales document items of type Returns and with an item status of Rejected

[Sales Document Items] {~} <[Rejected Return Items]>

• Conditional metric with base formula [Sales Documents] and filter Rejected Return Items

Accepted Return Items Number of sales document items of type Returns and with an item status of Complete

[Sales Document Items] {~} <[Accepted Return Items]>

• Conditional metric with base formula [Sales Documents] and filter Accepted Return Items

% Accepted Return Items vs. Total Return Items

Percentage of total return items that have been accepted

(((1.0001 * [Return Items]) - (1.0001 * [Rejected Return Items])) / (1.0001 * [Return Items]))

• Compound metric

% Accepted Return Items vs. Total Sales Order Items

Percentage of accepted return items compared to the total number of sales order items

((1.0001 * [Accepted Return Items]) / (1.0001 * [Sales Order Items]))

• Compound metric

Accepted Returns Quantity (Base Units)

Quantity of accepted returns [Item Quantity (Base Units)] {~} <[Accepted Return Items]>

• Conditional metric with base formula [Item Quantity (Base Unit)] and filter Accepted Return Items




Return Deliveries Number of delivery documents of type Return Deliveries

[Delivery Documents] {~} <[Return Deliveries]>

• Conditional metric with base formula [Delivery Documents] and filter Return Deliveries

% Return Deliveries vs. Outbound Deliveries

Total number of outbound deliveries compared to return deliveries

((1.0001 * [Return Deliveries]) / (1.0001 * [Outbound Deliveries]))

• Compound metric

Delivered Quantity (Base Units) for Return Deliveries

Delivered quantity for delivery documents of type Return Deliveries

[Delivered Quantity (Base Units)] {~} <[Return Deliveries]>

• Conditional metric with base formula [Delivered Quantity (Base Units)] and filter Return Deliveries

% Quantity for Return Deliveries vs. Total for Outbound Deliveries

Quantity of return deliveries compared to the quantity of outbound deliveries

([Delivered Quantity (Base Units) for Return Deliveries] / [Delivered Quantity (Base Units) for Outbound Deliveries])

• Compound metric

Net Return Amount Net amount for sales documents of type Returns

[Net Item Amount] {~} <Returns>• Conditional metric with base

formula [Net Item Amount] and filter Returns

Net Amount Rejected Return Items

Net amount for all return items with a status of Rejected

[Net Item Amount] {~} <[Rejected Return Items]>

• Conditional metric with base formula [Net Item Amount] and filter Reject Return Items

Net Amount Accepted Return Items

Net amount for all return items that were accepted

[Net Item Amount] {~} <[Accepted Return Items]>

• Conditional metric with base formula [Net Item Amount] and filter Accepted Return Items

% Accepted Net Return Amount vs. Net Sales Order Amount

Net amount of returns compared to the net amount of sales orders

([Net Amount Accepted Return Items] / [Net Sales Order Amount])

• Compound metric

% Quantity for Outbound Deliveries vs. Quantity Sales Orders

Quantity delivered in one period of time compared to the quantity sold in the same period

([Delivered Quantity (Base Units) for Outbound Deliveries] / [Sales Orders Quantity (Base Units)])

• Compound metric

Order Quantity Delivered (Base Units)

Sold quantity that has already been delivered

[Item Ordered Quantity Delivered (Base Units)] {~} <[Sales Orders]>

• Conditional metric with base formula [Item Ordered Quantity Delivered] and filter Sales Orders




Contribution metrics ported to a database

Contribution metrics are handled differently in different databases, due to the way integers are truncated. If you port SDAM to a supported SQL Server, DB2, or Teradata database, modify all contribution metrics by multiplying and dividing by 1.1, as shown in the following examples:

• Modify the definition of % Contribution to Product Level 1 Net Quotation Amount to ((1.1 * [Net Quotation Amount]) / (1.1 * [Net Quotation Amount (Total for Product Hierarchy Level 1)]))

• Modify the definition of % Contribution to Product Level 2 Net Quotation Amount to ((1.1 * [Net Quotation Amount]) / (1.1 * [Net Quotation Amount (Total for Product Hierarchy Level 2)]))

Backlog Quantity (Base Units)

Quantity pending delivery for sales orders

[Backlog Item Quantity (Base Units)] {~} <[Sales Orders]>

• Conditional metric with base formula [Backlog Item Quantity (Base Units)] and filter Sales Orders

Fulfillment Rate (Quantity)

Proportion of quantities that have already been delivered, for those sales orders with some pending delivery

([Order Quantity Delivered (Base Units)] / [Sales Orders Quantity (Base Units)])

• Compound metric

% Backlog vs. Ordered Quantity

Backlog quantities of sales orders with pending deliveries compared to the ordered quantities

([Backlog Quantity (Base Units)] / [Sales Orders Quantity (Base Units)])

• Compound metric

Backlog Net Amount Net amount of the quantities pending delivery for sales orders

[Backlog Net Item Amount] {~} <[Sales Orders]>

• Conditional metric with base formula [Backlog Net Item Amount] and filter Sales Orders

Fulfillment Rate (Net Amount)

Fulfillment rate based on the net amount of backlog quantities, for those sales orders with some pending delivery

(1 - ([Backlog Net Amount] / [Net Sales Order Amount]))

• Compound metric



© 2010 MicroStrategy, Inc. Filters glossary 161

Filters glossaryThis section lists all the filters used in SDAM.

All filter definitions are located in the Public Objects\Filters folder in the MicroStrategy project. Filters are organized into the following two subfolders in the MicroStrategy project. These divisions are reflected in the tables in the following sections:

• Filters for conditional metrics

• Filters embedded in reports

All the qualifications in specific attribute values are based on the values in SDAM’s default data warehouse. For example, Sales Document Status = Open or Delivery Document Status = Complete. If these values are different in a production environment, the qualification must be updated in each of the filters.

Filters for conditional metrics

The following table lists all the filters used to define the conditional metrics in the previous Metrics glossary tables.

Filter Description

Inquiries Identifies sales documents of type Inquiry.• Defined as an attribute qualification: Sales Document Type = Inquiry.• Used on conditional metrics.

Quotations Identifies sales documents of type Quotation.• Defined as an attribute qualification: Sales Document Type = Quotation.• Used on conditional metrics.

Reference Doc. Type = Inquiry

Identifies sales documents with a reference document of type Inquiry.• Defined as an attribute qualification: Reference Document Type = Inquiry.• Used as an embedded filter to define a filter for a conditional metric.

Quotations from Inquiries

Identifies sales documents of type Quotation and with a reference document of type Inquiry.

• Defined based on two other filters: Quotations and Reference Doc. Type = Inquiry.• Used on conditional metrics.

Rejected Quotation Items

Identifies sales document items of type Quotation with an item status of Rejected.• Defined as an attribute qualification: Sales Document Type = Quotation and Sales

Document Item Status = Rejected.• Used on conditional metrics.


162 Filters glossary © 2010 MicroStrategy, Inc.

Sales Orders Identifies sales documents of type Standard Sales Order.• Defined as an attribute qualification: Sales Document Type = Standard Sales Order.• Used on conditional metrics.

Reference Doc. Type = Quotation

Identifies sales documents with a reference document of type Quotation.• Defined as an attribute qualification: Reference Document Type = Quotation.• Used as an embedded filter to define a filter for a conditional metric.

Sales Orders from Quotations

Identifies those sales documents of type Standard Sales Order with a reference document of type Quotations.

• Defined based on two other filters: Sales Orders and Reference Doc. Type = Quotation.

• Used on conditional metrics.

Returns Identifies sales documents of type Return.• Defined as an attribute qualification: Sales Document Type = Return.• Used on conditional metrics.

Accepted Return Items

Identifies sales document items of type Return that have been accepted (sales document item status of Complete).

• Defined as an attribute qualification: Sales Document Type = Return and Sales Document Item Status = Complete.


Rejected Return Items

Identifies sales document items of type Return with an item status of Rejected.• Defined as an attribute qualification: Sales Document Type = Return and Sales

Document Item Status = Rejected.• Used on conditional metrics.

Outbound Deliveries

Identifies delivery documents of type Outbound Delivery.• Defined as an attribute qualification: Delivery Document Type = Outbound Delivery.• Used on conditional metrics.• Also used as an embedded filter in two reports.

Return Deliveries

Identifies delivery documents of type Returns Delivery.• Defined as an attribute qualification: Delivery Document Type = Returns Delivery.• Used on conditional metrics.

Set of Customers with Sales in the Quarter

Identifies the set of customers that had sales orders in the selected quarter.• Defined as a set qualification in the metric Net Sales Order Amount, where the metric

value is greater than zero and the output level is at the Customer and Quarter level.• Used as an embedded filter to define a filter for a conditional metric.

Set of Customers with Sales in the Previous Quarter

Identifies the set of customers that had sales orders in the quarter prior to the one selected.

• Defined as a set qualification in the metric Net Sales Order Amount Previous Quarter, where the metric value is greater than zero and the output level is at the Customer and Quarter levels.

• Used as an embedded filter to define a filter for a conditional metric.

Filter Description


© 2010 MicroStrategy, Inc. Filters glossary 163

Set of Customers with Sales in Two Consecutive Quarters

Identifies the set of customers that had sales orders in a quarter and also had sales orders in the previous quarter.

• Defined based on two existing filters: Set of Customers with Sales in the Quarter and Set of Customers with Sales in the Previous Quarter.


Sales Orders from Set of Customers with Sales in Two Consecutive Quarters

Identifies the set of sales orders from customers that had sales orders in two consecutive quarters.

• Defined based on three existing filters: Set of Customers with Sales in the Quarter AND Set of Customers with Sales in the Previous Quarter AND Sales Orders.


Set of Items – Delivered Late

Identifies the set of delivery document items where the metric Average Delay Confirmed to Actual Delivery Date - Outbound Deliveries is greater than zero.

• Defined as a set qualification in the metric Average Delay Confirmed to Actual Delivery Date - Outbound Deliveries, where the metric value is greater than zero and the output level is at the delivery document item level.


Set of Items – Delivered On Time

Identifies the set of delivery document items where the metric Average Delay Confirmed to Actual Delivery Date - Outbound Deliveries is exactly zero.

• Defined as a set qualification in the metric Average Delay Confirmed to Actual Delivery Date - Outbound Deliveries, where the metric value is exactly zero and the output level is at the delivery document item level.


Set of Items – Correctly Delivered Quantities

Identifies the set of delivery document items where the metric Difference Confirmed to Delivered Quantity is equal to zero.

• Defined as a set qualification in the metric Difference Confirmed to Delivered Quantity, where the metric value is exactly zero and the output level is at the delivery document item level.


Set of Items – Incorrectly Delivered Quantities

Identifies the set of delivery document items where the metric Difference Confirmed to Delivered Quantity is different from zero.

• Defined as a set qualification in the metric Difference Confirmed to Delivered Quantity, where the metric value is different from zero and the output level is at the delivery document item level.

• Used on conditional metrics.• Also used as an embedded filter in one report.

Filter Description


164 Filters glossary © 2010 MicroStrategy, Inc.

Filters embedded in reports

Many of the SDAM reports include one or more static filters defining filtering conditions that apply to the report. The following table lists and describes all the embedded filters.

Filter Description

Open Status (Sales Document)

Identifies sales documents with a status of Open.• Defined as an attribute qualification: Sales Document Status = Open.• Used as an embedded filter in several reports.

Rank Net Sales Order Amount – Top 5 by Quarter

Identifies for each quarter the report elements that are ranked the top five by the Net Sales Order Amount metric.

• Defined as a set qualification in the Net Sales Order Amount metric, with rank set at top five and break by attribute Quarter.

• Used as an embedded filter in two reports.

Rank Net Sales Order Amount – Top 5 Sales Orders by Quarter

Identifies the sales orders that are ranked the top five for each quarter by the Net Sales Order Amount metric.

• Defined as a set qualification in the metric Net Sales Order Amount, with rank set at top five, output level Sales Document, and break by attribute Quarter.

• Used as an embedded filter in one report.

Rank Net Sales Order Amount – Top 5 Sales Orders from Quotations

Identifies the sales orders from quotations that are ranked the top five for each quarter by the Net Sales Order Amount metric.

• Defined as a combination of filter Sales Orders from Quotations AND set qualification in the metric Net Sales Order Amount, rank top 5, with output level Sales Document and break by attribute Quarter.


Rank % Net Margin – Bottom 3 by Quarter

Identifies for each quarter the report elements that are ranked the bottom three by the % Net Margin Sales Orders metric.

• Defined as a set qualification in the metric % Net Margin Sales Orders, rank bottom three, and break by attribute Quarter.


Rank Net Sales Order Amount – Top 5

Identifies the sales orders from quotations that are ranked the top five by the Net Sales Order Amount metric.

• Defined as a combination of filter Sales Orders from Quotations AND set qualification in the metric Net Sales Order Amount, rank top five, with output level Sales Document.

• Used as an embedded filter for a scorecard’s report.

Open Deliveries – Issued and Pending Delivery

Identifies delivery document items that did not yet arrive at the customer site.• Defined as an attribute qualification: Delivery Document Item Status =

Open-Pending Deliver Cust.• Used as an embedded filter in one report.

Open Deliveries – Pending Goods Issue

Identifies delivery document items that were not yet issued.• Defined as an attribute qualification: Delivery Document Item Status =

Open-Pending Goods Issue.• Used as an embedded filter in one report.


© 2010 MicroStrategy, Inc. Prompts glossary 165

Prompts glossaryThis section lists all the prompts used in SDAM reports. The majority of the SDAM reports have prompts as embedded filters, allowing you to select the report qualification from a list of elements at run time. Prompts always require an answer for reports to run.

Open Deliveries – Pending Material Availability

Identifies delivery document items that are pending availability of materials.• Defined as an attribute qualification: Delivery Document Item Status =

Open-Pending Mat Availability.• Used as an embedded filter in one report.

Complete Status (Delivery Document)

Identifies delivery documents with a status of Complete.• Defined as an attribute qualification: Delivery Document Status = Complete.• Used as an embedded filter in several reports.

Outbound Deliveries Identifies delivery documents of type Outbound Delivery.• Defined as an attribute qualification: Delivery Document Type = Outbound

Delivery.• Used on conditional metrics.• Also used as an embedded filter in two reports.

All Types of Outbound Deliveries

Identifies delivery documents of type Outbound Delivery or Free Subsequent Delivery.

• Defined as an attribute qualification: Delivery Document Type in list (Outbound Delivery, Free Subsequent Delivery).

• Used as an embedded filter in two reports.

Set of Items – Incorrectly Delivered Quantities

Identifies the set of delivery document items where the metric Difference Confirmed to Delivered Quantity is different from zero.

• Defined as a set qualification in the metric Difference Confirmed to Delivered Quantity, where the metric value is different from zero and the output level is at the delivery document item level.

• Used on conditional metrics.• Also used as an embedded filter in one report.

Filter Description

Prompt Description

Customer – Multiple Selections Elements prompt: One or more customers must be selected

Customer – One Selection Elements prompt: One customer must be selected

Material Division – One Selection Elements prompt: One material division must be selected

Quarter – One Selection Elements prompt: One quarter must be selected

Material - One Selection Elements prompt: One material must be selected

Year – One Selection Elements prompt: One year must be selected


166 Prompts glossary © 2010 MicroStrategy, Inc.

Select from Product Hierarchy Elements

Expression prompt: One or more elements from any level in the Product hierarchy must be selected

Select Organization Attributes Object prompt: One or more organization attributes must be selected, and are used in the report template

Select Customer Characteristics Object prompt: One or more customer characteristics must be selected, and are used in the report template

Select Material Grouping Attributes Object prompt: One or more material grouping attributes must be selected, and are used in the report template

Prompt Description


BB.STRUCTURE OF SDAM’S BUSINESS LOGIC: LOGICAL DATA MODEL

Introduction

This appendix presents the logical data model on which the Sales and Distribution Analysis Module (SDAM) is built.

This appendix provides a description for:

• Business hierarchies, including their attributes, attribute definitions, relationships, forms, form expressions, lookup tables, and other tables

• Module transformations and user hierarchies

• Module facts

See Chapter 1, Introduction to the Sales & Distribution Analysis Module, for a general description, basic procedures, and additional details about understanding and working with SDAM’s logical data model.

Information can also be found by accessing each attribute’s definition using the Attribute Editor. The attributes can be found in the Schema Objects/ Attributes folder. Double-click an attribute to open the Attribute Editor.

Structure of SDAM’s Business Logic: Logical Data Model Sales & Distribution Analysis Module ReferenceB

168 Prerequisites © 2010 MicroStrategy, Inc.

PrerequisitesThis appendix assumes you have prior experience with logical data modeling and creating business intelligence applications using MicroStrategy technology.

SDAM logical schemaThe following diagram represents the logical model shipped with SDAM. The logical schema diagram is available in an Erwin file, located in Program Files\MicroStrategy\ Analytics Modules\Sdam\SDAM.er1.

Sales & Distribution Analysis Module Reference Structure of SDAM’s Business Logic: Logical Data Model B

© 2010 MicroStrategy, Inc. SDAM logical schema 169

Fact tables appear in gray.


170 Business hierarchies © 2010 MicroStrategy, Inc.

Business hierarchiesSDAM assists analysts, managers, and executives to obtain insight into the various factors that affect a business’s inquiry, quotation, order, delivery, and returns processes. SDAM accomplishes this partly through a set of attributes (business concepts) and their relationships to each other. These attributes are arranged in a specific sequence according to a business structure, and that arrangement is called a hierarchy.

The key business hierarchies in the sales and distribution analysis process are:

• Organization: The external organization responsible for selling the company’s materials, and the internal organization structures responsible for sales and delivery processing

• Material: The products or services sold by the company

• Customer: The entities to which materials are sold and distributed

• Shipping Point: Location through which materials are delivered to customers

• Sales Document: Tracks all sales transactions

• Delivery Document: Tracks all delivery transactions

• Time: The calendar time

Each business hierarchy is detailed in the following sections. For additional information on the hierarchies, see the MicroStrategy project definitions in SDAM’s Schema Objects\Attributes and Schema Objects\Facts folders. From one of these folders, double-click an attribute or fact to view definitions, properties, source tables, and so on.

Organization hierarchy

This hierarchy represents the company’s organizational structures, and is broken into the following subdivisions:

• External Organization: The external organization responsible for selling materials

• Internal Organization: The internal organization responsible for processing sales and delivery activities


© 2010 MicroStrategy, Inc. Business hierarchies 171

In the logical data model, these subdivisions are defined as independent hierarchies.

External Organization hierarchy

This hierarchy represents the company’s external organization through which materials (products and services) are sold to customers. A sales organization can sell materials through different distribution channels.

Note the following:

• A check sign in the previous figure indicates the attribute is an entry level in the corresponding hierarchy.

• In the logical data model, Sales Organization and Distribution Channel are defined as independent entities and the relationship is established through the fact tables.

Attribute Description Example

Sales Organization

Organizational unit responsible for selling material or services. From the customer point of view, this is the unit that holds the responsibility for delivery and material liabilities.

• It has two parent attributes: each Sales Organization is assigned to exactly one Company Code and one Industry Sector.

• Each business transaction is linked to a sales organization. (It is one of the attribute’s keys in the fact tables).

USA West – responsible for direct sales and retail wholesale in the West region

Sales Organization Industry Sector

Industry to which the sales organization provides service.

• An industry sector can have several assigned Sales Organizations (one-to-many relationship).

Retail, CPG, Telco, Chemical, Cross-Industry



The detailed definitions of each attribute in the MicroStrategy metadata repository listed previously are shown in the following tables.

Sales Organization

Sales Organization Industry Sector

Sales Organization Company Code

Accounting unit, consolidating the transactions of the sales organizations from a financial accounting perspective.

• A company code can have several assigned Sales Organizations (one-to-many relationship).

The demo project models a company (Universal Computers) with several company codes, such as UC-UK or UC-USA

Distribution Channel

Channel through which saleable products and services reach customers.

• Each business transaction is linked to a specific Distribution Channel. (It is one of the attribute’s keys in the fact tables).

Direct Sales, Finished Products Wholesales, Components Wholesales


Form Form Expression Lookup Table Other Tables

ID SALES_ORG_ID L_SALES_ORG F_SALES_DOC_ITEM,F_DELIV_DOC_ITEM

DESC SALES_ORG_ DESC

L_SALES_ORG None

Children Parents Relationship Type Table

None Industry Sector Many-to-one L_SALES_ORG

None Company Code Many-to-one L_SALES_ORG


ID INDUSTRY_ID L_INDUSTRY L_SALES_ORG

DESC INDUSTRY _DESC L_INDUSTRY None


Sales Organization

None One-to-many L_SALES_ORG



Sales Organization Company Code


Internal Organization hierarchy

This hierarchy represents how the company organizes sales activities internally.

Note the following:



ID COMP_CODE_ID L_COMP_CODE L_SALES_ORG

DESC COMP_CODE_DESC L_COMP_CODE None


Sales Organization

None One-to-many L_SALES_ORG


ID DIST_CHNL_ID L_DIST_CHNL F_SALES_DOC_ITEM, F_DELIV_DOC_ITEM

DESC DIST_CHNL_ID L_DIST_CHNL None



• The internal organization may have an additional attribute level, Sales Person (not implemented). Each sales group will have one or more salespeople assigned to it.


Sales Office

Sales Group


Sales Office Organizational unit responsible for sales within a specific geographical area.

• It has a child attribute. One or more sales groups can be established within a sales office to differentiate areas of responsibility (one-to-many relationship).

Los Angeles, London, San Francisco, Paris, Boston

Sales Group Group of salespeople who are responsible for processing sales transactions for certain materials and/or customer groups.

• Each Sales Group is assigned to exactly one Sales Office.• Each business transaction is linked to a specific sales

group. (It is one of the attribute’s keys in the related fact tables.)

Large Accounts – LA, Direct Sales – SF, Wholesale Market – NY


ID SALES_OFFICE_ID L_SALES_OFFICE L_SALES_ GROUP

DESC SALES_OFFICE_ DESC

L_SALES_OFFICE None


Sales Group None One-to-many L_SALES_GROUP


ID SALES_GROUP_ID L_SALES_ GROUP

F_SALES_DOC_ITEM, F_DELIV_DOC_ITEM

DESC SALES_GROUP_ DESC

L_SALES_ GROUP

None



Material hierarchy

Materials are the goods and services that are the subject of business transactions. The attributes and relationships in the following figure represent the Material hierarchy.

A check sign in the previous figure indicates the attribute is an entry level in the corresponding hierarchy.


Material Any good that is sold (products and services are combined under this term).

• Material has a number of parent attributes used to group materials, such as Material Division, Material Category, Material Type, Material Group, Product Hierarchy, and so on. They each have a one-to-many relationship with Material.

• Each business transaction is linked to a specific material. (It is one of the attribute’s keys in the related fact tables).

Universal Computer (UC) PC Economy 100, UC InkJet B/W E10, B/W Ink Cartridge

Material Type

Grouping of materials with the same basic characteristics.• Materials are uniquely assigned to a material type.

Raw materials, semi-finished products, finished products

Material Division

Organizational unit defining responsibility for sales of materials.• Materials are uniquely assigned to one division.

Computers, printers, supplies, and so on



Note the following:

• A number of assumptions were made when this analysis hierarchy was defined. The hierarchy includes a limited number of default grouping attributes, such as Material Division or Product Hierarchy, representing industry-standard material classifications. The module can be extended to include additional requirements.

• This hierarchy does not include any characteristic attributes defining measure and quantity specifications for the material, such as base unit of measure, sales unit, volume, and so on. The module can be extended to support them if required.

• All materials are assigned a base unit of measure; this usually represents the basic unit of measure in which the material is manufactured (for example, unit or kilogram). This module was designed with the assumption that all quantities will be measured based on the material “base unit of measure”; fact data about quantities is physically stored using the base unit.

Material Category

Grouping of material according to common attributes. (These will be different for each company).

• Materials are uniquely assigned to one category.

Single material, set of materials

Material Group

Grouping of materials, subdivided from a business perspective. (This will be different for each company).

• Materials are uniquely assigned to one material group.

Large and medium companies, consumers, small business

Product Hierarchy

Dimensional grouping of material with similar features.SDAM includes a Product hierarchy with three attributes:

• Product Hierarchy level 0 (Lowest level to which materials are directly assigned, containing the most specific product descriptions. See example.)

• Product Hierarchy level 1.• Product Hierarchy level 2 (Highest level in the hierarchy,

containing the most general, inclusive product descriptions. See example.)

• Level 0: Standard desktops, home desktops

• Level 1: Desktops, notebooks

• Level 2: PCs, servers

Material Industry Sector

Grouping of materials classified according to the industry sector to which they are targeted.

• Materials are uniquely assigned to one industry sector.Note: This attribute uses as lookup a table alias defined based on L_INDUSTRY.

Retail, Telco, Cross-Industry, Finance

Material Pricing Group

Grouping of materials according to the pricing group to which they belong.

• Materials are uniquely assigned to one pricing group.

High priced, low priced




• When materials are sold or delivered, they can be packed based on different units of measure (for example, an ink cartridge is sold as a pack of 10 units and delivered in packs of 5 units). The module can be extended to support other types of measures; it requires adding a coefficient to convert quantities when measured using different units and/or storing fact data about quantities in different units of measurement.

The detailed definitions of each attribute in the MicroStrategy metadata repository listed previously are shown in the following table.

Material

Material Type


ID MATERIAL_ID L_MATERIAL F_SALES_DOC_ITEM, F_DELIV_DOC_ITEM

DESC MATERIAL_DESC L_MATERIAL None


None Material Type Many-to-one L_MATERIAL

None Material Division Many-to-one L_MATERIAL

None Material Category Many-to-one L_MATERIAL

None Material Group Many-to-one L_MATERIAL

None Product Hierarchy Level 0

Many-to-one L_MATERIAL

None Material Industry Sector

Many-to-one L_MATERIAL

None Material Pricing Group Many-to-one L_MATERIAL


ID MAT_TYPE_ID L_MATERIAL None

DESC MAT_TYPE_DESC L_MATERIAL None


Material None One-to-many L_MATERIAL



Material Division

Material Category

Material Group


ID DIVISION_ID L_DIVISION L_MATERIAL

DESC MAT_DIVISION_DESC L_DIVISION_MAT None




ID MAT_CAT_ID L_MATERIAL None

DESC MAT_CAT_DESC L_MATERIAL None




ID MAT_GROUP_ID L_MATERIAL None

DESC MAT_GROUP_DESC L_MATERIAL None





Product Hierarchy Level 0




ID PROD_HIER_L0_ID L_PROD_HIER L_MATERIAL

DESC PROD_HIER_L0_DESC L_PROD_HIER None



Many-to-one L_PROD_HIER



ID PROD_HIER_L1_ID L_PROD_HIER None




Many-to-one L_PROD_HIER


None One-to-many L_PROD_HIER


ID PROD_HIER_L2_ID L_PROD_HIER None




None One-to-many L_PROD_HIER



Material Industry Sector

Material Pricing Group


ID INDUSTRY_ID L_MAT_INDUSTRY(Table alias for L_INDUSTRY)

None

MAT_INDUSTRY_ID None L_MATERIAL

DESC INDUSTRY_DESC L_MAT_INDUSTRY None




ID MAT_PRICE_GRP_ID L_MATERIAL None

DESC MAT_PRICE_GRP_DESC L_MATERIAL None





Customer hierarchy

This hierarchy represents the customer to whom a company sells materials (the sold-to party in the business transaction).



Customer (sold-to party)

Person or company that places an order for goods or services.

• Customer has a number of parent attributes such as Industry, Account Group, Region, and so on. There is a one-to-many relationship with all of them.

• Each business transaction is associated with one customer.

Note: This attribute is referenced by different column names in the lookup (CUST_ID) and fact tables (SOLD_TO).

Best Deals (a retailer that sell computers and printers), Trust Bank (a bank)

Customer Account Group

Grouping of customers by their location in reference to the company location.

• Customers are uniquely assigned to one account group.

Local customers, International customers

Customer Group

A company’s defined grouping of customers. In the SDAM default project, it indicates the type of customer.

• Customers are uniquely assigned to one customer group.

Wholesalers, retailers, not assigned, industrial customers

Customer Industry Sector

Grouping of customers according to the industry sector.Note: This attribute uses as lookup a table alias defined based on L_INDUSTRY.

Retail, Telco, Cross-Industry, Finance



Note the following:

• A number of assumptions were made when the Customer hierarchy was defined. A customer is a business partner to whom a company sells and delivers materials. There are different functions that a business partner may assume: sold-to party (company that places an order for products or services), ship-to party (company that receives the goods), bill-to party (company that receives the invoice for the goods delivered), and payer (company that pays for the goods). In a business transaction, the same customer can assume all these functions or several customers can be associated with different functions.

• In this module, only the sold-to party is modeled. The sold-to party is associated with the Customer hierarchy. Other partner functions can be added if required; they should be modeled as independent hierarchies based on the Customer hierarchy.

• This hierarchy includes a limited number of parent attributes, representing industry standard customer classifications. The module can be extended to include additional parents, such as Sales District, Number of Employees, Annual Sales, Customer Status, D & B code, and so on.

• This hierarchy does not include detailed customer information, such as address, phone number, tax number, postal code, and so on. These attributes can be added if required.


Customer Region

Customer’s geographical region. State or province

Customer Country

Customer’s country (parent of customer region). USA, UK, Spain, Germany




Customer

Customer Account Group

Customer Group


ID CUST_ID L_CUSTOMER None

SOLD_TO None F_SALES_DOC_ITEM, F_DELIV_DOC_ITEM

DESC CUST_DESC L_CUSTOMER None


None Customer Account Group

Many-to-one L_CUSTOMER

None Customer Group Many-to-one L_CUSTOMER

None Customer Region Many-to-one L_CUSTOMER

None Customer Industry Many-to-one L_CUSTOMER


ID CUST_ACC_GRP_ID L_CUSTOMER None

DESC CUST_ACC_GRP_DESC L_CUSTOMER None


Customer None One-to-many L_CUSTOMER


ID CUST_GRP_ID L_CUSTOMER None

DESC CUST_GRP_DESC L_CUSTOMER None





Customer Industry

Customer Region

Customer Country


ID INDUSTRY_ID L_CUST_INDUSTRY(Table alias for L_INDUSTRY)

None

CUST_IND_ID None L_CUSTOMER

DESC INDUSTRY_DESC L_CUST_INDUSTRY None




ID REGION_ID L_REGION None

CUST_REGION_ID None L_CUSTOMER

DESC REGION_DESC L_REGION None



None Country Many-to-one L_CUST_REGION


ID COUNTRY_ID L_COUNTRY L_REGION

DESC COUNTRY_DESC L_COUNTRY None


Region None One-to-many L_REGION



Sales Document hierarchy

The sales document hierarchy is the structure that records all type of sales transactions, including inquiries, quotations, sales orders, and returns:

• Each sales document includes one or more document items, and each item refers to one of the materials associated with the sales transaction.

• General sales information, such as customer, is defined at the sales document level. Sales facts, such as quantities and amounts, are defined at the sales document item level.

The following diagram shows the structure of a sample sales order with two items:



The sales document hierarchy is represented in the logical data model by the attributes and relationships in the following figure:



Sales Document

Information that is common for all the sales documents.• Each sales document includes one or more items.• Each sales document is uniquely identified by a sales

document ID.

Numeric or alpha-numeric ID

Sales Document Item

Information at the material level.• Each item in a sales document is uniquely identified by the

combination of a sales document and a sales document item.• Sales document and sales document item IDs define a

compound key. (They define a primary key for the fact table tracking sales transactions).


Sales Document Type

Types of sales documents.• Each sales document is associated with one type (one-to-many

relationship).Note: Some sales facts apply to only some document types; refer to the Facts section in this chapter for details.

Inquiries, quotations, sales orders, returns, and free-subsequent delivery




Sales Document

Sales Document Item

Sales Document Status

Processing status of the whole document.• Each sales document has one status associated with it

(one-to-many relationship).• This status applies to all items in a sales document; it is

calculated based on the status for the different document items.

Open: The document is being processed.Complete: The document processing is complete.

Sales Document Item Status

Status of each document item.• Each sales document item has an item status associated with it

(one-to-many relationship).• Status values depend on the document type. For example, an

item in a quotation can have four different statuses, such as Open, Complete, Rejected, or Expired, while inquiries, sales orders, and return items can have only two statuses: Open or Complete.

Open, Complete, Rejected, Expired



ID SALES_DOC_ID F_SALES_DOC_ ITEM

F_DELIV_DOC_ITEM


None Sales Document Status

Many-to-one F_SALES_ DOC_ITEM

None Sales Document Type

Many-to-one F_SALES_ DOC_ITEM

Sales Document Item

None One-to-many F_SALES_ DOC_ITEM


ID Compound key: SALES_DOC_ITEM_ID and SALES_DOC_ID

F_SALES_ DOC_ITEM

F_DELIV_ DOC_ITEM



Sales Document Type

Sales Document Status

Sales Document Item Status


None Sales Document Many-to-one F_SALES_DOC_ ITEM

None Sales Document Item Status

Many-to-one F_SALES_DOC_ ITEM


ID SALES_DOC_ TYPE_ID

L_SALES_DOC_ TYPE

F_SALES_DOC_ITEM, F_DELIV_DOC_ITEM

DESC SALES_DOC_ TYPE_DESC

L_SALES_DOC_ TYPE

None


Sales Document

None One-to-many F_SALES_DOC_ITEM


ID SALES_DOC_ STS_ID

L_SALES_DOC_ STATUS

F_SALES_DOC_ ITEM

DESC SALES_DOC_ STS_DESC

L_SALES_DOC_ STATUS

F_SALES_DOC_ ITEM


Sales Document



ID SDOC_ITEM_STS_ID L_SDOC_ITEM_ STATUS

F_SALES_DOC_ ITEM

DESC SDOC_ITEM_STS_ DESC

L_SDOC_ITEM_ STATUS

F_SALES_DOC_ ITEM



Reference Document hierarchy

A sales document can have a reference document, which is a related, earlier document. For example, a sales order originates from a quotation; the quotation is the reference document for the sales order. A quotation often originates from an inquiry; the inquiry is the reference document for the quotation.

The reference document hierarchy is represented in the logical data model by the attributes and relationships in the following figure:



Sales Document Item



Reference Document

Related, previous document for a given sales document.• Each reference document is uniquely identified by the reference

document ID.• The relationship with the sales document is established through the

sales document fact table.• Not all sales documents have a reference document.





Reference Document

Reference Document Item


Reference document item for a sales document item.• Each reference document is uniquely identified by a compound key

defined based on the reference document ID and the reference document item ID.

• The relationship with the sales document item is established through the sales document fact table.

• Not all sales document items have a reference document item.


Reference Document Type

Types of reference documents.• Each reference document is associated with one type (one-to-many

relationship).

Inquiries, Quotations, Sales Orders, Returns



ID REF_DOC_ID F_SALES_DOC_ITEM None


None Reference Document Type

Many-to-one F_SALES_DOC_ ITEM


None One-to-many F_SALES_DOC_ ITEM


ID Compound key: REF_DOC_ITEM_ID and REF_DOC_ID

F_SALES_DOC_ITEM None


None Reference Document

Many-to-one F_SALES_DOC_ITEM



Reference Document Type

Delivery Document hierarchy

The delivery document hierarchy is the structure that records all types of delivery transactions, including outbound deliveries, return deliveries, and free-subsequent deliveries:

• Each delivery document includes one or more document items, and each document item refers to one of the materials associated with the delivery transaction.

• General delivery information, such as customer or shipping point, is defined at the document level. Delivery facts, such as quantities and delivery dates, are defined at the delivery document item level.

• Delivery documents originate from sales documents such as sales orders or returns. For example, if all sales order items have identical shipping criteria, a delivery transaction is processed to fulfill the sales order. However, if items have different shipping criteria, then multiple delivery transactions are created.


ID REF_DOC_TYPE_ID L_REF_DOC_ TYPE

F_SALES_DOC_ ITEM

DESC REF_DOC_TYPE_DESC L_REF_DOC_ TYPE

None


Reference Document




The following diagram shows the structure of a sample outbound delivery with two items, and its relationship with the associated sales order:

The delivery document hierarchy is represented in the logical data model by the attributes and relationships in the following figure:

Note the following:




• Several independent attributes exist that can be related to the Delivery Document Item attribute. See Delivery Processing Dates, page 199 for information on these independent attributes.



Delivery Document

Information that is common for all the delivery documents.• The delivery document includes one or more items with material

information.• Each delivery document is uniquely identified by a delivery document

ID.• Delivery documents can reference a sales document, such as sales

orders. (The reference is defined in the delivery fact table).


Delivery Document Item

Information at the material level.• Each item in a delivery document is uniquely identified by the

combination of a delivery document and a delivery document item.• Delivery document and delivery document item IDs define a

compound key. (They define a primary key for the fact table tracking delivery transactions).

• The delivery document item can reference a sales document item. (The reference is defined in the delivery fact table).


Delivery Document Type

Types of delivery documents. • Each delivery document is associated with one type (one-to-many

relationship).Note: Some delivery facts apply to only some document types; see the Facts section in this chapter for details.

Outbound delivery, returns delivery, free-subsequent delivery

Delivery Document Status

Status of the whole document.• Each delivery document has one status associated with it

(one-to-many relationship).• This status applies to all items in a delivery document; it is calculated

based on the status for the different document items.

Open: The document is being processed.Complete: The document processing is complete.

Delivery Document Item Status

Status of each document item.• Each delivery document item has an item status associated with it

(one-to-many relationship).• Status values depend on the document type. For example, an item in

an outbound delivery may have several statuses, such as “open – pending material availability,” “open – pending arrival customer site,” and so on, defining different stages in the outbound delivery process.

Open, Complete



Delivery Document


Delivery Document Type


ID DELIV_DOC_ID F_DELIV_DOC_ITEM None


None Delivery Document Status

Many-to-one F_DELIV_ DOC_ITEM

None Delivery Document Type



None One-to-many F_DELIV_ DOC_ITEM


ID Compound key: DELIV_DOC_ITEM_ID and DELIV_DOC_ID

F_DELIV_DOC_ITEM None


None Delivery Document Many-to-one F_DELIV_ DOC_ITEM

None Delivery Document Item Status



ID DELIV_DOC_TYPE_ ID

L_DELIV_DOC_ TYPE

F_DELIV_DOC_ ITEM

DESC DELIV_DOC_TYPE_ D

L_DELIV_DOC_ TYPE

None


Delivery Document

None One-to-many F_DELIV_DOC_ITEM



Delivery Document Status

Delivery Document Item Status

Shipping Point hierarchy

The Shipping Point hierarchy represents the organizational unit that is responsible for the delivery of goods to the customer.

The shipping point hierarchy is represented in the logical data model by the attribute in the following figure:

The logical data model can be extended to include additional attributes and hierarchies to track additional shipping information,


ID DELIV_DOC_STS_ID L_DELIV_DOC_ STATUS

F_DELIV_DOC_ ITEM

DESC DELIV_DOC_STS_DESC L_DELIV_DOC_ STATUS

F_DELIV_DOC_ ITEM


Delivery Document



ID DDOC_ITEM_STS_ID L_DDOC_ITEM_ STATUS

F_DELIV_ DOC_ITEM

DESC DDOC_ITEM_STS_DESC L_DDOC_ITEM_ STATUS

F_DELIV_ DOC_ITEM






such as carrier (a business partner that delivers goods to customers), plant (location where goods are manufactured), shipping route, shipping point region, destination region, and so on. If you make additions:

• The logical data model must be modified to add new attributes to the delivery fact table (in the attribute ID column that tracks values for each individual delivery transaction), and to the corresponding lookup and relationship tables.

• New attributes must define new hierarchies in the MicroStrategy project, or else they must be part of the Shipping Point hierarchy.

The detailed definition of the attribute in the MicroStrategy metadata repository listed previously is shown in the following table.

Shipping Point

Time hierarchy

The time hierarchy represents the calendar time.


Shipping Point

Organizational unit that processes the deliveries.Each delivery transaction is associated with only one shipping point.

Los Angeles, Detroit, Dublin


ID SHIP_POINT_ID L_SHIP_POINT F_DELIV_DOC_ITEM

DESC SHIP_POINT_DESC L_SHIP_POINT F_DELIV_DOC_ITEM



The time hierarchy is represented in the logical data model by the attributes and relationships in the following figure:

Note the following:


• Several independent attributes exist that can be related to the Time hierarchy through the Item Creation Date attribute. See the Delivery Processing Dates section in this chapter for information on these independent attributes.


Year Calendar year. 2002

Quarter Calendar quarter. Q3-2003

Month Calendar month. Nov, 2003

Item Creation Date

Calendar date when the document item was created. This attribute references column CREATE_ITEM_DATE in the Sales and Delivery Document fact tables.

12/24/2002




Year

Quarter

Month


ID YEAR_ID L_CAL_YEAR L_CAL_QTR, L_CAL_MONTH, L_TIME


Quarter None One-to-many L_CAL_QTR


ID QUARTER_ID L_CAL_QTR L_CAL_MONTH, L_TIME

DESC QUARTER_DESC L_CAL_QTR None


None Year Many-to-one L_CAL_QTR

Month None One-to-many L_CAL_MONTH


ID MONTH_ID L_CAL_MONTH

L_TIME

DESC MONTH_DESC L_CAL_MONTH

None


None Quarter Many-to-one L_CAL_MONTH

Item Creation Date None One-to-many L_TIME



Item Creation Date

Delivery Processing Dates

A number of date attributes are associated with the processing of delivery documents. They are independent attributes in the logical data model. A relationship can be established with the Time hierarchy (through the Item Creation Date) or with the Delivery Document Item attribute.

These independent attributes are represented in the following figure:



ID DATE_ID L_TIME None

CREATE_ITEM_ DATE

None F_SALES_DOC_ITEM, F_DELIV_DOC_ITEM


None Month Many-to-one L_TIME

Attribute Description

Material Availability Date Date when materials are available for shipping

Loading Date Date when loading starts

Material Issue Date Actual date when materials are issued from the company’s shipping point to the customer

Planned Issue Date Planned date when materials will be shipped from the company shipping point to the customer

Delivery Date Date when the customer receives the materials




Material Availability Date

Loading Date

Material Issue Date

Planned Issue Date

Delivery Date

Confirmed Delivery Date Confirmed date when materials will be delivered to the customer

Requested Delivery Date Date requested by the customer to receive the materials

Attribute Description


ID MAT_AVAIL_DATE F_DELIV_DOC_ITEM None


ID LOAD_DATE F_DELIV_DOC_ITEM None


ID MAT_ISSUE_DATE F_DELIV_DOC_ITEM None


ID PLAN_ISSUE_DATE F_DELIV_DOC_ITEM None


ID DELIV_DATE F_DELIV_DOC_ITEM None


© 2010 MicroStrategy, Inc. Transformations 201

Confirmed Delivery Date

Requested Delivery Date

TransformationsSDAM includes the following time transformations to enable analysis of a selected time period compared to another time period. All these transformations are based on table transformations.


ID CONF_DELIV_DATE F_DELIV_DOC_ITEM None


ID REQ_DELIV_DATE F_DELIV_DOC_ITEM None

Transformation Definition Attribute Transformation Table Expression

Previous Month Enables analysis of a selected month compared to the previous month

Month L_CAL_MNTH PREV_MNTH_ID

Previous Quarter Enables analysis of a selected quarter compared to the previous quarter

Quarter L_CAL_QTR PREV_QTR_ID

Previous Year Enables analysis of a selected year compared to the previous year

Year L_CAL_YEAR PREV_YEAR_ID

Year to Month Enables analysis of a selected month compared to all months in the year, up to the selected month

Month L_MONTH_YTD YTD_MNTH_ID

Year to Quarter Enables analysis of a selected quarter compared to all quarters in the year, up to the selected quarter

Quarter L_QUARTER_YTD YTD_QTR_ID


202 User hierarchies © 2010 MicroStrategy, Inc.

User hierarchiesSDAM includes several user hierarchies to facilitate navigation through some of the business hierarchies listed previously.

Hierarchy Level Attribute Entry Point? Comments

Customer 1 Customer Country Yes This user hierarchy defines drill navigation through the Customer hierarchy attributes.

2 Customer Region No

2 Customer Industry Sector

Yes

2 Customer Group Yes

2 Customer Account Group

Yes

3 Customer No

Material 1 Product Hierarchy Level 2

Yes This user hierarchy defines drill navigation through the Material hierarchy attributes.

2 Product Hierarchy Level 1

No


No

3 Material Division Yes

3 Material Category Yes

3 Material Group Yes

3 Material Industry Sector

Yes

3 Material Type Yes

3 Material Pricing Group

Yes

4 Material No

Product Hierarchy


Yes • This user hierarchy allows you to navigate through the Product hierarchy from the top level to Material.

• Product Hierarchy Level 2 is the entry point.• This hierarchy is used in prompts to

dynamically select any element at any level of the Product hierarchy.


No


No

4 Material No


© 2010 MicroStrategy, Inc. User hierarchies 203


1 Distribution Channel

Yes This user hierarchy defines drill navigation for Distribution Channel.

Sales Organization

1 Sales Org. Company Code

Yes This user hierarchy defines drill navigation for the Sales Organization hierarchy.

1 Sales Org. Industry Sector

Yes

2 Sales Organization

No

Internal Organization

1 Sales Group Yes This user hierarchy defines drill navigation for the Internal Organization hierarchy.

2 Sales Office No

Shipping Point

1 Shipping Point Yes This user hierarchy defines drill navigation for Shipping Point.

Sales Document

1 Sales Document Type

Yes This user hierarchy defines drill navigation through the corresponding hierarchy.

1 Sales Document Status

Yes

2 Sales Document No

2 Sales Document Item Status

Yes

3 Sales Document Item

No

Reference Document

1 Reference Document Type


2 Reference Document

No

3 Reference Document Item

No



204 Facts © 2010 MicroStrategy, Inc.

FactsThis section describes the facts used in SDAM. The underlying processes are also explained briefly.

There are two groups of facts in SDAM:

• Facts measuring sales activities, which are based on sales documents

• Facts measuring delivery activities, which are based on delivery documents

For additional details, see the MicroStrategy project definitions in the Schema Objects\Attributes and Schema Objects\Facts folders. Double-click any attribute or fact to view definitions, properties, source tables, and so on.

Delivery Document

1 Delivery Document Type


1 Delivery Document Status

Yes

2 Delivery Document

No

2 Delivery Document Item Status

Yes

3 Delivery Document Item

No

Time 1 Year Yes • This user hierarchy allows you to navigate through the Time hierarchy.

• Year is the entry point.2 Quarter No

3 Month No

4 Date No

Time (Year to Month)

1 Year Yes This user hierarchy allows you to navigate through the Time hierarchy up to the Month level.

2 Quarter No

3 Month No



© 2010 MicroStrategy, Inc. Facts 205

Sales Document facts

Sales Document facts measure sales activities by tracking sales document amounts, costs, quantities, and the number of documents and document items.

All these facts are based on the fact table F_SALES_DOC_ITEM. Therefore, dimensional keys for these facts correspond to the fact table keys. Additional details, such as form expressions, links with physical schema tables and columns, and so on, can be found in the SDAM project by accessing the fact definition using the Fact Editor.

For details about physical tables and columns, see Data Storage: Physical Schema and Data Dictionary, page 213 in Appendix C.

Note the following:

• All quantity facts are stored in the fact tables using the material base unit of measure. If other units of measure are required, additional fact columns can be added. For example, you can add a fact column with quantities in the sales unit, or attributes for “base unit,” “sales unit,” and so on. You can also add a coefficient to convert quantities to the units required. See Material hierarchy, page 175 for details.

• All amounts and costs are measured in a single currency. If transaction currency is required, add a “currency” attribute and an “exchange rate” to convert transaction amounts into required currencies.

Fact Description

Net Item Amount The net transaction amount for each sales document item (after discounts or surcharges and before any taxes are applied).

• This is based on column NET_ITEM_AMOUNT from fact table F_SALES_DOC_ITEM.

• Value is expressed in the same currency for all records (single currency).Note: This fact does not apply to sales documents of type “Inquiry.”

Item Cost The cost for each item (cost associated with the acquisition of a material).• This is based on column ITEM_COST from fact table F_SALES_DOC_ITEM.• Value is expressed in the same currency for all records (single currency).

Note: This fact does not apply to sales documents of type “Inquiry.”

Quantity (Base Units) The quantity of material associated with each sales document item.• This is based on column ITEM_QTY_BU from fact table

F_SALES_DOC_ITEM.• It is measured in the material base unit.



Ordered Quantity Delivered (Base Units)

The quantity of goods, from a sales order item, already delivered to the customer (expressed in the material base units). This value is updated whenever a delivery for a sales order is completed.

• This is based on column ITEM_QTY_DELIV_BU from fact table F_SALES_DOC_ITEM.

• If the sales order is open this number may be smaller than ITEM_QTY_BU, indicating that some items are still pending delivery to the customer.

• Once the sales order is complete, this value must be equal to ITEM_QTY_BU indicating that the order was successfully completed. If the value is different, it can indicate incorrect processing of the order.

• It is measured in the material base unit.Note: This fact only applies to sales documents of type “Sales Order.”

Backlog Quantity (Base Units)

The quantity of goods, from a sales order item, that is pending delivery (expressed in the material base units). This value is updated based on the deliveries associated with the sales order.

• This is defined as [ITEM_QTY_BU] – [ITEM_QTY_DELIV_BU]. Both columns are from the fact table F_SALES_DOC_ITEM.

• Once the sales order is complete, this value must be equal to zero indicating that the order was successfully completed. If the value is different, it indicates incorrect processing of the order.

• It is measured in the material base unit.Note: This fact only applies to sales orders.

Backlog Net Item Amount

The net amount of goods pending delivery. This value is updated based on the deliveries associated with the sales order.

• This is defined as NET_ITEM_AMOUNT * (1 - [ITEM_QTY_DELIV_BU] / [ITEM_QTY_BU]). Both columns are from the fact table F_SALES_DOC_ITEM.

• Once the sales order is complete, this value must be equal to zero.• Value is expressed in the same currency for all records (single currency).

Note: This fact only applies to sales orders.

Net Price (Base Units)

The net price for the material item in base units. It is calculated by dividing the Net Item Amount by the quantity in base units.

• This is defined as NET_ITEM_AMOUNT / ITEM_QTY_BU. Both columns are from the fact table F_SALES_DOC_ITEM.

• Value is expressed in the same currency for all records (single currency).Note: This fact does not apply to sales documents of type “Inquiry.”

SALES_DOC_ID The unique identifier of the sales document. It is used to define count distinct of sales documents.

• This is based on column SALES_DOC_ID from fact table F_SALES_DOC_ITEM.

Fact Description



Delivery Document facts

Delivery Document facts track delivery document quantities, processing times, and the number of documents and document items.

The following diagram describes all the facts used to analyze delivery processing times:

SALES_DOC_ITEM_ID

The unique identifier of an item within a specific sales document. It is used to define counts of sales document items.

• This is based on column SALES_DOC_ITEM_ID from fact table F_SALES_DOC_ITEM.

CUST_ID from Sales Doc Fact Table

The unique identifier of the customer associated with the sales transaction. It is used to define count distinct of customers.

• This is based on column SOLD_TO from fact table F_SALES_DOC_ITEM.

Fact Description



All the facts are based on fact table F_DELIV_DOC_ITEM. Therefore, dimensional keys for those facts correspond to the fact table keys. Additional details, such as form expressions, links with physical schema tables and columns, and so on, can be found in the SDAM project by accessing the fact definition using the Fact Editor.

For details about physical tables and columns, see Data Storage: Physical Schema and Data Dictionary, page 213 in Appendix C of this reference guide.

Note the following:

• All quantity facts are stored in the fact tables using the material base unit of measure. If other units of measure are required, additional fact columns can be added. For example, you can add a fact column with quantities in the sales unit. See the Material hierarchy section in this chapter for details.

• All costs are measured in a single currency. If transaction currency is required, add a “currency” attribute and an “exchange rate” to convert transaction amounts into required currencies.

• All processing times and delays are based on calculations on Dates columns (DATE data type), and return an integer value indicating the number of days between two dates. Date and time calculations may require special attention; for more information see Calculating dates and times, and your database in this chapter.

Fact Description

DELIV_DOC_ID Unique identifier of the delivery document. It is used to define the count distinct of delivery documents.

• This is based on column DELIV_DOC_ID from fact table F_DELIV_DOC_ITEM.

DELIV_DOC_ITEM_ ID

Unique identifier of an item within a specific delivery document. It is used to define counts of delivery document items.

• This is based on DELIV_DOC_ITEM_ID from fact table F_DELIV_DOC_ITEM.


Confirmed quantity of material to be delivered for each transaction item.• This is based on ITEM_CONF_QTY_BU from fact table

F_DELIV_DOC_ITEM.• It is measured in material base units.


Quantity of material that is ultimately delivered for each transaction item.• This is based on ITEM_DELIV_QTY_BU from fact table

F_DELIV_DOC_ITEM.• It is measured in material base units.• Fact value is zero until goods are delivered. The value is updated once

delivery transaction is completed.



Difference between Delivery and Issue Date

Total time required to transport goods to the customer site, from the date they are issued from the company’s shipping point until the date they are received at the customer site.

• This is based on the calculation ([DELIV_DATE] - [MAT_ISSUE_DATE]); both columns are from fact table F_DELIV_DOC_ITEM.

• It is measured in days.

Difference between Delivery and Material Availability Date

Total processing time, from the date goods are available for packing and shipping (defined by the material availability date) until the date they arrive at the customer site.

• This is based on the calculation ([DELIV_DATE] - [MAT_AVAIL_DATE]); both columns are from fact table F_DELIV_DOC_ITEM.


Difference between Issue and Material Availability Date

Processing time required to package, load, and issue goods. It is defined as the difference between dates when goods are available for packing and shipping, defined by the material availability date, and the date they leave the company facilities.

• This is based on the calculation ([MAT_ISSUE_DATE] - [MAT_AVAIL_DATE]); both columns are from fact table F_DELIV_DOC_ITEM.


Difference between Issue and Loading Date

Processing time required to load and ship deliveries. It is defined as the difference between the loading start date and the date goods leave the company shipping facilities.

• This is based on the calculation ([MAT_ISSUE_DATE] - [LOAD_DATE]); both columns are from fact table F_DELIV_DOC_ITEM.


Difference between Loading and Material Availability Date

Processing time required for picking and packing goods. It is defined as the difference between the dates when goods are available for delivery and when they are ready for loading.

• This is based on the calculation ([LOAD_DATE] - [MAT_AVAIL_DATE]); both columns are from fact table F_DELIV_DOC_ITEM.


Delay between Confirmed and Actual Delivery Date

Delay between the date when goods are confirmed to arrive at the customer site and the actual date when goods are delivered to the customer.

• This is based on the calculation ([DELIV_DATE] – [CONF_DELIV_DATE]); both columns are from fact table F_DELIV_DOC_ITEM.


Delay between Requested and Actual Delivery Date

Delay between the customer’s requested date for receiving the shipment and the actual arrival date.

• This is based on the calculation ([DELIV_DATE] - [REQ_DELIV_DATE]); both columns are from fact table F_DELIV_DOC_ITEM.


Fact Description



Calculating dates and times with your database

Calculating dates

Each database type has a different way to calculate dates. Depending on the type of database you have, you may be required to modify the default fact expressions for the fact you want to use.

The following table lists the different database vendors and shows examples of a fact expression modified for that database. The example used is the fact Difference between Delivery and Material Availability Date, from the table above.

Delay between Requested and Confirmed Delivery Date

Difference between the confirmed date for delivery of goods to the customer site and the date requested by the customer to receive the shipment.

• This is based on the calculation ([CONF_DELIV_DATE] - [REQ_DELIV_DATE]); both columns are from fact table F_DELIV_DOC_ITEM.


Delay between Planned and Actual Issue Date

Delay between the date when the shipment is scheduled to leave the company’s shipping point and the actual date when the goods leave the shipping point.

• This is based on the calculation ([MAT_ISSUE_DATE] - [PLAN_ISSUE_DATE]); both columns are from fact table F_DELIV_DOC_ITEM.


Fact Description

Database Fact Expression

Oracle ([DELIV_DATE] - MAT_AVAIL_DATE])Note: Default fact expressions use the Oracle standard.

SQL Server ApplySimple (“datediff(day, #0, #1)”, [DELIV_DATE], [MAT_AVAIL_DATE])

DB2 ApplySimple (“DAYS(#0) - DAYS(#1)”, [DELIV_DATE], [MAT_AVAIL_DATE])

Teradata ([DELIV_DATE] - [MAT_AVAIL_DATE])



Calculating times

You may want to calculate time differences using a different time scale than that used in the project, for example, hours or minutes. To use a different time scale, you must modify the logical data model to use a TIMESTAMP data type for dates fields, and modify fact expressions accordingly to use database-specific operators/functions.

© 2010 MicroStrategy, Inc. Prerequisites 213

CC.DATA STORAGE: PHYSICAL SCHEMA AND DATA DICTIONARY

Introduction

This appendix provides a diagram of the physical schema that comes with the Sales and Distribution Analysis Module (SDAM). This appendix also provides descriptions of all the tables and columns in the default data warehouse.

PrerequisitesThis appendix was written for consultants and developers implementing and customizing the SDAM application and for those building ETL routines to populate the data warehouse. It assumes you are familiar with basic RDBMS concepts and Erwin data modeling.

Data Storage: Physical Schema and Data Dictionary Sales & Distribution Analysis Module ReferenceC

214 SDAM physical schema © 2010 MicroStrategy, Inc.

SDAM physical schemaThe following diagram represents the physical schema shipped with SDAM. The physical schema is available in an Erwin file, located in Program Files\MicroStrategy\Analytics Modules\Sdam\ SDAM.er1.

Fact tables appear in teal (color) or gray (black and white).

Sales & Distribution Analysis Module Reference Data Storage: Physical Schema and Data Dictionary C

© 2010 MicroStrategy, Inc. SDAM physical schema 215


216 Table information © 2010 MicroStrategy, Inc.

Table informationThis section describes each physical table used in SDAM.

Table Name Table Comment

F_SALES_DOC_ITEM Fact table containing all the sales document transactions.• Fact data stored at sales document and sales document item levels

(primary keys). Includes keys of hierarchies defining transaction and used for analysis (material, sales organization, and so on).

• Includes two Time keys, Create Item Date (date when document item was created) and Change Item Date (last time record was updated). Create Item Date is Time key for analysis.

• Includes two status fields for sales document and sales document items, indicating processing status.

• Fact data includes Net Item Amount, Item Quantity, Item Quantity Delivered (fact is updated based on deliveries associated with sales transaction), and Item Cost. Some fact columns only apply to specific document types; see the Column information section in this chapter for details.

Data is updated in the following cases:• Each time a sales document is created, new records are added.• If a sales document is updated, records are updated (historical data is not

stored). The Change Date column indicates the last time the record was updated.

• If a sales document and/or sales document item is deleted from the operational system, the corresponding records are deleted from this table.

F_DELIV_DOC_ITEM Fact table containing all the delivery document transactions.• Fact data is stored at delivery document and delivery document item

levels (primary keys). Includes keys of hierarchies defining transaction and used for analysis (material, shipping point, and so on).

• Includes two Time keys, Create Item Date (date when document item was created) and Change Item Date (last time record was updated). Create Item Date is used as Time key for analysis.

• Includes two status fields for delivery document and delivery document items, indicating processing status.

• Fact data includes confirmed and delivered quantity, item shipping cost, and delivery-related dates to calculate delivery processing times. Some fact columns only apply to specific types of documents; refer to the Column information section in this chapter for details.

Data is updated in the following cases:• Each time a delivery document is created, new records are added.• If a delivery document is updated, records are updated (historical data is

not stored). The Change Date column indicates the last time the record was updated.

• If a delivery document and/or document items are deleted from the operational system, the corresponding records are deleted from this table.

L_CAL_MONTH Lookup table for Calendar Month attribute.• It includes the relationship with Calendar Quarter and Year.• It includes the Previous Month relationship to calculate monthly trends.


© 2010 MicroStrategy, Inc. Table information 217

L_CAL_QTR Lookup table for Calendar Quarter attribute.• It includes the relationship with Calendar Year.• It includes the Previous Quarter relationship to calculate quarterly trends.

L_CAL_YEAR Lookup table for Calendar Year attribute.• It includes the Previous Year relationship to calculate yearly trends.

L_COMP_CODE Lookup table for Company Code attribute.

L_COUNTRY Lookup table for Customer Country attribute.

L_CUST_INDUSTRY Lookup table for the Customer Industry attribute.

L_CUSTOMER Lookup table for the Customer attribute.• This table is used as the lookup for a number of customer characteristic

attributes such as Customer Account Group, Customer Group, and Customer Classification.

• The table is also used to define relationships between Customer and other characteristic attributes (Customer Industry Sector, Customer Region, Customer Account Group, Customer Group, and Customer Classification).

• In this module, the customer is associated with the sold-to party function. If other business functions such as ship-to party, bill-to party, and payer are required, a table alias can be created to refer each of the different attributes to the same physical table.

• There is one record for each customer in the system.Data is updated in the following cases:

• When a new customer is added to the system, a record is added to this table.

• If data for a customer changes, the corresponding record is updated (historical data is not recorded).

L_DDOC_ITEM_ STATUS

Lookup table for the Delivery Document Item Status attribute.• This table stores the different statuses that a delivery document item can

have during processing (Open, Open-Pending Material Availability, Open-Pending Goods Issue, Complete, and so on).

L_DELIV_DOC_STATUS Lookup table for the Delivery Document Status attribute.• This table stores the different statuses that delivery documents can have

during processing (Open, Complete, and so on).Note: These statuses are different depending on the type of document; values may apply only to specific document types.

L_DELIV_DOC_TYPE Lookup table for the Delivery Document Type attribute.• This table stores the different types of delivery documents that are

processed (Outbound Delivery, Return Delivery, Free-subsequent Delivery, and so on).

Note: These statuses are different depending on the type of document; values may apply only to specific document types.

L_DIST_CHNL Lookup table for the Distribution Channel attribute.

L_DIVISION Lookup table for the Material Division attribute.



218 Table information © 2010 MicroStrategy, Inc.

L_INDUSTRY Lookup table for Industry Sector.• This table is used in several hierarchies where an Industry Sector attribute

exists, such as Sales Organization, Material, and Customer. A logical table alias is defined in the MicroStrategy project to reference the same physical table in the different hierarchies.

L_MAT_INDUSTRY Lookup table for the Material Industry attribute.

L_MATERIAL Lookup table for the Material attribute.• This table is used as the lookup for a number of characteristic attributes

such as Material Type, Material Category, Material Group, and Material Pricing Group.

• The table is also used to define relationships between Material and other characteristic attributes (Material Division, Material Industry Sector, Product Hierarchy, Material Group, and so on).

• There is one record for each material in the system.Data is updated in the following cases:

• When a new material is added to the system, a record is added to this table.

• If data for a material changes, the corresponding record is updated (historical data is not recorded).

L_PROD_HIER Lookup and relationship table for all the attributes in the Product hierarchy (Product Hierarchy Level 0, Level 1, and Level 2).When data is updated:

• If relationships between different levels change, the record is updated, losing any historical record of the previous structure.

L_REF_DOC_TYPE Lookup table for the Reference Document Type attribute.This table stores the different types of documents processed (Quotation, Sales Order, Return, Outbound Delivery, Return Delivery, and so on).

L_REGION Lookup table for the Customer Region attribute.• This is also the relationship table between Customer Region and Country.

L_SALES_DOC_ STATUS

Lookup table for the Sales Document Status attribute.• This table stores the different statuses that a sales document can have

during processing (Open, Complete, and so on).Note: These statuses are different depending on the type of document; values may apply only to specific document types.

L_SALES_DOC_TYPE Lookup table for the Sales Document Type attribute.• This table stores the different types of sales documents processed

(Quotation, Sales Order, Inquiry, Return, and so on).

L_SALES_GROUP Lookup table for the Sales Group attribute.• Also the relationship table between Sales Office and Sales Group.

L_SALES_OFFICE Lookup table for the Sales Office attribute.

L_SALES_ORG Lookup table for the Sales Organization attribute.• This table is used to define relationships with the characteristic attributes

associated with Sales Organization (Company Code and Industry Sector).



© 2010 MicroStrategy, Inc. Table column information 219

Table column informationThis section describes each physical table column used in SDAM.

The Data Type column information in the following table reflects an Oracle database-specific format; depending on what database type you use, your data type may appear differently. You can use the Erwin file (see the SDAM physical schema section above) to easily convert this information to another database type.

L_SDOC_ITEM_ STATUS

Lookup table for the Sales Document Item Status attribute.• This table stores the different statuses that a sales document item can

have during processing (Open, Expired, Rejected, Complete, and so on).Note: These statuses are different depending on the type of document; values may apply only to specific document types.

L_SHIP_POINT Lookup table for the Shipping Point attribute.

L_TIME Lookup table for the Date attribute.• This table defines the relationship between Date and all the time

attributes.

L_MONTH_YTD Lookup table that relates a given month to all months within the same year, up to the given month.

L_QUARTER_YTD Lookup table that relates a given quarter to all quarters within the same year, up to the given quarter.


Column Name Data Type

Primary Key (PK) / Foreign Key (FK)

Column Comment

Table: F_SALES_DOC_ITEM

SALES_DOC_ID Numeric (38,0)

PK Unique identifier for the sales document. With Sales Document Item, it defines the table primary key.

SALES_DOC_ITEM_ID Numeric (38,0)

PK Unique identifier for the sales document item. With Sales Document, it defines the table primary key.

SALES_DOC_TYPE_ID Numeric (38,0)

FK Identifies the type of sales document. Column value is the same for all items in the sales document.


220 Table column information © 2010 MicroStrategy, Inc.

SALES_DOC_STS_ID Numeric (38,0)

FK Identifies the status of the sales document. For example, Open indicates the document is being processed while Complete indicates that processing has been completed.Column value is the same for all items in the sales document. For example:

• If the status for all document items is complete, the document status is set to Complete.

• If one item has a status of Open but all other items have a Complete status, the document status is set to Open.

Note: Statuses are different depending on the type of document; values may apply only to specific document types.

SDOC_ITEM_STS_ID Numeric (38,0)

FK Unique identifier for the sales document item status. For example, Open indicates the document item is being processed while Complete indicates that processing has been completed.Values can differ for the different items in a document.Note: Statuses are different depending on the type of document; values may apply only to specific document types.

SALES_GROUP_ID Numeric (38,0)

FK Identifies the sales group associated with the sales document. Column value is the same for all items in the sales document.

DIST_CHNL_ID Numeric (38,0)

FK Identifies the distribution channel associated with the sales document. Column value is the same for all items in the sales document.

SALES_ORG_ID Numeric (38,0)

FK Identifies the sales organization associated with the sales document. Column value is the same for all items in the sales document.

MATERIAL_ID Numeric (38,0)

FK Identifies the material associated with each of the sales document items.

CREATE_ITEM_DATE TimeStamp (0)

FK Date on which the item was created. Used as the time key for analysis.

CHANGE_ITEM_DATE TimeStamp (0)

Last date on which the item was changed.



Column Comment



REF_DOC_ID Numeric (38,0)

FK Reference document associated with the sales document. Column contains values only if there is a preceding document; if not, the column is empty.

REF_DOC_ITEM_ID Numeric (38,0)

FK Reference document item for the sales document item. Column contains values only if there is a preceding document; if not, the column is empty.Column value is the same for all items in the sales document.

REF_DOC_TYPE_ID Numeric (38,0)

FK Identifies the document type for the preceding document. Column contains values only if there is a preceding document; if not, the column is empty.Column value is the same for all items in the sales document.

SOLD_TO Numeric (38,0)

FK Identifies the customer associated with the sales document. In this module, customer is associated with the sold-to party function. This column references CUST_ID column in the Customer lookup (L_CUSTOMER).Column value is the same for all items in the sales document.

NET_ITEM_AMOUNT Numeric (15,3)

Net transaction amount for each sales document item. This is the value after discounts or surcharges and before any taxes are applied.

• Value is expressed in the same currency for all records (single currency).

• If support for different currencies is required, currency and exchange rate information must be added to the transaction.

• The column does not apply to inquiry documents; in that case, the field will be empty.

ITEM_QTY_BU Numeric (38,0)

Material quantity for each item. Measured in the material’s base unit.



Column Comment



ITEM_QTY_DELIV_BU Numeric (38,0)

Material quantity already delivered for a sales document item.

• Measured in the material’s business unit.• This column is only measured for sales

documents that have a corresponding outbound delivery such as Outbound Deliveries or Free Deliveries.

• Column value will be updated based on deliveries associated with the sales document; when a delivery is complete the corresponding delivery quantity is updated in this field.

ITEM_COST Numeric (15,3)

Cost for each sales document item. The cost associated with the acquisition of a material.

• Value is expressed in the same currency for all records (single currency).

• If support for different currencies is required, currency and exchange rate information must be added to the transaction.

• The column does not apply to inquiry documents; in that case, the field will be empty.

Table: F_DELIV_DOC_ITEM

DELIV_DOC_ID Numeric (38,0)

PK Unique identifier for the delivery document. With Delivery Document Item, it defines the table primary key.

DELIV_DOC_ITEM_ID Numeric (38,0)

PK Unique identifier for the delivery document item. With Delivery Document, it defines the table primary key.

DELIV_DOC_TYPE_ID Numeric (38,0)

FK Identifies the type of delivery document. Column value is the same for all items in the document.



Column Comment



DELIV_DOC_STS_ID Numeric (38,0)

FK Identifies the processing status of the delivery document. For example, Open indicates delivery is being processed and Complete indicates that processing was completed.Column value is the same for all items in the document. For example:

• If status for all document items is complete, the document status is set to Complete.

• If one item has a status of Open but all other items have a Complete status, the document status is set to Open.

Note: Statuses are different depending on the type of document; values may apply only to specific document types.

DDOC_ITEM_STS_ID Numeric (38,0)

FK Unique identifier for the delivery document item status, indicating processing status. For example, Open-Pending Material Availability indicates delivery is on hold until materials are available. Value can differ for the different items in a document.Note: These statuses are different depending on the type of document; values may apply only to specific document types.


FK Unique identifier for the distribution channel associated with the delivery transaction. Column value is the same for all items in the delivery document.


FK Unique identifier for the sales organization associated with the delivery transaction. Column value is the same for all items in the delivery document.


FK Unique identifier for the sales group associated with the delivery transaction. Column value is the same for all items in the delivery document.


FK Unique identifier for the material associated with the delivery item.



Column Comment



SOLD_TO Numeric (38,0)

FK Identifies the customer associated with the delivery document. In SDAM, customer is associated with the sold-to party function. This column references CUST_ID column in the Customer lookup (L_CUSTOMER).Column value is the same for all items in the delivery document.

SHIP_POINT_ID Numeric (38,0)

FK Unique identifier for the shipping point from where materials are delivered to the customer. Column value is the same for all items in the delivery document.

CREATE_ITEM_DATE TimeStamp (0)

FK Date on which the item was created. This column is used as the time key for analysis.

CHANGE_ITEM_DATE TimeStamp (0)

Last date on which the item was changed.

SALES_DOC_ID Numeric (38,0)

FK Identifies the sales document originating the delivery. Column contains values only if there is a preceding sales document; if not, the column is empty.Column value is the same for all items in the delivery document.Note: This module assumes that delivery documents only have sales documents as preceding documents.

SALES_DOC_ITEM_ID Numeric (38,0)

FK Identifies the sales document item originating the delivery item. Column contains values only if there is a preceding document; if not, the column is empty.


FK Identifies the sales document type for the preceding document. Column value is the same for all items in the delivery document.

ITEM_CONF_QTY_BU Numeric (38,0)

Quantity confirmed for delivery to the customer. Defined at the document item level. Value is measured in the material’s base unit.



Column Comment



ITEM_DELIV_QTY_BU Numeric (38,0)

Actual quantity delivered to the customer for each document item. When delivery status is Open, this column indicates whether some quantities are pending delivery. When delivery status is Complete, this column indicates:

• If delivery is correct, this value must be equal to Confirmed Quantity.

• If delivery is not correct, there is a partial delivery to the customer.

Value is measured in the material's base unit.

ITEM_SHIP_COST Numeric (15,3)

Cost associated with shipping all the materials in a delivery document item to the customer. Value is expressed in a single currency (the one used for analysis).

REQ_DELIV_DATE TimeStamp (0)

Date requested by customer to receive the materials. Date is defined at the document item level.

CONF_DELIV_DATE TimeStamp (0)

Confirmed date when materials will be delivered to the customer. Date is defined at the document item level.

DELIV_DATE TimeStamp (0)

Date when customer receives the materials. Date is defined at the document item level.

MAT_AVAIL_DATE TimeStamp (0)

Date when materials are available for packing and shipping. Date is defined at the document item level. Column does not apply for return deliveries, only for those deliveries involving outbound shipping. For return deliveries, the field is empty.

LOAD_DATE TimeStamp (0)

Date when loading starts. Date is defined at the document item level. Column does not apply for return deliveries, only for those deliveries involving outbound shipping. The field is empty for return deliveries.

PLAN_ISSUE_DATE TimeStamp (0)

Planned date when materials leave the company’s shipping location to arrive on time at the customer site. Date is defined at the document item level. Column does not apply for return deliveries, only for those deliveries involving outbound shipping. The field is empty for return deliveries.



Column Comment



MAT_ISSUE_DATE TimeStamp (0)

Actual date when materials are issued from the company's shipping point to the customer. Date is defined at the document item level. Column does not apply for return deliveries, only for those deliveries involving outbound shipping. The field is empty for return deliveries.

Table: L_CAL_MONTH

MONTH_ID Numeric (38,0)

PK Unique identifier for month.

MONTH_DESC VarChar (30)

Month description.

YEAR_ID Numeric (38,0)

FK Unique identifier for year. References YEAR_ID in L_YEAR.

QUARTER_ID Numeric (38,0)

FK Unique identifier for quarter. References QUARTER_ID in L_QUARTER.

PREV_MONTH_ID Numeric (38,0)

Column identifying the ID of the previous month. Used to define previous month transformation for historical trend analysis.

Table: L_CAL_QTR


PK Unique identifier for quarter.

QUARTER_DESC VarChar (30)

Quarter description.



PREV_QTR_ID Numeric (38,0)

Column identifying the ID of the previous quarter. Used to define previous quarter transformation for historical trend analysis.

Table: L_CAL_YEAR


PK Unique identifier for year.

PREV_YEAR_ID Numeric (38,0)

Column identifying the ID of the previous year. Used to define previous year transformation for historical trend analysis.



Column Comment



Table: L_COMP_CODE

COMP_CODE_ID Numeric (38,0)

PK Unique identifier for company code.

COMP_CODE_DESC VarChar (30)

Company code description.

Table: L_COUNTRY

COUNTRY_ID Numeric (38,0)

PK Unique identifier for the customer country.

COUNTRY_DESC VarChar (30)

Country description.

Table: L_CUST_INDUSTRY

INDUSTRY_ID Numeric (38,0)

PK Unique identifier for the industry sector. Referenced as lookup for several attributes such as Customer Industry Sector, Material Industry Sector, and so on.

INDUSTRY_DESC VarChar (30)

Industry sector description.

Table: L_CUSTOMER

CUST_ID Numeric (38,0)

PK Unique identifier for the customer. References the SOLD_TO column in fact tables.

CUST_DESC VarChar (30)

Customer name.

CUST_ACC_GRP_ID Numeric (38,0)

Unique identifier for the customer account group associated with the customer.

CUST_ACC_GRP_DESC VarChar (30)

Description for the account group. Also used as lookup for this attribute.

CUST_GRP_ID Numeric (38,0)

Unique identifier for the customer group associated with the customer.

CUST_GRP_DESC VarChar (30)

Customer group description. Also used as lookup for this attribute.

CUST_IND_ID Numeric (38,0)

FK Unique identifier for industry sector associated with the customer. Column references INDUSTRY_ID in L_INDUSTRY.

CUST_REGION_ID Numeric (38,0)

FK Unique identifier for the region associated with the customer. Column references REGION_ID in L_REGION.



Column Comment



Table: L_DDOC_ITEM_STATUS

DDOC_ITEM_STS_ID Numeric (38,0)

PK Unique identifier for the delivery document item status.Note: These statuses are different depending on the type of document; values may apply only to specific document types.

DDOC_ITEM_STS_DESC VarChar (30)

Item status description.

Table: L_DELIV_DOC_STATUS

DELIV_DOC_STS_ID Numeric (38,0)

PK Unique identifier for the status associated with a delivery document.Note: These statuses are different depending on the type of document; values may apply only to specific document types.

DELIV_DOC_STS_DESC VarChar (30)

Delivery document status description.

Table: L_DELIV_DOC_TYPE

DELIV_DOC_TYPE_ID Numeric (38,0)

PK Unique identifier for the sales document type attribute

DELIV_DOC_TYPE_D VarChar (30)

Sales document type description.

Table: L_DIST_CHNL


PK Unique identifier for the distribution channel.

DIST_CHNL_DESC VarChar (30)

Distribution channel description.

Table: L_DIVISION

DIVISION_ID Numeric (38,0)

PK Unique identifier for the material division attribute.

DIVISION_DESC VarChar (30)

Division description.

Table: L_INDUSTRY


PK Unique identifier for the industry sector. Referenced as lookup for several attributes such as Customer Industry Sector, Material Industry Sector, and so on.



Column Comment





Table: L_MAT_INDUSTRY


FK Industry sector associated with the sales organization. This column references INDUSTRY_ID in L_INDUSTRY.



Table: L_MATERIAL


PK Unique identifier for the material.

MATERIAL_DESC VarChar (50)

Material description.

PROD_HIER_L0_ID Numeric (38,0)

Product Hierarchy Level 0 associated with the material.

DIVISION_ID Numeric (38,0)

FK Division ID associated with the material. Column references DIVISION_ID in L_DIVISION.

MAT_IND_ID Numeric (38,0)

FK Industry Sector ID associated with the material. Column references INDUSTRY_ID in L_INDUSTRY.

MAT_TYPE_ID Numeric (38,0)

Unique identifier for the material type. Column includes the material type associated with each material.

MAT_TYPE_DESC VarChar (30)

Material type description. Table is also used as lookup for this attribute.

MAT_CAT_ID Numeric (38,0)

Unique identifier for the material category. Column includes the material category associated with each material.

MAT_CAT_DESC VarChar (30)

Material category description. Table is also used as lookup for this attribute.

MAT_GRP_ID Numeric (38,0)

Unique identifier for the material group. Column includes the material group associated with each material.

MAT_GRP_DESC VarChar (30)

Material group description. Table is also used as lookup for this attribute.



Column Comment



MAT_PRICE_GRP_ID Numeric (38,0)

Unique identifier for the material pricing group. Column includes the pricing group associated with each material.

MAT_PRICE_GRP_DESC VarChar (30)

Material type description. Table is also used as lookup for this attribute.

Table: L_PROD_HIER


PK Unique identifier for the product hierarchy lowest level.

PROD_HIER_L0_DESC VarChar (50)

Level 0 description.


Unique identifier for Level 1.


Description for Level 1 (this table is also used as lookup for this attribute).


Unique identifier for Level 2.


Description for Level 2 (this table is also used as lookup for this attribute).

Table: L_REF_DOC_TYPE

REF_DOC_TYPE_ID Numeric (38,0)

PK Unique identifier for the reference document type.

REF_DOC_TYPE_DESC VarChar (30)

Reference document type description.

Table: L_REGION

REGION_ID Numeric (38,0)

PK Unique identifier for the customer region.

REGION_DESC VarChar (30)

Region description.

COUNTRY_ID Numeric (38,0)

FK Country ID associated with the region. This column references COUNTRY_ID in L_COUNTRY.



Column Comment



Table: L_SALES_DOC_STATUS

SALES_DOC_STS_ID Numeric (38,0)

PK Unique identifier for the status associated with a sales document.Note: These statuses are different depending on the type of document; values may apply only to specific document types.

SALES_DOC_STS_DESC VarChar (30)

Sales document status description.

Table: L_SALES_DOC_TYPE


PK Unique identifier for the type of sales document.

SALES_DOC_TYPE_D VarChar (30)

Sales document type description.

Table: L_SALES_GROUP


PK Unique identifier for the sales group.

SALES_GROUP_DESC VarChar (50)

Sales group description.

SALES_OFFICE_ID Numeric (38,0)

FK Sales office to which the sales group belongs. This references SALES_OFFICE_ID in L_SALES_OFFICE.

Table: L_SALES_OFFICE

SALES_OFFICE_ID Numeric (38,0)

PK Unique identifier for the sales office.

SALES_OFFICE_DESC VarChar (30)

Sales office description.

Table: L_SALES_ORG


PK Unique identifier for the sales organization.

SALES_ORG_DESC VarChar (30)

Sales organization description.


FK Industry sector associated with the sales organization. This column references INDUSTRY_ID in L_INDUSTRY.



Column Comment



COMP_CODE_ID Numeric (38,0)

FK Company code associated with the sales organization. This column references COMP_CODE_ID in L_COMP_CODE.

Table: L_SDOC_ITEM_STATUS

SDOC_ITEM_STS_ID Numeric (38,0)

PK Unique identifier for the sales document item status.Note: These statuses are different depending on the type of document; values may apply only to specific document types.

SDOC_ITEM_STS_DESC VarChar (30)

Item status description.

Table: L_SHIP_POINT

SHIP_POINT_ID Numeric (38,0)

PK Unique identifier for the shipping point.

SHIP_POINT_DESC VarChar (30)

Shipping point description.

Table: L_TIME

DATE_ID TimeStamp (0)

PK Unique identifier for the date attribute. It is used as ID and Description of the attribute.

MONTH_ID Numeric (38,0)

FK Unique identifier for month. References MONTH_ID in L_MONTH.


FK Unique identifier for quarter. References QUARTER_ID in L_QUARTER.



Table: L_MONTH_YTD

MONTH_ID Integer (4) FK Unique identifier for the month; the current format is YYYYMM and is stored as an integer. It references MONTH_ID in L_CAL_MONTH.

YTD_MNTH_ID Integer (4) PK Unique identifier for the month; the current format is YYYYMM and is stored as an integer. For a given MONTH_ID, this field includes all the months of the year up the the selected month.

Table: L_QUARTER_YTD



Column Comment



QUARTER_ID Integer (4) FK Unique identifier for the quarter; the current format is YYYYQ and is stored as an integer. It references QUARTER_ID in L_CAL_QTR.

YTD_QTR_ID Integer (4) PK Unique identifier for the quarter; the current format is YYYYQ and is stored as an integer. For a given QUARTER_ID, this field includes all the quarters of the year up the the selected quarter.



Column Comment

© 2010 MicroStrategy, Inc. Glossary: ad hoc query 235

GLOSSARY

ad hoc query A SQL query dynamically constructed by desktop tools and whose results are not known before it is sent to the server. The user is asking a new question that has not been answered by an existing report.

analyst The user category of a person who accesses reports, performs drilling, does exception reporting, generates report requirements, and is aware of the necessary analysis. An analyst receives useful data from information devices like alphanumeric pagers, fax machines, and e-mail without fully understanding how such information is derived or delivered.

analytics Predefined tools that allow analysis within each Analysis Module's functional areas. Analytics include reports, scorecards, dashboards, and so on.

attribute A data level defined by the system architect and associated with one or more columns in a data warehouse lookup table. Attributes include data classifications like Region, Order,

Glossary Sales & Distribution Analysis Module Reference

236 Glossary: axes, axis © 2010 MicroStrategy, Inc.

Customer, Age, Item, City, and Year. They provide a means for aggregating and filtering data at a given level.

See also:

• form

• expression

• child attribute

• parent attribute

axes, axis 1) A vector along which data is displayed. There are three axes—Row, Column, and Page. When a user defines a template for a report, he places template units—attributes, dimensions, metrics, consolidations, and custom groups—along each axis.

2) One part of a multi-part graphical diagram. Many SDAM reports display data on more than one graphical axis, such as the Quotation Activity Summary report and the Quarterly Conversion Summary report.

backlog Open sales orders, or goods that have been ordered but not yet processed for delivery. A backlog may represent delays from the manufacturer, delays in delivery processing, an unanticipated number of new orders, and so on.

business intelligence (BI) system

A system that facilitates the analysis of volumes of complex data by providing the ability to view data from multiple perspectives.

category See hierarchy.

child attribute The lower-level attribute in an attribute relationship.

See also:


• relationship

Sales & Distribution Analysis Module Reference Glossary

© 2010 MicroStrategy, Inc. Glossary: column 237

column 1) A one-dimensional vertical array of values in a table.

2) The set of fields of a given name and data type in all rows of a given table.

3) MicroStrategy object in the schema layer that can represent one or more physical table columns or no columns.

See also axis.

conditional metric A metric containing filter criteria in its definition.

consolidation An object that can be placed on a template and is made up of an ordered collection of elements called consolidation elements. Each element is a grouping of attribute elements that accommodates inter-row arithmetic operations.

Compare custom group.

cross sell The strategy of proactively marketing related products or services in addition to what a customer is already interested in buying. The results of a successful cross sell include an increase in the total value of the customer order or transaction.

custom group An object that can be placed on a template and is made up of an ordered collection of elements called custom group elements. Each element contains its own set of filtering qualifications.

data modeling A method used to define and analyze data requirements needed to support the business functions of an enterprise. These data requirements are recorded as a conceptual data model with associated data definitions. Data modeling defines the relationships between data elements and data structures.


238 Glossary: data warehouse © 2010 MicroStrategy, Inc.

data warehouse 1) A database, typically very large, containing the historical data of an enterprise. Used for decision support or business intelligence, it organizes data and allows coordinated updates and loads.

2) A copy of transaction data specifically structured for query, reporting, and analysis.

decile, deciling The method by which a group is broken up into ten groups of equal elements. The first decile consists of the top ten percent; the second, the 11th to 20th percent; the third, the 21st to 30th percent; and so on.

dimension An element or factor making up a complete entity or variable (a quantity that may assume any one of a set of values). For example, product types, times, and regions are three dimensions pertaining to sales. Different product types are sold over different time zones in different regions.

document 1) A container for formatting, displaying, and distributing multiple grid and graph reports from a single request.

2) The MicroStrategy object that supports such functionality.

drill A method of obtaining supplementary information after a report has been executed. The new data is retrieved by re-querying the Intelligent Cube or database at a different attribute or fact level.

See also:

• page-by

• sort

• subtotal

editor A dialog box used to create and edit MicroStrategy Objects. There is a Filter Editor, Template Editor, Attribute Editor, Metric Editor, Report Editor, and so on.


© 2010 MicroStrategy, Inc. Glossary: entry level 239

entry level The lowest level set of attributes at which a fact is available for analysis.

ETL (extraction, transformation, and

loading)

1) The process used to populate a data warehouse from disparate existing database systems.

2) Third-party software used to facilitate such a process.

expression Formulas built from functions, attributes, facts, metrics, and consolidations that can be used to define attribute forms, fact calculations, metrics, or filters.

fact 1) A measurement value, often numeric and typically aggregatable, stored in a data warehouse.

2) A schema object representing a column in a data warehouse table and containing basic or aggregated numbers—usually prices, or sales in dollars, or inventory quantities in counts.

See also metric.

fact table A database table containing numeric data that may be aggregated along one or more dimensions. Fact tables may contain atomic or summarized data.

filter A MicroStrategy object that specifies a set of criteria used to limit the data returned in a report. Specifically, it limits the returned values of an attribute in the result set to a specified range. It is normally implemented in the SQL WHERE clause. Examples include: “1997”, “All weekdays in May”, “Stores in the Northeast”.

folder A MicroStrategy object used for grouping and storing in a single place a set of objects that are similar, such as filters, templates, and reports.


240 Glossary: folder list © 2010 MicroStrategy, Inc.

folder list A portion of the interface that lists all the folders found in the project in a hierarchical fashion. It helps a user to visualize and browse through a MicroStrategy project.

form One of several columns associated with an attribute that are different aspects of the same thing. For example, ID, Name, Last Name, Long Description, and Abbreviation could be forms of the attribute Customer. Every attribute supports its own collection of forms.

graph analytic An analytic showing data as points, lines, or bars, arranged according to axes based on the chosen metrics. Although not all analytics can be displayed in every graph type available, many analytics can be viewed in several ways. Choices for graph display include bars, lines, area graphs, and three-dimensional graphs.

grid analytic An analytic consisting of rows and columns. Each row or column has a heading, and each heading represents a prompt from the analytic. Grid analytics can be modified easily, by drilling, moving columns, converting columns to rows, sorting, and using page-by to display subsets of the analytic data as separate pages.

hierarchy A set of attributes defining a meaningful path for element browsing or drilling. The order of the attributes is typically—though not always—defined such that a higher attribute has a one-to-many relationship with its child attributes.

item A single element of an inquiry, quotation, sales order, or delivery order. Each item refers to one of the materials requested by the customer in an inquiry, quotation, or order.

KPI (key performance indicator)

An indicator gauging how well a company progresses in numerous areas such as finance, customer service, and product availability and distribution.


© 2010 MicroStrategy, Inc. Glossary: level 241

level 1) In a data warehouse, facts are said to be stored at a particular level defined by the attribute IDs present in the fact table. For example, if a fact table has a Date column, an Item_ID column, and a fact column, that fact is stored at the Date/Item level.

2) With regard to metric calculation, the level is the level of calculation for the metric. For example, a metric on a report with Year and Store attributes would be calculated at the Year/Store level.

logical data model A graphical representation of data that is arranged logically for the general user, as opposed to the physical data model or warehouse schema, which arranges data for efficient database use.

lookup table A database table used to uniquely identify attribute elements. They typically consist of descriptions of dimensions. Lookup tables are usually joined to fact tables to group the numeric facts in the fact table by dimensional attributes in the lookup tables.

material category A division within the classification schema of company materials (or products).

metadata A repository whose data associates the tables and columns of a data warehouse with user-defined attributes and facts, to enable the mapping of the business view, terms, and needs to the underlying database structure. Metadata can reside on the same server as the data warehouse or on a different database server. It can even be held in a different RDBMS.

metric 1) A business calculation defined by an expression built with functions, facts, attributes, or other metrics. For example: sum(dollar_sales) or [Sales] - [Cost].

2) The MicroStrategy object that contains the metric definition.

See also fact.


242 Glossary: MicroStrategy Analytics Modules © 2010 MicroStrategy, Inc.

MicroStrategy Analytics Modules

A group of MicroStrategy projects with prepackaged metadata, reports, scorecards, and dashboards; default physical and logical data models to allow the modules to work with an external physical schema and data model or with the module’s packaged data warehouse schema; and reference guides for each Analysis Module’s data model, the individual analysis areas, metadata object definitions, data dictionary, and individual report use scenarios.

MicroStrategy Intelligence Server

Core of the MicroStrategy architecture; manages and organizes users, projects, and database connections; coordinates, prioritizes, and executes all user requests; and allocates the resources necessary to complete them. MicroStrategy Intelligence Server tracks schedules, manages security, and provides the ability to monitor and analyze the daily activity of the entire decision support environment.

multidimensional analysis

A form of analysis of the data in a data warehouse that includes many relationships, each representing a dimension. For example, a retail analysis may seek to understand the relationships among sales by region, by quarter, by demographic distribution (income, education level, gender), and by product. Multidimensional analysis provides results for these complex relationships.

object Conceptually, the highest grouping level of information about one concept, used by the user to achieve the goal of specified data analysis. More concretely, an object is any item that can be selected and manipulated, including folders, reports, facts, metrics, and so on.

outline mode Report viewing mode that creates indented, collapsible groupings of related elements to make reports neater and easier to read.


© 2010 MicroStrategy, Inc. Glossary: page-by 243

page-by Segmenting the data in a grid report by placing available attributes, consolidations, and metrics on a third axis called the Page axis. Since a grid is two-dimensional, only a slice of the cube can be seen at any one time. The slice is characterized by the choice of elements on the Page axis. By varying the selection of elements, the user can page through the cube.

See also:

• drill

• sort

• subtotal

parent attribute The higher-level attribute in an attribute relationship with one or more children.

See also:

• child attribute

• relationship

patterns Associations among data in a database which recur with some frequency. A pattern can be the fact that whenever product A is purchased, 73% of the time a customer also purchases product B; or product C is most often purchased at a particular time of year. In each of these examples, there is an association or linking of two or more facts to form a pattern.

project The highest-level intersection of a data warehouse, metadata repository, and user community, containing reports, filters, metrics, and functions.

prompt MicroStrategy object in the report definition that is incomplete by design. The user is asked during the resolution phase of report execution to provide an answer that completes the information. A typical example with a filter is choosing a specific attribute on which to qualify.


244 Glossary: query © 2010 MicroStrategy, Inc.

query A request for data from a database or data warehouse. A report is a database query.

ranking A type of OLAP function that returns the rank of a value in a group of values. Rows with equal values with respect to the ordering are assigned the same rank.

reference document A document that is related to the one in question, but which was created earlier. For example, a sales order usually originates from a quotation; the quotation is the reference document for the sales order. A quotation often originates from an inquiry; the inquiry is the reference document for the quotation.

relate table A table containing the ID columns of two or more attributes, thus defining associations between them.

relationship An association specifying the nature of the connection between one attribute (the parent) and one or more other attributes (the children). For example, City is a child attribute of State.

See also:


• child attribute

report The central focus of any decision support investigation. A report allows users to query for data, analyze that data, and then present it in a visually pleasing manner.

schema 1) The set of tables in a data warehouse associated with a logical data model. The attribute and fact columns in those tables are considered part of the schema itself.

2) The layout or structure of a database system. In relational databases, the schema defines the tables, the fields in each table, and the relationships between fields and tables.


© 2010 MicroStrategy, Inc. Glossary: schema object 245

schema object MicroStrategy object created, usually by a project designer, that relates the information in the logical data model and physical warehouse schema to the MicroStrategy environment. These objects are developed in MicroStrategy Architect, which can be accessed from MicroStrategy Desktop. Schema objects directly reflect the warehouse structure and include attributes, facts, functions, hierarchies, operators, partition mappings, tables, and transformations.

scorecard A type of tally sheet displaying a company's performance using key performance indicators (KPIs) that gauge how well a company progresses in areas such as finance, customer service, and product availability and distribution.

See also KPI (key performance indicator).

segmentation A technique for deriving clusters and classes by creating two-way and multi-way splits from a dataset according to specified variables.

sort Arranging data according to some characteristic of the data itself (alphabetical descending, numeric ascending, and so forth).

See also:

• drill

• page-by

• subtotal

subtotal A totaling operation performed for a portion of a result set.

See also:

• drill

• page-by


246 Glossary: threshold © 2010 MicroStrategy, Inc.

threshold Used to create conditional formatting for metric values. For example, if dollar sales is greater than $200, format that cell to have a blue background with bold type.

transaction An action that results in the creation of a document, either an inquiry, quotation, or sales order. Each transaction includes a number of items.

See also item.

transformation A schema object that encapsulates a business rule used to compare results of different time periods. Transformations are used in the definition of a metric to alter the behavior of that metric.

user hierarchy Named sets of attributes and their relationships, arranged in specific sequences for a logical business organization. They are user-defined and do not need to follow the logical model.


INDEX

Aad hoc analysis 6advanced metrics 6analysis areas 4analytics defined on 2Analytics Modules defined on xiiattributes defined on 11

Bbacklog

analysis 122by customer 125by material 123by organization 124

base formula metrics 140

Ccolumns 219configuring the module xiicurrency 205custom grouping 5customer delivery history analysis 117customer loyalty 77customer sales

analysis 76

Ddashboards 6, defined on 9dashboards, about 2data warehouse

and porting 12default, tables and columns 213options 13populating 213

definitions

object 12project 11

delivery

analysis 103by region 115by shipping point 110metrics 154open 105reports 104summary 108

delivery document facts 207delivery history analysis 117delivery service

analysis 121

Index Sales & Distribution Analysis Module Reference

248 © 2010 MicroStrategy, Inc.

by customer 130by shipping point 128metrics 157quality 126reports 122

delivery status 64designing analytical applications xiiidimension defined on 12documents 6, defined on 9drilling defined on 6

Eeditor 12entry level defined on 171Erwin file

logical schema 11, 168physical schema 13, 214

ETL routines 213expression, form 172

Ffact tables

and quantity facts 205in logical schema 169in physical schema 214

facts defined on 11, 204delivery document 207quantity 205sales document 205

filter 5filters glossary 161folder

public objects 8reports 7schema objects 12

form expression 172formatting, reports 5

Gglossaries

filters 161metrics 139prompts 165

Hhierarchies 12, defined on 170

customer 181delivery document 191material 175organization 170reference document 189sales document 185shipping point 195time 196user 202

Iinquiry

analysis 29by material 31, 34by region 36by sales office 32conversions 55metrics 143reports 30summary 30

inquiry-to-quotation conversion analysis 54

installing the modules xiiiinternational support xxiiintroduction 1

Kkey performance indicators 6, 8

Sales & Distribution Analysis Module Reference Index


Llogical data model defined on 11, 167logical schema diagram 168lookup tables 172

Mmapping methods 12material sales analysis 81metadata defined on xiimetrics

advanced, about 6base formula 140delivery 154glossary 139inquiry 143quotation 145sales order 148service 157

MicroStrategy Report Services 7MicroStrategy Sales & Distribution Analy-

sis Module

about 1analysis areas 4features 5solutions 2

module

about xiiconfiguring xiiinstalling xiiiporting xiii

multidimensional 11

Oobject

definitions 12glossary 139

properties 12public, about xischema, about xi

outbound delivery analysis 107Outline mode defined on 42

Ppage-by defined on 5physical schema

about defined on 13diagram 214

populating a data warehouse 213porting a module xiii, 12prerequisites xivProduct hierarchy 176

example 38profitability 73project

accessing 7definitions 11, 139

prompts defined on 5glossary 165

properties, object 12public objects folder 8

Qquantity facts 205quotation

analysis 37by customer 43by distribution channel 51by material 47by product 41by region 50conversions 54metrics 145rejections 53

Index Sales & Distribution Analysis Module Reference

250 © 2010 MicroStrategy, Inc.

reports 39summary 40, 45

quotation history analysis 59

RRDBMS 213reference document 38, 64reports

accessing 7formatting 5usage scenarios 7

resources xvreturns

analysis 132by customer 136by material 135processing 133

Ssales

by customer characteristics 78by organization 71summary 68top 5 orders 75

sales document facts 205sales order

analysis 63by customer 67by material 66metrics 148reports 64summary 65top 5 75

schema

logical 168physical 214

schema objects folder 12

scorecards 2, defined on 8, 15SDAM. See MicroStrategy Sales and Distri-

bution Analysis Module.

segmentation defined on 5shipping point

contributions 113delivery volumes 112processing times 114

solutions 2source tables 12support

international xxiisupport. See technical support.

Ttable columns 219tables

lookup 172physical 216

technical support xxiiithresholds defined on 5transformations 201