Setting up the development environment

  1. Download the code
  2. Follow the instructions below to get all the DLLs Open Data Publisher needs
  3. Build the solution with Visual Studio 2010.

For debug or deploy do the following:
  1. Publish the data service Services_WebRole on an IIS7 virtual directory.
  2. Publish the Interactive Sdk.Mvc_WebRole on an IIS7 root directory.
The two web roles need not be published on the same IIS7 server.

Projects actually used by Open Data Publisher

For the time being all of the original OGDI code is still in the solution until we decide what to do with it.

The projects, that are used by the Open Data Publisher are:
  • Services_WebRole
  • Interactive Sdk_Mvc
  • Interactive Sdk.Mvc_WebRole
  • InteractiveSdk.WorkerRole
  • StorageInterface
  • WindowsAzureStorage
  • Ogdi.Azure
  • OgdiConfig

Code Library Prerequisites

Please make sure to review and agree with component licensing terms.

File: Newtonsoft.Json.dll
Download: http://json.codeplex.com/releases/view/25040
Solution File Folder: %OGDI_ROOT%\Shared\

File: AjaxControlToolkit.dll
Download: http://ajaxcontroltoolkit.codeplex.com/releases/view/27326
Solution File Folder: %OGDI_ROOT%\Shared\

File: CSharpFormat.dll
Download: http://www.manoli.net/csharpformat/CSharpFormat.zip
Solution File Folder: %OGDI_ROOT%\Shared\
Notes: Requires Build

File: CssFriendly.dll
Download: http://cssfriendly.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=2159
Solution File Folder: %OGDI_ROOT%\Shared\

File: System.Threading.dll
Download: http://go.microsoft.com/fwlink/?LinkId=182996 (old URL)
Download: http://www.microsoft.com/en-us/download/details.aspx?id=24940
Solution File Folder: %OGDI_ROOT%\Shared\
Notes: After installing the package the DLL can be found in the %INSTALL ROOT%\Reactive Extensions\v1.0.2563.0\Net35folder. Default installation root is C:\Program Files\Microsoft Cloud Programmability.

File: System.Web.DataVisualization and System.Web.DataVisualizationDesign
Download: http://www.microsoft.com/downloads/details.aspx?FamilyId=130F7986-BF49-4FE5-9CA8-910AE6EA442C&displaylang=en
Solution File Folder: Referenced from default install location
Notes: Install the package

Component: ASP.NET MVC 1.0
Download: http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=53289097-73ce-43bf-b6a6-35e00103cb4b
Solution File Folder: Referenced from default install location
Notes: Install the package

Component: jquery.clipboard.min.js and jquery.clipboard.swf
Download: http://bradleysepos.com/projects/jquery/clipboard/release/jquery.clipboard-2.0.1.zip
Solution File Folder: %OGDIROOT%\Interactive SdkMvc\Interactive Sdk.Mvc_WebRole\Scripts

Component: WPF Toolkit
Download: http://wpf.codeplex.com/
Solution File Folder: Referenced from default install location
Notes: Install the package

Component: Fluent Ribbon Control Suite
Download: http://fluent.codeplex.com/
Solution File Folder: %OGDI_ROOT%\Shared\
Notes: Follow the instruction to download Fluent.dll

Component: LumenWorks.Framework.IO.dll
Download: http://www.codeproject.com/KB/database/CsvReader.aspx
Solution File Folder: %OGDI_ROOT%\Shared\
Notes: Follow the instruction to download assembly

Setting up the data service

Data service uses SQL server (2005 and higher) for storing metadata.

Data service is used both as a data feed and metadata feed for the Interactive SDK Mvc.

The following should be set in the 'Data Services\Services_WebRole\Web.config' actual contents depending on actual servers used.
  <appSettings>
    <add key="dataserviceconnectionstring" value="Data Source=localhost\SQLEXPRESS2008;Initial Catalog=odp;Integrated 
Security=SSPI;MultipleActiveResultSets=True" />
    <add key="appmetadataconnectionstring" value="Data Source=localhost\SQLEXPRESS2008;Initial Catalog=odp;Integrated 
Security=SSPI;MultipleActiveResultSets=True" />
  </appSettings>


The key "dataserviceconnectionstring" defines connection to data service metadata database.
The key "appmetadataconnectionstring" defines connection to interactive SDK Mvc metadata database.
In the given example both connections are the same but they don't need to be and they even don't need to be defined both if the Interactive SDK Mvc metadata feed is configured elsewhere.

The option 'MultipleActiveResultSets=True' should always be used.

Metadata

