OBIEE Tuning - Under the hood

 Monitoring what to tune:

• Monitor performance using Enterprise Manager,
• OBIEE Admin/Session tool
• Usage Tracking
• Fiddler browser based client developer add-on's - see where is most of the time being taken for a page load and plan accordingly.
• Use Oracle Application Testing Suite (OATS) for Load Testing using a test strategy reflecting the load and type of usage.

Understanding Monitoring is very important  because Oracle's documentation is not very clear on how the settings effect the overall performance. They at best provide two values - default, and suggested, without much explanation. So if we want to customize these for the situation at hand, we should be able to monitor the effects of changes and tweak it to the what best applies to our situation.

Apply latest patches

• Upgrade to the latest patch set - follow Support documentation (Upgrade advisory) to plan major upgrades.
• Stay on approved Java version

Delegate work away from OBIEE server

Reverse Proxy/OHS Cache: OBIEE prevents the browsers from caching its content. This is a desired tract to ensure fresh reports data, but overloads the BI Presentation server/OHS as all the static content now needs to be served for each and every request.

This can be mitigated by implementing a Reverse Proxy server or enabling caching on Web server.

Optimized connections

The Connection Pools to the underlying databases should be enough to be able to reasonably service the expected number of concurrent users. Pools also exists for network connection and here, the timeout should be reduced so that they can be freed more often and made available for subsequent calls. There are various parameter based on the underlying OS to achieve this:

• Database Connection Pools for these should be reviewed:
• Database connection in the physical layer:
• Init-Block Connection Pools
• Physical DB Connection Pools
• BIP/MDS Connection Pools (others if Essbase/ODI are installed)
• Inter-Process connection pool:increase the bridge connection pool for Presentation server to sufficient level
• Enable compression at web server to ensure high throughput
• Ensure sufficient search depth within the AD of choice. This can be result in additional complexity if there is a cyclic membership.

Thread Management

•     Manage Stuck thread trigger time: 
• Account for long running queries from the usage tracking parameters 
• Ensure that the system doesn't flag threads as stuck in that time frame.  
•     Increase thread pool for charting engine, job manager

Turn Off Debugging features on production:

•     Log levels
•     Wrap Data Type (JDBC)
•     Lock down production RPD to read only mode
•     Database client libraries - disable signal handling

JVM - Memory

• Increase Heap
• Compressed Reference
• Thread Local sizes based on available physical memory for OBIEE

Cache management:

• Move cache and temp directories(javaHost/BIP/BIPS) to RAM disk (~256 GB)
• Use cache seeding. Automate using SASeedQuery after ETL process.
• Adjust maximum cache size based on frequently used/first page reports
• Increase number of open files limit in OS to allow for multiple concurrent processing of cache/sort/logs etc.

Aggregate data

Reduce Database's processing cycles, and the network bandwidth needs.

Hardware Optimization

• Maximize Memory and Networking capability of each server. 
• OBIEE licensing is core based. Addition servers will result in added licensing cost. So maximize the memory and network capacity on the serve before scaling out.
• Throw in more hardware, vertically scale the system for performance, get high availability as an added benefit.

Sizing of Server

Factors used to determine the capacity of a server:

•     Total Named user vs Concurrent users
•     SSL turned on or not.
•     complexity of the Report formats
•     Underlying database and the connection between the two servers

BI Publisher

•     Ensure appropriate JDK/JVM
•     Optimal memory and disk space allocation
•     Use BI Publisher's XSLT processor (Enable scalable feature of XSLT processor)
•     Divide optimization efforts by
• Online/Concurrent Usage
• Report size - Small(~10 Pages)/Medium(~50 Pages)/Large(> 100 pages)/ Extremely large (> 100,000 rows)
• Bursting - Total number of report vs. time window
•     Run-time optimization
• Enable XSLT Processor, enable scalable features
• Enable Muti-Threading
• Increase Thread Count
• Increase FO Buffer size
• Increase Pages in Caches setting
• Enable Run-Time Cache
• Disable Autorun
•     Use JNDI over JDBC
•     Push database functions like Joins to Database (Use OBIEE models)
•     XML
• Use short names (smaller overall XML)
• Use Unique names across structures
• Avoid XML attributes
• Use Absolute paths for Searches rather than Relative paths
•      Scale out
•     Configure debug settings to minimal for production

Finally some functional optimization

• Rearrange dashboards so that the smaller/faster reports are rendered first
• Show summary reports (from aggregates) first
• Force users to put appropriate filters in prompts
• Partition database
• Index appropriately
• Run Statistics and keep them current

Moniter, Adjust, Repeat!

Sachin
Architect - Oracle Engineered Systems
Exalytics/Exalogic/Exadata
BuzzClan LLC

