Using universe objects I have built identical queries within Desktop Intelligence and QAAWS (used within Xcelsius) which return different results. Within Desktop Intelligence the query is grouped by the dimensions selected (with the measures aggregated appropriately) whereas within Xcelsius it is not.
I would like to ensure QAAWS queries are grouped in the same way as using DI. Does anyone know if this is a general problem? Are there any general settings within qaaws to auto group by to force the results of the query to be the same as Desktop Intelligence?
The sql generated in Desktop Intelligence is (note no group by statement):
dbo.DW_DIM_CLIENT INNER JOIN dbo.DW_FACT_1 ON (dbo.DW_DIM_CLIENT.DIM_CLIENT_ID=dbo.DW_FACT_1.DIM_CLIENT_ID)
INNER JOIN dbo.DW_DIM_1 ON (dbo.DW_DIM_1.DIM_1_ID=dbo.DW_FACT_1.DIM_1_ID)
Note: DW_DIM_CLIENT is just used to enforce client dimension restrictions on query results.
The results are grouped by MEASUREABLE_ORDER and MEASUREABLE (dimension objects) in Desktop Intelligence. However when an identical query is created in QAAWS and consumed within Xcelsius the results are not grouped by the dimension objects.
MEASUREABLE_ORDER and MEASUREABLE are set as Dimension objects in my universe and STRONGLY_AGREE, AGREE, DISAGREE and STRONGLY_DISAGREE are set as Measures set to aggregate by sum.
Any help in modifying my universe to force group by across all or, forcing QAAWS to group by on its queries would be greatly appreciated.
Business Objects XI 3.1
Xcelsius 2008 FP 2.
Windows Server 2003