Metadata is read from one meta table. A record in the meta table publishes an SQL server table as entity set available through the data service.

The meta table can be created with the following SQL statements:
USE odp
GO
CREATE TABLE odpdataservicemetadata
(
id INT IDENTITY(1,1) CONSTRAINT pk_odpdataservicemetadata_id PRIMARY KEY,
EntitySetName VARCHAR(50) NOT NULL,
TableName VARCHAR(50) NOT NULL,
RowId VARCHAR(50) NOT NULL,
Longitude VARCHAR(50),
Latitude VARCHAR(50),
KmlSnippet VARCHAR(50),
KmlName VARCHAR(50),
KmlDescription VARCHAR(20)
)
GO


The meaning of the columns in the odpdataservicemetadata table are:
  • EntitySetName - The name of the entity set as published through the OData feed.
  • TableName - The name of the SQL server table published as OData entity set.
  • RowId - Column in the table used for uniquely identifying each row of data. This must be specified for data paging to work. It is best if the RowId column is also indexed.
  • Longitude - Column in the table used to store longitude part of POINT KML representation.
  • Latitude - Column in the table used to store latitude part of POINT KML representation.
  • KmlColumn - Column in the table used to store a pregenerated KML snippet (e.g. 'kml_geometry').
  • KmlName - Column in the table used to store a name of the KML place mark.
  • KmlDescription - Column in the table used to store a description of the KML place mark.
Longitude and Latitude must both be defined for the KML feed to work with POINT placeholders. KmlColumn is mutualy exclusive with Longitude and Latitude.
The colums defined by Longitude and Latitude are not fed through OData feed and $metadata query.

Setting up the Interactive SDK Mvc

The Interactive SDK Mvc stores entity set metadata in SQL server (2005 and higher).

The Interactive SDK Mvc actually reads metadata from the data service.
The Interactive SDK Mvc reads data from another or the same instance of data service (or some other OData feed).

URI of data feed is part of each entity set's metadata.

The following should be set in the 'Interactive SdkMvc\Interactive Sdk.MvcWebRole\App_Data\WebConfigXML\appSettings.xml' actual contents depending on actual servers used.
<appSettings>
  <add key="serviceUri" value="http://odp.sl-king.com/dataservice/interactive/" />

It is recommended that "serviceUri" is kept private and not accessible to the internet because no security is used for updating the analytic info (see below).
This means that to keep the OData part of the data service accessible to the internet the data service should also be configured again separately in an IIS7 virtual directory accessible to the internet.

Metadata

Metadata is read from three meta tables. A record in the main table publishes an an entity set on the Interactive SDK Mvc site.

The metadata is actually read and fed to the Interactive SDK Mvc by the data service.

The meta table can be created with the following SQL statements:
USE odp
GO

CREATE TABLE odpdatasources
(
id INT IDENTITY(1,1) CONSTRAINT pk_datasource_id PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Description VARCHAR(50),
Disclaimer VARCHAR(1000),
)
GO

CREATE TABLE odpcategories
(
id INT IDENTITY(1,1) CONSTRAINT pk_category_id PRIMARY KEY,
Name VARCHAR(50) NOT NULL
)
GO

CREATE TABLE odpappmetadata
(
id INT IDENTITY(1,1) CONSTRAINT pk_odpappmetadata_id PRIMARY KEY,
EntityId UNIQUEIDENTIFIER DEFAULT NewID(),
category_id INT FOREIGN KEY REFERENCES odpcategories(id) DEFAULT 1,
datasource_id INT FOREIGN KEY REFERENCES odpdatasources(id) DEFAULT 1,
EntitySet VARCHAR(50) NOT NULL,
DatasetName VARCHAR(50) NOT NULL,
TechnicalInformation VARCHAR(50),
CollectionInstruments VARCHAR(50),
DataDictionaryVariables VARCHAR(50),
AdditionalInfo VARCHAR(50),
Description VARCHAR(50),
Keywords VARCHAR(50),
Links VARCHAR(50),
PeriodCovered VARCHAR(50),
GeographicCoverage VARCHAR(50),
CollectionMode VARCHAR(50),
UpdateFrequency VARCHAR(50),
LastUpdateDate DATETIME DEFAULT GETDATE(),
ReleasedDate DATETIME DEFAULT GETDATE(),
ExpiredDate DATETIME,
MetadataUrl VARCHAR(50),
EntityKind VARCHAR(50),
DownloadLink VARCHAR(1000),
DataSvcLink VARCHAR(200),
DataSvcKmlLink VARCHAR(200),
DataSvcKmlType VARCHAR(50)
);
GO


