Information, tips, tricks and sample code for Big Data Warehousing in an autonomous, cloud-driven world

Using OCI Resource Manage to deploy a complete end-to-end data warehouse

Keith Laker
Senior Principal Product Manager
A while back, I announced in the Autonomous Database Newsletter (June 24 edition) that we now had a series of reference architecture patterns for Autonomous Data Warehouse. Currently, the following patterns are available (note: more patterns are being built and will be added shortly):

Each reference architecture pattern comes with the following: best practices framework; a set of recommendations which are a starting point for implementing the pattern and a terraform script for deploying the end-to-end set of cloud services on OCI. That was great news for everyone because you now had a very precise system architecture plan for each data warehouse use case that could be expanded to incorporate additional use cases as your data warehouse project evolved over time.

These patterns were the result of a some amazing work by our Enterprise Architecture, OCI andADB PM teams. There are a lot more patterns to come and I will let you know as new ones get added to the list.

I have used a couple of the patterns myself and, if I am honest, while the Terraform scripts are great it was hard work to setup all the required keys etc and get everything in to place so I could run one of the pre-built Terraform scripts. But then again, Terraform is a completely new area for me!

Then this week our OCI team announced they have added one of our data warehouse patterns to the list of pre-built stacks within OCI Resource Manager. Here's a screen grab of the list of built-in stacks that are ready for deploying:


Right now, we have the "Departmental Data Warehouse" pattern available as a stack and this deploys an instance of Autonomous Database and an instance on Oracle Analytics Cloud for you. So how do you get started? Here is a walk-through of the main screens:

Step 1) - I have logged into my Always Free cloud account and arrived at the main OCI home page:

Step 2 - I have opened the main menu tree and scrolled down to Resource Manager and then selected "Stacks"

Step 3 - As this is the first stack I have built my home page is currently empty. Obviously we click on the blue Create Stack button.

Step 4 - I can build my own stack deployment process or I can select to use a pre-built stack and this is what we need to use in this case...


Step 5 - this pops a form which lists all the available pre-built stacks as I showed above. In this case I want to build a departmental data warehouse so let's select that box

Step 6 - now I can set the name and description for my deployment "stack" along with the compartment I want to use in my deployment (if you are not sure what a compartment is or does then spend a few minutes looking at this page in the documentation). Once you are happy with the information on this page, click the Next button in the bottom left corner.

Step 7 - this brings us to the form which is going to capture the information about our ADW and OAC instances. In the screen below you can see that I have set the number of CPUs for my ADW instance as 1, I have set the password for my ADMIN user, I have changed the name and display name for my new database, I have elected to build a 19c autonomous data warehouse and I have enabled auto-scaling. For storage, I am going to start with 1 TB. Just point out....both the number of OCPUs and the amount of storage and can be scaled independently and with zero downtime. 

Step 8 - scrolling down....I can set my license type. We allow you to bring your on-premise licenses to the cloud or you can simply buy additional licenses as required. With this departmental warehouse project I know I am going to include personally identifiable information about some of our employees so I need to keep that data totally safe and secure. Therefore, I am going to register my ADW instance with our Data Safe service (which is free to use with Autonomous Database).

...and to further protect my ADW instance this pre-built stack automatically uses Access Control Lists (ACLs). If you want more information about to use ACLs then click here. This process within Resource Manager will automatically configure the ACLs so that OAC can connect to my ADW instance so that's all taken care of but I need to allow my desktop computer to access my ADW instance as well. Therefore, I have to enter the public IP address for my desktop computer. If you don't include this information then you will not be able to use any of the desktop tools or built-in tools that come with ADW.

Step 9 -  Setup the details for my OAC instance. Again, I have opted for 1 OCPU and I am going to take the ENTERPRISE_ANALYTICS deployment option since this gives me access to all the features I need. If you use the pulldown menu you will there are two options: 1)Self-service analytics deploys an instance with data visualization, and 2) Enterprise analytics deploys an instance with enterprise modeling, reporting, and data visualization.


Step 10 - and now we are at the end of the process and ready to deploy our end-to-end departmental data warehouse. The review page gives us a chance to make sure everything is correct before we hit the Create button. 

The Jobs tab allows you to monitor progress of your deployment and at the end of this process you should have a new ADW instance and a new OAC instance within your tenancy.  


You should now have your new departmental data warehouse ready for use,

What's Next

Next you can start loading data into your ADW instance. Take a look at the Chapter 3 in the ADW documentation which covers loading data with Autonomous Data Warehouse - https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/user/load-data.html#GUID-1351807C-E3F7-4C6D-AF83-2AEEADE2F83E

Then you can launch the Oracle Analytics home page in your browser, connect to your ADW instance and start building reports and dashboards. The OAC product management team has put together a great series of labs that will get you started with OAC and ADW, see here: https://www.oracle.com/uk/business-analytics/data-visualization/tutorials.html

Hope this was useful and I will let you know as we add more patterns to the solution library and more patterns into the pre-built stacks within OCI Resource Manager. 


















Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.