Universe Properties

---

Universe properties allow you to override certain universe parameters that you may have already set in Designer (File | Parameters) such as the connection, controls for query time and size, and SQL generation options (see Chapter 6). In Supervisor, the Universe Properties also allow you to restrict data by column and row. Table Mappings allow you to replace table names in the default universe with group- or user-specific table names.

Universe Parameters: Definition, Controls, SQL

In Chapter 6, you set a number of parameters in the TESTFASH universe. In Supervisor, you can override certain parameters by group or by user. For example, the default result set for a universe may be 100,000 rows; in Supervisor, you could increase this to 300,000 rows for certain power users. Keep in mind that these controls are universe-specific.

For example, Al Saraisky is the CEO and has rights to all universes. Although he is a strong leader and CEO, he is new to computers. Al may unwittingly run resource-intensive queries. The Sales universe is widely used throughout the company and you need to make sure that inefficient queries do not affect response time. In the following example, the universe’s default SQL Query settings allow complex SQL statements. You want to disable these settings for the casual users.

1. Scroll to the group that contains the instance of the user you wish to modify. In this example, Sales and Marketing, Al Saraisky.

2. Within the Resource pane, select the Universe tab at the bottom of the screen.

3. Select the Sales universe.

4. Click Resource Properties.

   

5. If you wish, you can specify a unique connection for a user or group of users. This is useful if your database is replicated on two servers (for response time or security reasons). For example, a European group could have a connection to a database with just European Sales data that is physically located in Europe. A North American group could have a connection to a database with North American sales, physically located in North America.

6. Select the Controls tab, shown next. Supervisor displays the default settings from Universe Parameters set in Designer. Any changes you make for the specific user or group are highlighted in red. To restore the universe defaults, click Reset. As a general rule, you may want to increase the defaults for individual power users or information-intensive groups (financial analysts), but I do not recommend decreasing the controls. These options are explained in more detail in Chapter 6.

   

7. Select the SQL tab to control how SQL is generated and which operators the individual user or group can control. In the next example, I have modified Al’s rights to ensure he cannot generate complex SQL queries that may adversely affect response time.

   

8. Click OK to save your changes and close the Universe Properties dialog box.

Data Restrictions

Earlier in this chapter, you saw how column-level security can be accomplished in User Definitions | Security Access Levels. Supervisor allows yet another way to control column-level access: through object restrictions. The following table summarizes the key differences between security access levels and object restrictions:

Security access level	Global to all universes Requires corresponding settings in the universe Applies to an individual user (not a group) Varying security levels
Object restriction	Universe-specific No universe modifications needed Applies to a group or all instances of a user Object is completely removed for the group or user—no priorities

1. To set object restrictions, scroll to the group or instance of the user you wish to modify—in this example, Sales and Marketing.

2. Within the Resource pane, select the Universe tab.

3. Select the Human Resources universe.

4. Click Resource Properties.

5. Select the Objects tab, shown here:

   

6. Click Add.

7. Supervisor presents you with a New Restricted Object box. You can manually enter the Class\Object or click Select to use the Object Browser to expand any classes and select the individual object that you wish to restrict. In the next screen, I am adding a restriction on Salary.

   

8. Click OK to return to the New Restricted Object dialog box.

9. Click OK to add the object to the list of restricted objects.

10. Click Check All to verify that the object still exists in the repository. This will set the Status to OK. If any objects have recently been deleted from the universe, the Status will show Invalid ID.

11. Click OK to save your changes and close the Universe Properties dialog box.

    Note 

    Object restrictions apply to all instances of a user regardless of security access level and regardless of whether the restriction is inherited. If you have multiple instances of a user with different ways to inherit access to a universe, the objects will always be hidden, as the object restriction applies.

When a user builds a query, the user will no longer see the restricted objects from the object list. If a user tries to refresh a query that contains a restricted object (or for which the user does not have sufficient security access), the user receives the following error message:

Row Restrictions

Row restrictions restrict the rows returned in a query by appending a SQL WHERE clause to every query a user runs. Multiple row restrictions are appended to the query with AND. Row restrictions are useful for security purposes, but also for user productivity. They save users time by automatically filtering the data according to what they need to see.

Figure 12-6 shows a more detailed organization for the Plastics Express, Sales and Marketing group. The group is organized by a combination of product managers who can view global information, and regional managers who can view sales information within their regions only. For example, Nancy is the Product Manager for children’s clothes (product category = Girls, Boys). Nancy should be able to view all sales for these product categories, regardless of which country the customer resides in. Helle is the Regional Manager for Europe; she should be able to view sales for all product categories, but only where the region is Europe.

Figure 12-6: The Sales and Marketing organization is organized by product categories and regions; row restrictions allow data to be automatically filtered according to this organization.

Figure 12-7 shows all data rows available in the source system. In order to restrict which rows of data Nancy sees, you will add a restriction for SH.PRODUCTS.PROD_CATEGORY IN ('Girls','Boys'). Helle needs the restriction SH.COUNTRIES.COUNTRY_REGION = 'Europe'.

Figure 12-7: All data is still available in the database, but a WHERE clause will filter the data so that each person sees only the rows he or she needs.

1. To add a row restriction, follow steps 1–4 from the preceding section to select the individual user and universe to which you want the restriction to apply.

2. Select the Rows tab.

3. Click Add.

4. Enter the WHERE clause to append to every query, or click >> in the Where box to call the Where Clause Definition box.

5. Under Tables and Columns, click + to expand the desired table name and double-click to select the individual column name you wish to restrict. In this example, SH.COUNTRIES.REGION_ID will be added to the first part of the WHERE clause.

   

6. Select the operator and double-click to add it to the WHERE clause.

7. If necessary, select a function to convert the condition to a particular format. You must manually enter the restriction; there is no list of values available on this screen. If your restriction is on a character field, enclose the restriction in single quotes. If it is numeric, quotes are not required.

8. Click OK two times to return to the Universe Properties dialog box, shown in the next illustration.

9. Click Check All to verify that the object still exists in the universe.

   

   Caution 

   Check All only verifies that the universe object exists in the repository; it does not verify SQL syntax or valid data values. If you enter either of these incorrectly, users will receive an error or no rows when a report or query is refreshed.

Row restrictions can be quite powerful but also problematic if restrictions are applied against multiple user instances and multiple groups. Row restrictions do not have priority levels; instead, all restrictions are used to generate the WHERE clause and are connected with AND. For example, if the Sales and Marketing group incorrectly contained a row restriction for REGION='Americas' and Helle’s individual user restriction contained REGION='Europe', Helle would have no rows returned since the two conditions are appended to the query with an AND connector.

Database Views vs. Row Restrictions

Companies that have unique logins to the data source often create views for each user or group of users to accomplish the same thing as Supervisor’s row restrictions. To implement this, the DBA would create a security table that contains each user and a column with the data values for each restriction. The security table is then joined to the fact or dimension tables to ensure users see only their own data.

There is no best solution for how to accomplish row-level security. Views may be easier to implement for many users with multiple security restrictions. Views are database-specific, so if your company uses more than one BI tool, the security model is open and independent of the tool. However, too many views may confuse a database optimizer, and queries may not be processed as efficiently. Unless the DBA creates an application to maintain the row restrictions, security becomes centralized with the DBA.

Simple WHERE clauses generated through Supervisor will leverage the optimizer. However, the security settings are not seamless and can be more difficult to maintain for larger user groups. The benefit, though, is that supervisors are often power users who know which data a user needs to see; the security may be better maintained.

Table Mappings

Table mappings provide another way to implement row-level security. Table mappings allow Supervisors to rename the base table in a universe with a different table name. In order for table mappings to work, the column names from the original table used in the universe must be exactly the same as the column names in the mapped table.

To follow on the preceding example, I could have multiple tables, either views or physical tables:

• SALES that contains all sales data

• CHILD_SALES that contains a subset of data for PRODUCT_CATEGORY IN ('Boys','Girls')

• EURO_SALES that contains sales for REGION='Europe'

1. To remap a table, select the individual user or group and the universe to which you want the restriction to apply and click Universe Properties.

2. Select the Table Mapping tab.

3. Click Add.

4. Use the Select button to launch the table browser or enter the original table name in the master universe. If you use the Table Browser, click OK to return to the New Table Mapping dialog box, shown next.

5. Position your mouse in the Replacement Table box. Use the Select button to select the new table or enter the new table name—in this example, SH.EURO_SALES.

   

6. Click OK to return to the Universe Properties dialog box.

7. Click Check All to verify that the table names exist in the universe structure.

   

---