Implicit Fact Column in OBIEE 11g

Implicit Fact Column OBIEE 11g

An Implicit fact column is used when we have multiple fact tables and the report is getting generated using only dimension columns.

A User may request a report where it may have only Dimensions and no Fact columns. In this case, the server may sometimes get confused as to which fact table should it join to fetch the data. So it joins to the nearest fact table and pulls the data through it. So the report output obtained may be different from what the user is expecting.

So, in order to avoid this kind of error,we need to set Implicit Fact Column.

The goal of this is to guide the BI Server to make the best choice between two possible query paths.

We can set a fact attribute (measure) as an implicit fact column.

We can also create dummy implicit fact column on and assign any numeric value to it.

We can set implicit fact column in presentation catlog properties.

1.  Goto properties of presentation catlog in presentation layer.

2.  In implicit fact column section click on set and select any measure column from fact table.

3.  Click OK.

4.  Save your work.

Instead of selecting any fact measure column as implicit fact column, we can also define a dummy implicit fact.

1.  Create a Physical Column in Fact table in Physical Layer.

2.  Name it as Implicit_Column.

3.  Drag this column in Fact table from BMM layer.

4.  Double click on logical table source of fact table.

5.  In content tab, assign any numeric value to Implicit_Column.

You can set this column as implicit column in presentation catalog

Thanks, Keep visiting for more new topics.

Configuration of Data Sync for BICS

 Oracle Business Intelligence Cloud Service (BICS) with Data Sync

     1. Business Intelligence Cloud Service Data Sync Overview :

The Business Intelligence Cloud Service Data Sync supports the load of on-premises data residing in one or more relational or comma-separated value file sources into the schema provisioned on the Oracle Business Intelligence Cloud Service.

2. Installing the Business Intelligence Cloud Service Data Sync :

To install the Data Sync, you must meet the requirements and prerequisites, then unzip and run the application.
Download Data Sync from:
http://www.oracle.com/technetwork/middleware/bicloud/downloads/index.html

2.1  Prerequisites, Supported Databases, and JDBC Requirements :

       Before installing, you must have the 64 bit machine and Java 1.7 or later version of Java Developer Kit (JDK).
 Install jdk to a directory with no spaces in its name.
 
    Note: Java Developer Kit is required for Business Intelligence Cloud Service Data Sync. 
    The equivalent version of the Java Runtime Environment (JRE) is insufficient to support
    the installation.
 
2.2  Setting Up the Software :

To set up the software, copy the BICSDataSync.Zip file to an installation directory with no spaces in its name, and unzip the files.

2.2.1 Setting the Java Home:
Depending on your operating system, edit either the config.bat or config.sh file, modifying the line that sets the JAVA_HOME. Replace the @JAVA_HOME with the directory where the JDK is installed.

     2.2.2 Execute datasyncclient.bat or datasynccclient.sh :

Execute datasyncclient.bat or datasynccclient.sh depending on the operating system to launch the first time Data Sync Configuration Wizard and click next.

2.2.3 Configure the Repository and JAVA DB :

In Environment Configuration, select configure a new environment and click next to configure the repository and Java DB for Data Sync to store its metadata.

2.2.4 Specify Repository Name and click Next :

2.2.5 Choose Password for Datasync Login and click Next:

2.2.6 Configuration is complete, click finish to close the configuration wizard :

2.2.7 BICS Datasync server gets started :

BICS Datasync server gets started, To get started, please enter the password set for the repository. Skip the create project option. Project can be created from Datasync client. 

  Enter the same password given while Installing Datasync in step 2.2.5

  After successful Login, following screen will appear.

3. Creating Project and Connections :

3.1.1 Creating Projects :

a. To Create a new Project, select File --> Project, In the project creation wizard, specify the New Project Name and click OK.

Please Note: A project is a list of tables/files to upload in a single session. You may need multiple projects to populate a BI Cloud database – either if loading data from multiple sources or if you need to schedule different tables at different times

Give the relevant Project Name.

3.2.2 Creating Connections :

a. Once Project is created, we need to setup Source & Target connections, Source connections can be RDMBS or Files, BICS Datasync supports connections to popular RDMBS like MYSQL, ORACLE, SQL SERVER, DB2 etc.,

1. Click on Connections

2. Select the TARGET Source

3. Enter BICS Username

4. Enter BICS Password

5. URL should be same as the BICS URL excluding /analytics tag at the end.

6. To Test the connection, click on Test Connection button, a pop up window appears with the test result.

7. Click OK to Close the Test result window

8. Save the changes to the TARGET connection, if the connection is successfully tested.

b. Following are the steps for setting up the Source RDBMS connection (e.g. Oracle source).

1. In the Connection Tab, click on New to create a new Source DB connection

2. Enter the Name of the connection, Connection Type, Table owner, Username, Password, Service name, Host & Port number of the Source.

3. Click on Test Connection to check the status of the Test result

4. After reviewing the test results, Click OK to close the test result window

5. Once the connection is tested successfully, save the connection details.

3.2.3 Importing Metadata from Relational, File & Target into Project.

a. Once Connections for source and target is succesfully configured, then we can to create the tasks/ETL.

1. To setup a new datasysnc task, Select the Project Tab

2. In the Relational Data Tab, click on Data from Table to import the metadata of the source table(s)

3. In Import Table into Datasync window, select the appropriate data sources.

4. Search/Select the list of tables displayed and Import tables.

b. Review Target Metadata.

Note: By default BICS Datasync adds additional 3 columns in the Target table namely:

1. DSYS_BATCH_ID: Tracks the batch that is trying to upload the data. Each table load streams multiple batches (currently of 3,000 rows), with each batch assigned a unique number.

