Thursday, August 20, 2015

Creating a Database Directory in Oracle

Oracle database has many features which run outside the database. For example we have external tables, Export,Import ,SQL * Loader etc. For all these features we might need a directory to place the respective files. We cannot just give the path in the statements all the times.

We have the ability to create directories in the database and point it to any where on the server.Below command can be used to create directory.

CREATE [OR REPLACE] DIRECTORY directory_name AS 'path_name';

Example: CREATE DIRECTORY TEMP_DIR AS 'C:\Filepath\ExternalTables\Flatfiles';


Monday, August 10, 2015

OBIEE 11g: Basics of Presentation Layer

Presentation layer is the only visible layer to the user. The main purpose of this layer is to provide the user with meaningful attributes in organized folders. The users should be able to build the reporting solutions by seeing the names of the attributes in a particular presentation folder.

Simply put the presentation layer is a new version of BMM with proper grouping of columns and tables into subject areas according to the business model from BMM.



We can rename the columns all we want and can also implement the object level security from Presentation layer.

OBIEE 11g : Basics of BMM Layer

Business model and mapping layer is all about designing the star schema from the physical layer. But that’s not as simple as it sounds. It is also where you define or we can say guide the BI server to use

  • Preferred join condition
  • Preferred physical table
  • Logical level for aggregation
  • Hierarchy 
  • Arithmetic operations
  • Where clause
  • Fragmentation
  • Sorting order etc
All these things combined together will give you the desired level of effectiveness and if such standards are not followed OBIEE is nothing but a simple query generation tool.


The above picture is of a business model named “Usage Tracking” with four Hierarchies and 5 logical tables.


Each has its features and operations of its own. BMM layer can only contain start schema and all the dimensions should be connected to one or the other fact.

This is how a Business Model Mapping looks like


However there is one exception in the form of Lookup Table, which can be left alone but have to enable the Lookup table option

And the each dimension can have only one hierarchy related to it, which means you cannot create two hierarchies from the same dimension table.

OBIEE 11g : Basics of Physical Layer

Physical Layer is the first one from right and it also come first even when we are designing the RPD. It holds the information about the tables, columns, datatypes, database properties, connection pools etc

This is the exact replica of underlying Data source as we import the metadata from the database as shown below



This picture contains
  • One database “Oracle Data Warehouse ” 
  • Three connection pools 
  • One Catalog
  • One Database Schema “dbo”
  • And 3 tables & four Aliases

Database

When we import some table or any metadata into the repository a Database icon will be created automatically which by default stores some of the important information associated with underlying data source. The below pictures gives you some idea about the same




Connection Pool

A connection pool is an object under a database, it stores the parameters or has the location where the parameters are available, using which we can connect to the database. As discussed earlier we require 5 parameters to connect to a Oracle Database. The picture will give you the insights of the same




Catalog

Catalog is just a folder in physical layer which doesn’t have any specific significance

Schema Name

Schema Name is the name of database object under which the table is stored. Generally these names will be same as the user names if manually created and will have different names when created using an application or creation utility.

Tables

Tables are the physical objects of database where the data is stored and are exact resemblance to the database object.

Alias

Alias is a duplicate of a table created when there is a need for the same physical table more than once, below are general cases where aliases are created
  • Dimensions and facts are in same physical table
  • Same physical table needs to be joined to another table more than once using different join conditions
  • Improve performance by avoiding circular joins

OBIEE 11g: Pre Requisites for reporting using OBIEE 11g

As we are now using OBIEE for reporting purpose there are few important things that we need to obtain without which we cannot proceed.

Data Source: Most important is Data Source without this we don’t have the data or the metadata, so when there is no metadata there is no design and hence no reporting. Please ensure we have a data source with tables and some data and we know the details to connect to.

In order to connect to a data source (example Oracle Database) we need to have parameters that define the data source, these parameters include
  • Server Name ( Where Database is hosted ex. localhost)
  • Instance Name (Name of database or instance name ex. Orcl)
  • Port number ( ex 1521)
  • Username (Schema name or user who has access to required schema)
  • Password (Password of above mentioned username )
BI System : we should have a configured and working BI domain to develop and deploy the metadata files along with login as a BI Administrator
  • User Account in the OBIEE Application as BI Administrator
  • Access to the installation folder ( to change config settings)
Requirements: Now the most important of all these to know what you’re doing so before we start the process of development we should get our requirements straight.
  • Identify the exact data set 
  • Identify the tables involved and their physical relation
  • Confirm the type of view needed
  • Identify the aggregations involved

OBIEE 11g : Knowing the Metadata Files


OBIEE 11g works with lots of components and files but there are two important files which are the key in performing reporting or analytics. These files are
  • Repository
  • Catalog
These files are so important that any issue related to these files can cause severe issues like errors in reporting views to unavailability of service (downtime). A regular backup of these files along with the configuration files related is recommended as a contingency.

Repository

Repository file stores all the metadata related to data and the joins, priorities, aggregations etc. It’s the central spoc to the BI server where the query generation happens and is fired against a database whose details are also saved in repository.

Oracle BI server is owner of this file and the settings of BI server are maintained using NQSConfig.INI file

The repository file exists in the below location

[MIDDLEWARE_HOME]\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository

There can be many files available in the above repository folder but only file will be loaded when you start the BI server and that file name is available in the NQSConfig.INI file.

The NQSConfig.INI file is located in below location

[MIDDLEWARE_HOME]\instances\instance1\config\OracleBIServerComponent\coreapplication_obis1

Repository file contain these below data and can be maintained by BI Administration tool

  • Metadata ( Table Information, Information about DATA)
  • Connection Pool ( Database Information)
  • Joins ( Physical as well as logical complex)
  • Variables (Session & Repository)
  • Cache Information
  • Log level information
  • Alias Tables
  • Lookup Tables
  • Logical table Sources
  • Hierarchies
  • Level based Measures
  • Time Series Functions 

Catalog

This is generally called as Web catalog and can be present any where either on the server or a network share. It is a place or a folder structure where users create and save their analysis or reporting work.

And can only be accessed using the OBIEE analytics URL. The catalog can be viewed or maintained by using Catalog Manager.

Oracle Presentation Services is the owner of catalog and the settings of Oracle PS including the location of Catalog are maintained by instanceconfig.xml file.

Instanceconfig.xml file is located in the below location

[MIDDLEWARE_HOME]\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1

Webcatalog contains the below information
  • Saved Analysis
  • Saved Dashboards
  • Saved Agents
  • User Folders 
  • Shared Reports
  • Webcatalog Groups
  • Folder Permissions
  • User Preferences (timezone , Home Page etc)