Also you can take advantage of Metric Dimensionality i. 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. Create a metric: Count([Household Fact]) Distinct=True with a metric filter of [Outbound Force Join]@ID=1. doc_id = b. 1 MicroStrategy wants to have a Dimensional model (forget about left join) and you need to explode slowly changing dimensions like your price table. Having the following hierarchy: Country > Distribution Center > Calling Center > Full Outer Join Support is an advanced property that is hidden by default. 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. key. I want to query so I can get the SERVICE_TYPE_TEXT from the first table that matches the ID given in the second. The SQL passes will then left outer join and give you the results you want. 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. So in 9. 11 and earlier releases. Full outer join support. In the future, please include the complete table definition in your questions (the CREATE TABLE part). 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. 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. Normal join In normal join, only matching rows are returned from both the sources. Too bad!There's a reason we're at the top of the list. The MicroStrategy SQL Generation Engine uses the COALESCE function to ensure correctness and consistency in full outer join results. MicroStrategy’s in-memory cube technology has been generally referred to as PRIME cubes since 10. Why MicroStrategy Why MicroStrategy; Customer Stories; PlatformSo the existence of GODWFLAG is expected as metric A and B are not independent from each other though they are all outer join metrics. However the VLDB property of "Metric Join Type" for the auxiliary database Instance is set to Inner join. SYMPTOM: If a report contains multiple metrics and more than one of the metrics has a join type of 'outer,' the MicroStrategy SQL Generation Engine 9. The join type places conditions on the data to be displayed in the report. solutions Forum; Outer Join on attributes. This article is to introduce a workaround to achieve the attribute left outer join on MicroStrategy Web. With MicroStrategy 10. 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. Go to Metric Options again and note that the metric join still says Outer Join. 0: The SQL for a Report shows that two identical tables do a full outer join twice. This preference is located under the Tools menu > My Preferences > Editors tab > Filter Options, in which the first option, "Show advanced qualification," should be checked. Set the visualization "Join Behavior" for "All Attributes" to "Outer Join - Preserve Filter" 7. SQL FULL OUTER JOIN example: In this example, the clause returns all rows from both the customers and orders tables. The default folder is: C:Program Files (x86)MicroStrategyProduct Name when installed on a 64-bit Windows environment. For information on how to display this property, see Viewing and Changing Advanced VLDB Properties. In SQL terms, the SQL Engine must perform a left outer join from the lookup table to the fact table. To group data into subsets, you can use the page-by feature. The options for this property are. Let’s study various types of Inner Joins: Theta Join. Create a metric out of the 3 columns you mentioned and outer join them. Go to Tools and enable Show Advanced Settings. The join type places conditions on the data to be displayed in the report. MicroStrategy provides ETL, data warehousing and data mining for data analysis. M,table_b. •. The table now appears in bold as shown below. ExamplesThis article uses sample data to show how to do a merge operation with the full outer join. Ranked #1 for Enterprise Analytics in 2022 Gartner Critical Capabilities Report. 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. Loading × Sorry to interruptA metric join setting determines how data is combined by applying a specific type of join, inner or outer. 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. x-10. This issue has been addressed in MicroStrategy 2021. 0 or later, SQL Engine creates necessary intermediate table(s) and uses left outer join to mimic the same behavior as full outer join. 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. Ranked #1 for Enterprise Analytics in 2022 Gartner Critical Capabilities Report. Prefix, QuantityofLicensesInstalled, Purchased. 9. Consider the two tables below:1. Maybe try running one report each with each metric and making sure that there really are valuse that you are missing. 29K. SQL Full Outer Join W/ Coalesce. QUARTER_ID QUARTER_ID, SAP HANA 1. When no matching rows exist for a row in the left table, the columns of the right table will have NULLs for those records. 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. No support. SYMPTOM: A user wishes to perform another level of aggregation on the results of a compound metric. 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. Do perform outer join on nested aggregation when all formulas have the same level: If all the inner metrics have the same level, which is lower than the report level, and the formula join type for the outer metric is set to outer, then an outer join is performed on the. In MicroStrategy SDK 9. An inner join is the widely used join operation and can be considered as a default join-type. Additional Final Pass Option is an advanced property that is hidden by default. Why MicroStrategy Why MicroStrategy; Customer Stories; Platform Prerequisite Before you can perform an outer join, you must allow outer joins to be supported. HAVING. 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. Description The . Join 89 was the earlier standard wherein the joins are performed in the WHERE clause. In the first drop-down list, select the attribute to which you are linking the. Change the Metric join type of the Revenue metric to Outer join in the Report Data Options, as shown below: The report should appear as shown below. Open MicroStrategy Web. The join is important as the table contains an attribute in the entry level and the attribute to which to extend. Join Type for "All Attributes" is set to "Outer Join - Preserve Filter" for the Visualization. LEFT JOIN; RIGHT JOIN; FULL JOIN; 1. To configure or remove the existing version of the product, use the Add/Remove Program feature in. 4. To pick up the fix, upgrade to MicroStrategy 2021 or above and enable project level setting "Data Engine Version" to 2021 or above. x, and thus there is no GUID. 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. Follow the steps below. Click Demo. RE. The sql statement for this metric is shown below. Downward Outer Join Option. In the last pass, an outer join is performed against the OL (Outer Join to Lookup) table. 0 for databases that do not support Full Outer Joins. 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. e. 1/10. To save the updated Cube A, click Save Progress. the inner part of a Venn diagram intersection. A table relation defines a join on tables. PIVOT. When VLDB settings are configured at the report level to allow for an outer join to the lookup table in MicroStrategy Developer, the SQL does not show an outer join. Result: Acts as one dataset. MicroStrategy is the leading enterprise business intelligence tool with its mission statement to make every enterprise a more intelligent enterprise. The general case of JOIN operation is called a Theta join. The following SQL statement selects all customers, and all orders: SELECT Customers. xThe Cartesian Join Governing setting is available under Joins. First develop a query that gets you as. Loading. 1 Answer. Discussion around the uses of full outer joins. In MicroStrategy SDK 9. Similar to scenario 1, three passes are generated. the outer parts of a Venn diagram union. INSERT, UPDATE, and DELETE on Views. Create a metric out of the 3 columns you mentioned and outer join them. In MicroStrategy, there are two settings that users can access to control Outer Join behavior: Formula Join Type and Metric Join Type. LEFT OUTER JOIN. Name it "Outbound Force Join". c3. For example, consider the relationship between three attributes: Promotion, Item, and Quarter. 1. I worked around it but din't make any difference. contact The problem here is the WHERE clause makes Left outer join useless. 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. MySQL does not have FULL-OUTER-JOIN syntax. 0 or above, the issue cannot be reproduced. Trending Articles. Another way is to use the Lowest level attribute in the report object pane. If the join type is changed, the SQL statements would also change. Go back to Public Objects-->Reports. Steps to Reproduce This procedure is using the MicroStrategy Tutorial warehouse that is shipped with MicroStrategy. Ranked #1 for Enterprise Analytics in 2022 Gartner Critical Capabilities Report. It arbitrarily matches rows from the two tables, with no duplicates. 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. ) From the Tools menu, select Show Advanced Settings. Thus, when inner joins are used, the order of intermediate tables does not matter to the final result. In this example, we will join emp and dept tables using joiner transformation. Duplication of results when using FULL OUTER JOIN / COALESCE. However, if the Full Outer Join Support is NO, then the left outer join is used to simulate a full outer join. 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. If the join type is changed, the SQL statements will change accordingly. Levels at Which You Can Set This . Solution: This issue has been addressed in MicroStrategy 2021. 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. The frequently used clause in JOIN operations is “ON”. In Microstrategy Tutorial Database, let us take three tables ORDER_FACT,LU_ORDER and LU_CUSTOMER. To support this capability, the Analytical Engine behavior with respect to join data from multiple datasets has an option to control the join behavior between datasets known as 'Join Behavior'. Data Management Programming Tools MicroStrategy, Inc. 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. Set the Attribute Join Behavior to "Outer Join - Preserve Filter" 4. By default, inner joins are generated against all metrics in a report. I'm assuming that a16. Loading data into sample_joins from Customers. 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. itemNo is not null AND Store. 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. Options for the VLDB property, Attribute to Join When Key From Neither Side can be Supported by the Other Side, can no longer be selected once you upgrade your data engine version to 2021. All report metrics use outer join: No flags are generated. Outer Join. There's a reason we're at the top of the list. If installed over previous versions of the product, the folder remains the same. You can define joins between columns on the Import from Tables dialog while building a query for importing your data. id) AS vinyl_count FROM customer c FULL JOIN purchase p ON c. The join types for a metric base formula are as follows: A default join is a join that is. N FROM table_A FULL OUTER JOIN table_B ON table_A. x, in a grid formed by template units of multiple datasets, all the elements from the primary dataset will be. x. You can right-click the line representing the join and select one of the four join types described above to modify the join type. And I have another attribute called Address from Address table which is straightforward. column_name = table2. Drag the column from the first table onto the column from the second table. There's a reason we're at the top of the list. Full Outer Join Support = Outer-Join . Create three attributes named A, B, C, and after them added in a report in Developer, no data returns， as showed below. UNION. Steps are as below. Ranked #1 for Enterprise Analytics in 2022 Gartner Critical Capabilities Report. Rows containing values for inner join metrics but not the outer join metric are dropped. This allows the FOJ to be rewritten to a different kind of join in the. 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. Here is the workaround to achieve the attribute left outer join on MicroStrategy Web. 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. 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. 0. The point here is that you can use this technique to develop a complex piece of SQL, and then use that SQL in a MicroStrategy logical view, so you can build metrics and attributes on top of that SQL. 4. Sub-Queries should only be used as a fallback solution when you cannot use a JOIN operation to achieve the aboveThere's a reason we're at the top of the list. I also created an additional dummy metric that serves the same purpose as introduction of dummy data within the data warehouse. price FROM Book RIGHT JOIN Price ON Book. 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. This article describes how SQL Global Optimization handles inner versus outer joins between metrics in MicroStrategy. Inner Join is further divided into three subtypes: 1) Theta join 2) Natural join 3) EQUI join. In short, an inner join includes only data that is common across all component of the join. 4. 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. 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. Video. ttl_weight from rep_so a, rep_so_p1 b where a. Similarly, when no matching rows exist for a row in the right. Users can switch the table order to achieve the same variation of outer joins (right and full outer join). I would make sure that both metrics are configured as outer join. KB441377: Full Outer Join Optimization implemented in MicroStrategy 11. For more details on enabling the setting, refer to Change Data Engine Version After the fix, metric outer join works and data returns. 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. 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. FULL OUTER JOIN Orders ON Customers. Improve this answer. itemkey = sub. Cross joins are seen in metrics when joining across unrelated attributes inside of data blending. Ranked #1 for Enterprise Analytics in 2022 Gartner Critical Capabilities Report. This is a guest blog post co-written by Amit Nayak at Microstrategy. This section focuses on the VLDB properties that are set at the metric and report level. Note: One-sided outer joins (left outer join, right outer join) are not commutative. 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. SQL Global Optimization; Metric Join Type; Steps to Reproduce. 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. Solution: SELECT first_name, last_name, COUNT (v. KB31133: Join conditions are omitted in the calculation of nested aggregation metrics in MicroStrategy SQL Generation Engine. 8. To include the left outer join clause, follow the steps below: The following schema shows which setting takes precedence when set at different levels. Join common attributes (reduced) on both sides. This is a known defect in MicroStrategy 10. Chapters. Community & Support Search Discussions Open A Case View My CasesRows containing values for inner join metrics but not the outer join metric are dropped. Add a filter condition based on Call Center attribute. INSERT. It is denoted by symbol θ. 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. Some report metrics use outer join, and others use inner join: This case is currently not subject to Global Optimization level 3 or 4. VLDB properties can provide support for unique configurations. The following window appears: Highlight the table 'LU_REGION' under 'Source tables' and click on the 'Set as Lookup' button. Ranked #1 for Enterprise Analytics in 2022 Gartner Critical Capabilities Report. For metrics, the default join type is Inner join. For this example, assume we have a order_fact table has two dates, order_date and ship_date and an invoice_fact table with two dates invoice_date and actual_ship_date. 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. In an inner join, only those tuples that satisfy the matching criteria are included, while the rest are excluded. NOTE: The aforementioned document and the example in this document use the logical view feature introduced in MicroStrategy 8. Click the "VLDB Properties. Levels at Which You Can Set This . The SQL passes will then left outer join and give you the results you want. SAP HANA 1. By selecting this option, the Join Type VLDB property is assumed to be Join 92 and any other setting in Join Type is ignored. 10 it is possible for users to view the query details of a visualization in a dossier. iolaper (MIS) (OP)However, for XPS, it is by default a left outer join. Controls whether tables are joined only on the common keys or on all common columns for each table. Procedure: Right-click on the Project name and select Project Configuration. 1. A. 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). It is essential to understand the process to get the data from the multiple tables. 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. A fact extension can be used to relate a fact to an attribute using a fact table. The downward outer join pass performs a cross join between the higher-level metric pass and the lookup table (s) for the attribute (s) included in the report. Result: Acts mostly as one dataset, but missing values are. Join common key on both sides. 0 for databases that do not support Full Outer Joins. 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. The fourth row gets the rank 4 because the RANK() function skips the rank 3. Levels at which you can set. This can cause outer joins. Nice Software Solutions (NSS) is a professional services company, focusing in Business Intelligence and Data Warehousing solutions. The date restriction is in the Where-clause which limits the output to customers with orders only. Invalid SQL is generated when level metrics are outer joined to non level metrics in MicroStrategy 10. This article describes how out joins ignore the filter in the final pass in MicroStrategy SQL Generation Engine 10. Two other VLDB properties, Downward Outer Join Option and Preserve All Lookup Table Elements, have an option to apply the filter. Here is the workaround to achieve the attribute left outer join on MicroStrategy Web. Taking the report above as an example, when the grouping for the "Products" user hierarchy is set to "standard", the SQL for the report is as. Follow the steps below. Perform the data aggregation first, then perform a left outer join to bring in all attribute elements. Support, which assumes that the Join Type VLDB setting is Join 92; any other value in Join Type is ignored. Go to Data > VLDB Properties. Things you can do to use MicroStrategy with your tables: Create a logical table in MicroStrategy (or a view on your database) like: select Itemkey,. It tells Oracle you want to see all records in. This article addresses a case which fails with a cross join when an unrelated filter is applied. There's a reason we're at the top of the list. DealSizeUnit should be a metric that an attribute. Then, this document addresses how to workaround this particular issue. . Inner and outer joins are discussed with examples. APPEND / UNION merges records from similar columns and removes duplicates. . Click OK. No specific customizations were made for this feature. Move the new Modified New Dossier back to the original location. 4. User can define the inner/outer join behavior through Report Data Option in each individual report. 0. Step 1 – Create a new target table EMP_DEPTNAME in the database using the below script and import the table in Informatica targets. x. The strength of NSS lies in its outstanding model. •. Advanced Settings. Create a new dataset. Metric Join Type: VLDB. You can modify the join expression in one of two ways: Simple expression: This is the default. 0. Full outer join. Do not listen to per report level setting, preserve elements of final pass according to the setting at attribute level. Select SQL Data Warehouses. CREATE TABLE fruits ( fruit_id INTEGER PRIMARY KEY , fruit_name. c1 = t2. In the Tutorial project, the user needs to create a report, for. Beginning with MicroStrategy 8. “USING” clause requires that matching columns be of the same name. 000' group by b. In short, an inner join includes only data that is common to all components of the join. All rows of the intermediate table are preserved. But MySQL also does not have a RIGHT JOIN syntax. 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. left outer join phonecall b on a. Clear the Use default inherited value check box. In the right corner of the dashboard, change the data source. The VLDB setting "SQL Global Optimization" is set to Level 4. Query Details also explains that the Attribute Outer Join is correctly applied, in blue below. This video helps the default join bw attribute-attribute metric to metric join type and Report level in Mstr. An inner join is created and a line representing the join appears in the Query Builder pane, running. contacttypeid where b. Inner and outer joins are discussed with examples. iolaper (MIS) (OP) However, for XPS, it is by default a left outer join. Example 1: Same attributes in both datasets, different metric, and same filter (same element values). Perform full outer joins; Check for Zeros and Nulls in the denominator while performing divisions in Metrics. 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. 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. There's a reason we're at the top of the list. Glossary. An outer join includes data that applies to all components. For this reason, two additional options are provided: Do not do downward outer join for. In the right corner of the dashboard,. x: The report returns the following: Notice in the screen shot above that the Data for Project B is missing. Steps are as below. Join common key on both sides. In this video I discuss when to use joint element lists in MicroStrategy and demonstrate how to create them. 99K subscribers Subscribe 4. 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. Then this might result in what you need. 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. KB441377: Full Outer Join Optimization implemented in MicroStrategy 11. Note that if you want to have consecutive ranks, you can use the DENSE_RANK() function. On the Freeform Sources tab, select one of the following options: To create an Intelligent Cube by creating SQL statements using the Freeform SQL Editor. xWhy MicroStrategy Why MicroStrategy; Customer Stories; PlatformNote: In MicroStrategy 11. As clearly shown in the output, the second and third rows share the same rank because they have the same value. Now my objective is to generate an SQL which will look like the following: select. Some report metrics use outer join, and others use inner join: This case is currently not subject to Global Optimization level 3 or 4. Upload FilesOr drop files. Creating a. Ranked #1 for Enterprise Analytics in 2022 Gartner Critical Capabilities Report. 2 (Database is Oracle 8. A. 0 SP2 supports FULL OUTER JOIN syntax between tables in the FROM clause. There's a reason we're at the top of the list. The properties are saved in different folders, as indicated in the previous list. Ranked #1 for Enterprise Analytics in 2022 Gartner Critical Capabilities Report. In Web. Downward Outer Join Option is an advanced property that is hidden by default. FROM t1 FULL OUTER JOIN t2 ON t1. 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. A ORDER BY table_A. Full Outer Join Support is an advanced property that is. Our vision is to enable Intelligence Everywhere. Prepare Data. For example, here are three tables in warehouse. 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. 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. Step 2) Loading and Displaying Data. In MicroStrategy Agent, go to Schema Objects. num. Plus, it connects business apps while integrating data. Using MicroStrategy Developer, log in to a project connected to an MDX cube source. A newer version of MicroStrategy Workstation is already installed. 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. It visualises interactive dashboards, charts and graphs and improves analytics and predictive modelling capabilities. This target report inherits VLDB properties, including attribute outer join settings, from the parent report. This article is to introduce a workaround to achieve the attribute left outer join on MicroStrategy Web. 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. SQL Full Outer Join. 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. To be more specific the product creates a metric dataset for the join between datasets and then outer joins that to the. Theta Join allows you to merge two tables based on the condition represented by theta. The join type places conditions on the data to be displayed in the report. In MicroStrategy 10. Two attributes: Parent01 and Child01 have a parent-child relationship. item_key. We are active contributors to solutions on the MicroStrategy Community site. The graphics for each type of join are based on two tables, like this: Inner Join. An SQL join statement is used to combine rows or information from two or more than two tables on the basis of a common attribute or field. This should happen only if you use the first approach. Database instance, report. This property may have two possible values: 0 for inner join and 1 for outer join. Pre/Post Statements Drop Database Connection=Do not drop database connection after running user defined SQL [when using pre/post SQL] Query Optimization 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 . The MicroStrategy SQL Generation Engine uses the COALESCE function to ensure correctness and consistency in full outer join results. Why MicroStrategy Why MicroStrategy; Customer Stories; PlatformThere's a reason we're at the top of the list.