Connector features
Data catalog
Dataedo documents metadata of following objects:
- Tables
- Views
- Procedures
- Functions
- Sequences
- Jobs
- Jobs steps
For more details, go to Connector specification section
Data Lineage
For SQL Server Dataedo supports both manual and automatic lineage. Automatic lineage is currently available only for views. Dataedo will parse views and show source of each of the columns:
Data Profiling
You can profile data in tables and views to get a grasp of data sitting in your database:
Relationships (PK/FK) Tester
With Dataedo you can check if selected columns are viable candidates to create a relationship between tables:
How to connect
- Server name - IP address or host name under which database is available
- Port - port number under which database is available
- Authentication - way of authentication to be used when connecting to SQL Server:
- Windows Authentication - authentication that will use currently logged in Windows user. Does not require providing any other information.
- SQL Server Authentication - SQL authentication. Requires SQL login:
- User
- Password
- Azure Active Directory - Password - authentication with Azure AD login. Requires:
- User - AD username
- Password - password for the provided username
- Azure Active Directory - Password - authentication with Azure AD login. Requires:
- Azure Active Directory - Integrated - this authentication will use AD account currently logged in Windows
- Azure Active Directory - Universal with MFA - authentication through external Azure AD service. You can optionally provide username.
- Connection mode - specifies if connection will be encrypted.
- Encryped connection if possible - lest restrictive mode. If encryption is possible, data will be encrypted. Otherwise it will be send in plain text.
- Force encryption, trust server certificate - Dataedo will not connect unless connection can be encrypted. SSL certificate on server will not be verified.
- Force encryption, require trusted certificate - Most restrictive mode. Dataedo will connect only if connection can be encrypted and SSL certificate on server is valid.
- Encryped connection if possible - lest restrictive mode. If encryption is possible, data will be encrypted. Otherwise it will be send in plain text.
- Database - database name. For some types of authentication it is possible to list available databases by clicking three dot icon [...].
Connector specification
Supported versions
2008, 2008 R2, 2012, 2014, 2016, 2017, 2019, 2022.
Supported editions
LocalDB, Express, Developer, Standard, Business Intelligence and Enterprise.
Parallel Data Warehouse
Parallel Data Warehouse is not currently supported
Imported objects
| Object | Imported as |
|---|---|
| Table | Table |
| View | View |
| Procedure | Procedure |
| Function | Function |
| Sequence | Sequence |
| Job | Tasks/Jobs |
| Job stpes | Tasks/Jobs |
| Database trigger | Not imported |
Tables metadata
| Metadata | Imported as |
|---|---|
| Schema | Schema |
| Name | Name |
| Description | Description |
| Location (external table) | Location (hidden) |
| Columns | Table columns |
| Name | Name |
| Data type | Data type |
| Null/Not null | Nullable |
| Description | Description |
| Foreign Keys | Relationships |
| Constraint table | FK Table |
| Referenced table | PK Table |
| Name | Relationship name |
| Description | Description |
| Primary/Unique keys | Unique keys |
| Name | Key name |
| Columns | Columns |
| Description | Description |
| Triggers | Triggers |
| Definition | Script |
| Name | Name |
| Action | When |
| Description | Description |
| Indexes | Not imported |
Views metadata
| Metadata | Imported as |
|---|---|
| Schema | Schema |
| Name | Name |
| Description | Description |
| Columns | Table columns |
| Name | Name |
| Data type | Data type |
| Null/Not null | Nullable |
| Description | Description |
| Definition | Script |
Procedures metadata
| Metadata | Imported as |
|---|---|
| Schema | Schema |
| Name | Name |
| Description | Description |
| Definition | Script |
| Parameters | Input/Output |
| Description | Description |
| Name | Name |
| IN/OUT | Mode |
| Data type | Data type |
Functions metadata
| Metadata | Imported as |
|---|---|
| Schema | Schema |
| Name | Name |
| Description | Description |
| Definition | Script |
| Parameters | Input/Output |
| Description | Description |
| Name | Name |
| IN/OUT | Mode |
| Data type | Data type |
Sequences metadata
| Metadata | Imported as |
|---|---|
| Schema | Schema |
| Name | Name |
| Description | Description |
| Definition | Script |
| Parameters | Input/Output |
| Name | Name |
| IN/OUT | Mode |
| Data type | Data type |
Jobs metadata
| Metadata | Imported as |
|---|---|
| Name | Name |
| Description | Description |
| Create date | Create date |
| Modify date | Modify date |
Jobs steps metadata
| Metadata | Imported as |
|---|---|
| Name | Name |
| Command | Script |
| Create date | Create date |
| Modify date | Modify date |
| Output file | Input/Output |
| IN/OUT | Mode |
| Data type | Data type |
| Destination | Description |
Descriptions & extended properties
Dataedo reads and writes extended properties from/to following SQL Server objects:
| Object | Read | Write back |
|---|---|---|
| Tables | ✅ | ✅ |
| Column | ✅ | ✅ |
| Primary keys | ✅ | ✅ |
| Column comments | ✅ | ✅ |
| Unique keys | ✅ | ✅ |
| Foreign keys | ✅ | ✅ |
| Triggers | ✅ | ✅ |
| View | ✅ | ✅ |
| Columns | ✅ | ✅ |
| Function comments | ✅ | ✅ |
| Parameters | ✅ | ✅ |
| Stored procedures | ✅ | ✅ |
| Parameters | ✅ | ✅ |
| Sequences | ✅ | |
| Jobs | ✅ |
Data profiling
Datedo supports following data profiling in PostgreSQL:
| Profile | Support |
|---|---|
| Table row count | ✅ |
| Table sample data | ✅ |
| Column distribution (unique, non-unique, null, empty values) | ✅ |
| Min, max values | ✅ |
| Average | ✅ |
| Variance | ✅ |
| Standard deviation | ✅ |
| Min-max span | ✅ |
| Number of distinct values | ✅ |
| Top 10/100/1000 values | ✅ |
| 10 random values | ✅ |
Read more about profiling in a Data Profliling documentation.
Data Lineage
| Source | Method | Version |
|---|---|---|
| Views - object level | From dependencies | ✅ |
| Views - object level | From SQL parsing | ✅ |
| Views - column level | From SQL parsing | ✅ |
| Stored Procedures - object level | From SQL parsing | ✅ |
| Stored Procedures - column level | From SQL parsing | ✅ |
| Polybase External Tables - object level | From sys.external_data_sources and sys.external_tables views | ✅ |
| dbt | dbt connector | ✅ |
Column-level data lineage is retived using Dataedo SQL parser. Read more about capabilities of Transact-SQL SQL parser
Limitations
Following schema elements currently are not supported:
- Check constraints
- Non unique indexes
- Unique indexes on views (planned)
- Column level lineage for views with recursive CTE
Required access level
Importing database schema requires certain access level in documented database. Granting VIEW DEFINITION permission in the documented database to a new user will allow them to import all objects from the database. Alternatively, you can grant this permission only for specific objects you want to document.
Following objects are accessed during schema import process:
- INFORMATION_SCHEMA.COLUMNS
- INFORMATION_SCHEMA.KEY_COLUMN_USAGE
- INFORMATION_SCHEMA.PARAMETERS
- INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
- INFORMATION_SCHEMA.ROUTINES
- INFORMATION_SCHEMA.ROUTINE_COLUMNS
- INFORMATION_SCHEMA.TABLES
- INFORMATION_SCHEMA.VIEWS
- sys.all_objects
- sys.columns
- sys.computed_columns
- sys.extended_properties
- sys.foreign_key_columns
- sys.indexes
- sys.index_columns
- sys.objects
- sys.procedures
- sys.servers
- sys.sql_dependencies
- sys.sql_expression_dependencies
- sys.sql_modules
- sys.tables
- sys.views
- sysobjects
- sysusers
- sys.sequences
- sys.schemas
- sys.types
Importing jobs and jobs steps uses views from msdb System Database. Requires membership in the public role, due to usage of sp_executesql.
Following objects are accessed during jobs import process (user needs Select permission on those tables to import jobs) :
- dbo.sysjobs
- dbo.sysjobssteps
- dbo.syscategories - used for extended properties
- sys.server_principals - used for extended properties