SSAS Cube has integrates data from multiple fact tables,
with one or more fact tables per subject area.
Often we are required to show on dashboard the latest data update
date for each subject area.
The following MDX query gives the latest data date for the different
measure groups in Adventure Works cube.
WITH
MEMBER [Measures].[InternetSales Date] AS
Max
(
nonempty(
[Date].[Date].Members
,( [Measures].[Internet Sales Amount])
)
, [Date].[Date].CurrentMember.Member_Key
)
MEMBER [Measures].[ResellerSales Date] AS
Max
(
nonempty(
[Date].[Date].Members
,([Measures].[Reseller Sales Amount] )
)
, [Date].[Date].CurrentMember.Member_Key
)
MEMBER [Measures].[Finance Date] AS
Max
(
nonempty(
[Date].[Date].Members
,( [Measures].[Amount])
)
, [Date].[Date].CurrentMember.Member_Key
)
SELECT
{
[Measures].[InternetSales Date],
[Measures].[ResellerSales Date],
[Measures].[Finance Date]
} ON COLUMNS
FROM [Adventure Works]
Results
|
Last InternetSales Update
|
Last ResellerSales Update
|
Last Finance Update
|
|
20080731
|
20080601
|
20080630
|
The query uses a calculated measure with Max function to
figure out the latest data date for measure group. We need one such calculated measure for
each measure group.
