Consumption Analytics

Welcome to Consumption Analytics! This newly funded project is undergoing rapid development - please check back frequently for updates. If you have questions, feel free to email Stephen Hilton

To update: `   pip install tdcsm --upgrade` Version 0.3.9.5.8 (June 23, 2020) First GUI is live! To start, simply run the updated process and look for a new coa.py, which is downloaded into the root directory during the first run. Or, in the root directory open python3 and type:      from tdcsm.tdgui import coa      c = coa() Systems now hold two new elements, dbsversion and collection. For example, a system running 16.20 and pdcr would look like: dbsversion: "16.20" collection: "pdcr"With these additions, we get down to one fileset (i.e., DBQL_Core) instead of one fileset per combination (i.e., DBQL_Core_1620pdcr) Download_files() only happens once, not one-per-system (i.e., more than it needed to) Fixed several bugs pertaining to file-handling differences between Mac and Windows Removed the "skip_git" and "skip_dbs" settings Fixed DBQL_Core spool-out issues Fixed dim_app.csv file encoding problem New Fileset: vantage_health_check (from APAC) Version 0.3.9.5.0 (June 10, 2020) Connection and Encryption settings are now set at a per-system basis within source_systems.yaml ODBC connection type fixed Driver name is case sensitive and must match EXACTLY Driver names can be found in the windows ODBC Data Source Administrator under the Drivers tab Application can run special commands relating to sql that does not return any values Fixes error caused by re-processing of pptx files Fixes out-of-bounds issue relating to pptx processing Version 0.3.9.4.4 (June 2, 2020) added fileset: db_objects defaults can now be assigned per-file (or per fileset, like before) if config.yaml // settings // write_to_perm = "True", the upload_to_transcend() process will take an alternate upload approach to help debugging: write table to a perm table in adlste_coa_stg insert-select into global temp table in adlste_coa_stg call the stored proc to perform merge if successful, it will drop the perm table if NOT successful, the perm table in STG persists, to allow investigation

Useful Links Getting Starting Guide PowerBI Reports Consumption Analytics SharePoint COA Trello Project Board COA SQL on GitHub

FileSet List demo Simple demonstration project that exercises the major functionality. Should work on any Teradata system, regardless of version or platform. You will need access to Transcend to perform final upload_to_transcend() dbql_core Comprehensive data pull from QueryLog (dbql) table, aggregating by application, statement type, user department, and time. Collects CPU, IO, Query Counts, Spool, Cache Rates... all the major metrics available in DBQL that can aggregated by metrics above. Defaults to 6 weeks (42 days) per run. Of special note: This is primary source of PowerBI project Even aggregated, this can be a large extract depending on workload diversity. Expect in the neighborhood of 500k to 1M rows per quarter (3mo) on busy systems concurrency Generates concurrency average, peak, 80th and 95th percentile numbers, by day by hour. Default is 6 weeks. This includes auto-generated visualizations, including a line chart and heat-map. top_users Generates top users, per user, per week, month, and time period total. "Top" is measured and ranked several different ways: CPU IOGB Query Count Query Complexity Query RunTime Error Count (excluded in overall rank) These ranks are added up and re-ranked, to arrive at an overall Total Rank (except for Error Count). When WeekID and MonthID are both null, ranks are for the time-period total. When WeekID is null, ranks are for the month. When WeekID is NOT null, ranks are for the week. Partial weeks are not allowed, while months must have at least 3 full weeks of data to be included. The local "top_users.csv" file will contain UserName as well as a unique "UserHash." When uploading to Transcend, only the non-identifiable and non-reversable UserHash will be saved, thus preventing any customer PII from being stored on Trancend. K-Mean clustering visualizations included. db_objects Collection of analysis around database object characteristics, such as count of columns by types or formats, and in near-future, other table, database, or user analysis. success_plan Generates a template Consumption Analytics section for the CSM Success Plan, by stringing together other filesets, namely: cpu_summary concurrency top_users ...more to come!

Have an Analytic you want to contribute? Interested in making your mark? Contribute! Please reach out to Stephen to get added to the working team.