Sunday, October 7, 2007

BI Tools - First look at Microsoft BI Tools

Dear Friends,
Following a suggestion of Rui Santos (master colleague), I'll try to write a draft about Microsoft BI Tools using my current BI project.
As you probably know, the current version of SQL Server 2005 has two separate environments, one for management and one for development.
The development environment is called Business Intelligence Development Studio (BIDS) and is focused mainly in business intelligence area. Includes a module for Data Integration (SSIS), another for Data Analysis (SSAS) and another for Report (SSRS). The management environment is called SQL Server Management Studio (SSMS) and it’s here that you do all the work managing your databases.

I don’t know yet the other BI tools, but what I heard from community is that Microsoft tools are more “user-friendly” than others. But in my master I will try SAS tools and I will know if it’s true or not.
I’m very satisfied with Microsoft, and If today BI is more popular is because Microsoft is investing a lot in BI technologies. Why I am say this? Well… is my opinion, but there was big improvements particularly in SQL 2005, SharePoint 2007 (usually used to file share in 2003 version), the acquisition of ProClarity (tools to analyze BI data), the acquisition of Dundas software (Report tools), Office 2007… and others…

For these reasons, I think BI is the right path to our careers, particularly Microsoft! But I think if we know the BI concepts, with some extra work we can use other tools like SAS, WebFocus, SAP and others without problems.

My current BI project is very complex and the old system was too difficult to understand ( because it seemed like a data warehouse inside excel and access files… could you imagine that?!
In overall, the big difficulties was understanding the old system and define the best architecture.

Below I will show you some screenshots of Microsoft BI tools in my project.

SSIS - SQL Integration Services
As I wrote in a previous post, the goal of BI is provide the business with high data quality using intelligence! This module provides the right tools to transform, clean and audit data from several data sources and unsure that this data will be inserted correctly and compatible with destination structure – Is my concept of ETL.

Control Flow

Control Flow (Sequence container expanded)

SSAS - SQL Analysis Services
This module consisted of two major, and complementary, pieces of functionality: OLAP and Data Mining.
In this module you can add KPI (corporate measures), perspectives, translations and you must know the language of querying multi dimensional databases, MDX.
MDX is similar to SQL but while in SQL we query data from OLTP databases, with MDX we query data from OLTP databases.

Data Source View

Cube Designer

Calculations Designer (MDX Sintax)

Cube Browser

Cube Browser with some data

SSRS - SQL Reporting Services
Using the MDX, it's possible to create queries to an OLAP database and display here inside a table, matrix or in a chart.

Data designer (wizard)

MDX Data Designer

Report Designer

Don’t forget, this is a draft about each SQL BI module. If you think a more specific description of this subject could be useful, I will describe it better. (In the right panel you could see links of my preferred books)


Rui Almeida Santos said...

Olá Pedro.
Sinceramente gostei desta tua iniciativa de mostrares algumas das ferramentas utilizadas para construir um BI.
Concerteza que esta visão do SQLServer da Microsoft é-nos muito útil, não só para abrir o apetite para o que aí vem, mas também para podermos estabelecer uma comparação com outras ferramentas similares.
Entretanto Pedro, os meus parabéns pelo Post e se quiseres aprofundar o assunto, eu serei um dos teus leitores mais interessados.
Rui Santos

Pedro said...

Ola Rui!!
Obrigao mais uma vez pela tua visita a este humilde blog!
Irei aprofundar com toda a certeza!!
Um abraço e obrigado pelo comentario!

Anonymous said...

I am using Fintel's business intelligence tools and would like to recommend the same to all. You can get the complete info about the tools at the Fintel's website-

Pedro said...

Hi Anonymous,
Thanks for your visit to the blog.
In spite of advertising your products why you dont give us feedback about the things that your product could be bring that Excel doesnt... it'll be better, no?

Anonymous said...

Hi Pedro,
I saw your BI articals on and they are very useful for me..i am trying to subscribe since so long to get help from you on SSIS. but i am not getting any confirmation mail from the site. for login first time.may i know where i can get help from you?

Pedro said...

Thanks for your visit and kindly words!
You can contact me directly to my email to resolve some issue you could have!!

Sofia Sana said...

Yeah its really true...
BI is the right path to our
careers, particularly Microsoft!
But I think if we know the
BI concepts, with some extra
work we can use other tools
like SAS, WebFocus, SAP and
others without problems.
You deserve appreciations
for giving out this article;
it’s been a really fantastic remarkable read.

how to market your business


Related Posts Plugin for WordPress, Blogger...