Data professionals know the importance of documentation to ensure data quality, availability, security, and governance. Developers also need to understand the impact of updates and changes before they make them, so as not to break workflows and cause downtime. Axiom Data Science summarizes the need for documentation well:
“Descriptive metadata and documentation are critical to maintaining data quality. Metadata is “data about the data” that describes and contextualizes the dataset to ensure it is understandable to future users. Beyond standardized metadata, useful documentation might include standard operations procedures, field notes, etc., from which metadata may be derived or referenced.
Throughout the data lifecycle, both the metadata and documentation must be recorded and updated to reflect the actions taken to the data. This includes collection, acquisition, processing, quality review, and analysis, as well as any other stage of the data lifecycle.”
Documenting your entire data environment and understanding the impact of changes can be a challenge. If you’ve ever scoped out the effort required for manual data discovery and documentation, you’ll quickly begin to understand just how costly, time consuming, and difficult it can be. As a data estate grows, it becomes more costly and challenging to keep documentation up-to-date—and easier to miss something important.
Database Documentation and Data Lineage Analysis
SentryOne researched the frustrations, complications, and obstacles data professionals commonly encounter when it comes to documentation and impact analysis and built a solution, SentryOne Document, that you can use to easily maintain up-to-date documentation and ensure data compliance. The following is a high-level summary of SentryOne Document’s features, with more features to come.
Document Nearly All Object Metadata Down to the Column Level Across Multiple Technologies
Documentation in SentryOne Document is easy to navigate and search, including historical retention of old documentation. This enables your entire organization to crowd-source the documentation on a single web page, eliminating the need for users to install software on their local machines. Easily drill into a specific database, set of packages, reports, etc. to easily navigate the documentation. Or, if you’re looking for a specific object, you can find it quickly using the search bar on the top right.
You can also compare any object’s metadata from two different points in time to see highlighted differences—compare and review differences in (but not limited to) tables, schema, views, SSIS packages, Power BI reports, etc. In the near future, you’ll be able to compare two objects from different sources. This will enable comparison for the production versus non-production scenario. One use case for this would be determining if new/updated stored procedures from a development server were correctly deployed to the target production server.
Let’s look at an example scenario. You log off on Friday and everything is great. You come back on Monday and your clients are complaining about sluggish application performance after some updates were made over the weekend. You can take a new snapshot and then use the object comparison to look at the database to identify changes to properties and settings, stored procedures, views, tables, and more.
Many Supported Data Sources
SentryOne Document provides support for the following data sources and multiple ways to connect to your metadata, wherever it resides.
- SQL Server
- SQL Server Integration Services (SSIS)
- SQL Server Analysis Services (SSAS)
- Power BI
- SQL Server Reporting Services (SSRS)
- Azure Data Factory
- Azure Synapse Analytics (formerly Azure SQL Data Warehouse)
- Salesforce
- Excel
- Tableau
- Custom metadata import: Add any unsupported data sources to SentryOne Document manually
- And more data sources to come!
Visually Analyze a Customized View of Your Data Lineage
Accurately analyzing the origin of your data and how it is transformed as it moves throughout your environment is critical for ensuring data integrity and compliance with business rules and data privacy regulations.
With SentryOne Document, you can set specific filters to understand and explore the dependencies across technologies and environments. Easily answer questions such as “Where is this data used?” or “If we change or remove this object, which applications and systems will this impact upstream or downstream?” And enable your entire organization—DBAs, developers, analysts, compliance managers—to understand the true impact and scope of the environment. All will find value in the interactive impact analysis by setting appropriate filters to explore their current impact analysis query.
Let’s run through a use case together. You have a SQL Server table called Student that contains sensitive customer data. Your company has tasked you with determining where this sensitive data is being leveraged throughout your systems.
You would start by selecting the Student table on the left, as shown in the screenshot below. For filters, you can disable Key references and Object references, leaving only Data Lineage enabled. This means the result set will show only where data is moving in the environment.
Next, you’ll change the Dependency Direction. Bi-Directional will show both inbound and outbound references, Inbound Only will show you where the data is coming from. Since you want to determine where the data is going, you would to set this to Outbound Only. Now, when you extend the objects, you can quickly begin to understand that your sensitive data is being used in SSRS, in several stored procedures, and an SSIS package is pushing that data to a staging table. In this example, we don’t have any SSAS or other technologies but if they were used, you would see those as well in the result set.
Automate Updates to Documentation and Lineage Analysis
The snapshot process can be scheduled for automation through any process that can run the command line. Some examples would be PowerShell or Windows Task Scheduler. If a change is pushed through and you need to take a new snapshot immediately, you can also manually start a snapshot at any time through the web interface.
Customize Your Documentation with Ease
Enable your organization to document data compliance, business ownership and maintenance, developer notations, and more with SentryOne Document’s Data Dictionary. Multiple methods for inserting your notations are supported, including Text/Note, Drop Down, Pick List, Date Time, and more. Different data types enable your teams to quickly insert notations in the most efficient way for each unique circumstance. Changes made to Data Dictionary are immediately available to other users once saved. The ability to import and export the Data Dictionary is on the SentryOne Document product roadmap to further enable the quick insertion of values or exporting your existing Data Dictionary values to other systems.
Crowd-Sourced Documentation
Enable your entire organization to work with one set of live documentation that is available on your web browser without the need for individual users to install software. Whether you’re a DBA, developer, analyst, manager, or anything in between, there are immediate benefits to working from one set of shared documentation. For example, DBAs inserting notations for stored procedures can directly aid developers or analysts in understanding why that stored procedure exists and how it is used in other processes and applications.
Customized Documentation and Lineage Analysis Combined with Performance Monitoring
SentryOne Document’s Environment Map shows data collected from the SQL Sentry database to map connections between applications, users, clients, and targets (sourced from SQL Sentry’s Top SQL view and Windows processes). This information complements SentryOne Document’s Lineage Analysis feature by showing the dynamic usage of targets in the Lineage diagram and shedding light on the processes that are using the databases. If you’re planning to migrate to the cloud or to new upgraded on-premises servers, you need to understand how applications, users, targets, and clients are connected—this functionality will certainly help.
Check Out SentryOne Document
This is just the beginning for SentryOne Document—there are many enhancements planned. Want to take a tour of SentryOne Document? Explore our free online demo environment here!
You can also contact us with any questions or to request a live demo.
Additional Resources
