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.

PC1

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

 

 

 

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.

PC5

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

PC6

 

PC7

 

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.”
PC8
9) Repeat the step 6. Now you will see this table name is not a view.
PC9

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.
PC10

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

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

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”
PC13

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.
PC14

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.
PC15