A user pointed out an odd bug / inconsistency that occurs when using Excel pivot tables. Depending on how attributes are related to each other, Excel does some odd things when cross joining attributes from the same dimension on an axis.
Example 1. "Correct" cross join of attributes:
Connect to AdventureWorks from Excel pivot table. Drop in "Internet Sales Amount".
On rows add "State-Province" and "Total Children". Row 4 should read "State-Province, Total Children, Total".
Click the drop-down on "Total Children" and select "1".
The results should be filtered so that you only see states where there are internet sales for families with 1 child.
Example 2. "Incorrect" cross join of attributes:
Connect to AdventureWorks from Excel pivot table. Drop in "Internet Sales Amount".
On rows add "State-Province" and "Country". Row 4 should read "State-Province, Country, Total".
Click the drop-down on "Country" and select "United States".
The results *should* be filtered so that you only see states belonging to the United States. Instead, we still see states belonging to other countries.
Shouldn't the "United States" selection on the rows preclude the states from other countries? I don't understand why the pairs of attributes in examples 1 and 2 interact so differently. I would appreciate any meaningful explanation than I can pass on to the user.
Are you using Excel 2003 or Excel 2007? Not sure if I recreated the examples exactly or not, but in Excel 2007, it seems to behave as you expect it to. In the first example, I get the states filtered to just those that had customers with a total children value of 1. And in the second example, I get the states filtered to just those in the United States.
Unless I'm doing something wrong, this seems to work in Excel 2007. I don't have Excel 2003 loaded on my current machine, but could get to another machine to test this if you are indeed using Excel 2003...
Another question would be what service pack level you have installed for Analysis Services. I have SP2 installed (which I don't think would change the behaviour of Excel, but...) So, if you are using Excel 2007, what service package are you running for SSAS?
Dave Fackler
|||I started with Excel 2003. I saw the bizarre behavior. Then I saved the document and opened in Excel 2007. And still saw the bizarre behavior.
In 2007 I have to enable "classic layout" to get both "State-Province" and "Country" to both show up side-by-side (stacked) on rows. Then I just uncheck all countries and check only "United States". However the states and provinces of other countries are still displayed in the first column.
I'm on SP2 of SSAS. Thanks in advance.
|||
Is it possible that you have the Country attribute from the Geography dimension selected with the State-Province attribute from the Customer dimension (or vice versa)? Both dimensions contain geography attributes. While they are named the same, they represent attributes in different dimensions. Thus, they are related only through the fact table. That means that filtering of one will not filter values from the other (although the filtering does affect the fact data as expected).
Dave Fackler
|||The problem happens when the "State-Province" is added in rows with the "Country" of the *same* (Customer) dimension. We actually noticed the problem first in our production cubes and then reproduced it in AdventureWorks.
To be clear, add "State-Province" in rows so it shows in the *first* column, add "Country" in the *second* column, and then try to filter the country by the "United States". Unfortunately this doesn't preclude states from other countries.
This is such an obviously inconsistency but it only occurs when attributes are related to each other in the way that "State-Province" and "Country" are. I'm surprised that I haven't heard of other complaints. Also unusual is the fact that my own MDX expressions with these two attributes never return the same data that Excel is displaying.
Anybody else see this problem?
No comments:
Post a Comment