Ove​rview

Since VSI began to sup­port mul­ti­ple SIF ver­sions with its prod­ucts, we have had an issue with prop­er­ly man­ag­ing the many dif­fer­ent sets of data­base def­i­n­i­tions that have been required by each of the dif­fer­ent SIF ver­sions. As of the writ­ing of this arti­cle, there are 22 dis­tinct ver­sions of the SIF spec­i­fi­ca­tion that have been pub­lished across the three regions of the world.

VSICentralServ​ice — Public Information

The VSI­Cen­tralSer­vice web ser­vice is an oDa­ta web ser­vice that returns use­ful infor­ma­tion about the SIF spec­i­fi­ca­tion in elec­tron­ic form that can be con­sumed through pro­gram­ming libraries, data­bas­es or by appli­ca­tions such as Excel, Pow­er­Piv­ot or CMS sys­tems. Each record is time-stamped so that agents and appli­ca­tions can be kept up-to-date with­out need­ing to con­tin­u­al­ly reload the entire database.

This data­base con­tains some infor­ma­tion already avail­able in elec­tron­ic form but most­ly con­tains that which is main­tained by Visu­al Soft­ware staff. This is because much of the infor­ma­tion pub­lished in the spec­i­fi­ca­tion is not pub­lished in tab­u­lar form — it is either pub­lished as XSD files, PDFs or Excel files meant for peo­ple to read and are not eas­i­ly con­vert­ible for use by soft­ware applications.

We have found that when­ev­er we make assump­tions in our envi­ron­ment that any­thing is "fixed" in nature, it changes. Because of this dynam­ic nature, we have cre­at­ed sev­er­al dif­fer­ent ser­vices, one each pro­vid­ing infor­ma­tion about:

• The SIF Spec­i­fi­ca­tion — Public 
  • Coun­tries (regions)
  • Local­i­ties — places that define local extensions
  • Local­i­ty Ref­er­ences — spec­i­fi­ca­tions man­aged at a locality
  • Spec­i­fi­ca­tion ver­sions (com­bi­na­tions of data mod­el and infra­struc­ture versions)
  • SIF Objects
  • SIF Enti­ties — these are all the ele­ments and attrib­ut­es defined in the specification
  • SIF Code Values
  • SIF Extend­ed Elements

The VSI­Cen­tral data­base con­tains infor­ma­tion about all ver­sions of the SIF spec­i­fi­ca­tion (as of Jan 2014, there are 16 ver­sions in the data­base and over a mil­lion dif­fer­ent code val­ues). In order to pre­vent a sin­gle con­sumer from monop­o­liz­ing all the resources of the sin­gle shared serv­er, the web ser­vice itself lim­its return val­ues from queries to 20,000 items.

So, when mak­ing requests from this web ser­vice, it is rec­om­mend­ed that you fil­ter your requests to lim­it the infor­ma­tion to only that which you need. This can be done when call­ing the web ser­vice using the oDa­ta URL con­ven­tions defined in oDa­ta URI Con­ven­tions. Good exam­ples can also be found at: Fil­ter Exam­ples.

SIF Code​Values

This repos­i­to­ry con­tains the stan­dard SIF code val­ues as well as those local­ly defined by states and oth­er localities.

SIF Code Values Query — Example

In the fol­low­ing exam­ple, we use the fol­low­ing URL to call the web ser­vice, ask­ing it to return infor­ma­tion about code val­ues used in the SIF Object 'Stu­dentSec­tio­nEn­roll­ment' for the Data­Mod­elVer­sion '2.0R1'. Since only the US has a data mod­el with that ver­sion num­ber, no oth­er coun­tries will rep­re­sent­ed in the returned infor­ma­tion. The URL will be:

http://www.sifdepot.com/VSICentralService.svc/sifCodeValues?$filter=DataModelVersion eq '2.0r1' and Object­Name eq 'Stu­dentSec­tio­nEn­roll­ment'

Once exe­cut­ed, the returned infor­ma­tion will look like this in a brows­er window:

