The other day while working on data level security within OBIEE (BI Server), I found something very interesting I would like to share. While testing my data level security for various combinations of groups, I noticed that for one subject area I was not having my security filters applied.
That should not and can not be, I said to myself. I looked into and traced the presentation column I was using to the related logical tables. It just came up with the logical table which I applied the filter on. So why did it not add the filters? I must have tried different things, looked here and there with no avail. So I called it a night and would come back in the morning and try something different.
Behold the next day, with a fresh mind I found the culprit! It was a logical table (“Fact – Fins – AP Activity Compound”) which has no LTS (As seen below)!
Instead the entire logical table and its logical columns are derived off of other logical table columns and thus inherit their definitions. [To find out I selected all the logical columns and ran display related->logical columns. Below is what came back]
So that is why I was seeing my physical table (alias) which I applied the data level filter on within the physical queries in the NQSQuery.log. But, and here is the kicker, since it was not coming from the logical table that I added the filter too, it did not apply the filter.
Solution: I looked at all the logical tables which were used by that logical table which make up its logical columns to gather the conformed dimensions I want to filter on. Then I added the filter on this logical table. Viola, it is now added the filters to the query as expected.
Lesson Learned: If you apply data level security to a logical table, then it will be for that table and nothing that derives off of it.
Read Full Post »
I came across this bug/issue that some may encounter. The issue is with the page controls for the table view and them not working. If you come across it that the page controls do not work or navigate to the next page of records, then the first thing to look at is your compound view which is the default view set within dashboard pages. This only happens in compounds views! Look to see if there are any other views that have been deleted but not deleted in the compound view like here:
Now within the compound view, if you delete a view it just deletes it from the compound view and not the actual view itself. It also stands that if you delete the actual view it does not remove the reference within the compound view and thus what you see above may result and paging within table views will no longer work for those dashboards using this compound view. Reference is the key as compound views just store a reference to the views that compromise the compound view itself. So when a view is deleted, make sure that if it was referenced in the compound view, that the reference is also deleted.
Read Full Post »
Its been a very, long time since I posted. And for just cause, as I have been very busy working OBIEE and OBIA projects. I have noted down along the past year interesting finds which I will post in the coming months. Of which, I will begin with an issue I came across using Oracle 10GR2, STAR_TRANSFORMATION_ENABLED = TRUE and the WITH Clause. I came across this via one of the reports generated in OBIA within the Financial Analytics.
While testing reports, I came across this error: ORA-32036: unsupported case for inlining of query name in WITH clause. From Oracle what this means: “There is at least one query name which is inlined more than once because it’s definition query is too simple and references another query name. This is currently unsupported yet.” The Solution from Oracle: “remove such query name and retry”. Upon further investigation within Metalink and on the web this was a bug within the RDBMS and is fixed with the 11.2 patch set. Or is included within release 12.1. So how does this help me? We are not even on 11G db yet.
The one thing I noted was, this did not exist before within this environment. So what did I change? The only thing was that I added ALTER SESSION SET STAR_TRANSFORMATION_ENABLED=TRUE within the database conn pool connection script. Before, the instance did not have this set within the db init params and I wanted to utilized the star schema setup using bitmaps and such for OBIA and its best practice for this setup. Viola! This error pops up. Take off it goes away.
Solution for me, was one of 2 things, as there is no upgrade path in the near future for us to move up to 11G with that patchset in the near future.
1) Don’t use the STAR_TRANSFORMATION_ENABLED and potentially have slower performance than I would get with it.
2) Keep the STAR_TRANFORMATION_ENABLED = TRUE and turn off the support for the WITH clause. I opted for #2 as I did see better performance with this enabled and to me the WITH clause was not that important as it just recreates the SQL query not using the WITH clause.
Read Full Post »