Filter by referenced dimension

Question

I have a problem I am trying to solve that may be difficult to explain.

I have the below SSAS schema:

The item dimension is referenced directly by the performance fact, and the category dimenion is a Referenced dimension.  The Item Dimension only stores the final leaf category (i.e. the category that is right at the end of the tree, the category structure is n-levels deep).

What I would like to do is return the parent category (left-most category in the tree).

I use this query

SELECT 
{ 
  [Measures].[Revenue]
} ON COLUMNS
,
--NON EMPTY
{
  GENERATE
  (
    {
      [Category].[ParentCategoryId].[All]
    },
    DESCENDANTS
    (
      [Category].[ParentCategoryId].CURRENTMEMBER,
      [Category].[ParentCategoryId].[Level 02]
    )
  )
}
ON ROWS 
FROM ( SELECT ( { [Item].[Company].&[199] } ) ON COLUMNS 
FROM [MdxCube]
)

This will return all root categories, and not just those used by Company 199.  If I add the non-empty, it will return only those used by Company 199, but unfortunately only those which have revenue data.

What I need to do is return all root categories (or any level I want), that have a child category in the FinalLeafCategoryId in the Item table. I have tried filtering with descendents but am having no luck and am very stuck.

Is anybody able to help me with this?  I can supply more information if the above isn't clear enough.

Answer 1

One other question related to this - how would I return the Member_key in the above query instead of the name?

Or do I need to use:

WITH

MEMBER CategoryId AS [Category].[ParentCategoryId].Currentmember.Member_Key

Answer 2

Hi, depends for what purposes and for what client you want the MEMBER_KEY return.

One option is your Calculated member and other option is to use

SELECT 
{ 
  [Measures].[Revenue]
} ON COLUMNS
,
--NON EMPTY
{
  GENERATE
  (
    {
      [Category].[ParentCategoryId].[All]
    },
    DESCENDANTS
    (
      [Category].[ParentCategoryId].CURRENTMEMBER,
      [Category].[ParentCategoryId].[Level 02]
    )
  )
} DIMENSION PROPERTIES MEMBER_KEY
ON ROWS 
FROM ( SELECT ( { [Item].[Company].&[199] } ) ON COLUMNS 
FROM 

In this example the MEMBER_KEY will be retrieved as part of the CellSet, however will not be displayed by default. in SSMS you can see it when you double click the cell.

Answer 3

Thanks - don't suppose you are able help on the primary question per chance?

Answer 4

Although it is possible to do the filtering using the [FinalLeafCategoryID] attribute of the [ItemDimension] and [CategoryID] attribute of the [CategoryDimension] with nested FILTERs, the performance will be terrible.

Below is an example built on the Adventure Works Multidimensional Sample, using the [Geography] and [Reseler] dimensions, where [Geography] is referenced though the [Reseler]

SELECT
	{

	} ON COLUMNS,
	{
		EXISTS([Geography].[Geography].[City].MEMBERS, --Here you can put whatever attribute or hierarchy level
			FILTER([Geography].[Geography Key].[Geography Key].MEMBERS AS G, 
				FILTER([Reseller].[Geography Key].[Geography Key].MEMBERS AS R, 
					R.CURRENTMEMBER.MEMBER_KEY = G.CURRENTMEMBER.MEMBER_KEY
				).COUNT > 0
			)
		)
	} ON ROWS
FROM [Adventure Works]

If you really need such filtering, I suggest you to create a Measure Group which will be build from the same source table as the [ItemDimension] and create a regular relationship between this Measure Group and the [CategoryDimension]. This will enable you an effective filtering of the [CategoryDimension] based on the existence of the key in the [ItemDimension].

Answer 5

Thanks - I tried the below, but even after an hour it still hadn't loaded.  Is this the bad performance you are talking about, or have I broken it?

SELECT 
{ 
  [Measures].[Revenue]
} ON COLUMNS
,
--NON EMPTY
{
  EXISTS([Category].[ParentCategoryId].[Level 02].MEMBERS,
   FILTER([Category].[ParentCategoryId].MEMBERS AS G, 
      FILTER([Item].[FinalLeafCategoryId].MEMBERS AS R, 
       R.CURRENTMEMBER.MEMBER_KEY = G.CURRENTMEMBER.MEMBER_KEY
      ).COUNT > 0
    )
  )
}
ON ROWS 
FROM ( SELECT ( { [Item].[Company].&[199] } ) ON COLUMNS 
FROM [MdxCube]
)

Answer 6

Hi, depends on the amount of data, but as I have mentioned, it will have very bad performance. It takes a few seconds on Adwenture Works sample. So if you have large database, it can take forewer and probably the time will be exponential.

As I have mentioned, I suggest you to create a Fact table for the itermediate dimension and the filtering will be done by speed of light. :-) (Event it will be "factless" fact table, but will serve perfectly).

Answer 7

Hi IRDev,

Parent - child dimensions are always going to give you performance problems. SO, if possible you should look at flattening it into a finite number of levels and create a natural hierarchy out of those levels. 

As for your initial question, You can try out this mdx :

With set [GeoSet] as generate([Reseller].[Geography Key].[Geography Key].Members,
{LINKMEMBER([Reseller].[Geography Key].CurrentMember,[Geography].[Geography Key])})
SELECT {} on columns,
exists([Geography].[City].[City].members,[GeoSet]) on rows --choose whichever attribute you want to here
from [Adventure Works]
where ([Reseller].[Reseller Type].[Business Type].&[Specialty Bike Shop]) --- your filter clause on Item dimension

cheers,

Amit

---

amit kar

Answer 8

Yeah, much better approach with the GENERATE and LINKMEMBER :-)

Answer 9

That worked perfectly thanks soo much!!!!