Friday, March 9, 2012

Problem when creating a new named calculation

I have a Customer dimension in my model, and now I want to count the # unique customers in one of my fact tables.

Therefore I created a new named calculation in the fact table which does a count distinct of the customerkey in the fact table.

And when I try to add a new report model i get the following error message, even when I don't select anything in the Select report model generation rules screen:

TITLE: Microsoft Visual Studio

An error occurred while executing a command.
Message: Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
Command:
SELECT
COUNT([DEPARTURE]),
COUNT(DISTINCT [DEPARTURE]),
COUNT((SELECT
DISTINCT(
COUNT(CLIENTKEY)) AS Number_of_Persons
FROM
FCT_TABLE AS FCT_TABLE)),
COUNT(DISTINCT (SELECT
DISTINCT(
COUNT(CLIENTKEY)) AS Number_of_Persons FROM
FCT_TABLE
FCT_TABLE)),
COUNT(Arrival),
COUNT(DISTINCT Arrival)
FROM [dbo].[FCT_TABLE] t


BUTTONS:

OK

What should I do?

As I understand you have defined one of named calculations as

(SELECT DISTINCT(COUNT(CLIENTKEY)) AS Number_of_Persons
FROM
FCT_TABLE AS FCT_TABLE)

Yes, this named calculation will not work in some cases. But it will work if you push it into a named query. Redefine you FCT_TABLE table in DSV editor as a named query:

SELECT
*,
(SELECT DISTINCT(COUNT(CLIENTKEY)) FROM FCT_TABLE) AS Number_of_Persons
FROM FCT_TABLE

No comments:

Post a Comment