StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POAdding a dimension to a SQL Server cube inflates all measures
text
Body
copied!<p>I have a SSAS cube that has worked fine for a while. I recently added a new dimension and now all the measures are vastly inflated. For instance, a count that should be about 300,000 for the whole model (i.e. with no filters at all) is now 1,500,000. All the measures I have checked are affected. The measure values are always way too high, but not be any consistent factor (i.e. some are 5 times too big, others 10 or 20 times too big). The errors are present whether or not the new dimension is included in the query.</p> <p>I went back to a previously working version and tried again, making sure that the only changes I made were:</p> <ul> <li>Add a new table in the dsv </li> <li>Create a new dimension based on that table and add it to the cube </li> <li>Define referenced relationships in the dimension usage matrix to link the new dimension to the measures via one of the other existing dimensions</li> </ul> <p>My caution didn't pay off -- the second attempt gave the same result as the first, which points to a conceptual or design problem. Any ideas where I might look for my mistake?</p> <h1>More Details</h1> <p>The cube structure is as follows, with the recently added Church table highlighted:</p> <p><img src="https://i.stack.imgur.com/jqdVG.png" alt="Cube structure"></p> <p>The cube has many measure groups, but since all seem to be adversely affected by the introduction of this new dimension, I'll focus on just the one labeled "cm". We are a charity organisation that raises money for children in poverty around the world. "cm" represents a financial commitment that a supporter ("con" means "constituent") makes to child in need. Analysing the number of commitments by various con attributes has worked fine for a long time. We can also successfully analyse cm's by attributes in constituent_address via a referenced relationship. </p> <p>Since most of our constituents belong to a church of some form, we wanted to add church details to the cube (e.g. church name, church size, denomination). We could then, for instance, count the number of commitments made by constituents who belong to Baptist churches. I added the Church table, and modified the con named query to include a church_id. (I also checked that the new con query returns exactly the same number of records as the old one, so I don't think the ensuing problem is caused by the con table changing.)</p> <p>Here's an extract of the dimension usage matrix:</p> <p><img src="https://i.stack.imgur.com/fo8Q1.png" alt="Dimension usage"></p> <p>And here is a before and after example. The first shows the correct count of commitments before the Church dimension was added. The second shows the same measure, built from exactly the same data, with the only changes to the cube being the addition of the Church dim. As you'll see, the new Church dimension is not explicitly included in the query but the measure now shows a commitment count of 1,574,823 rather than 315,057.</p> <p><img src="https://i.stack.imgur.com/ufHOJ.png" alt="Before"> <img src="https://i.stack.imgur.com/IWDwv.png" alt="After"></p> <p>--Matt</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload