1 2 3 Previous Next 32 Replies Latest reply on Jan 24, 2019 6:56 PM by ForSly

    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?

        • 1. Re: Can we write select statements in rpd
          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??

          • 2. Re: Can we write select statements in rpd
            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.

            • 3. Re: Can we write select statements in rpd
              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?

              • 4. Re: Can we write select statements in rpd
                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!

                • 5. Re: Can we write select statements in rpd
                  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?!

                  • 6. Re: Can we write select statements in rpd
                    ForSly

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

                    • 7. Re: Can we write select statements in rpd
                      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

                      • 8. Re: Can we write select statements in rpd
                        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"

                        • 9. Re: Can we write select statements in rpd
                          ForSly

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

                          • 10. Re: Can we write select statements in rpd
                            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.

                            • 12. Re: Can we write select statements in rpd
                              ForSly

                              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.

                              • 13. Re: Can we write select statements in rpd
                                Robert Angel

                                Sincere thanks for providing company / moral support on this one...

                                • 14. Re: Can we write select statements in rpd
                                  Robert Angel

                                  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?

                                  1 2 3 Previous Next