How To Get All Items Created or Still Open For A Given Time

Question

I am working with a system were items are created (postDate dimension) and closed (endDate dimension). The endDate column is always populated with the last time the item was seen. An item is considered closed in a certain time if its last seen date is before the date you are querying. Each row in the fact table has the item, postDate, endDate, locationID, and some other dimensions used for aggregations. What I am trying to accomplish is getting all items still active for a given time frame. For example I want to know all items posted in November 2008 or before November 2008 that has not yet closed. In SQL it would look something like:

SELECT C.geoCountyArea,TM.CalendarYear,COUNT(DISTINCT a.itemid)
FROM [dbo].[factTable] a
JOIN dbo.dimDate AS TM
    ON TM.DateKey BETWEEN postDate AND endDate
JOIN [dbo].[dim_geography] C
    ON A.geographyID=C.geographyID
WHERE C.geoCountyArea = '1204000057'
AND TM.CalendarYear = 2008 AND TM.MonthNumberOfYear = 11
GROUP BY C.geoCountyArea,TM.CalendarYear
ORDER BY C.geoCountyArea,TM.CalendarYear

This returns 27,715 which is expected. Now, in MDX this looks like:

WITH MEMBER Measures.[itemCount] AS
    AGGREGATE(
        {NULL:[PostDate].[Month Name].&[2008]&[11]} * {[EndDate].[Month Name].&[2008]&[11]:NULL},
        [Measures].[Fact_itemCount]
    )

SELECT NON EMPTY (
    Measures.[itemCount]
    ) ON 0,
NON EMPTY (
    {[PostDate].[Month Name].&[2008]&[11]},
    {[Geography].[Geo County Area].&[1204000057]}
    )ON 1
FROM [Cube];

This returns 27,717 - which is 2 more than the SQL version that could be due to items with no end Date posted. Now, the complication comes when I want to get more than one explicit time - for example item count for all months in 2008 or item count for all years. I looked up methods to link a given param to another one via roll playing dimensions and came across this link. I altered my script so it looks like:

WITH MEMBER Measures.[itemCount] AS
    AGGREGATE( 
          {NULL:LINKMEMBER([DATE].[Calendar].CURRENTMEMBER
                     ,[PostDate].[Calendar])}
        * {LINKMEMBER([DATE].[Calendar].CURRENTMEMBER
                    , [EndDate].[Calendar]):NULL}
        , [Measures].[Fact_itemCount]
        )

SELECT {Measures.[jobCount]} ON 0,
NON EMPTY (
    {[DATE].[Month Name].&[2008]&[11]},
    {[Geography].[Geo County Area].&[1204000057]}
    )ON 1
FROM [Cube];

This, however, returns only the items created in November 2008 - value of 14,884. If I add in other months I do get individual counts for each month but, again, these are just the items created in those months.

How do I get the "active" item count for a given month/year/quarter without having do explicitly declare the time values in the AGGREGATE?

Is the dimension "date" related with either "post Date" or "end date"? — SouravA, Aug 01 '16 at 15:32
@SouravA, yes. `Date` is a dimension used by both `postDate` and `endDate`. However, if you query the cube just on `Date` dimension it treats it like `postDate` (similar to most Adventure Works examples). — wergeld, Aug 01 '16 at 15:34

whytheq · Answer 1 · 2016-07-30T09:27:23.543

0

Can you use NonEmpty?

WITH MEMBER Measures.[itemCount] AS
    AGGREGATE( 
          {NULL:
             NONEMPTY(
               [PostDate].[Month Name].MEMBERS //<<AMEND TO EXACT STRUCTURE USED IN YOUR CUBE
              ,[DATE].[Calendar].CURRENTMEMBER
             ).ITEM(0).ITEM(0)}
        * {NONEMPTY(
               [EndDate].[Month Name].MEMBERS //<<AMEND TO EXACT STRUCTURE USED IN YOUR CUBE
              ,[DATE].[Calendar].CURRENTMEMBER
             ).ITEM(0).ITEM(0): NULL}
        , [Measures].[Fact_itemCount]
        )
...

edited Jul 30 '16 at 09:27

answered Jul 29 '16 at 15:13

whytheq

34,466
65
172
267

This was throwing an error: `#Error Query (6, 5) The MEMBERS function expects a hierarchy expression for the argument. A member expression was used.` Then I fixed the dimension text formatting - now it is running, but has not yet completed (2 minutes and counting). – wergeld Jul 29 '16 at 15:20
@wergeld did it complete with the correct answer? (it has had a couple of months now ...!) – whytheq Oct 27 '16 at 06:05
No, This never returned a valid result. It only returned the counts for items created in a month and did not include those items still active for the month requested. – wergeld Oct 27 '16 at 11:49

score 0 · Answer 2 · answered Oct 27 '16 at 12:01

This ended up being the solution that provided valid results (tested against SQL calls against the warehouse tables):

WITH MEMBER Measures.[itemCount] AS
    AGGREGATE(
        {NULL:LINKMEMBER([Post Date].[Calendar],
            [Post Date].[Calendar])}
        * {LINKMEMBER([Post Date].[Calendar],
            [End Date].[Calendar]):NULL},
        [Measures].[Fact_itemCount]
    )

SELECT {Measures.[itemCount]} ON 0,
NON EMPTY (
    {[Post Date].[Month Name].Children},
    {[Geography].[Geo County Area].&[1204000057]}
    )
FROM [Cube]

Not that I am doing LINKMEMBER against the post and end dates - not against the global Date measure.

How To Get All Items Created or Still Open For A Given Time

2 Answers2