Tag Archives: 11g
How-to: Bridge Tables and Many to Many Relationships Demystified in OBIEE 11g
Bridge tables - entire books have been devoted to this concept, countless blogs write about it, and organizations offer entire classes dedicated to demystifying this idea. Ralph Kimball, creator of Kimball Dimensional Modeling and founder of the Kimball Group has written quite a few great articles discussing the theory of bridge tables.
Yet when researching for comprehensive guides on how to actually implement a bridge table in OBIEE 11g, the documentation available is either:
You can see in the above data set that:
PROBLEM 1 - Aggregation :
The number of good calls you received based on the above fact table is not 2100 - it's 300 + 500 + 200 = 1000
What if you don't care about aggregates? What if you just want a report that contains the employee, call type and a summation/grand total?
Notice how NUMBER_OF_GOOD_CALLS is repeated across multiple call types and the grand total is still incorrect. It's being duplicated due to the many to many relationship that exists between call type and employee. Furthermore, it paints an incorrect picture that NUMBER_OF_GOOD_CALLS is some how related to CALL_TYPE
should be modified to:
You can see from this example that we've taken the N:N dataset in the fact table and moved it into this bridge.
Yet when researching for comprehensive guides on how to actually implement a bridge table in OBIEE 11g, the documentation available is either:
- Out of date
- Contains implementation steps for OBIEE 10g which has since been deprecated
- Does not contain adequate detail
- e.g. missing key steps
This guide is going to outline the basic use case of a many to many relationship, how OBIEE 11g resolves this dilemma and how to successfully implement a bridge table model within the 11g platform.
First thing's first - what is a bridge table and why do we need it?
At its core, bridge table solve the many to many relationship we encounter in many datasets. Many to many relationships in itself are not "bad", but when attempting to conform a data set to a star schema - many to many relationships just do not work. Star schemas assume a one to many (1:N) cardinality from the dimension to the fact. This means "one attribute of a dimension row can be found in many rows of the fact table".
For Example:
- One job (job dimension) can be performed by many people
- You would see the same JOB_WID repeating in the fact table
- One employee (employee dimension) can have many jobs
- You would see the same EMPLOYEE_WID repeating in the fact table
- One call at a call center(ticket dimension) can have many ticket types
- You would see the same CALL_WID repeating in the fact table
- One patient (patient dimension) can have many diagnosis
- You would see the same PATIENT_WID repeating in the fact table
This 1:N cardinality is represented in OBIEE as (using call center/employee example) :
"Cardinality of '1' applied to the dimension and cardinality of 'N' applied to the fact'
But what happens when in the above examples, the cardinality is actually N:N?
For Example:- Many employees can have multiple jobs and each job can be performed by multiple employees
- Many patients can have multiple diagnosis and each diagnosis can be 'assigned' to many patients
- Many calls can have multiple call ticket types and each ticket type can belong to multiple calls
This many to many relationship is initially (and incorrectly) represented in OBIEE 11g as:
'Cardinality of '1' is applied to the two dimension tables and cardinality of 'N' is applied to the fact'
Any BI Architect should recognize the above model - it's a traditional star schema! If you stop here and decided to ignore the issue with your dataset and 'hope' OBIEE aggregates the model correctly, you're about to be disappointed.
Why star schemas dont work for N:N cardinality
Consider the following scenario: You're a call center manager and you want to capture the number of calls with positive feedback per employee. You also want to capture the type of calls an employee answers in any given day.
Upon analysis of the requirements you conclude that "each call received by an employee can have many call types and each call type can be answered by multiple employees".
For example:
- I answer a take a call that is classified as a 'new call', 'urgent', and 'out of state transfer' (three different call types) - this is the "each call received by an employee can have many call types".
- A colleague also received a phone call that is classified as 'out of state transfer' - this is the 'each call type can be answered by multiple employees"
Now let's put this data in a traditional star schema fact table as modeled below:
| ID | EMPLOYEE_WID | CALL_TYPE_WID | NUMBER_OF_GOOD_CALLS |
| 1 | 1 | 1 | 300 |
| 2 | 1 | 2 | 300 |
| 3 | 1 | 3 | 300 |
| 4 | 2 | 2 | 500 |
| 5 | 2 | 3 | 500 |
| 6 | 3 | 1 | 200 |
- EMPLOYEE 1:
- Has 3 different call types
- Has 300 positive reviews (NUMBER_OF_GOOD_CALLS)
- This metric is at the EMPLOYEE level and not the call type level!
- EMPLOYEE 2:
- Has 2 different call types
- Has 500 positive reviews (NUMBER_OF_GOOD_CALLS)
- This metric is at the EMPLOYEE level and not the call type level
- EMPLOYEE 3:
- Has 1 different call type
- Has 200 positive reviews (NUMBER_OF_GOOD_CALLS)
PROBLEM 1 - Aggregation :
- Employee 1 received 300 good calls
- Employee 2 received 500 good calls
- Employee 3 received 200 good calls
but due to the many to many cardinality of the data, the star schema duplicates the measures because each employee can take calls of many call types and each call type can be assigned to many employees!
PROBLEM 2 - Grand Totaling:
What if you don't care about aggregates? What if you just want a report that contains the employee, call type and a summation/grand total?
Notice how NUMBER_OF_GOOD_CALLS is repeated across multiple call types and the grand total is still incorrect. It's being duplicated due to the many to many relationship that exists between call type and employee. Furthermore, it paints an incorrect picture that NUMBER_OF_GOOD_CALLS is some how related to CALL_TYPE
How do we resolve this many to many cardinality with a bridge table?
When all is said and done, the incorrectly built star schema:
Let's break this down:
The bridge table:
This the purpose of the bridge table is to resolve the many to many relationship between the call type and employee. It will contain, at a minimum, the following four columns:
- The primary key of the table
- The EMPLOYEE_WID
- The CALLTYPE_WID
- The weight factor
The weight factor is what's going to resolve the issue of double counting.
- If an employee has 3 call types, there would be 3 rows and the weight factor of each row would be .33
- If an employee has 10 call types, there would be 10 rows and the weight factor of each row would be .1
In our bridge table data set, we're going to use the same 3 EMPLOYEE_WIDs and create the following:
| ID | CALL_TYPE_WID | EMPLOYEE_WID | WEIGHT |
| 11 | 1 | 1 | 0.33 |
| 12 | 2 | 1 | 0.33 |
| 13 | 3 | 1 | 0.33 |
| 23 | 2 | 2 | 0.5 |
| 24 | 3 | 2 | 0.5 |
| 31 | 1 | 3 | 1 |
The dimension that is joined to both the fact and bridge
This is a generic dimension that contains the unique EMPLOYEE IDs in your organization's dataset.
For example:
| ID | EMPLOYEE_ID |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| 10 | 10 |
The dimension that is joined to only the bridge table
This dimension contains all of the possible call types. Note how this table is not physically joined to the fact. This is because this specific dimension (CALL_TYPE) is what's causing the N:N cardinality
For example:
| ID | DESC |
| 1 | Call Type 1 |
| 2 | Call Type 2 |
| 3 | Call Type 3 |
| 4 | Call Type 4 |
| 5 | Call Type 5 |
| 6 | Call Type 6 |
| 7 | Call Type 7 |
| 8 | Call Type 8 |
| 9 | Call Type 9 |
| 10 | Call Type 10 |
The Fact Table
We've moved the N:N cardinality from the original fact table to the bridge table so the new fact table now contains exactly one row per employee and does not have the CALL_TYPE_WID.
| ID | EMPLOYEE_WID | NUMBER_OF_GOOD_CALLS |
| 1 | 1 | 300 |
| 2 | 2 | 500 |
| 3 | 3 | 200 |
How do we implement this model in OBIEE 11g?
Step 1: Import Tables into Physical Layer
This is always the first step performed when creating a model regardless of its type. In the above example i'm importing four tables:
Step 3: Create the Logical Data Model
The creation of the BMM is where we deviate from our standard build steps of a traditional star schema:
Step 3.2: Create a 2nd LTS in the existing dimension table
This is always the first step performed when creating a model regardless of its type. In the above example i'm importing four tables:
Step 2: Create the Physical Data Model
Based on our data set above the join conditions would be implemented as follows:
- 1:N relationship from employee dimension to fact table
- 1:N relationship from employee dimension to bridge
- 1:N relationship from call type dimension to bridge
Notice how employee_demo_d is the only dimension that is joined to the fact. w_call_type_d is not joined to the fact because that is the dimension that is causing the many to many relationship issue.
Step 3: Create the Logical Data Model
The creation of the BMM is where we deviate from our standard build steps of a traditional star schema:
- All associated dimension tables referencing the bridge table will be stored in a single BMM table
- The single BMM table will have two logical table source
Step 3.1 : Drag the fact table and dimension table that is connected to the fact table into the BMM.
In our example, we are dragging w_calls_f and w_employee_demo_d into the BMM:
Step 3.2: Create a 2nd LTS in the existing dimension table
- Right click W_EMPLOYEE_DEMO_D -> New Object -> New Logical Table Source
- Name it 'Bridge'
- Add W_BRIDGE_D and W_CALLTYPE_DEMO_D (the two dimensions not directly joined to the fact table) under the 'Map to these tables' section
- Next add the remaining dimension columns from W_CALLTYPE_DEMO_D and W_BRIDGE_DEMO_D to the Dimension table in the BMM
Step 3.3: Create a level-based dimension hierarchy for the dimension BMM
- This step should be completed whether or not the schema is a star or bridge
Step 3.4: Confirm the BMM model has a 1:N relationship from the dimension to fact
Step 3.5: Set aggregation rule of NUMBER_OF_GOOD_CALLS to sum
All measures in the BMM must have a mathematical operation applied to the column
Step 3.5: Set the Content level of the dimension table to 'detail' in within the LTS of the fact table
Again, this is something that should always take place regardless of the type of model
Step 4: Create the Presentation Layer
This part is straight forward, just drag the folders from the BMM into the new subject area:
The moment of truth
So why did we go through this elaborate exercise again? To fix the aggregation issues we were having with NUMBER_OF_GOOD_CALLS due to the N:N cardinality of the data set. Let's create that 'standalone KPI' Number of Good Calls:
Notice how the metric correctly sums to 1000. Let's check the back end physical query to confirm:
Notice how it's hitting the fact table and not the bridge or the call type dimension.
But what about the weight factor?
Let's go back to the scenario where we want to compare across dimensions joined via the bridge table (EMPLOYEE and CALL_TYPE):
- When creating a report that uses a measure from the fact table, a dimension value from the the employee table, and a dimension value from the table that causes the N:N cardinality - you need to use the weight factor to make sure your measure isn't getting double or triple counted:
 |
