Oracle BI EE 11g – Automating Password Updates of Connection Pools and Users – Command Line Options

If you had gone through my blog entry here, i would have talked about using UDML as a way for automating migration from dev to test/prod environments. But the major problem with UDML is that one cannot update the passwords of Connection Pools, Users etc since the UDML expects them to be encoded. In such a case, for updating passwords alone we can use an undocumented command line switch option for the AdminTool. For example, look at the screenshot below.
     
This is nothing but the connection Pool property of the ORCL database. Here, we would like to change the schema names and its corresponding passwords. For example, we would like to change the schema to SH and also would like to change the password. In order to do that, open a text editor and enter the below command. Save the file in the same directory as the Admin Tool ({OracleBI}\Server\Bin). If not, then you would have to give the full path of the file.
1 Open Dev_Machine.rpd Administrator Administrator
2 SetProperty "Connection Pool" "orcl"."Connection Pool" "User" "sh"
3 SetProperty "Connection Pool" "orcl"."Connection Pool" "Password" "welcome1"
4 Save
5 Exit
Now, open up a command prompt and navigate to {OracleBI}\Server\Bin. Typein in the below command,
1 admintool /command commandlineswitch.txt
     
This would automatically update the connection pool values.
     
You can use the above to update many objects within the repository. For updating passwords for users, just use the nqschangepassword.exe tool. For more details on this, check my blog entryhere. So, on a high level your entire migration process would look as shown below
     
Thanks to Phil for sharing this.

Oracle BI EE 11g – Public Reports and Dashboards – Bypassing authentication

Another common question that keeps circulating is, “how do we bypass login to certain BI EE Dashboards?”. The most obvious answer to this question is to pass the username and password through the url. But certain security requirements/standards in some companies do not allow passing of passwords through the URL. So, lets look at a simple approach wherein some dashboards can be seen without passing the passwords through the url. The idea is pretty simple. Identify a user, for example PUBLIC, that would get direct access to certain dashboards. This user should not exist in the BI EE repository. All the other users would exist in the BI EE repository. So, the first step to achieve this is to create a simple init block with the below shown sql.
1 select ':USER' from dual where upper( ':USER' ) = upper( 'Public' )
Make the above init block to set the USER system session variable. Also, check the “Required for Authentication” check box. The init block can connect to any oracle database connection pool.
     
Now, if you use the below shown go url, the dashboards would be shown. As you see we are not passing the passwords through the go url.
     
The above approach uses the concept of external table authentication. Basically, the init block checks for the PUBLIC user and ensures that the user is authenticated without even checking for the password. All the reports/dashboards that can be viewed by the “Everyone” privilege can be accessed by this PUBLIC user. Very simple but can be used in certain situations.

Oracle BI EE 11g – Row Level Security and Row-wise Intialized Session Variables

As you would probably know, BI EE provides the capability to do row-level security from the Business Model layer. There was a question the other day wherein a user wanted to know how to do row-level security, wherein more than 1 filter value come from some other table. For example, lets consider the standard SH schema that comes with Oracle Database. The BM for this schema would look like the one shown below
     
Consider another table PROD_SECURITY containing the columns USERNAME and PROD_CATEGORY.
     
So, basically, the above table provides the list of users and their corresponding product category for which the users have access to. Now, in our BM, the sales and the product table are joined at the Product level. Each product category can have multiple products and hence we would have to use row-wise initialized session variables to achieve this security. So, start with creating an init block which would populate a session variable PROD_FILTER with all the prod ids belonging to the product category for which the user has access to. The init block would use the sql below
1 SELECT 'PROD_FILTER',PROD_ID
2 FROM PRODUCTS A, PROD_SECURITY B
3 WHERE A.PROD_CATEGORY = B.PROD_CATEGORY
     
Once this done, use the below statement in the where clause of the content tab of the Sales data table.
1 ORCL."".SH.SALES.PROD_ID IN ( VALUEOF(NQ_SESSION."PROD_FILTER"))
     
So, basically the row wise initialized variable would be initialized to (100,200,….) etc. Lets check whether the security is getting properly applied from answers. Since, we have not secured on the dimension table, lets first include the products column in our report and see what happens to our report
     
As you see, it would list down all the product categories. Now lets include the AMOUNT_SOLD column from the fact table into our report.
     
As you see, the security has been applied and the user Administrator would be able to see only 2 categories for which he has access to.

Oracle BI EE 10.1.3.3.3/2 – Image Maps, HTML and GO URL

I wanted to blog about something very simple today which came to me as a question from two different users. If you had noticed my blog entry here, i would have shown how to go about creating image prompts. Lets look at another approach today that will give more control to reporting users. I will be using the same image as used in the previous blog entry (shown below)
     
So, our idea is to filter a report based on the year values displayed above i.e for example when a user clicks on 2000, then our aim is to open up a report with a year=2000 filter. So, lets start with a simple report as shown below.
     
Ensure that you have the prompted clause filter on Year. Then go to the narrative view of this report and enter the below HTML.
1 <body>
2 <img src="<a href="http://lh4.ggpht.com/krisvenky83/RysmfbTfQiI/AAAAAAAAAzQ/jcvF5eclvXc/Snap4.jpg">http://lh4.ggpht.com/krisvenky83/RysmfbTfQiI/AAAAAAAAAzQ/jcvF5eclvXc/Snap4.jpg</a>" usemap="#YearPrompt" border="0">
3 <map id ="YearPrompt"
4 name="YearPrompt">
5 <area shape ="rect" coords ="120,7,173,28" href="http://localhost:9704/analytics/saw.dll?Go&Path=/shared/Paint%20Demo/Image%20Map/Report&Options=rmf&Action=Navigate&P0=1&P1=eq&P2=Periods.Year&P3=2000"target=_new/>
6 <area shape ="rect" coords ="119,65,172,89"  href="http://localhost:9704/analytics/saw.dll?Go&Path=/shared/Paint%20Demo/Image%20Map/Report&Options=rmf&Action=Navigate&P0=1&P1=eq&P2=Periods.Year&P3=2001"target=_new/>
7 <area shape ="rect" coords ="118,124,174,145" href="http://localhost:9704/analytics/saw.dll?Go&Path=/shared/Paint%20Demo/Image%20Map/Report&Options=rmf&Action=Navigate&P0=1&P1=eq&P2=Periods.Year&P3=2002"target=_new />
8 </map>
9 </body>
     
As you see, what this above html does is it produces an image map and assigns the GO URL of the different reports to different map coordinates. So, when you click on Year 2000, the 1st url would pop up. Same would be the case for other years. Or the other option is to put the above HTML map directly in the dashboard Text Object as shown below.
     
Now, this Image Map would work without even using the Image Prompts. One good thing about this approach is that you can control the positioning of your target report.
     
     
Simple, but a better option than image prompts since you have more control.