Oracle Data Integrator (ODI) Web Edition and Data Transforms (built-in tool for Autonomous Database) are simple to use data integration tool. Although it is based on Oracle Data Integrator, much of the complexity in using the tool has been simplified so that data analysts or departmental users can build data integration projects quickly.

Keeping up with the theme of “ease of use”, now a data warehouse building wizard is added for NetSuite users. This wizard creates the data warehouse model and all the ETL flows automatically.

Note that users who simply want a NetSuite data warehouse, have an option to use NetSuite Analytics Data Warehouse (NSAW). You can read about NSAW in this link.

However, if they want to build a custom data warehouse with full control on the ETL flow, then data warehouse builder feature of ODI Web Edition is a must have. Examples of customization include, but not limited to:

  • Adding/modifying measure
  • Adding/modifying dimension hierarchies
  • Integrating data from non-NetSuite sources

This blog post guides you step by step to build the data warehouse from NetSuite.

Setting up Connections

Start with creating connection to your NetSuite source.

Provide the NetSuite credentials.

You will also need to create connections to your Autonomous Database for staging area and for data warehouse. Both staging area and data warehouse can be in the same Autonomous Database in different schemas.

Starting NetSuite data warehouse build wizard

Now we will kick off the NetSuite data warehouse build wizard. This wizard is available by right clicking on the NetSuite connection.

We need to provide the connection to the staging schema and the target schema, for the data warehouse.


 

Select the NetSuite business areas you are interested in.

After you click on “Save”, the wizard kicks of a background process to build the entire data warehouse.

No further action required!

Data warehouse is ready! Analyze data using data analysis tool

The process will take some time (minutes) depending on the size of selected business areas, creating the following objects:

  • Data Entities (staging tables, facts and dimension tables)
  • Data Flows from source to staging and staging to data warehouse tables
  • Workflows to kick off related data flows
  • Analytic View in Autonomous database with measures and dimension hierarchies.
  • Dashboard for data analysis

Note that the generated ETL process is quite sophisticated and uses the following methodology:

  • Creates audit records to keep track of DW load process for incremental extracts
  • Date based incremental extracts coded in data flow to load staging tables
  • Proper denormalization done by joining parent/child tables of NetSuite transactional data
  • Deletes are handles as well by keeping track of primary keys

User will have to start (or schedule) the main workflow to load the data.

After the data is loaded, users can simply use the data analysis tool in Autonomous Database to start analyzing.

 

Click on the dashboard which is created by the wizard.

Select the Analytic View for data analysis.

What’s next

So far, the wizard did everything from creating data warehouse model and ETL flows to creating the dashboard for analysis. However, all the objects are customizable as needed. User can simply review and edit any existing data flow and modify it.

Hundreds of automatically created data flows provide a great starting point to build the custom data warehouse for NetSuite.