Framework Manager 8.3 - Data Security Bug? {SOLVED}

[praveennb]

Maybe its a Bug in 8.3, I'm not sure anyway i wanted to share my experience here.

After upgrading the models from FM 8.2 to FM 8.3 everything worked fine, except
i ended up having a Major performance issue with reports with data security enabled, the reports ran for 20-30 Mins which earlier took just 10-15 secs!

--- Background Info --------
Using Series 7 security
2 databases named Details and Common using SQL 2005

the details database contains the fact and dimension data
the common database contains the security access tables and other tables

The Initial Model was built using FM 8.2 Version with 2 data sources
one for details and another for common.

Sample design
Product table - details database
Productcode  Prodname
01               xxxxx
02               yyyyy

productSecurity table - common database

Productcode  Userclass
01               ZoneA
02               ZoneB

In the FM model, after importing the tables,
the productSecurity  query is  modified as below

Select productcode From Security
where
   UserClass in
   (#CSVIdentityNameList()# )

the above productSecurity   query is joined to products query using 1:1 Join in FM

On the product query data security filters were applied, with criteria
product.Productcode=productSecurity.productcode

----------------------------

The above used to work great for BI 8.2 reports.

Something strange happened in version 8.3, when using the above approach for data security, my guess is that
that the reports are pulling all the data from the details database and then applying the security filters
and joins, instead of applying the joins and security filter to get the initial data.

Solution
After spending Days/hours, finally managed to the solve issue by
1.Creating a productSecurity view on the details database which points common database.
2.Create a new query subject based on productSecurity view and modify the query similar to the existing productSecurity
3.Delete the common datasource from the FM Model.

Now the reports are run time is back to normal.

Hope this helps.

[UseCog]

Thanks for this usefull information