1. What is MicroStrategy?
MicroStrategy is a business intelligence (BI), enterprise
reporting, and OLAP (on-line analytical processing) software vendor.
MicroStrategy software allows reporting and analysis of data stored in a
relational database, multidimensional database, or flat data file.
MicroStrategy describes its core reporting software as having a
"ROLAP" or "Relational OLAP" architecture, meaning that a
complex relational database can be expressed using a virtual multidimensional
cube structure that can be more easily understood by business users who wish to
navigate through the data.
2. What is metadata in Microstrategy?
It is the database repository where definitions of all
MicroStrategy objects are stored. Metadata could be hosted on most
databases.
In simple words, Metadata could be considered as the heart of MicroStrategy
environment.
3. What is an object prompt in MicroStrategy?
Object prompts provide users the ability to add additional objects
to a report. You can let users select from almost any object(attributes,
metrics, custom groups) available in MicroStrategy.
Object prompts can either determine the definition of the report template or
the report filter.
4. What does MicroStrategy Intelligence Server
allow users to do?
MicroStrategy Intelligence Server provides reporting and OLAP
analysis for the whole enterprise. All business users can obtain scorecards and
dashboards, operational reports, queries and OLAP and predictive analyses
without learning any programming or database syntax.
5. What are the different types of metrics
available in Microstrategy??
Level Metric, Transformation Metric, Pass through Metric, Adaptive
Metric, Non-aggregate Metric, Smart Metric, Derived Metric, Embedded Metric.
6. What is Level Metrics in Microstrategy?
Level metric defined the level at which the metric aggregates. By
default it is the report level.
You can create the level metric as follows.
1. Create a metric with the formula(for eg: Sum(Revenue))
2. Click on Level Dimensionality
3. Add the attributes or Hierarchy you want as level.
4. Save the metric.
7. What is Transformation Metric in
Microstrategy?
Transformation applied to a metric. Transformation is a schema
object which is used in a metric for time based analysis (Example:
Year-over-Year, Month-to-Date, Year-to-Date, etc.). There are two types of
transformation - table based and expression based.
Transformation shortcut metrics apply offset values, such as "four months
ago," to the selected attribute. The offset value is called a
transformation. Use transformation shortcut metrics to compare current values
for a given metric against corresponding values for that metric across a time
period.
For example, if you add a Last Year transformation to a revenue metric, the new
shortcut metric calculates last year revenue.
To create a transformation shortcut metric:
Click the name of a report to execute it. The report must be in either Grid
view or Grid and Graph view.
Right-click the column(s) or row(s) to use to create a transformation metric.
Select Insert Metric and point to Transformation. A menu opens with the
following options. Select the transformation interval in which the selected
column or row is to be displayed:
Normal: Displays unit figures for both the current values and corresponding
values for the interval selected.
Variance: Calculates the difference between current values and corresponding
values for the interval selected, for example, Revenue - (Last Year (Revenue)).
Variance Percentage: Calculates the difference between current values and corresponding
values for the interval selected, expressed as a percentage. For example,
Revenue - (Last Year (Revenue))/(Last Year (Revenue)).
10. What is percent-to-total shortcut metric
in Microstrategy?
Percent-to-total shortcut
metrics display the percent in relation to a selected total of each item
affected by the metric. Use a percent-to-total shortcut metric to show values
as percents of an accumulated row or column total. The metric can also total by
page, for each value of the attribute, or the grand total.
11. Can we access multiple operational
databases from within a single MicroStrategy project?
Yes.
A single MicroStrategy project can create reports accessing data from any
available data source across the enterprise. Developers can create these
reports by typing SQL into a free-form SQL editor and can even re-use stored
procedures, prompting, and security filters to create powerful reporting
applications.
12. What is a compound attribute in
Microstrategy?
A
compound attribute has its value determined by an expression which combines two
or more columns in a database to create a new column
13. What are Configuration Objects?
Configuration
objects are MicroStrategy objects which can be re used in multiple projects and
they appear in the system layer. Ex: Database Instances, Users, Login IDs,
Schedules
14. What are Schema Objects?
The
building block of BI. Schema objects are directly mapped to a column or columns
in the database. Attributes, Facts, Functions & Operators, Hierarchies,
Partition Mappings, Tables & Transformations
15. What are Public Objects?
Objects that generate
analytical data and are built on other schema objects or public objects. Also
called as application objects. Ex: Consolidation, Custom Groups, Drill Maps,
Reports, Documents, Filters, Prompts, Metrics, Templates and Searches
16. What is Adaptive Metric?
A metric defined on a fact which is mapped to two columns in two
tables (detail and aggregate) with different functions applied on both the
columns. This is achieved with pass through functions (ApplySimple and
ApplyAgg).
17. What is Non-aggregate metric?
By default metrics aggregate to a higher level based on the
attributes on a report, the default aggregate function is "sum". This
aggregation can be set to none, so that the metric does not aggregate to any
level.
18. What is Smart Metric?
When a compound metric is defined with other metric objects using
arithmetic operatic (like sum(M1/M2)) the sub total of the metric can be
calculated in multiple ways. Case where they are calculated row by row are
smart metric.
Example: In the above example, if the total are calculated using the mentioned
expression, it is defined as smart metric - "Sum (M1) / Sum (M2)"
19. What is Derived Metric?
A metric created within a report (local to that report) using the
report objects of the same report. Derived metric are OLAP services and are
calculated on the I-Server and do not reflect in the SQL.
Example: If a report has two metrics, M1 and M2. A derived metric can be
defined as M1+M2 or M1/M2 and so on.
20. What is Embedded Metric?
Embedded metrics are objects whose definitions and object IDs are
unique to and exist only in the context of the MicroStrategy Report in which
they reside. An embedded metric will have a different object ID than that from
which it originated. As its name implies, an embedded metric does not exist
outside the report object.
Embedded metrics are created when there exists a prompted filter in a
conditional metric and where the report is saved after answering those prompts.
The metric will have same definition as that of original metric but its ID will
be different than that of the original metric. Hence any changes made to the
original metric will not be reflected to the report.
21. What is MicroStrategy Intelligence Server?
MicroStrategy
Intelligence Server provides reporting and OLAP analysis for the whole
enterprise. All business users can obtain scorecards and dashboards,
operational reports, queries and OLAP and predictive analyses without learning
any programming or database syntax.
22. What are the benefits of using
MicroStrategy Intelligence Server?
MicroStrategy
Intelligence Server provides one centralzed architecture for all user
monitoring, reporting and analysis requirements. MicroStrategy Intelligence
Server also provides scalability to analyze any amount of data, support for any
number of users and a 24 X 7 operating environment, with robust security.
23. Does MicroStrategy Intelligence Server run
on Windows®, UNIX®, or Linux®?
MicroStrategy
Intelligence Server is certified on Windows, UNIX, and Linux operating systems.
MicroStrategy Intelligence Server has been designed to be a completely open
architecture built on industry standards and compiled to run on multiple
operating systems.
24. What is the Command Manager?
MicroStrategy
Command Manager lets you perform various administrative and application
development tasks by using text commands that can be saved as scripts.
Example: – server management, user management, security, database management.
25. What is a cache?
A cache contains the
properties and data of a report once a report has been run. Caches can be
stored in memory and/or on disk. When users ask for a report that is cached,
the Intelligence Server will retrieve the data from disk or memory instead of
running a query on the data source. Cache creation and usage securely leverages
other users work, increases query performance and reduces the workload on the
data warehouse.
26. What are Intelligent Cubes?
Intelligent
Cubes are in-memory caches stored by the Intelligence Server. While accessing
an Intelligent Cube, users can easily add or remove report objects (such as
attributes and metrics), add new metric calculations and filter their view of
the data -- all in an ad hoc fashion with speed-of-thought response times. Data
stored outside an Intelligent Cube is automatically accessed using the ROLAP
engine when drilling to more details.
27. How conflicts occur and what are the ways
to resolve them?
When
copying objects across projects with Object Manager, if an object with the same
ID as the source object exists anywhere in the destination project, a conflict
occurs.There are various ways to resolve depending upon the conditions like use
existing, replace, keep both, use newer, use older, update in same path, update
in new path and merge privileges.
28. What are report caches and how many types
are there?
A
report cache is a result set from an executed report that is stored on
MicroStrategy Intelligence Server.
There are 4 types: matching caches, history caches, matching-history caches and
xml caches.
29. What is the Conflict Resolution Window?
The
Conflict Resolution window provides the user with a means to decide how to
handle object conflicts between the source project and the destination
project.
In addition, the Conflict Resolution window displays the object name in the
original project, the object name in the destination project and the type of
conflict. Users may also specify a new name for the object depending on the
action chosen.
30. How does MicroStrategy Object Manager
determine if two projects are related?
MicroStrategy Object
Manager compares the Schema IDs of the two projects. Duplicated projects have
different Project IDs, but their Schema IDs are the same.
31. What is a joint child?
A joint child is Microstrategy way of handling Composite Keys.
Composite keys are constituted of two or more columns which together act as
unique identifier. To handle this case in Microstrategy we make this set of
columns, constituting composite keys, as joint child.
32. What is fact extension?
You can use level extensions to change a fact level, which is a
set of attributes that represent the lowest level of detail at which the fact
exists in the warehouse.
Level extensions define how facts can be extended, lowered, or disallowed to
other facts across the schema.
33. What is fact degradation?
When facts exist at a higher level than the report display level,
you must specify how the Engine degrades the data to the lower level. When you
lower the level at which a fact is reported, you are using degradation.
34. What are the types of facts in
MicroStrategy?
Simple facts:
A simple fact is made up of one or more fact expressions. With a simple fact
definition, you can define a fact as a column, constant, or simple expression.
Implicit facts:
An implicit fact is a virtual or constant fact that does not physically exist
in the database because it is created at the application level.
Derived facts:
A derived fact has its value determined by an expression that combines two or
more columns in a database to create a new column.
35. What are different types of metrics in
MicroStrategy?
• Simple: Simple metrics combine aggregate
operators with fact columns or attributes.
• Nested: Metrics that perform multiple aggregations by
placing one calculation formula inside another.
• Compound: A compound metric is a combination of expressions
that, through the use of functions, are themselves metrics.
• Derived
41. What is the difference in the way
Microstrategy Handles Custom Group and Consolidations?
Custom
Groups are handled at the database end where as Consolidations are handled at
the Analytical Engine end. As a result the Consolidations are not an overhead
for the database as there is a single pass in the query. On the other hand
Custom Groups are an overhead on the database as they fire a separate SQL pass
for every Custom group element.
42. What is a logical size of a table and what
does it depend on?
Logical
size is Microstrategy way of generating the best suitable/optimized SQL to
fetch the required data. Microstrategy follows an algorithm to calculate the
logical size of a table, which depends on the no of attributes and facts based
on the table and also the position of those attributes in the system hierarchy.
43. Why does MicroStrategy Object Manager
search for object dependencies?
MicroStrategy
Object Manager makes a list of all object dependencies before copying an object
to prevent metadata inconsistency. The time required for dependency checking
varies based on a customer metadata size and schema complexity. For large
metadata and complex schemas, gathering all the dependencies may take a long
time.
44. Can schema objects be copied across
projects with MicroStrategy Object Manager?
Yes,
schema objects can be copied across projects using MicroStrategy Object
Manager. MicroStrategy Object Manager moves objects seamlessly between similar
projects such as from a development project version to a production project
version where the warehouses are the same in terms of views, prefixes, and
warehouse structure. However, subtle changes in the warehouse that relate to
prefixes, views, or table structure cannot be tracked by MicroStrategy Object
Manager. For situations where the project warehouse structures or setups are
dissimilar, users may be required to make further edits of the objects to
ensure full integration into the destination project. These edits may include
hierarchical relationship changes or modifications to the prefixes.
45. While establishing relationship between
attributes how do you decide which attribute qualifies as child and which one
as parent?
While establishing the
relationship between attributes one can either look from business hierarchy
point of view and the attribute higher in the hierarchy becomes parent of the
attribute lower in the hierarchy. Parent and Child follow a one-to-many
relationship. Example Time hierarchy Year > Month > Date. Here Year would
be parent of Month and Date and Month parent of Date.
We can also identify Parent-Child relationship from database design point of
view. Here in a table the Primary Key uniquely identifies the other columns in
the table and hence qualifies as child of all the other attributes from the
table, in the same ways as a child in real world identifies his father (at
least the biological one).
46. What is heterogeneous mapping?
There
are no restrictions on the names for the columns used in the expressions of a
given attribute form. Heterogeneous mapping allows the engine to perform joins
on unlike column names. If the user defines more than one expression for a
given form, heterogeneous mapping will automatically take place when tables and
column names require it.
47. What is a conditional attribute in
MicroStrategy?
A
user may want to create an attribute with an alternating expression depending
on a certain condition, a conditional attribute. This condition may be
implemented through an ApplySimple.
48. What is a Implicit Attribute in
MicroStrategy?
An
implicit attribute is a virtual or constant attribute that does not physically exist
in the database because it is created at the application level. The implicit
attribute has its own expression.
49. Difference between project merge and
object manager in MicroStrategy?
Object
Manager can move just a few objects or just the objects in a few folders.
Project Merge moves all the objects in a project.
Object Manager must locate the dependents of the copied objects and then
determine their differences before performing the copy operation. Project Merge
does not do a dependency search, since all the objects in the project are to be
copied.
Project Merge can be run from the command prompt in Microsoft Windows.
50. How does MicroStrategy Object Manager
integrate with the MicroStrategy Product Suite security model?
Security in MicroStrategy
Object Manager is based on the MicroStrategy 7.x Product Suite security model.
All activities that can be performed in MicroStrategy Object Manager are
governed by privileges and access control lists. For example, if a user is not
allowed to access a certain folder in MicroStrategy Agent, they will not be
able to access the folder in MicroStrategy Object Manager.
51. How do we resolve
attribute roles in MicroStrategy?
By creating explicit table alias for the same or enabling the
Automatic Attribute Role recognition.
52. What are Logical Views used for in
MicroStrategy?
Logical Views allows application architects to create any desired
view using MicroStrategy, without DBA involvement. Once these Logical views are
created, they are available to the report designer in a way similar to any
other table. This allows developers to model attributes and facts whose
expressions span multiple tables.
53. Is it possible to use MicroStrategy Object
Manager while other users are making changes in MicroStrategy Agent?
Using MicroStrategy Object Manager to copy/move objects around is
not recommended while other user sessions are making changes using
MicroStrategy Agent, as it could lead to metadata inconsistency. Project and
schema locking prevent multiple users sessions from manipulating the schema at
the same time. This prevents metadata inconsistency from occurring.
54. What are the tracing options available in
MicroStrategy Object Manager?
Tracing is available under the Tools/Diagnostics menu. These
tracing options apply to every MicroStrategy product installed on the
machine.To see the SQL that has been executed against the metadata, go to the
Advanced tab and turn on "SQL Tracing" under the DSS MDServer
key.Function level tracing can be accomplished by going to the Advanced tab and
turning on "Function Level Tracing" under the DSS ObjectManager key.
55. What is report as filter in MicroStrategy?
In the MicroStrategy when the same filter conditions must be
applied to multiple passes, the same where clause appears in each of those
passes. This redundant where clause can be expensive if the filter conditions
are complicated and thus involve many tables and joins. Ideally, an
intermediate table populated with entries could be created to satisfy the
complicated filter conditions so that the rest of the SQL statements can use
that intermediate table. In that case, the where clause would be executed only
once instead of multiple times and SQL performance would be improved. In this
case to populate the tempor
56. What is the difference between Absolute
filtering and standard filtering in MicroStrategy?
When we use the absolute filtering in definition of level metric
whatever data we obtain from the filter is goingto be reported as such and the
the report filter will be overridden by the absolute filter settings. The
standard filtering the report filter interacts with the metric filter in the
normal way and what we obtain will be formatted according to the report filter
settings.
57. What is a Security filter in MicroStrategy?
Security filter is used to apply security at the database data
level.Whenever a users associated with security filter runs a report, a WHERE
clause is always included in the report sql with the condition defined in the
Security Filter.
58. What information is shared by the
application across MicroStrategy Web nodes?
MicroStrategy Web is designed to be as stateless as possible.
Therefore, no information is shared by the MicroStrategy Web application across
cluster nodes. All state information for running jobs is pushed to the client
browser.When a report is submitted by a MicroStrategy Web user, the user will
receive a wait page in the client browser.
This wait page will poll the MicroStrategy Web Server periodically for the
status of the report. This polling is performed as new http requests. This http
request will contain all state information, including encrypted login
information and MicroStrategy Intelligence Server connection information.
59. Roles in MSTR
Administrator: By default, the role/person will have full access
to the environment. In other words this role has full access to all the type of
objects mentioned above.
Architect: By default, access to configuration objects is restricted.
Developer: By default, no access to configuration objects, use access to schema
objects and full access to public objects.
60. What are Passthrough Functions?
Pass through functions are used to utilize various special
functions that specific to databases.Some of the passthrough functions
available are Applysimple and Applycomparision.
61. What is ScanMD and MD Doctor?
Scan MD is the tool to recover from logical inconsistencies where
MD Doctor fixes physical errors, When working with Microstrategy there is a
chance for the metadata to become corrupt. There are 2 types of errors;
Physical or Logical.When working with Microstrategy there is a chance for the
metadata to become corrupt. There are 2 types of errors; Physical or
Logical.ScanMD is used to recover from Logical discrepancies, where as MD
Doctor is used for Physical discrepancies.
62. Can the administrator easily maintain and
manage MicroStrategy Intelligence Server?
Yes. MicroStrategy Intelligence Servers centralized architecture
provides one console from which all maintenance and administration can be
performed. In addition, a standardized data dictionary for enterprise reporting
and OLAP analysis is stored in a metadata repository and enables reusable
reporting objects and business rules.
Interview Questions and Answers
What is metadata?
The database repository where
definitions of all MicroStrategy objects are stored. Metadata could be hosted
on most databases. In simple words, Metadata could be considered as the heart
of MicroStrategy environment.
MicroStrategy metadata contains datawarehouse connection information, project
settings and microstrategy object definitions.
What are the tasks that you can
perform with the MicroStrategy Architect?
Initially populate the metadata with
project definition and parameters, schema objects and create schema objects
What is difference between 2 ,3 , 4
tier connection?
In 2 tier architecture, the
MicroStrategy Desktop itself queries aganist the Data warehouse and the
Metadata with out the Intermediate tier of the Intelligence server.
The 3 Tier architecture comprises a
Intelligence server between MicroStrategy Desktop and the data Warehouse and
the Metadata.
The 4 tier architecture is same as 3
tier except it has a additional component of MicroStratey Web.
Intelligence Server is the
architectural foundation of the MicroStrategy platform. It serves as a central
point for the MicroStrategy metadata so you can manage thousands of end user
requests.
You are very limited in what you can
do with a 2-tier architecture. Things like clustering, mobile, distribution
services, report services, OLAP services, scheduling, governing, I cubes,
project administration are only available via Intelligence Server.
When a change is made in 2 tier – is
it reflected in 3 tier?
No it is not reflected. Following are
the methods by which we can reflect it in 3 tier.
· Update the schema in 2-tier. Restart the MicroStrategy
Intelligence Server. Recommended method by MSTR.
· Update the schema in 2-tier and reload the project from
the Project Monitor.
What is heterogeneous mapping?
There are no restrictions on the
names for the columns used in the expressions of a given attribute form.
Heterogeneous mapping allows the engine to perform joins on unlike column
names. If the user defines more than one expression for a given form,
heterogeneous mapping will automatically take place when tables and column
names require it.
For example, because different source
systems store Date information in various contexts, a data warehouse may have
multiple columns in different tables which all represent the concept of 'Date'.
The ID form of the attribute 'Date' may contain two expressions. The DATE_ID
column occurs in the LU_DATE table as well as in two other tables. The elements
in these columns correspond to elements in the ORDER_DATE column in the
order_detail and ORDER_FACT tables.
Heterogeneous Mapping of Attribute
Forms.
Why do we go for parent child
relationship?
While establishing the relationship
between attributes one can either look from business hierarchy point of
view and the attribute higher in the hierarchy becomes parent of the attribute
lower in the hierarchy. Parent and Child follow a one-to-many relationship.
Example Time hierarchy Year > Month > Date. Here Year would be parent of
Month and Date and Month parent of Date.
We can also identify Parent-Child relationship from database design
point of view. Here in a table the Primary Key uniquely identifies the
other columns in the table and hence qualifies as child of all the other
attributes from the table, in the same ways as a child in real world identifies
his father (at least the biological one).
What is a compound attribute?
A compound attribute has its value
determined by an expression which combines two or more columns in a database to
create a new column
What are different kinds of objects
in Microstrategy?
Configuration Objects:
Configuration objects are MicroStrategy objects which can be re used in
multiple projects and they appear in the system layer. Ex: Database
Instances, Users, Login ID's, Schedules
Schema Objects:
The building block of BI. Schema objects are directly mapped to a column or
columns in the database. Attributes, Facts, Functions & Operators,
Hierarchies, Partition Mappings, Tables & Transformations
Public Objects:
Objects that generate analytical data and are built on other schema objects or
public objects. Also called as application objects. Ex: Consolidation, Custom
Groups, Drill Maps, Reports, Documents, Filters, Prompts, Metrics, Templates
and Searches
How to create a conditional attribute
in MicroStrategy Desktop
A user may want to create an attribute with an alternating
expression depending on a certain condition, a conditional attribute. This
condition may be implemented through an ApplySimple statement such as the
following:
Types of Attributes
Simple
A simple attribute is made up of one or more expressions.
With a simple attribute definition, you can define an attribute as a column,
constant, or simple expression.
Implicit Attributes
An implicit attribute is a virtual or constant attribute
that does not physically exist in the database because it is created at the
application level. The implicit attribute has its own expression.
Derived Attributes
A derived attribute has its value determined by an
expression which combines two or more columns in a database to create a new
column.
Compound Key Attribute
A compound key attribute is an attribute whose primary key
is made up by the combination of two or more columns.
What is a Implicit Attribute?
An implicit attribute is a virtual or
constant attribute that does not physically exist in the database because it is
created at the application level. The implicit attribute has its own
expression.
What is a joint child?
A joint child is Microstrategy way of
handling Composite Keys. Composite keys are constituted of two or more columns
which together act as unique identifier. To handle this case in Microstrategy
we make this set of columns, constituting composite keys, as joint child.
What are attribute roles?
A user defines two attributes that have the same definition but play
different roles in the business model. In this example, attribute Origin
Airport and Destination Airport are defined using the same Lookup Table and
Column (Airport_ID). Both attributes share the same forms, or information about
them (Description, Location, etc.). In the fact table, however, a separate
column exists for each of their roles (Origin_Airport_ID and
Destination_Airport_ID).
When should attribute relationships
be modeled as separate attributes in a parent-child relationship and when
should they be modeled as forms of the same attribute?
It is preferable to use
separate attributes that are related hierarchically (that is, parent-child
relationships) for the following reasons:
Attributes that exist in a
hierarchical relationship can appear independently of each other on a report.
If 'Item' and 'Item Category' are modeled as separate attributes, reports may
then be designed to report on individual items or whole categories. If 'Item
Category' is considered a description (form) of 'Item', it becomes impossible
to report on 'Item Category'.
Attribute forms are not available as
metric dimensionality settings. In order to aggregate data at a particular
attribute level, that attribute must exist as an attribute. If the attribute is
modeled as an attribute form instead, it is possible to aggregate only at the
level of the attribute containing the form.
Attribute forms are not appropriate
under the following circumstances:
· When the attribute must be used as an aggregation level
(metric dimensionality). For example, customer and state: if a user wishes to
calculate sales totals by the states in which customers live, state should be a
separate attribute as a parent (or grandparent, and so on) of customer.
· When the attributes exist in a one-to-many or many-to-many
relationship. For example, customer status: Presumably, each status will apply
to several customers. Modeling status as a form of customer makes it always
subordinate to customer, which may impose unnecessary limits on reporting
options.
How are the drilling options for an
attribute decided?
Based on relation between attributes,
hierarchies and their drilling configuration
What are the two types of
Hierarchies?
System hierarchy: It contains all the
project attributes and its available browse paths and is based on relation
between attributes.
User defined Hierarchy: Custom
grouping of attributes and define their browse paths.
Facts
What is fact extension?
• You can use level extensions to change a fact level, which
is a set of attributes that represent the lowest level of detail at which the
fact exists in the warehouse.
• Level extensions define how facts can be extended,
lowered, or disallowed to other facts across the schema.
What is fact degradation?
When facts exist at a higher level
than the report display level, you must specify how the Engine degrades the
data to the lower level. When you lower the level at which a fact is reported,
you are using degradation.
Types of facts
Simple facts
A simple fact is made up of one or
more fact expressions. With a simple fact definition, you can define a fact as
a column, constant, or simple expression.
Implicit facts
An implicit fact is a virtual or constant fact that does
not physically exist in the database because it is created at the application
level.
Derived facts
A derived fact has its value determined by an expression
that combines two or more columns in a database to create a new column.
Metrics
What are different types of metrics?
• Simple : Simple metrics combine aggregate operators with
fact columns or attributes.
• Nested: Metrics that perform multiple aggregations by
placing one calculation formula inside another
• Compound : A compound metric is a combination of
expressions that, through the use of functions, are themselves metrics.
• Derived
What is Base Formula?
Use a simple expression as a base formula to
facilitate the creation of more complex metrics.
What is smart metrics?
Compound metrics are the ones that are derived by some
specific expression involving the different simple metrics. Eg, Total(
profit/units Sold). Smart metrics is when the compound metric is calculated
with the help of subtotal calculations for every element inside the compound
metric. For the above example the smart metric computation can be
Total(profit)/Total(Sold).
What is level metric?
Level metrics are advanced metrics which are set to be
evaluated at a specified attribute level. These are required when in the same
report you need to roll up a metric at two different levels side by side.
Example is comparison of “Revenue from a Region” to “Revenue from a
Country”. Here Region and Country are the two different levels.
The level of a metric, also referred to as dimensionality,
allows you to determine the attribute level at which the metric is calculated.
Default – Report Level
The elements needed to specify a level for a
metric
Target - Attribute
level at which the metric
Grouping - Determines the metric aggregation.
Filtering - governs the
relationship between the
report filter and the calculation of
the metric.
What is purpose of having
conditionality in metrics?
Conditionality associates a filter to the metric calculation.
This is an optional component.
What are the different
components of metrics?
• The formula defines the data to be used and the
calculations to be performed on the data. The outermost formula must be a group
function.
• The level, or dimensionality, determines the level at
which to perform the metric calculation. For example, you can choose to
calculate at the month level or year level.
• Conditionality associates a filter to the metric
calculation. This is an optional component.
• The transformation applies offset values, such as “four
months ago,” to the selected attributes. This is also an optional component.
What is the purpose of
transformation in Metrics? Types of transformation.
It encapsulates a business rule used to compare results of
different time periods.Transformations are used in the definition of a metric
to alter the behavior of the metrics.
• Expression - based transformations – You
implement these transformations using a mathematical
formula in Microstrategy Architect.
• Table - based transformations
– These transformations are based on a
transformation – or relate –table in the warehouse.
What is dynamic Aggregation?
Rollup metric values that occurs when an attribute is
moved from the report grid to the report objects.
For Eg: The report grid has Quarter and Customer City,
Revenue. If we remove Quarter into the report objects then revenue should
automatically roll up to Customer City.
How to ensure that a particular fact
table will be chosen for a metric
The MSTR operates in such a way that the incoming queries
and data retrievals are done from the table which has the least logical size.
Thus if we prefer a specific table to be the center of activity then we should
try to reduce the logical size of that specific table so that it becomes
considerably lesser than the other tables.
In Microstrategy, how can you direct the sql generated to use a
specifc table?
Using the Level parameter in the
Metric level options
Assuming you have OLAP licence,the easiest way to direct to a particular table
is to create a dummy fact on the table,include the fact in metric and put the
metric in report objects.
How to hide a particular metric in a
report for a specific user?
Using Object level security
What is Metric Formula Join Type? How
it is different with Metric Join Type?
Metric Formula Join Type is used for Compound Metrics and
determines how the different tables used in metric formula are joined.
Whereas the Metric Join Type determines how the metrics
are joined to other metrics.
Filters
What is filter?
Filter is used to restrict data in a report
What is report as filter?
In the MicroStrategy when the same filter conditions must
be applied to multiple passes, the same where clause appears in each of those
passes. This redundant where clause can be expensive if the filter conditions
are complicated and thus involve many tables and joins. Ideally, an
intermediate table populated with entries could be created to satisfy the
complicated filter conditions so that the rest of the SQL statements can use
that intermediate table. In that case, the where clause would be executed only
once instead of multiple times and SQL performance would be improved. In this
case to populate the temporary table we can use report as a filter
What is view filter?
View Filters: View Filters
are the conditions that come into play before a specific result is presented to
the user. Thus the view filters are not part of the SQL statements like report
definitions, rather they are the filters applied after the execution of the SQL
statements, after the data is retrieved from the data warehouse.
What is filtered prompt?
We can restrict the number of elements in a prompt using a
filter.
Difference between report and view
filter?
Report filters: report
filters are the conditions that accompany the report generating SQL statements.
The report definitions have the filters as part of their definition.
View Filters: View Filters
are the conditions that come into play before a specific result is presented to
the user. Thus the view filters are not part of the SQL statements like report
definitions, rather they are the filters applied after the execution of the SQL
statements, after the data is retrieved from the data warehouse.
Difference between Report Limit and
Report filter?
A report limit specifies a set of criteria used to
restrict the data returned in the report data set after the report metrics are
calculated.
Report Filter applies the where condition to the query
sent to warehouse to extract the results.
What is the difference between
Absolute filtering and standard filtering?
When we use the absolute filtering in definition of level
metric whatever data we obtain from the filter is goingto be reported as
such and the the report filter will be overridden by the absolute filter
settings. The standard filtering the report filter interacts with the metric
filter in the normal way and what we obtain will be formatted according to the report
filter settings.
What is a Joint Element List Filter?
Helps to choose combination of attribute
elements from different attributes to filter a report
Enables to create filters with attribute
pairs as well as triplets, quadruplets and so on.
This is available in the advanced
qualification section of the filters
What is a Security filter?
Security filter is used to apply security at the database
data level.Whenever a users associated with security filter runs a report, a
WHERE clause is always included in the report sql with the condition defined in
the Security Filter.
What is a Attribute to Attribute Filtering?
This is used to compare values of 2 attributes using their
forms.
Eg: Ship Date<Day(ID)+2
Prompts
What is prompt?
• Used to dynamically modify the contents of a report
• Enormous flexibility for designing reports
• One report can satisfy multiple reporting
requirements with prompts
• Allow users to select criteria for reports at
run-time
Different types of prompts?
1. Object: Allow users to select objects comprising a report
at run-time
Users can select from any objects to dynamically
build a report
2. Level: Level prompts enable you to specify the level of
aggregation calculation of a metric
3. Value: Allow users to type a value
Used in conjunction with other prompts for filters
or metrics
4. Filter Defintion Prompt
a. Choose from all attributes in a hierarchy
b. Qualify on an attribute
c. Choose from an attribute element list
d. Qualify on a metric
What is object prompt?
An object prompt allows you to select which MicroStrategy
objects to include in a report, such as attributes, metrics, custom groups and
so on. Object prompts can either determine the definition of the report
template or the report filter.
What is level prompt?
Level prompts enable you to specify the level of aggregation
calculation of a metric
Can we use hierarchy in prompt?
Yes we can use.
Report
What is the purpose of having
thresholds in report?
Used to create conditional formatting for metric values.
Two metric and one attribute – How
will the SQL look if the metrics are from same tables and are from different
tables?
If the two metrics are sourced from the same table then
only one pass will come.
If they are sourced from different fact tables then a pass
will be generated for each metric and in the final pass the data will be
aggregated,
Difference between drill map and
drill path. What is drill to template
Drill maps allow you to create fully customized
drill paths that are available to your users while drilling on a
report. By default, the paths available are based on the system Hierarchy of
the project. You can create custom drill maps that can override these
defaults.
Difference between Consolidation and
Custom Groups. How will they affect SQL ? Which is more expensive operation.
Custom Groups are handled at the database end where as
Consolidations are handled at the Analytical Engine end. As a result the
Consolidations are not an overhead for the database as there is a single pass
in the query. On the other hand Custom Groups are an overhead on the database
as they fire a separate SQL pass for every Custom group element.
A custom group is a set of special filters that can be
placed on a template. It is made up of an ordered collection of elements called
custom group elements. Consolidations are used to specify the data you want to
view in your report. They allow you to group attribute elements in new ways
without changing the metadata and warehouse definitions.
Difference between page by and
outline mode?
Page by enables to select and display subsets of report
results as separate pages.
Outline mode enables indented grouping of related
attributes This is useful when we want to display the same report at
different levels quickly.
How can we enable caching in reports?
Different types by which we can implement caching?
Caching can be enabled/disabled at project or report
level. However the report level setting will override the project level
setting.
Using Project Configuration we can enable the caching else
in report – using caching options we can enable or disable the caching.
Templates can also be used for caching. Using a common
template for developing common reports will help to fetch report data
faster.
Can Threshold be applied on
attributes? What are the different formatting types?
1. Font Type, Color
2. Background
3. Image
4. Text
What are adhoc reports and static reports?
Adhoc reports run in real time based on the input
parameters provided by the user at the run time.In Microstrategy, adhoc reports
are created using Prompts.
In static reports, users won't be provide any input
parameters.These reports are usaully schedule to run overnight and ready to
view immediatley in the mornings using cache.
Tables
How to handle table structure change
in DB to be reflected in MSTR?
Update the warehouse catalog.
Modify the data type in attribute editor.
When a column name changes in a table
how do we handle it in MSTR
1. Remove the association of the column from the respective
attributes.
2. Go to warehouse catalog and update the structure
3. Update the schema
4. Associate the attributes to the new column name. Save
5. Update the schema
How to we add tables into a schema
1. Warehouse catalog
2. Architect
When you add a physical table from the datawarehouse
into the project, MSTR creates a corresponding logical table in the metadata.
Physical table stores the actual data whereas the logical data stores
information about their corresponding physical tables including column names,
data type and schema objects associated with the column names.
There are two views - the physical view and the
logical view in the table editor. The logical view shows the attributes
and facts mapped whereas the physical view shows the columns and corresponding
data types.
What is a logical size of a table and
what does it depend on?
Logical size is Microstrategy way of generating the best suitable/optimized SQL
to fetch the required data. Microstrategy follows an algorithm to calculate the
logical size of a table, which depends on the no of attributes and facts based
on the table and also the position of those attributes in the system hierarchy.
What are Logical Views used for?
Logical Views allows application architects to create any desired view using
MicroStrategy, without DBA involvement. Once these Logical views are created,
they are available to the report designer in a way similar to any other table.
This allows developers to model attributes and facts whose expressions span
multiple tables
Other Questions
What is the data modelling tool in
MSTR called?
Architect
How do we migrate objects across
projects?
Object Manager
What is the difference between object
manager and project merge?
·
Object
Manager can move just a few objects or just the objects in a few folders.
Project Merge moves all the objects in a project.
·
Object
Manager must locate the dependents of the copied objects and then determine
their differences before performing the copy operation. Project Merge does not
do a dependency search, since all the objects in the project are to be copied.
·
Project
Merge can be run from the command prompt in Microsoft Windows.
What is a history list?
The portion of the interface that allows users to retrieve
the results of previously executed or scheduled reports.
What is difference between purging
and deleting cache?
Caching allows for improved performance in response to
report queries. Although the use of caching is an advantage, there are
instances when caches may expire or become invalid. Some of these instances
are:
When there are changes made to the objects in the data
warehouse, the existing caches may be configured so that they are no longer
valid when hitting certain warehouse tables. Any further report execution will
no longer hit the cache.
When the definition of an application object changes (such
as a report definition, report, template, metric definition, etc.), the related
report cache is marked as invalid.
When there is a need to control the growth of caches on
the Microstrategy Intelligence Server, old caches may need to be expired
automatically.
Invalidating Report Caches
Invalidation is a preventive measure that renders a cache
unusable by nullifying it. Cache invalidation only applies to Matching caches
and Matching-History caches. It makes the cache ineligible in the matching
process so it is not used to fulfill a report request.
In the case of a Matching cache, invalidating it will automatically
result in deleting it.
In the case of a Matching-History cache, invalidation
simply converts it to a History cache that is not used in the matching process
any more but is still accessible through History List messages that reference
it. However, if all these messages are deleted, the converted History cache
that is referenced is also deleted.
Expiring Report Caches
Cache expiration is a process that renders a cache
unusable by terminating its useful life. It yields the same results as invalidating
a cache and applies to Matching caches and Matching-History caches. Cache
expiration occurs automatically as per the 'Report cache duration' setting
which can be found under Project Configuration > Reports >Advanced >
Caching.
When a cache is updated, the current cache lifetime is
used to determine the cache expiration date based on the last update time of
the cache. This means that changing the Report cache duration setting does not
affect the expiration date of the already existing caches. It only affects the
new caches that are being or will be processed.
Deleting Report Caches
Cache deletion is a process that deletes the cache from
memory as well as disk. Report caches are automatically deleted by
MicroStrategy Intelligence Server if cache invalidation and History Lists are
performed and maintained properly.
In the case of a History cache, deleting it does not
automatically delete the associated History List messages that reference it.
After deleting a History cache, when a message that references it is retrieved,
the following occurs:
MicroStrategy Web users see an error message,
"Execution results not available. Would you like to re-execute?"
MicroStrategy Desktop users do not see the above error
message because MicroStrategy Desktop automatically resubmits the report for
execution.
On the other hand, a History cache is automatically
deleted when all the History List messages that reference it are deleted (when
its History List reference count reaches 0).
In the case of a Matching-History cache, when all the
History List messages that reference it are deleted, it is simply converted to
a Matching cache, while losing its History component.
Caches can be deleted:
Manually - via Cache Monitor and MicroStrategy Command
Manager
Scheduled - via MicroStrategy Cache Administration Utility
Scheduled - via MicroStrategy Desktop Scheduled
Administrative Task.
Purging Report Caches
Cache Purging is a process whereby all report caches can
be deleted in bulk, even the one references by History List messages.
How to apply row level security?
Applying Security filters to the user. This provides row
level security
When do we go for schema update?
When any of the schema objects are modified –
Attributes/Facts/ Transformations/Tables/Hierarchies/Partitions
When New Tables are added to the schema using warehouse
catalog.
When you update the structure of existing tables in the
warehouse catalog.
What are VLDB properties?
VLDB stands for Verly Large Data Base Properties. This is
Microstartegy way of handling database specific preferences while generating
the report SQL. There are number of them. A few common one are for Attribute or
Metric join types, cross join check, type of intermediate table, etc.
At which levels you can set the VLDB
properties? Which level has the highest pecedence?
VLDB Properties can be set at various levels like Report,
Template, Metric, Project, Database Instance and DBMS level. Out of this Report
level has the highest priority. It overrides all other levels.
What are the various ways of
incorporating security in Microstrategy?
In Microstrategy security can be
incorporated using a mix of any of the following ways:
·
Putting
user specific restrictions at the database end and using user specific
connection mapping. This is for column level security.
·
Applying folder and object level
security to restrict access to certain set of reports/objects
·
Applying Security filters
to the user. This provides row level security.
Object
Level
Folder Level
Row Level
How conflicts occur and what the ways
to resolve them?
When copying objects across projects with Object Manager, if an object with the
same ID as the source object exists anywhere in the destination project, a
conflict occurs.There are various ways to resolve depending upon the conditions
like use existing, replace, keep both, use newer, use older, update in same
path, update in new path and merge privileges.
What is
the command manager used for?
The command manger is the one through which you can manage
the applications, user accesses, security and databases of the microstrategy.
The command manager allows us to save the text commands that can be executed as
scripts. Thus it can help in automation of the entire management process.
What are the nulls on microstrategy
report and how to mask them.
1)If in this question we want to deal with null in MSTR
report than we have some display property in report data option which we can
change according to the replacement of null values.
DATA>>REPORT DATA OPTION>>DISPLAY>>NULL
VALUES
2)if we want to deal with sql geration of report than
there is an option of VLDB property according to you requirment you can deal
with sql query of report
Servers in MSTR
I-server: MicroStrategy
Intelligent server provides jobs management and analytical processing for all
MicroStrategy applications. This acts as a central component connecting the
metadata, warehouse, desktop, Web server and Narrow cast Server. Few or main
features: Reports Services, OLAP Services, Data Mining, Multi Source
connection, Caching, Clustering. Latest version supports installing I-server
(different packages) on Windows, Sun Solaris, IBM AIX, HP-UX, Linux. Contact
MicroStrategy for more information.
Web Server: MicroStrategy web server responds to the requests from
browsers. Web server interacts with the I-server to extract the necessary
information. Can be installed on most of the major web servers and supports
most popular browsers. Contact MicroStrategy for certified products.
Narrow cast Server: Narrowcast sever delivers personalized business insight to
emails, cell phones, pagers, file servers and print servers extending the reach
of Business Intelligence applications. It offers a comprehensive solution for
information delivery integrating a subscription portal with a delivery engine.
Roles in MSTR
· Administrator:
By default, the role/person will have full access to the environment. In other
words this role has full access to all the type of objects mentioned above.
· Architect:
By default, access to configuration objects is restricted.
· Developer:
By default, no access to configuration objects, use access to schema objects
and full access to public objects.
What are Passthrough Functions?
Pass through functions are used to utilize various special functions that
specific to databases.Some of the passthrough functions available are
Applysimple and Applycomparision.
What is the difference between
warehouse and metadata partition mapping in MicroStrategy Desktop
Warehouse Partition Mapping:
Warehouse Partition Mapping tables
are used for performance reason.
For example, a fact table in the data
warehouse may have two columns: Sales and Years. If a report is run with Sales
for the Year = 1999, the query engine will need to search through the entire
table for all the years, including 1999, to return the data.
To improve efficiency, Partition Base
Tables (PBT) can be created to have Sales for particular years. Assuming that
there are 10 years worth of data in the database, 10 different partition base
tables need to be created:
- PBT1: Sales for 1991 (Year=1991,
Sales=$)
- PBT2: Sales for 1992 (Year=1992, Sales=$)
...
- PBT10: Sales for 2000
A Warehouse
Partition Mapping Table (PMT) will then need to be created and will have:
|
Year
|
PBTName
|
|
1991
|
PBT1
|
|
1992
|
PBT2
|
|
...
|
|
|
2000
|
PBT10
|
The above PMT is going to tell the engine which table to
grab the data from for a particular year. This means that when the report is
run for Sales for 1997, then the query engine will first go to the Partition
Mapping Table and then find the correct PBT corresponding to the year 1997.
Metadata Partition Mapping:
Metadata Partition Mapping Tables map the attribute
elements to its corresponding PBT and performs the same function as the PMT.
To create a Metadata partitioning, follow the steps below:
1.
Go to Schema Objects > Partition,
in MicroStrategy Desktop 8.x.
2.
Right-click and select new >
Partition.
3.
Select the PBT. (i.e., PBT1)
4.
A data slice can be defined. The data
slice will be the attribute (i.e., Year) to join from this metadata PMT to the
PBT.
5.
Save and close.
By
doing this, the equivalent of a PMT in the warehouse has been created, but in
the metadata. In theory, both the warehouse and metadata partition mapping
tables are accomplishing the same task
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
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
Report Service Document
What is a document?
A
document displays your organization’s data in a format that is similar to a
PowerPoint presentation, where several grid and graph reports can be viewed at
the same time, along with images and text. High-quality, Pixel Perfect™
documents allow you to display your business data in a user-friendly way that
is suitable for presentation to management for boardroom-quality material.
Examples of documents include scorecards and dashboards, managed metrics
documents, production and operational documents, and more
What are the different export
formats?
Excel, PDF, Flash, HTML
What are the different view modes?
Flash View
HTML View
Design View
What is a dataset?
A
dataset is a MicroStrategy report that defines the data that the Intelligence
Server should retrieve from your data warehouse or from a cache that is
available to the document.
What is a hyperlink?
A
hyperlink connects text or an image in a document to a web page (the target of
the hyperlink). When the document is open in PDF View, as shown below, the
cursor changes to a hand when you hover the cursor over
text that contains a hyperlink.
What is grouping of data in document?
If the
data is grouped by page, drop-down lists are displayed at the top of the
screen,
What is a layout?
A
multi-layout document contains multiple documents, each in its own layout,
creating a “book” of documents. Each layout functions as a separate document,
with its own grouping, page setup, and so on, but the layouts are generated
into a single PDF document. If a document contains multiple layouts, tabs are
displayed at the top of the screen.
What is a dashboard?
A
dashboard is commonly only one page long, is intended to be viewed online, and usually
provides interactive features that let analysts change how they view the
dashboard’s data. By being only one page long, a dashboard makes it easy to
view the whole document at one time and see all the information. A dashboard
allows interactivity from users, so each user can change how they see the data,
within the limits of what the dashboard allows them. You must view a dashboard
in Flash View in MicroStrategy Web to be able to interact with its widgets,
selectors, and panel stacks
Difference between Dashboard and Scorecard - Click Here
What are the different display modes?
View
Mode:
view the results
Cannot
create a new document.
•Cannot
edit an existing document.
•Cannot
manipulate any objects on the document, as you can in any of the other display
modes.
Interactive
Mode
Edit an
existing document.
•View
the results of the document.
•Use selectors
to flip through the panels in a panel stack or display different attribute
elements or metrics in a grid or graph report displayed on the document.
•Format
grid and graph reports.
•Sort
grid reports and pivot report objects on them.
•Add
totals.
•Resize
rows and columns.
•Create
metrics based on report objects already on the grid report.
•Optimized
for dashboard viewing.
Cannot
create a new document.
•Cannot
format the layout and positioning of objects or the entire document.
•Cannot
format the Flash properties of widgets.
Flash
Mode
Access
and interact with features provided by Flash, such as widgets. Widgets are
interactive Flash-only graphs, such as gauges and time series slides, that
dynamically update when you select a new set of data.
•Format
widget Flash properties.
•Edit
an existing document.
•View
the results of the document.
•Use
selectors to flip through the panels in a panel stack or display different
attribute elements or metrics in a grid or graph report displayed on the
report.
•Sort grid
reports and pivot report objects on them
Cannot
create a new document.
•Cannot
manipulate or format grid or graph reports, except to sort and pivot objects on
them.
•If a
graph report uses a graph type that is not supported in Flash, the graph is not
displayed
What are controls?
Controls
are the objects that display the data, images, and shapes in a document; they
are the objects shown in the document’s Layout area as you design the document.
1. Text Fields
2. HTML container
3. Line or Rectangle
4. Image
5. Grid/Graph
6. Panel stack, which is a holder for a
collection of panels, or layers of data, in a document. A user can navigate or
flip through the panels in a panel stack; only one panel is displayed at a
time.
7. Selector, which allows users to
interact with the document, by flipping through the panels in a panel stack or
by displaying different attributes or metrics in a Grid/Graph
8. Widget, which displays the results of
a dataset report in Flash in MicroStrategy Web, allowing users to visualize
data in different ways than traditional reports displayed as Grid/Graphs do
In documents, Information about the document (such as page
numbers) and the dataset reports (such as report names and filter information),
what is it called? –
Auto
Text Codes
What are the different sections in a
document?
1. Page Header and Footer
2. Document Header and Footer
3. Detail Header and Footer
What are the different types of text field in a document?
Static
text: This
text does not change and is commonly used for labels or descriptions. Examples
in the sample document are the words “Employee” and “Revenue”. For directions
to add static text, see Adding static text to a document
•Dynamic
text: This text is automatically populated by the document or dataset.
Dynamic text is always included within braces { }.
There
are two types of dynamic text:
Data
field
Auto
text code
Eg:
Date/time: {&DATETIME}
What are the different types of metrics that can be created
within a document?
Calculated
expressions: A calculated expression is a metric that is calculated
dynamically, when the document is executed, directly from metrics on a document
dataset Eg: Revenue - Cost
Derived
metrics: A derived metric is a metric that is
obtained dynamically, when a document is executed, directly from metrics on a
document dataset. A derived metric is created using at least one of the metrics
in the document
Summary
metrics.
What are the issues faced when
exporting reports/documents to excel?
Choose Excel-compatible colors for
objects as well as grid and graph formatting. MicroStrategy Desktop's basic set
of 40 colors matches the Excel colors. Color from the Advanced Color
Picker will be matched by Microsoft Excel with more or less accuracy.
Use graphs that are supported by
Microsoft Excel. Examples of non-supported graphs are gauge graphs and
combination graphs.
Lines and rectangles are not
supported when exporting to Microsoft Excel. As a workaround, use a text field
border to create a line or a rectangle.
A line graph within a Report Services
document changes color when exported to excel. [Fixed in 9.3]
When exporting Report Services
documents with objects that are overlapped, unexpected behavior is seen as
Microsoft Excel does not support this feature and priority might be given
to one of the overlapped objects.
Word-wrapping specified for
multi-word object names does not take effect in PDF or Excel.
Custom line in a graph report is not
carried over when exporting to Microsoft Excel as a Live Chart [Not sure if its
fixed now]
The secondary axis on a dual axis
graph report is not displayed when exporting the report to Excel format with
the option "Export graphs as live Excel charts".Hence don't
export a graph report as live chart in Excel format when exporting it.
Banding does not display correctly
when exporting reports to Excel 2007 and above from MicroStrategy 9.2.1 Desktop
and Web. Change the excel options such that you can export to older versions.
Prompt details are not fully
displayed when exporting to Microsoft Excel 2007 from MicroStrategy Web
9.2.1.Change the excel options such that you can export to older versions.
Metric values will still appear
despite column width set to 0 when exported to Excel 2007 in MicroStrategy
version 9.2.1. Use excel 2003 to workaround.
Stacked Bar graph becomes Clustered
Bar graph when exporting a Report Services document to Excel in MicroStrategy
Web 9.2.1.
A Report Services document that
includes attributes which use the date or datetime datatype. When the document
is exported to Microsoft Excel, the attributes are recognized as plain text
instead of dates, thereby inhibiting the use of certain pivot functions in
Microsoft Excel native to the date format.
