Semantic Model Series: 3. Physical Layer

Physical Layer

Physical Layer in the first of the three layers in our metadata model (repository). It defines how Oracle Analytics connects to and retrieves data from external data sources, such as databases.

It includes:

• Connections to data sources (e.g., relational databases on-prem or in cloud, such as Oracle Autonomous Database, Snowflate, SQL Server, ...). Connections are defined system-wide and can be reused across different models. As we already learnt, they can be regular or system.

• Schemas and tables/views: when creating physical layer, tables (or even better, their definitions) can be imported. Besides tables, view or SQL expressions are also supported. Once imported tables can be renamed (underlying database tables are not affected). It is a good practice to create aliases for imported tables and then work with these aliases rather than directly database tables. There are several benefits for that which we will look into a bit later.

• Joins between tables define the relations between physical table in the model. BI Server genarates queries that are executed against database tables using join definitions between tables. Therefore joins definition is very important and has to handled with great care. It is worth to mention that there is no prescription what physical model should be implemented in the database before import. It can be 3NF, star or snow-flake schema, even flat, denormalized, table. Physical layer hasn't got any constraint, whereas we will see that logical model requires star schema model.

• Optional data source filters, caching settings, and descriptions:

  • data source filters can be directly applied on the physical data sources, which allows us to define row-level filter security setup
  • we can define caching strategy for each table in the physical layer.

Physical layer is important because:

• It abstracts the raw data from your users. Business users don’t have to deal with tables or SQL — they interact with the Logical and Presentation layers.
• It ensures performance tuning at the lowest level.
• Gives developers and administrators control over source metadata, joins, and security before transforming it into business-friendly formats.

In this lab, we will demonstrate how to create a physical layer. So, let's crack on!

Create a new database

Navigate to Physical layer first.

We can observe that there is one database called MyDatabase available. This database is a dummy one and needs updating. We can do that or simply create a new one. Let's begin from scratch, hence simply delete any existing database (be careful with deletions if you are not working on a brand new semantic model!).

Click on plus icon () next to Search field and select Create Database option:

Create Database dialog window opens. Enter Name, optionally Description and select your Database Type. Choose your database type, however in our case, we need to switch to Oracle Autonomous Data Warehouse option.

Click Ok to create a new database.

Newly created database is opened in the main part of the canvas. Currently everything is still empty:

There are 5 tabs which will define our database:

• General: These are genaral database definitions like database name and database type.
• Tables: This tab lists all the physical tables (and aliases) that belong to the database.
• Advanced: The Advanced tab allows configuration of database-specific SQL behavior.
• Connection Pools: This tab shows all connection pools defined for the database.
• Query Limits: This tab allows setting query-level restrictions at the database level.

We will focus on Tables and Connection Pools.

Connection Pools

Connection pool defines how Oracle Analytics connects to the database — including credentials, max connections, timeouts, etc. There can be more than one connection pool defined for a database. For example, it is a good practice to have a separate connection pool defined for variables initialisation, hence typically we would expect more that one connection pool defined here. But for our lab, one will be enough.

Navigate to Connection Pools tab. As we expected, connection pool list is empty.

So to be able to connect to any database, one connection pool needs to be created.

Look for a plus icon (), it is next to Search filed, and click on it. This will automatically add a new connection pool.

Click on details icon:

This icon has the same behavour throughout Semantic Model. It opens details for the selected object. In our case, selected object is connection pool New Connection Pool_1.

We can now update connection pool details, such as Name - let's rename it to OA Bootcamp Connection Pool - Main. We will look into more details for connection pool in later chapters.

Let's save what we've so far done. Save icon is located in the top bar on the right side, a diskette icon ().

Tables

Tables is the main part of database definition. Here we import tables, views or results of a sql query, and here we define joins between tables.

We'll begin with dimension tables first. As we saw, there are four dimension tables - tables that begin with D_. There are various ways to bring tables into database.

We'll use the simplest, drag and drop approach for these.

In the navigation bar on the left, switch to the Connections tab. Expand Schemas and click on OABOOTCAMP schema. Click on the following tables:

• D_CUSTOMERS
• D_GEOGRAPHY
• D_PRODUCTS
• D_TIME

Drag selected tables onto canvas, onto Tables section and drop them there. Four dimension tables should now be in included in the database.

Alternative approach is to add a new table to the database by clicking on a plus sign () next to Tables.

When clicked, menu shows. Select Add Table. Select Table dialog opens. Expand Schemas and navigate to fact table F_REVENUE. Select it and click Select.

After table list is refreshed, we should see five tables, 4 dimension and 1 fact table.

We will add one more table. Table will be another dimension table, called D_ORDERS and its columns will be subset of columns from F_REVENUE table.

Click again on plus sign () next to Tables.

This time select Create Physical Table.

