Morgan
Posted on September 14, 2021
Dynamics 365 Sales is a customer relationship management application that lets organizations manage sales processes and more. The application itself provides many features for reporting and data analysis; however, sometimes, there may be a need to create external applications for various purposes, including reporting and analysis. For this, Dynamics 365 provides services that external applications can access to get data. In this blog, we will see how we can use ComponentOne DataServices libraries like DataConnectors and DateEngine to fetch data and perform in-memory analysis.
Take a further look at DataConnectors here.
Business Use Case
For this demonstration, we want to analyze Dynamics 365 Sales Opportunities data in a .NET application to find:
- Opportunities at each stage of sales
- Open opportunities for each product
- Opportunities value in each sales representative's bucket
- Opportunities in each fiscal quarter
Project Setup
To assimilate the above information, we would need the following fields from the Opportunities entity:
- owner
- createdon
- stagename
- quantity
- extendedamount
- productname
The above data is often mapped across separate entities. For example, the Opportunities table may have a GUID for ownerid instead of the name of salesrep, the actual name of the sales rep may exist in another systemsuser table. Therefore, to get data, users may need to fetch data from different tables, which can get complicated. ComponentOne DataConnectors is a timesaver in this case, as the data connector lets users use SQL in ADO.NET or LINQ and EntityFramework Core to fetch and query data. This blog will use its Dynamics 365 Sales ADO.NET classes, and SQL JOIN queries to get the desired data from multiple tables.
Once the data is available, we will use C1DataEngine and C1PivotEngine to create in memory pivots to get desired results. To start, we create a WinForms application and add following NuGet packages:
- C1.AdoNet.D365
- C1.DataEngine
- C1.PivotEngine
Fetch Data from Dynamics 365 Sales
Add a class named PivotService and declare public property for C1DataEngine WorkSpace. Add GetData() method to fetch data from Dynamics 365 Sales.
class PivotService
{
private Workspace ws;
public Workspace Workspace => ws;
private dynamic opportunity;
private readonly string QueryName = "BaseQuery";
public C1PivotEngine engine;
private string GetD365SConnection()
{
var config = Properties.Settings.Default;
string extendProperties = "{\"resource\":\"" + config.Resource + "\"}";
string conectionStr = $@"Url={config.UrlDynamics};Use Etag=true;OAuth Client Id={config.ClientID};OAuth Client Secret={config.ClientSecret};OAuth Token Endpoint={config.TokenEndpoint};OAuth Extend Properties={extendProperties}";
return conectionStr;
}
public void GetData()
{
C1.AdoNet.D365S.C1D365SConnection con = new C1.AdoNet.D365S.C1D365SConnection(GetD365SConnection());
con.Open();
string sql = "select op.opportunityid, op.name, op._ownerid_value,op.createdon, op._parentaccountid_value,op.estimatedclosedate, op._parentcontactid_value, osp._activestageid_value, gc_renewaldate, gc_paymenttermscode, gc_opportunitytype, p.productnumber, p.name as productname, p.gc_salesedition, ps.stagename, opp.priceperunit, opp.quantity, opp.extendedamount, su.fullname as salesrep from opportunities op inner join opportunityproducts opp on opp._opportunityid_value = op.opportunityid inner join opportunitysalesprocesses osp on osp._opportunityid_value = op.opportunityid inner join products p on p.productid = opp._productid_value inner join accounts a on a.accountid = op._parentaccountid_value and a.name like 'account%' inner join processstages ps on ps.processstageid = osp._activestageid_value inner join systemusers su on su.systemuserid = op._ownerid_value LIMIT 105";
var cmd = new C1.AdoNet.D365S.C1D365SCommand(con, sql);
var connector = new DbConnector(this.ws, con, cmd);
connector.GetData("Opportunities");
}
}
Here, we created an instance of C1D365SConnection & C1D365SCommand, assigned the SQL query & connection to the command object. Finally, the DbConnector object is used, which executes the command to import the data to DataEngine workspace. The result is named Opportunities.
DataEngine Configuration
We next define a base query to fetch required columns from the imported data. This query will be available in memory for us to apply further transformations over the data. We create this query in the Init() method of the PivotService class. We also declare an instance of C1PivotEngine allowing us to create pivot transformation over the base query by connecting its instance to the DataEngine workspace.
DataEngine Configuration Expand source:
public PivotService()
{
Init();
}
private void Init()
{
this.ws = new Workspace();
this.ws.Init("workspace");
//Import data if Opportunities table is null or data is stale in workspace
if (Properties.Settings.Default.threshhold.Date < DateTime.Now.Date )
{
if (this.ws.GetTableData("Opportunities") == null)
{
this.GetData();
Properties.Settings.Default["threshhold"] = DateTime.Now.Date;
Properties.Settings.Default.Save();
}
}
this.opportunity = this.ws.table("Opportunities");
if (this.opportunity!=null)
{
var queryName = this.QueryName;
// create base query and execute it
var settings = new
{
opportunity.stagename,
opportunity.salesrep,
opportunity.createdon,
opportunity.productname,
opportunity.extendedamount,
Sales = Op.Sum(opportunity.extendedamount),
Quantity = Op.Count(opportunity.quantity),
QuantityTotal = Op.Sum(opportunity.quantity)
};
dynamic query = this.ws.query(queryName, settings);
query.Query.Execute();
//initialize pivotengine and connect workspace & base query
this.engine = new C1PivotEngine();
this.engine.Workspace = this.ws;
this.engine.ConnectDataEngine(queryName);
}
}
Since we will create several pivot transformations and avoid repetitive code, we will create a method to pass pivot fields from different transformations queries and return pivoted data based on the fields.
private C1PivotEngine CreateFlexPivotEngine(string queryName, string[] colFields, string[] rowFields, string[] valFields)
{
var fp = this.engine;
fp.BeginUpdate();
this.engine.ColumnFields.Clear();
this.engine.RowFields.Clear();
this.engine.ValueFields.Clear();
fp.ColumnFields.Add(colFields);
fp.RowFields.Add(rowFields);
fp.ValueFields.Add(valFields);
fp.EndUpdate();
return fp;
}
Pivot Transformations
Now we are ready to create pivots over the data we fetched from Dynamics365 server and create methods for each transformation. In the method, we will pass row, column, value fields to the above 'CreateFlexPivotEngine" function to create a pivot using these fields and return the pivotengine object.
Opportunities at Each Stage of Sales
Next, we want to know the value of opportunities at each stage of Sale. We do this by pivoting over the stagename and sales fields of the base query.
public IBindingList OpportunityByStage()
{
var fp = this.CreateFlexPivotEngine(this.QueryName, new string[] { "" }, new string[] { "stagename" }, new string[] { "Sales" });
return fp.PivotDefaultView;
}
We get the following result:
Open Opportunities for Each Product
The product-wise open opportunities could be known by creating a pivot using productname and sales column on the base query. Also, to get only open opportunities, we filter stagename to exclude 'close' salesstage.
public IBindingList ProductOppportunity()
{
var fp = this.CreateFlexPivotEngine(this.QueryName, new string[] { "stagename" }, new string[] { "productname" }, new string[] { "Sales" });
var fld = fp.ColumnFields[0].Filter;
fld.Condition1.Operator = ConditionOperator.NotEquals;
fld.Condition1.Parameter = "close";
return fp.PivotDefaultView;
}
We get the following result:
Opportunities Value in Each Sales Representative's Bucket
To know the value of opportunities with each salesrep, pivot over the slaresrep, stagename and sales fields.
public IBindingList OpportunityByRep()
{
var fp = this.CreateFlexPivotEngine(this.QueryName, new string[] { "salesrep" }, new string[] { "stagename" }, new string[] { "Sales" });
return fp.PivotDefaultView;
}
We get the following result listing sales against each sales rep at every stage.
Opportunities in Each Fiscal Quarter
C1PivotEngine allows us to group data over a range to present meaningful information. It creates range groups over string, numbers, and dates. In this case, we apply range grouping over the createdon field to get quarterly opportunity data. We pass the createdon field twice to rowfields so that one can show date and other could be used to get the fiscal quarter. This gives us sales estimates at each stage in each quarter.
public IBindingList OpportunityByFiscalQuarter()
{
var fp = this.CreateFlexPivotEngine(this.QueryName, new string[] { "stagename" }, new string[] { "createdon", "createdon" }, new string[] { "Sales" });
var year = fp.RowFields[0];
year.Range.RangeType = RangeType.FiscalYear;
year.Range.FiscalYearFirstMonth = 4;
var month = fp.RowFields[1];
month.Range.RangeType = RangeType.FiscalQuarter;
return fp.PivotDefaultView;
}
You may view the complete code of this application by downloading it here . You will need to update connection settings as per your Dynamics 365 server in order to run the sample.
We have seen how easy it is to fetch data from Dynamics 365 Sales using DataConnector libraries. It is as if we are using known ADO.NET classes without the learning curve. We also saw how we could perform pivot transformation over this data using C1DataEngine library. The C1DataEngine library can query and aggregate millions of records in memory within a fraction of a second, and coupled with pivot transforms, it makes a powerful data analysis library.
Posted on September 14, 2021
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.
Related
September 14, 2021
