Staging Tables vs. Objects

[Mitch Cofman]

Hi - we are in the process of implementing Clarity (15.7.1) for the first time. The application is up and running and we are trying to load data from external source systems. 

We wanted to use REST, but there is no API for Resources at this time. So we have made the decision to use XOG.

My question, what is the best practice when writing to Clarity? Should we write directly to the Object: Resources or implement a staging table? If the best practice is to utilize staging tables, is there documentation that you could point me too, that I could follow on how to implement?

I am able to read from Clarity. We have manually added some projects and resources. I am also able to write a change to the Object:Resources table.

Thank you,
Mitch

------------------------------
Mitch Cofman
------------------------------

[Martti Kinnunen]

The best practice is definitely NOT to write anything directly to the database.
A supported way is to use XOG. Others are third party integration tools with a UI that use supported methods like XOG.

Years ago when I have moved or created resources/users into a new system I have used XOG for that.
First collect the data and put it into an Excel spreadsheet. Then use tools of you choice to create xml file from the data. That can be done with formulas, mail merge, XML editors and others. There have been discussion threads on that in this community.

[Mitch Cofman]

Hi Martti,

Thanks for the reply. I glad to hear that writing directly to the database is not considered a best practice.

Some additional information, our source system for resources is MuleSoft and we have the ability to save the exported data in an XML format. If I understand correctly, this would allow us to bypass the Excel file.

I have created a new object to be my staging table. When I tired to write the staging table, I receive this error message: <Exception type="com.niku.xog.XOGException">Invalid request write for object GTRI Resource Staging Table</Exception>

Do you know how I can troubleshoot this error?

Thanks,

[Martti Kinnunen]

Well the thing is that you need the xml file in correct format and it does not matter how you get it.
So you your data in the format it is in
rsm_resources_write.xml
and
cmn_users_write.xml
in the xog client sample files. Fine

Then you need to set up a working XOG connection that may be the command line client that comes with Clarity and can be downloaded.
It like to use the XOGBridge from ITROI/Rego

A typical test would be to read the admin user.

When you start writing you need the rights to create new items and XOG write the object items.

Regarding your error if that is a custom object create a record in the GUI if you do not have any and the XOG read it out.
When you get it properly read out change the output file for the name and ID and try to write a new record.

I am not familiar enough with debugging to say specific about your error message. It could mean that the target object is not recognized, the xml file is not the correct format or something else. I do not think the rights are the problem.

[Martti Kinnunen]

I should try the following

<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_read.xsd">
<Header version="6.0.13" action="read" objectType="customObjectInstance" externalSource="NIKU">
<CustomObjectInstanceQuery>
<Filter name="objectCode" criteria="EQUALS">GTRI Resource Staging table</Filter>
<Filter name="instanceCode" criteria="EQUALS">592620</Filter>
</CustomObjectInstanceQuery>
</NikuDataBus>

In other words you need to have both  the  object code and the instance code in the query. Though  what you gave GTRI Resource Staging table, sounds more like the object name and not the code.

Did you try to read the admin user and did that come out all right. Ie. is you client set up OK?

[Mitch Cofman]

Hi Martti,

 

I have been testing this today and made the changes you suggested. You were correct, GTRI Resource Staging Table is the name of the Object.

 

I did read the admin user and reviewed many of the community posts. I was able to clear the error that I was having. The issue that I am experiencing now is that my XOG write returns not records event though it was successful.

 

I have narrowed it down to the objectCode and instanceCode lines of my xml file as the issue.

 

Here is the XML file code:

 

<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_read.xsd">

                <Header version="8.0" action="read" objectType="customObjectInstance" externalSource="NIKU"/>

                                <CustomObjectInstanceQuery>

                                                <Filter name="objectCode" criteria="EQUALS">gtri_rst_obj</Filter>

                                                <Filter name="instanceCode" criteria="EQUALS">*</Filter>

                                </CustomObjectInstanceQuery>

</NikuDataBus>

 

What I am trying to do is to be able to read the record that I manually added to the table.

 

Here is the output xml file that I am getting:

 

<NikuDataBus xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance xsi:noNamespaceSchemaLocation="../xsd/nikuxog_customObjectInstance.xsd">

<Header action="write" externalSource="NIKU" objectType="customObjectInstance" version="15.7.1.270"/>

<customObjectInstances objectCode="gtri_rst_obj"></customObjectInstances><XOGOutput> <Object type="customObjectInstances"/> <Status state="SUCCESS"/>

<Statistics insertedRecords="0" failureRecords="0" totalNumberOfRecords="0" updatedRecords="0"/> <Records/>  </XOGOutput></NikuDataBus>

 

What am I missing?

 

