Nov 05 2008
Designer XI 3 New Feature: Class Restrictions
Designer has provided the option to create “stub joins” in a universe for quite a few years. A stub join is a permanent where clause on a table that appears as a small stub rather than a full join connecting two tables. This feature has often been used to apply security to a table via a reference to the @Variable(’BOUSER’) value.
However this strategy had one major flaw in that if the table was not referenced in the query then the security would not be applied. I now have a much more flexible (and powerful) option in Designer 3: Class Restrictions.
Stub Join Review
Here is what a stub join looks like in the structure window.
The extra stub hanging off of the table includes the following syntax:
RESORT_COUNTRY.COUNTRY_ID = 1
The intent of this simple restriction is to apply a restriction so that the only country allowed to be displayed in this universe is the US, or Country ID of 1. Does it work? Sort of.
If I create this query:
… here is the SQL code generated:
SELECT
RESORT.resort,
SERVICE_LINE.service_line,
SERVICE.service
FROM
SERVICE,
SERVICE_LINE,
RESORT
WHERE
( RESORT.RESORT_ID=SERVICE_LINE.RESORT_ID )
AND ( SERVICE_LINE.SL_ID=SERVICE.SL_ID )
If I include the country object in the query, then this is the SQL code generated:
SELECT
RESORT_COUNTRY.country,
RESORT.resort,
SERVICE_LINE.service_line,
SERVICE.service
FROM
SERVICE,
SERVICE_LINE,
COUNTRY RESORT_COUNTRY,
RESORT
WHERE
( RESORT_COUNTRY.COUNTRY_ID=RESORT.COUNTRY_ID )
AND ( RESORT.RESORT_ID=SERVICE_LINE.RESORT_ID )
AND ( SERVICE_LINE.SL_ID=SERVICE.SL_ID )
AND ( RESORT_COUNTRY.COUNTRY_ID = 1 )
The important difference is that the first query does not include the intended restriction. Why not? Because the stub join is only included when the affected table is included in the query. This is a fairly substantial drawback to this technique.
Class Restrictions
Here is where this new feature shines. I can create a class restriction that is applied to every object from the class. The philosophy is similar; a stub join is applied to every element from a table; the class restriction extends the concept to a universe class. Is this powerful?
If I create the same query as above:
… here is the SQL generated:
SELECT
RESORT.resort,
SERVICE_LINE.service_line,
SERVICE.service
FROM
SERVICE,
SERVICE_LINE,
COUNTRY RESORT_COUNTRY,
RESORT
WHERE
( RESORT_COUNTRY.COUNTRY_ID=RESORT.COUNTRY_ID )
AND ( RESORT.RESORT_ID=SERVICE_LINE.RESORT_ID )
AND ( SERVICE_LINE.SL_ID=SERVICE.SL_ID )
AND
( RESORT_COUNTRY.COUNTRY_ID = 1 )
Look carefully, and you will see that the RESORT_COUNTRY table is not included in the SELECT clause at all, yet the restriction is still applied to the query. How was this done?
Building a Class Restriction
I start the process using the same technique that I would use to build a standard predefined condition in the universe. I create the new condition with the appropriate logic, as shown in the screen here. The new features are configured using the options on the lower corner of the dialog box.
All I have to do is check the “manditory” box and select Apply on Class and the condition is applied to every object from that class. If I create additional objects and insert them into that class, these new objects will automatically inherit the same condition restriction without additional work from me.
And it gets better. 
Building a Universe Filter
Here is a closer view of the options on the dialog box:
There are two choices: Apply on Universe and Apply on Class. Hmm.. I wonder what the first choice does?
I selected the Apply on Universe option and created a query that referenced several different tables in order to derive the sales revenue. Here is the SQL that was generated:
SELECT
sum(INVOICE_LINE.DAYS * INVOICE_LINE.NB_GUESTS * SERVICE.PRICE)
FROM
INVOICE_LINE,
SERVICE,
SERVICE_LINE,
COUNTRY RESORT_COUNTRY,
RESORT
WHERE
( RESORT_COUNTRY.COUNTRY_ID=RESORT.COUNTRY_ID )
AND ( RESORT.RESORT_ID=SERVICE_LINE.RESORT_ID )
AND ( SERVICE_LINE.SL_ID=SERVICE.SL_ID )
AND ( SERVICE.SERVICE_ID=INVOICE_LINE.SERVICE_ID )
AND
( RESORT_COUNTRY.COUNTRY_ID = 1 )
This is powerful stuff. I could create a hidden class in my universe, create any number of predefined conditions as universe filters, and leverage the @variable(’BOUSER’) restriction to apply security to the entire universe. No matter which objects a user selects for their query, my security would get applied 100% of the time. I like it. 
Conclusion
Stub joins were often used to apply security via a reference to the BOUSER variable. The problem with this solution is that the security was only applied to that table. If the query did not reference that table, the security didn’t get applied. The work required by the designer at this point was substantial… he or she would have to work through the entire universe and add that table to every object reference.
As a universe designer I now have the option to create a special condition at a class level. I can even create a condition that will be applied accross the entire universe. This is good stuff, and a feature that I am sure will be well received by universe designers everywhere.
This feature was one of several mentioned in my Designer Essentials presentation delivered at the 2008 GBN conference. This particular feature was on page 11.
Nice coverage Dave. I’m looking forward to using this new feature, just haven’t found the opportunity yet!
- Josh
Hi, Josh, thanks for your comment. This is, to be honest, one of my favorite new features in the 3.x release of Universe Designer. With this new options universe designers don’t have to go to every object and force them to include a specific table just to get security applied.
This feature has been extremely useful in the executive dashboard project that I’m currently working on, forcing a condition using @Variable(’BOUSER’) to achieve row-level security on Xcelsius 2008 dashboards using Query as a Web Service (QaaWS). Especially because QaaWS does not yet support (even in XI 3.1) universe restrictions, another common way to add row-level security to universes. BTW, when you make the condition mandatory, Designer automatically hides the condition object from the end-user allowing designers the flexibility to place the condition in the class where it makes the most sense. As an enhancement, though, it would be nice if Designer made a visual distinction between a mandatory condition and a condition object that I hid manually.
Question:
Let’s say I have a universe with 2 contexts (C1 and C2). A table TabA belongs to only one of them (C1). I create a universe filter based on TabA. Now I will create a query that is covered only by C2. What will happen? Will the universe filter not be applied to the query? Will it end up with a warning/error message? Or will I get a Cartesian product?
I can not test it because I dont have XI 3.
Thank you.
I suspect you will get the famous “Incompatible combination of objects” message if you used a universe filter in that case. You have created a scenario that can never work (a universe filter on a table that doesn’t exist in your context).
But this is just a guess.
Dave,
Thanks for this great post! I have been asked by at least 4 different clients about this security set up and with XI R2, the best we get was just half-baked. I can’t wait to get my hands dirty with XI 3.0 now!
Cheers!
I’m using exactly this approach for row-level security in a current project. I really appreciate the usefulness of this new XI 3.0 feature. MUCH easier than the stub join + all of the object maintenance for forced tables. Plus, the checkbox makes it easy to turn on/off for testing.
This is a good feature.. i worked a lot on stub joins ..but they were never helpful.
Now this is a good feature !
I’m wondering how different this newly introduced class restriction from the existing XIR2 security functionality.
Here is my illustration of a typical row level user restriction case .
Suppose we have 3 fact tables fct-1,fct-2,fct-3 and an user security table “sec_tbl” with two columns, country_cd and user_id
option-1: class restriction method
1. sec_tbl need to be joined to all three fact tables in designer
2. create filter with class restriction like sec_tbl.user_id = @Variable(’BOUSER’) applied to only fct-1,fct-2,fct-3 class.
now pull any object from fct class. the query will be like:
select fct-1.xx
from fct-1
,sec_tbl
where fct-1.country_cd = sec_tbl.country_cd
and sec_tbl.user_id = @Variable(’BOUSER’)
(The security table wont be that huge hence the extra join doesnt affect the performance of query.)
option-2: using BO designer’s manage security option:
1.Creat three restriction sets to the three fct tables like
fct-1.country_cd in (select distinct country_cd from sec_tbl where sec_tbl.user_id = @Variable(’BOUSER’))
fct-2.country_cd in (select distinct country_cd from sec_tbl where sec_tbl.user_id = @Variable(’BOUSER’))
fct-3.country_cd in (select distinct country_cd from sec_tbl where sec_tbl.user_id = @Variable(’BOUSER’))
2. Assign the above restriction set to group(s) in designer.
Now here is the query:
select fct-1.xx
from fct-1
where fct-1.country_cd in (select distinct country_cd from sec_tbl where sec_tbl.user_id = @Variable(’BOUSER’))
(Here the subqury is also not that costly)
One advantage of option-1 is, filter is applied even the filter table is not referenced, but to make the force join work the security table needs to be joined to required tables in universe.
Take an example of addition of new fact table..both the options need changes.option-1 add a new join and that of option-2 add a new restriction set.
2nd example: add a new object to the existing class, option-1 works great so as option-2.
Am i missing any advantage of the new feature over the existing one..
Hi Dave, Thanks for the useful info. I have one question though.
Does the restriction automatically gets applied to the Sub Classes as well?
hi dave,
i have a question:
I have three classes. ‘Event’ class, ‘Pmt’ class, ‘Tkt’ class.
Event class has
EventNo, EventName, EventID, VenueID.
Rest of the classes, Pmt and Tkt classes having measure objects.
My qurey’s are:
1) Select *
from event, tkt
where
event.ctseventid = tkt.ctseventid and venueid = 81
2) Select *
from event, pmt
where
event.ctseventid = pmt.ctseventid and venueid = 82
Note: I’ve joined this ‘tkt’ table to some other tables also. If I am going to give the condition ‘venueid = 81’ in the ‘tkt’ table, that will restrict the result for some other reports. That I don’t want.
Same for ‘Pmt’ table also.
If the only choice is ‘event’ table, can you pls tell me how it is possible??
sorry… mistakenly i typed as table instead of class. read those as classes.. ie event class, pmt class and tkt class. no tables.
Thanks Dave, nice summary, will love this feature in xi r3. i have so many universes using stub join to have security join. With this feature, the functionality now very simple.