You can do count (or sum) by X in a formula in OBIEE to achieve the same result; -
count(distinct a_flag by number)
But, and I hesitate to ask as it just looks so wrong, what is number in B_FACT, is this a foreign key??
And why, is a_flag in your fact table - it should just be measures and keys?
In short, what is this BI abomination I see before me??
a Flag is a measure and I am breaking it down by employee number. But for some reason the same query is giving me the desired results in the database but same cant be said in analysis.
I understand you can copy the logical sql and run it in the underlying database but when I try it I get an error. could that explain my problem?
Not sure if you are talking to yourself or me now, but here goes....
Provided you have the rights there is a feature called 'Direct Database Request' - this is a TESTING feature which is often misused to create emergency pieces of sql - my advice - don't go there - that is not the point of BI.
When you run direct database requests you use the real physical table names (and schema prefix) as you would see if you were looking at them through (say) Sqldeveloper or Toad.
However, to get the most out of OBIEE I recommend developing and designing to the recommended standards. If you do this you will have a performant and flexible data warehouse which will serve a multitude of purposes and on which OBIEE will add significant value.
To do this; -
Logical FACTS have measures and keys to link them to Logical Dimensions - and nothing else
Logical Dimensions have attributes and keys
So your flag should be in a dimension table
Your count # should be in the fact
You could also validly have count distinct of flag in your fact table
The two join by the easiest means imaginable.
You then filter by the number
The answer comes out correctly
Note well: try an aggregation on a dimension and it will frequently be wrong as OBIEE always makes the dimensions attributes distinct BEFORE applying any other aggregations
Note well: keep non-measures in logical Fact tables and OBIEE will often error - it is just wrong!
Do you mean your flag is 0 or -1 for true or false? - as often seen in MSSqlserver?
If so, this does not make it a measure, it is still an attribute, a measure is something you can meaningfully aggregate, you cannot meaningfully aggregate the equivalent of true,false,true,true?!
I have developed my rpd exactly like you described in your response. I have dimensions and fact joined
Yes my flag is a 0 or a one its based on a sql that determines if someone applied for same job group in the last six months, a 1 means they didn't apply and 0 means they did. So I am trying to get a count of all the employees that applied to same job group
Remembering some of your past threads, you always end up having to write your own measures formulas in the front-end. It's like if your RPD is just wrong or modelled for something else than what you look for. All the previous questions and this one is standard normal feature of the model in the RPD. So I struggle to understand how your RPD can be so useless and not used for the analysis requirements you seem to have.
Is your RPD really modelled for the business needs you have? Or is it modelled based on something else for some other reason?
PS: you keep saying you do counting on queries with DISTINCT and not a single aggregation, I still don't see what counting you do on something like "SELECT distinct A_FLAG, Number FROM B_FACT"
unless I am missing something you can aggregate the count of 1 based on say employee or job right?
OBIEE is not query-centric, it is data-centric.
You need to build your repository correctly to support this.
Your fact should have all of the measures, and keys to Job Group Dimension, Job Dimension (with your job number in?) etc
Your flag will be in one of the dimension tables
You then create an analysis pulling the appropriate count distinct measure from the fact and dimension tables
Not the first or last time I will recommend this, but try to work through the OBIEE for example materials on Oracle's sites and ideally get yourself some decent training, OBIEE is not just a sql tool and if you try to run with it as if you can just write some sql then you will come badly unstuck.
Yes, my rpd is modeled as per business requirements. and my query is on the database is "SELECT count distinct A_FLAG, Number FROM B_FACT" I am using distinct to eliminate extra rows / duplicates. in the rpd I just set aggregation rule for A_FLAG as count distinct but I don't have an option or a way to group it by what I would want in order to get a correct result.
Sincere thanks for providing company / moral support on this one...
Can you tell me where the 3 elements site i.e.
Flag (1 / 0) = fact
Employee = dimension
Job = dimension
Correct me if I am incorrect please as to the source of the three?