I have a requirement where in the BMM(The LTS for this logical table is say 'Fact'), I need a logical column with the following definition.
count(Dim.status) where Dim.status = ' Accepted' (I didnt add the Dim table to the LTS yet, should I?)
I know that we can do a count(Dim.status) in the column source . but for the where Dim.status = ' Accepted' part im not sure what to do.
Should I bring the Dim table as a LTS and define the 'where' condition in the content tab? will that work?
but what if I have another logical column where I need dim.Status = 'Declined'( we have many more status)
Create in this way add As LTS and create a new column in that column u can define the count,Now create a another colum and implement the conditon what u need
CASE WHEN Dim.status = ' Accepted THEN NEWCOL(COUNT(DIM.STATUS) ELSE END;
Try this and let me know the updates.
That exactly is my question. The where condition should be applicable only for that logical column. In a different logical column it might be a different where condition, but the table for both these logical columns would be same.
Also should this table be present as a LTS( the default LTS for this is a different fact)?
In the filter condition ( using) can we use a column whose table is not a LTS
Ex: Filter(count(Fact.Dollars) Using("dim.status = 'accepted'))
In the above example is the default LTS is the fact table, to use the above expression('Using' part)should I have the ' dim' table listed as a LTS too?
The suggest statement as Filter(count(Fact.Dollars) Using("dim.status = 'accepted'))
is based on Answers, this would care about the column is available in Subject Area or not thats it.
The same kind of functionality can get the using rpd.
There you might need to map/add (fact source properties and then map using pencil icon on 11g add button in 10g) the dim table to the fact
so that you can get the expression based on physical columns as
case when dim.status = 'accepted' then Fact.Dollars else 0 end
use Aggregate tab for sum;
Since you are using Fact.Dollars it suppose to be sum, ROW_WIDs go by counts
Hope this helps :) for more Qs send email
If helps mark