This document outlines the best practices for Dynamic Sourcing in Strategy. The following topics are addressed in this document:
- Intelligent Cube Design - Best Practices
- Intelligent Cube Design - Recommendations by Metric Type
- Report Design - Best Practices
I. Intelligent Cube Design - Best Practices:
- To ensure that the Intelligent Cube can participate in dynamic sourcing, design Intelligent Cubes with:
- the outer join properties enabled or
- enable the "Allow dynamic sourcing even if outer join properties are not set" setting
- Define Intelligent Cubes with the least restrictive filter possible to allow more reports to dynamic source from the Cube.
- It is preferable to have interval filters on the Intelligent Cube as opposed to point based filters as this improves the cube hit ratio.
- E.g., Date between June 1, 2011 and July 1, 2011 vs. Date in (June 1, 2011, June 2, 2011, ..., July 1, 2011)
- All attributes/attribute forms used in the Intelligent Cube filter should also be included in the Intelligent Cube template.
- E.g., Cube Filter: Year in 2008 AND Month in June 2008 > The Intelligent Cube must contain both Year and Month on its template.
- Utilize dynamic sourcing troubleshooting logs (such as the Cube Parse Log) to identify features that prevent a particular cube from being available for dynamic sourcing. These logs are available in the SQL view of Intelligent Cubes and Reports.
- The Strategy OLAP Services Guide lists the features that prevent Intelligent Cubes from being available for dynamic sourcing.
- Use the Cube Advisor to create Intelligent Cubes that will allow as many reports as possible to use dynamic sourcing.
- Reports cannot combine data from multiple Intelligent Cubes. All the data requirements of a report must be satisfied by a single Intelligent Cube.
II. Intelligent Cube Design - Recommendations by Metric Type:
Metric Type
| Metric Type Example
| Intelligent Cube Design
| Intelligent Cube Design Example
|
Simple Metric
| Sum(Revenue)
| Include the simple metric
| Sum(Revenue)
|
Smart Compound Metric
| Profit/Revenue
| Include the base metrics with standard filtering and standard grouping in the Cube. The base metric also needs to be at the report level.
| Profit, Revenue
|
Dimensional Metrics
| Yearly Sales
| Include the base metric with standard filtering, standard grouping and the attributes that constitute the level at which the metric is calculated. The base metric also needs to be at the report level.
| Year, Sales
|
Conditional Metrics
| Sales for Category Books
| Include the base metric with standard filtering, standard grouping and the attributes on which the condition is defined. The base metric also needs to be at the report level.
| Category, Sales
|
Transformation Metrics
| Last Year's Sales
| Include the transformation metric in the Intelligent Cube.
| Last Year's Sales
|
Nested Metrics
| Average Quarterly Sales:
(Avg(Sum(Revenue){~+}{Quarter+})
| Include the inner base metric with standard filtering, standard grouping and the attribute(s) that constitue the level at which the nested metric is calculated. The base metric also needs to be at the report level.
| Quarter, Sum(Revenue)
|
Non-aggregatable Metrics
| Quarterly Inventory:
Sum(Inventory){~+, <|Quarter}
| Include the non-aggregatable metric and the attribute on which the non-aggregation has been defined in the cube.
| Quarter, Quarterly Inventory:
Sum(Inventory){~+, <|Quarter}
|
MDX Metrics*
| Forecast Sales
| Include the same MDX Metric from the MDX source Cube as the one used by the reports.
| Forecast Sales
|
* Strategy does have support for dynamic sourcing with MDX sources.
III. Report Design - Best Practices:
- Report filter should be more restrictive than the Cube filter.
- E.g., Cube Filter: Year between 2006 and 2010; Report Filter: Year in (2007, 2008)
- Report filter should at least filter on all attributes and attribute forms used in the Cube filter.
- E.g., Cube Filter: Year between 2006 and 2010; Report Filter: Year in 2008 AND Month in June 2008
- All attributes used in the report filter should be available in the Cube template. This will facilitate the slicing of data as required by the report filter.
- E.g., Report Filter: Year in 2008 AND Month in June 2008. The Cube must contain both Year and Month on its template.
- The report must contain at least one metric at the same level as that of the attributes on the report.
- Make use of the dynamic sourcing troubleshooting logs (report parse log, mismatch log, extended mismatch log) to identify features that prevent a particular Cube from being available for dynamic sourcing. These logs are available in the SQL view of Intelligent Cubes and Reports.
- The Strategy OLAP Services Guide lists the features that prevent Intelligent Cubes from being available for dynamic sourcing.
