August 10, 2012 at 12:34 am
Hi All,
I have a cube with name 'SalesCube' with single fact table and few dimensions like 'TimeDim' and 'ItemDim'.
Measures are Qty, cost, Price etc...
Here after processing the cube... I have dragged 'MonthName', 'Item', 'Qty', 'Cost' and 'Price' into cube browser.
Everything working fine except price. The Price data was getting wrong.
Let me explain with an example....
In warehouse db we have data like...
TranDate(mm/dd/yyyy) Item Qty Cost Price
10/10/2011 XYZ 2 20 18
10/11/2011 ABC 2 10 15
10/12/2011 ABC 5 10 20
10/12/2011 ABC 2 10 15
etc.....
Now as I said, I have dragged 'MonthName', 'Item', 'Qty', 'Cost' and 'Price' into cube browser, the results are like...
Monthname Item Qty Cost Price
October 2011 XYZ 2 20 18
October 2011 ABC 9 30 50
For Item 'XYZ' thats correct. But for 'ABC' thats wrong. The price will be the AVG or other.
Please suggest, Thanks in advance.
Suresh.
August 10, 2012 at 5:05 am
In your sample data there isn't any data for ABC in Oktober 2011. Yet your results show there is. Did you link the dimensions properly to the fact table?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 10, 2012 at 5:20 am
Dimensions and Facts are properly related. here date field was in 'MM/DD/YYYY' format.
Thsnks,
Suresh.
August 10, 2012 at 5:24 am
suresh.muppidi (8/10/2012)
Dimensions and Facts are properly related. here date field was in 'MM/DD/YYYY' format.Thsnks,
Suresh.
Right, in Belgium we use dd/mm/yyyy. My mistake...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 10, 2012 at 5:29 am
So why exactly is price incorrect? 15 + 20 + 15 = 50.
If you want the average value, you need to calculate it yourself (unfortunately there isn't a built-in function). Just take the sum and divide it by the count.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply