In this blog post I will present BI Architecture Diagram and explain how it can be implemented using Microsoft Products.
Personally I don't compare Kimball vs Inmon vs Data Vault but I believe they can live happily together, and they support each other with the exception that I follow Inmon Architecture ideas (Taken from DW 2.0 Book) but implement it using Data Vault and present it to users using Kimball Methodology.
Everything in this blog post presents my current point of view which is subject to change... If it changes I will update this blog post.
Content
Is it worth discussing the past and past challenges? I believe not so I have decided not to refer to the past at all! Let's focus what we want to achieve and how we can potentially do it.
Below I start with "The Ultimate BI Architecture Diagram" which focuses on Data Needs so I'm not going to add extra layers for standard reporting, Sharepoint (collaboration) etc. I also don't make it explicit so you can apply your own vision to it, however I will explain it in details and it is up to you to decide if you agree or not or which bits you agree with or not. I'm always happy to have a conversation about it so feel free to post your experience.
Did I call it The Ultimate BI Diagram? Yes I did! I want to underline this is Final Phase of a Data Warehouse (focused on data) from my point of view however there it is important to understand that achieving The Ultimate state:
1) May require evolution (in many different ways)
2) Final Stage might be expensive so decision to implement The Ultimate BI System MUST be justified.
To better understand what I mean let's create an image. Image a track suitable for F1 Formula racing. In order to perform well and "win" the race you need Formula 1 and might have to upgrade from time to time (according to regulations). We can consider Formula 1 Ultimate Racing Machine however does your business need Formula 1?
Maybe your business in not all about performance maybe you need safety and stability. If your business is about getting from point A to Point B every day and you drive on Motorway and through various Cities than maybe Formula 1 is actually not that suitable?
Maybe you want the same performance as Formula 1 but you are limited on Motorway to 70 mph, and you can achieve the same performance with much lower cost. So maybe you just need a standard car?
Choosing a Data Warehouse is similar to choosing a car. You need to know:
-
what you are going to do with it: Daily commute, Leisure, Shopping or work? (Taxi, Bus, Deliveries)?
-
You need to consider your environment: Motorway, Cities, Off-Road?
-
Who is going to use it?
-
How much value does it bring (money savings (compared to bus, taxi, train), convenience, time, satisfaction)
Once you've got the basics you need to set up a budget which includes:
-
Initial cost (or monthly cost payments)
-
Maintenance Cost (repairs, checks etc)
-
Operating Cost (Drivers)
and once you've got the budget it is time to do shopping. So you need options that take into consideration your budget and what you are trying to achieve but bear in mind your routine or goal may change in the future!
If you don't need Formula 1 does it mean you don't need to implement The Ultimate BI System? Not quite... Formula 1 is still a vehicle like a standard car so you need wheel and steering wheel and several other parts so it is more about how much you want to spend for each part and is it worth it? In other words you can achieve your goals in a different way... they still exists but might not be explicitly part of your BI Architecture.
Data Warehouse or BI system will often evolve so let's start with a picture.
Data Warehouse evolves in order to "survive" in changing environment and business have a tendency to change fairy rapidly.
.... very soon I will break down my diagram into small pieces and discuss it in the following phases;
-
Phase 1: Replicating Source System
-
Phase 2: Dimensional Model
-
Phase 3: Master Data & Data Quality
-
Phase 4: Raw Data Warehouse
-
Phase 5: Power Users
Take care
Emil
