In the excel pivot I have the list of departments and sum of cost of items, the items bought isn’t necessary at this stage but I can swop the sum of cost of item with the count of items sold, which gives me the number of items sold per dept.
If I take the sum of costs of item per department and the count per item sold per dept, and divide sum of cost per dept by number sold it gives me the average cost per dept. So when I do this in the BO it gives me slightly higher figures.
The number of items sold per dept are a match but the sum of cost is not when I compare the BO figures with what I have in excel.
It may be a rounding issue. Without knowing or seeing your data I would hazard that as a guess at the moment. Does the raw data match in terms of counts and costs being the same for each?
I just checked the count of rows in my BO extract for one dept and it matches the rows count from excel, the cost per items also matches each part, there are no extras or differences either.
When I extract to excel and sum it matches what I had in the original excel but as I say when in BO it gives me a bit more, roughly about 10-20 more per dept.
There is no proper measure definition, because SQL aggregation is missing from your SQL statement. Revisit your measures in the universe and make sure you have some SQL aggregation (SUM, COUNT…) in the definition (SELECT box) of your measures.
Raw Excel data as a data source, which is also pivoted in Excel and the pivot doesn’t match the BO crosstab but the underlying raw data in BO matches the underlying raw data in Excel?
No, one data source from BO universe, when I pull that data straight to excel and pivot it I get one set of figures, if I replicate that excel pivot in BO I get different figures using the same data source as I used to pivot in excel.
Again, so you used MS Excel as data source for a universe? If so ensure your universe measures are defined properly -> Using a SQL aggregate function as well as projection is IMHO a definate MUST, see: SQL Aggregates
It does NOT MATTER, you are using a universe, right?
In any universe (MS Excel based or relational database based) the measures should be defined properly as outlined in my link.
Do you get the right result, if you create this measure in Webi?
That sort of universe measure will only calculate correctly at SQL run time, by the dimensions selected, not during slice and dice or summarisation (unless you make the measure database delegated).
Ok I do not have the aggregation in the SQL but how do I get measure aggregation into the sql statement? I can only add the measure to the results set not into the query?
Ok after a while I came back to this and also have access to the DB. I tried running the query in the DB with a sum on it and I got the same result as in BO. I took the raw data into excel and got a slightly different number for the summing of my values per dept. A count will match fine.
There’s something going on in excel when I create the pivot, someone suggested rounding but the values imported are already whole numbers, whether there is something in the DB I don’t know.
I now have access to the DB and found that there are duplicates in one of the tables. In BO this duplicate row is not shown but when I do a sum in BO it adds the duplicate values, but in excel it just adds what was extracted from the raw data i.e. non-duplicate data.
This would explain why I was getting different numbers between BO and excel for sum. I don’t manage the job that populates the DB nor the structure of but at least now it can get investigated and fixed.
(BOB member since 2002-06-20)
(BOB member since 2005-01-06)