September 10, 2015 at 3:33 pm
Ive been tasked with converting a collection of tables with SQL scripts into an SSAS cube with calculations, Im having a problem filtering the data in a way that I would find straightforward in SQL. An example of what I would like to do is to take data such as this:
Category Region
Bikes EU
Bikes EU
Bikes USA
Parts USA
Parts USA
Accessories USA
Accessories GB
Accessories EU
Into the following:
Category Region
Bikes EU
Bikes EU
Accessories USA
Accessories GB
In SQL I would do something like placing the following in the Where part of the script: WHERE (Category = 'Bikes' AND Region = 'EU) OR (Category = 'Accessories' AND Region NOT 'EU')
Ive been trying to replicate this in a calculated member, whilst I find it easy to filter by region OR Category, I find it difficult to combine the two. Here is part of my filter in the calculated member:
{[Vw Dim Parts].[Category].&[Bikes],
[Vw Dim Parts].[Category].&[Accessories]}
This filters for the category and accessories fine but when I do something like the following it throws an error:
{([Vw Dim Parts].[Category].&[Bikes] AND [Vw Dim Area].[Region].&[EU],
[Vw Dim Parts].[Category].&[Accessories] NOT [Vw Dim Area].[Region].&[EU]}
I don't know the exact syntax to replace the AND and NOT, when I try to process I get an error saying that members tuples and sets must use the same hierarchies.
Does anyone know how I can replicate the logic from SQL in SSAS calculated members? Thanks.
September 10, 2015 at 5:17 pm
It's a little tricky to do this in Analysis Services, due to the dimensional nature of cubes.
I would create 2 sets: One that returns the combination of "Bikes" and "EU", and one that returns "Accessories" for every country but "EU".
Let's look at an example, using the Adventure Works cube...
For the "Bikes" and "EU" combination, the set will look something like this:
{[Product].[Product Line].&[M]}
* {[Customer].[Country].&[Australia]}
The asterisk (*) above performs a crossjoin, returning all possible combinations. Because of the fact that we're just referencing one member from each dimension, only one combination will be returned in the set.
Now for the tricky one (to exclude a combination from a returned set), I'll be using the Except() function in MDX. (More info on that function can be found here: https://msdn.microsoft.com/en-us/library/ms144900.aspx[/url])
except
(
{
[Product].[Product Line].&
}
*
{
[Customer].[Country].[Country]
}
,
{
{[Product].[Product Line].&}
* {[Customer].[Country].&[Australia]}
}
)
The Except() function used above will return every Country and Accessory (product line "S") combination, excluding the members found in the second part of the function...in this case the Accessory and Australia combination.
The entire query would then look something like this:
with set Accessories_Not_Aus
as
except
(
{
[Product].[Product Line].&
}
*
{
[Customer].[Country].[Country]
}
,
{
{[Product].[Product Line].&}
* {[Customer].[Country].&[Australia]}
}
)
set Mountain
as
(
{[Product].[Product Line].&[M]}
* {[Customer].[Country].&[Australia]}
)
select{
[Measures].[Internet Sales Amount]
} on 0
,{
Accessories_Not_Aus
,Mountain
}
on 1
from[Adventure Works]
An image of the results are attached. Hope this helps.
September 11, 2015 at 1:34 am
Thanks a lot Martin for taking time to look into this. I'll give that a try late today and get back to you.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply