How to expose total number of days in each month via OLAP cube

Question

I'd like an easy way of getting the total number of days for each month in the date dimension.

Currently this information is not exposed in our cubes. Therefore I need to write custom mdx such as the following:

WITH 
  SET [13Mth] AS Tail([Date].[Date - Calendar Month].[Calendar Month].MEMBERS ,13) 
  SET [m] AS Tail([13Mth]) 
  MEMBER [Measures].[TotalNumDaysMth] AS 
    Datepart
    ("D",
        Dateadd
        ("M",1,
          Cdate(Cstr(VBA!Month([m].Item(0).Item(0).Lag(1).Name)) + "-01-" + Cstr(VBA!Year([m].Item(0).Item(0).Lag(1).Name)))
        )
      - 1
    ) 
  MEMBER [Measures].[TotalNumDaysMth-1] AS 
    Datepart
    ("D",
        Dateadd
        ("M",1,
          Cdate(Cstr(VBA!Month([m].Item(0).Item(0).Lag(1).Name)) + "-01-" + Cstr(VBA!Year([m].Item(0).Item(0).Lag(1).Name)))
        )
      - 1
    )

I don't believe our users will need this information within our cube browsing client but from a developer point of view I could do without having to always implement the above.

What approach should we use to make the above data more easily available?

score 2 · Accepted Answer · answered Dec 20 '14 at 21:43

I have always added an attribute to the date dimension "days in month", of type integer. You can hide this attribute, if you do not want to expose the attribute hierarchy to your users.

But you can still use in calculations.

So my advise would be add a proper attribute to your time dimension and base your calculations of on the attributes.

Hope this helps somehow.

score 1 · Answer 2 · answered Dec 24 '14 at 17:33

1

I am assuming that in your development scenario, the month will come from Web Service/Front End/SSRS report etc. In any such cases, you can create a measure that will return the count.

Approach 1: Year-Quarter-Month-Date exists

It is a good practice to have a similar hierarchy in place. The following MDX will work:

with set CurrentMonth as
[Date].[Year-Quarter-Month-Date].currentmember

set DatesInCurrentMonth as
descendants(abc, [Date].[Year-Quarter-Month-Date].[Date])

member [Measures].countofdaysinmonth as
count(DatesInCurrentMonth)

select measures.CountOfDaysInMonth on 0
from [MyCube]
where [Date].[Year-Quarter-Month-Date].[Month].&[Feb-2004]

Approach 2: Such hierarchy doesn't exist

with set CurrentMonth as
[Date].[Month].currentmember

set DatesInCurrentMonth as
exists([Date].[Date].children, abc, "<<Any Measure group>>")

member measures.countofdaysinmonth as
count(DatesInCurrentMonth)

select measures.countofdaysinmonth on 0
from [MyCube]
where [Accident Date].[Month].&[Feb-2004]

Let me know if they work/have issues.

answered Dec 24 '14 at 17:33

SouravA

5,147
2
24
49

Hi Sourav, couple of problems 1.there may be no results for some dates 2.for the current month future dates are not in the cube. – whytheq Dec 27 '14 at 15:31
Why dont you have future dates in cube? That is a standard feature of any date dimension. Which scenario might yield no results and why? – SouravA Dec 27 '14 at 18:07
If say, for example, on 26th Dec all measures are null then your code will return 30 days for December? Not sure why future dates should be standard - this is a matter of design: I think the cube looks a little tidier without these dates.(upped your answer though - effort appreciated) – whytheq Dec 27 '14 at 19:40
OK yes, the second approach has its issues. But the first approach will work just fine. The reason I said having future dates is a standard practice because it is absolutely required to have so in the scenarios I have faced till now. Come to think of it, it might make sense not to have future dates in many other cases. – SouravA Dec 27 '14 at 20:35
Hi Sourav: today our cube has data upto 27th December so your first approach will return 27 for `countofdaysinmonth` for Dec-2014 - I want it to return 31. – whytheq Dec 28 '14 at 22:27
1

In that case, Why don't you add a derived column in your cube's date dimension which contains this information. [This](http://stackoverflow.com/q/691022/2993606) SO answer shows how to calculate that for a given date. You would have to tweak it to calculate it for every month having data. – SouravA Dec 29 '14 at 09:05
1

Hi Sourav - exactly matey. I'm not the cube developer but have sent through a request to the developers. Thanks for the help as I needed confirmation that the code in my OP is currently my best bet unless a property is added into the cubes. I think it is fair to say we've ended up agreeing with @TomMartens answer. – whytheq Dec 29 '14 at 09:35

How to expose total number of days in each month via OLAP cube

2 Answers2