The following query has different result sets depending on which of the [Measures] I use in the WHERE clause. Having the [Measures].[MetricFormula] instead of [Measures].[Actual] activated, the query delivers a different (and expected) result.
WITH
MEMBER [Measures].[MetricFormula] AS Measures.[Downloads]
MEMBER [Measures].[Actual] AS [Measures].[MetricFormula], LANGUAGE = 1031,FORMAT_STRING='#,##0'
SET [mySet] as
topcount(
nonempty(
filter(
distinct descendants({[AppPackages].[AppPackages].&[F_TAAM00000069]}),
[Measures].[MetricFormula] <> 0 AND ([AppPackages].[AppPackages].PROPERTIES('AppTypeID') = 'DETAIL')
),
[Time].[Calendar].[Date].&[2015-03-22T00:00:00]:[Time].[Calendar].[Date].&[2015-03-31T00:00:00]
)
,6
,aggregate([Time].[Calendar].[Date].&[2015-03-22T00:00:00]:[Time].[Calendar].[Date].&[2015-03-31T00:00:00], [Measures].[MetricFormula]))
select
([Time].[Calendar].[Date].&[2015-03-22T00:00:00]:[Time].[Calendar].[Date].&[2015-03-31T00:00:00]) ON COLUMNS,
([mySet]) ON ROWS
FROM [Facts]
WHERE (
--([Measures].[MetricFormula]),
([Measures].[Actual]),
{[DownloadTypes].[DownloadTypes].&[New]}
)
([Measures].[Downloads] is the real measure defined in the cube.)
Question: Why does it deliver different results? I use this construct (in various forms) to simplify code based query generation. I'd like to understand the differnces before I start reworking my query generator.
Background: The query is supposed to calculate the Top 6 descendants of a given AppPackage for a given time range, based on the given measure. I start by finding the (non empty) descendants for the time range, on which I add the topcount, making sure that the time aggregated measure value drives the top calculation. Finally I put the evaluated set on the rows, the time on columns, specifying by where what to see. It works out nicely, besides the described effect.
Measures.[Downloads]
coming directly from RDBMS? If yes, what is the datatype of that column? If not, how are you creating this measure in cube? – SouravA