100 MSTR FAQ's

100 MSTR FAQ's

1. Differentiate report filter and view filter and justify which one is best

Ans. Report filter:

1)      Report filter restricts the data before the execution of a report

2)      Calculations are performed at Data ware house level

3)      It is a standard service

4)      By using report filter there will be a change in SQL view

              View filter:

1)      View filter restricts the data after the execution of a report

2)      Calculations are performed at analytical engine

3)      It is an OLAP service

4)      By using view filter there will not be any change in SQL view

2. What is the difference between report limit filter and set Qualification?

 Ans. There is no difference between these two but report limit filter is applicable only to that specific report where as a filter with set Qualification created outside can be used in multiple reports

3. Does report limit filter is applicable to drill report

Ans. Yes, when a report limit filter condition is applied on a report, the same condition is applied automatically when we drill the same report 

4. Can we apply report limit filter on attributes, if no, if I want to filter on region attribute how we can do that

Ans. No, we can’t! Report limit filter is applied only on metric values. To put filter condition on attributes we go for report filter or view filter

5.If we use report filter (set qualification) and report limit filter on the same report which one is best

Ans. Report limit filter is best. When compared to set qualification, report limit filter takes less no. of passes to execute the report i.e., why report limit filter is best

 6.What is relationship filter?

Ans. A relationship filter is a filter that can be used to define a specific type of relationship between two or more attributes that are not directly related, from a technical perspective

A relationship filter is nothing more than a nested sub query in where clause of a sql passes.

Ex: The customer and date attributes have no relationship to one another unless a customer purchases something on certain date, thus customer and date attributes are only related through the revenue fact (in which ever fact tables revenue fact is stored).

7.Security role and security filter

Ans. security filter A filter applied in the report reflects in the where clause of the sql and can be associated to a user or group for project. Security filter are the same as regular filters except that they contain only attribute qualifications, custom expressions and joint element list. Relationship filters and metric qualifications are not allowed in the security filters.

Security role it is nothing to do with the data, it is designed to provide privileges based on who is logging in.

8.What is a prompt and what are the different types of prompts?

Ans. Prompt is an object which allows users to interact with report at runtime. There are four types of prompts 1) filter definition 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

                             2) object prompt

                            3) Level prompt

                            4) Value prompts

9.What is a nested prompt? Give me a scenario

Ans. when we use prompt within another prompt that is called as nested prompt.

Ex: suppose we have a prompt that list all the states and in second prompt you would like to see the cities that belong to that particular state, then we have to create 2 prompts one prompt for states and another prompt for cities and place one in another

10.When we select a year we need to display only 6 months (say: jan, feb,…only 6months)

Ans. a) create a year prompt and select all years by qualifying on attribute element list

b) Create a month prompt and select month of the year attribute and select add elements and select any of six months which to be prompted

Create a report and place year, month attributes and place revenue metric and place the above two prompts on to report filter definition

11.In a text file we have 10 regions and we are using prompt element in one of our requirement, so how to use that text file in our report

Ans. Open a new filter and Qualify on attribute(region), select attributeà region, Qualify onàDesc form, select operator as in list or not in list, then click on import and browse for the text file containing 10 regions and save it.

Place this filter in prompt and use this prompt in a report.

12.In one of my requirement we have attributes and metrics. How many object prompts have to create in this scenario

Ans. 2

13. Can we place reports in object prompts?

Ans. yes

14.Can we place dashboards in object prompts?

Ans. yes

15.What is value prompt provide an example

Ans. The prompt which allows users to enter a text, date, number or other value type at run time. Value prompt is again subdivided into 4 types: 1. Date and time prompt

                                                                                2. Numeric prompt

                                                                                3. Text prompt

                                                                                4. Big decimal

Ex:  conversion of currency to dollars

1. Scenario: output of the report must include category, subcategory and revenue. Metric should be single , functions (avg, max, min,…) must  be prompted
2. Can we place hierarchies in prompts

Ans. Yes

3. What is a level metric, what are the elements of level metric, differentiate between standard and absolute filtering

