As the last post in this series, I wanted to recap and try to give a high level of what we wish to accomplish.
If you remember on the first post we wanted to achieve two things, and that was not to hurt the performance of the Applications or of the BI Solution.
In the second post, we defined some generic ways of thinking about data in general. Things like concentric data, superfluous data, solitary data, relational data, etc.
Then we look at a way to keep the number of SQL Jobs down so that we have a maintainable system in the end.
Then we looked at a way to monitor the system to make sure that performance does not degrade over time.
We even touched on a way of getting data from one system to another, and started up another set of related posts on that subject, specifically named "Getting data from Point A to Point B".
So as a graphical recap here is the high-level view of what we what to accomplish. Note: When it comes to graphics, I hope that you have already notice that other than screen shots, I am somewhat graphically impaired.
Now, on the left side you can see that we can have many different applications, some out on the internet, one multiple servers of our own, and not necessary all running the same version of SQL, or that matter maybe not even running SQL at all. It could be anything including spreadsheets, text files, etc. The idea is data is data it has to come from somewhere and at least parts of it relates to data from other systems. Check the second post for my terms and definitions and the rules that go along with them.
In the middle, you have one or more SQL Servers that handle the Stage 1 database(s) (for lack of a better name). This is where all of the data from the various systems ends up so that we can combine it, match it up, validate it, and in the end have our "one version of the truth". Rarely will anyone have access to this server or set of servers other than us. Moreover, is where most of the work is truly done in regards to creating warehouse tables, cubes, etc..
To the right of that is the final Business Intelligence Server. This is what the users actually see to get the information that they need to make decisions, regardless of what specific tool they use, or how they get here; this is where the actual final data lives. Now depending on how you want to set your security there may be multiple databases, cubes, etc. or just one. There are pros and cons to each but it is really up to you.
This wraps up Enterprise Data Warehousing for Consolidated Business Intelligence.
The only remaining things are getting data from point a to point b, and what tools the users want to use, which in my own opinion really should be as many options as possible and up to the users themselves.
<Previous Post in this Series>