In Part I we looked at the advantages of building a data warehouse independent of cubes/a BI system and in Part II we looked at how to architect a data warehouse’s table schema. Today we are going to look at where to put your data warehouse tables. Let’s look at the location of your data warehouse. Usually as your system matures, it follows this pattern: Segmenting your data warehouse tables into their own isolated schema inside of the OLTP database Moving the data warehouse tables to their own physical database Moving the data warehouse database to its own hardware When you bring a new system online, or start a new BI effort, to keep things simple you can put your data warehouse tables inside of your OLTP database, just segregated from the other tables. You can do this a variety of ways, most easily is using a database schema (ie dbo), I usually use dwh as the schema. This way it is easy for your application to access these tables as well as fill them and keep them in sync. The advantage of this is that your data warehouse and OLTP system is self-contained and it is easy to keep the systems in sync. As your data warehouse grows, you may want to isolate your data warehouse further and move it to its own database. This will add a small amount of complexity to the load and synchronization, however, moving the data warehouse tables to their own table brings some benefits that make the move worth it. The benefits include implementing a separate security scheme. This is also very helpful if your OLTP database scheme locks down all of the tables and will not allow SELECT access and you don’t want to create new users and roles just for the data warehouse. In addition, you can implement a separate backup and maintenance plan, not having your date warehouse tables, which tend to be larger, slow down your OLTP backup (and potential restore!). If you only load data at night, you can even make the data warehouse database read only. Lastly, while minor, you will have less table clutter, making it easier to work with. Once your system grows even further, you can isolate the data warehouse onto its own hardware. The benefits of this are huge, you can have less I/O contention on the database server with the OLTP system. Depending on your network topology, you can reduce network traffic. You can also load up on more RAM and CPUs. In addition you can consider different RAID array techniques for the OLTP and data warehouse servers (OLTP would be better with RAID 5, data warehouse RAID 1.) Once you move your data warehouse to its own database or its own database server, you can also start to replicate the data warehouse. For example, let’s say that you have an OLTP that works worldwide but you have management in offices in different parts of the world. You can reduce network traffic by having all reporting (and what else do managers do??) run on a local network against a local data warehouse. This only works if you don’t have to update the date warehouse more than a few times a day. Where you put your data warehouse is important, I suggest that you start small and work your way up as the needs dictate.

Here we will use the Mulesoft Anypoint platform to attach VPC to the AWS transit gateway to form a single network topology.

Cloud platforms provide greater flexibility. How do you choose to compute service in Google Cloud?

The serverless offerings of AWS are getting more and more popular. But it remains a challenge to know them well enough to leverage them properly.

In this article, learn how to build a Wasm UDF to perform sentiment analysis on data already stored in SingleStoreDB.

The Decorator pattern is a great fit for modifying the behaviour of a microservice. Native language support can help with applying it quickly and modularly.

We will discuss file uploads with the help of the IFormFile Interface and others provided by .NET and step-by-step implementation using .NET Core 6 Web API.

Discover the key differences between Databricks and Snowflake around architecture, pricing, security, compliance, data support, data protection, performance, and more.

This article talks about the architecture of DBMS (Database Management Systems), with their structure, advantages, features, and more.

Getting Started with Kubernetes is NOT easy. This article will help you understand some of the most important concepts of Kubernetes.

Building internal cost tools with AWS starts from understanding the CUR schema.

Learn what data ingestion is, why it matters, and how you can use it to power your analytics and activate your data as an essential part of the modern data stack.

This article will help automate the process of creating and configuring an Amazon Aurora Postgres Global Database. It also describes ways to handle fail-over scenarios.

The widely adopted SCM tools we use today, GitHub and Gitlab, are built on the dated architecture and design of git, but this has some security gaps we'll explore.

Continue on a journey into the world of cloud-native observability: go out onto the playing field to understand who the players are and what teams they form.

Explore the difference between two well-known Auth building methods: AWS Cognito and JSON Web Token. Plus, take a look at the AWS Cognito application process.

Check out the benefits of deploying a service mesh, popular tools for deploying a Service Mesh, and more here in this article.

CDK has landed, and the AWS community is hyped, but what exactly is CDK, how it works, and what does it mean for the world of Infrastructure-as-Code?

Gain context of three separate API solutions which can be used to validate name input fields within an application as you follow along with this demonstration.

This tutorial is an attempt to integrate CockroachDB CDC with Azure Event Hubs via our existing Kafka support.