You thought WHERE clauses in Universe Measure Objects were a bad idea? Not in BI4's IDT they're not - simple Year on Year Analysis example
In XI3.1 and previous versions, using the WHERE clause in the SQL Definition of an object was a bad idea. These created WHERE clauses in the SQL at runtime - fine if you are only using one type of restriction and were happy to limit the data set, but if you had more than one object the WHERE clauses were combined with an AND. In many cases these just contradicted each other and you got no results (e.g. SELECT sales FROM blah WHERE year=2006 and year=2005) and generally confused your users.
For BI4 in the Information Design Tool (not in the Universe Design Tool) the behaviour of these WHERE clauses has changed to something more useful - the query is split and an effective union is created (actually it's a full outer join - see Observations section).
To demonstrate I will create a basic report that shows sales revenue for a prompted year and compare it to the previous year in a single query, with no extra logic required in the report.
Year on Year Analysis Example Using eFashion
Information Design Tool
- Convert eFashion.unv to a .unx first.
- Copy the existing Sales revenue measure and call it Sales Selected Year.
- Add a WHERE clause in this new measure: @Select(Time period\Year) = @Prompt('Select Year','A','Time period\Year',,):
- Create a copy of this new measure and call it Sales Previous Year.
- Amend the WHERE class slightly to take 1 from the year: @Select(Time period\Year) = @Prompt('Select Year','A','Time period\Year',,)-1:
- Now publish the Universe.
- You can test in the Query section of IDT but what works here does not always work in Web Intelligence.
- Create a single data provider report with product and the two new measures:
- Look at the SQL and notice that Web Intelligence has created two queries with a UNION. The SELECT and GROUP BY clauses are the same but the WHERE clause is different as defined in our Universe measure objects:
- Run the report and select 2006 as the year. In the example below I have added separate data providers for 2006 and 2005 data to verify the results:
- The UNION is not actually a UNION - it's a FULL OUTER JOIN (I don't know why it's not a UNION ALL). For databases that support this feature, the join will be pushed down to the database if the JOIN_BY_SQL parameter is Yes in the Business Layer (by default it is not present, i.e. No) - example below is eFashion in SQL Server:
- If the database doesn't support full outer joins (like Access!!) the join will be performed in the Web Intelligence engine - for large amounts of data this will be a performance disadvantage but no more so than creating two single queries.
- If the join is done in the report, the queries are run sequentially and not in parallel - this is the same as having two data providers.
- All objects with exactly the same WHERE clause will be placed in the same statement - a separate SQL statement is created when the text of the WHERE clause is different – even a single space difference will create a new statement.
- All other objects in the SELECT, WHERE, GROUP BY and HAVING are replicated across the SQL statements so you can add extra WHERE clauses in the data provider if you wish.
- For objects with the same object WHERE clause placed in the same SELECT, the same WHERE clause in the SQL is repeated (once for each restricted object). This is untidy but I don't think it affects performance in any way as the database optimiser should ignore the repetition.
This is a simple example but you could get more complicated than this.
Also, this isn't actually documented anywhere which is a shame as this is a pretty powerful feature.