View Information

General Information & FAQs about Data Warehouse Security Views

How do I know if I’m using a table or view?
Table names begin with “T_”, for example T_PERS_HIST. View names begin with “V_”, for example V_EMPEE_PERS_HIST_1.

What is the difference between a table and a view?
Tables are the permanent, physical structures that store EDW (Enterprise Data Warehouse) data. Views are virtual tables that contain a subset of data from one or more EDW tables. To an EDW user, a view appears to be an actual table and can be used just like a table. Behind the scenes, the view is really a SQL query that indicates the rows and columns to be included in the view. The view column and the corresponding table column are often named exactly the same. For example, column PERS_LNAME in table T_PERS_HIST is also named PERS_LNAME in table V_EMPEE_PERS_HIST_1.

Why are views needed?
Most Views are needed for security reasons to restrict data access. The EDW is a centralized repository, therefore some EDW tables contain Student, HR and Finance data. Views prevent users from accessing data they don’t need and/or have access to, without altering the physical table structure. Views allow users to access just the information they need. Views also restrict access to confidential information such as SSN. 

How do I know what Views and Tables I have access to?
Run the following SQL query to list all tables and views you have access to:
    select owner, all_tables.table_name, grantee
    from all_tables,all_tab_privs
    where all_tables.table_name = all_tab_privs.table_name
    union
    select owner, all_views.view_name, grantee
    from all_views,all_tab_privs
    where all_views.view_name = all_tab_privs.table_name;

Do Universes map to Views or Tables?
Both. In many cases Universes map to Views instead of Tables for security reasons. See "Why are views needed?" (above) for more information. 

Can a Universe Object map to more than one View/Table?
No, an Object can only be mapped to one View or Table.

What type of View Metadata is available?
Currently the Metadata website contains a View Listing document. The View Listing document includes view name, column name, and view SQL. Decision Support is also working on a long-term solution to add view information to the Metadata database and link view metadata with existing table metadata.

How can the View Listing help me?
Use the View Listing to:

• Determine columns included in an EDW view
• Determine row restrictions for an EDW view (For example, the row restriction SQL for V_EMPEE_PERS_HIST_1 shows that this view only contains rows for employees also found in the T_EMPEE_PERS table)
• Determine the EDW source table/column for an EDW view/column (The EDW table and column can be used to look-up column logical name, column definition, column data type, source (ex: Banner) table, source column and source data type. Just type the view column name into the Metadata search field, then select the appropriate EDW source table from the search result list.)