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?


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.

26 comments:

  1. Very helpful information, thank you very much.

    ReplyDelete
  2. Awesome really helpful.. Thank you!!

    ReplyDelete
  3. Thanks a lot for the Good Post..... Keep Rocking!!!

    ReplyDelete
  4. very helpful one.. Thank you so much.

    ReplyDelete
  5. This is much helpful.. Thanks very much

    ReplyDelete
  6. Very Much Informative... (L)

    Dhinesh

    ReplyDelete
  7. much more questions on
    http://www.aptibook.com/Technical/Micro-strategy-interview-questions-and-answers

    very useful...
    all the best

    ReplyDelete
  8. it is very use full........thankyou very much

    purush

    ReplyDelete
  9. This was Great..Thanks!!!

    ReplyDelete
  10. Thank you so... Much...!!!! These question and answers are very helpful in cracking the interviews.

    ReplyDelete
  11. Awesome.. It is really helpful for peoples who are preparing for interviews.

    ReplyDelete
  12. Thnks,simple and super

    ReplyDelete
  13. Quiet Good one to refresh basics.. Kudos to Poornima sundaram

    ReplyDelete
  14. Wonder full collection of questions and is a Good handy to refer. Thanks.

    ReplyDelete
  15. Thank you, it is very useful !!

    ReplyDelete
  16. very useful collection....

    ReplyDelete
  17. Really its simpy superb for new and who are preparing interviews.

    ReplyDelete
  18. Thanks a lot,
    it's a very useful document...

    ReplyDelete
  19. Excellent.....keep updating more questions...Thank You

    ReplyDelete
  20. I just loved it...thanq so much..its marvellous..u r just through the interview if you will have a glance at all these..

    ReplyDelete
  21. very very helpfull for microstrategy users and interview also

    ReplyDelete