WebCOBRA.com Community Message Boards

jwolgamott
Posts: 257
Since: 09/26/2005

With the Spring 2008 release of WebCOBRA, we introduced a feature that should go above and beyond what you've been asking for in the WebAPI.

The Relational API is a full and complete copy of your WebCOBRA.com database.

We've optimized it to reduce size, and ported it to Access (We use SQL Server), but its all fields.

You can download it through the WebCOBRA IT Resources; you'll receive an email with the link within a couple of hours.

Some Terms and Conditions for this feature:
1) It's not as easy to use as the WebAPI. This is a relational database, so your data is spread across multiple fields
2) You can download a copy once per week
3) All support for the Relational API will occur here, on the DevCenter.
4) Letters and other binary images are not downloaded

We are extremely excited about this new feature -- it's entirely unique in our space, and will give you access to every piece of data you could need.

blambrecht
Posts: 65
Since: 02/05/2008

We are very pleased to see this new feature in place. One issue that our company will likely face as we migrate more clients to WebCobra is the 2GB size limit for Access database files.

Ideally, one way to resolve this would be if it were possible that the Access file would only contain records that were added and updated since the previous download.

Most of the tables have what would appear to be audit columns of inserts and edits. If that assumption is correct, would that be a feasible accommodation that could be made in the near future?

Bill Lambrecht
WageWorks, Inc

jwolgamott
Posts: 257
Since: 09/26/2005

Bill --

We took the 2GB limit on Access files into account when we decided on what datastore to use; Access was chosen because of the network effect ("everybody" has Access)

We also wanted to have a path to go down when we reach the 2GB limit. The techniques we're using to build and populate the Access Database could be ported over and create a SQL Server 2005 Express database file. We'd get up to 4 GB there. And we could automate a full SQL Server instance when we get above that.

Based on our calculations, we'll be able to store 196,000 QBs in a 2GB Access File.

We can do this because the Access file has no indexes or binary data (PDF storage).

Other techniques we can use if/when we approach the 400,000 QB barrier (in a 4 GB Express file) is to shard the database into several instances.

So, in conclusion, I think you're right to consider the 2GB a limitation, but it is in the plans to be able to handle it in the next 2-5 years.

blambrecht
Posts: 65
Since: 02/05/2008

Hi Jesse,

The steps you have outlined - using various versions of SQL Server .mdf files - would definitely work for our company as an solution to the 2GB Access limit.

In the future would you be willing to consider utilizing compressed SQL Server backup files such as with a tool like LiteSpeed to control file sizes and download times?

Thanks for your feedback,
Bill

mhowell
Posts: 7
Since: 09/24/2007

We requested a copy of our database on 3/2/08 but never received the email with the link to download. Is there a way to request this again, instead of going through the 7 day waiting period?

jwolgamott
Posts: 257
Since: 09/26/2005

Sure! I just need to know your Administrator Code and I can track it down for you.

mhowell
Posts: 7
Since: 09/24/2007

Administrator code is HRX

Thanks...

jwolgamott
Posts: 257
Since: 09/26/2005

I had the system regenerate a new copy of the Relational API and it shows it emailed you earlier today.

If you didn't receive it, please email spt@travisoft.com -- and paste in this URL, and we'll email you out the link you need to download it.

mhowell
Posts: 7
Since: 09/24/2007

Got it, thanks!

cgomez
Posts: 18
Since: 02/22/2008

Where is the documentation to use this Relational API? We are currently using the Simple Query to pull the information on a daily basis. We are looking for a automation solution. We cannot have a manually process for someone to run the WebAPI on a daily basis. Do you have any examples to the Relational API, so that we know how to use it?

jwolgamott
Posts: 257
Since: 09/26/2005

The WebAPI could be documented without much work -- the code is all there, so you could automate the queries, and even the loading of a Database.

That could either be a Windows Service, or a console application that you use a Windows Scheduled Task to download the information you need.

Regarding the documentation on the Relational API -- if you go into the IT Resources section, you'll see information on how to use the Relational API.

