1. How many types of license?
Two types: named users or cpu license.
2.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.
3. What are VLDB properties?
Answer
VLDB stands for Very 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.
4. What is the Command Manager?
Answer
MicroStrategy Command Manager lets you perform various administrative and application development tasks by using text commands that can be saved as scripts. Like for example: Ц server management, user management, security, database management.
5. What is an object prompt?
Answer
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.
6. What are Logical Views used for?
Answer
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.
7. How will Project Sources be configured in MSTR Office?
By editing the projectsources.xml, new project sources can be added.
8. Difference between standard, absolute and ignore condition in metrics?
Answer
Standard filtering allows the report filter to affect the metric. Absolute filtering raises the report filter to the level of the target, so all subcategories in the categories included on the report are added together. Ignore filtering disregards filtering criteria based on the attribute in the target and its related attributes (both parents and children).
9. Comparison between Consolidations and Custom Groups?
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.
11. What is the order of precedence of VLDB properties?
Reports>Project>Database Instance>DBMS
12. 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.
13. What is Metric Formula Join Type? How it is different with Metric Join Type?
This is basically 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.
14. 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).
15. How do we resolve attribute roles?
By creating explicit table alias for the same or enabling the Automatic Attribute Role recognition.
16. What are statistics tables and how do you configure the project statistics?
Statistics tables contain data on the MicroStrategy systemТs usage and performance, and are populated by all projects that are configured to log statistics.
To configure:-
1. Set up the Enterprise Manager.
2. Define the statistics and enterprise warehouse database.
3. Create the statistics database instance
4. Go the project configuration wizard of the project
5. Select statistics and define the what you want to log
17. Difference between project merge and object manager?
1. 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.
2. 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.
3. Project Merge can be run from the command prompt in Microsoft Windows.
18. 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.
19. 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. 4 types: matching caches, history caches, matching-history caches and xml caches.
20. 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).
21. 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. The logical size does not depend on the actual data (no of rows) in the database. If the candidate answers this than his concept is wrong
22. What are Level Metrics? And why are they required?
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.
23. What is the difference in the way Microstrategy Handles Custom Group and Consolidations? Any advantage of using one over the other?
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.
24. At which levels you can set the VLDB properties? Which level has the highest pecedence?
Answer
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.
25. 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:
1. Putting user specific restrictions at the database end and using user specific connection mapping. This is for column level security.
2. Applying folder and object level security to restrict access to certain set of reports/objects
3. Applying Security filters to the user. This provides row level security.
26. 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.
27. Please write a query to get All the departments, and the count of employee in those department, which have more than 50 employees and are based out of State New York. Following are the table structures:
27. Please write a query to get All the departments, and the count of employee in those department, which have more than 50 employees and are based out of State New York. Following are the table structures:
28. 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
29. What types of testing can be carried out over the microstrategy reports?
Answer
There are many aspects of the report that can be put into testing depending upon the type of report.
Naming convention tests.
Total calculations test.
SQL query validations.
Attribute and metric positions in report.
Drilling options Tests.
Prompts related verifications.
Security filter tests.
Formatting/export functionality tests.
Threshold testing
30. What do you mean by trimming of the SQL queries?
Answer
Trimming of the SQL refers to the clean up and optimizations that are applied to the query at the MSTR level using VLDB settings. The main thing that is achieved by the trimming is the removal of the unwanted cross joins and the other complex operations
31. How does the reporting varies in the Cognos and the Microstrategy?
Answer
The Cognos has the two metadata repository based database, while the microstrategy is based on single metadata repository. The Cognos has only a web based reporting tool, while the microstrategy can be operated as a webbased and a desktop tool. Cognos has a cube dataset which has to be refreshed to get latest repository values before reporting. The microstrategy has no cube dataset and we get the latest data from the data warehouse automatically available. The Cognos reporting can be considerably slow in performance than the microstrategy reporting.
32. What is compound and smart metrics?
Answer
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).
33. What are logical views? How are they useful?
Answer
The logical views help in establishing a specific view based on the combination of facts, attributes and expressions that are based on values from multiple tables. This can be done without the actual modification to the actual physical database or the DBA involvement. This way the reporting can be altered at the highest level of application and can be very quick way to alterations for reporting.
34. How can you make the SQL query to be based on a specific table of your choice?
Answer
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.
35. What are report filters and view filters?
Answer
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.
36. Why is it required to maintain a parent-child relationship among the various attributes? What are the various types of relationships depending on the number of participants?
Answer
The parent-child relationship helps in establishing the hierarchical structure to the entire logic and business model. For example, you can have the quarter to be a child of a specific year attribute. This will help us in having a modularized approach in the design and will help in establishing common usage semantics. The various types of relationships can be one to one, many to one, many to many, one to many relationships.
37. How is the number of passes in a SQL query significant in performance evaluation?
Answer
The number of passes represents the number of times the SQL query has to be parsed and different parts of the query is handled for every pass. The fewer passes we have the more efficient the query executes and faster the reports are generated.
38. What are consolidations and custom groups?
Answer
The custom groups are the made up of what are called as custom group elements and together they act as a special filter that can be implemented over a specific template. Consolidations refer to the way in which you group or order the data in your report. The different attributes can be gathered together without changing the underlying metadata and ware housing structure
39. How can you specify the report template and a report filter? How are reports optimized?
Answer
The objects maps are the ones that are used to select the group of microstrategy objects that are used to generate the report. This can serve as the template or filter. Then optimizations to the various reports can be done by analysis of the VLDB properties and the customizations can be monitored by the processing path taken up by the Anlytical engine
40. What are attribute roles? How can you resolve the attribute roles?
Answer
The attributes that are single in definition but the instances of the attribute can behave differently according to the role they take. Example for this can be a database of airports around the world. The attribute of airport name is always based on the same database table but can be seen as a destination airport or source airport. Such roles are resolved by having aliases for the same table, one alias for destination and one for source airports. We can also enable the automatic attribute role recognition by configuring it properly.
Two types: named users or cpu license.
2.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.
3. What are VLDB properties?
Answer
VLDB stands for Very 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.
4. What is the Command Manager?
Answer
MicroStrategy Command Manager lets you perform various administrative and application development tasks by using text commands that can be saved as scripts. Like for example: Ц server management, user management, security, database management.
5. What is an object prompt?
Answer
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.
6. What are Logical Views used for?
Answer
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.
7. How will Project Sources be configured in MSTR Office?
By editing the projectsources.xml, new project sources can be added.
8. Difference between standard, absolute and ignore condition in metrics?
Answer
Standard filtering allows the report filter to affect the metric. Absolute filtering raises the report filter to the level of the target, so all subcategories in the categories included on the report are added together. Ignore filtering disregards filtering criteria based on the attribute in the target and its related attributes (both parents and children).
9. Comparison between Consolidations and Custom Groups?
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.
11. What is the order of precedence of VLDB properties?
Reports>Project>Database Instance>DBMS
12. 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.
13. What is Metric Formula Join Type? How it is different with Metric Join Type?
This is basically 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.
14. 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).
15. How do we resolve attribute roles?
By creating explicit table alias for the same or enabling the Automatic Attribute Role recognition.
16. What are statistics tables and how do you configure the project statistics?
Statistics tables contain data on the MicroStrategy systemТs usage and performance, and are populated by all projects that are configured to log statistics.
To configure:-
1. Set up the Enterprise Manager.
2. Define the statistics and enterprise warehouse database.
3. Create the statistics database instance
4. Go the project configuration wizard of the project
5. Select statistics and define the what you want to log
17. Difference between project merge and object manager?
1. 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.
2. 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.
3. Project Merge can be run from the command prompt in Microsoft Windows.
18. 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.
19. 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. 4 types: matching caches, history caches, matching-history caches and xml caches.
20. 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).
21. 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. The logical size does not depend on the actual data (no of rows) in the database. If the candidate answers this than his concept is wrong
22. What are Level Metrics? And why are they required?
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.
23. What is the difference in the way Microstrategy Handles Custom Group and Consolidations? Any advantage of using one over the other?
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.
24. At which levels you can set the VLDB properties? Which level has the highest pecedence?
Answer
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.
25. 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:
1. Putting user specific restrictions at the database end and using user specific connection mapping. This is for column level security.
2. Applying folder and object level security to restrict access to certain set of reports/objects
3. Applying Security filters to the user. This provides row level security.
26. 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.
27. Please write a query to get All the departments, and the count of employee in those department, which have more than 50 employees and are based out of State New York. Following are the table structures:
27. Please write a query to get All the departments, and the count of employee in those department, which have more than 50 employees and are based out of State New York. Following are the table structures:
28. 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
29. What types of testing can be carried out over the microstrategy reports?
Answer
There are many aspects of the report that can be put into testing depending upon the type of report.
Naming convention tests.
Total calculations test.
SQL query validations.
Attribute and metric positions in report.
Drilling options Tests.
Prompts related verifications.
Security filter tests.
Formatting/export functionality tests.
Threshold testing
30. What do you mean by trimming of the SQL queries?
Answer
Trimming of the SQL refers to the clean up and optimizations that are applied to the query at the MSTR level using VLDB settings. The main thing that is achieved by the trimming is the removal of the unwanted cross joins and the other complex operations
31. How does the reporting varies in the Cognos and the Microstrategy?
Answer
The Cognos has the two metadata repository based database, while the microstrategy is based on single metadata repository. The Cognos has only a web based reporting tool, while the microstrategy can be operated as a webbased and a desktop tool. Cognos has a cube dataset which has to be refreshed to get latest repository values before reporting. The microstrategy has no cube dataset and we get the latest data from the data warehouse automatically available. The Cognos reporting can be considerably slow in performance than the microstrategy reporting.
32. What is compound and smart metrics?
Answer
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).
33. What are logical views? How are they useful?
Answer
The logical views help in establishing a specific view based on the combination of facts, attributes and expressions that are based on values from multiple tables. This can be done without the actual modification to the actual physical database or the DBA involvement. This way the reporting can be altered at the highest level of application and can be very quick way to alterations for reporting.
34. How can you make the SQL query to be based on a specific table of your choice?
Answer
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.
35. What are report filters and view filters?
Answer
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.
36. Why is it required to maintain a parent-child relationship among the various attributes? What are the various types of relationships depending on the number of participants?
Answer
The parent-child relationship helps in establishing the hierarchical structure to the entire logic and business model. For example, you can have the quarter to be a child of a specific year attribute. This will help us in having a modularized approach in the design and will help in establishing common usage semantics. The various types of relationships can be one to one, many to one, many to many, one to many relationships.
37. How is the number of passes in a SQL query significant in performance evaluation?
Answer
The number of passes represents the number of times the SQL query has to be parsed and different parts of the query is handled for every pass. The fewer passes we have the more efficient the query executes and faster the reports are generated.
38. What are consolidations and custom groups?
Answer
The custom groups are the made up of what are called as custom group elements and together they act as a special filter that can be implemented over a specific template. Consolidations refer to the way in which you group or order the data in your report. The different attributes can be gathered together without changing the underlying metadata and ware housing structure
39. How can you specify the report template and a report filter? How are reports optimized?
Answer
The objects maps are the ones that are used to select the group of microstrategy objects that are used to generate the report. This can serve as the template or filter. Then optimizations to the various reports can be done by analysis of the VLDB properties and the customizations can be monitored by the processing path taken up by the Anlytical engine
40. What are attribute roles? How can you resolve the attribute roles?
Answer
The attributes that are single in definition but the instances of the attribute can behave differently according to the role they take. Example for this can be a database of airports around the world. The attribute of airport name is always based on the same database table but can be seen as a destination airport or source airport. Such roles are resolved by having aliases for the same table, one alias for destination and one for source airports. We can also enable the automatic attribute role recognition by configuring it properly.
