Metric doesnt SUM... 1

[datamart]

Hi,

I am analysing MSTR 7.5. Created a small projetc with 3 attributes and fact and a metric (sum) defined over it.
I used the attrbutes and the metric in a report. the SQL of the report does not have the group by clause. Neither does it Sum the fact column.

Am I missing any configuration or setting???

Thanks.

 

[chael]

This is a common modeling issue. I would check the table and how you defined it in the schema...set the column you are trying to sum as the true key to the warehouse.

Without knowing how your attributes and facts are really designed, and whether you have correctly assigned parent:child relationships in the project I could speculate until the cows come home.

Another place to look: VLDB settings.

Chael

 

[datamart]

I unchecked the option of TRUE KEY in the table object in MSTR. It then started behaving the way I needed it to behave.

BTW, what is TRUE KEY and what does it signify?

 

[entaroadun]

It tells MSTR what level a warehouse table is at.

For example, if you have three attributes on a table: Week, Customer, and Product, and true key is turned on, then MSTR knows that there is one record for every combination of Week, Customer, and Product; i.e. the three attributes form the logical primary key for the table.

MSTR uses this for performance. For instance, if you have a report at the Week, Customer, and Product level, it will execute without doing a group by and without doing any agg functions. Logically,if there is only one record for every combination of Week, Customer, and Product, there's no point in doing any aggregation because there's only one record.

Unfortunately, MSTR turns this performance enhancing feature on by default. If four fields define the primary key for a fact table, but you only model two, then by default, MSTR thinks that those two form the primary key for the table. You may get incorrect results if you aren't aware this is happening.

 

[chael]

OK, my logic was wrong, but my advice was close enough to the mark.

Yet another excellent explanation by entaroadun.

Chael