Thanks,

Mitch

 

Mitch Cofman

PPM Consultant – GTRI

[e-mail]

[phone]

 

[Martti Kinnunen]

There are limitations how wild cards are supported in XOG. There have been threads where that is covered.
If you just want all instances you could leave the object instance filter line completely out.
Then it will bring all the instances where the user who is logged in with the XOG client has rights.

The output suggest that the xog query ran sucessfully, but there where no records that match the filter because of the wild card)

[Mitch Cofman]

Hi Martti, I tested the removal of the wild card and I received the same result. The XOG Query ran successfully, but returned no results.

 

<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_read.xsd">

                <Header version="8.0" action="read" objectType="customObjectInstance" externalSource="NIKU"/>

                                <CustomObjectInstanceQuery>

                                                <Filter name="objectCode" criteria="EQUALS">gtri_rst_obj</Filter>

                                                <Filter name="instanceCode" criteria="EQUALS">Cofman</Filter>

                                </CustomObjectInstanceQuery>

</NikuDataBus>

 

Any other thoughts?

 

Thanks,

 

Mitch Cofman

PPM Consultant – GTRI

[e-mail]

[phone]

 

[Martti Kinnunen]

Try

<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_read.xsd">

                <Header version="8.0" action="read" objectType="customObjectInstance" externalSource="NIKU"/>

                                <CustomObjectInstanceQuery>

                                                <Filter name="objectCode" criteria="EQUALS">gtri_rst_obj</Filter>

                                              

                                </CustomObjectInstanceQuery>

</NikuDataBus>

Are you running the XOG with your own ID or some other ID? Which XOG rights does that ID have?
If it is not your own ID what rights does the user have to GTRI Resource Staging Table? (view and edit?)

[Mitch Cofman]

Good Morning Martti,

 

I made the change that you suggested by removing the instanceCode line and received the following error:

 

<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:noNamespaceSchemaLocation="../xsd/nikuxog_customObjectInstance.xsd">

<Header action="write" externalSource="NIKU" objectType="customObjectInstance" version="15.7.1.270"/><customObjectInstances objectCode="gtri_rst_obj">

</customObjectInstances><XOGOutput> <Object type="customObjectInstances"/> <Status state="FAILURE"/>

<Statistics insertedRecords="0" failureRecords="0" totalNumberOfRecords="0" updatedRecords="0"/> <Records><Record>

<KeyInformation><column name="objectCode">gtri_rst_obj</column><column name="instanceCode"/>

</KeyInformation><ErrorInformation><Severity>FATAL</Severity><Description>Custom Object Instance Export Failed</Description><Exception>

<![CDATA[SQL Exception with error code : 936 message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00936: missing expression]]>

</Exception></ErrorInformation></Record></Records></XOGOutput></NikuDataBus>

 

Adding the instanceCode back in and running again clears this error, but no results are returned.

 

I am running the XOG with my ID. I have also tested with the Admin account. No changes with the change of ID's.

 

Here are the rights that I am currently assigned to under the Instance Access Rights:

 

 

Under the Access to this Object. For Resources, it's just me and I have the right Object-Administrator.

Under the Group Rights, I have Administration – XOG, which I am part of this group.

 

 

One other thing to note: If I make the change below  to use one of the attributes (see below):

<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_read.xsd">

              <Header version="8.0" action="read" objectType="customObjectInstance" externalSource="NIKU"/>

                             <CustomObjectInstanceQuery>

                                           <Filter name="objectCode" criteria="EQUALS">gtri_last_name</Filter>

                                           <Filter name="instanceCode" criteria="EQUALS">Cofman</Filter>

                             </CustomObjectInstanceQuery>

</NikuDataBus>

 

 

The Query fails and the error message is: XOG: Insufficient rights to perform XOG read of custom object instance</Description><Exception><![CDATA[

java.lang.NullPointerException

 

I have looked everywhere and I do not see any place to add any additional rights.

 

Hope this additional information helps resolve the issue.

 

Thanks,

 

Mitch Cofman

PPM Consultant – GTRI

[e-mail]

[phone]

 

[Rodriguez warne]

Thanks for sharing this 
McDVOICE

[David Morton]

Not addressing any of your issues with XOG at all ; but is staging tables even going to help you at all in creating resources?

Even if you create data in your staging table correctly, that has not created a resource, the only way to get information from the staging table to the resource is via (another) XOG?

So why do two lots of work, why not "just" XOG the resource straight away?

[Mitch Cofman]

Hi David,

 

My first question asked of this community was to determine whether we should write directly into the Resource Table vs. a staging table. Reply that I received was best practice was not to write directly to the database, hence the justification for implementing a staging table. In one of the previous companies I worked at, staging tables were utilized extensively; however I was not involved directly, so I am in the process of implementing for the first time.

 

Second, we are going to setup a nightly process that will automate this entire process. Our source system will be Mulesoft and as I mentioned, we wanted to implement using REST, but the Resource REST API is not supported yet.

 

Last we want to be able to manage the changes to the resource (I.e. name change or email address change).

 

Hope this answers your question.

 

Mitch Cofman

PPM Consultant – GTRI

[e-mail]

[phone]

 

[David Morton]

Think you misunderstand my point.

The only way to write data into the resources object is via the XML interfaces (XOG)
.
You will have to build that XOG code whether or not you are directly writing the data from an external system straight to the Resource XOG or whether you are writing that data from a "staging table" to the Resource XOG.

You are currently struggling with the XOG process to write data to your staging table - this seems to me just to be an extra step.

Nowhere have I said (yet!) that you could write data directly to any database tables........  HOWEVER, if your staging tables are just tables on a database (and you are not going to access them at all in Clarity), then you could actually write your data directly to those at database level (since they are "your" tables not "Clarity's" tables). But this still means you have to use a XOG process to get the data from your staging table to the Resource object.

--

I'll try and rephrase my point (sorry if I am labouring this a bit);

Your current plan seems to me to be;

1.Nightly process extracts data from Mulesoft  ; this calls XOG to write that data into a Clarity-owned staging area
2.Another (Clarity owned?) process will then need to run to read the data from the staging table and write it via XOG to the Resources object

What I'm saying is that it could be;

1.Nightly process extracts data from Mulesoft  - this calls XOG to write that data to the Resources object

--

(the Resource XOG can handle updates to non-key data - changes to names, email addresses etc - as long as the unique-id of the record (i.e. the resource id) stays the same then XOG would perform an update rather than an insert)

[Mitch Cofman]

David,

 

Thanks for clarifying and you are correct in that this was the plan that we came up, since the recommendation to not write directly to the Resource table.

 

I do want to point out that the issue that I am struggling with is the ability to READ from the staging table.

 

I am able to read/write directly to the resource object.

 

Thanks,

 

 

Mitch Cofman

PPM Consultant – GTRI

[e-mail]

[phone]

 

[Martti Kinnunen]

Sounds like the instance ID is a required tag. I still think no results is due of the filter nor matching.
While wild cards are not supported you can use between
eg.

<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_read.xsd">

              <Header version="8.0" action="read" objectType="customObjectInstance" externalSource="NIKU"/>

                             <CustomObjectInstanceQuery>

                                           <Filter name="objectCode" criteria="EQUALS">gtri_last_name</Filter>

                                           <Filter name="instanceCode" criteria="BETWEEN">B,D</Filter>

                             </CustomObjectInstanceQuery>

</NikuDataBus>

should return instances where the code is Cofman.
You can also use between when the code is numeric.

There should also be a right like GTRI Resource Staging Table - XOG access, which would eliminate the error.

[Mitch Cofman]

I agree with your assessment. I will try your modifications first thing in the morning.

 

In the meantime, I do not see this GTRI Resource Staging Table - XOG access right anywhere. Do you know how I can find this right?\

 

Thanks,

 

Mitch Cofman

PPM Consultant – GTRI

[e-mail]

[phone]

 

[Martti Kinnunen]

If you go to the Administration - Resouces and open the details of your user ID. There are the rights tab and there should be Global right for tha.
Search the rights the user already has for *XOG and second click add and
search again for *XOG

Either one should give include GTRI Resource Staging Table - XOG access

[Mitch Cofman]

Martti – Update, I found the XOG – Access right. It is a Global right. So I have this right assigned to me. So now I'm not sure what is causing the error.

 

Mitch Cofman

PPM Consultant – GTRI

[e-mail]

[phone]

 

[David Morton]

"I do want to point out that the issue that I am struggling with is the ability to READ from the staging table." 

To READ data from tables (staging or otherwise) - you can just do that with SQL, no need to use XOG.

(The only reason people suggest to "XOG out data" when trying to resolve problems with "XOG-ing in data" is to help understand what the XML should "look like" - since the XML format that you get when you XOG out the data is exactly what is needed to XOG-in the data.)

[Mitch Cofman]

All,

I wanted to let everyone know this issue has been resolved. The issue that I was having was that I was not using the correct objectID, when trying to perform the XOG's. Martti was correct in her assessment of what the issue was.

Here is the resolution, when you create an object, it assigns an ID; called ID, that I was not aware of. When I used this ID in my ObjectCode statement everything worked fine. Thanks Karl from TechSupport who assisted me in resolving this issue.