Lengths of string columns should be chosen so they can hold all intended string data.

The most important columns in the odpappmetadata table are:
  • category_id - References an entity set's category in the odpcategories table.
  • datasource_id - References an entity set's data source in the odpdatasources table.
  • EntitySet - Short name of an entity set, should be the same as in used in the odpdataservicemetadata meta table for data service.
  • DatasetName - Human readable short name of an entity set..
  • Description - Description of an entity set.
  • DataSvcLink - URI of the OData data service for this entity set (e.g. 'http://localhost/dataservice/v1/sql/cable/').
  • DataSvcKmlLink - URI of the KML data service for this entity set (e.g. 'http://localhost/dataservice/v1/sql/cable/?format=kml').
  • DataSvcKmlType - Short description of a type of KML data service intended for support of KML filters. At the moment only 'odp' (Open Data Publisher) is supported.

Analytic info

Analytic info (page viewing statistics, user ratings, etc.) is read from one tables. One record in this table corresponds to one published entity set in the interactive application. The entries are inserted and updated automatically by the interactive app.

The analytic info table can be created with the following SQL statements:
USE odp
GO

CREATE TABLE odpanalyticinfo
(
id INT IDENTITY(1,1) CONSTRAINT pk_analyticinfo_id PRIMARY KEY,
entityset_id INT FOREIGN KEY REFERENCES odpappmetadata(id) NOT NULL,
views_total INT DEFAULT 0,
views_today INT DEFAULT 0,
views_average INT DEFAULT 0,
last_viewed DATETIME DEFAULT GETDATE(),
positive_votes INT DEFAULT 0,
negative_votes INT DEFAULT 0
);
GO


External Downloads

A major feature added after the fork from OGDI was the ability to add multiple external downloads to each dataset. They are listed under the dataset information on the details page. The entityset_id is the identifier for the dataset the download is attached to.

Here is the SQL to create the external downloads table
CREATE TABLE [dbo].[odpdownloads]
(
	[id] [int] IDENTITY(1,1) NOT NULL,
	[entityset_id] [uniqueidentifier] NULL,
	[Name] [varchar](50) NULL,
	[Description] [varchar](max) NULL,
	[Link] [varchar](150) NULL,
	[IconLink] [varchar](100) NULL,
	[FileType] [varchar](50) NULL,
	[AdditionalInfoLink] [varchar](100) NULL
);
GO

Download Analytics

Each external download link clicked is counted and displayed by the download.

Create the external download analytics table with the following SQL
CREATE TABLE odpanalyticinfodownloads
(
	[id] [int] IDENTITY(1,1) NOT NULL,
	[entityset_id] [nvarchar](50) NOT NULL,
	[download_id] [int] NOT NULL,
	[download_date] [datetime] NOT NULL,
	[ip] [varchar](50) NULL
)

GO

Download Analytics Aggregation

The download analytics uses a view to aggregate the analytics.

It can be created with the following SQL
CREATE VIEW odpanalyticinfodownloads_agg
AS
SELECT     entityset_id, download_id, COUNT(*) AS download_count
FROM         dbo.odpanalyticinfodownloads
GROUP BY download_id, entityset_id

GO

Recaptcha enabled user rating

In Interactive SdkMvc\Interactive Sdk.MvcWebRole\App_Data\WebConfigXML\appSettings.xml insert the keys you get after setting up your Recaptcha account at http://www.google.com/recaptcha/whyrecaptcha:
  <add key="RecaptchaPrivateKey" value="" />
  <add key="RecaptchaPupblicKey" value="" />

Disabled OGDI functionalities

Comments

In Interactive SdkMvc\Interactive Sdk.MvcWebRole\Views\DataBrowser\Index.aspx see
  1. <div class="tab tc2 t2" title="Comments">
  2. <% Html.RenderPartial("Comments")

Announcements and learn about OGDI on home page

In Interactive SdkMvc\Interactive Sdk.MvcWebRole\Views\Home\Index.aspx see
  1. <%if(false){ %>

AddThis widget

In Interactive SdkMvc\Interactive Sdk.MvcWebRole\Views\Shared\Bookmark.ascx see
  1. <%if(false){ %>

User data request

In Interactive SdkMvc\Interactive Sdk.MvcWebRole\Views\DataCatalog\DataSetList.aspx see
  1. <%if(false){ %>

Last edited Nov 7, 2012 at 10:12 PM by RossCollicutt, version 32

Comments

thomasantony Nov 30, 2011 at 2:55 PM 
odpappmetadata is missing a field named ImageLink. This causes the /interactive Data Service to fail.