Data Governance made easy – your ultimate stored procedure to get a record count from...

Data Governance made easy – your ultimate stored procedure to get a record count from any table on any server and compare

Introduction

Data Governance plays an important role in building a successful data-driven company as it helps business users to trust the data. While a typical Data Analytics project starts with identifying all the relevant transactional sources of information, building your Data Warehouse according to business needs and feeding (and converting) the data as often as possible, you can’t get a buy-in from your business users. Your business users need to trust you data to make business decisions. This is where continuous operational reporting come handy to alert on a difference between your source of transactional and your target analytical systems. No matter how great you Data Warehouse is, if the users are constantly complaining about bad data, your Data Warehouse project days are numbered.

I am assuming that you followed best practices and isolated your company analytical server (OLAP) from your transactional server (OLTP). You also setup an ongoing replication in near real-time. As much as business users are happy to analyze all the historical data (great for trending analysis that requires volume of data) they would also love to see real-time data (great for an ecommerce company that is coming up with new products). But how do keep track that all your dependent tables are in-sync?

Problem

How do you compare data between source and destination tables using a repeatable process? One of the first reporting needs in a Data Governance report is comparing counts to identify any missing records. Having a code that allows to get a record count from any table anywhere (between multiple servers) will make it easy.

Solution

We are going to build a reusable stored procedure that could help in a Data Governance Report to capture record counts between source and destination tables to compare and keep track of trending.

While the first challenge is building the flexibility that allows to query a table/view record count on any server, the second challenge is how to avoid resources hungry COUNT(*) that could lead to performance issues.

The following stored procedure does just that – capturing a record count virtually anywhere using sp_executesql with dynamic SQL and taking advantage of statistical record count from sys.dm_db_partition_stats to avoid performance problems.

Code is here - https://datasteve.com/2019/06/02/your-ultimate-stored-procedure-to-get-a-record-count-from-any-table/

Equipped with this stored procedure, it is just a matter of time that you can build an operational report to look sharp and shinny that you can share with your business users (see Fig #1).