| Author |
Topic |
|
Joozh
Posting Yak Master
145 Posts |
 Posted - 2004-11-18 : 06:28:08
|
| Hi,I am really stuck here and need some help.The scenario is that I have a dimension called Product... each product is given points and this is defined in the underlying product table.The table on which my fact is based has a field called QtySold (which is the qty sold for the product). WhatI want is to somehow use Calculated Member (if that is the right solution) so that for any selected product, the user get's to see a calculated field called, say' Points earned, which will be QtySold multiplied by the points for that product.Hoping that my question is clear, can someone please help me find a solution.Billions of thanks in advance.Thanks & Regards.-J |
|
|
jojo_jacob
Starting Member
24 Posts |
Posted - 2004-11-18 : 06:59:11
|
| The following logic might work:Create a Member Property for Points in Product Dimension using the Points column of the table.Create a Calculated Member in the cube as[Measures].[QtySold]*StrToValue([Product].currentmember.Properties("Points"))Lemme know if this worked for u.Regards,Jojo |
 |
|
|
Joozh
Posting Yak Master
145 Posts |
Posted - 2004-11-18 : 07:14:08
|
| Many thanks for the reply. The solution seems to be working if I select a product. But if I am at the ALL product level, then I am getting a #ERR???In themeanwhile, I tried the following and that too seems to be giving the right results. Can you comment if the following approach is okay:I made Points a Measure and set it's Aggregate function property to 'Min'.Then I created a Calculated Member as follows:[Measures].[Points]*[Measures].[Qty Sold]To be honest, I am very new to MDX and thus am not sure if what I have done is the right approach or not :( Can you kindly comment/guide and once again many many thanks for your reply.Regards.Thanks & Regards.-J |
|
|
|
jojo_jacob
Starting Member
24 Posts |
Posted - 2004-11-18 : 07:39:07
|
| So, 'Points' is in the fact table??? How did u create it as a measure if it is in the Product dimension table?Jojo |
|
|
|
Joozh
Posting Yak Master
145 Posts |
Posted - 2004-11-18 : 23:25:48
|
| Hope these details will clarify:I have sales transactions in a table. This table has SaleDate, Product Code (Code of Product Sold), QtySold, CustomerCode, etc. I made a view which joins this table which the Product 'Master' table which contains ProductCode, Product Name, ProductCategopry, ...., and the Points for each Product....Now I am populating my fact table using this view (via a DTS) andthatis how I am getting the points in the fact table.I hope this clarifies.Thanks & Regards.-J |
|
|
|
jojo_jacob
Starting Member
24 Posts |
Posted - 2004-11-18 : 23:42:37
|
| Ok, 'Points' is there in the fact table. Then, why are you using 'Points' as a measure with MIN aggregation? That will result a wrong value in the upper levels (like [ALL Product]). You need aggregated total points in that level too, right?You can simply make a measure with source column as ("dbo"."Fact_Table_Name"."QtySold")*("dbo"."Fact_Table_Name"."Points") and give it a SUM aggregation. This will give u the correct results for all the levels. |
|
|
|
Joozh
Posting Yak Master
145 Posts |
Posted - 2004-11-19 : 01:18:39
|
| Hmmm.... maybe I doing a good job in making this unnecessarily complex?? :(Let me try to re-phrase the scenario (i'll use actual figures so that both of us can follow each other - sorry about this in advance):Suppose I have a product XYZ whose point is 1500.25. Now 1,562,099 of these products are sold for a given time period.All I want is to get the simple result of Qty Sold * Point as 'Points Earned' and get the result of 1500.25 * 1,562,099 = 2,343,539,024.75 as the correct answer.However this is what happened:1) I was using the SUM aggregate for my Point measure and it gave me a result of 2,113,852.25. This ws ofcourse wrong as it is simply summed up the Points of all the transactions in which product XYZ is sold. Are you with me so far. I hope I'm being clear???2) And then when using a Calculated Member and using the formula [Measures].[Points]*[Measures].[Qty Sold] I multiplied this number (2,113,852.25) with the Qty sold of product xyz (i.e. 1,562,099) I got the result of 3,302,046,485,873 which is incorrect.What I SHOULD GET is 1500.29 * 1,562,099 = 2,343,539,024.75Thus to avoid the problem, I used MIN aggregate instead of SUM and it gave me the correct result.I apologize if I have not been clear and I hope that this post clearly explains the whole scenario??Thanks & Regards.-J |
|
|
|
jojo_jacob
Starting Member
24 Posts |
Posted - 2004-11-19 : 02:06:06
|
| Yes J, it is very clear now. However, I am not able to make out how MIN works when u go one level above where you have multiple products (products has different points and it will take the MINIMUM points from all the products and multiple that with total qty sold) For Example, if you have only two products A and B with points 100 and 200. Let the qty sold for A and B are 3 and 7 respectively. So, the report should showTotal 10 1700ProdA 3 300ProdB 7 1400right?If the MIN is used, then the result would beTotal 10 3000 (since 300 is the min value for Points at this level)ProdA 3 300 (here it is fine as there is only one product)ProdB 7 1400 (here it is fine as there is only one product)May be, I am missing something here or I am too stretched :-) |
|
|
|
Joozh
Posting Yak Master
145 Posts |
Posted - 2004-11-19 : 02:19:51
|
| Hi Jacob,Yes you are correct that the MIN will give incorrect result if I go at a level above the particular product. I understand what you are saying but I am thinking that ifwhat Iam doing is correct thenin my front-end (Microsoft Data Analyzer), I will not show the Point and instead will only show the Points Earned and that will be the simplest solution.I too am finding this MDX stuff really VERY VERY hard to digest... as it is, it took me a while to get hold of Analysis Service, making and cube, etc. etc. by reading on-line help,posting queries like this one and getting help and now it seems like the real challenge is just beginning (as I feel the need to really learn MDX)... This is quite painful and no fun (trust me).I have been searching the web for a decent site from where I can learn MDX but all I can find is links to Amazon to buy books :(Regards.Thanks & Regards.-J |
|
|
|
jojo_jacob
Starting Member
24 Posts |
Posted - 2004-11-19 : 02:27:53
|
| It is not that difficult J. What I suggested was NOT to have a Calculated Member. Instead, have a simple Measure (called [Points Earned]) and use ("dbo"."Fact_Table_Name"."QtySold")*("dbo"."Fact_Table_Name"."Points") as the Source Column ... you need to edit the column ... no builder is provided for it. So, select one measure and then edit the Source Column property to have this calculation. Remember, this is not a Caluclated Member as far as Analysis Services is concerned. We just provide the calculation as simple SQL multiplication.Well ... this is just for the information. If you have the desired output correctly, why bother? |
|
|
|
Joozh
Posting Yak Master
145 Posts |
Posted - 2004-11-19 : 02:35:54
|
quote:
What I suggested was NOT to have a Calculated Member. Instead, have a simple Measure (called [Points Earned]) and use ("dbo"."Fact_Table_Name"."QtySold")*("dbo"."Fact_Table_Name"."Points") as the Source Column ... you need to edit the column ... no builder is provided for it. So, select one measure and then edit the Source Column property to have this calculation. Remember, this is not a Caluclated Member as far as Analysis Services is concerned. We just provide the calculation as simple SQL multiplication.Well ... this is just for the information. If you have the desired output correctly, why bother?
Actually I did try your suggestion just as you have explained but I get a error message saying "the column name is not valid" I wish I knew what I am doing wrong??Thanks & Regards.-J |
|
|
|
jojo_jacob
Starting Member
24 Posts |
Posted - 2004-11-19 : 02:40:09
|
| Ok, lets make it simpler ... have that multiplication done in the view itself and use that column as the measrue :-) I didn't want to disturb that RDBMS object ... The calculation in the source column works perfectly and I have used it many places. May be, the owner of the table is not DBO ... try that SQL in Query Analyzer and find out the absolute table.column name. |
|
|
|
Joozh
Posting Yak Master
145 Posts |
Posted - 2004-11-19 : 03:55:09
|
Hi Jacob,Thank you for your prompt replies and help. Before I say any further, plese accept mysincere and a BIG thank you.Your following solution worked just fine after I realized that I was making a spellingmistake in referring to the QtySold field (in my underlying view which is used to populate the fact, the name of the field is ProdQtySold and not QtySold. I guess I am/was so tired and frustrated that I overlooked this...Sincere thanks & regards.quote:
... Instead, have a simple Measure (called [Points Earned]) and use ("dbo"."Fact_Table_Name"."QtySold")* ("dbo"."Fact_Table_Name"."Points") as the Source Column ... you need to edit the column ... no builder is provided for it. So, select one measure and then edit the Source Column property to have this calculation. Remember, this is not a Caluclated Member as far as Analysis Services is concerned. We just provide the calculation as simple SQL multiplication.
Thanks & Regards.-J |
|
|
|
|
Tricky Question about using Calculated Member in A