I have many universes, and for all universes I have set measures to sum, where appropriate. However, this seems to work sometimes, and not others. I can't work out when or why it does or doesn't! Does anyone have experience of this?
This is vague. How do you know it's not working? Are you building Deski reports (I'm assuming since you posed in the Deski forum) with the measures and teh totals are wrong?
Thanks for response, and apologies for vagueness! Yes, I am building deski reports, and sometimes the aggregate sum fields total correctly in the report, and sometimes they don't total.
Do you have visibility to the universe(s) via Designer? If so, it would be good to verify that metrics are built properly, i.e. use of sum() or count() as appropriate. If a count() is placed where it should be a sum(), then that could be a cause. Also while talking about the universe, ensure that relationship joins between tables are correct as well.
I meant, do you have permissions and capability to open up and examine the universe(s)? In some deployments, you have a select few people assigned to maintain universes via Designer and only they are allowed to administrate. If you wear both hats, i.e. universe maintenance/access and Report Writer, then the answer would be "yes"....
if you are the universe designer, note there are two levels of aggregation.
a) done by the database (when you use aggregation functions like sum(), count(), etc.
these will be calculated on the lowest level needed (you see which in the group by clause)
b) done by deski (or webi for that matter). This is done when deski needs to aggregate further beyond what the database has done. The type of aggregation done there is defined in the advanced tab in the object definition. When you change the object select clause, this gets reset to the default (sum for count and sum, min or max when those are used in the select.
Obviously, if you have a dimension object, only the first type of aggregation is available and the object will not sum, min, max in the report (so i.e. going to the total line).
So the first thing you would want to do is check the objects are measures and both are set up correctly.
Then I have a suspicion because you tell me the totalline doesn't sum up correctly.
My question is, are you using multiple dataproviders?
If so, the only way to ensure measures are calculated correctly across multiple dataproviders, is by using the multicube() function. So in stead of just putting the object in the tableblock,
Thanks for your response, Marianne. All my objects which I need to be summed, are set to measures, with the aggregation set to "Sum". However they do not sum. Some questions:
- Re your point (a), where can I see this in the group by?
- Re your point (b), where is the advanced tab in the object definition? I am trying to find this to see what this is set to.
The a) aggregation is done by the group by clause in the actual select that is sent to the database,
so it differs depending on what the user selects when building the query.
But it will do this using the aggregation function you specified in the select.
So if you have
measure object salessum(sales.amount)
dimension objects countrycountry.id and storestore.id
Then the select for a query containting the two objects sales and country would have a group by country.id
The select for the query containing all three objects would have a group by on country.id,store.id
You can verify the actual group by by looking at the generated SQL in the query builder.
The b) aggregation is done according to what is specified in the advanced tab.
You see this tab when you are in Designer and open the object properties dialogue.
Its the second tab in it. (first is the select and where box, second it the aggregation (top part) and list of values (bottom part).
So say you selected all three objects and then created a table with only country and sales, the sales figures (that were queried per store, per country) will be aggregated according to that 2nd tab to the country level.
Note that when you use an aggregation function in the select, Designer will make a best quess as to the aggregation wanted and pre-fill it. So for the sum() function, it will also use sum as aggregation in 2nd tab.
Hope this helps
P.S. looking at the actual generated SQL will also help you determine if you have more that one select done to fullfill you dataprovider. If you have more than one, it is only acceptable if the differences are in measures only. If not, you will get more than one cube back from your query, without any control over how they are synchronized. If so you can try multicube to get the results you would expect (i.e. seen as one big cube i.s.o. more than one).
Thanks again for your response. I cannot see any group by when I view SQL in the report query panel.
I have found out some new information. Maybe if I explain my setup, you can help. I have a universe with some classes. One of the classes has the the following columns:
- col1 (dimension)
- col2 (dimension)
- col3 (dimension)
- col4 (dimension)
- col5 (measure, aggregation "none")
- col6 (measure, aggregation "sum")
I have a report which selects only col1, col3, col5 and col6. The report does not group by col1/col3/col5, to show a sum of col6. There is no group by.
If I change col6 to a dimension, and re-run the report, there is a group by col1/col3/col5! Do you know why?
If you have col1, col3, col5 and col6 in the query and the SQL-statement does not show any GROUP BY (when you click on the SQL button in the query panel) that would mean that in the 'SELECT' part of the SQL-statement no SUM is defined. E.g.
SELECT field1, field2, field3, SUM(field4)
FROM table
WHERE condition
GROUP BY field1, field2, field3
If there is a SUM and no GROUP BY then there is something wrong with the installation and the DBMS would return an error message when you try to run the query.
But if there is no sum at all in the SQL-statement then the measure object in the universe is not defined properly. Even when you set the aggregation to 'sum' in the advanced tab, this is only affecting the way deski is coping with the measure within the report itself.
So let's go through the different possibilities:
1. measure object defined with sum: =Sum(tablename.fieldname) and the aggregation is set to sum. Normal behaviour would be that the SQL-statement has a GROUP BY (when there are also other dimension object within the SELECT) and default aggregation within the report would be sum.
2. measure object defined without sum and the aggregation is set to sum. Then there would be no GROUP BY in the SQL-statement. The result however shown in the report is aggregated. In this case the database would return all rows not aggregated (depending on the condition).
3. measure object defined with sum: = Sum(tablename.fieldname) and the aggregation is set to none. The SQL-statement should have a GROUP BY and the result from the database should be aggregated. However deski will treat the measure as not being an aggregate.
Maybe if you shed some light on what col5 and col6 really are and how they appear in your SQL-statement we could give you some better indication of the problem.
Also, note that in both cases, where aggregation happens and also where this is no aggregation, there is no Group By in the select when I show SQL in deski.
Very good! This is exactly what I was trying to explain in my previous response.
First let's have a look at the sql-statements. Both don't have a 'GROUP BY' because there is no aggregation defined in the SELECT part of the statement. You would expect a 'GROUP BY' when the sql-statement would look like:
SELECT
ECLIPSE.TRAD_CLEAR_CHG.ACCODE,
ECLIPSE.TRAD_CLEAR_CHG.CCY,
SUM(ECLIPSE.TRAD_CLEAR_CHG.ORIG_CHGVAL)
FROM
ECLIPSE.TRAD_CLEAR_CHG
WHERE
(
ECLIPSE.TRAD_CLEAR_CHG.ACC_TYPE = 'C'
AND ECLIPSE.TRAD_CLEAR_CHG.TRADENO = 4531272
)
GROUP BY
ECLIPSE.TRAD_CLEAR_CHG.ACCODE,
ECLIPSE.TRAD_CLEAR_CHG.CCY
That way only 1 record would return from the database.
In your case both queries return the same number of records (you can check in deski when you go to the definition tab in the Data Manager. Menu: data > view data).
The only reason you see only 1, aggregated, result in the report is because the report aggregation type has been set to sum. So in the report BO will automatically aggregate the results for ' ORIG_CHGVAL' depending on the other objects available in the report block.
You have already set for the 'Orig Chgval' the report aggregation to sum in the Designer.
In BO6.5 (which I'm using) that is set on the 'Properties' tab (right click on an object and select Object Format).
On the 'Definition' tab you enter the name of the object, a description and you have to enter the 'Select'. That is where you put what is going into the 'SELECT' part of the sql-statement. For a measure which needs to be aggregated (eg. sum) you put: SUM(ECLIPSE.TRAD_CLEAR_CHG.ORIG_CHGVAL)
When generating the sql-statement BO will then take care of the GROUP BY.
Okay I am using XIr2. I think this is different to 6.5. There is no:
On the 'Definition' tab you enter the name of the object, a description and you have to enter the 'Select'. That is where you put what is going into the 'SELECT' part of the sql-statement. For a measure which needs to be aggregated (eg. sum) you put: SUM(ECLIPSE.TRAD_CLEAR_CHG.ORIG_CHGVAL)
When generating the sql-statement BO will then take care of the GROUP BY.
If I check the Designer Guide for XIR2 and look there how to create an object it is exactly the same as 6.5. I also checked the Designer Guide for XI3.1 and even there it is described in the same way as 6.5.
Let's go through it. When you create an object (dimension, detail or measure) in an universe with the Designer module and you modify the object properties you get a dialog box with different tabs.
The first tab is the definition tab where you:
- enter the name of the object
- select the typ of the object (character, number etc)
- enter a description for the object
- enter the 'Select' part of the object: this is where you put the SUM(database.table.field) which is put in the select part of the sql-statement BO sends to the database
- enter the 'Where' part of the object (optional) which is put in the where part of the sql-statement BO sends to the database
The second tab is properties tab with the qualification where you can select if the object is a dimension, detail or measure object. If you select measure here you have also the posibility to set how the measure will be projected when aggregated. This is where you define how the measure will respond within the report.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.