BuzzClan is a business consulting company collaborating to provide Oracle software advisory services & implementation services. BuzzClan LLC is committed to providing substantive business value on each and every client engagement. We do this through a combination of industry-specific business expertise, technical skills, proven project management methods and our “onsite - off site - offshore” delivery model. We strive to work in partnership with our customers to build high-performance teams and create business solutions that will last.

Engineered Systems VS. NIST Definition of Cloud Computing

According to the NIST,

Cloud computing is a model for enabling ubiquitous, convenient, on-demand network access to a shared pool of configurable computing resources (e.g., networks, servers, storage, applications, and services) that can be rapidly provisioned and released with minimal management effort or service provider interaction. This cloud model is composed of five essential characteristics, three service models, and four deployment models.(http://csrc.nist.gov/publications/nistpubs/800-145/SP800-145.pdf)

In this blog, I will examine how each of the five essential characteristics defined by NIST (in bold/italics) applies to the engineered systems - Exalogic, Exadata, and Exalytics.

On-Demand Self-Service:  

A consumer can unilaterally provision computing capabilities, such as server time and network storage, as needed automatically without requiring human interaction with each service provider.

This refers to the ability of cloud services consumers to configure and maintain the landscape presented to them. This allows for distributed administration capabilities. While the centralized team concentrates on managing the infrastructure tasks like initial setup, capacity planning and resource quota allocation, backup, disaster recovery, patching, libraries of Virtual Machine (VM) templates, middleware code, etc., the application-level administration is delegated to the consumers. They can create VMs from the template library and extend them according to their needs.

To enable the consumers to do this, several administration consoles are provided by the Cloud Infrastructure to allow for GUI, Command Line Interface (CLI) or scripted interaction with the system.

Oracle’s engineered systems provide the following self-service capabilities:
 •  Cloud admin account access
• Enterprise Manager Operations Center (EMOC) access 
• Virtual Data Center (vDC) Management tab with access to pre-built Virtual Machine templates for frequently used functions
• Base VM templates to build upon
• Command Line Interfaces (CLI and dCLI) access to most of these tasks for automation and one-touch unilateral provisioning

Broad Network Access:  

Capabilities are available over the network and accessed through standard mechanisms that promote use by heterogeneous thin or thick client platforms (e.g., mobile phones, tablets, laptops, and workstations).

Oracle’s engineered systems are generally used for private cloud offerings within secured corporate or public sector settings. Therefore, access to these is not required on a mobile device. The systems provide the access over SSL/SSH using the following: 

• Browser-based interfaces (EMOC/VM Manager/ZFSManager)
• CLI interface over SSH

Resource Pooling:  

The provider’s computing resources are pooled to serve multiple consumers using a multi-tenant model, with different physical and virtual resources dynamically assigned and reassigned according to consumer demand. There is a sense of location independence in that the customer generally has no control or knowledge over the exact location of the provided resources but may be able to specify location at a higher level of abstraction (e.g., country, state, or datacenter). Examples of resources include storage,  processing, memory, and network bandwidth.

This, I believe, is the most important characteristic of a cloud offering. With commodity servers running independently, we have seen that there is often a heterogeneous usage of resources. Most of the servers are underutilized while some can never have enough resources to service the load, creating a bottleneck referred to as 'underutilized server sprawl'. With an IaaS scenario, this can be fixed by dynamically rearranging the load on a particular resource. The engineered systems allow for resource quota for the users/accounts (Account Resource Limit).

• Oracle Virtual Machine Server - Virtualizer
• Virtual CPU/CPU oversubscription
• Common memory pool
• Common storage
• Shared network access  

Rapid Elasticity: 

Capabilities can be elastically provisioned and released, in some cases automatically, to scale rapidly outward and inward commensurate with demand. To the consumer, the capabilities available for provisioning often appear to be unlimited and can be appropriated in any quantity at any time.

We currently follow these steps in order to change the memory, vCPU, and/or the Network resource allocation for an existing guest vServer (Reference - Exalogic: Changing the Memory, vCPU and Network Resources for an existing guest vServer (Doc ID 1586296.1)):

1. Stop the vServer
2. Create a template
3. Create a vServer type with the required memory and vCPU resources
4. Delete the old server
5. Create a new vServer by using the template in step #2 and the vServer type created in step #3

I am hopeful that upcoming releases will make this process more streamlined and easier to manage.
Ideally, to ensure elasticity, the virtual servers should have memory, vCPU, network bandwidth, and storage quotas within a range. The servers should also have a priority definition which will help mitigate resource contention issue in case multiple vServers are trying. The vServer should be able to negotiate the resources based on its priority among other servers, the current load on the vServer, and the Virtual Server as a whole.
The disk space is shared among all the compute nodes and virtual machines.  Shares and projects can be created by the cloud admins to be mounted on the VMs. These shares can also be shared among multiple VMs.
Resource quotas are established at a cloud-account level. Any VM created within the account has access to the resources allocated to the account.
What’s missing?

• Networking quota
• Prioritization (if a resource is oversubscribed, which account/VM/process gets access to the resource if the physical limit is reached)
• Time slot-based allocation   

Measured service:

Cloud systems automatically control and optimize resource use by leveraging a metering capability at some level of abstraction appropriate to the type of service (e.g., storage, processing, bandwidth, and active user accounts). Resource usage can be monitored, controlled, and reported, providing transparency for both the provider and consumer of the utilized service.

Enterprise Manager 12c provides the capability to meter the usage and calculate chargeback of different resources (targets).  A universal charge plan contains rates for CPU, Memory and storage. Extended charge plans can be used across various target types. Details can be found in the OEM - Chargeback Administration.

What’s missing? 

• This functionality is not available for Exalytics servers yet. However, in an OBIEE implementation, certain Usage Tracking reports can be configured to provide this functionality.

Tech Term of the Day 

NIST - National Institute of Standards and Technology


Sachin
Architect - Oracle Engineered Systems
Exalytics/Exalogic/Exadata
BuzzClan LLC

BuzzClan is a business consulting company collaborating to provide Oracle software advisory services & implementation services. BuzzClan LLC is committed to providing substantive business value on each and every client engagement. We do this through a combination of industry-specific business expertise, technical skills, proven project management methods and our “onsite - off site - offshore” delivery model. We strive to work in partnership with our customers to build high-performance teams and create business solutions that will last.

Certified - Exalytics In-Memory Machine X3-4 Implementation Essentials

Hurrey!!

Today I got the result of the Exam I gave back in Dec. 

I have cleared the "Exalytics In-Memory Machine X3-4 Implementation Essentials" exam!, earning a OPN Certified Specialist designation in this field.

Together with the  "Exalogic Elastic Cloud X2-2 Certified Implementation Specialist" certification, it helps me differentiate myself as a proven expert in the field of Oracle Engineered Systems implementations.

Getting ready to crack the Exadata certification next!

Sachin
Architect - Oracle Engineered Systems
Exalytics/Exalogic/Exadata
BuzzClan LLC

BuzzClan is a business consulting company collaborating to provide Oracle software advisory services & implementation services. BuzzClan LLC is committed to providing substantive business value on each and every client engagement. We do this through a combination of industry-specific business expertise, technical skills, proven project management methods and our “onsite - off site - offshore” delivery model. We strive to work in partnership with our customers to build high-performance teams and create business solutions that will last.

OBIEE - Multi-tenancy User Authentication and Authorization

To facilitate Multi-Tenancy in OBIEE, a few new roles have been introduced in OBIEE. A new layer has been added which allows for administration at a tenant level, and another one to define users/authors at the tenant.

• BI Global Administrator
• Tenant Administrator
• Tenant User

Earlier posts in this series:
 OBIEE - Multi-Tenancy implementations - What is it
 OBIEE - Multi-Tenancy - Presentation Catalog

When OBIEE is configured for Multi-tenancy, there are two administration are available to configure the application. BI Global Administrator role (BIGlobalAdministrator) is used for overall global administration. This administrator controls privileges for all tenants and can access the Presentation Services Administration page, Oracle BI Administration Tool, Job Manager, Catalog Manager, and all content. This administrator is not associated with a specific tenant.

The new role, BITenantAdministrator have specific privileges that are granted in the Oracle BI Presentation Catalog for administering a tenant. Users in this role can perform. 

This role enables users to perform self-service administration tasks on one tenant. These administrators cannot access overall Presentation Services Administration page or the Privileges page. These administrators organize content for tenant users within the catalog by granting access to, creating, moving, and copying objects and folders.

Another User role, Tenant User, are equivalent to the BIAuthor and BIConsumer roles, but gives access to the artifacts within a particular tenant. 

Each Tenant in the system is assigned a GUID, and the users assigned to the tenant are also assigned GUID's to ensure that OBIEE sees them as distinct users and is shielded from name clashes and name changes. The user is based on the Tenant GUID. This GUID is also available as a session variable.

This way the user maintenance tasks are delegated to Administrators within each Tenant. Persons taking on this role should understand the OBIEE artifacts and the authorizations available 

Not all the features are Currently - multi-tenant - enabled. Here are a few:

• Catalog groups
• KPIs, scorecards, 
• BI Mobile, 
• BI Composer, 
• Oracle BI for Microsoft Office, 
• Act As functionality, 
• Direct database requests.
• Oracle RTD, BI Publisher, and Marketing Segmentation.
• Full-text catalog search with Oracle SES and Oracle Endeca Server. The basic catalog search is available.
• Oracle Essbase Components (including Financial Reporting, Calculation Manager, and Workspace).

These features are not available for BIGlobalAdminstrator for administration:

• Oracle BI Administration Tool
• Catalog Manager
• Job Manager.
• Usage tracking 
• MapViewer

Other limitations:

• Application Roles are defined system wide, and not tenant specific. Any roles defined will be available to all the tenants for selection through the dialogues.
• There are no 'Tenant' Specific configurations in the instanceconfig.xml file. These changes include privileges in the catalog, skins, and front-end customization.

Multi-Tenancy is disabled by default. A few entries needs to be added to the configuration files and domain configuration files.

A detailed documentation can be found at: Configuring for Multiple Tenants


Sachin
Architect - Oracle Engineered Systems
Exalytics/Exalogic/Exadata
BuzzClan LLC

BuzzClan is a business consulting company collaborating to provide Oracle software advisory services & implementation services. BuzzClan LLC is committed to providing substantive business value on each and every client engagement. We do this through a combination of industry-specific business expertise, technical skills, proven project management methods and our “onsite - off site - offshore” delivery model. We strive to work in partnership with our customers to build high-performance teams and create business solutions that will last.

OBIEE - Multi-Tenancy Implementations - What is it

What is a multi-tenancy:

Lets take a few use cases:

A large organization has multiple units, each maintaining a separate business application and underlying transactional database. Each one has a different user base, and security considerations when it comes to data access. Data can be collected in individual data marts and a BI solution can be built on top of these data marts. This way the different departments have independent control on the development, release and usage of these BI solutions. This is not an ideal solution, and eventually these data marts should be converged into a central data warehouse, but until than...

A product offering decides to offer a self service BI add-on. The product runs on a distributed database mode where each client has its own physical database/schema running off a common data model. This can be implemented two ways, one by bringing all the data into a central Data Warehouse, or by creating parallel data marts for each clients. In the second scenario, the BI implementation can have a common RPD and Presentation catalog, connecting to the individual data marts, with provision for custom reports in the catalog.

BI on the cloud. Many states are centralizing their IT functions into a 'technology' agency. The rule of thumb is that if any two or more agencies use a platform, that platform should be handed over to this central agency for hosting, management etc to conserve costs. In this scenario, the agency can host various BI servers and allow the agencies to buy time, space and bandwidth in these servers.

A large financial institution manages retirement and benefits plans for various companies. The data warehouse is central, but the BI dashboards are customized for each client. The security implementation is also different and there are various level of support and custom/self-service solutions that the clients can buy into. The clients can negotiate a different upgrade timeline for their dashboards which may result in different micro release cycles effecting development/testing and other SDLC lifecycle.

Each of these use cases presents a Multi-tenant implementation scenario. The site(s) needs to be envisioned, designed, developed, deployed and managed in sync with each other.

Oracle's OBIEE documentation defines 'Multitenancy' as:

Multitenancy refers to a principle in software architecture where a single installation of the software runs on one server or clustered servers, serving multiple client organizations. With a multitenant architecture, each client organization operates independently of other organizations that share the same infrastructure. Multitenancy offers the ability to host multiple companies (even competitors) in one deployment without them knowing of each other..

Issues: Code Management

One of the major issues comes wrt code management. To run a successful multi-tenant implementation, the central administration should be able to cope up with multiple release schedules, dependencies during the deployments and coordinating rollbacks and out of schedule/emergency deployment requests. 

One installation/implementation of an OBIEE server can only support one RPD - the model against which the users can create reports and dashboards. 

RPD is a piece of monolithic code, which is deployed as a single item. Internally for the ease of multi user development, the code can be structured as project. A project can be worked upon as a smaller rpd to shorten and simplify development effort and unit testing.

The RPD is a three layer model consisting of a physical,  business and presentation layer. A project consists of Presentation layer subset areas and their associated Business and physical layer. A project can be defined in a way that it serves a particular 'Tenant' in an implementation, thus isolating the deployable unit of code.

Ideally, multiple deployments will have common Dimension tables and some fact tables, and that can throw a spanner in this while design, but then that may also gravitate towards consolidating things into a single Data Warehouse. That is a topic for another blog.

That said, experience tells that RPD is still a monolithic piece of code when it comes to deployment, and on the other hand the presentation catalog is very fragmented. The deployable artifacts are

• Folders
• Reports
• Prompts
• Filters
• Dashboards
• Security
• ...

With one deployable file for each of these and their corresponding security metadata. To add to the mix, the Presentation catalog has an organic growth pattern. Users can create new artifacts, and modify existing ones in production.

it is a challenge to simultaneously manage a deployment with two code units, one monolithic and the other fragmented.  

Next I will discuss the Presentation Catalog implementaion- OBIEE - Multi-Tenancy - Presentation Catalog

Sachin
Architect - Oracle Engineered Systems
Exalytics/Exalogic/Exadata
BuzzClan LLC

BuzzClan is a business consulting company collaborating to provide Oracle software advisory services & implementation services. BuzzClan LLC is committed to providing substantive business value on each and every client engagement. We do this through a combination of industry-specific business expertise, technical skills, proven project management methods and our “onsite - off site - offshore” delivery model. We strive to work in partnership with our customers to build high-performance teams and create business solutions that will last.

OBIEE - 11g Inbuilt Mapping and Spatial capability - Setup BI Presentation Part III

I was finally able to fix the issue by creating a container managed datasource in weblogic and then use it in mapviewer

http://download.oracle.com/docs/cd/E12840_01/wls/docs103/ConsoleHelp/taskhelp/jdbc/jdbc_datasources/CreateDataSources.html

http://download.oracle.com/docs/cd/E14571_01/web.1111/e10145/vis_admin.htm#sthref973


Sachin
JainSys

OBIEE - 11g Inbuilt Mapping and Spatial capability - Setup BI Presentation server

Continued from -> OBIEE - 11g Inbuilt Mapping and Spatial capability - Setup MapView

After you have configured and tested the MapViewer, log into the BI Server and go to the Administration.

Click on the "Manage Map Data"

Click on the New Icon on the Layers Tab, and select all the Layers in the list box. Repeat for "Background Maps" and "Images" tables.



Continued...

Sachin
Architect - Oracle Engineered Systems
Exalytics/Exalogic/Exadata
BuzzClan LLC

BuzzClan is a business consulting company collaborating to provide Oracle software advisory services & implementation services. BuzzClan LLC is committed to providing substantive business value on each and every client engagement. We do this through a combination of industry-specific business expertise, technical skills, proven project management methods and our “onsite - off site - offshore” delivery model. We strive to work in partnership with our customers to build high-performance teams and create business solutions that will last.

OBIEE - 11g Inbuilt Mapping and Spatial capability - Setup MapView

Continuation from last post:
OBIEE - 11g Inbuilt Mapping and Spatial capability - Setup OBIEE

The next step is to setup the MapView component.

Download the sample mapping data from this link MVDemo, and follow the instruction in the included pdf file. This will create a sample spatial data which can be used to jump start the map implementation in reports.

Once the data dump is imported fire up the MapViewer configuration. Go to the EM and navigate to
Application Deployments > mapviewer(11.1.1)(bi_cluster) and click on the link under Web Modules.

It will take a few and the MapViewer configuration screen will come up.

Click on the Management tab on the right, and login if prompted (the userid/password will be the same as that of the weblogic admin).

Copy the configuration file, and paste on any text editor. Search for " <map_data_source name="
you will see a block like this:
<map_data_source name="mvdemo"
jdbc_host="your db host name"
jdbc_sid="your db sid"
jdbc_port="your db port"
jdbc_user="mvdemo"
jdbc_password="!mvdemo"
jdbc_mode="thin"
number_of_mappers="3"
allow_jdbc_theme_based_foi="false"
plsql_package=”web_user_info”
/>

replace the jdbc values as per your db setup. Leave the plsql_pacage value intact. for details, refer to the step II in the pdf that came along the demo files.

paste this back into the configuration file, and remove the comments, if any around this block. Click "Save & Restart". Once the server comes back, test the mapviewer by going to this link, and clicking "Submit" You should see a map.

http:/<your Server>:9704/mapviewer/demo/secure-mapping.html

Further reading: Oracle documentation - Introduction to MapViewer

Next - Step III Setup BI Presentation server



Sachin
Architect - Oracle Engineered Systems
Exalytics/Exalogic/Exadata
BuzzClan LLC

BuzzClan is a business consulting company collaborating to provide Oracle software advisory services & implementation services. BuzzClan LLC is committed to providing substantive business value on each and every client engagement. We do this through a combination of industry-specific business expertise, technical skills, proven project management methods and our “onsite - off site - offshore” delivery model. We strive to work in partnership with our customers to build high-performance teams and create business solutions that will last.

OBIEE - 11g Inbuilt Mapping and Spatial capability - Setup BI Server

Sachin
JainSys Inc.

As you click the + button on the compound view while creating a report, you will now see a new item - Map.
OBIEE now has the capability to include map views on a report. This functionality is provided by the Spatial boundary data provided by NAVTEQ. This spatial data and any other spatial meta data, including themes and styles, must be stored in an Oracle Database to be accessed by Oracle MapViewer for display in map views.
If you try to add a map view to the report, you may see this error message:

A basemap is required to render a map. Please ensure that at least one basemap is defined in the spatial meta-data

 Error Details

Error Codes: EMGUNIA3

Location: saw.views.evc.activate, saw.httpserver.processrequest, saw.rpc.server.responder, saw.rpc.server, saw.rpc.server.handleConnection, saw.rpc.server.dispatch, saw.threadpool, saw.threadpool, saw.threads

The basic installation takes care of the installation, but does not complete the configuration part. There are two modes in which the spatial data can be configured:

• Remote MapViewer : The Map Viewer is a separate server, and mitigate the cross-browser AJAX communication issues, a proxy is established on the main server. Also known as proxy mode. If a separate remote instance of MapViewer is configured as the rendering engine, then the browser cannot communicate with the remote instance for resources. Browsers do not allow cross-domain AJAX calls for security reasons. To overcome this limitation, all requests are first forwarded to the co-located MapViewer, which in turn communicates with the actual remote instance.
• Co-located MapViewer : Also known as non-proxy mode. If the MapViewer is located in the same domain as Oracle BI EE and used as the rendering engine, then all map resources (such as JavaScript files and images) are downloaded from that instance of MapViewer.

To configure the map viewer in a Non-Proxy mode, add the following configuration to the instanceconfig.xml file.
<ServerInstance>
   <SpatialMaps>
 <ColocatedOracleMapViewerContextPath>/mapviewer</ColocatedOracleMapViewerContextPath>
      <RemoteOracleMapViewerAbsoluteURL></RemoteOracleMapViewerAbsoluteURL>
      <LayerDataLayout>
        <MaxRecords>600</MaxRecords>
      </LayerDataLayout>
   </SpatialMaps>
</ServerInstance>


Step II - Setup Mapview

Sachin
JainSys Inc.

[nQSError: 46033] Datatype: 25 is not supported

With OBIEE 11g, I was not able to import metadata from an Access db. More on that later.

I ended up hand coding the physiscal layer and when I was testing the table definiations for correctness, I ran into a couple of nQSError 46033/25 not supported.

Several forums on OTN suggested "wierd characters" within the definiation, but I could'nt see any thing out of place.

Digging into the NQQuery.log file showed me that the following entries:

[<time>] [OracleBIServerComponent] [TRACE:5] [USER-0] [] [ecid: 00hyAdAozFn4MupKcQV4Ep0001j00004Rl] [tid: 1a64] [requestid: 1ba20001] [sessionid: 1ba20000] [username: <>] ############################################## [[
-------------------- SQL Request:
EXECUTE PHYSICAL CONNECTION POOL "<>"."Connection Pool" select "SocCode", "SocDescr", "SocTitle" from "Soc"
]]
This gave me the SQL Query. This is a marked improvement over 10g, in 11g you can get the query generated in the logs, even when u are running test data pulls in RPD. I took the query -
select "SocCode", "SocDescr", "SocTitle" from "Soc"
and ran it in Access. There the error message was even more cryptic:

"Syntax Error in Query. Incomplete query clause"

On further analysis, one of the column name is not right - the SocDescr is defined as SocDesc

So if you are seeing [nQSError: 46033] Datatype: 25 is not supported., see if the column defination has changed since the metadata was imported.

Till next time, while I struggle with auto import of metadata from Access.

Sachin
Architect - Oracle Engineered Systems
Exalytics/Exalogic/Exadata
BuzzClan LLC

BuzzClan is a business consulting company collaborating to provide Oracle software advisory services & implementation services. BuzzClan LLC is committed to providing substantive business value on each and every client engagement. We do this through a combination of industry-specific business expertise, technical skills, proven project management methods and our “onsite - off site - offshore” delivery model. We strive to work in partnership with our customers to build high-performance teams and create business solutions that will last.

OBIEE 11g - Manually Upgrade Catalog from 10g to 11g

This allows for catalog upgrade from 10g to 11g version without having to run the GUI tool - UA. 


1. Copy 10g catalog to a temp location
2. Stop 11g BIPS
3. Update instaceconfig.xml to the 10g catalog location i.e. <CatalogPath>10g_Location</CatalogPath>
4. Add these flags to instanceconfig.xml –
<Catalog>
<UpgradeAndExit>true</UpgradeAndExit>
</Catalog>

