Wednesday, August 22, 2012

Why does group by not work in some queries with attributes and metrics?

Question:

Requirement is that I need to create a report with two attributes and 1 metric from two different tables. I created a Logical table by unioning both the tables and took out the three columns from the UNION query. I then created 2 attrubutes and 1 fact (this was eventually converted into a Sum metric). Now when I create the report, the group by for the metric works when I add one attribute and 1 metric, but does not group by when I add the second attribute. Everything works, but the group by is removed and the sum part of the metric itself is removed from the sql (in the SQL View).

Solution:

Check if in both the tables "The key specified is the true key for the warehouse table" option has been checked. IF checked then uncheck it and update the schema and then try.



This setting is applicable only when all the attributes listed as table keys in the 
logical view of the fact table.

Therefore, when checked, this setting forces to not aggregate the data and to pull only 
from the fact tables directly. 




No comments:

Post a Comment