July 16, 2019 at 8:05 am
Hi Guys!
Long time reader – first timer poster.
Hoping someone can aid me in some tricky MDX, seeing as I’m relatively new to the subject.
I’m working on a request from a customer, to calculate something they call “FullTimeActivity”.
When breaking it down it is a “simple” division between the number of days between an activity start and end [ActivityDays] and number of days in a selected period [CalenderDays].
Now comes the tricky part (in my opinion) – it must be dynamic.
Let me give an example:
1: The customer chooses time period (year, month or date) from the hierarchy in my Time dimension. In this case lets say a month March 2018.
2: The query must then count the number of calendar days in March 2018 – [CalenderDays]
3: Find all the activities that are ongoing in March 2018
4: Count the number days that are within the chosen time period (March 2018) of each found activity – although if the start date of the activity is after March 1st, it must count from the activity start date, and if the end date is before March 31st, it must count to the end date – [ActivityDays]
I’ve managed to count the [CalenderDays] using a Count function, that uses descendants.
I’m having some trouble with the [ActivityDays] though.
I’ve tried the DateDiff function and I’ve tried the Count function without any success.
Here is my Count query:
WITH
MEMBER [Measures].[ActivityDays_Count] AS
({LINKMEMBER([Startdate].[Year – Month - Day].Currentmember, [Time].[Year - Month - Day])} :
{LINKMEMBER([Enddate].[Year – Month - Day].Currentmember, [Time].[Year – Month - Day])}).COUNT
Here is my Datediff query:
MEMBER [Measures].[ActivityDays_Diff] AS
DATEDIFF('d',
{LINKMEMBER([Startdate].[Year – Month - Day].Currentmember, [Time].[Year - Month - Day])},
{LINKMEMBER([Enddate].[Year – Month - Day].Currentmember, [Time].[Year – Month - Day])})
The Startdate and Enddate dimensions are role-playing on the Time dimension.
Hope someone can spot my error! Please let me know if you need any additional information to understand my question and explanation.
Thanks!
July 16, 2019 at 11:39 am
What does your fact table look like?
July 16, 2019 at 11:47 am
Hi Martin - thanks for the interest.
Well my fact table is comprised of an assortment of different keys, amongst them are my start and end keys – eg. ‘20180331’.
Realizing that might be a rather useless answer, here goes again.
The table designed to handle type 2 changes. By that, I mean that one activity can yield several rows. This is due to the fact that an activity has a start and end date, but in between those, the conditions of the activity can change. Which means that an activity also has validfrom and validto dates, which of course fall within the start and end dates.
The reason for this design is avoid snapshots and thereby to shorten our ETL process.
Hope this helps.
July 16, 2019 at 12:14 pm
Interesting use of a Temporal fact table 🙂
MDX doesn't do well with aggregates over ranges or where there is no data point (tuple), which is the case you have here. I'd recommend that you extrapolate the fact table in your cube model to be at the daily grain...almost like a daily snapshot. A cross-join with your Date dimension should do the trick here. Summing the activity days would then be easy as each row will represent one day.
Because of your unique use of a temporal fact, you will also have to build some logic into your source query to eliminate rows based on the valid from/to dates.
Hope this helps.
July 17, 2019 at 6:26 am
Hi Martin
Again - thank you for the response.
I’ve read some of the stuff you’ve written about the temporal design – good reading! We’ve managed to do some simple aggregating mdx queries on the temporal fact data, but this range thing is killing me.
The daily snapshot option is one that I’ve considered and for now is my backup plan.
I’ll give it a go again today, before setting the backup plan in motion.
Thanks!
July 17, 2019 at 12:38 pm
Thanks, I've dealt with Temporal tables a bit 🙂
It is unfortunate that the MDX language doesn't support ranged queries (outside of the members in the Date dimension) very well. If you had a Tabular model, these types of aggregations would have been a lot easier and without having to change the cube model significantly.
Just to be clear, I'm not suggesting that you change the underlying DW data model but only to extrapolate the data when bringing it into the cube. This will affect the performance of your source queries due to the cross-join, but using partitions will help with that.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply