Applying Mandatory Data Restrictions

To ensure that a restriction is always inferred when a particular object is used in an end-user query, place the restriction in the Where field of the Dimension Properties tab and use the SQL assistant. You can do this restriction when you create the object or you can add it later.

For example, consider the situation where users of Motors make queries related only to those cars that can be rented. In the Model table in the underlying Motors database, the distinguishing factor between cars that can be rented and those stocked for sale is that the MODEL_DAYRENT column contains data for rental cars and is null for sale-only cars. To create an object to list cars for rental, the object would have to include the restriction: MODEL.MODEL_DAYRENT IS NOT NULL

Drawback to Adding a WHERE Clause to ObjectsIf two or more similarly restricted objects are included in the same query, the conflict between the WHERE clauses causes no data to be returned. Consider a situation, where a user wanted data for UK clients and US clients. You might think that including both the UK clients and US clients objects would meet that need. However, the inferred SQL for the query would include the following WHERE clauses:

(COUNTRY_REGION.COUNTRY_NAME = United Kingdom) and (COUNTRY_REGION.COUNTRY_NAME = USA)

Since no country satisfies both these conditions, no data is returned.

There is an alternative to applying restrictions to objects without using WHERE clauses. You have multiple similar objects, but you avoid the conflicts that affect the return of data in queries. This method involves using a conditional SELECT clause for the object instead of a WHERE clause.

For example, if you want to force users to select financial results by year, you could create a series of Sales Revenue objects, one for each year. Each object would be created, starting from the standard sum aggregate used in the basic sales revenue SELECT statement.

sum(SALE_MODE.SALE_QTY*MODEL.MODEL_PRICE*(100-SALE.SALE_DISCOUNT)/100)

You can apply the condition for each year using the database function that applies IF THEN ELSE logic.

For Sales Revenue 2003, the SELECT statement appears as follows:

sum(CASE{fn year(SALE.SALE_DATE)} WHEN 2003 THEN (SALE_MODE.SALE_QTY*MODEL.MODEL_PRICE*((100-SALE.SALE_DISCOUNT)/100))ELSE 0 END)

For Sales Revenue 2004, the SELECT statement appears as follows:

sum(CASE{fn year(SALE.SALE_DATE)} WHEN 2004 THEN (SALE_MODE.SALE_QTY*MODEL.MODEL_PRICE*((100-SALE.SALE_DISCOUNT)/100))ELSE 0 END)

After you have created or edited the objects, test them individually and together in a single query. View the SQL to check whether the inferred SELECT statement includes the conditional SELECT syntaxes. The SQL appears as follows:

SELECT DISTINCT CLIENT.CLIENT_LASTNAME+','+CLIENT.CLIENT_FIRSTNAME, sum(CASE{fn year(SALE.SALE_DATE)} WHEN 2003 THEN (SALE_MODE.SALE_QTY*MODEL.MODEL_PRICE*((100-SALE.SALE_DISCOUNT)/100))ELSE 0 END), sum(CASE{fn year(SALE.SALE_DATE)} WHEN 2004 THEN (SALE_MODE.SALE_QTY*MODEL.MODEL_PRICE*((100-SALE.SALE_DISCOUNT)/100))ELSE 0 END) FROM CLIENT, MODEL, SALE, SALE_MODEL WHERE (CLIENT.CLIENT_ID=SALE.CLIENT_ID) AND (SALE.SALE_ID=SALE_MODEL.SALE_ID) AND (SALE_MODEL.MODEL_ID=MODEL.MODEL_ID) AND (SALE.SALE_TYPE='S') GROUP BY CLIENT.CLIENT_LASTNAME+','+CLIENT.CLIENT_FIRSTNAME

When the query is run, the report looks similar to this example:

The conditional SELECT statements remove the problem of the conflicting WHERE clauses. The data correctly shows the 2003 and 2004 Sales Revenue for each client.

If a table in your database has a flag that is used to choose between two or more domains, you can use this flag to apply restrictions at the table level.

For example, the SALE table in the Motors database has a column called SALE_TYPE, which is used to distinguish between sales transactions and rentals transactions. The flag is set to S for sales or R for rentals.

If you do not apply any restriction to this table, users running queries against the SALE table receive a report that includes data on rentals as well as sales.

With this restriction in place, however, the data returned is restricted to sales data, no matter where the table is used in the inferred SQL. For example, if the Sale table appears only in the FROM clause of the SQL, the restriction is still applied in the WHERE clause.

This is the main advantage of applying restrictions at the table level.

You can limit the data returned for an object to values from the table inferred by the object that also match values in another table.

In our example, the measure number of cars sold is based on the table SALE_MODEL. The table SALE_MODEL is not filtered to SALE_TYPE =S.

If you do not select the table SALES, where the column filter SALE.SALE_TYPE =S applies, then users running queries against the SALE_MODEL table receive a resulting report that includes all data. Therefore, the results are wrong.

The SALES_MODEL table is marked because the object Select referenced this table. If you run a query with the objects Maker and Number of Cars Sold, the following query script is generated:

SELECT "MAKER"."MAKER_NAME", sum("SALE_MODEL"."SALE_QTY") FROM "MAKER" INNER JOIN "MODEL" ON "MODEL"."MAKER_ID"="MAKER"."MAKER_ID") INNER JOIN "SALE_MODEL" ON ("SALE_MODEL"."MODEL_ID"="MODEL"."MODEL_ID") GROUP BY "MAKER"."MAKER_NAME"

The restriction on the SALE table : SALE.SALE_TYP=S is missing; this table will return wrong values for the query.