We help IT Professionals succeed at work.

MDX - Children Sum or Max Measure

WaterAid
WaterAid used Ask the Experts™
on
Hi there

I wonder if you can help...  I have a very simple report that details the performance of mailing and I'm having trouble displaying some of the data correctly.  The mailing is sent to a number of different segments and the performance of these segments is rolled up at group level.  The view this report is based on pulls together two tables, one which contains the topline details of the segment (volume per segment, cost etc) and the other, primary table,  for responses (Income, Date etc).

When trying to detail the Volume for each Segment, I've just used the MAX function - this works fine is you only view data at the Segment level.  However, when adding in the Segment Group parent dimension, the MAX function simply picks out the highest value from the children Segments (highlighted in red).

What MDX can I use to accurately capture this data?  ie To get that cumulative Volume to 6480?!
And is there a more truthful way of deriving that figure for the Segments too?!

With thanks Problem Measure
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Can you post your existing MDX?
Also - you say that using MAX works fine when viewing at Segment level, but then ask for a 'more truthful' way of getting the value. Do you suspect that the figure returned by MAX is incorrect at segment level?

Author

Commented:
Hi

Sorry, perhaps I should have made this clearer...  The Volume associated with each Segment are stored on the Segment summary table - but when creating the view that links Segment summary data with the transactions table, the Segment Volume is recorded against each transaction.  So we end up with a view table that contains:

Transaction ID | Transaction Segment | Transaction Value | Transaction Volume
Tr001               | 2010/MAR/A1            | £10                         | 1022
Tr002               | 2010/MAR/A1            | £20                         | 1022

etc...

I use the MAX syntax as a sum would now detail 2044 with the above data.

However, the MAX statement is inappropriate when I try to aggregate at the higher level of Segment Group.

I'm confused now - you seem to be describing a solution that is using SQL, not MDX. Can you post the sql that is used to define the view? I presume you are basing the report on the view as a datasource? The view sql would be useful as it would explain the scope of the Max function that is then applied to the transactions (per segment?)
Also - is the view correct, or should - in the above example - the £10 transaction value only have the number of transactions that led to the £10 value, rather than the volume for the whole segment?

Author

Commented:
Hi Tim

The report that I'm using is based on a SQL view that OUTER JOINS two tables - one of transactions (let's call it TRANSACTIONS) and another of what is essentially topline campaign data (CAMPAIGN) - segment names and descriptions, volume of mail pieces sent out, costs etc.  The report I'm preparing in Analysis Services looks to report on the number and value of transactions that have been generated by each segment.  

So, I need the Volume (number of mail pieces sent out) from the CAMPAIGN and the value and number of transactions from the TRANSACTIONS table.  Bringing these together in a View means that the CAMPAIGN data is associated with each transaction (as per the sample above).  

CAMPAIGN

Segment Name | Volume | Cost
Segment 1        |  1022    | £1000
Segment 2        |   490     | £450
Segment 3        |   372     | £300
Segment 4        |  1384    | £900  

TRANSACTIONS

Transaction ID | Transaction Segment | Transaction Value
Tr001               | Segment 1                 | £10
Tr002               | Segment 1                 | £5
Tr003               | Segment 4                 | £15

Joining these two tables on (Segment Name = Transaction Segment) results in:

Transaction ID | Transaction Segment | Transaction Value | Volume
Tr001               | Segment 1                  | £10                         | 1022
Tr002               | Segment 1                  | £5                           | 1022
Tr003               | Segment 4                  | £15                         | 1384

Basing a cube on this view, I want to pull together a summary that reports by Segment the Volume of mail pieces distributed, value and number of responses.  Using MAX to define that Volume measure is fine as it picks the highest value for each Segment from the view data (so here, 1022 for Segment 1).  I could create a measure that divides the SUM of Volume for all members by the number of members but this somehow doesn't seem right.

Using MAX causes a problem when aggragating this data at a higher level (Segment Group) as it obviously won't total the individual Segment Volumes.

