I have a question related to using Filter function in oracle answers.
When trying to insert a Filter (expr) Using (expr) clause in the formula area of a fact table field, It errored out with msg saying about using a wrong measure.
I know this can be done with a case expression but I tried filter clause since this is available in oracle answers.
Please help me figuring out this scenario.
If you want help, you are going to have to be more descriptive. What was the error message?? Don't ask the question as if we already know what you are talking about or are at your desk looking at the screen with the message. Put a screen shot or type the message. Otherwise, you'll just get a lot of "shot in the dark" answers or an answer like, "Well, use the 'correct' measure, then."
Please try again.
What the error message means is that the FILTER function only works on measures ("facts"), not dimensions. So for example you could say, "filter Ledger Balance using year = 2009" but you can't use "filter Region using product = 'electronics' since "Region" is not a fact.
Got it? Hope that clears things up. If not, more details...
David / Raghu - Thanks for u'r replies and apologizes for not posting question with proper material.
Am posting my code and the error message from the screen.
IFNULL(FILTER("Fact - MBS Loan Transactions"."OUTSTANDING PRINCIPAL" USING "Dim - MBS Loan"."LOAN TYPE HPD/HDC/BNK" = 'HDC'),0)
nQSError: 10058] A general error has occurred. [nQSError: 22032] Function FILTER requires at least one measure attribute in its first argument. (HY000)
SQL Issued: SELECT "Dim - MBS Loan"."LOAN AMOUNT", "Dim - MBS Loan"."LOAN TYPE HPD/HDC/BNK", "Dim - MBS Loan"."LOAN TYPE SEN/SUB", "Dim - MBS Project"."PROJECT NAME", "Dim - MBS Project"."PROJECT NUMBER", "Fact - MBS Loan Transactions"."AR BALANCE INTEREST", "Fact - MBS Loan Transactions"."GL BALANCE INTEREST", IFNULL(FILTER("Fact - MBS Loan Transactions"."OUTSTANDING PRINCIPAL" USING "Dim - MBS Loan"."LOAN TYPE HPD/HDC/BNK" = 'HDC'),0) FROM "Financials - MBS"
OK (Ignore Error)
Please continue answering my queries. Since am a newbie your answers won't be just a reply but it's actually learning for me.
Based on the error message, "Fact - MBS Loan Transactions" is not a Fact (or Measure) dimension, probably in Business Model, "Fact - MBS Loan Transactions" is not joined properly, try to check the physical and business model diagram.
Please explain me the basic cause of this error. Am trying to understand some concept also.
As a developer am not having access to the BI Admin. your / other experts explanation will help me understand more.
The basic cause is, most likely, your Fact table is not joined properly to the other dimensions, so instead of creating a Fact table, it is treating the table as a dimension. Try to ask your person in charge for doing the BMM about the correctness of the diagram.