Rank with partitioning – MDX

Rank on grouping

Recently I got a problem of ranking based on a group.
Lets try to solve the same problem using Adventure Works cube.

We have details of customer country and customers. We want to rank them based on their internet sales. But we want the rank to be reset when the country changes.
In general rank will assign rank in ascending order , but we want to reset it when the country changes.

We have created a set [myset] for customer and country sorted on [Internet Sales Amount] and we will asign them a rank.

WITH
SET [myset] AS
ORDER(
{[Customer].[Country].[Country].MEMBERS* [Customer].[Customer].[Customer].MEMBERS}
,[Measures].[Internet Sales Amount],ASC)
MEMBER [Measures].[rank]
AS
RANK(([Customer].[Country].currentmember,[Customer].[Customer].CURRENTMEMBER),
[myset]
)
SELECT {[Measures].[Internet Sales Amount],[Measures].[rank]} ON 0 ,
[myset] ON 1
from [Adventure Works]

Here we can see the rank is in ascending for order for all customers irrespective of country. We want to reset this rank when the country changes. So we will create a new member
[Measures].[newrank]

WITH
SET [myset] AS
ORDER(
{[Customer].[Country].[Country].MEMBERS* [Customer].[Customer].[Customer].MEMBERS}
,[Measures].[Internet Sales Amount],ASC)
MEMBER [Measures].[rank]
AS
RANK(([Customer].[Country].currentmember,[Customer].[Customer].CURRENTMEMBER),
[myset]
)
MEMBER [Measures].[newrank] AS
iif([myset].ITEM([Measures].[rank]-1).ITEM(0).NAME <> [myset].ITEM([Measures].[rank]-2).ITEM(0).NAME ,
1,([myset].ITEM([Measures].[rank]-2), [Measures].[newrank])+1
)
SELECT {[Measures].[Internet Sales Amount],[Measures].[rank], [Measures].[newrank]} ON 0 ,
[myset] ON 1
from [Adventure Works]

Having function in SSAS

Having function

This is undocumented function in SSAS. Those who have worked with SQL Server knows that having is used to filter aggregate data. Same use here. In general we can get same result using filter function but having is more easy to understand and might be giving better result.

Lets see how can we use this.

We want only those customers who have internet sales amount is less than 1000

SELECT [Measures].[Internet Sales Amount] ON 0,
[Customer].[Customer].[Customer].MEMBERS
HAVING [Measures].[Internet Sales Amount] < 1000 ON 1 FROM [Adventure Works]

Same Query we can run using filter function also

SELECT [Measures].[Internet Sales Amount] ON 0,
FILTER([Customer].[Customer].[Customer].MEMBERS
, [Measures].[Internet Sales Amount] < 1000) ON 1 FROM [Adventure Works]

As we can see having is easily readable and easy to understand .

Detach DB in SSAS

Detach and attach DB in Analysis Service

Now in Analysis Service 2008 its possible to detach and attach database.
Its make easy to move database to different location which was not so easy in past

lets see how we can do it

If we want to attach it we hvae to give path of folder where the cube is stored.