Which method is best for implementing the desired aggregation?

[cgwaters]

[My apologies for the length of this message. I thought it easier to explain by example.]

I'm designing a WebI report that aggregates (i.e., sums) "quantity sold" values for each company in my database. I'm encountering confusing results and am hoping someone can explain it.

For illustrative purposes, I've reduced my universe to one table containing two columns: Company Name and Quantity Sold. The contents of the table are as follows:

"Company A", 1
"Company A", 1
"Company B", 2
"Company B", 2
"Company C", 3
"Company C", 3
"Company D", 4
"Company D", 4

In the following WebI tables, the Avoid duplicate row aggregation setting is not selected, by default.

With Quantity Sold configured in my universe as a measure having an aggregation function of None, WebI produces a table with the following (undesired) result:

Company     Quantity Sold
----------- -------------
Company A   #MULTIVALUE
Company B   #MULTIVALUE
Company C   #MULTIVALUE
Company D   #MULTIVALUE

Enabling the Avoid duplicate row aggregation setting produces a table with the following (again, undesired) result:

Company     Quantity Sold
----------- -------------
Company A               1
Company A               1
Company B               2
Company B               2
Company C               3
Company C               3
Company D               4
Company D               4

Changing the Quantity Sold report column's formula from "=[Quantity Sold]" to "=SUM([Quantity Sold])", regardless of the Avoid duplicate row aggregation setting, produces the same (undesired) results. Why doesn't using SUM in the report's column formula produce the desired result?

With Quantity Sold configured in my universe as a measure having a Sum aggregation function, WebI produces a table with the following result:

Company     Quantity Sold
----------- -------------
Company A               2
Company B               4
Company C               6
Company D               8

This is the desired result! Changing the Quantity Sold column's formula from "=[Quantity Sold]" to "=SUM([Quantity Sold])", while unnecessary, produces the same result, regardless of the Avoid duplicate row aggregation setting.

Why does using a SUM aggregation function in the universe produce the desired result? And since summing the Quantity Sold values limits the grain of what can be reported (i.e., the individual Quantity Sold values are no longer reportable), isn't this an undesireable approach?

