Objects for Business Reporting

1
Objects for Business Reporting

• MIS 497

2
Objective

• Learn about miscellaneous objects required for
  business reporting.

3
Why?

• Our final goal in this project is to create
  business reports.
• Business reports are made of other objects,
  namely attributes, metrics, filters,
  consolidations and custom groups.
• In the following sections well examine each of
  these groups and then start making our own
  business reports.
• Example Quarterly Sales Report for 1998

4
Filters
5
What is a Filter?

• A Filter specifies the conditions that the data
  must meet in order to be included in the report
  results. It affect the WHERE clause in SQL.
• Examples
• Year1998
• CityBoston
• ItemSKU107345

6
Defining a Filter

• Defining of a Filter might involve two distinct
  sets
• Creation of the desired set(s) or report objects
• Comparisons
• Value (eg. Dollar Salesgt10,000)
• Rank (eg. Top 5 Customers)
• Percentage (eg. Bottom 20 Stores)
• Pattern (eg. Customer Name Begins With John)
• Combination of the created sets
• Logical Operation
• And
• Or
• Not

7
Set Combinations

• Union Operator (OR) takes union of two sets.
  Data that belongs to any of the united sets is
  returned with this operation
• Exclusion Operator (NOT) which takes a negation
  of a set.
• The Intersection Operator (AND) which intersects
  two sets.
• Example
• Set 1 Boston, Chicago, Miami
• Set 2 Boston, Atlanta
• Union Set 1 OR Set 2 Boston, Chicago, Miami,
  Atlanta
• Exclusion Set 1 NOT Boston Chicago, Miami
• Intersection Set 1 AND Set 2 Boston

8
Comparison Operators

• Value Comparison Operators compare values.
  Values can be numbers, text, strings, column
  names or expressions. The comparison operators
  are
• Between
• Not between
• Exactly
• Greater than
• Greater than or equal to
• Less than
• Less than or equal to
• Different from

• Pattern comparison operators (case sensitive)
  allow text strings to be compared
• Begins with
• Ends with
• Contains
• Does not begin with
• Does not end with
• Does not contain
• Like
• Not like

• Rank operators used to define Metric
  qualifications
• Bottom
• Equals
• Exclude Top
• Exclude Bottom
• Top

9
Filter Creation

• A Filter can be created as its own object or as a
  part of a report.
• Types of qualification
• Select Elements
• Qualify on Attribute
• Qualify on Metric
• Qualify on Filter
• Advanced/Custom Filter

10
Filter Creation

• Specific Instructions on how to create filters
  can be found in the Report Designer Guide

11
Metrics
12
What is a Metric?

• A Metric is a business value.
• Metrics allow analysis of data.
• Metrics are built from Facts, Functions,
  mathematical operators and other Metrics.
• Metrics are defined in MSTR Desktop, not in the
  DW.
• The DW contains fact columns metrics are defined
  by a combination of fact columns and aggregate
  functions or by using existing metrics.

13
Types of Metrics

• Simple Metrics
• Compound Metrics
• Conditional Metrics
• Dimensional Metrics
• Transformation Metrics
• Not covered in this class.

14
Simple Metrics

• Simple metrics are built from Facts, Functions
  and mathematical operators.
• Examples
• Total Sales Sum(Order Amt)
• Total Cost Sum(Unit Cost)
• Profit Sum(Order Amt) Sum(Unit Cost)
• Price Avg(Discount Price)

15
Simple Metrics

• Facts
• Facts are stored as Schema Objects. They are
  included into the metric by dragging and dropping
  into the Formula window in the Metric Editor.
• Functions
• The default function is SUM. Other functions
  ranging from Max/Min to Rank and Between are
  available in the Functions and Operators folder
  in Schema Objects.

16
Compound Metrics

• Two existing simple metrics can be combined with
  mathematical operators to create a compound
  metric.
• Examples
• Profit Total Sales Total Cost
• Profit Margin (Total Sales Total
  Cost)/Total Cost
• To Market Total Sales/Market Sales

17
Compound Metrics

• Functions
• Functions, such as SUM are not used in the
  creation of compound metrics.
• Advanced Functionality
• The underlying definitions of the simple metrics
  comprising a compound metric are carried through
  to the calculation of the compound Metric.
