Oracle Business Intelligence

Data Warehousing Objects

Fact tables and dimension
tables are the two types of objects commonly used in dimensional data warehouse
schemas.

Fact tables are the large
tables in your data warehouse schema that store business measurements. Fact
tables typically contain facts and foreign keys to the dimension tables. Fact
tables represent data, usually numeric and additive, that can be analyzed and
examined. Examples include Sales, Cost and Profit.

Dimension tables, also
known as lookup or reference tables, contain the relatively static data in the
data warehouse. Dimension tables store the information you normally use to
contain queries. Dimension tables are usually textual and descriptive and you
can use them as the row headers of the result set. Examples are Customers or
Products

Fact Table:

A fact table typically has
two types of columns: those that contain numeric facts (often called measurements),
and those that are foreign keys to dimension tables. A fact table contains
either detail-level facts or facts that have been aggregated. Fact tables that
contain aggregated facts are often called summary tables. A fact table usually
contains facts with the same level of aggregation.

Fact tables are summarized
into three types:

1Additive

Semi-Additive

Non-Additive

Additive facts can be
aggregated by simple arithmetical addition. A common example of this is sales. Semi-additive
facts can be aggregated along some of the dimensions and not along others. An
example of this is inventory levels, where you cannot tell what a level means
simply by looking at it. Non-additive facts cannot be added at all. An example
of this is averages.

Requirements of Fact Table:

You must define a fact
table for each star schema. From a modeling standpoint, the primary key of the
fact table is usually a composite key that is made up of all of its foreign
keys.

Dimension Table:

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.

Commonly dimension tables
are categorized into three types:

Conformed
dimension

Degenerated
dimension

Junk
dimension

If we are saying in simple word, Conformed
dimension table is a table which is having at least one or multiple primary key
or unique key to join with one or multiple fact table. For example product
table is related with the sales fact table

A conformed dimension is a set of data
attributes that have been physically implemented in multiple database tables
using the same structure, attributes, domain values, definitions and concepts
in each implementation. A conformed dimension cuts across many facts.

Dimensions are conformed when they are either exactly the
same (including keys) or one is a perfect subset of the other. Most important,
the row headers produced in the answer sets from two different conformed
dimensions must be able to match perfectly.

A junk dimension is a convenient grouping of typically
low-cardinality flags and indicators. By creating an abstract dimension, these
flags and indicators are removed from the fact table while placing them into a
useful dimensional framework.

A Junk Dimension is a dimension table consisting of
attributes that do not belong in the fact table or in any of the existing
dimension tables. The nature of these attributes is usually text or various
flags, e.g. non-generic comments or just simple yes/no or true/false indicators.
These kinds of attributes are typically remaining when all the obvious
dimensions in the business process have been identified and thus the designer
is faced with the challenge of where to put these attributes that do not belong
in the other dimensions.

One
solution is to create a new dimension for each of the remaining attributes, but
due to their nature, it could be necessary to create a vast number of new
dimensions resulting in a fact table with a very large number of foreign keys.
The designer could also decide to leave the remaining attributes in the fact
table but this could make the row length of the table unnecessarily large if,
for example, the attributes is a long text string.

Hierarchies

Hierarchies are logical
structures that use ordered levels as a means of organizing data. A hierarchy
can be used to define data aggregation. For example, in a time dimension, a
hierarchy might aggregate data from the month level to the quarter level to the
year level. A hierarchy can also be used to define a navigational drill path
and to establish a family structure.

Within a hierarchy, each
level is logically connected to the levels above and below it. Data values at
lower levels aggregate into the data values at higher levels. A dimension can
be composed of more than one hierarchy. For example, in the product dimension,
there might be two hierarchies—one for product categories and one for product
suppliers.

Dimension hierarchies also
group levels from general to granular. Query tools use hierarchies to enable
you to drill down into your data to view different levels of granularity. This
is one of the key benefits of a data warehouse.

When designing
hierarchies, you must consider the relationships in business structures. For
example, a divisional multilevel sales organization. Hierarchies impose a
family structure on dimension values. For a particular level value, a value at
the next higher level is its parent, and values at the next lower level are its
children. These familial relationships enable analysts to access data quickly.

Levels

A level represents a
position in a hierarchy. For example, a time dimension might have a hierarchy that
represents data at the month, quarter, and year levels. Levels range from
general to specific, with the root level as the highest or most general level.
The levels in a dimension are organized into one or more hierarchies.

Level Relationships

Level relationships
specify top-to-bottom ordering of levels from most general (the root) to most
specific information. They define the parent-child relationship between the
levels in a hierarchy.

Hierarchies are also
essential components in enabling more complex rewrites. For example, the
database can aggregate an existing sales revenue on a quarterly base to a
yearly aggregation when the dimensional dependencies between quarter and year
are known.

Typical Dimension Hierarchy

Wherever we go, we have to know this concepts to work on data warehousing technologies.

Creating Presentation Layer of SH Repository The main purpose of building the Presentation catalog in Presentation Layer is to provide the useful and necessary data from the end-users perspective i.e. different view of data for business measures are provide for different type of users.

Steps for making Presentation catalog in Presentation Layer are as follows:

1)In case you closed the rpd file, use the following steps to open the repository.

2)Verify that the Presentation layer is visible. If it is not visible, select View > Presentation from the menu bar.

3)Make the DW Business model available for Queries. To do so, double click on the DW business model in the BMM layer > select the Checkbox for

4)Drag the DW Business Model folder from the Business Model and Mapping Layer to the Presentation Layer. Thus the DW presentation catalog is created in Presentation Layer.

5)Reordering the columns of DW presentation catalog.

a)Expand the DW presentation catalog.

b)Double click the Customers table and select the column tab. c)Change the order of columns using Up and Down buttons. Select the column and then click on Up or Down button. After rearranging it should appear as :

d)Click OK to close the window.

6)Similarly reorder the other columns by following the method described in step 4 in following order.

7)Rename the columns of DW presentation catalog.

a)Expand the Products presentation table.b)Double-click the Prod Name column, select the General tab in the Presentation Column dialog box and uncheck the Use Logical Column Name.c)Change the column name by typing Product Name in the name field.d)Click OK.

8)Repeat the process till all the column of Products presentation table get name as:

Note: Alternatively you could rename these columns in the Business Model & Mapping layer (manually or using Rename wizard shown previously)

9)In order to meet end user’s requirement we have to hide/delete some columns from presentation Layer which are not used by users or are required in Business Model & Mapping layer. For this purpose you have to perform deletion of unnecessary columns:

a)In the Channels presentation column, select the Channel Id and press Delete key from keyboard. b) Click Yes to delete the object. c) Similarly delete the following columns from the presentation tables.

10)Verify the presentation tables for the changes you made.

11)Save the repository. Do not check global consistency.

Tip: Drag and drop the business model from the business model and mapping layer onto the presentation layer, this ensures faster creation of the presentation model.

Testing and Validating Repository Consistency Check

Before testing the repository by running queries using Oracle BI Answers, you have to check the consistencies of repository and its business model.Steps of checking repository are as follows:1)If you haven’t open the Oracle BI Administration tool just open it by following steps. a)Select Start > Programs > Oracle Business Intelligence > BI Administration. b)Select File > Open > Offline. c)Double-click SH.rpd to open the repository file. d)Enter the following parameters: User: Administrator Password: Administrator e)Click OK.

3)If any error appears in Consistency Check Manager, then edit the repository to correct the inconsistencies and rerun Check Global Consistency as in step 1.

4)Consistency Check Manager can show three type of messages:

Error: These messages indicate that there has been some inconsistency in the model and need to be fixed before querieng. If you get these errors then go through the error description and fix it before proceeding.Warning: These messages show some warning messages which may or may not cause any errors.Best Practices: These messages are displays information about the best practices, which can be followed.5)On successfully and errorless appearance of Consistency Check Manager, click on Close.

So, Finally we have built the RPD for SH schema and will learn how to create our first report using this presentation catalog and How to add that report into the Dashboard.

The main purpose of the business model is to capture how users think about their business using their own business terms. The business model simplifies the physical schema and maps the users’ business terms to physical sources.

There are two main categories of logical tables: Fact and Dimension. Logical fact tables contain the measures of organization’s business operations and performance. Logical dimension tables contain the data used to qualify the facts.

