KB36463: "SPU swap partition: Disk temporary work space is full" error message appears when publishing an Incremental Refresh cube in MicroStrategy Intelligence Server 9.2.x and newer against Netezza

• Strategy

The following knowledge base article describes an issue that could use to high temporary table space in databases. A specific example is provided when occurring against a Netezza warehouse but the issue itself is not specific to Netezza.

SYMPTOM:

"SPU swap partition: Disk temporary work space is full" error message appears when publishing an Incremental Refresh cube in Strategy Intelligence Server 9.2.x and newer against a Netezza database.


Error: SQL Generation CompleteQueryEngine encountered error: Execute Query failed.
Error type: Odbc error. Odbc operation attempted: SQLExecDirect. ERROR: SPU swap
partition: Disk temporary work space is full Connection String:

Though the error above is specific to Netezza, the problem as discussed below can effect other Databases as well. The error message will be different depending on the vendor however.

CAUSE:
The SQL generated for incremental refresh cubes is not optimal. The SQL contains complex joins on large temporary tables, as shown below, resulting in Netezza running out of temporary work space.


Sql
Select * 
from ZZMD00 pa11

join ZZMD01 pa12

  on  (pa11.REGION_ID = pa12.REGION_ID)

join ZZMD02 pa13

  on  (pa11.COUNTRY_ID = pa13.COUNTRY_ID and

pa11.DAY_DATE = pa13.DAY_DATE and

pa11.ITEM_ID = pa13.ITEM_ID and

pa11.REGION_ID = pa13.REGION_ID)

join LU_ITEM a14

  on  (pa11.ITEM_ID = a14.ITEM_ID)

join LU_SUBCATEG a15

  on  (a14.SUBCAT_ID = a15.SUBCAT_ID)

join ZZMD03 pa17

  on  (a15.CATEGORY_ID = pa17.CATEGORY_ID and

pa11.COUNTRY_ID = pa17.COUNTRY_ID and

pa11.DAY_DATE = pa17.DAY_DATE and

pa11.ORDER_ID = pa17.ORDER_ID and

pa11.REGION_ID = pa17.REGION_ID)

join ORDER_FACT a18

  on  (pa11.DAY_DATE = a18.ORDER_DATE and

pa11.ORDER_ID = a18.ORDER_ID)

join LU_DAY a19

  on  (a18.ORDER_DATE = a19.DAY_DATE and

pa11.DAY_DATE = a19.DAY_DATE)

join LU_COUNTRY a110

  on  (pa11.COUNTRY_ID = a110.COUNTRY_ID)

join LU_REGION a111

  on  (pa11.REGION_ID = a111.REGION_ID)

join LU_BRAND a112

  on  (a14.BRAND_ID = a112.BRAND_ID)

join LU_CUSTOMER a113

  on  (a18.CUSTOMER_ID = a113.CUSTOMER_ID)

join LU_MONTH a114

  on  (a19.MONTH_ID = a114.MONTH_ID)

join LU_CATEGORY a115

  on  (a15.CATEGORY_ID = a115.CATEGORY_ID and

pa17.CATEGORY_ID = a115.CATEGORY_ID)

join LU_QUARTER a116

  on  (a19.QUARTER_ID = a116.QUARTER_ID)

ACTION:

The complex joins can be broken down into smaller and simpler SQLs by using the "Normalize Intelligent Cube data in database using relationship tables (can provide improved performance in scenarios where Intelligent Cube data includes a large ratio of repeating data, dimensions include a large number of attributes, and attribute lookup tables are much smaller than fact tables)" VLDB property located in Data > VLDB Properties > Query Optimizations > Data Population for Intelligent Cubes.

Comment

0 comments

Details

Knowledge Article

Published:

May 17, 2017

Last Updated:

May 17, 2017

Error: SQL Generation CompleteQueryEngine encountered error: Execute Query failed. Error type: Odbc error. Odbc operation attempted: SQLExecDirect. ERROR: SPU swap partition: Disk temporary work space is full Connection String:

Sql Select * from ZZMD00 pa11 join ZZMD01 pa12 on (pa11.REGION_ID = pa12.REGION_ID) join ZZMD02 pa13 on (pa11.COUNTRY_ID = pa13.COUNTRY_ID and pa11.DAY_DATE = pa13.DAY_DATE and pa11.ITEM_ID = pa13.ITEM_ID and pa11.REGION_ID = pa13.REGION_ID) join LU_ITEM a14 on (pa11.ITEM_ID = a14.ITEM_ID) join LU_SUBCATEG a15 on (a14.SUBCAT_ID = a15.SUBCAT_ID) join ZZMD03 pa17 on (a15.CATEGORY_ID = pa17.CATEGORY_ID and pa11.COUNTRY_ID = pa17.COUNTRY_ID and pa11.DAY_DATE = pa17.DAY_DATE and pa11.ORDER_ID = pa17.ORDER_ID and pa11.REGION_ID = pa17.REGION_ID) join ORDER_FACT a18 on (pa11.DAY_DATE = a18.ORDER_DATE and pa11.ORDER_ID = a18.ORDER_ID) join LU_DAY a19 on (a18.ORDER_DATE = a19.DAY_DATE and pa11.DAY_DATE = a19.DAY_DATE) join LU_COUNTRY a110 on (pa11.COUNTRY_ID = a110.COUNTRY_ID) join LU_REGION a111 on (pa11.REGION_ID = a111.REGION_ID) join LU_BRAND a112 on (a14.BRAND_ID = a112.BRAND_ID) join LU_CUSTOMER a113 on (a18.CUSTOMER_ID = a113.CUSTOMER_ID) join LU_MONTH a114 on (a19.MONTH_ID = a114.MONTH_ID) join LU_CATEGORY a115 on (a15.CATEGORY_ID = a115.CATEGORY_ID and pa17.CATEGORY_ID = a115.CATEGORY_ID) join LU_QUARTER a116 on (a19.QUARTER_ID = a116.QUARTER_ID)