Finally, with Quantity Sold defined in my universe as a dimension (as some threads suggest doing so as to overcome #MULTIVALUE results), WebI produces a table with the following (undesired) result:

Company     Quantity Sold
----------- -------------
Company A               1
Company B               2
Company C               3
Company D               4

Note that the values are aggregated; i.e., only one instance of each Company-Quantity Sold combination is listed. Enabling the Avoid duplicate row aggregation setting produces a table with the following (again, undesired) result:

Company     Quantity Sold
----------- -------------
Company A               1
Company A               1
Company B               2
Company B               2
Company C               3
Company C               3
Company D               4
Company D               4

However, disabling the Avoid duplicate row aggregation setting and changing the Quantity Sold column's formula from "=[Quantity Sold]" to "=SUM([Quantity Sold])", produces a table with the following result:

Company     Quantity Sold

----------- -------------

Company A               2

Company B               4

Company C               6

Company D               8

This is the desired result! Why does configuring the Quantity Sold column as a dimension (and using the SUM formula) produce the desired result?

I always figured it best to configure such numeric columns as measures rather than as dimensions -- but having to configure the Quantity Sold measure with a SUM aggregation function in the universe...as well as the various forum comments about configuring such columns as dimensions to overcome #MULTIVALUE results...makes me wonder. Can anyone comment?

[Thank you for taking the time to read this message!]

[former_member197846]

Avoid Duplicate Rows Aggregation functionality in WEBI Tables uses Projection Aggregation set in Universe.

When you pull the Company Name and Quantity Sold objects in query, WEBI returns the following resultset

Company     Quantity Sold
----------- -------------
Company A               1
Company A               1
Company B               2
Company B               2
Company C               3
Company C               3
Company D               4
Company D               4

Case 1:

Avoid Duplicate Row Aggregation disabled and None Projection Aggregation function in Universe

- WEBI can not determine the Aggregate function to be used for Quantity Sold. Hence throws #MULTIVALUE error.

Case 2:

Avoid Duplicate Row Aggregation disabled and SUM Projection Aggregation Function in Universe

- WEBI understands that it needs to do SUM(Quantity Sold) for same Company Name in Table

Case 3: Avoid Duplicate Row Aggregation is enabled for a Table

- WEBI is informed to ignore all Aggregation functions hence it doesn't change the result with SUM(Quantity Sold) formula at Report Level.

- Even when Company Name is removed from Table SUM(Quantity Sold) formula does not aggregate Quantity Sold.

I am not sure about defining Measure as a Dimension to get rid of #MULTIVALUE error.

Hope this helps to clear your confusion.

[Former Member]

Hi Chris,

I think this is all getting overly complicated. Simply define anything you want to aggregate (count/sum) in the universe as a measure and make it a count or sum or other.

Don't define numbers you want to aggregate as dimensions or else you may see odd results (like #multivalue). Make sure you don't have anything in the rows that could be throwing off the aggregate calculations Webi is trying to do.

Thanks

[cgwaters]

Thank you, Rakesh, Abhijit, and Bill. It has been very frustrating trying to troubleshoot something that should be so simple. [Part of the challenge has been that my universe updates aren't always seen in WebI; to see the updates, I have to close the report or logout or (sometimes) even close all instances of my browser; in addition, the Rich Internet Application version of WebI, besides being very slow to load, frequently generates fatal errors that require me to start over.]

I didn't provide the details in my initial post, but in my report I have multiple tables that are hierarchical in nature; i.e., selecting a record in the first table needs to filter the contents of the second table, etc. In the first table, I need the measure to be aggregated; in the second table, I need the measure to not be aggregated. What's the best way to accomplish this?

Are there any issues with adding a column twice in a universe -- one instance as a measure having an aggregation (sum); another instance as a measure having a non-aggregation (none)?

Initially, I tried to use one query for both (and additional) tables; however, including both the aggregated measure and the non-aggregated measure seemed to skew the results. My current plan is to use a separate query for each table (with the aggregated measure in the first query and the non-aggregated measure in the second query) and to use linked elements and merged dimensions to keep the contents of the tables in sync. [I realize this information goes beyond the subject of the thread but I wanted to provide enough details to make it clear what I'm trying to accomplish.]

Thanks!

[Former Member]

Are there any issues with adding a column twice in a 
universe -- one instance as a measure having an aggregation (sum); 
another instance as a measure having a non-aggregation (none)?

Once you define that way, It will be confusing for users. They have to remember when to which ?  Its like you are putting some limitations on using universe objects.  That is not the best practice.

If you have 4 or 5 such objects it will be more confusing isn't it ? 

Regards,

Irfan Mohammed

[cgwaters]

I see what you mean; however, I was just following up on Bill's comment:

"Simply define anything you want to aggregate (count/sum) in the universe as a measure and make it a count or sum or other."

For my report, I need to be able to report the column's individual values as well as the aggregated values -- hence my question about defining the column twice in the universe. I'm not sure how else to accomplish this.

[Former Member]

I don't see any problem in defining it in both ways, depending on your needs. As Irfan mentions, it could be a little confusing, but if you name them clearly it should not be. For instance name one as [Quantity Sold (Aggregate)] and another as [Quantity Sold (Not Aggregate)] or something like that.

However, I don't think you should have to do that. You should be able to control whether it aggregates or not in the report. And if not, you could make a variable using the object and redefine it as a dimension so it does not automatically aggregate. I think you have several options.

Thanks

[cgwaters]

Thanks, Bill. When I initially included the QuantitySold column twice in my universe -- one as a measure having an aggregation (sum); another as a measure having a non-aggregation (none) -- and, especially when I had both columns included in the same query; i.e.,

SELECT
  Table__2."CompanyName",
  sum(Table__2."QuantitySold"),
  Table__2."QuantitySold",
  etc.

WebI would occasionally and inexplicably convolute the SQL into a UNION; e.g. (relying on memory here),

SELECT

  0,

  Table__2."CompanyName",

  sum(Table__2."QuantitySold"),

  0,

  etc.

UNION

SELECT

  1,

  Table__2."CompanyName",

  0,

  Table__2."QuantitySold",

  etc.

GROUP BY 0, 1, 3, etc.

In other words, WebI insisted on putting each QuantitySold column into a separate query. This led me to wonder if I was trying to force WebI to do something it wasn't happy about doing. WebI hasn't done this lately, however, so perhaps it was a gremlin.

On the other hand, based on the encouraging comments in this thread, I have since started over with a new WebI report and the aggregation seems to be working -- so perhaps something in the first report simply became corrupt.

I need to spend time reviewing how variables can be used in WebI. But why would creating a variable based on the object and then redefining it as a dimension result in no aggregation?

[former_member189638]

It is always best to have the aggregation at the Universe instead of the Report level.

When you have a summation in the Universe; the data is first fetched, aggregated and then passed to the WebI report.

When you have a summation in the Report; the data is first fetched, passed to the WebI report and then aggregated.

This difference plays an important role when you have a row limit set in the Universe. Else I don't think it would make much difference.

Consider the following table in the database which has 5 records.

Prod     Sales

P1        10

P1        20

P2        20

P2        30 

P3        10

Now if you have set a Row limit as 3 in the Universe, it means Universe will pass only 3 rows to the report.

If the Summation is set in the Universe; then it will fetch all the data from the table, aggregate it and pass the 3 rows in the WebI report. So your output will be as expected.

Prod     Sales

P1        30

P2        50

P3        10

If the Summation is set in the Report; then it will fetch all the data from the table, pass only first 3 rows to the WebI report and do the aggregation. In this case the report output is incorrect

Prod     Sales

P1        30

P2        20