Automated dynamic report reconciliation process for SSRS using C#.NET and Nunit(.NET)

Introduction

As a data warehouse developer, we all want to make that our daily reports are correct and up to date. But most of the time even though it goes through few stages of testing, it is always possible for new bugs to appear in production. This may occur due to the issues in ETL flow as well. Most of the time, reports should be reconciled with each other. Imagine one report contains one value and another one contains a different value. This can lead users to lose the confidence in a whole data warehouse. So it’s important to have a dynamic way to reconcile related reports with each other. Below mentioned way is a method that I came up with to tackle this issues using C# and the reports generated in SSRS by using its xml format. 

Background

Purpose of this process to help Data Warehouse developers and admins to maintain there reporting services by having an automated process that can be used to reconcile related reports with each other. Which can ultimately help increase the quality of the reports that resides in a data warehouse or in a report server. Once the properties that needs to be reonciled are setup, test cases will be automatically generated to for each data row dynamically.

Using the code

Step 1

First create a subscription is SSRS report server to generate the report and send the file as XML format to a location in the server or any folder that is accessible.

Make sure that reports which needs to reconciled are subscribed this way and they will be available when the C# code kicks off.

Step 2

Create the required tables in the database end which will be used to setup the test cases. Sql queries for these tables are mentioned below.

//Create UnitTest table where all unit test case details are stored
CREATE TABLE [dbo].[UnitTest](
    [UnitTestID] [int] IDENTITY(1,1) NOT NULL,
    [UnitTestName] [nvarchar](250) NULL,
    [ParameterOne] [nvarchar](100) NULL,
    [ParameterTwo] [nvarchar](100) NULL
) ON [PRIMARY]

//Create sub table which hold information on which properties in the report should be tested against each other
CREATE TABLE [dbo].[UnitTestReportDetail](
    [UnitTestReportDetailID] [int] IDENTITY(1,1) NOT NULL,
    [UnitTestID] [int] NULL,
    [ReportPropertyName] [nvarchar](100) NULL,
    [ParameterOneElementCollectionName] [nvarchar](100) NULL,
    [ParameterOneDescendantName] [nvarchar](100) NULL,
    [ParameterOneAttributeName] [nvarchar](100) NULL,
    [ParameterTwoElementCollectionName] [nvarchar](100) NULL,
    [ParameterTwoDescendantName] [nvarchar](100) NULL,
    [ParameterTwoAttributeName] [nvarchar](100) NULL,
    [Variance] [int] NULL,
 CONSTRAINT [PK_UnitTestReportDetail] PRIMARY KEY CLUSTERED 
(
    [UnitTestReportDetailID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Step 3

Create a C# console application.

Download Nunit framework for .NET free of charge and include it created console project.

Step 4

First create the entity class that will hold the values both reports for each property that needs to be compared.

public class ReportValueEntity
{
    public string ReportReconciliatioName { get; set; }
    public string ReportPropertyName { get; set; }
    public string ReportCollectionValue { get; set; }
    public decimal ReportOneValue { get; set; }
    public decimal ReportTwoValue { get; set; }
    public decimal VarianceValue { get; set; }

    public override string ToString()
    {
        return string.Format("{0} - {1}({2}): Expected - {3} to {4} but Actual {5}", ReportReconciliatioName, ReportPropertyName, ReportCollectionValue, (ReportOneValue - VarianceValue).ToString(),(ReportOneValue + VarianceValue), ReportTwoValue);
    }
}

Step 5

Create DataSource.cs class which will hold the method on retrieving report values from XML reports formats.

public class DataSource
	{
		public static List<reportvalueentity> AllReportValues()
		{
			return GetAllReportValues();
		}

		public static List<reportvalueentity> GetAllReportValues()
		{
			try
			{
				DAO dao = new DAO();
				DataTable tableValueTest_DT = dao.getDataTable("SELECT * FROM UnitTest WHERE TestTypeID = " + ((int)TestTypeEnum.Report_Values_Test).ToString());

				List<reportvalueentity> allValues = new List<reportvalueentity>();

				foreach (DataRow unitTest in tableValueTest_DT.Rows)
				{
					string reportOne_Name = unitTest["ParameterOne"].ToString();
					string reportTwo_Name = unitTest["ParameterTwo"].ToString();
					int reportID = int.Parse(unitTest["UnitTestID"].ToString());

					XDocument xmlFileOne = XDocument.Load(string.Format(@"report one path.xml", reportOne_Name));
					XDocument xmlFileTwo = XDocument.Load(string.Format(@"report two path.xml", reportTwo_Name));

					DataTable reportValueTest_DT = dao.getDataTable("SELECT * FROM UnitTestReportDetail WHERE UnitTestID = " + reportID.ToString());

					XNamespace dfOne = xmlFileOne.Root.Name.Namespace;
					XNamespace dfTwo = xmlFileTwo.Root.Name.Namespace;

					foreach (DataRow testProperty in reportValueTest_DT.Rows)
					{
						List<xelement> collectionList = xmlFileOne.Descendants(dfOne + testProperty["ParameterOneElementCollectionName"].ToString()).ToList();
						List<xelement> collectionList2 = xmlFileTwo.Descendants(dfTwo + testProperty["ParameterTwoElementCollectionName"].ToString()).ToList();

						foreach (XElement item in collectionList)
						{
							ReportValueEntity reportValueEntity = new ReportValueEntity();
							reportValueEntity.ReportReconciliatioName = unitTest["UnitTestName"].ToString();
							reportValueEntity.ReportPropertyName = testProperty["ReportPropertyName"].ToString();
							reportValueEntity.VarianceValue = decimal.Parse(testProperty["Variance"].ToString());

							reportValueEntity.ReportCollectionValue = item.Attributes().FirstOrDefault().Value.ToString();

							XElement item2 = collectionList2.Where(a => a.Attributes().FirstOrDefault().Value == reportValueEntity.ReportCollectionValue).FirstOrDefault();

							if (item2 != null)
							{

								XElement element = item.Descendants(dfOne + testProperty["ParameterOneDescendantName"].ToString()).FirstOrDefault();
								XElement element2 = item2.Descendants(dfTwo + testProperty["ParameterTwoDescendantName"].ToString()).FirstOrDefault();

								if (element != null || element2 != null)
								{
									if (element.Attributes(testProperty["ParameterOneAttributeName"].ToString()).FirstOrDefault() != null)
										reportValueEntity.ReportOneValue = decimal.Parse(element.Attributes(testProperty["ParameterOneAttributeName"].ToString()).FirstOrDefault().Value.ToString());

									if (element2.Attributes(testProperty["ParameterTwoAttributeName"].ToString()).FirstOrDefault() != null)
										reportValueEntity.ReportTwoValue = decimal.Parse(element2.Attributes(testProperty["ParameterTwoAttributeName"].ToString()).FirstOrDefault().Value.ToString());

									allValues.Add(reportValueEntity);
								}
							}
						}
					}
				}

				return allValues;

			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
	}
</xelement></xelement></reportvalueentity></reportvalueentity></reportvalueentity></reportvalueentity>

DAO.cs and Connection.cs classes will hold simple and basic methods to retrieve data from the UnitTest tables we created before.

public class DAO
{
    public DataTable getDataTable(string query)
    {
        try
        {
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            Connection connection = new Connection();
            ds = connection.GetDataSet(query);
            dt = ds.Tables[0];
            return dt;
        }
        catch (Exception ex)
        {

            throw ex;
        }
    }
}

public class Connection
{
    SqlConnection sqlConnection;

    public Connection()
    {
        if (sqlConnection == null || sqlConnection.State == ConnectionState.Closed)
        {
            sqlConnection = getConnection();
        }
    }

    public SqlConnection getConnection()
    {
        return sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["Dev"].ConnectionString);
    }

    public void openConnection()
    {
        if (sqlConnection.State != ConnectionState.Open)
        {
            sqlConnection.Open();
        }
    }

    public void closeConnection()
    {
        if (sqlConnection.State != ConnectionState.Closed)
        {
            sqlConnection.Close();
        }
    }

    public DataSet GetDataSet(string query)
    {
        SqlDataAdapter dataAdapater = new SqlDataAdapter();
        DataSet dataSet = new DataSet();
        SqlCommand command = sqlConnection.CreateCommand();
        command.CommandText = query;
        dataAdapater.SelectCommand = command;

        dataAdapater.Fill(dataSet);
        return dataSet;
    }
}

Configure the connection string in App.config file

<configuration>
  <connectionstrings>
      <add connectionstring="connection string" name="Dev" providername="System.Data.SqlClient">
    </add></connectionstrings>
</configuration>    

Below enum can be used to extend this process to include reconciliation of data sets and row counts in between data sets.

public enum TestTypeEnum
{
    Table_Values_Test = 1,
    Report_Values_Test = 2,
    Row_Count_Test = 3
}

Step 6

Now create the ComparisonTest.cs class which will hold all test cases. When the assembly is called or executed, method in this class will get executed.

	[TestFixture]
	class ComparisonTest
	{
		[Test, TestCaseSource(typeof(DataSource), "AllReportValues")]
		public void CompareReportValues(ReportValueEntity entityRow)
		{
			Assert.That(entityRow.ReportTwoValue, Is.InRange<decimal>((entityRow.ReportOneValue - entityRow.VarianceValue), (entityRow.ReportOneValue + entityRow.VarianceValue)));
		}
	}    
</decimal>

Step 7

Now specify the properties that you want to be reconcile in the table. Example is mentioned below,

Once you specify the relevant report names and the type. You can start specifying the in detail information on how and the what properties should be reconciled in the report.

Variance column can be used to have a value range. For example to check whether value of the report two is in a specific range with the use of Variance value we specify. [ See the code in ComparisonTest.cs ]

Below I have pasted the parts from the XML of the reports to understand what areas I have setup to be reconciled with the above screen shots.

Report One - 

Report Two - 

Above example shows how Discount value in two seperate reports are being reonciled against each other.

Step 8

Once all the above mentioned steps are done. Build the C# project and go to bin folder of the C# project and double click on the .dll file that got created. Make sure that you have installed Nunit framework before this step. If then, you will be able to see and how the above mentioned property gets reconciled against each other for all rows of the ParameterElementCollection mentioned above.

Points of Interest

This process can be automated by hosting this through a continous integration tool such as Jenkins. With a CI tool like jenkins, we can make sure that reports are not broken with each commit and each ETL process and keep the report quality high. 

With my next article, we will see how to configure jenkins to host this sort of a test project to run daily or on a scheduled period.