Integrate Google Analytics Data using Entity Framework

The ComponentOne 2020v3 release enhances the number of datasources supported by .NET Standard service library DataConnectors, which provides a set of connectors used to connect to different types of data sources. Now, you can connect to six data sources, namely OData, Microsoft Dynamics 365 Sales, Salesforce Data, Kintone, QuickBooks Online, and Google Analytics using an ADO.NET provider for each type. These providers have the same ADO.NET architecture as the native .NET data providers for SQL Server and OLEDB. Hence, they ease the complexity of accessing data by letting you follow a similar set of classes available in the .NET architecture. These providers also have advantageous features such as authentication, caching, and SQL/LINQ query support. To add up to these features, the DataConnector also includes an Entity Framework (EF) Core provider for each type of datasource, which makes this library useful even when working with Entity Framework Core.

This blog will help you understand how to use the Entity Framework (EF) Core provider for Google Analytics to connect to the Google Analytics data and even explain the basic features, including authentication, querying, and caching.

Entity Framework Core provider for Google Analytics

As we all know, Entity Framework Core enables .NET developers to work with a database using .NET objects. Entity Framework maps each table in the database to an entity, where the entity is a class defining each field in the table as a property in the class. This collection of entities is known as Entity Data Model. This model saves database data in the properties of entities and retrieves data from the database, and automatically converts it to entity objects. Hence, to work with an Entity Framework Core provider for Google Analytics, first define the Entity Data Model.

To get started, you would need to create a .Net Core or .Net Framework project for any platform Console, Windows, or Web. In the newly created project, install the C1.DataConnector, C1.AdoNet.C1GoogleAnalytics and C1.EntityFrameworkCore.GoogleAnalytics packages from NuGet to start working with Entity Framework Core provider for Google Analytics.

Generating Entity Data Model

Entity Data Model comprises of several entities and a context class. The context class represents a session with the underlying database. Users can perform CRUD (Create, Read, Update, Delete) operations, where the entities are classes used to represent each table in the database. This step will elaborate on how to use scaffolding to generate context classes and entities.

Entity Framework Core provider for Google Analytics supports scaffolding to either create the entity classes and the context class manually or by using scaffolding. This blog post will discuss the Scaffolding approach. For understanding the manual implementation of the classes, refer to this documentation.

After configuring the project with the DataConnector Google Analytics Provider packages, you would need to install the 2.1.0 version of Microsoft.EntityFrameworkCore.Tools NuGet package for scaffolding the entity classes and the Context class.

After installing this package, open the package manager console by clicking on Tools → NuGet Package Manager → Package Manager Console. Once the console is displayed, use the following command to generate the entities and the context class:

 Scaffold-DbContext "Key File=*****.json;View Id=*****" C1.EntityFrameworkCore.GoogleAnalytics -OutputDir "GeneratedCode" -Tables Goals

The Scaffold-DbContext command defined above is currently accepting three parameters, connection string, the output folder name, and the list of table names for which to generate an entity class. Let's look into the definition of the connection string for Google Analytics.

Connection String

The Entity Framework Core provider for Google Analytics currently supports authorization using service account credentials. The service account credentials that get downloaded as a JSON file must be provided in the connection string using the KeyFile property. The ViewId for the Google Analytics view should be set through the ViewId property to make authorized access to Google Analytics data. Refer to this documentation for details.

Here is a sample connection string defined by specifying the KeyFile and ViewId attributes:

//Configure connection string  
 string connectionstring = "Key File=*****.json;View Id=*****";

The above command's execution generates a context class and an entity class corresponding to each table specified in the scaffold command's tables list parameter. In this case, it is just one table, "Goals." These classes are saved in a folder generated in the project folder with the same name as specified in the scaffolding command. Here is a quick view of the generated context class and the entity class Goals:

Observe the overridden OnConfiguring method defined in the Context class(named MainContext by default) above. It invokes the UseGoogleAnalytics method, which is an extension method defined for DbContextOptionsBuilder class. This method configures a context that is used to connect to the Google Analytics database.

Fetching Data

The Entity Framework Core provider for Google Analytics lets you query against the data source using .Net objects and standard SQL/LINQ syntax. The provider supports most SQL functionalities, including join queries, functions, and summaries. The sample code below depicts the READ operation using an object of the context class:

  //Fetch data  
private void Form1_Load(object sender, EventArgs e)  
 {  
  MainContext db= new MainContext();   
  List<Goals> goals_records = db.Goals.ToList();  
 }

Here is an example for fetching the data from GoogleAnalytics using C1 GoogleAnalytics Entity Framework Core Provider and binding it to FlexGrid in a WinForms application.

Here is the sample code snippet implementing the same, assuming FlexGrid control has already been added to the Windows Forms using designer:

  //Fetch data and bind to FlexGrid  
private void Form1_Load(object sender, EventArgs e)  
{    
   //Apply theme to FlexGrid  
   c1ThemeController1.SetTheme(c1FlexGrid1, "Office2016White");  

   //Fetch Google Analytics data and bind to FlexGrid  
   MainContext db= new MainContext();  
   c1FlexGrid1.DataSource = db.Goals.ToList();

   //AutoSize columns to display data  
   c1FlexGrid1.AutoSizeCols();      
 }

Here is a glimpse of FlexGrid after loading the fetched data:

Similarly, users can bind the fetched Google Analytics data to a FlexPivot control, as depicted below:

Find the demo sample for the same here or create a dashboard using the fetched data. Here is a quick glimpse:

You can find the detailed steps for creating the dashboard here.

Caching

The performance of the EF Core provider for Google Analytics can further be enhanced by accessing the data from the cache when the user performs similar operations repeatedly. To support this type of functionality, the DataConnector library provides in-built caching support. We would need to configure the connection object to set the cache properties and enable cache for a connection. The UseCache property must be set to true, while you can customize the default cache location by setting the CacheLocation property. The CacheTolerance property is used to set the cache tolerance time in seconds, with the default value set to 600 seconds.

Here is the updated connection string, which must be specified in the scaffolding command to enable caching by setting all the cache properties:

Scaffold-DbContext "Key File=*****.json;View Id=*****;Use Cache=true; Cache Tolerance=6; Cache Location='C:\temp\gacache.db';" C1.EntityFrameworkCore.GoogleAnalytics -OutputDir "GeneratedCode"

Incremental Caching

The EF Core provider for Google Analytics provides another caching strategy known as Incremental Caching, which updates the cache data periodically to add new records from the source entities without refreshing the complete cache data. This technique relies on a unique field and a timestamp field which records the last time the record was updated, or a new record was inserted. Hence, any table which needs to implement incremental caching must have a Timestamp column. Google Analytics has a default timestamp column, "Updated." The "IncrementalUpdate" and "IncrementalUpdateColumn" properties of C1GoogleAnalyticsConnectionStringBuilder class must be set to enable Incremental caching. For more details, refer to the documentation topic for Incremental Caching.

Here is the updated connection string, which must be specified in the scaffolding command to implement incremental caching:

Scaffold-DbContext "Key File=*****.json;View Id=*****;Incremental Update=true; Incremental Update Column=LastModifiedDate"" C1.EntityFrameworkCore.GoogleAnalytics -OutputDir "GeneratedCode"

For more detailed information, kindly refer to the DataConnector documentation. Also, explore all types of DataConnector and their features using Desktop DataConnector Explorer and Web-based DataConnector Explorer. Download and install the ComponentOne DataConnectors service component from here and find the installed samples at the following location: 'C:\Users\xxxx\Documents\ComponentOne Samples\ServiceComponents\DataConnector'.