Top X de Top Y con miembro RestOf donde X e Y son jerarquías de diferentes dimensiones
Esto funciona bien:
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];
losEXISTING
La palabra clave ayuda mucho.
Si las dos jerarquíasON ROWS
no son de la misma dimensión, en la forma en que los países y estados están en lo anterior, entonces tenemos algo como lo siguiente:
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];
Puede ver en los resultados de lo anterior que el mismo conjunto de categorías se repite para cada país, en el mismo orden, es decir, el motor no encuentra el TopCount por país.EXISTING
Ahora es redundante.
¿Cómo podemos adaptar el segundo script de arriba para que tenga una funcionalidad similar al script superior?
Editar
Un mejor ejemplo es el siguiente uso de Producto. Es como si el motor estuviera encontrando elTopCount
para Todos los países y luego poner el mismo conjunto contra cada país. Me gustaria elTopCount
para cada país:
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
Esta es la última versión a través de las ideas de Sourav; desafortunadamente, los miembros de RestOfProds no funcionan correctamente:
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
Lo siguiente tiene el orden correcto para que el miembroRestOfProds
siempre sigue su respectivo 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
Lo siguiente tiene el orden correcto para que el miembroRestOfProds
siempre sigue sus respectivos 5 principales + He agregado un conjunto adicional en filas:
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];