If you have specific questions on tables and queries, please open up a new thread in the Relational API forum (http://devcenter.webcobra.com/foru ms/5.aspx)

blambrecht
Posts: 65
Since: 02/05/2008

Jesse,

I requested a copy yesterday but did not get the e-mail notice that it was ready. I e-mailed the support group and was told to use the message board to to request the link be resent.

Thank you,
Bill Lambrecht - WageWorks, Inc
bill.lambrecht@wageworks.com

jwolgamott
Posts: 257
Since: 09/26/2005

Bill -- I'm having the system regenerate the RelationalAPI -- it's just picked up the job now, so it should email out to you within an hour.

blambrecht
Posts: 65
Since: 02/05/2008

Are there ever times where records may be physically deleted from tables as opposed to just having the [DELETED] column updated?

Just wondering so that we can make sure our processes are accounting for that.

Thanks!
Bill Lambrecht

jwolgamott
Posts: 257
Since: 09/26/2005

Bill --

Yes, records can and will be deleted; in fact, the [DELETED] column has been deprecated in our codebase, but still exists for legacy queries on our end.

It can be safely ignored in all cases.

blambrecht
Posts: 65
Since: 02/05/2008

I requested a download from our test environment yesterday morning but did not receive the e-mail notification that it was ready.

My e-mail address is bill.lambrecht@wageworks.com

Thanks,
Bill Lambrecht

jwolgamott
Posts: 257
Since: 09/26/2005

Bill --

Thanks for letting us know -- I resubmmitted the request to the queue and it's being processed right now. It will email you when its complete; probably 1-2 hours from now.

blambrecht
Posts: 65
Since: 02/05/2008

Can someone please check on the status of a request for a Relational API refresh I entered around 9:15 AM CST and have not recevied the download link as of 2:00 CST.

Thank you,
Bill Lambrecht
bill.lambrecht@wageworks.com

jwolgamott
Posts: 257
Since: 09/26/2005

Bill -- We'll check into why it wasn't picked up until just now, but if you check the status you'll see its now PickedUp

Since you requested it from the main non-test account of yours, it'll probably be fairly quick to get to you.

blambrecht
Posts: 65
Since: 02/05/2008

Hi,
I need to locate the contact name for customers for a report. I see the field DesignatedGroupContact in QBGroups, but when I look in the Contacts and Users table the ID values do not match up at all.

Is there a different table that links DesignatedGroupContact to an account rep?

Thank you,
Bill Lambrecht

jwolgamott
Posts: 257
Since: 09/26/2005

Bill --

The field QBGroups.DesignatedGroupContact is the QB (QB.QBAutoID) in the Group who is the billing contact; usually the former employee who lost coverage.

I think you might be wanting the name of the Client/Employer personnel. This would be the name of the Contact when you view the Client/Employer detail screen.

To get this, you want to get the Contact ID from the contact table of the Client/Employer.

Here's a sample query to do so:
select * from companies
inner join SystemInformationLinkTable on SystemInformationLinkTable.ModuleId ent = companies.companyid and SystemInformationLinkTable.ModuleTy peID = 1 and SystemInformationLinkTable.InformationTypeID = 2
inner join Contacts on Contacts.ContactID = SystemInformationLinkTable.TableLinkIdent

Our conversation on http://devcenter.webcobra.com/conversations/63.aspx explains the magic constants used.

This will return all contacts, not just the top 1. Also, this does not return their contact information, but a seperate query could return that.

blambrecht
Posts: 65
Since: 02/05/2008

Greetings,

Are the ChangedOn columns in the tables updated with every update to the row automatically such as via a GETDATE() in the stored proc or a trigger on the tables themselves?

For example, we are looking to generate a report for when an address changes and was hoping that we could use the ChangedOn colummn in the Addresses table for the indicator. That would be the most straight forward approach.

If not, is there another way within the database to infer the change of address such as a letter being sent out or other event/activity captured within the database?

Thanks,
Bill Lambrecht

jwolgamott
Posts: 257
Since: 09/26/2005

Bill --

The ChangedOn and CreatedOn are handled by our Stored Procedures, not necessarily the triggers.

You can trust that address changes are being tracked in by the ChangedOn table though; those are being properly tracked in the table.

If its important to tell _what_ changed, in addition to the fact that the entire record changes. The following query will show all address edits for QB addresses:

select * from qbhistorydetail
inner join QB on QB.QBAutoID = qbhistorydetail.QBAutoID
where
qbhistorydetail.PKField = 'AddressID'
and Action = 2 -- ActionID2 is Edit

blambrecht
Posts: 65
Since: 02/05/2008

Hi,

I want to capture when a QB becomes enrolled. Currently I am derriving this from the existence of a row in the [QBDateTracking] table where DateTypeID = 22 (Enrolled),
and then using the QBGroupID to find the other information needed.

This leads me to 2 questions:

1) Is DateTypeID value 22 QBStatus of Enrolled or Enrolled with 100% Subsidy or does is this a broader range of Enrolled status?

