1, it is possible to identify specific attributes as having an incomplete lookup table by setting the “Preserve all final pass result elements” VLDB property within the attribute. If you are finished adding to Cube A, click Update Dataset. Set the Attribute Join Behavior to "Outer Join - Preserve Filter" 4. 1 release. This will be your lookup table for your Item attribute. MicroStrategy Engine provides the capability to switch the order of the table using the VLDB setting "From Clause Order". Ranked #1 for Enterprise Analytics in 2022 Gartner Critical Capabilities Report. FULL OUTER JOIN. PIVOT. The Full Outer Join Support setting specifies if the database platform supports full outer join syntax. Full Outer Join Mismatching. If a document does not contain any primary datasets, all datasets are joined using inner joins. Joint child relationships are really another type of many-to-many relationship where one attribute has a many-to-many relationship to two otherwise unrelated attributes. Full Outer Join using Union clause. 4. KB441377: Full Outer Join Optimization implemented in MicroStrategy 11. KB17183: Join 89 versus Join 92: Join 89 limitations in MicroStrategy 8. Two attributes: Parent01 and Child01 have a parent-child relationship. Make sure that this attribute is stored at the specific level in the Fact table which you want to join. Steps to Reproduce This procedure is using the MicroStrategy Tutorial warehouse that is shipped with MicroStrategy. In some scenarios, the cross join is resolved through modifications to the schema objects, which can have a ripple effect to all other reports in an environment. Choose File > New > Intelligent Cube. In this article, we will discuss FULL OUTER JOIN using LEFT OUTER Join, RIGHT OUTER JOIN, and UNION clause. This technical article explains a SQL change where previous full outer join syntax would be generated for databases, but the warehouse does not support full outer join syntax. The following a sample code on how to modify. x, a metric's join type is defined by a property called 'Metric Join Type' within the 'VLDB Select' property set. Limitations: With Join 89, a table could not be outer-joined to more than one other table. SAP HANA 1. For information on how to display this property, see Viewing and Changing Advanced VLDB Properties. The object Type is listed as Super Cube: This can also be seen when editing or authoring a dossier. Select "CITY_MNTH_SLS" and "LU_MONTH". You can apply an inner or outer join, which are described in more detail below. txt. Relating Column Data with SQL: Joins The table below summarizes the Joins VLDB properties. The . The Full Outer Join Support property specifies whether the database platform supports full outer join syntax: 9. The following procedure includes these steps. This article is to introduce a workaround to achieve the attribute left outer join on MicroStrategy Web. In this situation the presence of Year_date on the grid limits the data to just rows that exist in table 2 which is why the year 2016 is not seen. This dialog box is accessed from the Advanced Settings option in the Tools menu of the Metric Editor. There's a reason we're at the top of the list. This is a known defect in MicroStrategy 10. x. [Category],[Year]} / [Revenue])) as [DM2] from DBRM2 to Ex7_tempcube1 select * from Full Outer Join( Ex7_tempcube0, Ex7_tempcube1 ) on Year, Category to Ex7_tempcube2 Drop Ex7_tempcube0 Drop Ex7_tempcube1. JOINS can also be used in other clauses such as GROUP BY, WHERE, SUB. My report will have these two. Ranked #1 for Enterprise Analytics in 2022 Gartner Critical Capabilities Report. 0 for databases that do not support Full Outer Joins Number of Views 1. Prerequisite Before you can perform an outer join, you must allow outer joins to be supported. Normal join In normal join, only matching rows are returned from both the sources. I think as Jac74 said i have to work with view or in my case i can add a null row in the the table and use applysimple to convert that null value to some other value like "0" to make a regular inner join. You can modify the join expression in one of two ways: Simple expression: This is the default. Click OK. The Full Outer Join Support setting specifies if the database platform supports full outer join syntax. So, if there are rows in "Customers" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Customers", those rows. 1K views 3 years ago MicroStrategy In this video I will show you why you need outer joins and how to solve this for multiple scenarios in MicroStrategy using. 8. The SQL you get will be:Syntax : SELECT column_name (s) FROM table1 RIGHT JOIN table2 ON table1. Here is the workaround to achieve the attribute left outer join on MicroStrategy Web. KB14540: Result rows are omitted when using full outer join support in MicroStrategy SQL Generation Engine 9. If you are upgrading to MicroStrategy 11. Why is this happening? This VLDB property becomes obsolete with an upgrade to a data engine version of 2021 or later. The following examples show how datasets are joined. StoreId in ('12345') You can then add in Item as a second INNER JOIN, relating it. In the Datasets panel, right-click the attribute you want to link and choose Link To Other Dataset. First: drop the client column from ClientIDPrefixSumPurchased and ClientIDPrefixSumInstalled both from the select clause and the group by. name, Price. Why MicroStrategy Why MicroStrategy; Customer Stories; PlatformThere's a reason we're at the top of the list. xWhy MicroStrategy Why MicroStrategy; Customer Stories; PlatformNote: In MicroStrategy 11. Where it is recognized, it is usually an optional keyword. The join type determines what type of join (inner or outer) is used by the SQL Generation Engine for each one of the metrics in a given report. Click Demo. This causes multiple counting in the report level aggregation, and also the discrepancy between the Income Bracket IDs and descriptions. The following procedure includes these steps. Perform full outer joins; Check for Zeros and Nulls in the denominator while performing divisions in Metrics. The "Downward Outer Join" VLDB property allows higher-level metrics to be outer joined by constructing a table of all necessary attribute elements to be included early in the join path. Full Outer Join Support is an advanced property that is hidden by default. Full Outer Join Support = Outer-Join . The following table lists folders and files of interest that are included in the default directory structure after an installation of Developer. 7. The year_date attribute from table 2 is added to the grid. UNION. The customers with cust_id “3” have no orders, and the orders with order_id “103” has an id order and no matching customer, so their respective columns contain NULL values in the result. Master outer and Detail outer joins are equivalent to left outer joins in SQL. x. Unrelated and nonshared attributes will be cartesian joined. There's a reason we're at the top of the list. In MicroStrategy Analytics Enterprise 10. Loading. Create three reports named A (with attribute A), A&B (with attribute A and B),A&C (with attribute A and C) Create a dashboard based on the three reports created in last step. This is due to the fact that the user allocates resources in anticipation of a very large amount of data (even if the string is only a few hundred. Inner Join is further divided into three subtypes: 1) Theta join 2) Natural join 3) EQUI join. Instead, it creates two passes of SQL and creates all SQL correctly except without an outer join to the lookup table in the final pass. Controls whether tables are joined only on the common keys or on all common columns for each table. In their own words, “MicroStrategy is the largest independent publicly traded business intelligence (BI) company, with the leading enterprise analytics platform. To perform an outer join against a lookup table Open the Project Configuration Editor. In short, an inner join includes only data that is common to all components of the join. id = p. Join Type for "All Attributes" is set to "Outer Join - Preserve Filter" for the Visualization. Setting a join specification allows you to place conditions on the data selected for display in a report. Two other VLDB properties, Downward Outer Join Option and Preserve All Lookup Table Elements, have an option to apply the filter. If this choice is selected at attribute level, it will be treated as preserve common elements (that is, choice 1) optionA metric join setting determines how data is combined by applying a specific type of join, inner or outer. All rows of the intermediate table are preserved. Invalid SQL is generated when level metrics are outer joined to non level metrics in MicroStrategy 10. x. The strength of NSS lies in its outstanding model. Too bad!A simple idea would be to modify the metric by means of dimensionality, namely, the LEFT OUTER JOIN attributes with the setting ignore add / None. Steps to reproduce This example uses the MicroStrategy Tutorial warehouse that is shipped with MicroStrategy. c1 = t2. Scenario 2. In the MicroStrategy Tutorial, a report with the Subcategory attribute and the Freight and Discount metrics generates the following SQL (using Microsoft SQL Server): select a13. ACTION: To take advantage of this new feature upgrade to MicroStrategy 10. MicroStrategy provides modern analytics on an open, comprehensive enterprise platform used by many of the […] Right outer join: Performs a right outer join on the columns. INNER JOIN. There are basically four types of JOINS in SQL. The table now appears in bold as shown below. 1 or above, to pick up the fix, enable project level setting "Data Engine Version" to 11. 99K subscribers Subscribe 4. thread395-807711. Enable optimization to remove repeated tables in full outer join and left outer join passes. The following commands have been added for Intelligent Cube management: Get the status of a Multi-Table Data Import (MTDI) cube refresh ; Get the status of table refresh within a MTDI cube NOTE:SQL OUTER JOIN overview and examples. Preserve All Final Pass Result Elements. In SQL terms, the SQL Engine must perform a left outer join from the lookup table to the fact table. Also you can take advantage of Metric Dimensionality i. 11 or above. Full Outer Join Support: Support: Select/Insert: Bulk Insert String ; Select/Insert: UNION Multiple INSERT: Use UNION: Select/Insert: Distinct / Group By option: Use DISTINCT: Query Optimizations. A,table_a. There's a reason we're at the top of the list. This article describes how to create an outer join between two attributes. SELECT. Metric Join Type: VLDB. However, enabling full outer join support for specific reports is recommended if full outer joins are only used for a small to moderate amount of reporting needs. Change the join type from inner to outer. Trending Articles. Why MicroStrategy Why MicroStrategy; Customer Stories; PlatformSimilar to scenario 1, three passes are generated. Follow the steps below. The unmatched rows are returned with the NULL keyword. x, once a custom group and a level metric are add into a report, though you enable join type to outer join, null value still can’t been seen. Ranked #1 for Enterprise Analytics in 2022 Gartner Critical Capabilities Report. If the join type is changed, the SQL statements will change accordingly. You can right-click the line representing the join and select one of the four join types described above to modify the join type. If Full Outer Join Support = Yes, the workflow of SQL Engine is as below: Generate the SQL as inner join. Also return the rows from the outer joined where there's a match on the join key. Select VLDB Properties from the Data menu to access the VLDB Properties (Report) dialog box. Therefore, attributes with null values for the ID will not be behave properly in the MicroStrategy product. Default Value. A UNION clause can be used as an alternate to get the same result as FULL OUTER JOIN. SQL Join statement is used to combine data or rows from two or more tables based on a common field between them. Starting in MicroStrategy 10. You want to place metrics on the report and make the join types Outer Join. Our solutions work to guide towards best practices and provide guidance to the user community. To add another cube, repeat Steps 4 and 5. An outer join includes all data from every element (the union of all. And once again I had to annoy me about the fact that there is no clever way to MicroStrategy, an outer join between the attributes to be generated. All report metrics use outer join: No flags are generated. solutions Forum Left outer joins iolaper (MIS) (OP) 19 Jul 04 09:53 Hello guys, I am trying to generate a. SQL Full Outer Join. To see rankings for only the attribute elements which were filtered on (the first 5 elements), remove the 'Max of 1' and 'Rank Revenue' metrics as shown below. The purpose is to show how a filter can be manipulated to give the developer full access over the where clause generated by MicroStrategy's SQL engine. On the duplicated visualization, go to Metric Options and change outer join for both the metrics to be inner join. KB31133: Join conditions are omitted in the calculation of nested aggregation metrics in MicroStrategy SQL Generation Engine. The FULL OUTER JOIN command returns all rows when there is a match in either left table or right table. Do the same with Response. Different type of joins. Notice the outer join is applied, and that in the last pass of the CSI, there is a cross join between L_Category & L_Year and then "left outer join" to the Metric result. Community & Support Search Discussions Open A Case View My CasesRows containing values for inner join metrics but not the outer join metric are dropped. An outer join includes data that applies to all components. All rows of the intermediate table are preserved. Maybe try running one report each with each metric and making sure that there really are valuse that you are missing. •. Plus, it connects business apps while integrating data. A simple idea would be to modify the metric by means of dimensionality, namely, the LEFT OUTER JOIN attributes with the setting ignore add / None. CAUSE. 1. 11, analysts can now add direct links within a dossier. The only joins supported by MicroStrategy use the = operator. After the project information is. 4K. (For information on object levels, see Order of Precedence . There's a reason we're at the top of the list. RE. Steps are as below. I worked around it but din't make any difference. If the Full Outer Join Support VLDB property (see Join Type) is set to Support, this property is ignored and the Join 92 standard is used. N FROM table_A FULL OUTER JOIN table_B ON table_A. This article is to introduce a workaround to achieve the attribute left outer join on MicroStrategy Web. SELECT (Virtual Columns) SELECT (with Derived Column List) SEMIJOIN to a Procedure. Full outer join/full join; Cross join/cartesian product; You can also perform a “self join”, and use equijoins and non-equijoins, which I’ll explain later in the guide. e. SYMPTOM. Customer,a. Ranked #1 for Enterprise Analytics in 2022 Gartner Critical Capabilities Report. 1 Answer. The following examples explain this in more detail. Use default inherited value. . Improve this answer. This is an identified defect in MicroStrategy Web 10. To Display the Advanced Properties. x:The report returns the following: Notice in the screen shot above that the Data for Project B is missing. The two are the same thing and can be used interchangeably. VLDB Properties. MicroStrategy is the leading enterprise business intelligence tool with its mission statement to make every enterprise a more intelligent enterprise. In the same pass and between 2 different tables. Beginning with MicroStrategy 8. MicroStrategy 2021 Update Installer provides customers with a simple and centralized solution for applying critical fixes to the MicroStrategy 2021 Platform Release. Download the above emp_deptname. There's a reason we're at the top of the list. From what you have described an outer join on the metrics seems to be the right thing to do. MySQL does not have FULL-OUTER-JOIN syntax. Create a new dataset. Ranked #1 for Enterprise Analytics in 2022 Gartner Critical Capabilities Report. There's a reason we're at the top of the list. The SQL passes will then left outer join and give you the results you want. Two attributes: Parent01 and Child01 have a parent-child relationship. In the right corner of the dashboard, change the. 8. Join common key on both sides. This means you can manually change the suffix to . Some do not. In the last pass, an outer join is performed against the OL (Outer Join to Lookup) table. Full Outer Join Support is an advanced property that is. LEFT [OUTER] JOIN. This article is to introduce a workaround to achieve the attribute left outer join on MicroStrategy Web. INTERSECT. •. For metrics, the default join type is Inner join. This is a known issue prior to MicroStrategy SQL Engine 9. Therefore, SQL is generated for the filtering report using an inner join between the two metrics, and since the metrics have no attribute elements in common, all data are excluded. x. This issue has been addressed starting in MicroStrategy 9. The full outer join or full join returns all rows from both tables, matching up the rows wherever a match can be made and placing NULL s in the places where no matching row exists. When the metric join type is set to Outer Join and the SQL Global Optimization is set to Level 4, extra SQL passes are generated. You can define how these elements are joined using the Metric Formula Join Type dialog box. Select Right Outer Join to include all records from the column in the second table and only those records from the column in the first table in which the join condition is satisfied. zip and use 7Zip, or similar tools, to unzip it. Prepare Data. 1. LEFT OUTER JOIN. This can be done with a union of the IDs of the multiple intermediate tables that need to do an outer join and then using the union table to left outer join to all intermediate tables, so this approach generates more passes. The SQL Engine makes three SQL passes: KB11125: How to control the join order of metrics in the final pass when using the "Do not do downward outer join" VLDB se… Number of Views 9. Data Management Programming Tools MicroStrategy, Inc. You are not really doing a Cartesian product; you are doing an INNER JOIN. You must have write access to the datasets you intend to link. CAUSE This is a known issue in MicroStrategy 10. Create another logical table (or view) for your prices: Full Outer Join Support: Support: Select/Insert: Bulk Insert String ; Select/Insert: UNION Multiple INSERT: Use UNION: Select/Insert: Distinct / Group By option: Use DISTINCT: Query Optimizations: Sub Query Type: Use temporary table; falling back to IN (SELECT col) for correlated subquery: Query Optimizations: SQL Global Optimization Click the Joins folder to expand it and then choose Full Outer Join Support. c2,t1. The key thing is, MS uses the exact opposite concept from the database outer join - I am not sure why, but say metric A you need for sure, metric B is optional, you need to set metric A as OUTER JOIN metric,. Rerun the report and it should bring back all the data you need. Another way is to use the Lowest level attribute in the report object pane. MicroStrategy has a VLDB (Very Large Database) setting that can be configured to indicate the underlying database software does not support Full Outer Join operation. Join common attributes (reduced) on both sides. Why MicroStrategy Why MicroStrategy; Customer Stories; PlatformHow to Define Join Behavior. It is essential to understand the process to get the data from the multiple tables. By default MicroStrategy uses inner join for your metrics. ttl_weight from rep_so a, rep_so_p1 b where a. key. However, in the third pass, when combining the results from the first two passes to generate data for Unit Sales, a full outer join between pass 1 and 2 is performed. There's a reason we're at the top of the list. RIGHT OUTER JOIN. There's a reason we're at the top of the list. Connectivity. Ranked #1 for Enterprise Analytics in 2022 Gartner Critical Capabilities Report. Join From Clause Order = Move MQ Table in normal FROM clause order to the last (for RedBrick) Full Outer Join Support = Outer-Join . It is denoted by symbol θ. As shown in the example above, all data for cost is aggregated together and shown for every row of a category. Perform the data aggregation first, then perform a left outer join to bring in all attribute elements. In MicroStrategy Workstation, right click on any cube created via Data Import and select Properties. Ranked #1 for Enterprise Analytics in 2022 Gartner Critical Capabilities Report. Description The . column_name = table2. RIGHT [OUTER] JOIN. This video helps the default join bw attribute-attribute metric to metric join type and Report level in MstrFor example, setting Metric Join type to "Outer" at the report level (Metric editor > Tool > Metric Join Type) overwrites the Metric Join Type set at the Database Instance Level: The subject of Outer Joins is a very broad one, however, as a continuation of the topics covered in this article, users might find it useful to refer to the following. Open MicroStrategy Developer. An outer join includes data that applies to all components. For information on how to display this property, see Viewing and Changing Advanced VLDB Properties. The join type places conditions on the data to be displayed in the report. There's a reason we're at the top of the list. Set the Attribute Join Behavior to "Outer Join - Preserve Filter" 4. The SQL standard defines three types of OUTER JOINs: LEFT, RIGHT, and FULL but SQLite supports only the natural LEFT OUTER JOIN. Having the following hierarchy: Country > Distribution Center > Calling Center > Full Outer Join Support is an advanced property that is hidden by default. To include the left outer join clause, follow the steps below: The following schema shows which setting takes precedence when set at different levels. DealSizeUnit should be a metric that an attribute. This attribute join type uses the second approach. x. Ranked #1 for Enterprise Analytics in 2022 Gartner Critical Capabilities Report. 0 for databases that do not support Full Outer Joins Number of Views 1. However, in the third pass, when combining the results from the first two passes to generate data for Unit Sales, a full outer join between pass 1 and 2 is performed. If the parent report is configured, under Report Data Options > Attribute Join Type, to preserve all lookup table elements joined to the final result table, then the target report will also preserve lookup table elements. 7. Login MicroStrategy Developer 2. Data can come from any source accessible by Microstrategy 8, including the data warehouse, data. In the future, please include the complete table definition in your questions (the CREATE TABLE part). This defect is addressed in MicroStrategy 11. In a MicroStrategy project using multisourcing, users may note that the VLDB property "Metric Join Type" for the primary database Instance is set to Outer Join. Check Query Details This document describes how to use the Logical View feature introduced in MicroStrategy to specify an outer join between two attribute lookup tables when only attributes are on a report. Full Outer Join Support is an advanced property that is. •. To Define a Join Between the Columns of Two Tables. In Web. For example: Metric (Default Inner Join):But I think MS SQL engine some how using table inserts to workaround it to complete half outer joins, but not full outer joins. Contrast this with an inner join. It’s because the Engine will do full outer join on the template Attributes when the Metric is defined on Attribute. In the following example, we will join emp and dept tables using joiner transformation in. Do you first pass and left outer (or Right or full, which ever floats your boat) to the second pass on the one column that you want. Hi, I have the following query in microstrategy report . Attribute to Join When Key From Neither Side can be Supported by the Other Side. id = Price. 0, the "MCE join to get data combination" setting is being retired for any project with Data Engine Version 11 or above. No support. Levels at which you can set. Extra SQL passes are generated when the metric join type is set to Outer Join and SQL Global Optimization is set to Level 4 in a report, we will remove the extra SQL passes to improve performance. Specifically if a metric at a higher level is set to outer join while a lower level metric is set to inner join while both include an attribute the lower level metrics version of the attribute will be used to join to lookup tables despite being less complete. This can cause outer joins. FULL JOIN. mstr file is actually a . 0 or above, the issue cannot be reproduced. [CATEGORY_ID] = a12. Null values for attribute IDs are not supported in MicroStrategy nor can outer joins be performed on attribute IDs. Navigate to the Intelligent Cube (Cube B) you want to add. CAUSE: In the example above, the final pass compares End_id of Temptable1 and End_id of Temptable2. Ranked #1 for Enterprise Analytics in 2022 Gartner Critical Capabilities Report. SELECT select_list FROM T1 FULL JOIN T2 ON join_predicate; Code language: SQL (Structured Query Language) (sql) In this syntax: First, specify the left table T1 in the FROM clause. All report metrics use outer join: No flags are generated. The following SQL statement selects all customers, and all orders: SELECT Customers. For attributes, the default join type is Outer Join. Sub queries. How to use an outer join to retrieve data when a metric on the report has a condition that is mutually exclusive with the report filter in MicroStrategy 10. There's a reason we're at the top of the list. 0. FULL OUTER JOIN. Here the filtered attributes are lost because of the outer join and all the years are shown on the report, but the metric is only calculated for 1997. However, if you go to DB instance > VLDB Setting > Joins > Full Outer Join Support, you will find that by default “No Support” is selected. Specifically if a metric at a higher level is set to outer join while a lower level metric is set to inner join while both include an attribute the lower level metrics version of the attribute will be used to join to lookup tables despite being less. Of course in your case no vehicle is a car and a bus too, so you need to tell MicroStrategy to do an outer joins for the two conditional metrics that you created. itemNo is not null AND Store. We can retrieve data from more than one tables using the JOIN statement. item_key. I would make sure that both metrics are configured as outer join. There are 2 rows in the Forecast table that have the same combination of Model, Month, Country, rows with ID 2 and 3: Why MicroStrategy Why MicroStrategy; Customer Stories; Platform As a result, the metrics' join types fall back to the project default, which is inner join for MicroStrategy Tutorial. For example, tutorial datasets may contain unrelated attributes and cross join metric values rather than joining the metric values. customer_id FULL JOIN vinyl v ON p. Connecting. CUSTOMER_ID (+); Now, I have created an attribute called customer Id at MSTR and defined the join (Through checking the box). Ranked #1 for Enterprise Analytics in 2022 Gartner Critical Capabilities Report. Glossary. Select the join type to use from the following options: A default join uses the joins already defined in each element of the compound metric. In this situation the presence of Year_date on the grid limits the data to just rows that exist in table 2 which is why the year 2016 is not seen. 9. No support; Support, which assumes that the Join Type VLDB setting is Join 92; any other value in Join Type is ignored; Use default inherited value. Full Outer Join . In 11. This can be done with a union of the IDs of the multiple intermediate tables that need to do an outer join and then using the union table to left outer join to all intermediate tables, so this approach generates more passes. There are mainly 4 different types of JOINS in SQL server. A simple idea would be to modify the metric by means of dimensionality, namely, the LEFT OUTER JOIN attributes with the setting ignore add / None. Beginning with MicroStrategy SQL Engine 9. A SQL statement joins back to the lookup table when using a logical view to create an outer join between attributes in MicroStrategy SQL Generation Engine. You can modify the join expression in one of two ways: Simple expression: This is the default. Go back to Public Objects-->Reports. I have one table with a column ID and SERVICE_TYPE_TEXT, and another table with columns. x. Left outer join for attribute is unavailable in Developer. This article describes an issue with column names when using global optimization with MicroStrategy SQL Engine 9. This target report inherits VLDB properties, including attribute outer join settings, from the parent report. Log into MicroStrategy Developer. Follow the steps below. Theta Join allows you to merge two tables based on the condition represented by theta. We will learn all JOINS in SQL server with examples: INNER JOIN/simple join. Great idea, but does not work! From a no apparent reason, no metric dimensioned in MicroStrategy can be “conducting”, ie standing in a LEFT OUTER JOIN on the left side. STEPS TO REPRODUCE: Create a custom group with two rows, region in Canada and region in center;This document describes how to use the Logical View feature introduced in MicroStrategy to specify an outer join between two attribute lookup tables when only attributes are on a report. Each basket stores zero or more fruits and each fruit can be stored in zero or one basket. BACKGROUND: Theta joins are not supported by MicroStrategy. Scenario 2. price FROM Book RIGHT JOIN Price ON Book. many-to-many and jointing child relationships; Logical Charts Size the lightweight way to understandI want implement an outer join in Microstrategy 7. Some report metrics use outer join, and others use inner join: This case is currently not subject to Global Optimization level 3 or 4. Database instance, report. 4. ID, SERVICE_TYPE. Ranked #1 for Enterprise Analytics in 2022 Gartner Critical Capabilities Report. 1/10. Refresh individual tables and check status of the table refresh is available MicroStrategy 10. This article explains how to control the join order of metrics when using downward outer join settings in MicroStrategy. In MicroStrategy 10. 4. This video demonstrates how to create custom SQL filters in MicroStrategy. column_name; 3. As shown in the example above, all data for cost is aggregated together and shown for every row of a category. How to use an outer join to retrieve data when a metric on the report has a condition that is mutually exclusive with the report filter in MicroStrategy 10.