Advanced Filters

When evaluating Dynamic Dates, in what order should the filter be built?

Addition/Subtraction of days, weeks, months, years should always be done first Then "first of month", "this week", "this year", etc ex. Today = Feb 2 First of Month(Today - 7) = Jan 1 First of Month(Today) - 7 = Jan 27 ** incorrect**

What are the 5 pre-defined Pass-Through Apply Functions?

ApplySimple - simple arithmetic operators. used for defining metrics, filters and attribute form expressions ApplyAgg - uses aggregate functions (sum, max, etc). used for defining facts ApplyOLAP - OLAP functions (rank, RunningSum, etc). used for defining metrics ApplyComparison - uses comparison operators (<=, >, etc). used primarily for defining filters ApplyLogic - uses logical operators (and, or, or not, and not). used for defining filters

What is the difference between Attribute-to-Attribute and Join Element List filters?

Attribute-to-Attribute are on the attribute forms Join Element Lists are on the attribute elements

What are the most common use of Relationship Filters?

Market basket - provides the retailer with information to understand the purchase behavior of a buyer Product Affinity Analysis - identify the relationship between 2 products

What are the different Metric qualifiers?

Metric value - value on which to qualify a metric (ex. metric value > 100) Rank - numeric rank of values (ex. rank top 40) Percent - percentages of the values being ranked (ex. percent top 10)

What external data sources can you use to Import Filter Elements?

Microsoft Excel or a text file

How do you define Relationship Filters?

Output Level - list of attributes on which to filter (GROUPBY) Filter Qualification - filtering criteria (WHERE) Relation (or Relateby) - defined through a fact, table or system default. (FROM)

What is the Relationship Filter similar to?

Shortcut-to-a-Report Qualification can often use to achieve the same results

What are some Dynamic Date options?

Today This Week This Month This Year

What is a Dynamic Date Filter?

aka rolling dates. fixed sets of dates, or ranges of dates, that change over time based on the system clock of the Intelligence Server (Desktop in a 2-tier environment) ex. a report that always examines sales from 2 months ago

What is a Join Element List?

combinations of ATTRIBUTE ELEMENTS from different attributes to filter a report. ex. Year & Region attributes. (Central, 2010) OR (Northeast, 2010) OR (Central, 2009) OR (South, 2009) vs simple filter. Year in (2009, 2010) AND Region in (Central, Northeast, South). Would retrieve additional, undesired attribute element pairs such as (2010 AND South) and (2009 AND Northeast)

Is it faster to use pass-through functions or their RDBMS-level counterparts?

depends on how the function is evaluated in the given RDMS. need to test both

What are Relationship Filters and how do you use them?

enable you to define a relationship between two indirectly related attributes without having to include a metric on the report ex. Customer & Date. Only related through the Revenue fact (customer purchases something on a given date)

What is Break By?

enables you to choose the level at which to restart the rank or percentage for a metric. must be greater than, or equal to, the level of aggregation of the metric ex. Top 2 Items by Units Sold vs Top 2 Items by Units Sold with Break of Category

What is Metric-to-Metric Comparison

enables you to create a report that compare the value of two metrics ex. report that returns only those records with Revenue greater than Last Year Revenue

What is an Attribute-to-Attribute comparison filter?

enables you to create reports that compare values of two attributes using their respective ATTRIBUTE FORMS ex. a report that retrieves only those orders that were shipped within 2 days of their order date. Ship_Date < (Order_Date +2)

What are Import Filter Elements?

enables you to import a list of data from existing files (outside MSTR) into the filter definition. at times, may be a quicker alternative to selecting each individual element (ex. hundreds of customers last names) these can also be exported to an external file you can use a prompt to select which file to import from at run-time

What is a Metric qualifier?

enables you to restrict the rows of data being returned by constraining the metrics

What is an Indirect relationship?

no parent/child relationship typically from different hierarchies defined through fact tables

What is a Direct relationship?

parent/child relationship defined through lookup and relationship tables

What are Pass-Through Functions and how can they be used?

provide access to the special function or syntactic constructs that are not standard in MSTR, but are provided by various RDMS platorms. You bypass the Analytical Engine, allows you to take advantage of your databases native functionality

What is a Set Qualification?

restricts rows of data shown on a report based on a metric qualifier and relationship filters

What is the Output Level of the Metric qualifier?

what attribute level you want to qualify on ex. Revenue > 1000, Output Level:Month - limits result set to months that have sales > 1000 If Day is also included on the report (lowest level attribute), it shows only those days that fall in months with sales > 1000