OBIEE 12c BMM layer modeling

Question

Hi Gurus,

Here is the requirement in OBIEE 12c.

I have a fact table which contains sales amounts ($) for all months from the data warehouse.  Also i have a dimension which is month table which contains all months.

Now in answers, if i pull "$ sales" column from the fact table, then i should see sales amount of only current month.

If i pull "month" column from 'Month' dimension table along with the fact table column, then i should see sales amount for all months.

Please give any inputs how this could be achieved.

Thanks in advance.

[Deleted User] · Answer

-

- "12c" isn't a precise indication as there were about 40 releases of "12c" so far

- Your question needs much more clarity because just showing "$ sales" and it being "the current month" and then adding the "Month" column to show all months simply makes no sense at all. Neither in general analytical terms nor in how analytical tools are built and used. You may want to take 3 steps back and think about what you are trying to achieve first and then think about a "how".

Gianni Ceresa · Answer

2711084 wrote:if i pull "$ sales" column from the fact table, then i should see sales amount of only current month.

This is something you generally want to do with filtering or you are going to have many side effects you don't really want.

"current month" doesn't exist, it has no meaning, OBIEE doesn't know what your current month is. This is a business rule which a filter can cover easily, by having a variable representing the current month for example.

You could in theory change the aggregation from SUM to LAST, but it will not return the current month, it will return the last one. And no need to say it will make values wrong at any other level than your month.

The side effects and issues of changing the aggregation are many more than just do things as they are supposed to be: a filter telling the analysis which month figures you want to see.