Data Modeling for BI Analytics vs Reporting

To create data models for business intelligence, you first need to understand your BI Blueprint, then it is pretty critical to truly understand the difference between reporting and analytics (R&A) data models.  Modeling data for R&A happens in pipes #5, #7 and sometimes (but not optimally) in the DSVs (data source views) found in pipe #8.  

Keeping in mind that tables arranged in a circle does not a star schema make, below is a slide that articulates in part the difference between the two.  Think of reporting as a pile of Tinker Toys -- you SELECT tables and JOIN...JOIN...JOIN to a bunch more.  Analytics is about flattened "denormalized" data arranged into subject area dimensions and measure groups, preferably with pre-processed totals, stored like Rubik's Cube.

If you are serious about data modeling for business intelligence, dig deep into the following concepts each which are worthy of individual blog posts.

1. Subject area dimensions vs tables that contain codes and relate to transactional records
2. Slowly changing dimensions
3. Correct modeling of deleted rows
4. Denormalization techniques
5. Degenerate dimension and techniques for handling large dimensions in cubes
6. Modeling for many-to-many relationships
7. Modeling for predictive analytics
8. Modeling for ABC (audit, balance and control) aka metadata and data verification

When I get my blogging juices on, I'd like to post a bit about each one.  In the interim, you can contact me in About.