Ans. Metrics which calculate at specified attribute level rather than that of default report level, such type of metrics are called as level metrics.

Target, filtering, grouping are the 3 elements of level metric

Standard filtering: In case of standard filtering the report filter interacts in the usual way with level metric and shows data only for the elements specified in the report filter

Absolute filtering: In case of absolute filtering it raises the report filter elements to the parent level

16.What is non aggregate metric?

Ans. In level metrics if the grouping is set to none then it is called as non-aggregate metric.

The non-aggregate metrics are used in solving percentage calculations.

17.Can we place filters and prompts in derived metrics?

Ans. No

18.Can we place hierarchies in metrics?

Ans. No

19.Under level dimensionality what are the objects can we place

Ans. Prompts, Hierarchies and attributes only

Scenario: banker, client, industry, revenue, sales       Revenue à must be calculated on banker sales à must be calculated on client, industry

Ans. Create two level metrics. On first metric select the revenue metric and in level dimensionality select the target as banker. On second metric select the sales metric and in level dimensionality select the target as client and industry. Then on report put the above two level metrics

20.Difference between compound metric and compound smart metric

Compound Metric	Smart Metric
We can perform arithmetic operations on more than one metric	When we want to subtotals/grand totals values correctly then go to smart metric scenario & enable Smart metric
Ex: rank(metric)      Ntile(metric)	Formula: sum(m1)/sum(m2)

Ans.

When we perform subtotals you find difference in compound metric & smart metric reason is calculations calculated at row level in smart metric [accurate correct values come in smart metric]

21.Scenario: in a report top 10 items in terms of revenue is calculated using report filter and top 5 customers in terms of revenue is calculated using conditional metric. In output which qualification (report filter/conditional metric) is going to be executed?

In the above Question if it applies report filter first it should ignores report filter and should apply on conditional metric

22.Is nested metric a simple metric or compound metric

Ans. Nested metric is a simple metric

23.What is a nested metric, explain with a scenario

Ans. A metric with in another metric or the metric which performs multiple aggregation by placing one calculation formula inside another.

Ex:  Suppose we want to see profit data averaged at the year level but existing fact tables provide month level profit data so, to calculate yearly average for profit we go for nested metric

                                       Avg( sum(profit){~ month}) {~ year}

24.When we use level metric which part of SQL gets effected?

Ans. where and group by

25.Can we use transformations as derived metrics?

Ans. yes

26.What are the metric join types, what are the default join types, is there any case to change metric as outer

Ans. The join type for metric is inner join.

The default join type for

                                               Attributes à Outer join

                                               Metrics     à Inner join

Yes we can change the metric join type from inner to outer, go to report data options and select metric join type click the drop down box and select join type as outer.

27.Can we apply level dimensionality to derived metric?

Ans. Yes

28.Can we place dashboard in a dashboard

Ans. No

29.What is a widget?

Ans. widget is one of the features of dashboard with which we can visualize data in multiple ways

30.In gauge widget if I place 2 metrics how would be the output

Ans. gauge widget will get two pointers/needles

31.How many attributes and metrics can be placed on document?

Ans. any no. can be placed

32.Suppose we have two reports, in one report we have region and revenue and in another report we have region and profit. If we place region as selector and selected any specific region which report is going to be effected

Ans. target

33.What is target in a selector?

Ans. targets are the controls that the selector effect such as grid/graphs, panel stack and other selectors

34.What is a panel and what is a panel stack

Ans. we can display different controls (objects that can be placed on a document such as grid/graph, graph, grid etc.,) in a dashboard/document so that users can navigate them as if they are pages for the larger document, these pages or layers are panels.

Group of panels is referred as panel stack.

35.Interesting or challenging about mstr in your experience

36.Difference between RSD (report service document) and dashboard

37.In dashboard can we see other sections?

38.In a document how can we create a metric?

Ans. No we can’t create a metric in a document rather a derived metric can be created

39.How do we link one document to other document?

40.What are the different selector properties you are aware of?

41.What is the difference between filter and slice in selector?

42.What are the different ways we can view document in mstr web, what is the difference between interactive mode, flash mode, editable mode, express mode and view mode

