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 |
|
sqlprogrammer
Starting Member
3 Posts |
 Posted - 2005-07-14 : 02:08:40
|
| I have a Sales Margin cube having Revenue,Cost,Margin as measures and customer,sales invoice,product group,time,area as dimensions.When i run the following MDX query, i get a value for revenue which is double the value i should get.I checked the fact table, value in the fact table for this measure is correct.Any ideas ?? select {[Measures].[Revenue]} on columns, non empty [Sales Invoice].members on rows from [Sales Margin] Regards |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-07-14 : 04:14:58
|
| Did You do an incremental update of Your cube ?rockmoose |
 |
|
|
sqlprogrammer
Starting Member
3 Posts |
Posted - 2005-07-14 : 06:21:23
|
| I did not, just in case, i did a full process after reading your reply and tried the query again, it still gives the incorrect result. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-07-14 : 06:51:34
|
| It definitely sounds like a duplicate problem.Are the other measures also double, or just this one ?You can add a simple COUNT measure, and make sure that this one shows up with the same # of rows as the facttable.(I quite often have a #OfTransactions measure, I find these to be quite handy, and the users find them at least semi-interesting)In cases like these, I check the JOINS in the cube designer, and the DATA in the fact+dimension tables,and do a full process of dimensions+cube (as You have done).There is a possibility that a dimension table has double rows, counting everything twice.rockmoose |
|
|
|
sqlprogrammer
Starting Member
3 Posts |
Posted - 2005-07-14 : 09:08:05
|
| Thanks,rockmoose,that was brilliant. One of the dimension tables - DateTime - had duplicate records and so all the measures in any MDX query were showing duplicate values.It is solved now. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-07-14 : 09:13:26
|
quote:
Originally posted by sqlprogrammer
Thanks,rockmoose,that was brilliant. One of the dimension tables - DateTime - had duplicate records and so all the measures in any MDX query were showing duplicate values.It is solved now.
You are welcome.Put a primary key on the table - DateTime -,if the pk already is an identity or such, put a unique index/constraint on the "date" column.DateTime is reserved word, maybe not a very good name for a table...Always have pk / unique constraints on all your tables, and You will have eliminated this particular kind of problem  rockmoose |
|
|
|
|
|
|
|
|
MDX query returns double values