5. Start BIPS – This will upgrade the catalog and shutdown automatically
6. Remove the flag true from the instanceconfig.xml
7. Start BIPS again



Sachin
Architect - Oracle Engineered Systems
Exalytics/Exalogic/Exadata
BuzzClan LLC

BuzzClan is a business consulting company collaborating to provide Oracle software advisory services & implementation services. BuzzClan LLC is committed to providing substantive business value on each and every client engagement. We do this through a combination of industry-specific business expertise, technical skills, proven project management methods and our “onsite - off site - offshore” delivery model. We strive to work in partnership with our customers to build high-performance teams and create business solutions that will last.

OBIEE 11g - RPD Upgrade issues

Sachin
JainSys Inc. These are couple of issues I have encountered so far while upgrading from 10g to 11g on the RPD side.
These are simple issues, but the error message are not very forthcoming. Hope this helps you get through.

1. All the TNS entries required in the RPD should be available in the TNS file for the server to start successfully after the conversion. If you see these errors in the log files, update the TNS entries to match the 10g server's.

[OracleBIServerComponent] 
[nQSError: 17014] Could not connect to Oracle database. 
[[[nQSError: 17001] Oracle Error code: 12154, message: ORA-12154: TNS:could not resolve the connect identifier specified at OCI call OCIServerAttach.



The TNS entries should be made in this location:

MW_HOME\network\admin\


More details about the directory structure at : OBIEE 11g - Demystifying the directory structure

2. If your 10g RPD has BI Event Tables for caching, remove them before the upgrade. The upgrade process does not convert them over properly, and it will result in errors related to "invalid cache" .


[nQSError:58004] A fatal error occurred while initializing the cache.

3. If you were trying to upgrade the catalog, and the Upgrade tool froze somewhere in the middle, then there is a good chance that you will not be able to start PS anymore.
There will be all sorts of error messages in your error logs, but none of them will point to this:
Look at the instanceconfig.xml and search for the following entry:

<Catalog>
<UpgradeAndExit>true</UpgradeAndExit>
</Catalog>
OR

<ps:Catalog xmlns:ps="oracle.bi.presentation.services/config/v1.1">
         <ps:UpgradeAndExit>true</ps:UpgradeAndExit>
      </ps:Catalog>

If you see this, remove this entry and restart the services.
Some errors that will show up in the saw.log files if this is the case:

[OBIPS] [NOTIFICATION:1] [] [saw.catalog.scrubacls.scrub] [ecid: ] [tid: ] Scrubbed 0 of 661 records[[
File:scrubacls.cpp

[OBIPS] [NOTIFICATION:1] [] [saw.subsystem.catalog.initialize.upgrade] [ecid: ] [tid: ] Upgrade catalog started[[
File:initializecatalog.cpp

Multiple entries similar to this

[OBIPS] [WARNING:1] [] [saw.subsystem.catalog.initialize.upgrade] [ecid: ] [tid: ] WARNING: missing SAW_CATALOG_REGISTER_IOOBJECT_FACTORY for scorecardperspectivelist1[[
File:upgradesearchhandler.cpp


I will be updating this log with other issues I encounter!


Sachin
JainSys Inc.

WebLogic - Boot Identity File

Every time a weblogic server is started using scripts or when it is setup as a Windows service, it needs the Weblogic admin userid and password.

One way to ensure that the scripts are not waiting for the user response every time is to create and store the user credentials and save them as a file. the boot identify file starts out as a plain text file, but when the server is started for the first time after (every time a change is made to the file) the entries are encrypted and it is more secure than storing unencrypted credentials in a startup or shutdown script file.

Steps to create the boot identify file:

1. Start the Administrator server once and provide the credentials when prompted.
2. Create a text file and place the following text in it:

username=username

password=password

3. Save the file as boot.properties in the security directory of the server's root directory
D:\app\Administrator\product\11.1.0\MiddleWareHome\user_projects\domains\bifoundation_domain\servers\bi_server1\security
4. The server automatically uses this file at next start and stop.
5. The content of the file after the encryption looks something like this
#Sun Dec 05 17:51:49 CST 2010 password={AES}T2DxUm0Oy3kG3xxxxxxxxxxxxxxxysyJaxjpOSg5Tqw\= username={AES}IV+SFI84mucU0xxxxxxxxxxxxxxxxxxCVfWIMPOtlU8\=

OBIEE 11g - Demystifying the directory structure

Sachin
BuzzClan LLC

Unlike 10g installation, which contained everything within the OracleBI and OracleBIData
directories, the 11g installation's footprint is rather complex. This is due to
the fact that the installation also includes Weblogic, EM and other components.
These are the main directories created on a typical installation:

• wlserver_10.3: The WebLogic Server home, which contains Java components, one Administration
  Server, and one or more Managed Servers.
• user_projects: The User Projects directory, which contains product domains, including
• one or more Oracle Business Intelligence domains.
• Oracle_BI1: The Oracle home, which contains all the binary files (read-only) that
  are specific to Oracle Business Intelligence.

Figure shows a typical Oracle Business Intelligence directory structure in a Fusion Middleware home. At the top of the directory structure is the Middleware home, which contains at least three subdirectories: the WebLogic Server home subdirectory, the User Projects subdirectory, and the Oracle BI home subdirectory. If you remember the installation, you provided the location for the MiddleWare directory at this screen. Recollect this and lets walk to the other locations.

• MW_HOME -- MiddleWare directory
• WL_HOME -- MW_HOME/wlserver_10.3/
• DOMAIN_HOME -- MW_HOME/user_projects/domains/bifoundation_domain/
• ORACLE_INSTANCE -- MW_HOME/instance/instance1

This link lists the names and locations of configuration files for each Oracle Business Intelligence system component. You are advised to use Fusion Middleware Control to update Oracle Business Intelligence configuration settings and ensure that changes are correctly propagated.

This figure illustrates the various components Under ORACLE_INSTANCE directory.
Logs, configuration files, cache, catalog, repository are now stored under this directory as per the schema shown. The general structure is

• MW_HOME\ORACLE_INSTANCE\config\COMPONENT\bi_component_name\
• MW_HOME\ORACLE_INSTANCE\logs\COMPONENT\bi_component_name\

The different components are :

• OPMN –
• OracleBIClusterControllerComponent    -- ClusterController
• OracleBIJavaHostComponent             -- Javahost
• OracleBIPresentationServicesComponent -- Presentation Services 
• OracleBISchedulerComponent -- Oracle BI Scheduler
  
• OracleBIServerComponent               -- Oracle BI Server
• ORACLE_HOME\upgrade\logs              -- logs for upgrade related activities.

Oracle BI content changes from 10g to 11g

Directory or Files	10g Location	11g Location
AdminTool.sh equalizerpds.sh/equalizerpds.exe JobManager.sh MigrateEUL.sh NQClient.sh nqcmd.sh/nqcmd.exe	BI_ORACLE_HOME/server/Bin	BI_ORACLE_HOME/bifoundation/server/bin
DBFeatures.INI NQSConfig.INI	BI_ORACLE_HOME/server/Config	ORACLE_INSTANCE /config/OracleBIServerComponent/coreapplication_obisn
NQClusterConfig.INI	BI_ORACLE_HOME/server/Config	ORACLE_INSTANCE /config/OracleBIApplication/coreapplication
NQQuery.log NQSAdminTool.log NQServer.log	BI_ORACLE_HOME/server/Log	ORACLE_INSTANCE /diagnostics/logs/OracleBIServerComponent/coreapplication_obis1
Oracle BI Server repository directory: SampleAppLite.rpd paint.rpd	BI_ORACLE_HOME /server/Repository	ORACLE_INSTANCE/bifoundation/OracleBIServerComponent/ coreapplication_obisn/repository
Samples: order.xml Product.xml samplesales.udml	BI_ORACLE_HOME /server/Sample/samplesales	ORACLE_INSTANCE /bifoundation/OracleBIServerComponent/coreapplication_obisn /sample/SampleAppFiles
Usage Tracking: SQL_Server_Time UsageTracking.rpd UsageTracking.zip	BI_ORACLE_HOME /server/Sample/usagetracking	ORACLE_INSTANCE /bifoundation/OracleBIServerComponent/coreapplication_obisn/ sample/usagetracking
Other Schemas (for example): Oracle_alter_nq_acct.sql SAACCT.DB2.sql SAACCT.MSSQL.sql SAACCT.Oracle.sql Note: Use the Repository Creation Utility to install the Oracle BI Schema	BI_ORACLE_HOME /server/Sample/Schema	ORACLE_INSTANCE /bifoundation/OracleBIServerComponent/coreapplication_obisn/schema
credentialstore.xml instanceconfig.xml (for Presentation Services) userpref_currencies.xml	OracleBIData/web/config	ORACLE_INSTANCE/config/OracleBIPresentationServicesComponent/ coreapplication_obipsn
javahost sawlog0.log	OracleBIData/web/log	ORACLE_INSTANCE /diagnostics/logs/OracleBIPresentationServicesComponent/ coreapplication_obipsn
catalogmanager.exe	BI_ORACLE_HOME \web\catalogmanager	ORACLE_INSTANCE\bifoundation\OracleBIPresentationServicesComponent\ coreapplication_obipsn\catalogmanager\runcat.cmd
instanceconfig.xml (for Oracle BI Scheduler)	OracleBIData\eb\config	ORACLE_INSTANCE\config\OracleBISchedulerComponent\ coreapplication_obischn
odbc.in user.sh	BI_ORACLE_HOME/setup	ORACLE_INSTANCE /bifoundation/OracleBIApplication/coreapplication/setup

Sachin
Architect - Oracle Engineered Systems
Exalytics/Exalogic/Exadata
BuzzClan LLC

BuzzClan is a business consulting company collaborating to provide Oracle software advisory services & implementation services. BuzzClan LLC is committed to providing substantive business value on each and every client engagement. We do this through a combination of industry-specific business expertise, technical skills, proven project management methods and our “onsite - off site - offshore” delivery model. We strive to work in partnership with our customers to build high-performance teams and create business solutions that will last.