Design OLAP cube and MDX query for my needs

0
votes

I have an OLAP cube with a time dimension [year > month > day month > hour]. Now I need to create some reports very fast such as report for all sales of yesterday (like: 07:00 AM - 265 sales), all sales for last month (like: 1st February 2013 - 6625 sales), and it's all goig easy & fast because for the yesterday I'm querying: "WHERE {Hierarchize({[Date].[2013].[2013-2].[2013-2-1]})}" and for the month I'm querying :"WHERE {Hierarchize({[Date].[2013].[2013-2]})}".

But now I need also to create a report for the last 30 days. Any ideas how can I make this report as fast as the last month report? Thanks!

1
mdxolapolap-cubemondrian

1 Answers

0
votes

You need to look at the CurrentDateMember function, see Diethards blog here for a comprehensive explanation:

http://diethardsteiner.blogspot.co.uk/2009/10/current-date-function-on-mondrian.html

As for performance - the only solution is to turn on the various mondrian logs, and check that firstly there is no slow SQL - if there is then fix that in the database first - chances are a missing index or something.

Finally if it's still slow then post the full mdx. You dont have 2 separate date sets do you?