Skip to main content

Order of tables appear in the SQL generated by the MicroStrategy SQL Generation Engine


Order of tables appear in the SQL generated by the MicroStrategy



Most latest generation databases provide the same performance regardless of the order in which the tables are placed in the FROM clause SQL query. Their optimizers take care of choosing the most efficient way of performing the joins. Other databases perform differently producing a different order. This means that a database compiler may choose a different join path that can be more or less efficient.

By default, the MicroStrategy SQL Generation Engine orders the tables in the following way:
  1. Fact Tables
  2. Metric Qualification (MQ) Tables
  3. Relationship Tables
  4. Lookup Tables

But this behavior can be changed by using the 'From Clause Order' VLDB property under the 'Joins' folder. This setting has four values:


Below are the values that the setting may have:
  • Normal FROM clause order as generated by the engine
    This is the default value. It uses the following order:

    1. Fact Tables
    2. MQ Tables
    3. Relationship Tables
    4. Lookup Tables


  • Move last table in normal FROM clause order to the first
    This option moves the last table in the normal order (which is a lookup table) to the beginning of the From clause. This value is useful in some Oracle databases where it may be more efficient for the Fact table not to be the first table in the From clause.

  • Move MQ table in normal FROM clause order to the last (for Redbrick)
    This value orders the MicroStrategy SQL Generation Engine to place the MQ tables at the end of the FROM clause. So the order becomes the following:

    1. Fact Tables
    2. Relationship Tables
    3. Lookup Tables
    4. MQ Tables
  • Reverse FROM clause order as generated by the Engine
    This value reverses the normal order of tables as generated by the MicroStrategy SQL Generation Engine. So the order becomes the following:

    1. Lookup Tables
    2. Relationship Tables
    3. MQ Tables
    4. Fact Tables
Note: The "From Clause Order" VLDB property is intended to control syntax only, not join behavior. Report results should be the same for all values of this setting. If the report uses only inner joins, then the only thing that changes is the table order. One-sided outer joins will switch direction when the From clause order is altered.

For example, a report that performs left outer joins to lookup tables (using the "Preserve all final pass result elements" VLDB property) will generate SQL such as the following:

select   a11.REGION_ID REGION_ID,
   max(a14.REGION_NAME) REGION_NAME,
   a12.CATEGORY_ID CATEGORY_ID,
   max(a13.CATEGORY_DESC) CATEGORY_DESC,
   sum(a11.TOT_DOLLAR_SALES) WJXBFS1
from   STATE_SUBCATEG_REGION_SLS   a11
   left outer join   LU_SUBCATEG   a12
    on    (a11.SUBCAT_ID = a12.SUBCAT_ID)
   left outer join   LU_CATEGORY   a13
    on    (a12.CATEGORY_ID = a13.CATEGORY_ID)
   left outer join   LU_REGION   a14
    on    (a11.REGION_ID = a14.REGION_ID)