2) Is there an alternative method for identifying when a QB becomes enrolled?

Thank you,
Bill Lambrecht

jwolgamott
Posts: 257
Since: 09/26/2005

Bill --

That's exactly the right approach to capture the date the QB becomes enrolled (which has no relation to their first day of being covered under the health plan and being enrolled).

For the 100% subsidy, that's a harder question. The most common way of entering a 100% subsidy is having them become 100% subsidized on the completion of the QB Add Wizard. This method will not leave an entry in the QB Date Tracking.

For these participants, your best bet is to get their Created_On date in the QBGroups table. Or, you can define their enrollment date as either their EventDate or their first day after loss of coverage. As a final alternative, you can choose their "Enrollment Window Begin" date, which is in the QB Date Tracking.

blambrecht
Posts: 65
Since: 02/05/2008

Hello,

for records that have a CreatedBy value of -1, is that used only for "backoffice" processes like migration from Desktop, or could that also be used for records that were uploaded via an electronic file?

The reason for asking - if I want to get records (like Payments) that were made within a specific date range using the CreatedOn value results in a lot of older payments returning on the report if those CreatedOn dates fall within the date range and those records were added as part of a migration.

So is it safe to assume that the CreatedBy = -1 is only used for migrations or could other processes use -1 as well?

Thanks,
Bill Lambrecht

jwolgamott
Posts: 257
Since: 09/26/2005

Bill --

I think your query may have a misplaced comma in it -- The CreatedOn values are all date stamps, and the createdBy field can be a -1 (for migrations)

You can use the CreatedOn field for your tables; it will be the date the row was inserted into the table. So for your data conversions, they will have nearly the same data; all future payments entered will have their creation date.

blambrecht
Posts: 65
Since: 02/05/2008

Jesse,

Sorry, my question was not very clear. Can you clarify - is the CreatedBy value of -1 only used for migrations? Or could other processes like an electronic file upload also result in a CreatedBy value of -1?

Thanks,
Bill

jwolgamott
Posts: 257
Since: 09/26/2005

Bill --

Sorry, I missed the point of your question and went off on a tangent.

I just checked a system integrator payment upload, and the CreatedBy gets set to be -1 during that process.

tslusher
Posts: 5
Since: 05/15/2008

I'm having the same problem that Bill and mhowell had: I never get the email with the link to the Relational API when it is processed. Administrator code is CBIZ. Thanks!

jwolgamott
Posts: 257
Since: 09/26/2005

tslusher --

I ran through the CBIZ relational download again and you should be receiving the email shortly (within the next hour).

tslusher
Posts: 5
Since: 05/15/2008

Got the email, thanks!

evannielsen
Posts: 11
Since: 08/10/2008

Is there a way to get the documents that have been created by webCOBRA out via this API or some other programatic device?

sszelei
Posts: 53
Since: 09/02/2009

You must access the documents though the WebCOBRA interface.

Regs,

Steven