Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SqlSimon
Starting Member
4 Posts |
 Posted - 2010-06-15 : 10:02:48
|
| Hi,I hope someone can shed light on a problem I noticed in the data coming from a SSAS 2005 cube.I have sales data in the Fact table and a Warehouse Dimension.Warehouse belong to Divisions in the company structure.Division is a direct attribute of Warehouse, in the Warehouse dimension.If I executed the SQL statement:SELECT SUM(DLYS_Sales_Qty) FROM FACT_Daily_SalesINNER JOIN DIMN_Warehouse ON WHSE_Code = DLYS_Warehouse_CodeWHERE WHSE_Division_Code = 'DIV1' AND WHSE_Code IN ('WHS1','WHS2')WHS1 and WHS2 both belong divison DIV1.I get a total of Sales Qty of 351,211. Which is fine.In MDX, what I believe to be the equivalent MDX:SELECT [Measures].[Sales Qty] ON columnsFROM [cube_DailySales]WHERE([dim_Warehouse].[Warehouse Division].[DIV1], {[dim_Warehouse].[Warehouse].[WHS1],[dim_Warehouse].[Warehouse].[WHS2]})I get a mystery figure of 1,233,393. Which is neither the total of all DIV1 (2.5m) or a total of filtered warehouses (350k).If I put the warehouses into the output of the MDX query:SELECT [Measures].[Sales Qty] ON columns,{[dim_Warehouse].[Warehouse].[WHS1],[dim_Warehouse].[Warehouse].[WHSE2]} on rowsFROM [cube_DailySales]WHERE[dim_Warehouse].[Warehouse Division].[DIV1]I get the correct breakdown:WHSE1: 215,589WHSE2: 135,622Which would give a total of 351,211 as expected.Any ideas what I might have done wrong, or what the first MDX statement is actually doing?The dimn and fact table are directly related in the DSV of the cube.I have rebuilt and copied to cube and dimension to another SASS 2005 server and the same results are returned there too. |
|
|
|
|
|
|
|
MDX Confusion