Creating a data provider
1. Overview
This sample shows how to create a custom data provider, connecting to the Twitter REST API as an example. This data provider can then be used when creating a data connector.
You will learn how to do the following:
- Create an extension
- Create a data provider
- Create a data reader
2. Getting started
The current version of the sample solution targets both .NET Framework and .NET Core/.NET 5+ so that the packaged extension will work for version 7 of the software and higher on all platforms.
The following prerequisites must be installed on your computer to build the provided sample without modifications:
- Visual Studio 2017 or higher
- Microsoft .NET Framework 4.7.2
- Microsoft .NET Core 3.1
You can modify the sample project to target different versions of .NET if preferred depending on the version used by your application instance.
The NuGet packages referenced as dependencies are initially set to version 7.0.1. If your version of the application is newer and the APIs you are using may have changed, you can update the version of the package references.
You will also need a Twitter consumer key and consumer secret. For more information on obtaining these, go to the Twitter Developers website.
2.1. Downloading the solution
To download the sample solution, click here.
(A sample solution is also available for version 6 and versions 1 through 5.)
2.2. Opening the solution
Extract TwitterDataProviderSample.zip to a folder and open the solution in Visual Studio to build the extension.
To use the option to publish directly to your application instance, run Microsoft Visual Studio as an administrator before opening the solution. For example, you can right-click Visual Studio in the start menu and find the Run as administrator option.
The solution file is located at: [Extracted folder]\TwitterDataProvider\TwitterDataProvider.sln
3. The project
The project is a class library.
- OAuthHelper.cs - Helper class for creating Twitter authorization headers.
- PublishExtensionTemplate.props - Used for auto publishing the extension after the build succeeds, and defines extension properties, and files.
- 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 the required 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 recognized, it needs to contain a class that extends the ExtensionPackageInfo2 class. This class contains a call to the base constructor that reads extension package information from the extension manifest file, which is packaged with your extension when published.
TwitterDataProviderPackage.cs
/// <summary>
/// This class contains the package information about the extension package.
/// </summary>
public class TwitterDataProviderPackage : ExtensionPackageInfo2
{
/// <summary>Initializes a new instance of the
/// <see cref="TwitterDataProviderPackage"/> class.</summary>
/// <param name="extensionManifest">The extension manifest.</param>
public TwitterDataProviderPackage(ExtensionManifest extensionManifest)
: base(extensionManifest)
{
}
}
3.2. Publish extension template
This sample has a mechanism to automatically publish the extension when building, which is the Dundas.BI.PublishExtension NuGet package. When this package is added to the project, it creates a PublishExtensionTemplate.props file containing MSBuild property and item groups, which define how to create and publish the extension.
When the DtFilePath property is set to the file path of the dt tool of an application instance, it will then publish the extension directly to that instance when you build the solution. It will also touch the web.config file to force the web application to reset.
If the DtFilePath property is not set, it will create a .zip file you can add to your application instance using the Extensions screen in the administration UI. After building the solution with default settings and the Release solution configuration, this .zip file can be found in the bin\Release\netcoreapp3.1 subfolder of your solution. It targets both .NET Framework and .NET Core/.NET 5+.
<Project>
<Target Name="DefineDundasBIExtensionProperties" AfterTargets="CopyFilesToOutputDirectory">
<!-- Properties used to publish extension -->
<PropertyGroup>
<!-- Extension Author -->
<ExtensionAuthor>MyCompany Sample Author</ExtensionAuthor>
<!-- Extension Name -->
<ExtensionName>$(AssemblyName)</ExtensionName>
<!-- Extension Display Name -->
<ExtensionDisplayName>$(AssemblyName)</ExtensionDisplayName>
<!-- Extension Folder Name -->
<ExtensionFolderName>$(AssemblyName)</ExtensionFolderName>
<!-- Extension Main Assembly Name -->
<ExtensionMainAssemblyName>$(AssemblyName).dll</ExtensionMainAssemblyName>
<!-- Extension Id -->
<ExtensionId>4580e9db-2023-4d50-85a5-c303d0d4b997</ExtensionId>
<!-- Extension Copyright -->
<ExtensionCopyright>Copyright (c)</ExtensionCopyright>
<!-- Extension Version -->
<ExtensionVersion>1.0.0.0</ExtensionVersion>
<!-- The outfolder where the extension zip file will be left. -->
<ExtensionOutputFolder>$(OutputPath)</ExtensionOutputFolder>
<!-- DT -->
<!-- If this is specified the extension will be installed using dt. -->
<DtFilePath></DtFilePath>
<!-- Framework Folder -->
<FrameworkFolderRelative>$(OutputPath)\..</FrameworkFolderRelative>
<FrameworkFolder>$([System.IO.Path]::GetFullPath($(FrameworkFolderRelative)))</FrameworkFolder>
</PropertyGroup>
<!-- Define files to include -->
<ItemGroup>
<!-- Define the NetFramework assemblies -->
<NetfwAssemblies Include="$(FrameworkFolder)\net472\$(AssemblyName).*" />
<!-- Define the NetCore assemblies -->
<NetCoreAssemblies Include="$(FrameworkFolder)\netcoreapp3.1\$(AssemblyName).*" />
<!-- Define any app resources for the extension. -->
<AppResources />
<!-- Define any file resources for the extension. -->
<FileResources />
<!-- Define any localization files for extension. -->
<Localizations />
<!-- Define Extension Supported Runtimes -->
<ExtensionSupportedRuntimes Include="NetFramework" />
<ExtensionSupportedRuntimes Include="NetCore" />
</ItemGroup>
</Target>
</Project>
For more details on using this package to automate publishing extensions, see Using the Dundas.BI.PublishExtension NuGet package.
3.3. Defining the Twitter data provider
In order to create a data provider, you will need to extend 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 MyCompany.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 appear in the data connector dialog when the Twitter Data Provider is selected:
The IsEncryptionRequired properties set above mean that these settings are considered secure and will appear blank when the data connector is re-opened after saving.
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.
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:MyCompany.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:
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 a 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;
}
The screen name parameter defined above allows users of this data connector to populate the screen name through a parameter that can either be set in the data cube or made public to set on a dashboard.
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:
- If no data was retrieved yet, get the data.
- Get the next item.
- 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 MyCompany.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 provide to the application. 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
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.
5. Using the data provider
This sample was written to represent the data as a stored procedure in the application, which makes use of a screen name parameter that needs to be set to a Twitter handle before data can be retrieved.
After creating the data connector specifying the required Consumer and Consumer Secret API keys, a stored procedure can be found in the Programmatic Structures folder under the data connector in the Explore window.
Drag the stored procedure onto a new data cube, then configure its screen name parameter.
Like any other stored procedure with parameters, this parameter can be set to a specific value within the data cube, or it can be made public to set it using a view parameter or filter on a dashboard or other view.
The following dashboard was created using the Twitter data provider.