43.Differentiate between consolidations and custom groups

Ans. Consolidations:

a.      Consolidations are derived from attribute elements only

b.      In consolidations the calculations are performed at analytical engine

c.       Less no. of SQL passes

d.      Efficiency is high

e.      Consolidations are preferred when the data is static

               Custom groups:

a.      Custom groups are derived from attributes as well as metrics also

b.      In Custom groups calculations are performed at DWH level

c.       More no. of SQL passes

d.      Less efficient

e.      Custom groups are preferred when the data is dynamic

44.What is a derived element, what are the types of derived elements?

Ans.  à Grouping of attribute elements on a report, these groups provide a new view of report data for analysis and formatting purposes

Derived elements are evaluated on the report data set retrieved from an I-cube, without re-executing SQL

Types of derived elements:

1)      Grouped derived element

2)      Filter derived element

3)      Calculation derived element

45.Can we import consolidation elements from other consolidations?

Ans. yes, go to elements tab, click on import elements

46.In which scenario you go for consolidations and custom groups

Ans. in our requirement if we need to deal with only attributes we go for Consolidations

            In our requirement in a report we need to display say: top 5 customers, bottom 10 items, top 5 items (dealing with both attributes and metrics) in such case we go for custom groups.

 Intelligent CUBES

47.What is i-cube

Ans. I-cube is a multi-dimensional object on IN-Memory database. When we run some specific report, rather than returning data from the DWH we can return sets of data from the DWH to Intelligent server memory, so that we can build multiple reports that gather data from i-cube instead of querying DWH

48.Why we go for i-cubes

Ans.  Mainly to reduce the execution time of the report we go for i-cubes.

Suppose we have 15 columns (combination of attributes and metrics), if these 15 columns are existing in 10 reports in such case rather than going against DWH and fetching data which leads more execution time we can create a single I-cube with all those 15 columns which leads to very less execution time

 49.What are the objects that can be placed on to i-cube

Ans. Attributes, metrics, reports, filters, hierarchy, templates etc

 50.What are the objects that can’t be placed on to i-cube OR What are the limitations of i-cube

Ans.   

a) Consolidations and custom groups

               b) OLAP services features

                                        View filter

                                        Derived metric

                                       Derived element

                                       Dynamic aggregation

              c) Prompts

 

51.Can we place prompts in I-cube, if yes  how

Ans. No we can’t place prompts in icubes

 

52.Can we drill outside the icube, if so, whether it hits icube or data ware house

Ans. yes, we can drill outside of an icube.

Drilling outside the icube requires a new report to be executed against the DWH. Uncheck the option use default settings and check allow reports to drill outside of the icube

 53.What is dynamic sourcing

Ans. dynamic sourcing is an automatic link between icubes and reports.

 If the complete report definition of any report is present in any single cube then it gets the data from icube or else it hits the DWH.

Once we enable dynamic sourcing option, report first checks the data is present in icube or not then if not it fetches the data from DWH.

54.How many types of icubes are there, differentiate between icube and personal icube

Ans. The icubes are of two types: 1) normal icubes and 2) personal icubes

1)      Icube: àwe can access multiple reports

àOnly report filter is available before publishing the icube

àDerived elements can be worked

2)      Personal Icube: àIt is linked to a single report

   à Both report filter and view filter are available

   àDerived elements cannot be worked

55.What are the possible errors that we get when we publish an icube

Ans.  a) Error1: icube is not published

    b) Error2: icube is currently offline

    c) Error3: icube is being published but the publishing process is not yet finished

 56.What are the prompts that are supported in icube reporting and which are not supported

Ans. All the prompts are supported except “hierarchy prompt” and “level prompt” are not supported in icube reporting.

57.How to apply color on attributes

Ans. On a report go to grid options, select custom banding, click on settings and select the color whatever we require

58.How to get odd records in different color

Ans. On a report go to grid options, select custom banding, click on settings and select the two banding colors and in  banding criteria select option by number of rows and select the rows as 1 for first color and second color also

59.How to apply condition on attribute and the result should reflect on metric

60.What is logical size, how is it calculated, explain its significance

