Thursday, March 21, 2013

Why should you put your data warehouse on a different system than your OLTP system?

There are a log of reasons. OLTP is designed to serve real-time transactional business needs. The idea is get in/get out quickly and efficiently. OLAP is designed to have large batch windows that aggregate and summarize data into reporting schemas. The two are dynamically opposed. The processing of the OLAP portion of the database kills the processing and memory utilization needed to keep the OLTP system active and "lively". The OLAP system also interferes with the data as it's locking and causing contention on data as it's creating the aggregate views that are needed.
The list for this goes on and on and on and on. They are two vastly different types of systems though. The resources will interfere with each other and end up choking your entire system to death.

To navigate from one item to a set of related items. Drilling typically involves navigating up and down through the levels in a hierarchy. When selecting data, you can expand or collapse a hierarchy by drilling down or up in it, respectively.

Drill down
To expand the view to include child values that are associated with parent values in the hierarchy

Drill up
To collapse the list of descendant values that are associated with a parent value in the hierarchy Slice and dice
This is an informal term referring to data retrieval and manipulation. We can picture a data warehouse as a cube of data, where each axis of the cube represents a dimension. To "slice" the data is to retrieve a piece (a slice) of the cube by specifying measures and values for some or all of the dimensions. When we retrieve a data slice, we may also move and reorder its columns and rows as if we had diced the slice into many small pieces. A system with good slicing and dicing makes it easy to navigate through large amounts of data.

The process of consolidating data values into a single value. For example, sales data could be collected on a daily basis and then be aggregated to the week level, the week data could be aggregated to the month level, and so on. The data can then be referred to as aggregate data. Aggregation is synonymous with summarization, and aggregate data is synonymous with summary data.

No comments:

Post a Comment