group by   a11.REGION_ID,
   a12.CATEGORY_ID
    Moving the last table to the top of the From clause places the joins for the first three tables in parentheses. LU_REGION, formerly last, now appears first and right outer joins to the parenthesized join expression.

    select   a11.REGION_ID REGION_ID,
       max(a14.REGION_NAME) REGION_NAME,
       a12.CATEGORY_ID CATEGORY_ID,
       max(a13.CATEGORY_DESC) CATEGORY_DESC,
       sum(a11.TOT_DOLLAR_SALES) WJXBFS1
    from   LU_REGION   a14
       right outer join       (STATE_SUBCATEG_REGION_SLS   a11
       left outer join   LU_SUBCATEG   a12
        on    (a11.SUBCAT_ID = a12.SUBCAT_ID)
       left outer join   LU_CATEGORY   a13
        on    (a12.CATEGORY_ID = a13.CATEGORY_ID))
        on    (a11.REGION_ID = a14.REGION_ID)
    group by   a11.REGION_ID,
       a12.CATEGORY_ID
      Both From clauses are functionally identical. This is by design -- the intent of this property is to change the From clause order only, to fit better with a particular database platform's SQL optimizer.

      If it is needed to change the behavior of outer joins, the VLDB properties pertaining to outer joins should be used:

      • Preserve all final pass result elements
      • Preserve all lookup table elements
      • Downward outer join


      Labels:

      Comments

      Post a Comment

      Popular posts from this blog

      Microstrategy Custom number formatting symbols

      Custom number formatting symbols If none of the built-in number formats meet your needs, you can create your own custom format in the Number tab of the Format Cells dialog box. Select  Custom  as the Category and create the format using the number format symbols listed in the table below. Each custom format can have up to four optional sections, one each for: Positive numbers Negative numbers Zeros Text Each section is optional. Separate the sections by semicolons, as shown in the example below: #,###;(#,###);0;"Error: Entry must be numeric" For more examples, see  Custom number formatting examples . To jump to a section of the formatting symbol table, click one of the following: Numeric symbols Character/text symbols Date and time symbols Text color symbols Currency symbols Conditional symbols Numeric symbols For details on how numeric symbols apply to the Big Decimal data type, refer to the  Project Design Guide . ...
      4 comments
      Read more

      MicroStrategy URL API Parameters

      MicroStrategy URL Structure The following table summarizes the root URL structure used for every request to MicroStrategy Web. Environment Main Application URL Administration URL J2EE http://webserver/MicroStrategy/servlet/mstrWeb http://webserver/MicroStrategy/servlet/mstrWebAdmin .NET http://webserver/MicroStrategy/asp/Main.aspx http://webserver/MicroStrategy/asp/Admin.aspx Every request sent to MicroStrategy Web calls a central controller. Parameters are appended to  Main.aspx  or  mstrWeb  (in a .NET and J2EE environment, respectively) to indicate to the controller how the request should be internally forwarded and handled. The following examples show a URL for accessing a MicroStrategy folder when the user does not have an existing session. The URL contains not only the parameters needed to connect to MicroStrategy Web, but also the parameters needed to log on and create a session. J2EE environment: <a href="http:...
      2 comments
      Read more

      Custom Subtotal Displays in MicroStrategy

      Defining custom subtotal displays in MicroStrategy By default, when users apply subtotals in a report, the name of the subtotal is displayed in the subtotal line items that appear in the report. Users can use custom subtotals to give more control over the characteristics of a subtotal. Custom subtotals allow users to define custom subtotal line items that appear on the reports  U sers can make the subtotal name dynamic by typing special characters in the subtotal name field as listed in the following table. Character Description #A The name of the attribute under which the subtotal appears. #P The name of the attribute to the left of, or above the attribute under which the subtotal appears. #0 All the forms of the parent element. #1 The first form of the parent element reading from left to right or from top to bottom. #2 The second form of the parent element reading from left to right or from top to bottom. #3 The third form of th...
      Post a Comment
      Read more

      Control the display of null and zero metric values

      Show   Control the display of null and zero metric values in a grid report You can determine how to display or hide rows and columns in a grid report that consist only of null or zero metric values. You can have MicroStrategy hide the rows and columns in the following ways: Hide rows and columns that consist only of null metric values Hide rows and columns that consist only of zero metric values Hide rows and columns that consist only of null or zero metric values (default) Once you have defined how MicroStrategy hides null and zero metric values in the grid, you can quickly show or hide the grid using the Hide Nulls/Zeros option in the Data menu, as described below, or by clicking the  Hide Nulls/Zeros  icon  in the Data toolbar. To determine how null and zero metric values are displayed or hidden in a grid report Open the report in Edit mode. From the  Tools  menu, select  Report Options . The Report Options...
      Post a Comment
      Read more

      Case functions Microstrategy

      Ca se functions Microstrategy Case functions return specified data in a SQL query based on the evaluation of user-defined conditions. In general, a user specifies a list of conditions and corresponding return values. Case This function evaluates multiple expressions until a condition is determined to be true, then returns a corresponding value. If all conditions are false, a default value is returned.  Case  can be used for categorizing data based on multiple conditions. This is a single-value function. Syntax Case ( Condition1 ,  ReturnValue1 ,  Condition2 , ReturnValue2 ,...,  DefaultValue ) Example Case(([Total Revenue] < 300000), 0, ([Total Revenue] < 600000), 1, 2) sum(Case (Day@DESC in (“Sat”,”Sun”), Sales, 0) {~+} Sum(Case(Category@DESC In("Books","Electronics"),Revenue,0)){~+} CaseV (case vector) CaseV  evaluates a single metric and returns different values according to the results. It can be used to perfo...
      3 comments
      Read more

      Types of filters in Microstrategy

      Types of filters in Microstrategy Below are the types of filters: 1. Attribute qualification filter These types of qualifications restrict data related to attributes on the report. a) Attribute form qualification Filters data related to a business attribute’s form(s), such as ID or description. •  For example, the attribute Customer has the forms ID, First Name, Last Name, Address, and Birth Date. An attribute form qualification might filter on the form Last Name, the operator Begins With, and the letter H. The results show a list of customers whose last names start with the letter H. b) Attribute element list qualification Filters data related to a business attribute’s elements, such as New York, Washington, and San Francisco, which are elements of the attribute City. • For example, the attribute Customer has the elements John Smith, Jane Doe, William Hill, and so on. An attribute element list qualification can filter data to display only those customer...
      5 comments
      Read more

      Retrieve a list of user groups and the associated users in MicroStrategy

          Retrieve a list of user groups and the associated users in MicroStrategy Developer 9.x Although MicroStrategy Command Manager 9.x can be used to generate lists of all user groups and all users within a particular group, there is currently no way for it to retrieve a list of all groups and all of the users in each group. If such a list is desired, it can be created using the Project Documentation Wizard in MicroStrategy Desktop Developer 9.x. Follow the steps below to create a list of all groups and the users in each group: In MicroStrategy Developer 9.x, select 'Project Documentation' from the Tools menu to start the wizard. Select any project that is in the project source that contains the users and groups and click Next. Select only Configuration Objects for documentation. Uncheck the 'Basic Properties' object category from the next screen, as shown below: Then select only 'User Group' under the Configuratio...
      Post a Comment
      Read more

      Predictive modelling in Data Science using Microstrategy

      Creating a predictive modelling in MicroStrategy MicroStrategy Data Mining Services has been evolving to include more data mining algorithms and functionality. One key feature is MicroStrategy Developer’s Training Metric Wizard. The Training Metric Wizard can be used to create several different types of predictive models including linear and exponential regression, logistic regression, decision tree, cluster, time series, and association rules. Linear and exponential regression The linear regression data mining technique should be familiar to you if you have ever tried to extrapolate or interpolate data, tried to find the line that best fits a series of data points, or used Microsoft Excel’s LINEST or LOGEST functions. Regression analyzes the relationship between several predictive inputs, or independent variables, and a dependent variable that is to be predicted. Regression finds the line that best fits the data, with a minimum of error. For example, you have a dataset ...
      1 comment
      Read more

      Microstrategy Report Services documents and dashboards

      Microstrategy Report Services documents vs Dashboards A MicroStrategy Report Services document displays data coming from multiple reports, with the data laid out and designed in presentation-quality format. Most data on a document is from one or more underlying datasets. A dataset is a standard MicroStrategy report. Other document components that do not originate from the dataset, such as static text used for a title or heading, page numbers, and images, are added by the document's designer and are stored in the document's definition. A Report Services (RS) dashboard is a special type of document. An RS dashboard is commonly only one page long, is intended to be viewed online, and usually provides interactive features that let analysts change how they view the dashboard’s data, as well as what data they are viewing. A broad selection of widgets and a wide variety of formatting options allow you to design a customized, interactive dashboard. Both documents and RS dashb...
      1 comment
      Read more