Adding New Physical Column to an Existing Fact or Dimension Table in Oracle BICS

3 years ago  •  By  •  0 Comments

Accelerant specializes in Oracle Cloud Computing, Business Intelligence, and Data Mining. We help our customers unlock actionable information scattered across multiple systems. Our comprehensive approach provides strategic roadmap, technical expertise, post-delivery support. Accelerant is Gold Partner with Oracle.

Oracle Business Intelligence Cloud Service (BICS) is the industry’s first BI platform (or technology or system) in the cloud that makes analytics available to everyone. Cloud is defined as access to a pool of computing resources (servers, storage, networks, services, etc.) that can be rapidly provisioned and made available with minimal overhead.

Need to add a new physical column in the existing fact or dimension table of your model and need help?

Follow the steps below how to do it.

In our example we are adding a “Month_ID” physical column in our “All Drivers Scores Dim” table.

1) Sign in to Oracle BI Cloud Service.

2) Click the Modeler icon on the Home page.


3) You must always lock the data model before making any changes. Click Lock to Edit to lock the data model.




4) To add a new physical column in an existing fact or dimension table, first select the table in which you want to add. Here we are selecting “All Drivers Scores Dim” table from our dimension tables.

PC35) After clicking on the table link, you will see the “Source” file name in Overview tab. Copy the Source file name because we want to filter for the source file name.PC4

6) In the left pane of the modeler click the Filter icon to the right of the selected menu.


7) In this Filter area, enter a string value for filtering the display. So paste the copied file name in “Filter Database Objects.”





8) Now we found the view of “All Drivers Scores Dim” table. Select the view and click on the “SQL Query” tab. Copy the table name (which is also a view) “V_ALL_DRIVERS_SCORES.”
9) Repeat the step 6. Now you will see this table name is not a view.

10) Now you have to add a new column in SQL Query. Type the column name “ALL_DRIVERS_SCORES”.”MONTH_ID” as we did in below screen shot.

11) After adding the name, click on the Save button on right side of the page to save the changes.

12) Similarly, you need to add the same column name in source of “All Drivers Scores Dim.”

13) After adding the column name, go to the “All Drivers Scores Dim” table Overview tab. To the right of the page click on the gear icon and select “Synchronize with Database”

14) A “Failed to synchronize table(s)…” warning appears at the bottom of the page. Click on the link “Add columns to table All Drivers Scores Dim” as highlighted in screen shot below.

15) Then another notification “There is a new column in table V_All Drivers Scores Dim. Column name is Month_ID” appears at the bottom of the page. The new physical column name Month_ID has been successfully added in the table.