具有RestOf成员的前Y个中的前X个，其中X和Y是不同维度的层次结构 [英] Top X of Top Y with RestOf member where X and Y are hierarchies from different dimensions
问题描述
这很好:
WITH
SET [AllCountries] AS [Country].[Country].MEMBERS
SET [AllStates] AS [State-Province].[State-Province].MEMBERS
SET [Top2States] AS
Generate
(
[AllCountries]
,TopCount
(
(EXISTING
[AllStates])
,3
,[Measures].[Internet Order Count]
)
)
MEMBER [State-Province].[All].[RestOfCountry] AS
Aggregate({(EXISTING {[AllStates]} - [Top2States])})
SELECT
{[Measures].[Internet Order Count]} ON COLUMNS
,{
[AllCountries]
*
{
[State-Province].[All]
,[Top2States]
,[State-Province].[All].[RestOfCountry]
}
} ON ROWS
FROM [Adventure Works];
EXISTING关键字有很大帮助.
如果两个层次结构ON ROWS不在同一个维度上，就像上面的国家和州一样，那么我们将具有以下内容:
If the two hierarchies ON ROWS are not from the same dimension, in the way that countries and states are in the above, then we have something like the following:
WITH
SET [AllCountries] AS [Country].[Country].MEMBERS
SET [AllCats] AS [Product].[Category].[Category].MEMBERS
SET [Top5Cats] AS
Generate
(
[AllCountries]
,TopCount
(
(EXISTING
[AllCats])
,5
,[Measures].[Internet Order Count]
)
)
MEMBER [Product].[Category].[All].[RestOfProds] AS
Aggregate({(EXISTING {[AllCats]} - [Top5Cats])})
SELECT
{[Measures].[Internet Order Count]} ON COLUMNS
,{
[AllCountries]
*
{
[Product].[Category].[All]
,[Top5Cats]
,[Product].[Category].[All].[RestOfCountry]
}
} ON ROWS
FROM [Adventure Works];
您可以在上面的结果中看到，针对每个国家/地区以相同的顺序重复了相同的类别集，即引擎未找到每个国家/地区的topCount. EXISTING现在是冗余的.
You can see in the results of the above that the same set of categories are repeated against each country, in the same order i.e. the engine is not finding the topCount per country. EXISTING is now redundant.
我们如何适应上面的第二个脚本，使其具有与顶部脚本相似的功能?
How can we adapt the second script above so that it has similar functionality as the top script?
修改
一个更好的例子是下面使用Product.好像引擎正在为所有国家/地区查找TopCount，然后对每个国家/地区放置相同的集合.我想要每个国家的TopCount:
A better example is the following using Product. It is as if the engine is finding the TopCount for All countries and then putting the same set against each country. I'd like the TopCount for each country:
WITH
SET [AllCountries] AS
[Country].[Country].MEMBERS
SET [AllProds] AS
[Product].[Product].[Product].MEMBERS
SET [Top5Prods] AS
Generate
(
[AllCountries]
,TopCount
(
(EXISTING
[AllProds])
,5
,[Measures].[Internet Order Count]
)
)
MEMBER [Product].[Product].[All].[RestOfProds] AS
Aggregate({(EXISTING {[AllProds]} - [Top5Prods])})
SELECT
{[Measures].[Internet Order Count]} ON COLUMNS
,NON EMPTY
{
[AllCountries]
*
{
[Product].[Product].[All]
,[Top5Prods]
,[Product].[Product].[All].[RestOfProds]
}
} ON ROWS
FROM [Adventure Works];
Edit2
这是Sourav想法的最新版本-不幸的是RestOfProds成员无法正常运行:
This is the latest version via Sourav's ideas - unfortunately the RestOfProds members are not functioning correctly:
WITH
SET [AllCountries] AS
[Country].[Country].MEMBERS
SET [AllProds] AS
[Product].[Product].[Product].MEMBERS
SET [Top5Prods] AS
Generate
(
[AllCountries] AS a
,
{
(
a.Current
,[Product].[Product].[All]
)
+
//The top x prods
TopCount
(
NonEmpty
(
a.Current * [AllProds]
,[Measures].[Internet Sales Amount]
)
,5
,[Measures].[Internet Sales Amount]
)
}
)
SET [RestOfProds] AS
Extract
(
{[AllCountries] * [AllProds]} - [Top5Prods]
,[Product].[Product]
)
MEMBER [Product].[Product].[All].[RestOfProds] AS
Aggregate([RestOfProds])
SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS
,{
[Top5Prods]
,
[AllCountries] * [Product].[Product].[All].[RestOfProds]
} ON ROWS
FROM [Adventure Works];
Edit3
以下内容具有正确的顺序，因此成员RestOfProds始终跟随其各自的前5名
The following has the correct order so that the member RestOfProds always follows it's respective top 5
WITH
SET [AllCountries] AS
[Country].[Country].MEMBERS
SET [AllProds] AS
[Product].[Product].[Product].MEMBERS
SET [Top5Prods] AS
Generate
(
[AllCountries] AS a
,{
//The top x prods
TopCount
(
NonEmpty
(
a.Current * [AllProds]
,[Measures].[Internet Sales Amount]
)
,5
,[Measures].[Internet Sales Amount]
)
}
)
MEMBER [Product].[Product].[All].[RestOfProds] AS
Aggregate([Country].CurrentMember * [AllProds] - [Top5Prods])
SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS
,Generate
(
[AllCountries] AS X
,
Order
(
Intersect
(
X.CurrentMember * [AllProds]
,[Top5Prods]
)
,[Measures].[Internet Sales Amount]
,bdesc
)
+
{X.CurrentMember * {[Product].[Product].[All].[RestOfProds]}}
) ON ROWS
FROM [Adventure Works];
Edit4
以下内容具有正确的顺序，以便成员RestOfProds始终遵循其各自的前5名+我在行上添加了另一组:
The following has the correct order so that the member RestOfProds always follows it's respective top 5 + I've added a further set on rows:
WITH
SET [2months] AS
{
[Date].[Calendar].[Month].&[2007]&[9]
,[Date].[Calendar].[Month].&[2007]&[10]
}
SET [AllCountries] AS
[Country].[Country].MEMBERS
SET [MthsCountries] AS
[2months] * [AllCountries]
SET [AllProds] AS
[Product].[Product].[Product].MEMBERS
SET [Top5Prods] AS
Generate
(
[MthsCountries] AS A
,{
//The top x prods
TopCount
(
NonEmpty
(
A.Current * [AllProds]
,[Measures].[Internet Sales Amount]
)
,5
,[Measures].[Internet Sales Amount]
)
}
)
MEMBER [Product].[Product].[All].[RestOfProds] AS
Aggregate
(
([Date].[Calendar].CurrentMember,[Country].CurrentMember) * [AllProds]
-
[Top5Prods]
)
SELECT
{[Measures].[Internet Sales Amount]} ON COLUMNS
,Generate
(
[MthsCountries] AS X
,
Order
(
Intersect
(
X.Current * [AllProds]
,[Top5Prods]
)
,[Measures].[Internet Sales Amount]
,bdesc
)
+
{X.Current * {[Product].[Product].[All].[RestOfProds]}}
) ON ROWS
FROM [Adventure Works];
推荐答案
EXISTING在命名集上使用时，实际上并没有什么不同，因为命名集是在放置轴成员之前创建的.在您的情况下，GENERATE实际上只是在创建静态集合，然后在轴上将所有东西交叉连接.
EXISTING when used on named sets, doesn't really make a difference since named sets are created before the axes members are laid out. In your case, GENERATE is really just creating static sets and then on the axes everything is just cross joined.
要使TopCount脚本正常工作，您需要处理一组中的所有cross joins，以便将所有内容一起评估.我不确定，但是您可以尝试以下操作:
To get the TopCount script working, you need to handle all the cross joins inside one set so that everything is evaluated together. I am not sure, but you might try the below:
WITH
SET [AllCountries] AS [Country].[Country].MEMBERS
SET [AllCats] AS [Product].[Category].[Category].MEMBERS
SET [Top5Cats] AS
Generate
(
[AllCountries] as a
,
{
(a.current , [Product].[Category].[All] ) //The ALL member
+
TopCount //The top 2 categories
(
NonEmpty((a.current * [AllCats] ) , (a.CURRENT, [Measures].[Internet Order Count]))
,2
,[Measures].[Internet Order Count]
)
}
+
{ //The rest of the members
a.current * [AllCats]
-
a.current *{
TopCount
(
NonEmpty([AllCats] , (a.CURRENT, [Measures].[Internet Order Count]))
,2
,[Measures].[Internet Order Count]
)
}
}
)
MEMBER [Product].[Category].[All].[RestOfProds] AS
Aggregate({(EXISTING {[AllCats]} - [Top5Cats])})
SELECT
{[Measures].[Internet Order Count]} ON COLUMNS,
[Top5Cats] ON ROWS
FROM [Adventure Works];
如果需要RestOfCats成员，则可以添加此代码.
If you need the RestOfCats member, you can add this code.
SET [RestOfCats] AS
EXTRACT
(
{
[AllCountries] * [AllCats]
-
[Top5Cats]
},
[Product].[Category]
)
MEMBER [Product].[Category].[All].[RestOfCats] AS
Aggregate([RestOfCats])
编辑2
继续您的示例，在定义中另外删除所有"成员.
Continuing on your example, removing the 'All' member additionally in the definition.
SET [RestOfProds] AS
Extract
(
{[AllCountries] * [AllProds]} - [Top5Prods] - [AllCountries]*[Product].[Product].[All]
,[Product].[Product]
)
这篇关于具有RestOf成员的前Y个中的前X个，其中X和Y是不同维度的层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