2. DSYS_INSTANCE_ID: Tracks the Data Sync installation instance ID

3. DSYS_PROCESS_ID:  Tracks the process ID assigned to a certain run of the job.

4. Creating Jobs & Loading On-Premise data to BICS Schema :

4.1.1 Creating & Executing Jobs :

a. A job is an instance of running a particular project and can be associated with a schedule to regularly run it. Clicking on the Jobs button in the top menu bar opens the Jobs window. To create a new executable job, click New & Enter a Job Name.

b.  To Execute the Job

1. Click on Run Job button.

2. Go to the Current Jobs.

3. All Tasks would be queued.

c. You can also view the Job History by selecting the History tab to review the status of the load.

d. Finally we can check the data that is been loaded in Oracle Cloud Schema by logging on to BICS Myservices and browsing through the schema objects as shown below, notice the extra columns created by BICS Datasync.

 5. Conclusion :

The Business Intelligence Cloud Service Data Sync supports the load of on-premises data residing in one or more relational or comma-separated value file sources into the schema provisioned on the Oracle Business Intelligence Cloud Service.

Thanks for visiting..Keep Learning..:)

RPD Layers

Three Layers of RPD:

RPD (Repository) is divided into 3 layers

1. Physical Layer: This layer is used for

ü  Importing data

ü  Creating Aliases

ü  Building physical joins

ü  Setting up connection pool and its properties

ü  Enabling/ Disabling cache for individual table

2. BMM (Business Model & Mapping) Layer: This layer is used for

ü  Writing the business logic

ü  Creating Logical columns and tables

ü  Creating hierarchy

ü  Creating LBM (level based measures)

ü  Creating shares

ü  Creating Time series functions

ü  Creating Fragmentation on tables

ü  Creating filters on repository

3. Presentation Layer: This layer is used for

ü  Arranging the data for users view (Folder Structure)

ü  Creating Presentation hierarchy

ü  Creating Implicit Fact column

ü  Implementing Column level security

In short three layers of RDP consist of following functions:

PRESENTATION LAYER User Roles And Preferences Simplified Views Logical SQL Interface

BMM LAYER Dimensions Hierarchies Measures Calculations Aggregation Rules Time Series Functions

PHYSICAL LAYER Map Physical data Connections Schema Aliases, Joins

Data Warehouse Concepts

Facts Tables:

ü  A fact table typically has two types of columns: foreign keys to dimension tables and measures those that contain numeric facts. A fact table can contain fact's data on detail or aggregated level.

ü  A fact table stores quantitative information for analysis and is often de-normalized.

ü  Fact table is typically numeric data and it is often data that can be easily manipulated, particularly by summing together many thousands of rows.

Types of fact:

1.      Additive:
Additive facts are facts that can be summed up through all of the dimensions in the fact table. A sales fact is a good example for additive fact.

2.      Semi-Additive:
Semi-additive facts are facts that can be summed up for some of the dimensions in the fact table, but not the others.
E.g.  Daily balances fact can be summed up through the customers dimension but not through the time dimension.

3.      Non-Additive:
Non-additive facts are facts that cannot be summed up for any of the dimensions present in the fact table.
E.g. Facts which have percentages, ratios calculated.

Dimensions:

ü  A dimension is a structure, often composed of one or more hierarchies, that categorizes data. Dimensional attributes help to describe the dimensional value. They are normally descriptive, textual values. Several distinct dimensions, combined with facts, enable you to answer business questions. Commonly used dimensions are customers, products, and time.

ü  Dimension data is typically collected at the lowest level of detail and then aggregated into higher level totals that are more useful for analysis. These natural rollups or aggregations within a dimension table are called hierarchies.

Surrogate key:

ü  Surrogate keys are nothing but integers which do not have any meaning in terms of business and used as primary key in dimension table.

ü  Surrogate keys join the dimension tables to the fact table. Surrogate keys serve as an important means of identifying each instance or entity inside of a dimension table.

Fact less fact:

A fact less fact table is fact table that does not contain fact. They contain only dimensional keys and it captures events that happen only at information level but not included in the calculations level. Just information about an event that happen over a period.

A fact less fact table captures the many-to-many relationships between dimensions, but contains no numeric or textual facts. They are often used to record events or coverage information. Common examples of fact less fact tables include:

ü  Identifying product promotion events

ü  Tracking student attendance or registration events

ü  Tracking insurance-related accident events

ü  Identifying building, facility, and equipment schedules for a hospital or university

ü  Fact less fact tables are used for tracking a process or collecting stats.

E.g. student, time, and class dimensions used to create student attendance fact less fact table. 

Degenerated dimension:

ü  A degenerate dimension is when the dimension attribute is stored as part of fact table, and not in a separate dimension table.

ü  These are essentially dimension keys for which there are no other attributes. In a data warehouse, these are often used as the result of a drill through query to analyze the source of an aggregated number in a report.

ü  You can use these values to trace back to transactions in the OLTP system.

Conformed dimension:

ü  A Dimension that is used in multiple locations is called a conformed dimension.

ü  A conformed dimension may be used with multiple fact tables in a single database, or across multiple data marts or data warehouses.

Schema types:

1.  Star Schema:

ü  In the star schema design, a single object sits in the middle and is radically connected to other surrounding objects (dimension lookup tables) like a star.

ü  Each dimension is represented as a single table.

ü  The primary key in each dimension table is related to a foreign key in the fact table.

2.  Snowflake schema:

ü  The snowflake schema is an extension of the star schema, where each point of the star explodes into more points.

ü  In a star schema, each dimension is represented by a single dimensional table, whereas in a snowflake schema, that dimensional table is normalized into multiple lookup tables, each representing a level in the dimensional hierarchy.