Creating a data provider

1. Overview

This sample shows how to create a simple Twitter data provider. You will learn the following:

  • How to get started
  • How to create a Dundas BI extension
  • How to create a data provider
  • How to create a data reader

Note
This is meant as a guide for creating a data provider but not necessarily using the best practices for connecting to Twitter data.

2. Getting started

The following prerequisites must be installed on your computer:

  • Visual Studio 2012, or higher.
  • Microsoft .NET Framework 4.5
  • Dundas BI Server

In addition you will also need a Twitter consumer key and consumer secret. For more information in obtaining these properties, go to the Twitter Developers website.

2.1. Downloading file

To download the Twitter Data Provider Sample solution click here.

2.2. Extracting sample to SDK folder

This sample is designed to automatically publish the extension to your Dundas BI instance. First you must extract the TwitterDataProviderSample.zip to the SDK folder within the instance. To extract the TwitterDataProviderSample.zip do the following:

  1. Find the SDK folder for your instance. It is located at [instance root]\sdk.
  2. Extract the TwitterDataProviderSample.zip to the SDK folder.
  3. You should see the following result:

    Samples added to the SDK folder
    Samples added to the SDK folder

    With the following structure:

    • [instance root]
        • sdk
            • Samples
                • TwitterDataProvider

    Important
    If the sample is not in the exact folder, it will not work correctly.

    2.3. Opening Solution

    To open the Visual Studio solution simply do the following:

    1. Right click on the Microsoft Visual Studio shortcut, and click run as administrator.
    2. Click the File Menu, then Open Solution.
    3. Double click the solution located at: [instance root]\sdk\Samples\TwitterDataProvider\TwitterDataProvider.sln

    3. The project

    The project is a class library.

    • OAuthHelper.cs - Helper class for creating Twitter authorization headers.
    • PublishExtension.targets - Used for auto publishing the extension after the build succeeds.
    • TwitterConnection.cs - Class used for connecting to Twitter.
    • TwitterConnectionInfo.cs - Contains information used to connect twitter.
    • TwitterDataProvider.cs - Class where the data provider structures, and properties are defined.
    • TwitterDataProviderPackage.cs - This class contains the package information about the extension package.
    • TwitterDataReader.cs - Reads the data from a particular query result into a Dundas BI format.
    • TwitterObjects.cs - Objects used for serializing objects.
    • TwitterProviderIds.cs - IDs for the Twitter data provider properties.

    3.1. ExtensionPackageInfo class

    In order for a data provider to be read by Dundas BI it needs to first contain a class that extends the ExtensionPackageInfo class. This class contains the package information about the extension package.

    TwitterDataProviderPackage.cs

    /// <summary>
    /// This class contains the package information about the extension package.
    /// </summary>
    public class TwitterDataProviderPackage : ExtensionPackageInfo
    {
            /// <summary>
            /// Gets the name of the extension package author.
            /// </summary>
    	public override string Author
    	{
    		get { return "Dundas Data Visualization Sample Author"; }
    	}
    
            /// <summary>
            /// Gets the copyright text associated with the extension package.
            /// </summary>
    	public override string Copyright
    	{
    		get { return "Dundas Data Visualization, Inc."; }
    	}
        /// <summary>
        /// Gets the localized display name of the extension package.
        /// </summary>
    	public override string DisplayName
    	{
    		get { return "Twitter Data Provider Package"; }
    	}
            /// <summary>
            /// Gets the unique identifier of the extension package.
            /// </summary>
    	public override Guid Id
    	{
    		get { return new Guid("4580e9db-2023-4d50-85a5-c303d0d4b997"); }
    	}
            /// <summary>
            /// Gets the name of the extension package.
            /// </summary>
            /// <remarks>
            /// The return value of this property should never change depending on the culture.
            /// </remarks>
    	public override string Name
    	{
    		get { return "Twitter Data Provider Package"; }
    	}
            /// <summary>
            /// Gets the version of the extension package.
            /// </summary>
    	public override Version Version
    	{
    		get { return new Version(0,0,1); }
    	}
    }
    
    

     

    3.2. Publish Extension Targets

    This sample has a mechanism to automatically publish the extension. This mechanism is the PublishExtension.targets file which overrides the AfterBuild target. This will create the following file after successfully compiling the solution:

    [instance root]\www\BIWebsite\App_Data\Extensions\TwitterDataProvider\bin\TwitterDataProvider.dll

    It will then touch the web.config to force the web application to reset.

    3.3. Defining the Twitter data provider

    In order to create a data provider you will need to extend a Dundas.BI.Data.DataProviders.DataProvider. In the example below we use the tabular provider which is commonly used to connect to web services. This type is extended from the RelationalDataProvider. Another type of provider that can be extended is the OlapDataProvider.

    TwitterDataProvider.cs

    using Dundas.BI.Data;
    using Dundas.BI.Data.DataProviders;
    using Dundas.BI.Data.DataProviders.Special;
    using Dundas.BI.Data.Metadata;
    using Dundas.BI.Data.Metadata.Relational;
    using Dundas.BI.Data.Parameters;
    
      ...
    
    namespace Dundas.BI.Sample.TwitterDataProvider
    {
        /// <summary>
        /// This represents a Twitter Data Provider
        /// </summary>
        public class TwitterDataProvider : TabularProvider
        {
          ...
        }
    }
    
    

    3.3.1. Defining properties

    To define properties for a data provider you need to override the PropertyDescriptors property. In the case below we define two encrypted properties: Consumer Key Id, and Consumer Secret Id. Each property ID is unique. In this case we define them in the TwitterProviderIds class.

    TwitterProviderIds.cs

    /// <summary>
    /// Ids for the Twitter Properties.
    /// </summary>
    public class TwitterPropertyIds
    {
    	#region Static Fields
    	/// <summary>
    	/// The consumer key property identifier
    	/// </summary>
    	public static readonly Guid ConsumerKeyId = new Guid("8845e65f-97cb-458f-98ff-6b5d8554160f");
    	/// <summary>
    	/// The consumer secret property identifier
    	/// </summary>
    	public static readonly Guid ConsumerSecretId = new Guid("6ce3cbe0-87cc-4b23-9f4b-35cc4b005ca0");
    	#endregion Static Fields
    }
    
    ...
    
    /// <summary>
    /// Gets the data provider specific property descriptors.
    /// </summary>
    public override IReadOnlyList<Dundas.BI.Data.DataProviders.ProviderProperty> PropertyDescriptors
    {
        get
        {
            if (_propertyDescriptors == null)
            {
                _propertyDescriptors = DefineProperties();
            }
            return _propertyDescriptors;
        }
    }
    /// <summary>
    /// Defines the properties for the data provider.
    /// </summary>
    /// <returns></returns>
    private IReadOnlyList<Dundas.BI.Data.DataProviders.ProviderProperty> DefineProperties()
    {
        List<ProviderProperty> properties = new List<ProviderProperty>();
        ProviderProperty propertyDescriptor;
        propertyDescriptor = new StringProperty(
            TwitterPropertyIds.ConsumerKeyId,
            "Consumer Key Id",
            "Consumer Key Description",
            string.Empty, int.MaxValue);
        propertyDescriptor.IsEncryptionRequired = true;
        properties.Add(propertyDescriptor);
        propertyDescriptor = new StringProperty(
            TwitterPropertyIds.ConsumerSecretId,
            "Consumer Secret Id",
            "Consumer Secret Description",
            string.Empty, int.MaxValue);
        propertyDescriptor.IsEncryptionRequired = true;
        properties.Add(propertyDescriptor);
        return new ReadOnlyCollection<ProviderProperty>(properties);
    }
    
    

    The following properties will be appear in the Data Connector panel when the Twitter Data Provider is selected:

    Twitter data connector properties
    Twitter data connector properties

    3.3.2. Test the Connection

    The data connector screen contains a Test Connection button. The example below outlines the code used for the Test Connection method.

    Twitter data connector test connection button
    Twitter data connector test connection button

    TwitterDataProvider.cs

    /// <summary>
    /// Tests the connection.
    /// </summary>
    /// <param name="dataConnector">The data connector.</param>
    /// <returns>
    /// The test connection result.
    /// </returns>
    public override Dundas.BI.Data.DataProviders.TestConnectionResult TestConnection(
        Dundas.BI.Data.DataProviders.IProviderConnector dataConnector)
    {
        using (TwitterConnection twitterConnection = TwitterDataProvider.Connect(dataConnector))
        {
            twitterConnection.GetUserTimeline("DundasData");
            TestConnectionResult result = new TestConnectionResult(null, null);
            return result;
        }
    }
    
    /// <summary>
    /// Connects the specified data connector.
    /// </summary>
    /// <param name="dataConnector">The data connector.</param>
    /// <returns>A <see cref="T:Dundas.BI.Sample.TwitterDataProvider.TwitterConnection"/>.</returns>
    /// <exception cref="System.ArgumentException">Missing required value.</exception>
    private static TwitterConnection Connect(IProviderConnector dataConnector)
    {
        string consumerKey =
            dataConnector.ProviderValues.GetStringValue(TwitterPropertyIds.ConsumerKeyId, string.Empty);
        string consumerSecret =
            dataConnector.ProviderValues.GetStringValue(
                 TwitterPropertyIds.ConsumerSecretId, string.Empty
                 );
        if (string.IsNullOrEmpty(consumerKey) ||
            string.IsNullOrEmpty(consumerSecret)
            )
        {
            throw new ArgumentException("Missing required value.");
        }
        TwitterConnectionInfo twitterConnectionInfo = 
            new TwitterConnectionInfo(consumerKey, consumerSecret);
    
        return new TwitterConnection(twitterConnectionInfo);
    }
    
    /// <summary>
    /// Gets the open connection.
    /// </summary>
    /// <param name="dataConnector">The data connector.</param>
    /// <returns>
    /// The open connection.
    /// </returns>
    /// <exception cref="System.ArgumentNullException">dataConnector</exception>
    public override IDisposable GetConnection(
        Dundas.BI.Data.DataProviders.IProviderConnector dataConnector
        )
    {
        if (dataConnector == null)
        {
            throw new ArgumentNullException("dataConnector");
        }
        return TwitterDataProvider.Connect(dataConnector);
    }
    
    

    3.3.3. Discovering Structures

    To support discovering the structures of the data provider, first define the structures as a list of structures. In the case below we will create one structure that represents a Twitter user timeline and will be based on the stored procedure where we pass the screen name:

    Note
    This is based on the call to GET statuses/user_timeline from the Twitter Rest API.

    TwitterDataProvider.cs

    /// <summary>
    /// Gets the list of the structures with the specified type.
    /// </summary>
    /// <param name="connection">The connection.</param>
    /// <param name="structureTypes">The structure types.</param>
    /// <returns>
    /// The list of the structures.
    /// </returns>
    public override IEnumerable<Dundas.BI.Data.Metadata.DataStructure> DiscoverStructures(
        IDisposable connection, Dundas.BI.Data.Metadata.SupportedDataStructures structureTypes
        )
    {
        List<DataStructure> dataStructures = new List<DataStructure>();
        DataStructure dataStructure = 
            new RelationalStructure(
                SupportedDataStructures.RelationalStoredProcedure, 
                "UserTimeline"
                );
    
        dataStructures.Add(dataStructure);
        return dataStructures;
    }
    
    

    Next define the structure itself. Below we will define the columns we want within the structure, and the screen name parameter. If your data provider supports multiple structures, you must check which one is being discovered and return values accordingly. A quick way to do that would be to check the dataStructure.Name value.

    TwitterDataProvider.cs

    /// <summary>
    /// This method is to discover the data structure.  
    /// Columns and Parameters will be defined in the object.
    /// </summary>
    /// <param name="connection">The connection.</param>
    /// <param name="dataStructure"></param>
    /// <returns>A Datastructure with columns defined, and parameters defined.</returns>
    public override DataStructure DiscoverStructure(IDisposable connection, DataStructure dataStructure)
    {
        TwitterConnection twitterConnection = connection as TwitterConnection;
        RelationalStructure structure = dataStructure as RelationalStructure;
        structure.Columns.Clear();
        structure.Columns.Add(
            new RelationalColumn(structure, "ScreenName", DataType.String(16))
        );
        structure.Columns.Add(
            new RelationalColumn(structure, "Text", DataType.String(165))
        );
        structure.Columns.Add(
            new RelationalColumn(structure, "CreatedAt", new DataType(DatabaseType.DateTime))
        );
        structure.Columns.Add(
            new RelationalColumn(structure, "RetweetCount", new DataType(DatabaseType.Int32))
        );
        structure.Columns.Add(
            new RelationalColumn(structure, "FavoriteCount", new DataType(DatabaseType.Int32))
        );
    
        NativeParameter nativeParameter =
            new NativeParameter("ScreenName", DataType.String(260));
        nativeParameter.IsOptional = false;
        nativeParameter.Description = "Twitter Screen Name";
        nativeParameter.Direction = NativeParameterDirection.Input;
        structure.Parameters.Clear();
        structure.Parameters.Add(nativeParameter);
        return structure;
    }
    

    In order for this data structure to be used the screen name must be defined in a data cube.

    Twitter screen name parameter
    Twitter screen name parameter

    Tip
    It is a good idea to use the warehouse or in-memory storage option as the Twitter API has restrictions on the number of requests that can be made.

    3.4. Defining the Twitter data provider reader

    3.4.1. Creating the class

    The Twitter data provider reader will need to extend the Reader class, or implement the IReader interface. In the example below we extend the Reader class. If working with multiple data structures, it is best to define a separate reader for each.

    TwitterDataReader.cs

    public class TwitterDataReader : Reader
    {
        /// <summary>
        /// Initializes a new instance of the <see cref="TwitterDataReader"/> class.
        /// </summary>
        /// <param name="twitterConnection">The twitter connection.</param>
        /// <param name="query">The query.</param>
        public TwitterDataReader(TwitterConnection twitterConnection, QueryDefinition query)
        {
    	    _currentItemCount = 0;
    	    _twitterConnection = twitterConnection;
    	    _queryDefinition = query;
    	    _relationalStructure = (RelationalStructure)query.StructureDefinition;
        }
    }
    

    3.4.2. Defining the schema

    The schema needs to be defined within the data reader in order to return the column metadata. This information can be extracted from the query definition. This is demonstrated in the code snippet below:

    TwitterDataReader.cs

    /// <summary>
    /// Gets the column metadata of the reader.
    /// </summary>
    public override IList<Dundas.BI.Data.Metadata.IDataElement> Schema
    {
    	get
    	{
    		return this._relationalStructure.DataElements.ToList();
    	}
    }
    
    

    3.4.3. Reading the data

    The next code added is to advance the reader to the next record. If there is another row it will return true, otherwise false.

    The code below does the following:

    1. If no data was retrieved yet, get the data.
    2. Get the next item.
    3. If item exists return true, otherwise return false and stop reading.

    TwitterDataReader.cs

    /// <summary>
    /// Advances the reader to the next record.
    /// </summary>
    /// <returns>
    /// <see langword="true"/> if there are more rows; otherwise, <see langword="false"/>.
    ///</returns>
    public override bool Read()
    {
    	if(twitterDataList == null)
    	{
    		string screenName =
    			this._queryDefinition.Parameters.FirstOrDefault(
    				parameter => parameter.Name == "ScreenName"
    				).Value.ToString();
    		twitterDataList =
    			this._twitterConnection.GetUserTimelineForCurrentYear(screenName).ToList();
    	}
    	_currentItem = NextItem();
    	if(_currentItem == null)
    	{
    		return false;
    	}
    	else
    	{
    		return true;
    	}
    }
    
    

    After a record is read the items are returned using the custom indexer. For the case below we are matching the index to the column we defined earlier in the structure.

    TwitterDataReader.cs

    /// <summary>
    /// Gets the element value located at the specified index.  
    /// </summary>
    /// <value>
    /// The element value.
    /// </value>
    /// <param name="elementIndex">Element index.</param>
    /// <returns>The element value.</returns>
    public override object this[int elementIndex]
    {
    	get
    	{
    		object returnObject;
    		switch (elementIndex)
    		{
    			case 0:
    				returnObject =
    					_currentItem.user.screen_name;
    				break;
    			case 1:
    				returnObject =
    					_currentItem.text;
    				break;
    			case 2:
    				DateTime dataDate =
    					DateTime.ParseExact(
    					_currentItem.created_at,
    					TwitterConnection.dateTimeFormat,
    					CultureInfo.InvariantCulture
    				);
    				returnObject =
    					dataDate;
    				break;
    			case 3:
    				returnObject = _currentItem.retweet_count;
    				break;
    			case 4:
    				returnObject = _currentItem.favorite_count;
    				break;
    			default:
    				returnObject = null;
    				break;
    		}
    		return returnObject;
    	}
    }
    
    

    3.5. Defining the Twitter connection

    The Twitter data provider will need to extend the ConnectionBase class in order to define how it connects to Twitter.

    TwitterConnection.cs

    using Dundas.BI.Data.DataProviders.Special;
    using System;
    using System.Collections.Generic;
    using System.Globalization;
    using System.IO;
    using System.Linq;
    using System.Net;
    using System.Text;
    using System.Threading.Tasks;
    using System.Web.Script.Serialization;
    
    namespace Dundas.BI.Sample.TwitterDataProvider
    {
    	public class TwitterConnection : ConnectionBase
    	{
    		#region Fields
    
    		private TwitterConnectionInfo twitterConnectionInfo;
                    internal const string dateTimeFormat = "ddd MMM dd HH:mm:ss zzzz yyyy";
    
    		#endregion Fields
    
    		#region Constructors
    
    		public TwitterConnection(TwitterConnectionInfo twitterConnectionInfo)
    		{
    			this.twitterConnectionInfo = twitterConnectionInfo;
    		}
    
    		#endregion Constructors
    
    
                   ...
           }
    }
    

    The key methods to define in this class are the ones we use to fetch our data structures from the server. For example the GetUserTimeline() method which is called by the TwitterDataReader defined above in order to fetch the raw data from Twitter that will be used to construct a table to send to Dundas BI. Here is that method trimmed to emphasize the important sections:

    TwitterConnection.cs

    internal IEnumerable<TwitterData> GetUserTimelineForCurrentYear(string screenName)
    {
    
    
        List<TwitterData> fullTwitterDataObjects = new List<TwitterData>();
    
        int currentYear = DateTime.Now.Year;
    
        bool keepRequesting = true;
               
        string lastEntryProcessed = string.Empty;
    
        while(keepRequesting)
        {
    
            string url =
                    string.Empty;
    
            if(string.IsNullOrEmpty(lastEntryProcessed))
            {
                url =
                    string.Concat(
                        "https://api.twitter.com/1.1/statuses/user_timeline.json?screen_name=",
                        screenName,
                        "&include_rts=true&exclude_replies=true&count=200&"
                        );
            }
            else
            {
                url =
                string.Concat(
                    "https://api.twitter.com/1.1/statuses/user_timeline.json?screen_name=",
                    screenName,
                    "&include_rts=true&exclude_replies=true&max_id=", 
                    lastEntryProcessed, 
                    "&count=200&"
                    );
            }
    
            HttpWebRequest apiRequest =
                (HttpWebRequest)WebRequest.Create(
                    url
                    );
    
            apiRequest.Headers.Add(
                OAuthHelper.GetAuthorizationHeaders(twitterConnectionInfo)
                );
    
            apiRequest.Method = "Get";
    
            using (WebResponse timeLineResponse = apiRequest.GetResponse())
            {
                using (var reader = new StreamReader(timeLineResponse.GetResponseStream()))
                {
    
                    JavaScriptSerializer javaScriptSerializer = new JavaScriptSerializer();
    
                    List<TwitterData> twitterDataObjects = 
                        javaScriptSerializer.Deserialize<List<TwitterData>>(
                                reader.ReadToEnd()
                            );
    
    
                    if(twitterDataObjects.Count() == 0 )
                    {
                        keepRequesting = false;
                    }
    
                    foreach(TwitterData data in twitterDataObjects)
                    {
                        // Filter out retweets.  
                        if(!data.text.StartsWith("RT @"))
                        {
                            if (!data.id_str.Equals(lastEntryProcessed))
                            {
    
                                DateTime dataDate =
                                        DateTime.ParseExact(
                                        data.created_at,
                                        TwitterConnection.dateTimeFormat,
                                        CultureInfo.InvariantCulture
                                    );
    
                                if (dataDate.Year.Equals(currentYear))
                                {
                                    fullTwitterDataObjects.Add(data);
                                }
                                else
                                {
                                    keepRequesting = false;
                                }
    
                            }
                        }
                    }
    
                    lastEntryProcessed = twitterDataObjects.Last().id_str;
    
    
                                
                }
            }
               
        }
    
        return fullTwitterDataObjects;
    }
    

    4. Debugging

    In order to debug the data provider, you can use the following:

    System.Diagnostics.Debugger.Launch();
    
    

    This pop up is a window that will allow you to attach the debugger.

    Debugger pop-up
    Debugger pop-up

    5. Result

    The following dashboard was created using the Twitter data provider.

    Example of dashboard created using Twitter data provider
    Example of dashboard created using Twitter data provider

    6. See also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri