Beginners Guide to Business Intelligence: What is Business Intelligence About?

I have decided to do a beginners guide to BI on my blog. I thought this would be a great way to tie all the resources I use and give you a good idea for an approach to BI projects. I am starting off with a question that needs to be answered.


What is Business Intelligence About? Decisions of Course

I often find myself in situations where I have to try and explain Business Intelligence. It’s not that easy to define BI in a universal definition that everyone agrees with. BI is as much a collection of technologies and principles as anything else.

So here it goes,

‘Business Intelligence is about taking the guess work out of the decision making process.’

That’s right it’s not about technology or a wonderful new process. It’s about the management decision making process that occurs in every business. To demonstrate this in more detail lets review what the Management Decision Making Process (MDMP) is about.

The MDMP was introduced to me on my first data warehousing project by Peter Nolan the architect of the project. He took me through the concept of why you want to start a BI project in the first place. He posed a simple question, ‘How is Business?’

There are four primary drivers that make up a cycle that form the MDMP. Consider each factor as deliverables forming a BI platform. The diagram below shows four primary drivers for the MDMP.

Made an Investment – Every organization makes an investment either in a product or a service. The tracking of the investment is the first driver in the MDMP. For example a product stock keeping or a resource schedule report allows a compony to track its resources.

What Happened? – The second driver covers the result of the investment. Did the investment payoff, did we make a sale? For example a sales report allows business to track the success of the investments.

Why did it happen? – The third driver is to analyse the success of the investment. Organizations use OLAP analysis Ad-hoc reporting to drill down to the reasons for the performance.

What if? – The fourth and final driver and the most difficult to implement. Organizations are constantly looking for new opportunities to invest, questions like; what is the impact of further investment? What if we gave 10% further discount? Looking for new opportunities with data mining and forecasting techniques helps answer this question.

So how do you deliver MDMP?

When starting a BI project you must set firm goals around the end deliverables. A great place to start is taking a single business process and mapping to the MDMP. (Selecting a business process is an entirely different subject for a blog.)

Once a process has been selected a solid business case been derived using the MDMP. The four primary drivers of MDMP will allow you to set clear goals for delivery that the business can understand.

SQL Server 2005 vs. MDMP

Reviewing the SQL Server 2005 product against MDMP shows how much BI functionality Microsoft has release in the version. Below is a table showing the reporting functionality SQL Server 2005 has built into support the MDMP.

Conclusion

Basing your BI project on the MDMP you have a common point of view across the business and technology requirements are key to delivering a successful project. For further information on the MDMP check out the resources below.

Resources

Getting Started and Finishing Well By Peter Nolan
http://www.intelligententerprise.com/010507/print/webhouse.htm

Where is Peter?
http://www.peternolan.com/

Beginners Guide to Business Intelligence: What is a Data Warehouse?

Download SQL Server 2005 now!

Hello All,

Again I am completely snowed under with work so I have not written any blogs since Monday.

I am currently working on feature for this blog and I will have it out soon.

Now is a good time as any to get started with SQL Server 2005. Download the current BETA CTP from http://www.microsoft.com/sql/2005/productinfo/ctp.mspx

Good Luck

Myles

What I am going to see at TechEd 05

Hello All,

Well its 17 days out to New Zealand TechEd 05 and I can’t wait.

I know I have not blogged for a long while. This is due to work commitments and TechEd prep. I am still in the middle of a large Data warehouse project. It’s been running for the last 8 months and nearing completion.

There are a few TechEd sessions I am really looking forward to seeing. I thought it would be a good idea to share them with you. Below are the key sessions I don’t want to miss.

• BIN303 - Exploiting Data Mining in SQL Server 2005 - Richard Lees
• BIN304 - SQL Server Integration Services: Deploying, Managing and Loading a Data Warehouse - Tony Bain
• BIN310 - SQL Server 2005: End-to-End Part 3 (Analyze and Act) - Thierry D'Hers
• DBA313 - Architecting a Large Scale Data Warehouse with SQL Server 2005 - Doug Barrett

I haven’t listed the times here as the schedule may change for the latest TechEd schedule see: http://nigelp.members.winisp.net/schedule.htm

Bye for now.

Myles

TechEd 05 Preparation – Project Real

Hello All,

While I am doing prep for TechED 05, I came across a couple of really good public links to project Real. For those of us that are going to be converting a lot of SQL 2000 Clients to 2005 it's on the 'must read list'.

Check Out:

Project REAL: Technical Overview
http://www.microsoft.com/technet/prodtechnol/sql/2005/projreal.mspx

Project REAL—Business Intelligence in Practice
http://www.microsoft.com/sql/bi/projectreal/default.mspx

Happy Reading

Myles

News Group Post: Slowly Changing Dimensions Type 2

Note: This is a response to a news group posting. Due to formatting problems I have reposted the response on my Blog. Don't worry I don’t intend this again.


Rico asks how I can implement type 2 changes in a dimension.

Hello Rico,

Type 2 is relatively easy to implement, but it can be a pain for users to understand and use.

Implementing Type 2

Generally I use a set of standard flags to track the changes in the dimension. Each flag is used to determine the state of the record. I am assuming that you are using Surrogate keys in your dimensions and Fact tables.

Date From – The date the record arrived in the dimension
Date To – The date the record is deemed to be changed
Current Flag – The State of the record Y or N

With these flags you can track changes of the dimension at the lowest level such as employee changes.
e.g.

Employee Dimension



You will have to come up with at change capture process. If you have type 1 deployed already it would be the same logic for identifying the changes, with the exception of updating old records and a creating new records in the dimension. Remember you will have to update your surrogate key lookup in you fact table build to load with the current dimension record. I.E. “WHERE Current Flag =’Y’ ”

Implementing Type 2 without Flags

To be honest I have not tried this method but it worth considering if your Business users have problems reporting using the Type 2 Flags. Rather than having one Employee Dimension create a series of mini dimensions against the fact table. This would allow you to track changes against the fact record instead of the Dimension.

E.G.

Employee Dim is broken into three new dimensions (Dim Manager, Dim Employee, and Dim Job Title) as shown in the diagrams below




There is a HUGE draw back to this you end up a large amount of dimensions and a really wide fact table if you have to include a lot of them.


Hope this gets you started.


Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/