Appendix L: Including data from other sources

BlueCielo Explorer can be configured to include data from other sources (for example,other MS SQL Server or Oracle databases not maintained by BlueCielo Explorer) along with its own data. This can be very helpful so that all of the data can be discovered by users from within the BlueCielo Explorer web client. This foreign data can be shown in searches, view columns, and property pages, but it cannot be updated from BlueCielo Explorer.

One example of this is a MS SQL Server query of a SharePoint document list like described in Extracting and Loading SharePoint Data in SQL Server Integration Services.

BlueCielo Explorer has an open design such that including other data sources can be easily done in the BlueCielo Explorer database itself as demonstrated in the following example T-SQL script.

Example

------------------------------------------------------------------------------
-- Prepare the database with sample data
------------------------------------------------------------------------------

-- A sample table (or view) containing the external data
CREATE TABLE [ExternalData] (
       [ID] [int] IDENTITY(1,1) NOT NULL,
       [Area] [nvarchar](255) NOT NULL,
       [Description] [nvarchar](255) NOT NULL
) 
GO

-- Add some data in the sample table
INSERT INTO [ExternalData] ([Area],[Description]) VALUES 
      ('Area1','Description on Area 1'),
      ('Area2','Description on Area 2'),
      ('Area3','Description on Area 3'),
      ('Area4','Description on Area 4')
GO

-- Make sure the Explorer database has a match for the foreign key
UPDATE DocumentRevisionCustom SET DocumentRevisionCustom.c_Custom_Area='Area1' 
GO

------------------------------------------------------------------------------
-- Configure the external properties
------------------------------------------------------------------------------

-- Create the table definition in the Explorer database
-- With a foreign key relation on [c_Custom_Area]
INSERT INTO [DocumentTableDefinition] ([TableName],[UnderlyingName],[Relation])
     VALUES (
        'ExternalData',                -- The alias name of the external table 
        'ExternalData',                -- The name of the external table
        'ExternalData.Area=DocumentRevisionCustom.c_Custom_Area') -- The foreign key relation
GO

-- Create a property set for the external properties
INSERT INTO [DocumentPropertySet] ([Name]) VALUES ('MyExternalProperties')
GO

-- Create the property definition for an external property
BEGIN
DECLARE @PSID int;

       SELECT @PSID = ID FROM [DocumentPropertySet] WHERE [Name]='MyExternalProperties';

       INSERT INTO [DocumentProperty]
           ([DocumentPropertySetID]
           ,[Name]
           ,[DisplayName]
           ,[ColumnName]
           ,[DataType]
           ,[Length]
           ,[Indexed]
           ,[HasLookupIndex]
           ,[InFullText]
           ,[PropertyType]
           ,[TableAlias]
           ,[ColumnAlias]
           ,[AllowClientGridColumn]
           ,[AllowClientCriteria])
       VALUES
           (@PSID
           ,'MyExternalProperties.AreaDescription'
           ,'Area Description'
           ,'Description'
           ,'String'
           ,255
           ,0
           ,0
           ,0
           ,2
           ,'ExternalData'
           ,'ExternalData'
           ,1
           ,1);
END
GO