Database Reference
In-Depth Information
6.10.1.14 Query for Shared Members
Identifying shared members is a bit more compli-
cated and requires the use of the generate() function. This query will return all mem-
bers of a dimension and flag which ones are shared members or duplicate members.
WITH MEMBER [Measures].[Products_SharedMembers] AS
'Count(
Generate({[Products].CurrentMember} AS [var1],
Generate(Filter([Products].Levels(0).Members,
[Products].CurrentMember.[MEMBER_NAME] =
[var1].Item(0).Item(0).[MEMBER_NAME]),
Filter ({[Products].CurrentMember},
IsAncestor( [Products], [Products].CurrentMember )))))'
SELECT
{[Measures].[Products_SharedMembers]} ON AXIS(0),
[Products].Levels(0).Members ON AXIS(1)
FROM [ASOSamp.Sample];
use this query to refine the query above and only return the shared members or
duplicate members.
WITH MEMBER [Measures].[Products_SharedMembers] AS
'Count(
Generate({[Products].CurrentMember} AS [var1],
Generate(Filter([Products].Levels(0).Members,
[Products].CurrentMember.[MEMBER_NAME] =
[var1].Item(0).Item(0).[MEMBER_NAME]),
Filter({[Products].CurrentMember},
IsAncestor([Products], [Products].CurrentMember)))))'
SELECT {} ON AXIS(0),
Filter(
Except([Products].Levels(0).Members,
Descendants([All Merchandise])),
[Measures].[Products_SharedMembers] > 1) ON AXIS(1)
FROM [ASOSamp.Sample];
Axis-1
+-------------------
(Flat Panel)
(HDTV)
(Digital Recorders)
(Notebooks)
6.10.2 Calculating Optimal Sparse Dimension Order
It has long been believed that the hourglass model of largest dense to smallest dense
and then smallest sparse to largest sparse would yield the best outline order for a BSo
application. In many cases, this is true, but it is not based on the dimensions with larg-
est or smallest member counts, but rather the largest and smallest parent to child ratios.
Because this has been generally difficult to calculate, most developers have used mem-
ber count as the starting point for assessing outline dimension order. using the query
below, an administrator can quickly calculate a dimension's parent/child ratio and com-
pare with other dimensions to determine optimal outline order. run the queries below
and then match up against the member counts for the dimensions. I think you will be
