1 2 3 4 Previous Next 54 Replies Latest reply: Jul 16, 2013 4:46 PM by Nasar-Oracle RSS

    how to do a count distinct value in awm

    579054
      Hi!!!

      I create a cube with 3 dimensions:
      dimension1:I use the snowflake assignation type, cause I have the information of the dimension on two tables.
           level1:all
           level2:older or younger than 25
           level3:person
      level1,2 tables
      ID_MAS_DE_25     DES_MAS_25_CAS          TOT_MAS_DE_25     DES_TOT_MAS_DE_25

      1          <= 25               TOT_MAS_DE_50     TODOS(all)
      2          > 25               TOT_MAS_DE_50     TODOS(all)
      3          = 25               TOT_MAS_DE_50     TODOS(all)

      level3 table
      ID_PERSONA     DES_PER_CAS     ID_MAS_MENOS(foreign key)
      1          JANE          1
      2          JHON          3
      3          SELLY          3
      4          ROBERT          2
      5          ALBERT          1
      6          ADAM          3
      7          EVA          3

      dimension2:star schema
           level1:all
           level2:city

      ID_MUNICIPIO     DES_MUNIC_CAS          TOT_MUNIC     DES_TOT_MUNIC

      1          BILBAO               TOT_MUNIC     TODOS
      2          LONDON               TOT_MUNIC     TODOS
      3          PARIS               TOT_MUNIC     TODOS

      dimension3:snowflake
           level1:all
           level2:car type
           level3:car

      ID_TIPO_COCHE     DES_TC_CAS          TOT_TCOCHES     DES_TOT_TCOCHES

      1          FORD               TOT_TIPOS_COCHES     TODOS
      2          VOLSWAGEN          TOT_TIPOS_COCHES     TODOS
      3          MERCEDES          TOT_TIPOS_COCHES     TODOS
      4          RENAULT               TOT_TIPOS_COCHES     TODOS
      5          OPEL               TOT_TIPOS_COCHES     TODOS

      ID_REGISTRATION_NUM     DES_COCHES_CAS     ID_TIPO_COCHE

      12454XF               12454XF          1
      14585HY               14585HY          2
      48796JI               48796JI          1
      1489OP               1489OP          5
      9874LO               9874LO          5
      5975IO               5975IO          4
      3659SD               3659SD          1
      5697UY               5697UY          4
      8963AS               8963AS          4
      5687SA               5687SA          3


      the cube takes the values through this fackt table


      ID_PERSONA ID_MUNICI ID_REGISTRATION CANT
      ---------- --------- --------------- ----------
      1 1 12454XF 1
      1 1 14585HY 1
      2 1 48796JI 1
      3 3 1489OP 1
      4 3 9874LO 1
      5 2 5975IO 1
      5 2 3659SD 1
      6 2 5697UY 1
      7 3 8963AS 1
      7 3 5687SA 1


      with this cube, I have to show these values:
      How many cars are in Bilbao?
      3-->"12454xf","14585hy","48796ji"     
      how many people that is 25 years old have a car?
      4-->Eva,Adam,Selly,Jhon
      how many people has a car?
      7
      Do anyone know how can I show all this different values in just one measure?if yes, how?
      I have been thinking about how can I do a count(distinct(value)) in awm. Do you know where is that option?
      also, in this cube, to show all this different values, we will have to apply different aggregation types for
      each level, and I dont know if this could be possible.

      thanks

      *for better understanding, here are the real values of the fack table


      registration     Name     Car     City     Ages     
      number     

      12454XF     Jane     Ford          Bilbao     20     
      14585HY     Jane     Volswagen     Bilbao     20     
      48796JI     Jhon     Ford          Bilbao     25     
      1489OP     Selly     Opel          Paris     25     
      9874LO     Robert     Opel          Paris     30     
      5975IO     Albert     Renault          London     20     
      3659SD     Albert     Ford          London     20     
      5697UY     Adam     Renault          London     25     
      8963AS     Eva     Renault          Paris     25     
      5687SA     Eva     Mercedes     Paris     25     



      best regards,
        • 1. Re: how to do a count distinct value in awm
          Klaker-Oracle
          To design a count distinct in AWM you need to have access to the leaf node data and the fact value should be 1 for each record. Then you would typically use the MAX aggregation method on all dimensions except the dimension that you want to de-duplicate over.

          The MAX allows you de-duplicate the records over those dimensions allowing you to count each instance of a record only once. Then over the time dimension it is possible to use MAX. Or you can use SUM on any of the other.

          I think in your model you should be able to combine Dimensions 1 and 2 together and make another dimension called Age Group that contains your age grouping. So in this case I think you will need to MAX on dimension 3 and Age Group and SUM the new dimension that is the combination of your Dims 1 & 2.


          If you send me an email (keith.laker@oracle.com) I can send you a document that explains how to manage COUNT DISTINCT measures within OLAP.

          Hope this helps


          Keith Laker
          Oracle EMEA Consulting

          BI Blog: http://oraclebi.blogspot.com/
          DM Blog: http://oracledmt.blogspot.com/
          BI on Oracle: http://www.oracle.com/bi/
          BI on OTN: http://www.oracle.com/technology/products/bi/
          BI Samples: http://www.oracle.com/technology/products/bi/samples/


          Does that help?
          • 2. Re: how to do a count distinct value in awm
            579054
            Hi keith,
            My fackt table have the leaf node data, the fackt tables values are the last levels values, and the values are 1 for each record. But if I
            use the Max aggregation method on one dimension then it dosent calculate what I want.I cant understand what do you mean when you say de-duplicate, and
            I dont have a time dimension, so I dont know what you mean when you say that is possible to use max in time dimensions.
            If I combine dimension 1 and 2:dimension age_group will be like this???level1:all,level2:city,level3:older or jounger 25,level4:person???so in this case,
            I will have to do max in this dimension and sum dimension of the cars? Please, can you explain again how could I do the combine dimension?
            I sent an email to keith.laker@oracle.com asking for more information about how to manage COUNT DISTINCT, but I dont know if it is possible to do a diferent
            count distinct for each level in a dimension.
            waiting for your answer,
            many thanks for all.
            • 3. Re: how to do a count distinct value in awm
              579054
              Hi keith,
              My fackt table have the leaf node data, the fackt tables values are the last levels values, and the values are 1 for each record. But if I
              use the Max aggregation method on one dimension then it dosent calculate what I want.I cant understand what do you mean when you say de-duplicate, and
              I dont have a time dimension, so I dont know what you mean when you say that is possible to use max in time dimensions.
              If I combine dimension 1 and 2:dimension age_group will be like this???level1:all,level2:city,level3:older or jounger 25,level4:person???so in this case,
              I will have to do max in this dimension and sum dimension of the cars? Please, can you explain again how could I do the combine dimension?
              I sent an email to keith.laker@oracle.com asking for more information about how to manage COUNT DISTINCT, but I dont know if it is possible to do a diferent
              count distinct for each level in a dimension.
              waiting for your answer,
              many thanks for all.
              • 4. Re: how to do a count distinct value in awm
                579054
                Hi keith!
                Thanks for your document, it has been very interesting. I have been trying to apply it in my example but I have problems to compile the program. This is the code of my program after creating a view like in the example:

                vrb cd integer
                vrb chnl text
                vrb reg text
                vrb mun text
                vrb mas text

                define local_cd_meas variable integer <MAYOR_MENOR,MUNICIPIOS,COCHES>

                sql declare cur_user Cursor for select CDVAL,MAYOR_MENOR,MUNICIPIOS,COCHES-
                from VISTA_KEITHENA

                SQL OPEN cur_user

                sql fetch cur_user into :cd, :mas, :mun,:reg
                limit MAYOR_MENOR to mas
                limit MUNICIPIOS to mun
                limit COCHES to reg
                local_cd_meas=cd

                while sqlcode eq 0
                do
                sql fetch cur_user into :cd, :mas, :mun,:reg
                limit MAYOR_MENOR to mas
                limit MUNICIPIOS to mun
                limit COCHES to reg
                CDVAL=cd

                doend

                consider C_KK
                eq local_cd_meas
                delete local_cd_meas
                commit
                update
                sql close cur_user
                sql cleanup

                and when I compile I have this problem:the variable doesnt exist. Do I have to create the variable in variables first?whitch type of variable is that?

                ERROR: (ORA-34492) El objeto de espacio de trabajo analítico LOCAL_CD_MEAS no existe.
                In KK!KK PROGRAM:
                local_cd_meas=cd
                ^
                ERROR: (ORA-34492) El objeto de espacio de trabajo analítico LOCAL_CD_MEAS no existe.
                In KK!KK PROGRAM:
                local_cd_meas=cd
                ^
                many thanks in advance!
                • 5. Re: how to do a count distinct value in awm
                  579054
                  Hi Keith!thanks again for your usefull explanations. I have solucionated my examples problem, I have done maximun on each dimension except the dimension I wanted to count(with 2 leveles). But now, I realised that when I trie to do these with a dimension that has more than one level it doesnt work. For example, I have created a cube with 2 dimensions
                  dim_1:niv1,niv2,niv3 and dim_2:niv1,niv2 and niv3. If I put dim1 max, and dim2 sum,cause, y dont have the option of total, it does countdistinct of the first level, but then it calculates wrong, cause it sums the second level.
                  otherwise, is possible to create a cube with just one dimension(with 3 leveles) to calculate a count(distinct) in a measure, andif yes, how???.
                  Thanks in advance.
                  • 6. Re: how to do a count distinct value in awm
                    Klaker-Oracle
                    The number of levels within the dimension is irrelevant. It should work as you are applying both MAX and SUM aggregation operators on the dimension not individual levels.

                    Did you define the correct order in the aggregation panel?

                    Keith
                    • 7. Re: how to do a count distinct value in awm
                      579054
                      Hi Keith!
                      Thanks again for your answer, but I dont have a solution yet.My example is like this:THIS IS THE FACKT TABLE:
                      ID_EXP_SOL---------------ID_MUNICI----CANT_SOLICS_EJER
                      ---------------------------------------------------------------------------------------
                      EB1-0000/93-BB-000.001---059.01-------1
                      EB1-0001/89-BB-000.001---059.01-------1
                      EB1-0001/89-BB-000.002---059.01-------1
                      EB1-0001/89-BB-000.003---059.01-------1
                      EB1-0001/89-BB-000.004---059.01-------1
                      EB1-0001/89-BB-000.005---059.01-------1
                      EB1-0001/89-BB-000.006---059.01-------1
                      EB1-0001/89-BB-000.007---059.01-------1
                      EB1-0001/89-BB-000.008---059.01-------1
                      EB1-0001/89-BB-000.009---059.01-------1
                      EB1-0001/89-BB-000.010---059.01-------1
                      EB1-0001/89-BB-000.014---059.01-------1
                      EB1-0001/89-BB-000.015---059.01-------1
                      EB1-0001/89-BB-000.016---059.01-------1
                      EB1-0001/89-BB-000.017---059.01-------1
                      EB1-0001/89-BB-000.018---059.01-------1
                      EB1-0001/89-BB-000.019---059.01-------1
                      EB1-0004/81-LE-009.009---000.00-------1
                      EB2-0130/07-BB-000.001---078.48-------1
                      EB2-0130/07-BB-000.002---078.48-------1
                      EB2-0130/07-BB-000.003---078.48-------1
                      EB2-0130/07-BB-000.004---078.48-------1
                      EB2-0130/07-BB-000.005---078.48-------1
                      EB2-0130/07-BB-000.999---078.48-------1

                      *All the values are the leaf value for each dimension:
                      dimension1:all, Expedientes(proccess),Solicitudes(requests)
                      dimension2:all,states,citys(id_munici)
                      I want to count distinct Expedientes. For that I create these two dimensions and when I create the cube I put first dimension2(of the cities) like max, and then dimension1 like 'sum'. I create a measure with these properties and the result is:
                      24 for all,all. it counts distinct requests.
                      Otherwise if I put first the dimension1 as max and dimension2 as sum, it counts distinct cities.What do you think?
                      Thanks for all your help
                      • 8. Re: how to do a count distinct value in awm
                        579054
                        sorry, I havent finished writing...
                        the result I want is 4 expedientes for all,all.
                        thanks again
                        • 9. Re: how to do a count distinct value in awm
                          Klaker-Oracle
                          To get what you want you need to have three dimensions:

                          Processes SUM
                          Requests MAX
                          Geography MAX

                          Since requests are not really related to processes in this context. You need to drop processes out of the model since this is what you are trying to count.

                          Alternatively, if you want to keep this as a two dimensional model then you will need to define a custom measure within your cube. Load data into your base measure but do not solve any of the levels above the leaf level. Then create a DML program that tests the level within the cube and returns the correct value.


                          Keith Laker
                          Oracle EMEA Consulting

                          BI Blog: http://oraclebi.blogspot.com/
                          DM Blog: http://oracledmt.blogspot.com/
                          BI on Oracle: http://www.oracle.com/bi/
                          BI on OTN: http://www.oracle.com/technology/products/bi/
                          BI Samples: http://www.oracle.com/technology/products/bi/samples/
                          • 10. Re: how to do a count distinct value in awm
                            579054
                            Hi Keith! Thanks again for answering. You are rigth when you said I need 3 dimensions for that, and these could be a solution, but I would like to keep this as a two dimensional model, and give each level the count distinct value, but I dont know how I have to create the custom measure for that, and assign it to each level,not to each dimension.
                            thanks in advance
                            • 11. Re: how to do a count distinct value in awm
                              579054
                              Hi again...
                              As you can see, I have been trying to solucionate my problem with two dimensions and by dml and I couldnt...
                              Now, although i didnt like this metod, I tried to do with 3 dimensions(each level I wanted to count like a dimension) for that, but I can visualice just the top level cause then it gives this error:
                              BIB-9009 Oracle OLAP no ha podido crear el cursor.oracle.express.idl.util.OlapiException: java.sql.SQLException: ORA-03114: no conectado a ORACLE

                              do you know how I can solutionate it? I have to give more space to the cursor but how, and witch one???
                              thanks in advance.

                              Also if you can explain better how I could create a custom measure for each level I would appreciate a lot.

                              best regards,
                              • 12. Re: how to do a count distinct value in awm
                                Klaker-Oracle
                                Increase the setting of open_cursors in your init.ora file and restart your database.

                                Keith
                                • 13. Re: how to do a count distinct value in awm
                                  579054
                                  Hi Keith!
                                  I increase from 300 to 500 first and now to 1000 but I still have this error.
                                  do you know anything else I can do???
                                  thanks.
                                  • 14. Re: how to do a count distinct value in awm
                                    Jim Carey-Oracle
                                    The 3114 -- no connection to oracle message means that your session has gone away (crashed? killed? etc). You should look in the event log for additional information. You should also have a trace file.

                                    Jim
                                    1 2 3 4 Previous Next