Types of Report Caches
There are two
categories of report caches, Matching and History
Based on these two
categories, the following types of report caches are displayed in the Cache
Monitor:
1.
Matching caches
2.
History caches
3.
Matching-History caches
4.
XML caches
Matching caches
When a report is
run in a MicroStrategy 8.x project, with report caching enabled, the
Intelligence Server determines for each report request whether it can be served
by an already existing cache. If there is no match, it then runs the report on
the database and creates a new cache. The type column for this cache on the
cache monitor will be 'Matching.'
Matching-History caches
When a report is
sent to history directly instead of being executed, the type column in the
cache monitor will be 'Matching, History.' Matching-History cache is a Matching
cache with at least one History List message referencing it. It is actually one
cache with two logical parts: Matching and History.
History caches
The following two
circumstances result in the Type column displaying only 'History':
1.
Invalidation of a report cache after is has been sent to
History: When a report is sent to History, the Type column in the
Cache Monitor for this cache entry, will display 'Matching, History'. The Type
column of the cache changes to "History" if the cache is invalidated.
The cache is invalidated when report definition is changed and user saves the
modified report. Administrator can also invalidate cache by right clicking the
cache entry in cache monitor and choosing 'Invalidate Cache'.
2.
When report caching is not enabled and the report is sent
to History: Report caching is not enabled for the project as evidenced by the
setting 'Enable report server caching' in Project
Configuration > Caching, being unchecked. When a report is run, no cache
will be created. Under these circumstances, when a report is sent to History,
there will be an entry in the Cache Monitor and its Type column will display
only 'History'.
XML Caches
When a report is
executed from Web, an XML cache for this report is created in XML format. It is
available for reuse on Web later on. It is possible that the XML cache is
created at the same time as its corresponding normal report cache. Although
just a different format of the same report cache, the XML cache is maintained
as a distinct cache and thus counts towards the maximum number of caches as an
independent unit. It is automatically removed when the associated report or
History cache is removed.
Types of Caches
1. Element Cache
Used by attribute
element list.
When is it created?
1. Browse attribute
elements when browsing a hierarchy
2. Browsing a prompt
3. In Filter Editor
2. Object Cache : when
you open the editor of an object
When is it created?
1. Opening a report
editor
2. Opening a
attribute/fact /metric editor
3. Report Cache : when
executing a report
Matching and
History - Types
4. Document Cache :
when executing a document
How to resolve many to many relationships?
To resolve a
many-to-many relationship means to convert it into two one-to-many, many-to-one
relationships. A new entity comes between the two original entities, and this
new entity is referred to as an intersection entity or cross reference entity.
It allows for every possible matched occurrence of the two entities. For
example, the "many-to-many" relationship of many EMPLOYEEs are
assigned many TASKs which can be resolved by creating a new entity named
EMPLOYEE_TASK. This resolves the "many-to-many" relationship by
creating two separate "one-to-many" relationships. The two
"one-to-many" relationships are EMPLOYEE or parent entity which is
assigned EMPLOYEE_TASK or child entity and TASK or parent entity is assigned to
EMPLOYEE_TASK or child entity. Whilst this may appear complex, the introduction
of the EMPLOYEE_TASK child entity reduces data redundancy and improves overall
database and application performance.
Major
Differences between 8 and 9
1. Distribution
Services is new in 9
2. Ability to create
prompts and filters in web
3. Drilling in
Documents
4. Dashboards with
multiple layouts
5. Intelligent Cubes
6. Back and Forward
buttons in web
7. Personalised prompt
answers
What is evaluation ordering?
Determines the
order in which Analytical Engine performs different kinds of calculations.
Can be set at:
Project, Report, Template
Default Ordering
1. Subtotal
2. Compound Metrics
3. Consolidation
4. Metric Limit
User Defined
Ordering
1. Compound Metrics
2. Consolidation
3. Metric Limit
4. Subtotal
What are the VLDB properties?
Very Large Scale
Database Properties.
Governing: Intermediate Row Limit, Maximum SQL/MDX size, result set
row limit
Joins
Attribute to join
when key from neither side can be supported by the other side
Possible Values: -
Join common key on both sides, Join common attributes (reduced) on both sides
Base Table Join for
Template: Controls how fact tables are joined, for a report containing metrics
from different fact tables, or for a compound metric which has the base metrics
coming from different fact tables. Controls whether temporary tables will be
created for each metric or if fact tables will be directly joined.
Downward Outer Join
Option: Controls how joins are performed when joining metrics which are
calculated at different levels.
Full Outer Join
Support: Controls the property which informs the Engine if a full outer join is
supported by the Target database.
Preserve All Final
Pass Result Elements: Controls how final pass result elements are joined to
Lookup/Relationship tables
Preserve all Lookup
Table Elements: Provides users the option to control if all lookup table
elements should be included in the final results.
Metrics
Metric Join Type:
Controls the type of join that is used to join a metric's data with other
metric data on a report.
Default to Metric
Name
Null Check
Query Optimizations
SQL Global Optimization: Determines if SQL should be optimized by
combining multiple passes, and it should be optimized, controls the level of
optimization
- Level 0: No optimization.
- Level 1: Remove Unused and Duplicate Passes.
- Level 2: Level 1 + Merge Passes with Different SELECT
- Level 1: Remove Unused and Duplicate Passes.
- Level 2: Level 1 + Merge Passes with Different SELECT
WHERE clause driving table: Controls which table the Engine should use
to apply the filter (WHERE clause). By default Fact Table is used
Intelligent Cubes
What is an Intelligent Cube?
In-memory version of report data that can be manipulated by the
MicroStrategy Analytical Engine?
Types of Cubes
Two unique methods to implement Intelligent Cube Technology:
•Personal Intelligent Cubes: You can begin by creating reports in
MicroStrategy as usual, and then analyze your reports with OLAP Services
features such as view filters, derived metrics, and derived elements. These
features are processed on the in-memory copy of data known as a personal
Intelligent Cube, rather than processed on the data warehouse.
•Intelligent
Cubes: Rather than returning data from the data warehouse for a single
report, you can return sets of data from your data warehouse and save them
directly to Intelligence Server memory. These sets of data can be shared as a
single in-memory copy, to be used by many different reports created by multiple
users.
Activities on Cubes
1. Dynamic Aggregation
2. Derived Metrics
3. Derived Elements
4. Metric
Filters and View Filters
Advantages of Cubes
1. Fast Performance
2. Scheduling the Cube
3. Drilling
4. Data Sharing
Difference between Standard and OLAP reports
If none of the OLAP
features are used then it is a standard report, once any feature is added like
view filter, derived metrics then its converted to a OLAP report. A Standard
report can be converted to OLAP but not vice versa.
Difference between Personal Intelligent Cube and Intelligent
Cube
1. In PIC, Full access to re execute
data against the warehouse but in IC , in order to re-execute against the
warehouse , we have to drill on the data.
2. PIC is linked to a
single report whereas multiple reports can access a IC.
3. Both view and
report filters can be used in PIC but only view filters can be used in IC
4. In IC, prompts can be used only on
objects included in IC but in PIC it can be applied even on objects not in the
prompt.
5. Security Filters
can be applied on both IC and PIC.
6. Consolidations and Custom Groups cannot be used in reports
using IC but this can be achieved by using derived elements
7. Derived elements
can be used only on IC not on PIC.
Features not supported in Intelligent Cubes
1. Consolidation and
Custom Group
2. OLAP Service
Features: View Filters and Derived metrics cannot be used
3. Prompts cannot be
used
What is dynamic sourcing?
Dynamic sourcing
extends the accessibility of Intelligent Cubes by allowing standard reports to
access any published Intelligent Cubes that can satisfy the data requirements
of the report
How to Unpublish and Intelligent Cube?
From the Folder
List, expand Administration, then expand System Monitors, then expand Caches,
and select Intelligent Cubes. The Intelligent Cube Monitor is displayed. Right-click
an Intelligent Cube and select Delete. Unpublish only deletes data in the cube
but not the cube itself.
When does the report fails due to the unavailability
of Intelligent Cubes?
1. When I Cube is not
published
2. When enough space
is not there for publishing
3. Cube is in the
process of publishing
4. Cube is offline
What are the
different types of derived elements?
1. Group Derived: A Group derived element is a
combination of attribute elements into a single derived
element.
Eg: East Coast: Groups the Mid-Atlantic,
Northeast, and Southeast attribute elements.West Coast: Groups the Northwest
and Southwest attribute elements
2. Filter Derived: A Filter derived element
uses a filter qualification to determine the combination of attribute elements
for a derived element.
For Eg: Southern
Regions: Returns attribute elements whose name begins with South.
•Northern Regions:
Returns attribute elements whose name begins with North.
3. Calculation Derived: A Calculation derived
element uses operators and functions to combine attribute elements and derived
elements into calculations that define a single derived element
All other derived
elements: Collects all attribute elements that are not inclulded in derived
elements and includes them as individual attribute elements by default
Comments