Ans. when we bring the tables into MSTR from DWH and create attributes on the logical table automatically a logical size will be assigned to the table based on no. of attributes [mstr calculates logical sizes based on its own algorithms based on no of attributes and the position of attributes in the hierarchy.]

 Significance: when we have same column present in multiple tables, the table with lowest logical    size is going to hit

61.If I want to change the header object on the top of a report how can we do it

Ans. On that report go to report data options, in display option select alias and give the alias name

62.Can we change the header object which should be reflected in SQL, if yes how to do

Ans. No we can’t do

63.We have two dimension tables say prod_dimension, time_dimension. If we need data from both these two tables how can we do that

Ans. The above two tables have no relationship between them so, by the help of a fact table including the key columns of above two tables we can fetch data from both the tables

 

64.Explain about star schema and snow flake schema and point out some differences

Ans. In MicroStrategy if a fact table is surrounded by multiple dimension tables then such a schema is called as star schema.

In MicroStrategy if a dimension table is further divided into sub dimension tables and that sub dimension tables are indirectly related to fact tables, such a schema is called as              snowflake schema.

 65.Fact doesn’t exist at level specified, what would be the reasons

Ans. à when related objects are updated and we forgot to update schema

è Fact doesn’t exist at Level fact

è When corresponding tables are unchecked in fact editor

66.How to hit a particular fact table

Ans. it hits the table with lowest logical size. To hit the particular fact table:

1)      Using metric editor (Rclick on formula -> sum parameters->fact id -> select the table to forcibly hit )

2)      Using fact editor (by changing mapping from automatic to manual and checking or selecting the desired table)

3)      Explicitly changing the logical size in table editor

67.What is fact extension

Ans. If the fact at warehouse level will be loaded at lowest granularity and in microstrategy we want to report at different level (parent level), then in such case we go for fact extension

Ex: In warehouse we have day level granularity and in mstr if we want to report at month level, it is called as fact extension

Fact extension is done in fact editor.

 68.What is fact degradation

Ans. In warehouse we have month level granularity and in mstr if we want to report at day level, it is called as fact degradation

 

69.What is heterogeneous mapping

Ans.  As per business functionality they serve the same but they exist as different column names in different tables. Grouping all those different column names in to 1 single name is called as heterogeneous mapping

70.What are transformations and various types of transformations

Ans. Transformation is an object which is used for time series analysis. When we need to compare current data to previous data for better business analysis we go for transformations.

Transformations are helpful in data mining. Table based transformations and expression based transformations are the two types of transformations

Table based transformation: Whenever the values calculated are stored in a table which is specifically designed for transformation such a transformation is a table based transformation

Expression based transformation: When we apply arithmetic operations, mathematical functions for the table based transformations such type of transformations are called as expression based transformation 

71.Which part of SQL is effected when we put transformations in a report?

Ans. “Where” clause is affected and number of passes are more on the report using transformations

 72.One attribute, one metric, one transformation metric how many passes we can see in sql ?

Ans. 3

73.What is hierarchy, what are the types of hierarchies

Ans. Hierarchies are grouping of directly related attributes ordered to reflect their relationship.

There are two types of hierarchies,

       System Hierarchy:  when we drill on a report we get system hierarchy

User defined Hierarchy: customized grouping of attributes. If we want a particular hierarchy not to be shown to end user at that instance we go for user defined hierarchy

74.Can we edit system defined hierarchies

Ans. Yes, every business keeps on changing so there should be updating for existing hierarchies

75.What is the default evaluation order

Ans. The default evaluation order is:   1) Compound smart metric

                                                                    2) Consolidations

                                                          3) Derived metric

                                                          4) Report limit filter

                                                          5) Sub Totals

 

76.Can we change the evaluation order, if yes how

Ans. Yes, we can change the default evaluation order. Go to report data options and select evaluation order, uncheck the option set default evaluation order and then we can change evaluation order by changing the numbers

77.What are the VLDB settings you have worked out

