KB3775: The SQL for a MicroStrategy Report displays '1' instead of a COUNT of facts

• Strategy

This article notes a situation that can occur with count optimization

SYMPTOM:

When a user runs a Strategy Report that includes a COUNT(Fact) metric and attributes at the lowest level of their respective dimensions and/or the ID's for those attributes are the only attributes included in the fact table, the SQL appears similar to the following:

select a22.STORE_ID STORE_ID,
a22.STORE_DESC STORE_DESC,
a21.SN_ID SN_ID,
1 COUNTOFCOS
from FACT_SN_STORE a21,
LU_STORE a22
where a21.STORE_ID = a22.STORE_ID

instead of the following:

select a22.STORE_ID STORE_ID,
max(a22.STORE_DESC) STORE_DESC,
a21.SN_ID SN_ID,
count(a21.COST_AMT) COUNTOFCOS
from FACT_SN_STORE a21,
LU_STORE a22
where a21.STORE_ID = a22.STORE_ID
group by a22.STORE_ID,
a21.SN_ID

CAUSE:

To return the above SQL, the template contains the attributes Store and Serial Number and the metric COUNT(Cost_Amt):

The fact table FACT_SN_STORE has either of these structure:
1.)

Web Professional and OLAP Services
Startup type: AutomaticStartup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Running Server shutdown abnormally before system reboot Running Running Startup type: Automatic Startup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Stopped Server shutdown abnormally before system reboot Running StoppedFACT_SN_STORESTORE_IDSN_IDCOST_AMTFACT_SN_STOREYEAR_IDSTORE_IDSN_IDCOST_AMTYEAR_IDSTORE_IDSN_IDCOST_AMT199710020051998100200619982002007STORESERIAL NUMBERCOUNT (COST_AMT)100200110020012002001

Store, Class, and Item are the only attributes related to this table, so the SQL Generation Engine assumes that they constitute the primary key for this table. Therefore, the metric display is '1' for each row and the SQL does not contain a Group By expression.

Performing a COUNT(Fact) requires querying the fact table, which can be an expensive process. Therefore, if the SQL contains a fixed number, the fact table is not queried, resulting in a more efficient process.

2.)

The physical fact table contains additional attributes that are not used as an attribute or fact within Strategy. (This corresponds to Year_Id in the above example.) The logical table view in Strategy therefore does not consider Year_Id as key of the fact table even though it is part of the primary key of the physical fact table. As a result, Store and Serial Number are considered to be the only attributes related to this table, which causes SQL without a Group By expression. Furthermore the SQL will contain a constant of '1' as the count of Cost Amount because of performance reasons as mentioned above in Situation 1.

Below is a sample record set for the second configuration:

