Operational Reporting from a Dimensional Model

[AndyPainter]

Having worked with various models over the years, i'm begining to think that a multi layered data model approach is best for medium and larger corporations. (see my IM model here http://enterpriseinformationmanagement.wordpress.com/information-management-architecture/)

I've seen lots of times where a dimensional model doesn't work as well for 'operational reporting', and an ODS would have been better suited ... but this would mean having a series of different models/data layers.

1. OLTP - Line of business/source systems
2. ODS/MDM - data models for operational reporting and consolidation of corporate data, 3NF/data model patterns
3. EDW - the core data warehouse, modelled using dimensional techniques including SCD II for history
4. Data Marts - cubes, cut down dimensional models, Netezza type appliances

I fully understand there is a cost involved in creating/maintaining and populating all the data models, but i've still yet to see one model/system that can provide covereage for 2 - 4, allthough Teradata would have you beleive they can do it with the 'Active Warehouse'. If the corporation is small or has limited volumes of data, it probably feasible to get away with just having 1 and 3.

From a delivery perspective, the above doesn't need to be delivered all in one go, but can be built out as needed following the blueprint.

Any thoughts/comments?

[Jeff Smith]

A data warehouse is not necessarily a single database. A data warehouse is made up of all of it. A good data warehouse has consistency. If multiple data bases are needed, then that's the need. But, the more data is duplicated, the more etl processes that exist, the greater the chance that 2 people can come up with 2 completely different numbers for the same thing.

[Jeff Smith]

Your MDM is the staging area for data. If near Real time reporting is needed, it comes from the MDM. Your EDW includes everything that is fed from the MDM. Many database software and reporting software blur the boundary between a Cube and a dimensional database.

As far as the term Data Mart - ask 10 people to define data mart and you'll get 10 different answers. Sure, you can create a variety of databases for specific facts, and as long as they share common dimensions, the the system is viable. But with today's reporting software, you can make a single database look like multiple "marts" with different user groups seeing different aspects of a single large database. And on the flip side, the reporting software can make multiple databases look like a single database. There are so many options - each fact table be in it's own database and then create a shell database with views to tables in databases with physical tables. Create one big database with all of the tables and create a series of shell database with views make to a single fact table and it's dimension tables.

Worried about too many people hitting the database or worried that data mining activities will over burdent the database and slow performance for the people who need standard reports? Then put the database on a SAN and let multiple servers hit the same database allowing miners to bring their server to it's knees without impacting other users.

The point is, there are a multitude of ways of achieving the same thing but the key is to make sure the dimension tables are shared.

I prefer one database and controlling access with the reporting software. I never know what data someone is going to need.

[ngalemmo]

Generally speaking, to support operational reporting you need to store a lot of transitional information that changes frequently and has little historical or strategic value to the business. That type of stuff can significantly overburden the design and maintenance of a dimensional data warehouse, so, as you point out, an ODS is used.

What I have found is that implementing an ODS can significantly improve and simplify data capture for loading the data warehouse. Because you can control the design and implementation of the ODS, it can be closely integrated with the DW, such as sharing key dimensions and delta capture for fact loads.

[Sponsored content]