Report Filter vs View Filter

Key Concepts

1. Report Execution Flow:

• MicroStrategy checks for data in this order:

• Intelligent Cubes (faster as it avoids database queries).

• Caches (stored results from previous executions).

• Metadata (definitions and connection details, leading to SQL generation).

• SQL execution involves three engines: SQL Engine, Query Engine, and Analytical Engine.

2. Dynamic Sourcing:

• Automatically uses existing Intelligent Cubes for data retrieval, improving performance.

• Often disabled at the project level due to potential data mismatch issues.

3. Caching:

• Improves performance by storing results.

• Configurable at project or report level (e.g., expiration schedules).

• Should be disabled if real-time data is required.

4. SQL Generation:

• SQL statements are generated automatically by the SQL Engine based on metadata definitions.

• View SQL for debugging, identifying joins, and filter conditions.

Types of Filters

1. Report Filter:

• Applied before report execution.

• Impacts the WHERE clause or occasionally the HAVING clause in SQL.

• Reusable and can be applied across multiple reports.

• Example: Filter for “Region = South” will include WHERE region_id = 'South' in SQL.

2. View Filter:

• Applied after report execution.

• Works only on report objects (objects already included in the report).

• Calculation happens in the Analytical Engine and does not modify SQL.

• Example: Filter for “Region = South” in a view filter applies post-SQL execution in MicroStrategy’s OLAP layer.

3. Report Limit:

• Restricts the number of rows or aggregates returned in the result.

• Applied in the HAVING clause.

4. Security Filter:

• Restricts data based on user permissions (e.g., region-level access).

Differences Between Report Filter and View Filter

1. Timing:

• Report Filter: Applied before report execution.

• View Filter: Applied after report execution.

2. SQL Impact:

• Report Filter: Directly impacts the SQL by modifying the WHERE or HAVING clause.

• View Filter: Does not affect the SQL; calculations are applied within MicroStrategy.

3. Flexibility:

• Report Filter: Can include attributes that are not part of the report.

• View Filter: Limited to attributes and metrics already present in the report (report objects).

4. Calculation Location:

• Report Filter: Calculations happen in the SQL Engine and are processed at the database level.

• View Filter: Calculations occur in the Analytical Engine within MicroStrategy.

Advantages of View Filters

• Avoids database hits; calculations are performed at the MicroStrategy level.

• Faster adjustments to data visualization without re-executing SQL.

Key Features to Highlight

1. Dependency Analysis:

• Use Search for Dependents to identify which reports use a specific filter.

• Ensures changes to filters do not disrupt dependent reports.

2. OLAP Services:

• Includes Dynamic Aggregation, Derived Metrics, Derived Elements, and View Filters.

3. Analytical Engine:

• Handles subtotals, grand totals, and OLAP-based calculations.

4. Reusability:

• Filters and other objects can be reused across multiple reports for consistency.

Best Practices

1. Use Report Filters for data restrictions known prior to execution.

2. Use View Filters for post-execution filtering based on report objects.

3. Avoid modifying shared filters directly—check dependencies first.

4. Disable caching if real-time data is critical or if frequent data updates occur.

These points should give you a strong foundation for answering questions about MicroStrategy’s report filters, view filters, and execution process.