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 we’ll 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: • Year=1998 • City=Boston • Item=SKU107345

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 Sales>10,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 • 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 • 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

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 – doesn’t 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 don’t 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 • Can’t 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 it’s 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.