Web Professional and OLAP Services Startup type: AutomaticStartup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Running Server shutdown abnormally before system reboot Running Running Startup type: Automatic Startup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Stopped Server shutdown abnormally before system reboot Running StoppedFACT_SN_STORESTORE_IDSN_IDCOST_AMTFACT_SN_STOREYEAR_IDSTORE_IDSN_IDCOST_AMTYEAR_IDSTORE_IDSN_IDCOST_AMT199710020051998100200619982002007STORESERIAL NUMBERCOUNT (COST_AMT)100200110020012002001	Web Professional and OLAP Services Startup type: AutomaticStartup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Running Server shutdown abnormally before system reboot Running Running Startup type: Automatic Startup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Stopped Server shutdown abnormally before system reboot Running StoppedFACT_SN_STORESTORE_IDSN_IDCOST_AMTFACT_SN_STOREYEAR_IDSTORE_IDSN_IDCOST_AMTYEAR_IDSTORE_IDSN_IDCOST_AMT199710020051998100200619982002007STORESERIAL NUMBERCOUNT (COST_AMT)100200110020012002001	Web Professional and OLAP Services Startup type: AutomaticStartup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Running Server shutdown abnormally before system reboot Running Running Startup type: Automatic Startup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Stopped Server shutdown abnormally before system reboot Running StoppedFACT_SN_STORESTORE_IDSN_IDCOST_AMTFACT_SN_STOREYEAR_IDSTORE_IDSN_IDCOST_AMTYEAR_IDSTORE_IDSN_IDCOST_AMT199710020051998100200619982002007STORESERIAL NUMBERCOUNT (COST_AMT)100200110020012002001	Web Professional and OLAP Services Startup type: AutomaticStartup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Running Server shutdown abnormally before system reboot Running Running Startup type: Automatic Startup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Stopped Server shutdown abnormally before system reboot Running StoppedFACT_SN_STORESTORE_IDSN_IDCOST_AMTFACT_SN_STOREYEAR_IDSTORE_IDSN_IDCOST_AMTYEAR_IDSTORE_IDSN_IDCOST_AMT199710020051998100200619982002007STORESERIAL NUMBERCOUNT (COST_AMT)100200110020012002001
Web Professional and OLAP Services Startup type: AutomaticStartup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Running Server shutdown abnormally before system reboot Running Running Startup type: Automatic Startup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Stopped Server shutdown abnormally before system reboot Running StoppedFACT_SN_STORESTORE_IDSN_IDCOST_AMTFACT_SN_STOREYEAR_IDSTORE_IDSN_IDCOST_AMTYEAR_IDSTORE_IDSN_IDCOST_AMT199710020051998100200619982002007STORESERIAL NUMBERCOUNT (COST_AMT)100200110020012002001	Web Professional and OLAP Services Startup type: AutomaticStartup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Running Server shutdown abnormally before system reboot Running Running Startup type: Automatic Startup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Stopped Server shutdown abnormally before system reboot Running StoppedFACT_SN_STORESTORE_IDSN_IDCOST_AMTFACT_SN_STOREYEAR_IDSTORE_IDSN_IDCOST_AMTYEAR_IDSTORE_IDSN_IDCOST_AMT199710020051998100200619982002007STORESERIAL NUMBERCOUNT (COST_AMT)100200110020012002001	Web Professional and OLAP Services Startup type: AutomaticStartup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Running Server shutdown abnormally before system reboot Running Running Startup type: Automatic Startup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Stopped Server shutdown abnormally before system reboot Running StoppedFACT_SN_STORESTORE_IDSN_IDCOST_AMTFACT_SN_STOREYEAR_IDSTORE_IDSN_IDCOST_AMTYEAR_IDSTORE_IDSN_IDCOST_AMT199710020051998100200619982002007STORESERIAL NUMBERCOUNT (COST_AMT)100200110020012002001	Web Professional and OLAP Services Startup type: AutomaticStartup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Running Server shutdown abnormally before system reboot Running Running Startup type: Automatic Startup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Stopped Server shutdown abnormally before system reboot Running StoppedFACT_SN_STORESTORE_IDSN_IDCOST_AMTFACT_SN_STOREYEAR_IDSTORE_IDSN_IDCOST_AMTYEAR_IDSTORE_IDSN_IDCOST_AMT199710020051998100200619982002007STORESERIAL NUMBERCOUNT (COST_AMT)100200110020012002001
Web Professional and OLAP Services Startup type: AutomaticStartup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Running Server shutdown abnormally before system reboot Running Running Startup type: Automatic Startup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Stopped Server shutdown abnormally before system reboot Running StoppedFACT_SN_STORESTORE_IDSN_IDCOST_AMTFACT_SN_STOREYEAR_IDSTORE_IDSN_IDCOST_AMTYEAR_IDSTORE_IDSN_IDCOST_AMT199710020051998100200619982002007STORESERIAL NUMBERCOUNT (COST_AMT)100200110020012002001	Web Professional and OLAP Services Startup type: AutomaticStartup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Running Server shutdown abnormally before system reboot Running Running Startup type: Automatic Startup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Stopped Server shutdown abnormally before system reboot Running StoppedFACT_SN_STORESTORE_IDSN_IDCOST_AMTFACT_SN_STOREYEAR_IDSTORE_IDSN_IDCOST_AMTYEAR_IDSTORE_IDSN_IDCOST_AMT199710020051998100200619982002007STORESERIAL NUMBERCOUNT (COST_AMT)100200110020012002001	Web Professional and OLAP Services Startup type: AutomaticStartup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Running Server shutdown abnormally before system reboot Running Running Startup type: Automatic Startup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Stopped Server shutdown abnormally before system reboot Running StoppedFACT_SN_STORESTORE_IDSN_IDCOST_AMTFACT_SN_STOREYEAR_IDSTORE_IDSN_IDCOST_AMTYEAR_IDSTORE_IDSN_IDCOST_AMT199710020051998100200619982002007STORESERIAL NUMBERCOUNT (COST_AMT)100200110020012002001	Web Professional and OLAP Services Startup type: AutomaticStartup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Running Server shutdown abnormally before system reboot Running Running Startup type: Automatic Startup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Stopped Server shutdown abnormally before system reboot Running StoppedFACT_SN_STORESTORE_IDSN_IDCOST_AMTFACT_SN_STOREYEAR_IDSTORE_IDSN_IDCOST_AMTYEAR_IDSTORE_IDSN_IDCOST_AMT199710020051998100200619982002007STORESERIAL NUMBERCOUNT (COST_AMT)100200110020012002001
Web Professional and OLAP Services Startup type: AutomaticStartup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Running Server shutdown abnormally before system reboot Running Running Startup type: Automatic Startup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Stopped Server shutdown abnormally before system reboot Running StoppedFACT_SN_STORESTORE_IDSN_IDCOST_AMTFACT_SN_STOREYEAR_IDSTORE_IDSN_IDCOST_AMTYEAR_IDSTORE_IDSN_IDCOST_AMT199710020051998100200619982002007STORESERIAL NUMBERCOUNT (COST_AMT)100200110020012002001	Web Professional and OLAP Services Startup type: AutomaticStartup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Running Server shutdown abnormally before system reboot Running Running Startup type: Automatic Startup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Stopped Server shutdown abnormally before system reboot Running StoppedFACT_SN_STORESTORE_IDSN_IDCOST_AMTFACT_SN_STOREYEAR_IDSTORE_IDSN_IDCOST_AMTYEAR_IDSTORE_IDSN_IDCOST_AMT199710020051998100200619982002007STORESERIAL NUMBERCOUNT (COST_AMT)100200110020012002001	Web Professional and OLAP Services Startup type: AutomaticStartup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Running Server shutdown abnormally before system reboot Running Running Startup type: Automatic Startup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Stopped Server shutdown abnormally before system reboot Running StoppedFACT_SN_STORESTORE_IDSN_IDCOST_AMTFACT_SN_STOREYEAR_IDSTORE_IDSN_IDCOST_AMTYEAR_IDSTORE_IDSN_IDCOST_AMT199710020051998100200619982002007STORESERIAL NUMBERCOUNT (COST_AMT)100200110020012002001	Web Professional and OLAP Services Startup type: AutomaticStartup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Running Server shutdown abnormally before system reboot Running Running Startup type: Automatic Startup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Stopped Server shutdown abnormally before system reboot Running StoppedFACT_SN_STORESTORE_IDSN_IDCOST_AMTFACT_SN_STOREYEAR_IDSTORE_IDSN_IDCOST_AMTYEAR_IDSTORE_IDSN_IDCOST_AMT199710020051998100200619982002007STORESERIAL NUMBERCOUNT (COST_AMT)100200110020012002001