In order for grand totaling to work correctly with bridge table measures that use weight facts you must set the aggregation rule of the column (in this case column 1) to sum within Answers:   So what did we accomplish in this guide?
|
keywords: bridge table, cardinality, many-to-many, OBIEE 11g, helper table, answers, analytics, aggregation, LTS, measures, kimball
FYI: GoURL Primer with OBIEE 11g
The Oracle BI Presentation Services Go URL command is for use in incorporating specific Oracle Business Intelligence results into external portals or applications. The Go URL is used when you add a result to your favorites, or add a link to a request to your dashboard or an external Web site. It has a number of forms and optional arguments that can be used to control its behavior.
Oracle has provided limited documentation on goURL parameters and with use cases that require end users to navigate to specific dashboards or request dashboards be embedded into pre-existing web pages, i've decided to create a Primer on commonly used GoURL functionality:
Common goURL Parameters:
Passing Filters
Oracle has provided limited documentation on goURL parameters and with use cases that require end users to navigate to specific dashboards or request dashboards be embedded into pre-existing web pages, i've decided to create a Primer on commonly used GoURL functionality:
Common goURL Parameters:
| Parameters | Syntax | Definition |
|---|---|---|
| NQUser | &NQUser=x | User ID |
| NQPassword | &NQPassword=x | Password |
| Path | &Path=x | Path of the answer to execute. You can find it in the properties page (Answers/Manage Catalog/ and click on the properties icon (a little hand) of the object that you want. See picture above |
| Link Options | &Options=x | The x can be one or more of the following letters: * m : Modify Request * f : Printer Friendly * d : Download to Excel * r : Refresh Results |
| Printer Friendly | &Action=print | Results are in a printer-friendly format, without the paging controls, hot links, and so on. |
| Passing Filters | &Action=Navigate | To apply filters to the answer (see section below on Passing Filters) |
| Application Friendly | &Action=Extract &Action=Scroll | Results are displayed in an application-friendly format, such as for Microsoft Excel, without the paging control, hot links, and so on. The Extract action also acts as a Navigate action (read Passing Filters to the Oracle BI Presentation Services Go URL Through a URL (Navigation)) so you can filter the results that are returned by the call. |
| Specific View | &ViewName=x | This shows an individual result view rather than the default compound view |
| Specific View | &ViewID=go~x | This shows an individual result view rather than the default compound view where x is the name of the view |
| Specific Style | &Style=x | This shows the results using a specified style. If the style does not exist, the default is used. |
| Result Format | &Format=x | This controls the format of the results. This is the format, where x can be xml, html, txt (tab separator), csv (comma separator) |
| File Extension | &Extension=.csv | This controls the file extension of the download file |
| Language | &Lang=fr | This controls the language of the report. The value permitted are the values of weblanguage |
| done | &done=portalPages | parameter allows you to create a return link equal to a path that you have specified |
You can use the &Action=Navigate in conjuction with the with the parameters below to pass filters directly to a report via goURL. Use &P0=n where n equals the number of parameters you wish to filter and P1...to P6 with the one or more of the operators below:
| Operator | Meaning |
|---|---|
| eq | Equal to or in. |
| neq | Not equal to or not in. |
| lt | Less than. |
| gt | Greater than. |
| ge | Greater than or equal to. |
| le | Less than or equal to. |
| bwith | Begins with. |
| ewith | Ends with. |
| cany | Contains any (of the values in &P3). |
| call | Contains all (of the values in &P3). |
| like | You need to type %25 in place of the usual % wildcard. See the examples that follow. |
| top | &P3 contains 1+n, where n is the number of top items to display. |
| bottom | &P3 contains 1+n, where n is the number of bottom items to display. |
| bet | Between (&P3 must have two values). |
| null | Is null (&P3 must be 0 or omitted). |
| nnul | Is not null (&P3 must be 0 or omitted). |
| &P2=ttt.ccc | In this parameter, ttt is the table name and ccc is the column name. If the table or column contains spaces, it must be quoted with double-quotes. Spaces should be escaped as %20, for example, Measures."Dollar%20Sales". |
| &P3=n+xxx+yyy+...+zzz | In this parameter, n is the number of values, and xxx, yyy, and zzz are the actual values. Note: If the value of P3 begins with a numeric character, the entire value must be enclosed in quotes. example: saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate&P0=1&P1=top &P2=Customers.Region&P3="7 |
Passing Filters Examples:
his returns records for the East and Central regions:
Saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate&P0=1&P1=eq&P2=Customers.Region&P3=2+Central+East
This returns records for like Regions E....t:
saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate&P0=1&P1=like&P2=Customers.Region&P3=1+E%25t
This returns the top two regions by dollars sold:
saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate&P0=1&P1=top&P2="Sales%20Facts".Dollars&P3=1+2
This is an example where the number of arguments is not included in the syntax:
saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate&P0=1&P1=top&P2=Customers.Region&P3=Central
This returns records with between 2,000,000 and 2,500,000 in sales:
saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate&P0=1&P1=top&P2="Sales%20Facts".Dollars&P3=2+2000000+2500000
This returns records for Regions beginning with the letter E:
saw.dll?Go&Path=vate&P0=1&P1=bwith&P2=Customers.Region&P3=1+E
This returns records for Regions containing the letter E and having more than 20 million in sales:
saw.dll?Go&Path=/Shared/Test/SB2&Action=Navigate&P0=2&P1=cany&P2=Customers.Region&P3=1+e&P4=gt&P5="Sales%20Facts".Dollars&P6=1+20000000
Generating SQL Statements using goURL:
The Go URL command can be used to issue Oracle Business Intelligence SQL. These forms of the Go URL return tabular results. The basic options from &Style= and &Options= can be used here as well.
To issue Oracle Business Intelligence's simplified SQL, include the escaped SQL as a parameter to the Go URL. For example:
saw.dll?Go&SQL=select+Region,Dollars+from+SupplierSales
where the FROM clause is the name of the Subject Area to query.
Alternatively, the command IssueRawSQL can be used to bypass the Web processing and issue SQL directly against the BI Server.
keywords: OBIEE 11g, goURL, answers, ad-hoc, analysis, analytics
How-to: Automatically Refreshing Dashboards in OBIEE 11g via iFrame Bursting- The Flat Screen TV Use Case
With production costs associated to flat screen LCD/plasma/LED TVs dropping into the 100s of dollars over the past few years, these TVs are no longer relegated to the home theater space. Now enterprises can utilize these products as part of their campaign to bring analytics to the forefront of their business. Consider the following scenarios:
Navigate to:
Note the two important components highlighted in red:
- A call center needs a mechanism to communicate performance metrics to the office without daily emails, meetings and paper-printed dashboards
- An IT organization needs to track hardware utilization 'in real time' with passive notifications activated during peaks and irregular activities
- The firm's testing team needs the ability to communicate defect and resolution metrics against service level agreements across multiple teams
Short of daily emails and unnecessary meetings, it was up to the individual worker to take the initiative to stay informed. The above scenarios can be effectively resolved through the use of 'Dashboard Displays' on flat screen TVs strategically placed throughout the office to keep leadership informed and team members accountable.
When we think of how OBIEE is used, the typical scenario that comes to mind is a user logging into the analytics front page. While little documentation exists, it is certainly possible to transform OBIEE from a 'user-based interactive analytics tool' to a 'dashboard style display fitted to large monitors'.
The main issue OBIEE Architects face is how resolve the issue of stale data across the technology stack. Stale data can exist within the BI Server, BI Presentation Services and even in the browser. Each component has its own resolution technique as well as potential issues. When a dashboard is displayed on a static monitor, a mechanism must be in place to ensure it's refreshed and timed correctly with existing agents.
This guide is going to outline the steps required to implement OBIEE for the above scenarios. The technology used includes:
Add the following red code to the file:
Navigate to:
Step 1.3 Modify web.xml
Decompress the analytics.war and analytics-ws.war file using 7zip and modify the web.xml in each war file by adding the red code to the file:
When we think of how OBIEE is used, the typical scenario that comes to mind is a user logging into the analytics front page. While little documentation exists, it is certainly possible to transform OBIEE from a 'user-based interactive analytics tool' to a 'dashboard style display fitted to large monitors'.
The main issue OBIEE Architects face is how resolve the issue of stale data across the technology stack. Stale data can exist within the BI Server, BI Presentation Services and even in the browser. Each component has its own resolution technique as well as potential issues. When a dashboard is displayed on a static monitor, a mechanism must be in place to ensure it's refreshed and timed correctly with existing agents.
This guide is going to outline the steps required to implement OBIEE for the above scenarios. The technology used includes:
- iFrame Bursting (disabled by default in OBIEE 11g)
- Oracle WebLogic to redeploy modified XML components
- goURL
- HTML (Meta Tags)
- Oracle BI 11.1.1.7.0
Some blogs on the web recommend using javascript to achieve this but I do not recommend this approach as javascript execution is based on the permission of the browser and has the potential to be disabled. At the very least a pop up box will appear asking the user if they authorize the javascript function to execute - not a pleasant end user experience.
Furthermore, Oracle has acknowledged that the mechanism they developed for auto-refresh does not work and no known resolution exists - see Doc ID 1416002.1 and Bug 13058029. What is interesting is that Oracle's method used to work in OBIEE 10g!
Step 1: Enable iFrame Bursting
By default, OBIEE 11g server does not allow dashboards to be displayed in an iFrame. The reason this is done is to better secure the dashboards and prevent hackers from using an iFrame to overlay malicious content.
Step 1.1. Navigate to export/obiee/11g/instances/instance1/config/OracleBIPresentationServicesComponent/coreapplication_obips1 and modify instanceconfig.xml
Add the following red code to the file:
<Security>Step 1.2 Download analytics.ear
<InIFrameRenderingMode>allow</InIFrameRenderingMode>
</Security>
Navigate to:
/export/obiee/11g/Oracle_BI1/bifoundation/jee and download analytics.earThe analytics.ear file contains two war files that house the web.xml that needs to be modified.
Step 1.3 Modify web.xml
Decompress the analytics.war and analytics-ws.war file using 7zip and modify the web.xml in each war file by adding the red code to the file:
<servlet-name>RelatedContent</servlet-name>
<url-pattern>/RelatedContent</url-pattern> </servlet-mapping>
<context-param> <param-name>oracle.adf.view.rich.security.FRAME_BUSTING</param-name> <param-value>never</param-value> </context-param><login-config> <auth-method>CLIENT-CERT</auth-method> </login-config>
Step 1.4 Upload & Recompress the war files
Upload the two war files back to /export/obiee/11g/Oracle_BI1/bifoundation/jee
Step 1.4.1 Recompress analytics.war
Navigate to:
/export/obiee/11g/Oracle_BI1/bifoundation/jee/analytics/analytics
Execute:
jar cvf0 ../analytics.war
(Note: Include the space and period at the end of the command)
Navigate up one level to:
/export/obiee/11g/Oracle_BI1/bifoundation/jee/analytics
Delete the analytics folder so that the only files that remain in that directory are:
(1) META_INF, (2) analytics.war, and (3) analytics-ws
Step 1.4.2 Recompress analytics-ws.war
Navigate to:
/export/obiee/11g/Oracle_BI1/bifoundation/jee/analytics/analytics-ws
Execute:
jar cvf0 ../analytics-ws.war . (Note: Include the space and period at the end of the command)Navigate up one level to:
/export/obiee/11g/Oracle_BI1/bifoundation/jee/analytics
Delete the analytics-ws folder so that the only files that remain in that directory are:
(1) META_INF, (2) analytics.war, and (3) analytics-ws.war
Step 1.5 Recompress the analytics.ear file
Navigate to:
/export/obiee/11g/Oracle_BI1/bifoundation/jee/Rename the old analytics.ear file as a backup
Execute: jar –cf analytics.ear –C analytics . (Note: Include the space and period at the end of the command)e)Delete the analytics folder.
Make sure that the analytics.ear file was produced in the export/obiee/11g/Oracle_BI1/bifoundation/jee/ folder.
Delete the analytics folder.
Step 2: Restart BI Services
This can be achieved by either running opmnctl stopall via command line or through Enterprise Manager as depicted below:
Step 3: Create a test HTML file with the embedded iFrame:
Open a text editor of your choice and paste in the following:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Frameset//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-frameset.dtd">
<HTML>
<HEAD>
<TITLE>Important Dashboard</TITLE>
<META http-equiv=no-cache>
<META content=900 http-equiv=refresh>
</HEAD>
<BODY>
<IFRAME style="Z-INDEX: 998; POSITION: absolute; WIDTH: 100%; HEIGHT: 100%; TOP: 0px; LEFT: 0px" src="http://yourdashboard.com/ImportantDashboard" frameBorder=no scrolling=auto> </IFRAME>
</BODY>
</HTML>
Note the two important components highlighted in red:
- The Meta tags forcing the automatic refresh every 900 seconds.
- The iFrame that embeds the URL of your dashboard
- The ability to display dashboards on a static device (large monitor) that will have little human interaction but requires up to second (or in this case every 900 seconds) data
- The ability to embed a dashboard in an iFrame.
keywords: OBIEE 11g, iFrame, auto-refresh, answers, web.xml, instanceconfig.xml, goURL, analytics
How-to: Image Referencing with OBIEE 11g
In a typical OBIEE engagement, the client may want to utilize out of the box or custom images within various dashboards and reports. This requirement leads to many open questions, including:
So - if we work under the assumption that the above folder is indeed the root directory, then it we now know why the image returns a broken link, report_good_percentange.jpg is not stored in the 'root' directory of the analytics web server, it is actually stored in:
keywords: fmap, answers, OBIEE 11g, image customization
- Where are these images located?
- How do I embed the image into a dashboard or report?
- How do I maintain the integrity of the image URL across multiple environments?
Where are these images located?
All images are stored in the 'browser look and feel plus' folder of the BI Server, you've probably seen this notated as 's_bflap'. This folder exists in two locations and it is critical that any image you upload be housed in both:- Oracle_BI1bifoundationwebappress_blafpimages
- user_projectsdomainsbifoundation_domainserversbi_server1tmp_WL_useranalytics_11.1.17dezjlwarress_blafpimages
How do I embed the image into a dashboard or report?
OBIEE 11g has a little known feature called 'fmap' which can be used to display an image based on the relative URL of the image. Little documentation exists on it other than a few notes released by Oracle which include:- How To Display Custom Images Using Fmap In OBIEE 11g (Doc ID 1352485.1)
- Image FMAP on Linux (Doc ID 491154.1)
How do I maintain the intregrity of the image URL across multiple environments?
Here is where things get tricky due to the lack of documentation that exists. Let's say you want to use the image 'report_good_percentage.jpg' located in the s_blafp folder:
So as outlined in Oracle's documentation you use 'fmap:report_good_percentage.jpg' or even 'fmap:images/report_good_percentage.jpg', but to your dismay all you see is a broken image link:
Why?
It is important to remember that fmap displays the image of the relative URL. So what does relative mean? What is 'it' relative to? In regards to fmap, the relative URL is the root directory of the analytics web server, which in OBIEE 11g is:
Which makes sense if you understand how applications are deployed in weblogic. The presence of the WEB-INF directory in the aforementioned folder is how Weblogic determines if a folder if a deployable application directory.
/export/obiee/11g/user_projects/domains/bifoundation_domain/servers/bi_server1/tmp/_WL_user/analytics_11.1.1/7dezjl/war
So - if we work under the assumption that the above folder is indeed the root directory, then it we now know why the image returns a broken link, report_good_percentange.jpg is not stored in the 'root' directory of the analytics web server, it is actually stored in:
/export/obiee/11g/user_projects/domains/bifoundation_domain/servers/bi_server1/tmp/_WL_user/analytics_11.1.1/7dezjl/war/res/s_blafp/imagesLet's update the fmap relative url to correctly reference report_good_percentage.jpg by modifying it to:
fmap:res/s_blafp/images/report_good_percentage.jpgUnfortunately..
Why does fmap STILL not work?
Let's take a look at the URL that's actually being generated:
Notice anything funny? Why is OBIEE 11g adding a 'Missing_' folder to the URL directory? Countless bloggers have theorized this as a bug in OBIEE and some even suggest making a 'Missing_' folder in the root directory of the analytics web server. I don't think that is the best approach because as you deploy this application across multiple servers, you'll have to make sure all environments have that same folder. Keep it simple right?
We can resolve this by modifying the fmap url to revert one directory closer to its root by using '/..':
fmap:/../res/s_blafp/images/report_good_percentage.jpg
Let's check the URL being generated just to make sure:
The image displays, and the 'Missing_' folder is no where to be found. If your requirements have extensive image customizations, perhaps changing the entire look and feel, I recommend deploying an entirely new skin as outlined in Oracle Note: How to Use Custom Images in OBIEE (Doc ID 1484623.1)
FYI: Troubleshooting Security with the Oracle BI Security Diagnostics Helper
The hundreds of BI Security notes on Oracle Support, the countless blogs dedicated to OBIEE 11g security configuration, the myriad of configuration scenarios, test conditions, and troubleshooting steps documented in Oracle's Fusion Middleware Guide for Oracle Business Intelligence - what does all of this mean? The configuration of security within the OBIEE 11g platform is easier said than done.
With the release of OBIEE 11g, Oracle has made efforts to mitigate (or at least rule out) the potential risks associated with security configuration in their new weblogic-centric model. This is achieved through a little known helper utility called the 'Oracle BI Security Diagnostics' tool. This tool gives developers the ability to:
And finally a success message "Added code grants to bidiagnostics" which really just means the EAR file is ready for deployment in AdminServer.
With the release of OBIEE 11g, Oracle has made efforts to mitigate (or at least rule out) the potential risks associated with security configuration in their new weblogic-centric model. This is achieved through a little known helper utility called the 'Oracle BI Security Diagnostics' tool. This tool gives developers the ability to:
- Diagnose security configuration issues within OBIEE
- Test BISystemUser configuration settings
- Test authentication of users
Step 1: Install the Oracle BI Security Diagnostics helper via WLST
In your unix terminal, execute the following command:e.g.
MW_HOME/ORACLE_HOME/common/bin/wlst.sh addDiagnosticsCodeGrant.py t3://<WebLogic_host_name>:<WebLogic_port_number>
mw_home/Oracle_BI1/common/bin/wlst.sh addDiagnosticsCodeGrant.py t3://localhost:7001You're going to be taken through a series of terminal interactions starting with the initalization of WLST:
Followed by log-in credentials to connect to WLST:
Step 2: Deploy bidiagnostics.ear to AdminServer
In Admin Console (default 7001/console), navigate to Deployments -> Control tab and look for a deployment called 'bidiagnostics (11.1.1)' . Check the corresponding box and click 'Install':
The deployment is a straight forward process, and after the installation is complete just make sure you set the bidiagnostic application to 'Active'.
Step 3: Access the BI Diagnostic Helper
Once the installation is complete, you can navigate to the application by accessing the following url:
http://mycomputer:7001/bidiagnostics/security/diagnostics.jspYou'll be taken to a very simple UI that will give you the opportunity to perform basic security diagnostics and (hopefully) resolve any OBIEE 11g security issues you may encounter!