• Compound metrics are automatically updated when
  changes occur in the definitions of the metrics
  they include.

18
Dimensional Metrics

• Dimensionality Allows the creator of a Metric to
  determine the level at which a Metric can be
  calculated.
• All metrics, by default are given dimensionality
  at the report level. This means that the
  default level of calculation of a metric is
  according to the level of attributes on the
  template or report grid.
• Any attribute can be specified as the
  dimensionality of a metric, thereby determining a
  level of calculation. If an attribute is
  specified, report level calculations will be
  overridden.

19
Dimensional Metrics
20
Dimensionality - Filtering

• This setting governs the relationship between the
  report filter and the calculation of the metric.
• Standard doesnt impact the Report Filter
• Absolute will calculate a metric taking the
  report filter into consideration but including
  related criteria in a WHERE EXISTS clause rather
  than in the WHERE clause itself.
• Ignore will calculate a Metric completely
  disregarding filter criteria directly related to
  the attribute specified in dimensionality (direct
  child, parent, grandparent).
• None determine, from other level units how to
  handle related filter attributes.

21
Dimensionality - Filtering
22
Conditional Metrics

• Metric Conditionality allows a Filter to be
  applied to the definition of a Metric.
• This allows any Metrics on the Report to be
  calculated according to separate Filter criteria.
• Only one Filter may applied at a time to the
  definition of a Metric.
• Conditionality may be applied only to Metrics
  with an aggregate operator in the formula.

23
Non-Aggregatable Metrics

• Some facts dont make sense if they are
  aggregated across some dimensions, thus when
  creating metrics based on them one should
  designate them as non-aggregatable metrics.
• Examples
• Stock or inventory calculations
• Bank balances
• Cant be aggregated across Time, but might be
  summarized over Geography or Product dimensions.
• Uses Grouping functionality in the Metric Editor.

24
Count of Attributes

• Metrics can be defined by a combination of
  aggregate operators or attributes rather than
  fact columns.
• Examples
• Count Items
• Count Customers

25
Metric Creation

• Specific Instructions on how to create metrics
  can be found in the Report Designer Guide

26
Templates
27
What is a Template?

• A Template defines the layout of general
  categories of information in a report. In a
  template, you specify the information you want to
  retrieve from the DW and the way you want it to
  be displayed.

28
Template Creation

• A template can be created as its own object
  (global template) or a part of a report (local
  template).
• When its created as a separate object, it can
  be reused in other reports.
• Changes in local template will affect only that
  report where it was created changes in a global
  template will affect all reports that use it.

29
Template Creation

• Template is a quite simple object that is created
  by dragging and dropping objects (attributes,
  metrics, consolidations and custom groups). The
  best way to learn more about templates is to
  play with them.
• Template behavior is similar to the one of Pivot
  Tables in Microsoft Excel.
• More on templates can be found in Report
  Designer Guide

30
Reports
31
What is a Report?

• A Report is a combination of a template (format)
  and a filter (content) it is the data presented
  to the end user.

32
Report Creation

• Reports are created and modified using the Report
  Editor. Reports can be created using predefined
  Templates and Filters or create them on the fly
  through the Report Editor.

33
Report Display Modes

• Grid
• Graph
• SQL
• Design

34
Report Manipulations
35
Totals and Subtotals

• Totals and subtotals can be set separately for
  both rows and columns
• If there are several attributes on the Report,
  subtotals can be turned on for selected
  attributes.
• There are many types of totals beside usual
  total, including count, minimum, maximum,
  average, median, mode, standard deviation,
  variance, geometric mean and product.

36
Pivoting

• Pivoting allows moving of Attributes or Metrics
  between rows and columns directly on the Report.

37
Sorting

• Sorting can be done both in quick and advanced
  mode.
• Pictured Advanced Mode

38
Drilling

• Drilling allows one to look at specific data at
  level other than that of the original grid or
  graph. User can drill down, up or across
  Attributes.

39
Page By

• For better view of a report, one might use the
  Page By functionality

40
Exporting

• Export Destinations can be one of the following
  file types
• Excel
• Word
• Access
• Text File
• HTML file

41
What to read

• Microstrategy Report Designer Guide.