Not able to change Presentation Table names

Welcome to OBIEE 12c.. With the default BI Admin Tool configuration, you cannot change any object's name from presentation layer..

If you try to change the Table Name from a presentation layer, you would see Name field is not editable at all as in below screenshot.

To enable this, Go to Tools > Options
Select check-box for "Edit Presentation Names"

You can now see the option for Name is enabled to edit into it..

Happy Blogging..!!

Start - Stop OBIEE Services - 12c

Welcome to OBIEE 12c.. :)

As we already know, a lot of OBIEE developers life became an easy from the time we are experiencing OBIEE 12c or onwords version of the same.. The basic and day to day life tasks for OBIEE Admin / Developers is to upload a repository or restart of servers. This was really a time consuming tasks with 11g version of OBIEE. Here with 12c, we can just run a single command to upload a new repository and everything else will be taken care by this utiliy.. This is a link to the detailed article to upload a new rpd. Upload Repository Commands - downloadrpd & uploadrpd

Same is with the restart of services. There is no need to Google "Restart sequence of OBIEE 11g services". Oracle has provided a simple and single command line utility to start or stop all the OBIEE stack, this includes Admin Server + BI Servers + BI Components. This utility is available for both the OS types i.e Unix and Windows too.. Below is the step (yeah, singular.!!) to stop the services,

Go to the mentioned location and run stop.cmd

C:\Oracle\Middleware\Oracle_Home\user_projects\domains\bi\bitools\bin

To start the services, Run start.cmd

Happy Blogging..!!

Installing OBIEE 12c on Windows 7 - 64 Bit

1. Verify your system environment:

Before beginning the installation, verify that the minimum system and network requirements are met.

Verifying Certification, System Requirements, and Interoperability

Obtain the following Oracle distributions:

Download the Oracle Fusion Middleware 12c Infrastructure and Oracle BI (12.2.1) distributions from any of the following locations:

Oracle Technology Network or Oracle Software Delivery Cloud.

Here's the link for OTN

http://www.oracle.com/technetwork/middleware/bi/downloads/default-3237328.html

Install prerequisite software:

Install the Java Development Toolkit (JDK) and the Oracle Fusion Middleware Infrastructure to create Oracle home, and to install the Oracle Fusion Middleware software directory for further installations.

Set below environmental variable,

JAVA_HOME=C:\Java\jdk1.8.0_101

2. Install the Oracle Fusion Middleware software:

Run the Oracle BI installer to install Oracle BI software in the same Oracle home where you installed the Oracle Fusion Middleware Infrastructure infrastructure.

• Open command prompt in an Administrator mode and run the below command to start the installation,

• Wait for a moment to see the Welcome screen of Fusion Middleware.

•  Click next to continue just to skip Automate updates.

 

• Click Next to select home location of Fusion Middleware.

• Click Next.

• The installer will then perform prerequisite checks, specially the supported OS and JDK version before starting the installation.

• You can un-check the box and go ahead if you are not interested in security/update mails from Oracle Support.

• Click Install and wait to finish.

• Click Finish and see the summary of the installed product.

3. Install the Oracle Business Intelligence software:

The OBIEE 12c software comprises just two of the files. 

For Windows 7-64 bit, 

fmw_12.2.1.2.0_bi_windows64_Disk1_1of2.zip 

fmw_12.2.1.2.0_bi_windows64_Disk1_2of2.zip

Unzip these files into 1 directory. Please be informed that, fmw_12.2.1.2.0_bi_windows64_Disk1_2of2.zip wll get expand into another zip file, DO NOT unzip it again.

• Simply double click the setup file, and follow the below screenshots

• Specify the home for this installation. This should be the same home as the Fusion Middleware installed earlier. If confused, select it from drop down option. ;-) 

• Specify whether you want SampleAppLite installed with this installation or not. As this is first time I'm installing 12c, I need to play around the new things in this so called latest version. 

• We get installation summary. Notice that, unlike OBIEE 11g, we are not asked which components of OBIEE to install – BI Enterprise Edition, BI Publisher or Essbase. The software install always gives you all three, however later during the configuration we can decide which components we actually want setup. 

4. Create RCU

You can create the database schema either using the Repository Creation Utility (RCU) or using the Oracle Business Intelligence 12c Configuration Assistant.

Follow the below screenshots to complete this straight forward approach,

Configuring the Domain

In this final part I will perform the configuration of OBIEE 12c. This will setup the domain with Fusion Middleware, configure the BI components and most importantly setup the URLs so you can connect!

This is performed with the configuration utility which can be found under <obiee_home>/bi/bin

Be informed that, there are two config.sh (config.cmd for windows) files, but in different directories.

<obiee_home>/bi/bin contains the one for standard single server installations

ORACLE_HOME/oracle_common/common/bin contains the one for enterprise deployments. This is far more comprensive than the single server installation and allows you to configure multiple instances on multiple servers.

Run the Oracle Business Intelligence 12c Configuration Assistant tool to configure the BI domain.

• Select the choices as per your requirement. 

• Then the standard prerequisite checks are performed to ensure the server is configured correctly. 

• Let the default values be there and select the weblogic password to login to EM, Console and Analytics. 

• Enter the details of schema that you created earlier. 

• The summary screen then displays showing the settings you’ve selected and main URLS for accessing the weblogic console, enterprise manager, OBIEE and BI Publisher. Save this file for future references.
• Click on Configure to start the configure process. This can take considerable time to configure and start all the components installed so far.
• Click Finish.

Once you go successfully through all this, you are all set to use your brand new BI system. ;-)

