KB31095: How to force the MicroStrategy SQL Engine to generate SQL that distributes on all Attribute ID columns against Netezza

• Strategy

When creating a table in Netezza, the distribution key is used to determine the node on which each row of data is stored. That is, any table will be distributed across multiple nodes based upon the distribution key. An important factor in Netezza database design is selecting good distribution keys for tables.

By default, for reports involving intermediate tables, Strategy SQL Engine generates the 'DISTRIBUTE ON RANDOM' syntax, provided the following VLDB properties are set under the ‘Tables’ folder:

Intermediate Table Type = Permanent table or True temporary table
Table Creation Type = Explicit Table

This is illustrated by the following SQL:

CREATE TEMP TABLE ZZMD00(
CALL_CTR_ID SMALLINT,
WJXBFS1 FLOAT)
DISTRIBUTE ON RANDOM

If it is desired to have SQL generated that specifies a distribution key on all attribute ID columns (non-aggregatable columns that appear in the GROUP BY clause) that are used in the intermediate table, the following combination of VLDB settings may be used:

This report returns the average and sale for each . So we can see that the employees with the biggest average sales are in 'France' but the strongest regions are in 'USA'. Tables Intermediate Table Type Permanent table Tables Table Creation Type Explicit Tables Table Qualifier TEMP Tables Create Post String DISTRIBUTE ON (!a) Indexing Intermediate Table Index Don’t create an index	This report returns the average and sale for each . So we can see that the employees with the biggest average sales are in 'France' but the strongest regions are in 'USA'. Tables Intermediate Table Type Permanent table Tables Table Creation Type Explicit Tables Table Qualifier TEMP Tables Create Post String DISTRIBUTE ON (!a) Indexing Intermediate Table Index Don’t create an index	This report returns the average and sale for each . So we can see that the employees with the biggest average sales are in 'France' but the strongest regions are in 'USA'. Tables Intermediate Table Type Permanent table Tables Table Creation Type Explicit Tables Table Qualifier TEMP Tables Create Post String DISTRIBUTE ON (!a) Indexing Intermediate Table Index Don’t create an index
This report returns the average and sale for each . So we can see that the employees with the biggest average sales are in 'France' but the strongest regions are in 'USA'. Tables Intermediate Table Type Permanent table Tables Table Creation Type Explicit Tables Table Qualifier TEMP Tables Create Post String DISTRIBUTE ON (!a) Indexing Intermediate Table Index Don’t create an index	This report returns the average and sale for each . So we can see that the employees with the biggest average sales are in 'France' but the strongest regions are in 'USA'. Tables Intermediate Table Type Permanent table Tables Table Creation Type Explicit Tables Table Qualifier TEMP Tables Create Post String DISTRIBUTE ON (!a) Indexing Intermediate Table Index Don’t create an index	This report returns the average and sale for each . So we can see that the employees with the biggest average sales are in 'France' but the strongest regions are in 'USA'. Tables Intermediate Table Type Permanent table Tables Table Creation Type Explicit Tables Table Qualifier TEMP Tables Create Post String DISTRIBUTE ON (!a) Indexing Intermediate Table Index Don’t create an index
This report returns the average and sale for each . So we can see that the employees with the biggest average sales are in 'France' but the strongest regions are in 'USA'. Tables Intermediate Table Type Permanent table Tables Table Creation Type Explicit Tables Table Qualifier TEMP Tables Create Post String DISTRIBUTE ON (!a) Indexing Intermediate Table Index Don’t create an index	This report returns the average and sale for each . So we can see that the employees with the biggest average sales are in 'France' but the strongest regions are in 'USA'. Tables Intermediate Table Type Permanent table Tables Table Creation Type Explicit Tables Table Qualifier TEMP Tables Create Post String DISTRIBUTE ON (!a) Indexing Intermediate Table Index Don’t create an index	This report returns the average and sale for each . So we can see that the employees with the biggest average sales are in 'France' but the strongest regions are in 'USA'. Tables Intermediate Table Type Permanent table Tables Table Creation Type Explicit Tables Table Qualifier TEMP Tables Create Post String DISTRIBUTE ON (!a) Indexing Intermediate Table Index Don’t create an index
This report returns the average and sale for each . So we can see that the employees with the biggest average sales are in 'France' but the strongest regions are in 'USA'. Tables Intermediate Table Type Permanent table Tables Table Creation Type Explicit Tables Table Qualifier TEMP Tables Create Post String DISTRIBUTE ON (!a) Indexing Intermediate Table Index Don’t create an index	This report returns the average and sale for each . So we can see that the employees with the biggest average sales are in 'France' but the strongest regions are in 'USA'. Tables Intermediate Table Type Permanent table Tables Table Creation Type Explicit Tables Table Qualifier TEMP Tables Create Post String DISTRIBUTE ON (!a) Indexing Intermediate Table Index Don’t create an index	This report returns the average and sale for each . So we can see that the employees with the biggest average sales are in 'France' but the strongest regions are in 'USA'. Tables Intermediate Table Type Permanent table Tables Table Creation Type Explicit Tables Table Qualifier TEMP Tables Create Post String DISTRIBUTE ON (!a) Indexing Intermediate Table Index Don’t create an index
This report returns the average and sale for each . So we can see that the employees with the biggest average sales are in 'France' but the strongest regions are in 'USA'. Tables Intermediate Table Type Permanent table Tables Table Creation Type Explicit Tables Table Qualifier TEMP Tables Create Post String DISTRIBUTE ON (!a) Indexing Intermediate Table Index Don’t create an index	This report returns the average and sale for each . So we can see that the employees with the biggest average sales are in 'France' but the strongest regions are in 'USA'. Tables Intermediate Table Type Permanent table Tables Table Creation Type Explicit Tables Table Qualifier TEMP Tables Create Post String DISTRIBUTE ON (!a) Indexing Intermediate Table Index Don’t create an index	This report returns the average and sale for each . So we can see that the employees with the biggest average sales are in 'France' but the strongest regions are in 'USA'. Tables Intermediate Table Type Permanent table Tables Table Creation Type Explicit Tables Table Qualifier TEMP Tables Create Post String DISTRIBUTE ON (!a) Indexing Intermediate Table Index Don’t create an index

The following SQL is generated during the creation of intermediate tables after applying the above VLDB settings:

create TEMP table ZZMD00 (
CALL_CTR_ID SMALLINT,
WJXBFS1 FLOAT)
DISTRIBUTE ON (CALL_CTR_ID)

It can be seen that the distribution key is specified on CALL_CTR_ID which comprises the attribute ID column involved in the intermediate table.

If it is desired to have SQL generated that specifies a distribution key on all columns that are used in the intermediate table (and not just attribute ID columns), the same combination of VLDB settings may be used as above, except for replacing the wildcard !a by !!! as below:
Create Post String = DISTRIBUTE ON (!!!)

The following SQL is generated when the ‘Create Post String’ specified as above:

create TEMP table ZZMD00 (
CALL_CTR_ID SMALLINT,
WJXBFS1 FLOAT)
DISTRIBUTE ON (CALL_CTR_ID, WJXBFS1)

It can be seen that the distribution key is now specified on all columns involved in the intermediate table (CALL_CTR_ID, WJXBFS1).

Third Party Software Installation WARNING

The third-party product(s) discussed in this technical note is manufactured by vendors independent of Strategy. Strategy makes no warranty, express, implied or otherwise, regarding this product, including its performance or reliability.

Comment

0 comments

Details

Knowledge Article

Published:

May 26, 2017

Last Updated:

May 26, 2017