Interview Questions and Answers -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.

A Custom Group can do everything a Consolidation can, but a Consolidation is much faster than a Custom Group.

e.g.: A Consolidation is an in memory grouping similar to a case statement.  It can only act on a single attribute and all element groupings must be explicitly defined.  A Custom Group is a basically a set of filters.  It can be used across multiple attributes and metrics, as well as include actual filters and conditions.  While slower than a Consolidation, it is more flexible.  A new element being added to the database would require the Consolidation to be modified, but not a Custom Group.

Ragged Hierarchies/ Recursive Hierarchy

Ragged Hierarchies occur when levels can be skipped.  The most common case is an organization chart where an Executive Assistant may report directly to a Chief Executive.  A Recursive Hierarchy is when there is an indeterminate number of parent/child relationships embedded in a single table.

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.InMicrostrategy, 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