NOTE: There are two data records for the same one store and serial number (one for each year). When users run the report with the current configuration, the following result appears:

Web Professional and OLAP Services Startup type: AutomaticStartup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Running Server shutdown abnormally before system reboot Running Running Startup type: Automatic Startup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Stopped Server shutdown abnormally before system reboot Running StoppedFACT_SN_STORESTORE_IDSN_IDCOST_AMTFACT_SN_STOREYEAR_IDSTORE_IDSN_IDCOST_AMTYEAR_IDSTORE_IDSN_IDCOST_AMT199710020051998100200619982002007STORESERIAL NUMBERCOUNT (COST_AMT)100200110020012002001	Web Professional and OLAP Services Startup type: AutomaticStartup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Running Server shutdown abnormally before system reboot Running Running Startup type: Automatic Startup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Stopped Server shutdown abnormally before system reboot Running StoppedFACT_SN_STORESTORE_IDSN_IDCOST_AMTFACT_SN_STOREYEAR_IDSTORE_IDSN_IDCOST_AMTYEAR_IDSTORE_IDSN_IDCOST_AMT199710020051998100200619982002007STORESERIAL NUMBERCOUNT (COST_AMT)100200110020012002001	Web Professional and OLAP Services Startup type: AutomaticStartup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Running Server shutdown abnormally before system reboot Running Running Startup type: Automatic Startup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Stopped Server shutdown abnormally before system reboot Running StoppedFACT_SN_STORESTORE_IDSN_IDCOST_AMTFACT_SN_STOREYEAR_IDSTORE_IDSN_IDCOST_AMTYEAR_IDSTORE_IDSN_IDCOST_AMT199710020051998100200619982002007STORESERIAL NUMBERCOUNT (COST_AMT)100200110020012002001
Web Professional and OLAP Services Startup type: AutomaticStartup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Running Server shutdown abnormally before system reboot Running Running Startup type: Automatic Startup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Stopped Server shutdown abnormally before system reboot Running StoppedFACT_SN_STORESTORE_IDSN_IDCOST_AMTFACT_SN_STOREYEAR_IDSTORE_IDSN_IDCOST_AMTYEAR_IDSTORE_IDSN_IDCOST_AMT199710020051998100200619982002007STORESERIAL NUMBERCOUNT (COST_AMT)100200110020012002001	Web Professional and OLAP Services Startup type: AutomaticStartup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Running Server shutdown abnormally before system reboot Running Running Startup type: Automatic Startup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Stopped Server shutdown abnormally before system reboot Running StoppedFACT_SN_STORESTORE_IDSN_IDCOST_AMTFACT_SN_STOREYEAR_IDSTORE_IDSN_IDCOST_AMTYEAR_IDSTORE_IDSN_IDCOST_AMT199710020051998100200619982002007STORESERIAL NUMBERCOUNT (COST_AMT)100200110020012002001	Web Professional and OLAP Services Startup type: AutomaticStartup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Running Server shutdown abnormally before system reboot Running Running Startup type: Automatic Startup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Stopped Server shutdown abnormally before system reboot Running StoppedFACT_SN_STORESTORE_IDSN_IDCOST_AMTFACT_SN_STOREYEAR_IDSTORE_IDSN_IDCOST_AMTYEAR_IDSTORE_IDSN_IDCOST_AMT199710020051998100200619982002007STORESERIAL NUMBERCOUNT (COST_AMT)100200110020012002001
Web Professional and OLAP Services Startup type: AutomaticStartup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Running Server shutdown abnormally before system reboot Running Running Startup type: Automatic Startup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Stopped Server shutdown abnormally before system reboot Running StoppedFACT_SN_STORESTORE_IDSN_IDCOST_AMTFACT_SN_STOREYEAR_IDSTORE_IDSN_IDCOST_AMTYEAR_IDSTORE_IDSN_IDCOST_AMT199710020051998100200619982002007STORESERIAL NUMBERCOUNT (COST_AMT)100200110020012002001	Web Professional and OLAP Services Startup type: AutomaticStartup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Running Server shutdown abnormally before system reboot Running Running Startup type: Automatic Startup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Stopped Server shutdown abnormally before system reboot Running StoppedFACT_SN_STORESTORE_IDSN_IDCOST_AMTFACT_SN_STOREYEAR_IDSTORE_IDSN_IDCOST_AMTYEAR_IDSTORE_IDSN_IDCOST_AMT199710020051998100200619982002007STORESERIAL NUMBERCOUNT (COST_AMT)100200110020012002001	Web Professional and OLAP Services Startup type: AutomaticStartup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Running Server shutdown abnormally before system reboot Running Running Startup type: Automatic Startup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Stopped Server shutdown abnormally before system reboot Running StoppedFACT_SN_STORESTORE_IDSN_IDCOST_AMTFACT_SN_STOREYEAR_IDSTORE_IDSN_IDCOST_AMTYEAR_IDSTORE_IDSN_IDCOST_AMT199710020051998100200619982002007STORESERIAL NUMBERCOUNT (COST_AMT)100200110020012002001
Web Professional and OLAP Services Startup type: AutomaticStartup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Running Server shutdown abnormally before system reboot Running Running Startup type: Automatic Startup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Stopped Server shutdown abnormally before system reboot Running StoppedFACT_SN_STORESTORE_IDSN_IDCOST_AMTFACT_SN_STOREYEAR_IDSTORE_IDSN_IDCOST_AMTYEAR_IDSTORE_IDSN_IDCOST_AMT199710020051998100200619982002007STORESERIAL NUMBERCOUNT (COST_AMT)100200110020012002001	Web Professional and OLAP Services Startup type: AutomaticStartup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Running Server shutdown abnormally before system reboot Running Running Startup type: Automatic Startup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Stopped Server shutdown abnormally before system reboot Running StoppedFACT_SN_STORESTORE_IDSN_IDCOST_AMTFACT_SN_STOREYEAR_IDSTORE_IDSN_IDCOST_AMTYEAR_IDSTORE_IDSN_IDCOST_AMT199710020051998100200619982002007STORESERIAL NUMBERCOUNT (COST_AMT)100200110020012002001	Web Professional and OLAP Services Startup type: AutomaticStartup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Running Server shutdown abnormally before system reboot Running Running Startup type: Automatic Startup type: Manual Server shutdown normally before system reboot Running Stopped Server running before system reboot Running Stopped Server shutdown abnormally before system reboot Running StoppedFACT_SN_STORESTORE_IDSN_IDCOST_AMTFACT_SN_STOREYEAR_IDSTORE_IDSN_IDCOST_AMTYEAR_IDSTORE_IDSN_IDCOST_AMT199710020051998100200619982002007STORESERIAL NUMBERCOUNT (COST_AMT)100200110020012002001

ACTION:

The Strategy SQL Engine needs to know that the key attributes in the logical fact table view do not completely constitute the primary key of the physical fact table.

Follow the steps below to correct the issue:

In Strategy Developer, open the folder Project Source\Project\Schema Objects\Tables.
Double-click on the fact table included in the SQL. In the above example, it is FACT_SN_STORE
Uncheck the box labeled 'The key specified is the true key for the warehouse table,' as shown below:

Save and close the window.
Open the Schema menu and select to 'Update Schema….'
In the next screen, check the three boxes and click 'Update'.
Run the same report again. The SQL should appear as the second sample in the SYMPTOM section of this document.

Comment

0 comments

Details

Knowledge Article

Published:

May 18, 2017

Last Updated:

May 18, 2017