Interesting Facts

1. Analytical engine calculates metrics such as percentile,rank,average after getting the raw data back from the warehouse and also report limits are applied after the results have been calculated. In such cases, Report limits sometimes behave like a view filter . This makes the results of a calculation appear to be incorrect as compared to what is shown on the report.n the case of analytical metrics, since the calculation of the metric and application of the report limit are done by the analytical engine, the HAVING clause is not seen in the report SQL.
To achieve the correct calculation, use a metric qualification in the report filter, rather than using a report limit to filter out the result set.

2.A Logical view cannot be created from another logical view created in Microstrategy as its a table created using SQL rather than the actual physical table that exists in the warehouse. Logical tables are created when required to a self join by creating alias tables and also when an attribute expression requires to return result based on data from columns coming from different tables.


3.A report limit is used in cases where you want to filter data after a condition is satisfied. For Eg: you want to fetch top 5 performing products and these products need to belong to X product group. In a report filter it will place where conditions and there is a possibility that first top 5 ranks are fetched and after that the product group filter is applied such that in top 5 only 3 belong to product group.

In that case you need to apply a report limit which actually puts a having clause rather than a where cluse. So the rank function needs to be placed in report limit. So first all the products in product group x is filtered and then top 5 is applied.

4.Advanced Qualification Filter is by default hidden in MicroStrategy. It can be enabled at Tools->My Preferences->Filters and check the box for Show Advanced Qualifications


5. The issue of exported contents inside panel stack getting clipped in pdf has been fixed in 9.3


6. To have the same attributes in page by and grid of the report at the same time you can create a derived metric with formula max(attribute@desc){attribute} and use attribute in page by and metric in report grid.

6. You can choose to display all prompts on a single page or each prompt on a different page in web using  Preferences in Web - > Prompt and select All in one page or Each on separate page.

7. An Object prompt created with template objects to be added to a report does not get added to the grid report unless the minimum and maximum number of objects settings is made to 1.

8.When making changes in a parent selector, the target selector filters properly but does not automatically populate with a corresponding value, causing the target grid to display no data. To fix this, select the "Automatically update when there is no data for current selection" option in the selector properties.

9. There is no limit on the parent child relationships that can be given to an attribute except that the metadata will be more complex to manage.


10.Advanced Sorting is always performed on data that is generated from the result set of a report. Hence you cannot sort on objects that are not present in the grid.

11. By default conditionality can be applied only to simple metrics and it ignores any report filter elements that are related to its conditionality filter. To remove this setting go to advanced section and uncheck "Remove related report filter elements"

7 comments:

  1. which is faster and preferred ? - expression based transformation or transformation or table based transformation

    ReplyDelete
  2. Table based is much effective when there are complex calculations to get the previous month or previous week or previous day.
    For Eg: if you want to show the revenue for current day and previous day but the previous day is not the day before current day but is based on complex calculations which involves including weekends and holidays then its better to get the table populated from ETL side and use them directly in transformations. In this situation we need to write complex formulas for expression based transformation. This type of transformation does involve a table and a join getting added to the query. However if you have just simple calculations like year - 1 then its better to go for expression based

    ReplyDelete
  3. how can I use dynamic calculation in filter to get month to date values

    ReplyDelete
  4. Please check this technote if it helps.

    https://resource.microstrategy.com/support/Authoring/DisplayTN.aspx?tnkey=20256&formatted=1

    If not please explain me the exact scenario so that i can provide the exact solution.

    ReplyDelete
  5. If I want to return an attribute value for last month, instead of a metric value, how would I go about this? (ie. attributeA value is the same, month is 1 month back, attributeB value is returned)

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. You can use a pass through expression in the attribute using a case when statement

      ApplySimple("case when #0=#0-1 then #1 end",Month@ID,AttributeB@Desc)

      Delete