Maybe I'm asking the impossible...!
Ok, I think you're going about this the wrong way. You shouldn't be building a view that combines the transactions together and then basing a measure group on that. You should build a dimension on your Segment (and possibly on transaction if you think anyone will ever want to analyse at that level - although you could drill through instead, so I'd leave the individual transaction level out) and two measure groups that share the Segment dimension - SegmentData and Transaction Value.
When you then construct your MDX statement that returns the sum of the volume by Segment and sum of  transaction volume by Segment it will just work. The MDX query engine will effectively select the correct underlying values - so you'd get £15 and 1022 for Segment 1. The MDX would be something like

SELECT {[Measures].[Segment Volume], [Measures].[Trans Value]} On COLUMNS,
[Segment].[Segment].Allmembers ON ROWS
From <your cube>

Or you could just use SQL, unless you see some future in the use of Analysis Services, and base the report on some sql such as :

select c.SegmentName, sum(TransValue) TransValue, count(m.TransID) NumTransactions, Min(SegmentVol) SegmentVol, Min(SegmentCost) SegmentCost
from Campaign c
left outer join MailTransaction m
on c.SegmentName = m.SegmentName
group by c.SegmentName

which would return:

Segment Name, TransValue, NumTransactions, SegmentVol, SegmentCost
Segment 1      15      2      1022      1000
Segment 2      NULL      0      490      450
Segment 3      NULL      0      372      300
Segment 4      10      1      1384      900

Tim

Author

Commented:
Hi Tim

I see what you're saying with the SQL report - yes, this woudl work, but I publish this report through an html OWC pivot-based front end that enables end-users to add/remove other dimensions in so I does need ot be AS-based..

Sorry, I didn't totally understand your point about the View.  Are you saying I shouldn't be building this type of view at all?  How then do I create a summary of the transaction data (value and number) to associate with the CAMPAIGN data?  There could be other data such as Transaction Date to bring into the report, so this might require each transaction to be represented.

Thanks
No, I wouldn't build such a view for the DSV. You let Analysis Services do the work. Build the dimension from distinct Segment names. Build a Transaction dimension from distinct transaction ids. Build a measure group for segment data to hold the Segment dimension reference and the Volume and Cost data. Build a measure group for Transactions with both Segment and TransactionID references and transaction value.
Once you've built a cube using these you will be able to drop the measures into a query and get values by segment and drill down to transaction id etc.

I just built this cube based on the above instructions and got this when browsing the cube: looks correct to me.


CampaignCube.jpg

Author

Commented:
Hi Tim

Sorry I didn't get back to you sooner - I've been off this project for a short while.  I understand the logic in what you've outlined above, but I think my limited knowledge of Cube-building is letting me down.  

I thought that a cube based on a View was the only way to get measures and dimensions from different fact tables into a single report.  How do I go about building a measure group that includes the relelvant CAMPAIGN data as well as the TRANSACTION values if I can only derive the measures from one table?  

With thanks,
Simon
Hi,

No - one of the advantages of having information structured within a cube is that you can combine measures from multiple measure groups into a query / report very easily as long as the measure groups share at least one dimension - in this case Campaign (via Segment name).

For my test scenario I created the two tables:

CREATE TABLE [dbo].[Campaign](
      [SegmentName] [varchar](50) NOT NULL,
      [SegmentVol] [int] NULL,
      [SegmentCost] [int] NULL,
PRIMARY KEY CLUSTERED
(
      [SegmentName] ASC
)

and

CREATE TABLE [dbo].[MailTransaction](
      [TransID] [varchar](50) NOT NULL,
      [SegmentName] [varchar](50) NOT NULL,
      [TransValue] [int] NULL,
PRIMARY KEY CLUSTERED
(
      [TransID] ASC
)

and populated them with your example data.

In BIDS, I used the wizards to first create the dimensions: right click on Dimensions / New Dimension...Use existing table, chose Campaign (Segment Name is selcted as key)... then repeat to create the Mail Transaction dimension from the MailTransaction table.
I then created a New Cube, selecting both tables as measure group tables and all of the default measures to include and it also then selected the existing dimension references. Think it might have suggested creating a 'Campaign 1' duplicate dim as well, but I deselected that.

Then when processed, it is is possible to drop the values from the separate measure groups into the cube browser as in the screen shot.

Does that help?

Tim

Author

Commented:
Tim, that's brilliant - thanks!

I'm working with SQL Server 2000, so I wasn't yet up to speed on the 2005 functionality.  I just built this on a server with 2005 and pulled the data through from 2000.  It's spot on.

Thanks so much for your help!  Much appreciated.