SSAS - Creating a measure the only counts the newest events
I'm new to SSAS and I'm not sure how to heavily customize a measure开发者_运维知识库. My fact table stores every change (events like created new page or deleted page). The default measure will return a count of all changes but I also need a measure that will display the number of pages (to which the events refer) in the system. It should only count one event for every distinct page but using the latest status of the system as of the date being used as a filter. For example: Somebody creates 5 pages on Monday and then deletes 2 pages on Tuesday and adds 1 page on Friday. That's 8 events but only 6 unique pages so the measure should display 5 pages if filtered for Tuesday and 6 pages if filtered for Friday. It should only count the events that are the last one for that page (As opposed to using the first event for that page as this could affect other dimensions). How would I go about doing this?
You already have an Event_Count Measure, and you now need to create a Page_Count measure. This Page_Count measure will have a Last_Change_Type dimension and a Time dimension. Slicing the Page_Count measure by the Last_Change_Type dimension will give you what you're after.
To create a view for your Page_Count fact, you could just group your Event_Count fact table by the Page_Id and get the MAX(Event_Date) info for all the dimensions.
This is all very conceptual without a view of your schema, but your measure doesn't sound complex, it is just not at the "Event" granularity, it is at the "Page" granularity.
精彩评论