Microstrategy Interview Questions - Part 3

21). 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.

22). 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.

23). 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.

24). 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.

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:
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.

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:

Department table: Department_ID, Department_Name, State (Note: Department ID is Unique here and more than one department can belong to one State)

Employee Table: Employee_ID, Employee_Name, Department_ID (Note: Employee ID is unique here and more than one Employee can belong to one department, but one employee does not belong to more than one department)

Following should be the query
SELECT   dept.department_id,
         Max(dept.department_name),
         Count(emp.employee_id)
FROM     department dept
         JOIN employee emp
           ON dept.department_id = emp.department_id
WHERE    dept.state = “new york”
GROUP BY dept.department_id
HAVING   Count(emp.employee_id) > 50

28). What are the different types of caches?

Result cache, history cache, element cache, object cache.

29). What are the different levels of VLDB properties?

Attribute, Database instance, Metric, project, report, template, and transformation.

30). What is dynamic aggregation?

It allows you to change the level of report aggregation on-the-fly, while you are reviewing the report results.