(note: some browsers do not (or no longer) dis­play XML mes­sages prop­er­ly or need a plug-in before they are able to dis­play for­mat­ted XML.

Information Stored for Each Co​de Value

The fol­low­ing is the def­i­n­i­tion of the table in the sup­port­ing data­base that con­tains the infor­ma­tion returned about each returned code value.

Key	Name	Data Type	Max Length (Bytes)	Allow Nulls	Iden­ti­ty	Default
	Recor­dID Unique iden­ti­fi­er in the code val­ues table	int	4	False	1 — 1
(4)	Coun­tryCode Data Mod­el Coun­try — US, UK, AU or GLOBAL	varchar(10)	10	True
(4)	Data­Mod­elVer­sion Data Mod­el Version	varchar(10)	10	True
(4)	Infra­struc­ture­Ver­sion Infra­struc­ture that sup­ports this data mod­el for this coun­try (use­ful for queries)	varchar(10)	10	True
(3)	Local­i­ty­Name If exten­sion, the name of the local­i­ty main­tain­ing this code value	varchar(50)	50	True
	Local­i­tyRef­er­ence If this is a local code val­ue, this refers to the local spec­i­fi­ca­tion that pro­vides more detail for this code	varchar(75)	75	True
(2)	Object­Name SIF object relat­ed to the element/attribute that uses this code value	varchar(255)	255	True
	Item­Type E=Element, A=Attribute	varchar(5)	5	True
	Item­Name Base ele­ment name asso­ci­at­ed with this code val­ue (even if the code val­ue is direct­ly asso­ci­at­ed with an attribute) — this is use­ful for searching	varchar(75)	75	True
	SIFI­den­ti­fi­er - this is a hexa­dec­i­mal val­ue assigned to this code val­ue to make these val­ues com­pat­i­ble with the SIF 3.* specifications.	varchar(50)	50	True
	XPATH XPATH of item asso­ci­at­ed with this code val­ue (ele­ment or attribute)	varchar(255)	255	True
	Code­Val­ue The code val­ue as defined in the spec­i­fi­ca­tion (orig­i­nat­ed by an out­side author­i­ty such as the NCES)	varchar(255)	255	True
	Code­Mean­ing Descrip­tion — use­ful in appli­ca­tions for lookup tables	varchar(max)	max	True
	Cod­eDescrip­tion	varchar(max)	max	True
	Date­Cre­at­ed Date this entry was first created	date­time	8	True		(get­date())
	Las­tUp­dat­ed Date this entry was last edited	date­time	8	True		(get­date())
	IsDep­re­cat­ed This code val­ue will be removed in a future release of the SIF specification	bit	1	True		((0))
	Las­tAc­tion This may either be 'Add" or 'Change"	varchar(10)	10	True		('Add')
	Cre­at­ed­By Name of the account that cre­at­ed this code val­ue table entry	varchar(255)	255	True		('admin')
	Main­tained­By Name of the account that has author­i­ty to main­tain this table entry — this has sig­nif­i­cance when the code val­ue is local­ly main­tained; it will cor­re­spond to an account name of the local­i­ty-assigned user respon­si­ble for main­tain­ing these entries	varchar(255)	255	True

The XML returned by the web ser­vice has a 1–1 cor­re­spon­dence to the val­ues in this table.

SIF Ent​ities — Obje​ct Detail

This method returns infor­ma­tion from the SIF spec­i­fi­ca­tion. We have found it help­ful for pop­u­lat­ing drop­down box­es and keep­ing oth­er parts of the SIF agent up to date.

SIF Entities Query​— Example

In the fol­low­ing exam­ple, we use the fol­low­ing URL to call the web ser­vice, ask­ing it to return infor­ma­tion about the SIF Object 'Stu­dentSec­tio­nEn­roll­ment' for the Data­Mod­elVer­sion '2.0R1'. Since only the US has a data mod­el with that ver­sion num­ber, no oth­er coun­tries will rep­re­sent­ed in the returned infor­ma­tion. The URL will be:

http://www.sifdepot.com/VSICentralService.svc/sifEn­ti­ties?$filter=DataModelVersion eq'2.0r1' and Object­Nameeq'Stu­dentSec­tio­nEn­roll­ment'

Once exe­cut­ed, the returned infor­ma­tion will look like this in a brows­er window:

(note: some browsers do not (or no longer) dis­play XML mes­sages prop­er­ly or need a plug-in before they are able to dis­play for­mat­ted XML.

Countries (re​gions)

This table con­tains infor­ma­tion about the regions (coun­tries) for which infor­ma­tion is main­tained by this ser­vice. This method should be called first and this infor­ma­tion should be used to fil­ter oth­er calls to reduce the amount of infor­ma­tion returned.

The fol­low­ing table defines the val­ues returned by this web ser­vice method:

SIF Coun​tries​— Example

SIF Versions (​combinatio​ns of data model and infrastructure versions)

This method returns infor­ma­tion about the com­bi­na­tions of Coun­try /Infrastructure / Data Mod­el ver­sions for which infor­ma­tion is avail­able from this ser­vice. This method should be called first and this infor­ma­tion should be used to fil­ter oth­er calls to reduce the amount of infor­ma­tion returned.

The fol­low­ing table defines the val­ues returned by this web ser­vice method:

SIF Versions​— Example

Localities — places th​at​define local extensions

This method returns infor­ma­tion about the local­i­ties about which Extend­ed Ele­ments and Code Val­ues are main­tained. This method should be called first and this infor­ma­tion should be used to fil­ter oth­er calls to reduce the amount of infor­ma­tion returned.

The fol­low­ing table defines the val­ues returned by this web ser­vice method:

SIF Localities — Example

Locality References — s​pe​cifications managed at a locality

This method returns detailed infor­ma­tion about the spec­i­fi­ca­tions referred to in the Local­i­ty Definitions.

The fol­low­ing table defines the val­ues returned by this web ser­vice method:

SIF Locality References​—​Exam​ple

SI​F Objects

This method returns infor­ma­tion about the objects that are avail­able with each ver­sion of the SIF spec­i­fi­ca­tion. Remem­ber to apply fil­ters for the Coun­tryCode and spec­i­fi­ca­tion ver­sions when call­ing this method.

The fol­low­ing table defines the val­ues returned by this web ser­vice method:

SIF​Objects​— Example

SIF Extended Elements

Call­ing this method will return infor­ma­tion about Extend­ed Ele­ments reg­is­tered with Visu­al Soft­ware for the Local­i­ty specified.

The fol­low­ing table defines the val­ues returned by this web ser­vice method:

SIF Extended Elements — Example

vsiCentralService — oData Information Access

The for­mat of the XML returned by this web ser­vice is defined by the oDa­ta spec­i­fi­ca­tion. Accord­ing to the www.odata.org web site, the Open Data Pro­to­col is defined as follows:

ODa­ta is a stan­dard­ized pro­to­col for cre­at­ing and con­sum­ing data APIs. ODa­ta builds on core pro­to­cols like HTTP and com­mon­ly accept­ed method­olo­gies like REST. The result is a uni­form way to expose full-fea­tured data APIs.

By mak­ing use of the oDa­ta out­put for­mat, the out­put of this web ser­vice can be used as input in many ways.

• There are many freely avail­able libraries avail­able that accept oDa­ta input sources, including 
  • .NET
  • Javascript
  • Java
  • PHP
  • Ruby
  • C++
  • Sil­verlight
  • Telerik envi­ron­ments

• There are CMS and oth­er appli­ca­tions that accept oDa­ta input, including: 
  • Joom­la
  • Dru­pal
  • Web­Sphere
  • Pow­er­Piv­ot, Excel
  • Dynam­ics
  • SAP
  • Webn­odes
  • eBay
  • Nuget
• Data­bas­es
  • SQL Serv­er
  • MySQL
  • Azure, oth­ers

As con­fir­ma­tion that this ser­vice has passed all the require­ments for an oDa­ta end­point, we ran the code ser­vice through the oDa­ta val­i­da­tion tool (the clos­est thing they have to certification):

..and it passed all the valid­i­ty tests.

Consuming Web Service​Data

Through the ODa­ta inter­face, the data from the VS SIF Infor­ma­tion Web Ser­vice may be con­sumed by a host of oth­er inter­faces. For an up to date list of providers, con­sumers, live ser­vices and pro­gram­ming libraries that sup­port ODa­ta (and, hence this web ser­vice), see: ODa­ta Ecosystem

Importing​Into Excel

As an exam­ple, this series of screen shots illus­trate how this web ser­vice data may be import­ed into an Excel spread­sheet. First, on Excel's "Data" tab, choose to import data from the web. This fea­ture has been avail­able (at least) from Office 2010. (Click image to see full view).

Enter the URL of the web ser­vice, includ­ing fil­ters into the "Address" input box and click the "Go" but­ton. If the URL you spec­i­fied will return many rows of data, expect to wait a few minutes.

Excel will dis­play the out­put of the web ser­vice in its XML form in the win­dow. Click the "right arrow" but­ton shown here in this pic­ture to select the entire contents.

When the XML has been select­ed, Press the 'Import' but­ton. Depend­ing on how much data has been loaded from the web ser­vice, this may take a few minutes.

This screen will ask you if you want to add this data to the cur­rent work­sheet or to a new worksheet.

This is a typ­i­cal error — since this web ser­vice will always out­put some numer­ic columns and since Excel will always trans­late them to text columns, this is warn­ing you that it is doing so. If you want to use them as numer­ic columns in Excel, you will need to man­u­al­ly change them back. Your data has been import­ed. Notice that ALL of the web ser­vice out­put has been import­ed, includ­ing some columns that you will not need in Excel. If you are going to use this same ODa­ta out­put for input to pro­gram­ming con­trols, how­ev­er, they will need this infor­ma­tion, so we need to dis­play it. If you don't want to see it, man­u­al­ly remove the columns you don't need.