I have a star schema with one fact and 10-12 dimensions. I have to restrict data content by office i.e office1user lgs in and will be able to see only data for office1. office2 users logs in and will be able to only data for offic2.
One way to restrict this is by having a sql condition for the user on the universe in supervisor on the fact where office = <office1userās office>
How about restricting dimension values to only those values valid for that office. For example to limit browsing employees from an employee dimension a query like this could be executed via the fact:
select emp_name from employee_dimension where emp_id in
(select emp_id from fact where office = <office1userās office>
how can something like this be forced (i.e a condition on the fact table even though just the dimension is being browsed).
I know there is a way to force joins via designer. Is that the way to go ?
That would mean that if I wanted to have a similar behviour on all dimensions I would have to force joins on each dimension in the designer.
Just a tip if you have many dimension objects:
Look at stub-joins (self restricting joins as well).
Since you are going against the fact table (for your subquery to limit your dimension) you might want to make sure an index on the column FactTable.Office has been implemented and can be utilized.
This can be done at Designer level. You have to change the definition of each OBJECT that has been defined from EMP table. You need to open the object properties and click on the tables button. It will list all the tables in the universe with only EMP table selected. Now you need to CTRL+select OFFICE tableā¦ This will make sure that whenever this object is dragged into Query Panel, both these tables should be used to generate the SQL.
I know of this feature already(forcing joins on every table in the universe). I was wondering is there some way you could force browsing via the fact even if the user wants to simply browse the dimension. This way we could force constraints on the fact to filter out appropariate rows for the user.
I just want to know whether the tool(s) (supervisor, designer) allow you to specify a contraint on a fact in āone placeā and have that constraint applied every time you browse any table in the dimension and not have to specify additional joins on every dimension in the universe.
I dont want any discussion or input on why go through the fact when somebody just wants to browse dimensions etc. I just want to know if the tool can do it, thats all.
I donāt think there is a āone placeā where you can apply a constraint on a FACT which affects all dimensions, within the Business Objects Tool set. You donāt say the database that you are using, Oracle, for example has Fine Grain Access control which might help, there are probably similar things in other DBs too.
I have put constraints on my LOVs by using a join to =@Variable(āBOUSERā)
My dimensions are built from Derived tables (availabe in 6.5) as an alternative to using two tables in an object definition.
(BOB member since 2002-06-20)
(BOB member since 2002-09-23)
(BOB member since 2002-09-05)