2) Create a Business Model by right clicking in the white space in the Business Model and Mapping layer and select New Business Model. In the name field, name the business model as DW and click OK. Alternatively, you could drag and drop the Physical schema to create the initial Business Model.

3) Create logical tables by right-clicking on DW business model and select New Object > Logical Table and name it Sales Fact in the Logical Table dialog box and click OK.

4) Create Logical column of Sales Fact table

a) In The Physical Layer expand SALES table.b) Select and drag AMOUNT_SOLD column from the Physical layer to the Sales Fact logical table in the Business Model and Mapping layer. A new AMOUNT_SOLD logical column is added to the Sales Fact logical table.In the Business Model and Mapping layer, double-click AMOUNT_SOLD logical column to open the Logical Column dialog box.c) Go to the General tab and in the Name field, rename the column to “Amount Sold”d) Click the Aggregation tab.

e) In the Default aggregation rule drop down list, select SUM. Aggregation rule determines the aggregation type to be used in the SQL query generated by OBI. For example, sum, avg, count etc.

f) Click OK to close the Logical Column dialog box. Notice that the logical column icon is changed to indicate an aggregation rule is applied.

g) Pull the QUANTITY_SOLD column from the SALES table to the Sales Fact

h) Add the aggregation rule as SUM for Quantity Sold logical column. It should appear as like below

5) Create Dimensions in Business Model and Mapping Layer as follows : a) Select the following tables in the Physical layer. CHANNELS, TIMES, PRODUCTS, CUSTOMERS and PROMOTIONS

b) Drag the selected tables from the Physical layer onto the DW business model folder in the Business Model and Mapping layer. This automatically creates logical tables in the Business Model and Mapping layer.

d) Place the Sales Fact table in the middle. To adjust the zoom factor, right click on the empty space of diagram window and select the zoom.

e) Click the New Join button in the toolbar.

f) Click on the PRODUCTS table icon first and then click on Sales Fact table icon in the Logical Table Diagram. The Logical Join dialog box opens. Leave the defaults as they are and click OK.

g) The join is creating a one-to-many (1:N) relationship that joins the key column in the first table to a column that is a foreign key in the second table. Create the joins and repeat the steps for the remaining tables. You don’t have to specify any join conditions in Business Model; BI server will pick the joins defined in the Physical Layer.

In this post, we will learn How to create the relation between the
tables using Physical Layer Diagram.

Using the Physical
Diagram approach you can define both Primary and Foreign keys in one step.

In SH Schema, the following is the key that you are going to join
between the table.

PROMOTIONS. PROMO_ID = SALES. PROMO_ID

TIMES. TIME_ID = SALES.
TIME_ID

CUSTOMERS. CUST_ID = SALES. CUST_ID

CHANNELS. CHANNEL_ID = SALES. CHANNEL_ID

Follow the steps to
create physical layer Diagram:

1.In the Physical layer, right-click
the DW data source > select Physical Diagram > Object (s) and All
Joins. The option will be the screen below,

The physical diagram may appear as in screenshot below:

2.Objects will appear very big, but
the zoom can be adjusted. To adjust the zoom level, right click on the white
space and set the zoom level to 100%, as
shown in screenshot below :

3.You
can now drag the tables to move around and rearrange them.

Click the Create New
Join button on the toolbar. Using the Foreign Key, make a join between the
tables in the physical Layer.

4.Click the PRODUCTS table first and then on the SALES table. The Physical Foreign Key
dialog box will open up.

Select
PROD_ID from PRODUCTS and PROD_ID from SALES.

5.Click Ok. If prompted for any question, please select Yes.

6.Repeat the above steps to define
join between all the tables. The join conditions are as follows:

7.When you are finished, click the X
in the upper right corner to close the Physical Diagram.

8.Select File > Save or
click the Save button on the toolbar to save the repository.

9.Click No when prompted to
check global consistency.

10.Leave
the Administration Tool and the DW repository open for the next topic.

Keep saving the repository at regular intervals.

Note:

The order of selecting the tables matters while
defining the joins. The join has one to many relationships. The first table and
the second table have one to many relationships. Generally, there is one to
many relationships between Dimensions and Facts. So, we click on dimension
first and then on Fact.

Congratulations! You have successfully created a new
repository, set up an ODBC data source and imported a table schema from an
external data source into the Physical layer, and defined keys and joins.