Clarity PPM

Expand all | Collapse all

Great new XOG tool - check it out!

Suman Pramanik05-17-2019 04:36 AM

  • 1.  Great new XOG tool - check it out!

    Posted 03-05-2015 09:52 AM

    We've been on Clarity (on Demand) for about five years and I've done a little XOGing here and there just to move configuration between environments. I've always used the command line tool that CA provides which works, but is not exactly user friendly. Each time I needed to move a large amount of config it required many hours of time to prep the XML files, test them, then move them from Dev to Test then to Production. Files can only be done one at a time and it's a pretty labor intensive effort that requires a lot of concentration.

     

    Enter the XOG & Query Bridge tool from IT ROI (which is FREE, by the way!)  It's AWESOME!  For XOGing it offers two methods - a simple request version similar to the GUI version that was available in Clarity until recently and a bulk input version that can handle larger input files and even multiple files compressed into zip format. I used the simple version to create my write files one by one then zipped them all up and submitted them as one bulk request into my test environment. Naturally this won't eliminate the need for you to clean up the files and remove unwanted stuff that gets pulled out before you XOG things back in. The XOG Bridge does, however, also offer an XSLT tranformation tool that could assist with that. I haven't used it yet because I'm not that advanced with XOG but if I had it might have helped me resolve a few issues that caused me some grief. Once I had all the bugs worked out of my individual files, moving everything into Production very literally took FIVE MINUTES. Amazing.

     

    The other component of the tool is a query client that lets you supply SQL code and then spits out the result for you as a table on screen plus as an Excel files. For On Demand customers like me this is as huge help. Now I don't have to go through the headache of logging in to VPN just to do a quick query on one of my environments. I haven't really used the SQL tool much yet, and I must admit that my first attempts to copy/paste some existing code ran into a few hiccups. It doesn't seem to accept table or field aliases, which is a bummer because I always use them when writing my SQL code. I also wish that they would provide the NSQL code that they create behind the scenes as an output in addition to the data itself. Saving me some of the headache of converting my SQL to NSQL would be a huge help.  At any rate, it's still a very useful feature that I will likely take advantage of more in the future.

     

    There is a user guide provided with the tool, as well as a desktop version that offers most of the same functionality available in the web version. Check it out and register for your account!

    https://xogbridge.itroisolutions.com/xogbridge

     

    Disclaimer: I don't work for IT ROI and they aren't paying me to submit this post. I'm just a pleased customer who wants to give Federico (fpena) and his team some thanks for a great tool!



  • 2.  Re: Great new XOG tool - check it out!

    Posted 03-05-2015 10:22 AM

    Got this update from Federico after my post. Just another reason to use this tool - great support behind it!

     

    "As for the NSQL you ask for, it dynamically creates it for you it should have a name like

     

    Username_loginUser_qbtemp.  In my case it is fpena_fpena_qbtemp.  Note that it overwrites the NSQL every time you change it, but you can get the NSQL out of it.

    As for aliases and such, yes agreed, not very flexible, and this is because we have to translate to NSQL (which is less flexible), but a good workaround is to write an inline view, and put your aliases in there (meaning your from is your select, then sky is the limit of aliases).

     

    For example

     

    Select prjName, prjCode

    From

                    (select inv.code prjCode, inv.name prjName from inv_investments inv)

    Hope this helps."



  • 3.  Re: Great new XOG tool - check it out!

    Posted 03-05-2015 10:40 AM

    THanks Amy for your kind words.

     

    Just in case someone is on premise or wants a desktop version of the tool, they can download here  https://xogbridge.itroisolutions.com/xogbridge-resources/desktop-versions/XOG-Query-Bridge-desktop-v1.0.4.exe

    Thanks

    Federico



  • 4.  Re: Great new XOG tool - check it out!

    Posted 03-09-2015 10:08 AM

    Since there is a misunderstanding of how XSLT is used, i have decided to add a couple of examples.

    The below example has several different examples of how to do XML transformation.  This is somewhat documented, so you may uncomment/comment the piece that you need or the example that you need.

     

    I will post an example of how to clean up objects in the next couple of days.

    EXAMPLE BEGINS HERE.

     

    <xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

         <xsl:output indent="yes" />

         <xsl:strip-space elements="*" />

          <!-- copy all nodes and attributes -->

         <xsl:template match="*|@*">

              <xsl:copy>

                   <xsl:apply-templates select="node()|@*" />

              </xsl:copy>

         </xsl:template>

      <!-- ############################################

      Remove Last updated by from all sections  This is in case the user does not exist in target system

      ##################################################-->

      <xsl:template match="@lastUpdatedBy" />

      <!-- remove all XOGOutput Tags These are useless when xogging back in-->

         <xsl:template match="XOGOutput" />

      <!-- remove General  Tags as they are not needed and can cause errors -->

         <xsl:template match="General" />

         <!-- ##################################################################

      remove all Tags whose languageCode whose atribute Language is not en

      #####################################################################-->

         <xsl:template match="*[@languageCode!='en']" />

      

      <!-- ######################################################

       Need to do Project twice on create from template

       Here we check to see if isNewFromTemplate is true

       If Yes (xsl:when) Then copy project elements twice

      ######################################################### -->

      <xsl:template match="Project">

         <xsl:choose>

             

              <xsl:when test="@isNewFromTemplate='true'">

                   <Project>

                        <xsl:for-each select="@*">

                             <xsl:if test="name() = 'active'

                                              or name() = 'finish'

                                              or name() = 'start'

                                              or name() = 'managerResourceID'

                                              or name() = 'name'

                                              or name() = 'projectID'

                                              or name() = 'status'

                                              or name() = 'template'

                                              or name() = 'fromTemplate'

                                              or name() = 'format'">

                                  <xsl:attribute name="{name()}"><xsl:value-of select="."/></xsl:attribute>

                              </xsl:if>

                        </xsl:for-each>

                        <CustomInformation>

                             <ColumnValue name="partition_code"><xsl:value-of select="CustomInformation/ColumnValue[@name='partition_code']"/></ColumnValue>

                            

                        </CustomInformation>

                   </Project>

                   <Project>

                        <xsl:for-each select="@*">

                             <xsl:if test="name() != 'isNewFromTemplate'

                                    and name() != 'fromTemplate'">

                                  <xsl:attribute name="{name()}"><xsl:value-of select="."/></xsl:attribute>

                              </xsl:if>

                        </xsl:for-each>

                        <xsl:apply-templates select="node()" />

                   </Project>

              </xsl:when>

              <xsl:otherwise>

      

       <!-- ######################################################

      When it is not isNewFromTemplate we only copy the project elements once

      

      ######################################################### -->

                   <Project>

                        <xsl:apply-templates select="node()|@*[name(.)!='isNewFromTemplate']" />

                   </Project>

              </xsl:otherwise>

         </xsl:choose>

         </xsl:template>

         <!-- escape text nodes (on ColumnValue elements) -->

         <xsl:template match="ColumnValue">

              <xsl:element name="ColumnValue">

                   <xsl:attribute name="name"><xsl:value-of select="@name"/></xsl:attribute>

                   <xsl:call-template name="escapeQuote">

                        <xsl:with-param name="text" select="." />

                   </xsl:call-template>

              </xsl:element>

         </xsl:template>

         <!-- ##################################################################

      Handle Multi Value Lookup  This piece of code tokenizes each value as to allow for multiselects

      that come inside of the XOG file.

      ####################################################################-->

         <xsl:template match="ColumnValue[@name='xxxAttributeCodexxx']">

              <xsl:element name="ColumnValue">

                   <xsl:attribute name="name">

                    <xsl:text><xsl:value-of select="@name"/></xsl:text>

                   </xsl:attribute>

                   <xsl:call-template name="tokenize">

                        <xsl:with-param name="ColumnValue" select="text()"/>

                   </xsl:call-template>

              </xsl:element>

       </xsl:template>

      

         <!-- ##########################################################################

      Remove OBSAssocs when name or id is empty -- THis is important so it does not fail as all 3 values are required

      ###########################################################################-->

         <xsl:template match="OBSAssocs">

              <xsl:choose>

                   <xsl:when test="OBSAssoc/@id">

                        <OBSAssocs complete="false">

                           <xsl:apply-templates select="@*|node()"/>

                       </OBSAssocs>

                   </xsl:when>

               </xsl:choose>

         </xsl:template> 

         <!--###################################################################################

      Replace attribute value $CLEAR$ to an empty string -- useful for blanking out records (nodes)

      inside of cells.  Meaning if client wants to send blank value, they send $CLEAR$

      #########################################################################################

      -->

         <xsl:template match="@*">

                   <xsl:attribute name="{name()}">

                        <xsl:choose>

                             <xsl:when test="current()='$CLEAR$'">

                                  <xsl:text></xsl:text>

                            </xsl:when>

                             <xsl:otherwise>

                                  <xsl:call-template name="escapeQuote">

                                       <xsl:with-param name="text" select="." />

                                  </xsl:call-template>

                             </xsl:otherwise>

                        </xsl:choose>

                    </xsl:attribute>

         </xsl:template>

         <xsl:template match="*/text()[.='$CLEAR$']"></xsl:template>           

         <!-- Split delimited values into Value tags -->

         <xsl:template name="tokenize">

             <xsl:param name="ColumnValue" select="."/>

             <xsl:param name="separator" select="','"/>

             <xsl:choose>

                   <xsl:when test="not(contains($ColumnValue, $separator))">

                         <Value>

                               <xsl:value-of select="normalize-space($ColumnValue)"/>

                         </Value>

                   </xsl:when>

                   <xsl:otherwise>

                         <Value>

                               <xsl:value-of select="normalize-space(substring-before($ColumnValue, $separator))"/>

                         </Value>

                         <xsl:call-template name="tokenize">

                               <xsl:with-param name="ColumnValue" select="substring-after($ColumnValue, $separator)"/>

                         </xsl:call-template>

                   </xsl:otherwise>

             </xsl:choose>

         </xsl:template>

      

      

         <!-- Replace Escaped quotes with un-escaped ones -->

         <xsl:template name="escapeQuote">

              <xsl:param name="pText" select="."/>

              <xsl:if test="string-length($pText) >0">

                   <xsl:value-of select="substring-before(concat($pText, '\&quot;'), '\&quot;')"/>

                   <xsl:if test="contains($pText, '\&quot;')">

                        <xsl:text>"</xsl:text>

                        <xsl:call-template name="escapeQuote">

                             <xsl:with-param name="pText" select="substring-after($pText, '\&quot;')"/>

                        </xsl:call-template>

                   </xsl:if>

              </xsl:if>

    <!-- ############################################################################

    remove unused OBS's based on a specific value.  THis will remove the complete node

    #################################################################################

    -->

     

     

       <xsl:template match="OBSAssocs/OBSAssoc[@id='location' or @id='Department OBS' or @id='service_area' ]"/>

    <!--

    ########################################################################################

    Adding complete=true to different sections of security parameters. This is handy for migrating group/user/obs rights and making sure that they all get moved/removed from target

    ########################################################################################

    -->

        <xsl:template match="obs">

             <xsl:copy>

       <xsl:attribute name="complete">

        <xsl:value-of select="'true'"/>

       </xsl:attribute>

       <xsl:apply-templates select="@*"/>

           <xsl:apply-templates select="node()"/>

             </xsl:copy>

         </xsl:template>

    <xsl:template match="GlobalRights">

             <xsl:copy>

       <xsl:attribute name="complete">

        <xsl:value-of select="'true'"/>

       </xsl:attribute>

       <xsl:apply-templates select="@*"/>

           <xsl:apply-templates select="node()"/>

             </xsl:copy>

         </xsl:template>

      <!-- Instance Rights-->

      <xsl:template match="InstanceRights">

             <xsl:copy>

       <xsl:attribute name="complete">

        <xsl:value-of select="'true'"/>

       </xsl:attribute>

       <xsl:apply-templates select="@*"/>

           <xsl:apply-templates select="node()"/>

             </xsl:copy>

         </xsl:template>

      <!-- OBS Instance Rights-->

        <xsl:template match="InstanceOBSRights">

             <xsl:copy>

       <xsl:attribute name="complete">

        <xsl:value-of select="'true'"/>

       </xsl:attribute>

       <xsl:apply-templates select="@*"/>

           <xsl:apply-templates select="node()"/>

             </xsl:copy>

         </xsl:template> 

      

         </xsl:template>

    </xsl:stylesheet>

     

     

     

     

    <!-- ###################################################################################################################

    THis code below is handy for other examples of how to use XSLT for transformation  Note that this part is all comented out

     

     

    <!--  Normal Attribute replace id from an external file that contains the 2 id's one from source, one from target-->

    <!-- Replace alt_manager-->

      <xsl:template match="ColumnValue[@name='alt_manager']">

      <xsl:text disable-output-escaping="yes">&lt;ColumnValue name=&quot;alt_manager&quot;&gt;</xsl:text>

     

     

      <xsl:variable name='external_lookups'

      select="document('F:\Directv\Migration\replace_ids\UserId.xml')/Records/Record[@source_id=current()]/@target_id" />

      <xsl:value-of select="$external_lookups" />

     

     

      <xsl:text disable-output-escaping="yes">&lt;/ColumnValue&gt;</xsl:text>

      

      </xsl:template>

     

     

      <!--Set manager as admin as current manager does not exist. 

      Good when migrating users from one system to the other and all users not present--> 

    <!-- first run use this code, second one, comment it out-->

      <xsl:template match="@managerUserName[parent::Resource]">

      <xsl:attribute name="managerUserName">

      <xsl:text>admin</xsl:text>

      </xsl:attribute>

      </xsl:template> 

     

     

    <!--Set user active so all associations can be made then deactivate--> 

    <!-- first run use this code, second comment it-->

      <xsl:template match="@isActive[parent::Resource]">

      <xsl:attribute name="isActive">

      <xsl:text>true</xsl:text>

      </xsl:attribute>

      </xsl:template> 

    ################################################################################################################################

    -->



  • 5.  Re: Great new XOG tool - check it out!

    Posted 03-10-2015 02:30 AM

    Thanks for sharing these, Federico

     

    NJ



  • 6.  Re: Great new XOG tool - check it out!

    Posted 03-11-2015 11:16 PM

    Here is the XSLT for trimming the Object XML, that leaves only the attribute tag, and NLS for english.

     

    <xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

         <xsl:output indent="yes" />

         <xsl:param name="incomingXML" />

         <xsl:strip-space elements="*" />

    <!-- #################################################### -->

    <!-- GLOBAL REPLACE FOR ALL XML FILES -->

    <!-- Change Version to 12 -->

    <!-- Remove last updated by in all tags -->

    <!-- Remove XOG Output -->

    <!-- AUTHOR: IT-ROI Solutions -->

    <!-- #################################################### -->

    <!-- copy all nodes and attributes -->

         <xsl:template match="*|@*">

              <xsl:copy>

                   <xsl:apply-templates select="node()|@*" />

              </xsl:copy>

         </xsl:template>

    <!-- Change the XOG Version to 12.0-->

      <xsl:template match="@version[parent::Header]">

      <xsl:attribute name="version">

      <xsl:text>12.0</xsl:text>

      </xsl:attribute>

      </xsl:template>

    <!-- Remove Last updated by from all sections-->

      <xsl:template match="@lastUpdatedBy" />

    <!-- remove all XOGOutput Tags -->

         <xsl:template match="XOGOutput" />

     

    <!-- remove virtual attributes --> 

        <!-- <xsl:template match="*[@virtual!='false']" /> -->

    <!-- first run removig virutal, then run it with virtuals -->

     

     

    <!-- #################################################### -->

    <!-- Below code is to remove everything from object Read except for attribute tags

    Ensure referential XML files have run prior to this one such as lookups, queries,

    Run Views separately using the view read to avoid issues -->

    <!-- #################################################### -->

    <!-- Remove all nls languageCode that do not equal 'en' handy for issues with character sets -->

         <xsl:template match="*[@languageCode!='en']" />

         <!-- remove all pages Tags -->

         <xsl:template match="pages" />

         <!-- remove all portlets Tags -->

         <xsl:template match="portlets" />

         <!-- remove all Processes Tags -->

         <xsl:template match="Processes" />

         <!-- remove all jobDefinitions Tags -->

         <xsl:template match="jobDefinitions" />

         <!-- remove all queries Tags -->

         <xsl:template match="queries" />

         <!-- remove all views Tags -->

         <xsl:template match="views" />

         <!-- remove all lookups Tags -->

         <xsl:template match="lookups" />

         <!-- remove all partitionModels Tags -->

         <xsl:template match="partitionModels" />

     

      <!-- remove all DisplayMappings Tags -->

         <xsl:template match="displayMappings" />

      <!-- Remove all <scoreContributions/>-->

      <xsl:template match="scoreContributions" />

         <!-- remove all links Tags -->

         <xsl:template match="links" />

         <!-- remove all action Tags -->

         <xsl:template match="action" />

         <!-- remove all actions Tags -->

         <xsl:template match="actions" />

     

      

         <!-- remove all autonumbering Tags -->

         <xsl:template match="autonumbering" />

     

     

    </xsl:stylesheet>



  • 7.  Re: Great new XOG tool - check it out!

    Posted 03-13-2015 11:56 AM

    Been playing a little with this today (the desktop version) - specifically for bulk XOG file uploads.

     

    Overall ; very neat!   - seems to work OK, but I'm struggling a bit with getting the results of the various XOG file outputs easily;

     

    The tool lists "100% success" against all my XOG requests and provides a "Download" button ; I can download a zip file (one at a time - ugh!), but even then that only contains a log file of the result of the execution of the XOG ; I am looking for the XOG response XML itself because I know that at least one of my input files is going to fail - but I can't seem to do this at all within the tool ; everything looks OK in the tool and OK with the "log file" results.

     

    The way that I have found that I can spot the XML responses is via a file-system search on the working directory (by looking for any ".xml" files under the xogresponse directory) - and I can tell immediately from the size of the XMl files whether a FAILURE has occurred.  (So I can find what I'm looking for, just need to remember to clear out that directory before I start).

     

    --

     

    So my question really is ; am I missing something - should I be able to tell from the tool itself whether any of the bulk XOGs have generated a XML failure response (but run through OK)?

     

     

    (I'm quibbling really here - I like it and would happily recommend it)



  • 8.  Re: Great new XOG tool - check it out!

    Posted 03-13-2015 12:07 PM

    David - The zip file you get should have the log and the result XML too (see my screenshot)

    Yes, the included log file is not super helpful because as long as an XML file is returned the log file will show success even if your actual result is not successful.



  • 9.  Re: Great new XOG tool - check it out!

    Posted 03-13-2015 12:09 PM

    Hi Dave,

    at the moment the tool only lets you know weather it was successful sending it to XOG. It does not parse the response to provide a status on the actual request.  With this being said, the desktop version (under options) has a workpath folder which stores the requests and responses.  as for the web version, i recommend clicking on the send results via email checkbox that way you do not have to download them but they go to your inbox.

    We are always open to enhancement requests (XOG & Query Bridge for CA PPM Feedback), and it sounds like the one you are mentioning should be functionality that we could include.

    What do you recomend for seeing the results for each run?  1 zip file for the complete bulk?

    I appreciate your feedback.

    Federico



  • 10.  Re: Great new XOG tool - check it out!

    Posted 03-13-2015 12:30 PM

    @amy - interesting, the zip file I get when I download (from the desktop version) is only the .log file, so maybe that differs from the web version

     

    @fpena - yeah that workpath folder is where I was finding the log files - I can work OK with that, I was really just checking whether I was missing something obvious!

     

    Ideally I'd expect the bulk-XOG screen to report the XOG success/failure (as another quibble - "Success" on that screen is rather misleading, since to me "SUCCESS"/"FAILURE" is something I look for in the XOG output file).

     

    Just to clarify - I was using the bulk load to run in a number of portlet config files (rather than data) - so the response is rather binary ; either the portlet loaded ("SUCCESS") or it didn't ("FAILURE")  - for other types of XOG file the answer is less clear, data loads could have a combination of success/failure records - I appreciate that this is a somewhat tricky problem!

     

    I'd definitely say one file/download for the entire bulk-results is the right way (or you could multi-select the relevant results from the window send all the selected ones - I noted I was not able to select more than one row at a time in that results window)



  • 11.  Re: Great new XOG tool - check it out!

    Posted 03-18-2015 08:50 PM

    Dave based on your request, a new version is available for download with the following fixes/enhancements

    XogBridge desktop version

    Change log

    Version 1.0.5

    16 March 2015

    What is new in 1.0.5?

    * ‘Download all’ button added that allows to download XOG results from a selected set of requests.

    * Downloadable files have been added a suffix indicating the result of the XOG requests: e.g. T3_I2_U1_F0, where:

    T = Total, I = Inserted, U = Updated, F = Failed

    * The log generated upon each bulk request has been enhanced to include the XOG request result statistics: number of total, inserted, updated and failed records. It ONLY applies for requests submitted after the installation of the new version.

    * Menu option added to access the application log.

    * Latest version automatic verification.

    Improvements

    * In the execution history list, now it is possible to select several rows and perform the following actions: Cancel, Delete and Download.

    Bugs fixed

    * Rename results zip file based on the XOG request result statistics for enhanced usability.

    * Error occurring when trying to download the result of a cancelled request.



  • 12.  Re: Great new XOG tool - check it out!

    Posted 04-21-2015 12:26 PM

    Just as an FYI, V 1.0.6 is now available for download.  Remember, use an email address that you can see so you get our application maintenance notifications.

    all above issues plus some new functionality is available. Stay posted for release notes.

    Cheers

    Federico



  • 13.  Re: Great new XOG tool - check it out!

    Posted 06-08-2015 09:29 AM

    Hi All,

    V1.07 (V1.10 Web version) is available for Download.  one of the main new features is that it allows end users to extract data from existing NSQL queries (removing limitations for the export to excel).

    to see full update LOG click here  http://www.itroisolutions.com/ppm-blog/xog-query-bridge-xq-change-log?utm_campaign=XOG+Query+Bridge+&utm_source=hs_email…

    we have had tremendous feedback, so we hope that your team can take advantage of this functionality.

    Thanks

    Federico



  • 14.  Re: Great new XOG tool - check it out!

    Posted 05-16-2019 04:04 PM

    For anyone that stumbles across this older post:

    The new URL for the XOGBridge is at: http://xogbridge.com 

     

    Thanks,
    Josh



  • 15.  Re: Great new XOG tool - check it out!

    Posted 05-17-2019 04:36 AM

    Awesome tool Josh. Thanks for sharing