Ans.    1. Sql version 89 to 92

           2. Derived table (avoiding multiple passes and creates less passes)

           3. Avoiding Cartesian join type

           4. Avoiding sub-Query type

           5. Create indexes on temporary tables and drop before

           6. Drop all temporary tables before passes

78.In database say we have 100 rows but on mstr report we are getting 80 rows what can be the possible reason

Ans. a) the schema relation might be incorrect in MSTR(by correcting the schema relationship or  by vldb à data population)

b) We may have some condition at MSTR level (like view filter)

79.If a dashboard is running for more time what are the different steps you have to take to tune it

Ans. 1) Hide unused document sections by collapsing the section on template

2If the datasets are returning large amounts of data then consider adding Grouping(group by) to the document

3)      Plan the design of a document so that all related data can be on single screen/page

4)      Do not include so many graphical objects that the data becomes unimportant

5)      While designing the document preview it in each display mode, make sure that the document appears as expected in each display mode

 6) We see whether the report is bringing data from cube or data ware house, if it is bringing from data ware house we check if it is feasible to create an icube

7) By building aggregate tables

8) By building indexes on the columns used for conditional checking (In where clause)

9) By changing the date, time data type to date data type in mstr

10) By setting vldb properties

80.Difference between object manager and project merge

Ans. object manager:

a.            object manager is used to move a few objects from one environment to another environment

b.            object manager must locate the dependencies of the copied objects and then determine their differences before performing copy operation

c.            Project merge: a. project merge is used to move all the objects from one environment to another environment

d.            project merge doesn’t check for the dependencies since, all the objects in the project are to be copied 

81.What are managed objects

Ans. certain mstr features automatically create new schema objects, referred as managed objects which are not directly related to project schema.

è In mstr standard, schema objects relate information in the logical model and physical ware house schema to the mstr environment

è Managed objects are type of schema objects that relate MDX  data source to MSTR environment

82.How do you move components from one environment to other environment.  do you move the objects individually or do you create a package and move them

Ans. we create a package for which objects you want to move then import the same package into destination environment

 

83.What are the different conflicts we encounter while moving objects from one environment to other environment

Ans. when copying objects across projects with object manager, if an object with same id as a source object exist anywhere in the destination project a conflict occurs.

Various ways to resolve depending on the conditions: Use existing, Replace, Keep both, Use New, Use Old, and Update in same path

84.What are the different action types that are present In object manager

Ans.

a.      Copy objects within and across related projects

b.      Make objects within a project

c.       Delete objects within a project

d.      Rename objects within a project

e.      Search objects within a project

f.        Find an objects parent/child within a project

85.Can we place template in a template

Ans. No 

86.Scenario: in a report we have one attribute and one metric (say revenue), if revenue is greater than 60% it should be in green color else it should be in red color

Ans. By the help of Thresholds we can do it

87.When you click on update schema, what are the options we get

Ans.  a. update schema logical information

         b. recalculate table keys and fact entry levels

         c. recalculate table logical sizes

         d. recalculate project, client, object cache size

         e. purge all element caches

88.What are view reports and standard reports, differentiate them

Ans. view reports are nothing but the reports accessing icubes, Standard reports are the normal reports

 

89.Scenario: Create a report that returns number of items sold by region and category for the year 2011 

90.Foreign key

Ans. it is a column/group of columns that is used to establish a link between two tables

91.Sub queries in mstr

92.Team structure and your role

93.Your responsibilities

94.When we have database why there is need for data ware house

95.What is surrogate key

Ans. surrogate key Is a number that uniquely identifies records in dimension table (non intelligent keys are surrogate keys) Ex: Slowly Changing Dimensions

 96.How joins are established in mstr

Ans. Based on the vldb settings, report data options (attribute join type , metric join type), in metric  editor by selecting the metric join type

97.What are the different things you have worked on admin

Ans. worked on creation of users and groups, created security filters to restrict the data for a group or individual user, moved objects from one environment to another environment (development to QA)

98.What is groupby in sql

99.In one single column if we get both negative and positive values our requirement is positive values should be one individual column and negative values should be in another column

Ans. by using case statements in sql

100.How do you resolve m to m relationships

Ans. by using bridge tables (temporary tables)