Enter below URL to open Analytics, http://obieenow.localdomain:9502/analytics

Best Practices to Improve Query performance In OBIEE

Hi all,

Got fed up of this question?? Yes, me too.. So penning down some of the recommended practices to make better your query performance.

1. Dashboard should be as interactive as possible: column selectors, drill-down, guided navigation… Interactivity is one of the best assets of Oracle BI. Use it.
2. Check if time is spent on BI Server or database (response time and physical query duration versus compilation time). Normally, time spent on BI Server should not exceed few seconds. Otherwise, analyze the steps done on BI Server to find the cause
3. Are all tables included in this query really necessary? Do we have tables that are joined but are not included in select clause and do not have filters applied (real filters, not join conditions)?
4. If optimizing the SQL is not enough, look with a DBA at execution plan and find out the root cause of performance issue. Globally there are mainly four ways to improve performance at this point:
• Reducing volume of IOs by improving data access path.
• Reducing volume of IOs by reducing the volume of data read.
• Review the filters applied.
• Increasing parallelism (number of thread used to read big tables)
• Improving IO speed (hardware improvement, in-memory...)
5. Tend to star data model in repository
6. Use hints in physical layer of the repository
7. Use fragmentation
8. Use aggregation
9. Set the no. of elements
10. Use the where clause in LTS if possible
11. Apply index on column from DB side only
12. Get long running physical queries, check explain plan and make change accordingly to DB tables or from OBIEE.
13. Try to use cache seeding wherever possible
14. Implement data level security to lower the data set pulled from DB
15. Reconfirm that aggregate/fragmented tables are getting used properly
16. Various configuration options can be used to limit the maximum number of rows that can be downloaded, processed, rendered, included in mail, etc.

That's it what I can think of. There can be many more, please feel free to add in comment section.

Happy blogging.. :-)

 

Bridge table in OBIEE 11g, with example

Generally, OBIEE only supports one-to-many relationships but in some rare cases we have to use many-to-many relationships in our data model. To achieve this, we use the bridge table.

Consider a scenario where there is a sales representative who is participating in many deals that pay commission. Additionally, each deal may include many sales representatives so that each sales representative receives a percentage of the commission. In such case, we must model this many-to-many relationship in the repository using a bridge table concept.

Suppose F_COMMISSION fact table with commissions paid per invoice. D_SALESREP dimension table will have data for sales representative. D_CUSTOMER contains the data for each customer.

Now we will design bridge table COMMISION_BRIDGE in such a way that it will create a many-to-many relationship between the F_COMMISSION fact table and the D_SALESREP dimension table. COMMISION_BRIDGE should have a weight factor to calculate the weighted distribution of commissions among sales teams.
A rough physical diagram for the above scenario will look like as below,

Hope this helps, happy blogging..!! :-)

Generate a metadata dictionary in OBIEE 11g (Manual Method)

Keep in mind, you can generate metadata only in offline mode of a repository. Also note that this is Windows-Only Utility.

• Open the repository using the OBIEE Administration tool in offline mode.
• Navigate to Tools > Utilities > Generate Metadata Dictionary > Execute 

• Specify a directory location where you want this metadata should get exported, here I have given C:\OBIEE_Things\Metadata

• You should see a below message on a completion of this task.

• Now copy WEB-INF folder from $INSTANCE_HOME\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes to C:\OBIEE_Things\Metadata
• This would create a directory named as your repository name under the specified location. Here, "ABC".
• Rename this directory to the name as it is on the server. This is a mandatory step.

Note that, you can find this name in NQConfig.INI configuration file or else go to EM > Business Intelligence > coreapplication > Deployment > Repository. Here, it is ABC_BI0010

• Now login to Console (with admin privillage) to deply this metadata as an application.
• Deployments > Lock and Edit > Install.

• Specify Path as C:\OBIEE_Things\Metadata and select the radio button for ABC.

Note that, without copying WEB-INF folder to the metadata directory, the ABC directory cannot be deployed as an application in Weblogic, an application in weblogic needs deployment descriptor file (WEB-INF/web.xml)

• Click Next > Select Install this deployment as an application (this is selected by default)

• Click Next > Select bi_server1 > Click Next.

• Select I will make the deployment accessible from the following location > Click Next.

• Select Yes, take me to the deployment configuration screen (this is selected by default) and click Finish.

• Click Activate Changes.
• Once the changes are activated, click on Control tab > Lock and Edit > select ABC > Select start > Servicing all requests. > Click Yes > Click Release Configuration.

• Edit instanceconfig.xml and add the following after first ServerInstance tag:
• Location of instanceconfig.xml is $INSTANCE_HOME\config\OracleBIPresentationServicesComponent\coreapplication_obips1

<SubjectAreaMetadata>

<DictionaryURLPrefix>http://obieenow.localdomain:9704/Metadata/</DictionaryURLPrefix> 

</SubjectAreaMetadata>

• Save the changes.

• Go to EM and restart the services.
• Log in to analytics as user with BIAdministrator privileges. 
• Click Administration > Manage Privileges > Access to Metadata Dictionary. Make sure the users have Access to Metadata Dictionary privilege.
• Log in as user who has Access to Metadata Dictionary. 
• Click New > Analysis > Select Subject Area > Click on Metadata Dictionary. 

• This would open a new window with Metadata Dictionary details. This is nothing but the presentation layer to the BMM layer mapping.

• Click on the Logical Column Mapping - Dollars. This will present you the BMM layer to the physical layer mappings.

Happy blogging.. :-)