Saturday, September 18, 2010 4:57 PM
New SQL Server 2008 install on Windows 7 Ultimate. Install SQL Server 2008 with SSAS and I seem to successfully build the cube.
Drag date onto the left. Make Sales Amount the single measure. And put Country as the y axis. (no Page field dimension).
Scanning the values from right to left, that is, for a given year the values for each country, all values are the same. E.g. CY 2001 shows a value of 11,331,808.96 for all countries as well as the Grand Total.
I didn't see any error during the cube build and yet aggregations (or something) seems clearly to be wrong.
I get this same incorrect statement of the data using whatever tool. Say 2008 Management Studio vs targeting the cube from an Excel 2003 spreadsheet Pivot Tbl.
Thursday, September 30, 2010 5:32 PM
... are you working with 'Internet Sales', 'Reseller Sales' or the 'Total Sales' facts; also from which dimension did you drag the 'Country' column?
Unfortunately, the relationships in the model are not that crispy clear as we might have wanted for a learning aid.
As for your problem, most of the time when you see that values are all the same as the 'All <dimension name>' it reflects the fact that there is no relationship between the fact table, from where you are dragging the measure, and the dimension, from where yo are dragging the attribute. This is due to the fact that all measures must roll up to the 'All Level' of all dimensions, then when you try to slice a measure by an unrelated attribute you get the 'All <measure>' for all slicing values of the attribute.
If you open your solution, go to the [Adventure Works] cube tab and cllick the 'Dimension Usage' tab ... you would be able to see that the [Geography] dimension only has relationships with the [Reseller Sales] and [Reseller Orders] measure groups.
So, if your attempt was to slice 'Sales Amount', from [Internet Sales] over 'Country', from [Geography], then you are bound to get the result you are telling us that you get. If on the other hand, you are getting those results while slicing 'Sales Amount', from [Reseller Sales]... then I would llike to have more input from your scenario to help you.
Hope this helps you understanding that there is nothing wrong with your model or the way it was processed.
Saludos, JuanPablo Jofre
- Edited by JPJofre Thursday, September 30, 2010 6:39 PM Extending the answer