Understanding Informatica Profiling and PWH Purge Issues

📚 Profiling helps us understand the strengths and weaknesses of our data in a data warehouse.

🏢 The profiling architecture consists of four modules: analyst service, developer tool, data integration service, and profile warehouse.

🔍 Execution of profile definitions involves reading from the source, executing the profile, and storing the results in the profile warehouse database.

🔍 Profiling in data integration service requires enabling the profiling model and creating a profile warehouse with metadata tables.

⚙️ Different database properties and advanced profiling properties can be configured to meet specific requirements, such as the maximum concurrent profile jobs.

📊 After executing a profile, the results can be observed in the profiling console or client, with options for drill down analysis.

📊 The overview page shows stats like the number of profile runs, columns in the table, and applied rules.

🔍 The profile execution provides details about non-distinct values, value frequency, patterns, null percentage, and data types.

🔎 Drill down option allows viewing specific column data, checking for duplicates, and analyzing record types.

⚙️ Performing drill down on stage mode connects to the source database, extracts results, and displays them.

💾 Stage data option can affect performance on profile warehouse operations, recommended for complex or mainframe sources.

🏛️ Maintaining profile warehouse is crucial to prevent data growth and degradation of performance.

🔒 Purging profile warehouse content is recommended using 'purge' command to maintain consistency and prevent failures.

🔍 Profiling and PWH purge is recommended to avoid inconsistency and ensure accurate results.

📆 The recommended frequency for performing the purge is every 15 to 30 days based on historical data needs.

💽 Database optimizations, such as adding an index and adjusting database properties, can significantly improve purge performance.

🔍 The video discusses issues with purging data from a profile warehouse database, including slow performance and lack of activity during purging.

📚 To address these issues, the video recommends collecting logs and performing DB tracing to identify delayed and intensive queries. It also suggests maintaining regular purge activity to prevent excessive growth in the profile warehouse database.

💡 By implementing these recommendations, the purge process can be optimized and the profile warehouse can be effectively managed.

Identifying tables that are being written to frequently to optimize performance.

Investigating the hang scenario during profile fetching to determine the cause.

Using additional logging and DBA assistance to diagnose performance or hang issues.