When using the OLAP Analyzer in SAS Enterprise Guide, cube view actions such as adding a dimension or expanding a dimension level have slow performance. By default, when more than one dimension is displayed on either the row or column axis, SAS Enterprise Guide uses the CROSSJOIN function with NON EMPTY specifiers.
Here is an example of a default query generated by the SAS Enterprise Guide cube viewer:
SELECT NON EMPTY CrossJoin({
[Geographic].[All Geographic].Children }, {
[Measures].[PREDICTSUM] }) PROPERTIES MEMBER_CAPTION, PARENT_UNIQUE_NAME
ON COLUMNS, NON EMPTY CrossJoin({
[ProductLine].[All ProductLine].Children }, {
[Time].[All Time].Children }) PROPERTIES MEMBER_CAPTION, PARENT_UNIQUE_NAME
ON ROWS
FROM [Prdcube]
In contrast, the NONEMPTYCROSSJOIN function usually has better performance. Here is an example of the same query generated by the SAS Enterprise Guide cube viewer with the updated configuration option:
SELECT NonEmptyCrossJoin({
[Geographic].[All Geographic].Children }, {
[Measures].[PREDICTSUM] }) PROPERTIES MEMBER_CAPTION, PARENT_UNIQUE_NAME
ON COLUMNS, NonEmptyCrossJoin({
[ProductLine].[All ProductLine].Children }, {
[Time].[All Time].Children }) PROPERTIES MEMBER_CAPTION, PARENT_UNIQUE_NAME
ON ROWS
FROM [Prdcube]
Click the Hot Fix tab in this note to access the hot fix for this issue. After the hot fix is applied, you can set a property so that SAS Enterprise Guide uses the NONEMPTYCROSSJOIN function, which should improve performance. To use the property, perform the following steps. (These steps are also in the post-installation instructions for the hot fix.)
If you are SAS Enterprise Guide 32-bit, navigate to .\Program Files (x86)\SASHome\x86\SASEnterpriseGuide\7.1
If you are SAS Enterprise Guide 64-bit, navigate to .\Program Files\SASHome\SASEnterpriseGuide\7.1
<appSettings>
<add key="UseNonEmptyCrossJoinFunction" value="true" />
</appSettings>
Product Family | Product | System | Product Release | SAS Release | ||
Reported | Fixed* | Reported | Fixed* | |||
SAS System | SAS Enterprise Guide | Microsoft Windows Server 2016 | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 |
Windows 7 Enterprise 32 bit | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 | ||
Windows 7 Enterprise x64 | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 | ||
Windows 7 Home Premium 32 bit | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 | ||
Windows 7 Home Premium x64 | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 | ||
Windows 7 Professional 32 bit | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 | ||
Windows 7 Ultimate 32 bit | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 | ||
Windows 7 Professional x64 | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 | ||
Windows 7 Ultimate x64 | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 | ||
64-bit Enabled AIX | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 | ||
64-bit Enabled Solaris | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 | ||
HP-UX IPF | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 | ||
Linux for x64 | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 | ||
Microsoft Windows Server 2012 Std | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 | ||
Microsoft Windows Server 2012 R2 Std | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 | ||
Microsoft Windows Server 2012 R2 Datacenter | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 | ||
Microsoft Windows Server 2012 Datacenter | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 | ||
Microsoft Windows Server 2008 for x64 | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 | ||
Microsoft Windows Server 2008 R2 | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 | ||
Microsoft Windows Server 2008 | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 | ||
Microsoft Windows 10 | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 | ||
Microsoft Windows 8.1 Pro x64 | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 | ||
Microsoft Windows 8.1 Pro 32-bit | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 | ||
Microsoft Windows 8.1 Enterprise x64 | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 | ||
Microsoft Windows 8.1 Enterprise 32-bit | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 | ||
Microsoft Windows 8 Pro x64 | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 | ||
Microsoft Windows 8 Pro 32-bit | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 | ||
Microsoft Windows 8 Enterprise x64 | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 | ||
Microsoft Windows 8 Enterprise 32-bit | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 | ||
Microsoft® Windows® for x64 | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 | ||
z/OS | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 | ||
Solaris for x64 | 7.15 | 8.1 | 9.4 TS1M5 | 9.4 TS1M5 |
A fix for this issue for SAS Enterprise Guide 7.15 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/B4N.html#62106Type: | Problem Note |
Priority: | high |
Date Modified: | 2018-04-10 09:40:48 |
Date Created: | 2018-04-09 10:57:29 |