MicroStrategy Interview Questions & Answers
MicroStrategy Interview Questions and Answers
Dear Readers, Welcome to MicroStrategy Interview Questions have been designed specially to get you acquainted with the nature of questions you may encounter during your Job interview for the subject of MicroStrategy. These MicroStrategy Questions are very important for campus placement test and job interviews. As per my experience good interviewers hardly plan to ask any particular questions during your Job interview and these model questions are asked in the online technical test and interview of many IT companies.
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)).
8. What is Pass through Metrics in Microstrategy?
Metric created using pass through functions (example: ApplySimple). Pass through functions are executed at the database level.
In all pass through functions we use parameters to address objects. Parameters should start with "#0". For example, if two parameters are to be defined, they should be "#0" & "#1". All the parameters used should be defined at the end of the function.
Pass through functions are functions applied on the database at the time of report execution. Pass through functions can be used in metrics, filters and attributes. They are applied in the expression pane of the objects.
9. What is Rank Metrics in Microstrategy?
Rank shortcut metrics apply a ranking number to the metric values for a given attribute. When you create a rank shortcut metric, you can determine break-by options for each attribute on the report. Use the rank function to show the relative position of a given value in relation to other values on a report.
Rank shortcut metrics calculate subtotals and dynamic aggregation for functions that have a default dynamic aggregation (such as sum or minimum). If the shortcut metric is based on an attribute, it also calculates correctly for functions that do not have a default dynamic aggregation function (such as average and count distinct).
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.
36. What is purpose of having conditionality in metrics in MicroStrategy?
Conditionality is an optional component.
It associates a filter to the metric calculation.
37. What is dynamic Aggregation in MicroStrategy?
Rollup metric values that occurs when an attribute is moved from the report grid to the report objects.
38. What are VLDB properties in MicroStrategy?
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.
39. 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.
40. What are pass-through functions and why they are so called? Give some examples.
Pass-through functions are Microstrategy way of generating database specific SQL construct which otherwise are not possible. These are called pass-through functions because Microstrategy does not check the actual SQL construct and dumps it as is on the database. Example include ApplySimple, ApplyComparison, etc.
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 temporary table we can use report as a filter.
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 Micr
ostrategy 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.