8 Replies Latest reply on Nov 6, 2013 2:37 PM by user2616171

    Planning Form Metadata SQL Extract Query

    user2616171
      I am trying to find Oracle SQL logic that I can use to extract metadata that defines planning web forms from the HSP_OBJECT, HSP_FORM_...etc. tables. I need this information so that I can query and analyze metadata changes that could impact planning web forms. In particular I need to get a list of all "Account" dimension members that are within a form that are set for "Input" data property in the form. We are on Planning 11.1.2.1.101.8. Any help on this is very much appreciated.
        • 1. Re: Planning Form Metadata SQL Extract Query
          user2616171
          Still looking for a SQL solution as there are many planning forms and accounts that are involved. Going to the planning dimension and "show usage" option would be too difficult. Still looking for a backend/SQSL query solution using HSP_FORM repository query.
          • 2. Re: Planning Form Metadata SQL Extract Query
            Jake Turrell
            This is from Brian Marshall's Kscope12 presentation on the Hyperion Planning Repository. You should be able to modify this to suit your specific needs. Lots of other useful queries in this presentation . . . ODTUG should post these in a couple of months.

            SELECT
            o.[OBJECT_NAME] AS Form_Name
            ,REPLACE(REPLACE(REPLACE((SELECT flo.[OBJECT_NAME]
            FROM HSP_FORM_LAYOUT fl
            INNER JOIN HSP_OBJECT flo on fl.DIM_ID = flo.[OBJECT_ID]
            WHERE fl.FORM_ID = f.FORM_ID AND fl.LAYOUT_TYPE = 0 FOR XML Raw)
            , '"/><row OBJECT_NAME="', ', '), '<row OBJECT_NAME="', ''), '"/>', '' )
            AS POV_Dimensions
            ,REPLACE(REPLACE(REPLACE((SELECT flo.[OBJECT_NAME]
            FROM HSP_FORM_LAYOUT fl
            INNER JOIN HSP_OBJECT flo on fl.DIM_ID = flo.[OBJECT_ID]
            WHERE fl.FORM_ID = f.FORM_ID AND fl.LAYOUT_TYPE = 1 FOR XML Raw)
            , '"/><row OBJECT_NAME="', ', '), '<row OBJECT_NAME="', ''), '"/>', '' )
            AS Page_Dimensions
            ,REPLACE(REPLACE(REPLACE((SELECT flo.[OBJECT_NAME]
            FROM HSP_FORM_LAYOUT fl
            INNER JOIN HSP_OBJECT flo on fl.DIM_ID = flo.[OBJECT_ID]
            WHERE fl.FORM_ID = f.FORM_ID AND fl.LAYOUT_TYPE = 2 FOR XML Raw)
            , '"/><row OBJECT_NAME="', ', '), '<row OBJECT_NAME="', ''), '"/>', '' )
            AS Row_Dimensions
            ,REPLACE(REPLACE(REPLACE((SELECT flo.[OBJECT_NAME]
            FROM HSP_FORM_LAYOUT fl
            INNER JOIN HSP_OBJECT flo on fl.DIM_ID = flo.[OBJECT_ID]
            WHERE fl.FORM_ID = f.FORM_ID AND fl.LAYOUT_TYPE = 3 FOR XML Raw)
            , '"/><row OBJECT_NAME="', ', '), '<row OBJECT_NAME="', ''), '"/>', '' )
            AS Column_Dimensions
            ,REPLACE(REPLACE(REPLACE((SELECT fla.[OBJECT_NAME]
            FROM HSP_FORM_ATTRIBUTES fa
            INNER JOIN HSP_OBJECT fla on fa.DIM_ID = fla.[OBJECT_ID]
            WHERE fa.FORM_ID = f.FORM_ID FOR XML Raw)
            , '"/><row OBJECT_NAME="', ', '), '<row OBJECT_NAME="', ''), '"/>', '' )
            AS Attribute_Dimensions
            ,REPLACE(REPLACE(REPLACE((
            SELECT fc.CALC_NAME
            + CASE fc.RUN_ON_LOAD WHEN 1 THEN ' (Run on load)' ELSE '' END
            + CASE fc.RUN_ON_SAVE WHEN 1 THEN ' (Run on save)' ELSE '' END
            as CALCs
            FROM HSP_FORM_CALCS fc
            WHERE fc.FORM_ID = f.FORM_ID FOR XML Raw)
            , '"/><row CALCs="', ', '), '<row CALCs="', ''), '"/>', '' )
            AS Calcs
            ,REPLACE(REPLACE(REPLACE((
            SELECT aco.[OBJECT_NAME]
            FROM HSP_ACCESS_CONTROL ac
            INNER JOIN HSP_OBJECT aco ON aco.[OBJECT_ID] = ac.[USER_ID]
            WHERE ac.[OBJECT_ID] = f.FORM_ID AND ac.ACCESS_MODE = 3 FOR XML Raw)
            , '"/><row OBJECT_NAME="', ', '), '<row OBJECT_NAME="', ''), '"/>', '' )
            AS Users_Write
            ,REPLACE(REPLACE(REPLACE((
            SELECT aco.[OBJECT_NAME]
            FROM HSP_ACCESS_CONTROL ac
            INNER JOIN HSP_OBJECT aco ON aco.[OBJECT_ID] = ac.[USER_ID]
            WHERE ac.[OBJECT_ID] = f.FORM_ID AND ac.ACCESS_MODE = 1 FOR XML Raw)
            , '"/><row OBJECT_NAME="', ', '), '<row OBJECT_NAME="', ''), '"/>', '' )
            AS Users_Read
            ,REPLACE(REPLACE(REPLACE((
            SELECT aco.[OBJECT_NAME]
            FROM HSP_ACCESS_CONTROL ac
            INNER JOIN HSP_OBJECT aco ON aco.[OBJECT_ID] = ac.[USER_ID]
            WHERE ac.[OBJECT_ID] = f.FORM_ID AND ac.ACCESS_MODE = -1 FOR XML Raw)
            , '"/><row OBJECT_NAME="', ', '), '<row OBJECT_NAME="', ''), '"/>', '' )
            AS Users_Denied
            ,o.MODIFIED AS Last_Modified
            ,o.MODIFIED_BY AS Modified_By
            FROM
            HSP_FORM f
            INNER JOIN
            HSP_OBJECT o ON f.FORM_ID = o.[OBJECT_ID]
            • 3. Re: Planning Form Metadata SQL Extract Query
              user2616171
              In converting this SQL to Oracle SQL, i get an error "ORA-00907: missing right parenthesis" pertaining to the "FOR XML Raw" syntax. I am not familiar with this FOR/XML syntax. If I remove the FOR statement I get an error that the subquery does not return a single row. Do you have a converted SQL for Oracle?
              Thanks. Really appreciate your feedback!
              • 4. Re: Planning Form Metadata SQL Extract Query
                BrianMarshall
                Those pieces of the query are specific to SQL Server. Basically it takes the sub-query and turns it into a comma separated list in a single column. You can do something similar in Oracle, but I haven't had a lot of time to go through and actually do the conversion yet. Take a look at this:

                http://stackoverflow.com/questions/468990/how-can-i-combine-multiple-rows-into-a-comma-delimited-list-in-oracle

                There is a decent example there of how it would work. I haven't tested that function (don't have an Oracle DB handy at the moment), but I think it would look something like this:

                APPEND_FIELD('SELECT flo.[OBJECT_NAME] FROM HSP_FORM_LAYOUT fl INNER JOIN HSP_OBJECT flo on fl.DIM_ID = flo.[OBJECT_ID] WHERE fl.FORM_ID = f.FORM_ID AND fl.LAYOUT_TYPE = 0', ',')

                So basically feed it the sub-query and the delimiter and it should work. Again, I haven't had a chance to test it just yet, so give me a shout if it doesn't work and I'll hunt down an Oracle-based VM to get it working. Also, if you want the rest of the queries, just shoot me your e-mail address and I can send it to you.

                -Brian
                • 5. Re: Planning Form Metadata SQL Extract Query
                  BrianMarshall
                  Also, looking a little closer, that might not work on a sub-query. So...you may want to try one of the other options from the link above. It depends on what version of Oracle you are running as some of the newer versions have string aggregation functions included. Also, there appears to be an XML-type method much like SQL Server has.
                  • 6. Re: Planning Form Metadata SQL Extract Query
                    user2616171
                    I was not able to get the query converted to Oracle SQL. Let me know if you have version that has been converted....really appreciate your help on this.
                    • 7. Re: Planning Form Metadata SQL Extract Query
                      CL
                      Oh my goodness, this saved my life, and probably the life of several other people. Thanks Jake and Brian.

                      I took Brian's code (and oh yeah, Kscope is an awesome and useful conference) and modified it for Oracle.

                      NB -- wm_concat is not a totally supported keyword, and (as far as I can tell) not supported before 11g. Here's a really good review of it and other strategies:
                      Let us discussion at wmsys.wm_concat VS ListAgg

                      Regardless, I am nothing if not a SQL hack, it works in my client's environment (really nicely, I must say), and even though this thread is old, I throw it out there so no one else has to do this.

                      And with that disclaimer, the Oracle version of the query:
                      SELECT 
                        o.OBJECT_NAME AS Form_Name
                        , (
                            SELECT 
                              wm_concat(flo.OBJECT_NAME)
                            FROM 
                              HSP_FORM_LAYOUT fl
                            INNER JOIN 
                              HSP_OBJECT flo on fl.DIM_ID = flo.OBJECT_ID
                            WHERE 
                              fl.FORM_ID = f.FORM_ID AND fl.LAYOUT_TYPE = 0
                          )
                          AS POV_Dimensions
                        , (
                            SELECT 
                              wm_concat(flo.OBJECT_NAME)
                            FROM 
                              HSP_FORM_LAYOUT fl
                            INNER JOIN 
                              HSP_OBJECT flo on fl.DIM_ID = flo.OBJECT_ID
                            WHERE fl.FORM_ID = f.FORM_ID AND fl.LAYOUT_TYPE = 1
                          )
                          AS Page_Dimensions
                        , (
                            SELECT 
                              wm_concat(flo.OBJECT_NAME)
                            FROM
                              HSP_FORM_LAYOUT fl
                            INNER JOIN 
                              HSP_OBJECT flo on fl.DIM_ID = flo.OBJECT_ID
                            WHERE 
                              fl.FORM_ID = f.FORM_ID AND fl.LAYOUT_TYPE = 2
                          )
                          AS Row_Dimensions
                        , (
                            SELECT 
                              wm_concat(flo.OBJECT_NAME)
                            FROM
                              HSP_FORM_LAYOUT fl   
                            INNER JOIN 
                              HSP_OBJECT flo on fl.DIM_ID = flo.OBJECT_ID
                            WHERE
                              fl.FORM_ID = f.FORM_ID AND fl.LAYOUT_TYPE = 3
                          )
                          AS Column_Dimensions
                        , (
                            SELECT 
                              wm_concat(fla.OBJECT_NAME)
                            FROM 
                              HSP_FORM_ATTRIBUTES fa
                            INNER JOIN 
                                HSP_OBJECT fla on fa.DIM_ID = fla.OBJECT_ID
                            WHERE 
                                fa.FORM_ID = f.FORM_ID    
                          )
                          AS Attribute_Dimensions
                        , (
                            SELECT 
                              wm_concat(fc.CALC_NAME || CASE fc.RUN_ON_LOAD WHEN 1 THEN ' (Run on load)' ELSE '' END || CASE fc.RUN_ON_SAVE WHEN 1 THEN ' (Run on save)' ELSE '' END)
                            AS CALCs
                            FROM 
                              HSP_FORM_CALCS fc
                            WHERE 
                              fc.FORM_ID = f.FORM_ID
                          )
                          AS Calcs
                        , (
                            SELECT 
                              wm_concat(aco.OBJECT_NAME)
                            FROM 
                              HSP_ACCESS_CONTROL ac
                            INNER JOIN 
                              HSP_OBJECT aco ON aco.OBJECT_ID = ac.USER_ID
                            WHERE 
                              ac.OBJECT_ID = f.FORM_ID AND ac.ACCESS_MODE = 3
                          )
                          AS Users_Write
                        , (
                            SELECT 
                              wm_concat(aco.OBJECT_NAME)
                            FROM 
                              HSP_ACCESS_CONTROL ac
                            INNER JOIN 
                              HSP_OBJECT aco ON aco.OBJECT_ID = ac.USER_ID
                            WHERE 
                              ac.OBJECT_ID = f.FORM_ID AND ac.ACCESS_MODE = 1
                            )
                          AS Users_Read
                        , (
                            SELECT 
                              wm_concat(aco.OBJECT_NAME)
                            FROM 
                              HSP_ACCESS_CONTROL ac
                            INNER JOIN 
                              HSP_OBJECT aco ON aco.OBJECT_ID = ac.USER_ID
                            WHERE 
                              ac.OBJECT_ID = f.FORM_ID AND ac.ACCESS_MODE = -1
                          )
                          AS Users_Denied
                        , o.MODIFIED AS Last_Modified
                        , o.MODIFIED_BY AS Modified_By    
                      FROM
                        HSP_FORM f
                      INNER JOIN
                        HSP_OBJECT o ON f.FORM_ID = o.OBJECT_ID
                      Regards,

                      Cameron Lackpour
                      • 8. Re: Planning Form Metadata SQL Extract Query
                        user2616171

                        Thanks all for following up and updating this as I am sure many of us will benefit from this code.   Is it possible to list the row dimension "items" in the form that are enabled for input?  The query above provides the correct POV showing dimensions, but if we can identify Read/Write items will help us tremendously for a vast of ways to  identify impact to member items, read/write, security, etc.  Again, thanks all for following and updating this blog.