This document describes how to use the Logical View feature introduced in Strategy to specify an outer join between two attribute lookup tables when only attributes are on a report. This method exists as attribute only outer joins will not be generated on their own by the Strategy SQL engine. This is because they are only necessary with ragged/unbalanced hierarchies which are not supported as null attribute IDs are not supported (parent elements with no child elements or child elements with no parents). Brief instructions are provided using the example below.
Two attributes: Parent01 and Child01 have a parent-child relationship. Their Lookup tables are defined, as follows
[PostgreSQL Unicode(x64)]
Driver=/opt/mstr/MicroStrategy/install/pgsql-11/lib/psqlodbcw.so
Description=ODBC for PostgreSQL
Setup=/opt/mstr/MicroStrategy/install/pgsql-11/lib/psqlodbcw.so
FileUsage=1
DriverUnicodeType=1[TPCH_PG]
Driver=/opt/mstr/MicroStrategy/install/pgsql-11/lib/psqlodbcw.so
Description=PostgreSQL Unicode(x64)
Database=tpch
Servername=xxx.xxx.xxx.xxx
Port=5432
Protocol=9.3
ReadOnly=No
RowVersioning=No
ShowSystemTables=No
ConnSettings=
DriverUnicodeType=1Amazon Redshift DatatypeMSTR DatatypeDATA TYPEMSTR TYPEDATEDATETIMETIME(6)SECONDTIMETIMESTAMP(6)TIMESTAMPTIMESTAMP(7)TINYINTINTEGER(1)SAMLLINTINTEGER(2)INTEGERINTEGER(4)BIGINTINTEGER(8)SMALLDECIMALDECIMAL(16,0)DECIMALDECIMAL(10,10)REALREALDOUBLEDOUBLEFLOAT(n)DOUBLEVARCHARVARCHAR(n)NVARCHARNVARCHAR(n)ALPHANUMVARCHAR(n)SHORTTEXTVARCHAR(n)VARBINARYVARBIN(5000)BLOBLONG VARBIN(0)CLOBLONG VARCHAR(0)NCLOBLONG VARCHAR(0)Parent01Child01
|
[PostgreSQL Unicode(x64)]
Driver=/opt/mstr/MicroStrategy/install/pgsql-11/lib/psqlodbcw.so
Description=ODBC for PostgreSQL
Setup=/opt/mstr/MicroStrategy/install/pgsql-11/lib/psqlodbcw.so
FileUsage=1
DriverUnicodeType=1[TPCH_PG]
Driver=/opt/mstr/MicroStrategy/install/pgsql-11/lib/psqlodbcw.so
Description=PostgreSQL Unicode(x64)
Database=tpch
Servername=xxx.xxx.xxx.xxx
Port=5432
Protocol=9.3
ReadOnly=No
RowVersioning=No
ShowSystemTables=No
ConnSettings=
DriverUnicodeType=1Amazon Redshift DatatypeMSTR DatatypeDATA TYPEMSTR TYPEDATEDATETIMETIME(6)SECONDTIMETIMESTAMP(6)TIMESTAMPTIMESTAMP(7)TINYINTINTEGER(1)SAMLLINTINTEGER(2)INTEGERINTEGER(4)BIGINTINTEGER(8)SMALLDECIMALDECIMAL(16,0)DECIMALDECIMAL(10,10)REALREALDOUBLEDOUBLEFLOAT(n)DOUBLEVARCHARVARCHAR(n)NVARCHARNVARCHAR(n)ALPHANUMVARCHAR(n)SHORTTEXTVARCHAR(n)VARBINARYVARBIN(5000)BLOBLONG VARBIN(0)CLOBLONG VARCHAR(0)NCLOBLONG VARCHAR(0)Parent01Child01
|
|
SQLData Returnedselect a12.parent01_ID parent01_ID,
a13.parent01_DESC parent01_DESC,
a12.child01_ID child01_ID,
a12.child01_ID child01_ID0
from child01 a12
join parent01 a13
on (a12.parent01_ID = a13.parent01_ID)
|
Note that although there are 4 ID values for the attribute Parent01, there is no defined relationship for the Parent01 element p4 (ID=4) in the Lookup table for attribute Child01. Placing both attributes on a report will result in the following SQL and data returned. Parent01's element p4 will not be returned. This is known as a ragged hierarchy and is not supported in Strategy.
SQLData Returnedselect a12.parent01_ID parent01_ID,
a13.parent01_DESC parent01_DESC,
a12.child01_ID child01_ID,
a12.child01_ID child01_ID0
from child01 a12
join parent01 a13
on (a12.parent01_ID = a13.parent01_ID)
|
SQLData Returnedselect a12.parent01_ID parent01_ID,
a13.parent01_DESC parent01_DESC,
a12.child01_ID child01_ID,
a12.child01_ID child01_ID0
from child01 a12
join parent01 a13
on (a12.parent01_ID = a13.parent01_ID)
|
SQLData Returnedselect a12.parent01_ID parent01_ID,
a13.parent01_DESC parent01_DESC,
a12.child01_ID child01_ID,
a12.child01_ID child01_ID0
from child01 a12
join parent01 a13
on (a12.parent01_ID = a13.parent01_ID)
|
SQLLogical View Definition
|
Using Strategy, create a Logical View with the following SQL. Note the Left Outer Join.
SQLLogical View Definition
|
SQLLogical View Definition
|
select a12.parent01_ID parent01_ID,
a12.parent01_DESC parent01_DESC,
a11.child01_ID child01_ID,
a11.child01_DESC child01_DESC
from parent01 a12
left outer join child01 a11
on (a11.parent01_ID = a12.parent01_ID)
|
Add Source TableAdd Relationship
|
Modify the attribute Child01 to include the new Logical View as a source table and redefine a relationship to attribute Parent01 using the new Logical View.
Add Source TableAdd Relationship
|
Add Source TableAdd Relationship
|
|
SQLData Returned
|
Executing the same report will now return all elements for Attribute Parent01.
SQLData Returned
|
SQLData Returned
|
select /* RRR01 */ a12.parent01_ID parent01_ID,
a12.child01_ID child01_ID
from (select a12.parent01_ID parent01_ID,
a12.parent01_DESC parent01_DESC,
a11.child01_ID child01_ID,
a11.child01_DESC child01_DESC
from parent01 a12
left outer join child01 a11
on (a11.parent01_ID = a12.parent01_ID)) a12
|
|
Internal reference number KB10818
KB10818
