1

I have been reading up on Data Warehousing and its purpose. The main purpose seems to be to track historical data and whatever changes may have occurred over time.

The DW book I am reading gives a number of reasons for using a DW schema...

1) Normalized schema may have 1000's of tables

2) Naming conventions may not be enforced

3) Data may be in multiple databases

4) Data quality issues

5) Historical data requirements

I am now working on a project where it has been suggested to me to use DW schema. However, none of the above 5 reasons apply to my situation.

1) Data is in region of 10 tables

2) Columns are named correctly 3) All data is in a single database

4) No reported Data Quality issues

5) No requirements for historical data

So, is tracking historical data the main reason to use DW?

  • 1
    Have you considered what performance hit a query over 2, 5 or maybe 10 years worth of data might have on your production database? – Ben Oastler Nov 5 '14 at 22:08
7

As a BI consultant, my view on datawarehousing is that it provides (primarily) non-technical users with an easily accessible set of facts and dimensions.

Often, you'll see the following features in a data warehouse:

  • denormalized dimensions,
  • simplified fact tables and measures,
  • pre-aggregated balances,
  • pre-accumulated amounts over some time dimension,
  • often with some business logic (distributions, calculations, statistics) already applied to the data,
  • dimension data organized as a hierarchy where originally wasn't,
  • etc

All of this serves to simplify the understanding of, and accessibility to, the business data for people who aren't too comfortable writing SQL queries or using pivot table tools. Some of the greatest pitfalls in data warehousing and BI involve users not understanding the structure or meaning of the data, producing incorrect results and subsequently not trusting the BI solution.

2

No.

As you stated, data warehousing is valuable:

  • When you have multiple source systems of data
  • When you have data quality issues
  • When you want snapshotting
  • When you want the ability to quickly look at a large set of data across many disparate dimensions, hierarchies, and aggregations

Generally speaking, data warehousing comes into play when you are looking for a "single version of truth" among all of your data, and so you need a single place (the data warehouse) to go for data reporting and analysis.

If your users are happy with the reporting capabilities of your existing database model, then I wouldn't worry about data warehousing. Judging from the scope of your system, it seems like it would be rather trivial to provide end-users with the data analysis and reporting they need without adding on a data warehouse layer on top.

1

Another reason to use a data warehouse or dedicated reporting server is to avoid placing load on a production OLTP server. Or if you have to write reports which include data from different systems. The way you have described your db I doubt there would be much advantage in denormalising unless you wanted to build OLAP cubes on it. In which case an OLAP friendly schema might have significant benefits.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.