1 / 20

Module 5 Metadata, Tools, and Data Warehousing

Module 5 Metadata, Tools, and Data Warehousing. Section 1 Metadata Management. Metadata. Information about data is referred to as Metadata. DBA’s rely on metadata to manage a database. In order for data to be anything more than simply data, metadata is required. Metadata strategy

burt
Download Presentation

Module 5 Metadata, Tools, and Data Warehousing

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Module 5 Metadata, Tools, and Data Warehousing ITEC 450 Section 1 Metadata Management

  2. Metadata Information about data is referred to as Metadata. DBA’s rely on metadata to manage a database. • In order for data to be anything more than simply data, metadata is required. • Metadata strategy • Procedures for identifying and defining data ownership and stewardship • Methods for the collection and storage of metadata (typically using a repository) • Policies to enforce data stewardship procedures and security for metadata access • Measurements to gauge the quality and usability of metadata ITEC 450

  3. Type of Metadata • Technology metadata – technical aspects of the data as it relates to storing and managing the data in computerized systems • SSN is 9 digits of numbers • ANI is 10 digit of numbers starting with not 0 or 1 • Business metadata – aspects of how the data is used by the business, and is needed for the data to have value to the organization • SSN is a unique identification number, associated with a person • ANI’s first 3-digits are area code, and the remaining 7 digits are local phone number. ITEC 450

  4. DBMS Metadata For DBA’s, the DBMS itself is a good source of metadata • System catalog – technology metadata about database objects • Names of every database, table, column, index, view, relationship • Constraints such as primary key, foreign key, and not null • System catalog is • Active – automatically build and maintained • Integrated – the system catalog is a part of DBMS and up-to-date with any changes within the database • Non-subvertible – DMBS operations are the only mechanism for populating the system catalog ITEC 450

  5. Repository A repository stores information about an organization’s data assets. • Repository is used for • Store information about your data, processes, and environment. • Support multiple ways of looking at the same data • Store in-depth documentation, and produce detail and management reports • Repository benefits • Integrated views of multiple systems • The consistency it provides in documenting data elements and business rules • Support of a rapidly changing environment • Repository challenges • Keeping the repository up-to-date • Many metadata sources, such as application component metadata, business metadata, data modeling metadata, database metadata ITEC 450

  6. Module 5 Metadata, Tools, and Data Warehousing ITEC 450 Section 2 Database Management Tools

  7. Benefits of Database Management Tools A DBA tool reduces the amount of time, effort, and human error involved in maintaining efficient database systems and applications. • Ease the administrative burdens • Fulfill market niches not adequately supported by the major DBMS vendors • Automate database monitoring and routine activities ITEC 450

  8. Data Modeling and Design Tools The tools provide a consistent and coherent means of creating conceptual and logical data models and transforming them into physical database designs. • Do not have to be unique to a specific database • Support the standard tasks associated with logical data modeling such as entity-relationship diagramming and normalization • Create a physical data model geared to each of your target DBMS platforms • Reverse engineering to generate standard DDL automatically from major DBMS system catalog • Examples: Erwin from CA, PowerDesigner from Sybase, ER/Studio from Embarcadero, Rational Data Architect from IBM, MySQL workbench from MySQL, Oracle Designer from Oracle ITEC 450

  9. Change Management Tools The tools provide capabilities to perform various database alterations. • Alter database parameters that can not be easily performed with ALTER statement • Modify database structures with cascading effects • Change a column’s data type and length • Remove columns from a table • Batch requested changes into a work list that can be executing in the foreground or the background • Provide database analysis and planning prior to implementing database changes • Examples: CA Database Command Center ITEC 450

  10. Database Comparison Tools The tools enable DBA’s to compare one database to another in terms of its database objects and structures. Such tools will identify differences and automatically generate the DDL to sync among databases. • Find missing migrations • Detect any discrepancies among different databases • Are often useful during application program testing and debugging • Examples: TOAD from Quest, SQL Compare for SQL Server from Red Gate ITEC 450

  11. Database Object Migration Tools The tools facilitate the quick migration of database objects from one environment to another. • Provide a systematic method to promote changes instead of manually running DDL’s • Can migrate all dependent objects and security • Enhance database securities • Reduce the migration time • Examples: Softek LDMF from IBM, DBMigration from shareware ITEC 450

  12. Performance Management Tools • System Performance Tools • Examine the database server, its configuration, and usage • Monitor and report CPU, Memory, I/O usage and history info • Trace individual process and capture information • Database Performance Tools • Read the database statistics from the system catalog, and provide additional analysis with enhanced tool capability • Set thresholds and provide maintenance actions • Provide a series of canned reports detailing the potential problems • SQL Performance Tools • Analyze the SQL in an application program • Suggest alternative SQL solutions • Assess impacts caused by database object changes ITEC 450

  13. Evaluating DBA Tool Vendors • List of features • Vendor reputation • Customer satisfaction • Support model and enhancement request • Upgrade and new feature development/support ITEC 450

  14. Module 5 Metadata, Tools, and Data Warehousing ITEC 450 Section 3 Oracle Data Dictionary and Dynamic Performance Views

  15. Oracle Data Dictionary It’s the heart of the DBMS, and key to DBA’s success. The views are static, as Oracle updates them only when a DDL transaction take place. • The underline tables are located in the SYSTEM tablespace, and owned by the user SYS. The data dictionary views are built on top of these base tables. • The data dictionary contains key items: • User information, roles and privileges • Object information, constraint information, storage information • Storage information, operational information • The three sets of data dictionary views with prefix of: • USER – objects that the user owns • ALL – objects that the user has been granted privileges • DBA – all objects in the database, accessible by DBA’s or special granted ITEC 450

  16. General Views • DICT – all data dictionary views and short description SQL> select * from DICT where table_name like '%INDEXES'; • PRODUCT_COMPONENT_VERSION – version of all major components of the Oracle database SQL> select * from product_component_version; • DBA_SOURCE – source code of a database object SQL> select text from dba_source where owner = 'HR' and name = 'SECURE_EMPLOYEES'; SQL> select text from dba_source where owner = 'HR' and name = 'SECURE_DML'; • DBA_OBJECTS– all objects in the database SQL> select object_name, object_type from dba_objects where owner = ‘HR’; ITEC 450

  17. User Management Related Views • DBA_USERS – database user info SQL> select username, account_status from dba_users; • DBA_ROLES – all database roles SQL> select * from dba_roles; • DBA_SYS_PRIVS, DBA_ROLE_PRIVS – System and role privileges • DBA_TAB_PRIVS – table-level privielges SQL> select grantee, privilege from dba_tab_privs where owner = 'HR' and table_name = 'EMPLOYEES'; ITEC 450

  18. Storage Related Views • DBA_FREE_SPACE – free space of tablespace SQL> select tablespace_name, sum(bytes) from dba_free_space group by tablespace_name; • DBA_SEGMENTS – segment details SQL> select segment_name, segment_type, tablespace_name from dba_segments where owner = 'HR'; ITEC 450

  19. Dynamic Performance Views The views are dynamic, as they are updated continuously while the database is running. Also called v$ views. • Memory related views • Session and user related views • Performance-monitoring views • SQL-related views ITEC 450

  20. Commonly Used Views • Memory – v$sga, v$sgastat • Session – v$session, v$sess_io, v$session_longops • SQL info – v$sql, v$sqltext • Performance – v$instance, v$lock, v$locked_object • General – v$instance, v$license, v$database, v$parameter ITEC 450

More Related