CODEWITHSUNDEEP
databasedata-warehouse
Tae-Sung Shin
Tae-Sung Shin

Reputation: 20620

Operational data warehousing for both data entry (OLTP) and BI (OLAP)

I am thinking of a simple way to build operational data warehouse leading to (near) real-time BI.

For example, I am using SQL server platform for database applications. One application is for OLTP data entry and the other with data warehousing, eventually used for SSAS and BI reporting.

I know common infrastructure for BI is

OLTP database -> Periodically updated data warehouse -> Analytic cube -> Reporting,

where OLTP (normalized) database handles transactional operations and data warehouse stores updated data for BI operations.

But I am thinking of two ways to make data warehouse real-time.

  1. Make data warehouse (denomalized) handles transactional operations as well without OLTP database.
  2. Update separate data warehouse whenever data entry is done in OLTP database

The reasons for #1 and #2:

  1. There would not be much second-to-second transactions in the single OLTP database for my application
  2. Slow transaction does not hurt my applications
  3. There would be 10 or so tables in the OLTP database
  4. Main selling point of my applications is near real-time BI application.
  5. With the operational data warehouse, there is no need periodic update from OLTP

Of course, unknown part for #1 is how slow transaction will be when the analysis cube is being updated. But method #2 solves this problem especially when data update for data warehouse is asynchronously done.

I would like to ask if there is any gotcha I missed or if there is any better method to build operational data warehouse.

Upvotes: 0

Views: 344

Answers (1)

Marc Polizzi
Marc Polizzi

Reputation: 9375

icCube is an in-memory OLAP server written in JAVA that can source its data from any relational DB. It supports incremental load for near real-time BI.

The DB servers won't be impacted when the cubes are updated as icCube is building the cubes in a separate process and then all the queries are done to this external process. You'll have to configure icCube to check periodically for updates in the DB servers (e.g., you can have a trigger table for a quick/low-cost check).

It supports MDX/XMLA for Excel access for example and has its own Web Reporting package.

Upvotes: 1

Related Questions