Can we write select statements in rpd

ForSly

I have a requirement to get a count of flag in my fact table. I can get a  correct count in the database using this code (

SELECT    distinct A_FLAG,

Number FROM  B_FACT

where Number  =12345

)

however when I try in this in the analysis (count(distinct "Fact - A_FLAG" by "Number ") ) and filter by  Number  =12345 I am not getting the correct counts . my question is if there is a way to write the code that I am running in the database in rpd or simply what would be the best approach?

Robert Angel

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??

ForSly

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.

ForSly

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?

Robert Angel

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!

Robert Angel

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?!

ForSly

I have developed my rpd exactly like you described in your response.  I have  dimensions and fact joined   

ForSly

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

Gianni Ceresa

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"

ForSly

unless I am missing something you can aggregate the count of 1  based on say employee or job right?

Robert Angel

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.