Anything is possible with MDX

I was with one of my favourite customers last week and had an impromptu challenge put before me. We had a cube containing the company's sales data and the challenge was to reproduce a report they had created in their legacy system. (I found out later that this report had taken a couple of months to produce.) The requirement was to create a report that would display the last 7 days of a particular outlet and for each day show the sales and transactions for the top hour of that day.

I have often been quoted as saying that if the data is in the cube, one MDX query can answer any question that you might have. So the pressure was on to create the report. Fortunately, they were using ReportPortal software, which is a highly functional thin client tool, and it has good charting capability. The web browser tool allows you to create your own sets and calculated members. So all I really needed to do was create a set for the last 7 days. I will reproduce a similar query using my weblog database, which has comparable dimensions.

Here is a set declaration for the last 7 business days.

tail(nonempty([Date].[Year Month Day].[Day],[Measures].[Bytes Total]),7)

The calculated members for the top hour etc are simply a matter of taking the top hour and getting the attribute/tuple require. Here is the full query on my weblog database. Note, the good thing about a tool like ReportPortal is that you can declare sets and calculated members and it will still honour filters and hierarchies that you have dragged and dropped on rows/columns. So the only MDX you need to write is for the set and calculated member, the rest of the report is created by dragging and dropping from the palette.

with
set Last7Days as tail(nonempty([Date].[Year Month Day].[Day],[Measures].[Bytes Total]),7)
member Measures.TopHourName as order([HoursOfDay].[Hour Of Day],[Measures].[Hits],BDESC).item(0).member_caption
member Measures.TopHourSessions as (order([HoursOfDay].[Hour Of Day],[Measures].[Hits],BDESC).item(0),[Measures].[Sessions])
member Measures.TopHourHits as (order([HoursOfDay].[Hour Of Day],[Measures].[Hits],BDESC).item(0),[Measures].[Hits])
select
{Measures.TopHourName,Measures.TopHourSessions,Measures.TopHourHits} on columns,
[Date].[Month].[Month]*Last7Days on rows
from EasternMining
where ([Client Host].[Client Geography].[Region].&[Americas].[UNITED STATES])

Like all good cube browers, ReportPortal will graph your results. In this case, the most appropriate chart was a hybrid column/line chart, with sales as columns on the left hand y axis and transactions as a line on the right hand y axis.

The takeaway from this is that, so long as the cube has the necessary data, a single MDX query can get the answer to any question.