Post reply

Year To Date as MDX in cube? or just query?

  • September 13, 2010 at 2:41 am

    #240264

    Dear all,

    I have little practical experience of SSAS/MDX and I would appreciate a bit of advice...

    Where I currently work, there has been many years of very bad habits and relatively low skills level so I feel I need to check some of their "assertions".

    One such assertion is that it is not possible to get decent performances when performing "Year To Date" queries by using MDX within the cube.

    Their first approach has been to try and calculate the Year To Date figures in the ETL... and that proves difficult with serious performance problems (are we surprised???).

    So, the current thinking is that it should be left to report writers.

    I find this disappointing because I would rather have this in the cube directly (as the end users request, actually).

    Question: since it looks such a simple MDX on the time dimension, would there be any fundamental problem in introducing the YearToDate as a calculated member in the Time Dimension?

    Or am I getting this drastically wrong? If so, could you point me towards "useful reading" to brush up my knowledge, please?

    Many Thanks

    Eric

  • September 13, 2010 at 6:45 am

    #1219997

    Personally I think MDX excels at time based calculations. I think it is one of the main reasons to use MDX.

  • September 13, 2010 at 6:49 am

    #1219999

    My question then would be: Is there any reason why we should exclude every MDX based calculated member in a cube?

    Would this be likely to kill the overall preformance of the cube?

  • September 13, 2010 at 11:27 am

    #1220209

    Generally I would create all common calculations & measures in the cube using MDX Expressions. Leave the ad-hoc and infrequently used calculations to the report unless the calculation is a particularly difficult calculation to do right.

    BIDN has a good article on time based calculations that would likely ehop you. I believe registration is required, but if you do this kind of work regularly it is a good site to use in addition to SSC of course.

    Here is the link.

    http://www.bidn.com/articles/mdx-and-dmx/169/mdx-time-calculations-done-the-right-way">

    http://www.bidn.com/articles/mdx-and-dmx/169/mdx-time-calculations-done-the-right-way

  • September 13, 2010 at 1:50 pm

    #1220299

    Thanks a lot, I'll have a look

  • September 14, 2010 at 7:27 am

    #1220659

    I have some very complex (at least they seem so to me) time queries in my cubes, and they don't kill performance. It is important, however, to have date components be unique. So don't have for the Year - Month -Day 2010 - May - 03 for example. This would be better as 2010 - May 10 - 5/3/10. You will then be able to use the attribute relationship as I've shown in the attachment. This will allow the queries to be as efficient as possible because the layers will be able to build on each other.

  • September 20, 2010 at 4:17 am

    #1223894

    RonKyle (9/14/2010)

    I have some very complex (at least they seem so to me) time queries in my cubes, and they don't kill performance. It is important, however, to have date components be unique. So don't have for the Year - Month -Day 2010 - May - 03 for example. This would be better as 2010 - May 10 - 5/3/10. You will then be able to use the attribute relationship as I've shown in the attachment. This will allow the queries to be as efficient as possible because the layers will be able to build on each other.

    An integer based time dimension such as 20100101 for 1st January 2010 is considered as a better practice for the reason that it occupies less space just 2 bytes in comparision to datetime datatype

    Raunak J

  • September 20, 2010 at 4:22 am

    #1223896

    This used to be advised by the Kimball group but they actually changed their mind in one of their later books (don't remember which one).

    Initially, they thought that a surrogate key was better as in any dimension but then thought that having a proper date type allowed easier calculations.

    This is all from the top of my head though... and my kids keep telling me that I am loosing it, so who knows! 😀

  • September 20, 2010 at 10:38 pm

    #1224500

    Just a note: Integer occupies 4 bytes - same as SMALLDATETIME.

    SMALLINT (2 bytes) is not big enough to keep 20100101 value.

  • September 20, 2010 at 10:42 pm

    #1224503

    The entire idea is to store date as an unique integer value for performance enhancements

    the default format of dd:mm:yy hh:mm:ss is not a good practice

    Raunak J

  • September 21, 2010 at 12:54 am

    #1224561

    The entire idea is to store date as an unique integer value for performance enhancements

    This isn't the entire idea. The calculations can also be done much easier. I can subtract 1535-1452 to get the elapsed days between two date integers. Had those been formatted into human readable as some advocate, 20100315-20091222, instead of 83 I would get 9093. BTW, I store my dates as smallints. This might not be possible for everyone with a lot of past information, but otherwise would be the better choice for most others. Every little savings helps.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply