need to understand pros and cons of measures in Universe vs. Reports

[Former Member]

I modified a WebI report that had two measures selected from the Universe:

- count(COURSES_ID)

- sum(COURSES_CRED_HRS)

As a result, there were two queries created, but the only difference was one had the COUNT and one had the SUM. this seemed to me to be overly complicated and added an extra layer of effort. Everything else in the query was the same.

So I ended up removing the measures from the report, then just did the calculation in the report, and so I ended up with a cleaner report, imo, with these

Count([Course Nbr])

Sum([Cred Hrs])

So was just wondering what are the considerations for each. We don't have much data so the number of rows returned isn't a consideration.

But I am nine months into this and trying to understand the pros and cons, how WebI thinks, etc.

[CdnConnection · Accepted solution]

Bill.

      When you create the Aggregated Measure within the Universe the SQL Query uses the Database aggregation by creating a Group By clause.  It bring MUCH less records before WebI starts creating the report.

When you create the Aggregation at WebI level, you will get ALL the records from the Database NOT Aggregated.

You always want to push the Aggregation to the Database, at the Universe level.

You do have the option at the Universe Query Option to NOT create separate query per measure, see below

Regards,

Ajay

[Former Member · Accepted solution]

Ajay,

ok, that makes perfect sense, thank you.

In my particular case, we reported on details, so there was no benefit to using the Universe measure, and the group by actually had no direct effect, since the details were present. Plus it complicated things with the two queries.

But that helps to understand, just what I was looking for. And it justifies my solution .

For the "Multiple SQL statements for each measure", is there a best practice for that ? What are the considerations there ?

We have minuscule data compared to a large corporation, so that doesn't play as big a factor into our architecture, but definitely good to keep in mind.

[CdnConnection · Accepted solution]

Bill,

     After running the WebI report with & without the Group By go to the Data tab of the and check the record count. You can also check the size of the WebI document by use the WebI rich-client.

Ajay

[Former Member · Accepted solution]

Ok, that helps too. I think you are on 4.x, we're still XI 3.1. But I verified the counts both ways in Oracle.

What about best practices for "Multiple SQL statements for each measure".

thx.

[CdnConnection · Accepted solution]

Bill,

   It all depends on you own Database and Universe combination needs to be tested to see what works best for your site.   The default is MULTI.

Regards,

Ajay

[Former Member · Accepted solution]

Ok, thanks again.

[andreasja_schneider · Accepted solution]

Also do UNCHECK the Option "Multiple SQL Statements for each measure" in IDT, assuming your universe designer has checked and resolved any fan/chasm traps in the universe.

In your scenario this will lead to just ONE, effective SQL code being generated in Webi along the lines of:

SELECT SomeDimensions,

   SUM (SomeMeasure), Count (SomeDimension)

FROM FactTable_1, DimensionTables

...
GROUP BY SomeDimensions

[Former Member · Accepted solution]

Andreas,

excellent, thank you. Definitively the one statement makes more sense in this particular situation, but I realize there are pro's and con's to the Multiple Path option.