In the first step of Create Physical Table dialog we need to provide a name for a new table, D_ORDERS in our case.

Click Ok to continue.

New tab for D_ORDERS is opened. We have now several options how to further define columns for this new Physical table. We will use SQL Query to populate the table.

First, let' navigate to General tab. Observe that Source is set to Table. Change it to Select Statement.

Navigate to the Columns tab and populate Select Statement with the following SQL code:

SELECT  ORDER_KEY,
        ORDER_STATUS, 
        CHANNEL_NAME,
        TIME_BILL_DT,  
        TIME_PAID_DT
FROM F_REVENUE

Now, let's add columns, which should match results of the SQL statement.

Let's run a Preview and check our SQL query:

We can save our work now.

So far, we have imported some of dimension tables into our database. We have added fact table and we have created a new physical table based on the SQL query.

In the next step, we will remove some of the columns that are not a foreign key or are not treated as measure from the fact table (by defualt fact tables should contain only columns that are either a measure or foreign key that relates to one of dimensions).

In our F_REVENUE fact table we have two such candidates: ORDER_STATUS and CHANNEL_NAME. Both columns have been included in D_ORDERS table and will relate to the fact table using ORDER_KEY, therefore it is safe to remove them from the F_REVENUE fact table.

Double click on F_REVENUE table in the navigation bar on the left side. New page is opened with definition of F_REVENUE table:

Right-Mouse click on ORDER_STATUS column in the column list and select Delete from the menu:

Repeat deletion also for CHANNEL_NAME.

F_REVENUE has now the following columns:

• Measures
  • UNITS
  • COST_FIXED
  • COST_VARIABLE
  • REVENUE
  • DISCNT_VALUE
• Foreign Keys
  • ORDER_KEY
  • PROD_ITEM_KEY
  • ADDR_KEY
  • CUST_NUMBER
  • TIME_BILL_DT
  • TIME_PAID_DT

Table Aliases

It is good practice and recommended approach to create aliases for all of physical tables in database. In some cases, which we will also review, we simply must create aliases.

Currently we have 6 tables in our database, 5 dimension tables and 1 fact table. We will create an alias for each one of the as follows:

Right-mouse click on selected table name in the Tables list. Table associated menu opens and the first option in the menu is Create Physical Table Alias. Let's select that option.

When Create Physical Table Alias dialog opens, the only option is to provide a name for that specific table alias.

Let's repeat the steps above for all tables except dimension table D_TIME.

Once finished, Tables list should look like this:

The reason that we wanted to expose D_TIME is in the fact that we have two DATETIME columns in the fact table which are both important:

• TIME_BILL_DT: billing date
• TIME_PAID_DT: payment date

Both relate to the same dimension table, D_TIME. However, if we had only one dimension table for time, which of the 2 dates should we join with D_TIME as both are important. Solution is easy. Simply create 2 alias tables and problem solved:

• D_TIME_BILL_DT
• D_TIME_PAID_DT

We don't need a separate database table in our database schema, for Oracle Analytics one time dimension is enough and we are resolving this issue simply by introducing that many alias table as we need.

So, finally we have the following alias tables created:

• DIM_CUSTOMERS
• DIM_GEOGRAPHY
• DIM_PRODUCTS
• DIM_ORDERS
• DIM_TIME_BILL_DT
• DIM_TIME_PAID_DT
• FACT_REVENUE

We are ready now to do some Joins.

Joins

Joins are important because these can be understood as explicit instructions to BI Server to generate queries. Unless we have implemented constraints in a database already (which is not very often in data warehouses), we need to create joins manually.

We have got two options:

• use dialog driven approach to define and specify each relation between two tables or
• use graphical user interface and draw relations between tables.

Let's just demonstrate dialog driven approach first.

Under Tables tab we can observe that there is also Joins section.

If we wanted to add a new join, there is a plus sign () next to Joins. When clicked, Add Physical Join dialog pops up. Define a new join as follows:

Click Add to save and add a new join to the list.

We could continue defining joins one by one. Alternative approach is to do this graphically.

Select all Dim_ and Fact_ tables from the list of tables and right-mouse click to open a menu. From the menu select Show Physical Diagram and then Selected Tables Only. Instead of selecting all required tables, we could have included them one by one, simply by adding (drag and drop) them to the diagram when required.

As we can see in the diagram above, there is already one join displayed. It is join that we've created in previous step. Note the direction of the arrow. It is pointing from fact table into direction of dimension table. This indicates Many-to-One relationship between the two tables, looking from fact table.

Now we just need to add all other joins. Pay attention to the direction, drag from fact table out.

When done, simply close Physical Diagram 1 tab.

Final joins list should contain the following Joins:

This also concludes the session on Physical Layer. Physical Layer is now prepared and we can move to the next session which is about Logical Layer or Business Layer as it is also often called.