Using modeling toolsDatabase profiling

Goal:- Demonstrate feasability of database profiling using data

modeling tools- Give added value for business and technical analysts- Increase Data Governance

Audience:• Managers• Analysts• Developpers

Using modeling tools

Tool used in this example:- Sybase PowerDesigner 15.3- Sybase SQL Anywhere 12; Demo DB

Using modeling tools

• Step 1: • reverse the database

Using modeling tools

• Step 2: - Add extentension « Data Profiling »

Using modeling tools

• Step 3: - Add data source definition

Using modeling tools

• Step 4a: - Locate tables to be profiled and associate

Data Source

Using modeling tools

• Step 4b: - Locate views to be profiled and associate

Data Source

Using modeling tools

• Step 4c: - Define business rules to be verifyed (1 rule is

applicable to model, one view or one table) control of vertical and horizontal constraints:

Using modeling tools

• Step 5: - For each column of tables/views to be

profiled, modify profiling option [optional]

Using modeling tools

• Step 6: - Run Global/Table/View data profiling by right-

cklicking in the model, choose « Data Solutions de Spirlet » then « Data Profiling » then appropriate data profiling action.

Using modeling tools

Usage • Step 7: - Check results for analysis

Using modeling tools

Usage • Step 8: - Display results in multi-model reports

Using modeling tools

Usage • Step 9: - Use results in impact and linage

Using modeling tools

Advantages• Virtually connect any database vendor• Data profiling and business rule verification is often done manually• A lot of time is saved by automation• Results can be reused by other automated solutions• Integrated inside PowerDesigner Repository

Can be used on:• Application databases• data warehouses (sources, datawarehouses, data marts)

Using modeling tools

Q&A

Using modeling tools