From what you have described an outer join on the metrics seems to be the right thing to do. RIGHT OUTER JOIN. Set the Attribute Join Behavior to "Outer Join - Preserve Filter" 4. num. This issue has been addressed starting in MicroStrategy 9. 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. The Remove Repeated Tables For Outer Joins property determines whether an optimization for outer join processing is enabled or disabled. INNER JOIN. The subsets you separate your business data into are called pages, and you then page your way through the report, viewing one data subset at a time. You can right-click the line representing the join and select one of the four join types described above to modify the join type. Note that if there are other metrics on the report whose report level metric join type is set to 'inner join,' the report SQL will reflect inner joins and left outer joins depending on the reporting scenario. In reality, an . Use the Advanced Settings dialog to customize the SQL that MicroStrategy generates and determine how data is processed by the Analytical Engine. There's a reason we're at the top of the list. However, if you go to DB instance > VLDB Setting > Joins > Full Outer Join Support, you will find that by default “No Support” is selected. Ranked #1 for Enterprise Analytics in 2022 Gartner Critical Capabilities Report. Join common attributes (reduced) on both sides. Login MicroStrategy Developer 2. Here is the workaround to achieve the attribute left outer join on MicroStrategy Web. You can modify the join expression in one of two ways: Simple expression: This is the default. Our solutions work to guide towards best practices and provide guidance to the user community. Prefix, QuantityofLicensesPurchased, (QuantityofLicensesInstalled. The operation corresponds to a join operation in relational algebra. Go to Metric Options again and note that the metric join still says Outer Join. There's a reason we're at the top of the list. Ranked #1 for Enterprise Analytics in 2022 Gartner Critical Capabilities Report. Choose File > New > Intelligent Cube. If this is the case, then the engine must join the two tables, and this join can be an inner or an outer. This property only takes effect if the metric formula contains facts from different fact tables. The MicroStrategy product has been built from the ground up to focus on metrics and analysis of aggregated and calculated values. If the condition is moved to the outer join definition this query would produce customers without orders and customers that meet the condition on order date. KB31133: Join conditions are omitted in the calculation of nested aggregation metrics in MicroStrategy SQL Generation Engine. You are returned to the Project Configuration Editor. This is actually true for all ‘ OUTER ‘ joins. ID, SERVICE_TYPE. In Web. Null values for attribute IDs are not supported in MicroStrategy nor can outer joins be performed on attribute IDs. I want to query so I can get the SERVICE_TYPE_TEXT from the first table that matches the ID given in the second. 33 Responses to “Handling Conditions on Outer Joins”. This will help authors of dossiers embed workflows that provide consumers with options for guided analyses. The first pass calculates Sales; the second pass calculates Item Sold. Additional details about each property, including examples where necessary,. By default all the attributes are outer join in report data option. e. 4. Interact with the engaged MicroStrategy Community to get answers to all your questions and collaborate for solutions. c2,t1. Why MicroStrategy Why MicroStrategy; Customer Stories; PlatformHow to Define Join Behavior. The following procedure includes these steps. MicroStrategy is the leading enterprise business intelligence tool with its mission statement to make every enterprise a more intelligent enterprise. The concept is simple. . However, it is not a good practice to use the asterisk (*) in the SELECT statement when you embed SQL statements in the application code like Python, Java,. Full Outer Join Support = Outer-Join . You are returned to the Project Configuration Editor. MicroStrategy uses different defaults for the join type based on the database you are using. 12, with Data Engine Version 2019 or above, there are duplicated rows of data returned in some scenarios for a dossier/report/document which is created based on an intelligence cube and has the join behavior for attributes set to be Outer Join-Preserve Filter. 2/7/2019 3:34:07 PM select * from Left Outer Join. Note: One-sided outer joins (left outer join, right outer join) are not commutative. 9. Creating Subqueries;. If a document does not contain any primary datasets, all datasets are joined using inner joins. By default, MicroStrategy will use FULL OUTER JOIN when needed because in SAP HANA, queries that perform full outer joins between several tables perform better than when the Full Outer Join VLDB setting is set to No Support where the SQL Engine will generate SQL. id = Price. In the last pass, an outer join is performed against the OL (Outer Join to Lookup) table. By default, MicroStrategy will use FULL OUTER JOIN when needed because in SAP HANA, queries that perform full outer joins between several tables perform better than when the Full Outer Join VLDB setting is set to No Support where the SQL Engine will generate SQL that simulates full outer joins. Database instance, report. Follow the steps below. Steps to reproduce This example uses the MicroStrategy Tutorial warehouse that is shipped with MicroStrategy. Note that if there are other metrics on the report whose report level metric join type is set to 'inner join,' the report SQL will reflect inner joins and left outer joins depending on the reporting scenario. For attributes, the default join type is Outer Join. Video. Prepare Data. 11 and earlier releases. 99K subscribers Subscribe 4. How to use Guacamole to copy and paste text or files in MicroStrategy on AWS; All new Download site is now available!Community & Support Search Discussions Open A Case View My CasesFull Outer Join not returning unmatched join field values SQL SERVER 2005. name, Price. Change the join type from inner to outer. In MicroStrategy SDK 9. Normal join In normal join, only matching rows are returned from both the sources. There's a reason we're at the top of the list. Community & Support Search Discussions Open A Case View My CasesHow 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. This article addresses a case which fails with a cross join when an unrelated filter is applied. No support. For information on how to display this property, see Viewing and Changing Advanced VLDB Properties. 5. id UNION SELECT * FROM t1 RIGHT JOIN t2 ON t1. Join Base Table Join for Template = Use Fact Table . It is dynamically created when a new report is created in MicroStrategy MicroStrategy Developer 9. Those intermediate passes will then be merged in a last pass (if metric join type is set to outer then the last pass is a full outer join). A ORDER BY table_A. If you write your queries with just ‘ FULL JOIN ‘, SQL Server knows you want to do a FULL OUTER JOIN. In the case of SQL Server database instance, the intermediate passes will store the data into true temporary tables, as stated by the "Tables -> Intermediate Table Type" VLDB option. Steps are as below. Attribute to Join When Key From Neither Side can be Supported by the Other Side. CAUSE This is a known issue in MicroStrategy 10. Steps are as below. 1/10. This method exists as attribute only outer joins will not be generated on their own by the MicroStrategy SQL engine. 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. 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. x:The report returns the following: Notice in the screen shot above that the Data for Project B is missing. Given a choice, it is recommended to use a JOIN over a sub query. Create three attributes named A, B, C, and after them added in a report in Developer, no data returns， as showed below. 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. item_key. UNION. Issue with full outer join in SQL Server. In their own words, “MicroStrategy is the largest independent publicly traded business intelligence (BI) company, with the leading enterprise analytics platform. LEFT JOIN. Users can link the dossier to the following elements: • Any external URL. Example. 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. Click OK. If you created two facts with the condition inside than you don't need the conditional metrics and the outer join –This article describes how SQL Global Optimization handles inner versus outer joins between metrics in MicroStrategy. the outer parts of a Venn diagram union. Tip #1: The word ‘ OUTER ‘ is optional. Scenario 2. Too bad!Here is a list of some tips and tricks you should know about the FULL OUTER JOIN. A join clause in the Structured Query Language combines columns from one or more tables into a new table. (See Example 1: Same attributes, same filter) Example 2: Same attributes with different element values. 4. In short, an inner join includes only the data common to all the elements in the join, whether that data is tables or metrics. FULL OUTER JOIN. 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. VLDB properties allow you to customize the SQL that MicroStrategy generates, and determine how data is processed by the Analytical Engine. Date functions specific to different databases (YTD) transmutations lacking using transformation. Ranked #1 for Enterprise Analytics in 2022 Gartner Critical Capabilities Report. Preserve All Final Pass Result Elements. So in 9. The Project-Level Very Large Database (VLDB) Property setting, found in Joins > Preserve All Lookup Table Elements, 'Preserve lookup table elements joined to final pass result table based on template attributes with filter' is not turned on, which results in. In MicroStrategy SDK 9. The MicroStrategy SQL Generation Engine uses the COALESCE function to ensure correctness and consistency in full outer join results. 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. key. 0. KB310329: How to achieve attribute left outer join effect in MicroStrategy Web. Preserve common elements of lookup and final pass result table This attribute. For example: Metric, by default is Inner Join. STEPS TO REPRODUCE Connect a Snowflake database instance to Microstrategy. For information on how to display this property, see Viewing and Changing Advanced VLDB Properties. FULL OUTER JOIN Orders ON Customers. Step 2) From the navigation bar on the left- Click Databases. Here you will see your Metrics. To understand Downward Outer Join, consider the following report that contains the attribute Store and two metrics, Sales Per Store (M1). Create a metric out of the 3 columns you mentioned and outer join them. Beginning with MicroStrategy SQL Engine 9. SQL Full Outer Join. Cross Join. 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. Click the "VLDB Properties. In the right corner of the dashboard, change the. Join common key on both sides. Some report metrics use outer join, and others use inner join: This case is currently not subject to Global Optimization level 3 or 4. In the right corner of the dashboard,. Jan 26, 2013 at 19:40. Join common key on both sides. ACTIONThe downward outer join algorithm introduces a cost, in terms of performance and an inflated number of result rows, in exchange for improved data reliability. txt. There's a reason we're at the top of the list. LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table. Create a metric out of the 3 columns you mentioned and outer join them. KB483322: Two identical tables for a full outer join twice when metric join type is set to Outer Join and SQL Global OptimOuter join: Left Outer Join; Right Outer Join; Full Outer Join; Inner Join. To perform an outer join against a lookup table Open the Project Configuration Editor. c3. Full Outer Join Support is an advanced property that is. x: The report returns the following: Notice in the screen shot above that the Data for Project B is missing. This article describes how to create an outer join between two attributes. Theta Join allows you to merge two tables based on the condition represented by theta. 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. ttl_weight from rep_so a, rep_so_p1 b where a. 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. We specialize in MicroStrategy and are expert in producing quicker, impactful and user-friendly analytics within Data Warehousing and Business Intelligence domains. The join is important as the table contains an attribute in the entry level and the attribute to which to extend. And I have another attribute called Address from Address table which is straightforward. The MicroStrategy SQL Generation Engine uses the COALESCE function to ensure correctness and consistency in full outer join results. This article is to introduce a workaround to achieve the attribute left outer join on MicroStrategy Web. Here is the workaround to achieve the attribute left outer join on MicroStrategy Web. CustomerID. [CATEGORY_ID])) To be able to view all elements of category, including those from the metric pass that are not present in the Category lookup, the SQL statement needs a left outer join clause. 2 (Database is Oracle 8. The table now appears in bold as shown below. Add a filter condition based on Call Center attribute. This causes multiple counting in the report level aggregation, and also the discrepancy between the Income Bracket IDs and descriptions. column_name; 3. Loading. There's a reason we're at the top of the list. NATURAL JOIN. Downward Outer Join Option. Inner Join is further divided into three subtypes: 1) Theta join 2) Natural join 3) EQUI join. With MicroStrategy 10. This issue has been addressed in MicroStrategy 2021. OFFSET and FETCH. This attribute join type uses the second approach. For attributes, the default join type is the outer join. left outer join phonecall b on a. This article describes how out joins ignore the filter in the final pass in MicroStrategy SQL Generation Engine 10. 52K KB440718: Aggregate from base derived metric with an attribute to ignore filter does not get ignored in the metric calcula… Select VLDB Properties from the Data menu to access the VLDB Properties (Report) dialog box. Levels at which you can set. xThe Cartesian Join Governing setting is available under Joins. This article is to introduce a workaround to achieve the attribute left outer join on MicroStrategy Web. INSERT. However, this results in poorer performance with a. Also you can take advantage of Metric Dimensionality i. The Full Outer Join Support interface opens. Limitations: With Join 89, a table could not be outer-joined to more than one other table. From the Connect to Your Data window, choose MicroStrategy Datasets. 58K. Let us take two tables to demonstrate full outer join: table_A. If two columns both have null values, those columns are not recognized as "Equal". Then this might result in what you need. SYMPTOM. CustomerID=Orders. doc_id = b. The FULL OUTER JOIN command returns all rows when there is a match in either left table or right table. NOTE: The aforementioned document and the example in this document use the logical view feature introduced in MicroStrategy 8. Chapters. You can apply an inner or outer join, which are described in more detail below. solutions Forum; Outer Join on attributes. Open MicroStrategy Developer. This article describes an issue with column names when using global optimization with MicroStrategy SQL Engine 9. This is a known defect in MicroStrategy 10. There's a reason we're at the top of the list. There's a reason we're at the top of the list. If the cross join is not needed and the fact extension is required to take effect, change the grouping by "None" to "Standard" or remove the related attribute or user hierarchy from the level metric. 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. x A report defined as shown below returns no data in Microstrategy 10. Ranked #1 for Enterprise Analytics in 2022 Gartner Critical Capabilities Report. Similar to scenario 1, three passes are generated. zip file, with the. SQL Join statement is used to combine data or rows from two or more tables based on a common field between them. Two attributes: Parent01 and Child01 have a parent-child relationship. Inner Join : When the inner join is used, it considers only those attributes that we want to match both the table and, if anything that doesn’t, wouldn’t be included in our result table. Scenario 2. We will use the employees and departments. In short, an inner join includes only data that is common to all components of the join. Things you. The . I am simulated the problem in Microstrategy Tutorial. You can define how these elements are joined using the Metric Formula Join Type dialog box. In this situation a full outer join is done between the two tables to get a full set of elements of the year_ID attribute . Full Outer Join : The full outer Join keyword returns all records when there is a match in left or right table records. This allows the FOJ to be rewritten to a different kind of join in the. A metric join setting determines how data is combined by applying a specific type of join, inner or outer. As shown in the example above, all data for cost is aggregated together and shown for every row of a category. mstr file is a zipped file of object definition package and cube files. If two columns both have null values, those columns are not recognized as "Equal". Therefore, attributes with null values for the ID will not be behave properly in the MicroStrategy product. contacttypeid where b. To perform an outer join against a lookup table Open the Project Configuration Editor. 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. 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. The date restriction is in the Where-clause which limits the output to customers with orders only. ) From the Tools menu, select Show Advanced Settings. Open the VLDB Properties Editor to display the VLDB properties for the level at which you want to work. 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. Ranked #1 for Enterprise Analytics in 2022 Gartner Critical Capabilities Report. Loading. This article is to introduce a workaround to achieve the attribute left outer join on MicroStrategy Web. However, Full outer join is not supported when Join Type = 89, so the SQL is generated as inner join when Full outer join type = Yes. Full Outer Join Support is an advanced property that is hidden by default. 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. For example, if a report showing your profit data is. Full Outer Join Support is an advanced property that is. This should happen only if you use the first approach. This is a guest blog post co-written by Amit Nayak at Microstrategy. The default folder is: C:Program Files (x86)MicroStrategyProduct Name when installed on a 64-bit Windows environment. An inner join is a standard type of join. 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. x may need to. 0 for databases that do not support Full Outer Joins A full outer join B full outer join C does not equal C full outer join A full outer join B. Click the Joins folder to expand it and then choose Full Outer Join Support. Refresh individual tables and check status of the table refresh is available MicroStrategy 10. 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. The following descriptions use an example of a report containing the Region attribute, the Sales metric, and the Budget metric: By default, inner joins are generated for all metrics in a report. FULL JOIN. ago. calldate='2004-08-24 10:29:42. It is denoted by symbol θ. 52K KB440718: Aggregate from base derived metric with an attribute to ignore filter does not get ignored in. 7. id GROUP BY first_name, last_name; Explanation: This is an example of using FULL JOIN as well as joining more than two tables. A Venn diagram representing the full join SQL statement between tables A and B. For example, in the MicroStrategy Tutorial project, the user creates the following objects: Metric : Sum (Revenue). LEFT OUTER JOIN. To Define a Join Between the Columns of Two Tables. Reports that generate certain type of outer joins - One example is a simulated outer join, that pull data from two separate intermediate tables but require the results to be inserted into another table to perform the outer join logic. Invalid SQL is generated when level metrics are outer joined to non level metrics in MicroStrategy 10. Perform a left outer join first to keep all attribute elements at the Store, Day, and Item level, then aggregate the data to the Store and Month level. 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. I'm assuming that a16. Ranked #1 for Enterprise Analytics in 2022 Gartner Critical Capabilities Report. You are not really doing a Cartesian product; you are doing an INNER JOIN. x-10. The SQL you get will be:Syntax : SELECT column_name (s) FROM table1 RIGHT JOIN table2 ON table1. x-10. CREATE TEMPORARY TABLE. A. ShelfNo = Department. Procedure: Right-click on the Project name and select Project Configuration. The following descriptions use an example of a report containing the Region attribute, the Sales metric, and the Budget. VLDB properties can provide support for unique configurations. c2 FULL OUTER JOIN t3 ON COALESCE(t2. Let’s study various types of Inner Joins: Theta Join. 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. To pick up the fix, upgrade to MicroStrategy 2021 or above and enable project level setting "Data Engine Version" to 2021 or above. By selecting this option, the Join Type VLDB property is assumed to be Join 92 and any other setting in Join Type is ignored. ttl_weight from rep_so a, rep_so_p1 b where a. In SQL terms, the SQL Engine must perform a left outer join from the lookup table to the fact table. You can modify the join expression in one of two ways: Simple expression: This is the default. Steps are as below. Ranked #1 for Enterprise Analytics in 2022 Gartner Critical Capabilities Report. Ranked #1 for Enterprise Analytics in 2022 Gartner Critical Capabilities Report. ( How?) Expand Database instances. This filter must be applied in the final pass to find the correct store-region relationship, even though the final pass is a normal join instead of a downward join. Inner joins versus outer joins. Levels at Which You Can Set This . In short, an inner join includes only data that is common to all components of the join. Ranked #1 for Enterprise Analytics in 2022 Gartner Critical Capabilities Report. The Link Attributes Dialog opens. The Cartesian Join Governing setting is available under Joins. (For information on object levels, see Order of Precedence . That will help people help you. No PostgreSQL specific customizations were made for this feature. 29K. PIVOT. “USING” clause requires that matching columns be of the same name. 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. An inner join is created and a line representing the join appears in the Query Builder pane, running. Here is the workaround to achieve the attribute left outer join on MicroStrategy Web. Microstrategy Desktop can automatically join data from multiple different sources in the same report document. Result: Acts as one dataset. All report metrics use outer join: No flags are generated. Creating a. By default MicroStrategy uses inner join for your metrics. Join common key on both sides. For example, consider the relationship between three attributes: Promotion, Item, and Quarter. You can define join behavior through Report Data Option in each individual report in design view. x, in a grid formed by template units of multiple datasets, all the elements from the primary dataset will be. FROM t1 FULL OUTER JOIN t2 ON t1. APPEND / UNION merges records from similar columns and removes duplicates. MicroStrategy provides ETL, data warehousing and data mining for data analysis. 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. Perform the steps in this article to change the metric join type from the product default (Inner) to Outer for all reports in the project. 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. Let’s take an example of using the FULL OUTER JOIN clause to see how it works. customer_id FULL JOIN vinyl v ON p. This article describes how SQL Global Optimization handles inner versus outer joins between metrics in MicroStrategy. 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. Perform a left outer join first to keep all attribute elements at the Store, Day, and Item level, then aggregate the data to the Store and Month level. Instead of listing all columns in the SELECT clause, we just used the asterisk (*) to save some typing. Step 1) Creation of table “sample_joins” with Column names ID, Name, Age, address and salary of the employees. MicroStrategy Intelligence Server uses an ODBC Call (SQLDescribeCol) to determine the proper resources to allocate for the query results for VARCHAR columns this will lead to performance degradation. id. In this video I discuss when to use joint element lists in MicroStrategy and demonstrate how to create them. RIGHT JOIN. 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. If you are finished adding to Cube A, click Update Dataset. Another way is to use the Lowest level attribute in the report object pane. The MicroStrategy SQL Engine applies the designated join type to the data pulled from your data source's tables. This defect is addressed in MicroStrategy 11. ttl_qty, b. 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. EQUI JOIN; SELF JOIN; 2. The SQL standard defines three types of OUTER JOINs: LEFT, RIGHT, and FULL but SQLite supports only the natural LEFT OUTER JOIN. In MicroStrategy Analytics Enterprise 10. 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. 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. Plus, it connects business apps while integrating data. select a11. id) AS vinyl_count FROM customer c FULL JOIN purchase p ON c. The Full Outer Join Support setting specifies if the database platform supports full outer join syntax. x-10. r/PowerBI. For these databases, MicroStrategy implements a technique of analyzing each metric's result pass to obtain all the distinct attribute elements that are required to preserve the rows. There's a reason we're at the top of the list. Countries: This is a reference table with the. All report metrics use outer join: No flags are generated. SELECT Installed. Ranked #1 for Enterprise Analytics in 2022 Gartner Critical Capabilities Report. 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. FULL JOIN makes duplicate records for each match on either side. For metrics, the default join type is Inner join. FROM Table-1 LEFT OUTER JOIN Table-2ON (col1 = col2) <WHERE condition>; You can replace LEFT OUTER JOIN by RIGHT OUTER JOIN or FULL OUTER JOIN for the respective output. Click Demo. Solution: This issue has been addressed in MicroStrategy 2021. Starting in MicroStrategy 11. Log into MicroStrategy Developer. CountryID is a whole number value that represents the unique identifier from the Countries table.