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 (http://pedrocgd.blogspot.com/2007/06/ssis-temporary-image-to-msdn-forum.html) 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)
Regards!

BI CaseStudy - SSIS Package v2

Dear Friends,
In a previous post I described the first version of my current project main SSIS packages.
http://pedrocgd.blogspot.com/2007/06/ssis-packages-structures-v1.html

Today, I will describe how the main package changed. I‘ll probably do some changes to this version, depending on your feedback.
The majority of this objects are sequence containers objects and each of them has several steps inside.




This package will be executed in 95% of the cases for each day and it will import data of several data sources like SQL Server and Sybase. Sometimes the package could be executed not only for one date but between two dates to correct an unexpected error or for other unexpected reason.

1. SEQ VALIDATE Surrogate Keys
Step to validate my surrogate keys. (This step will be explained in a future post)

2. SEQ GET Configuration
Get the start and end date from the database table Configuration. These dates could be manual (stored in DB) or automatic (getting last date from main fact table Dados)

3. SEQ VALIDATE LastRunDate DADOS
Step to avoid inserting duplicate rows from an existent day in FactTable Dados

4. SEQ VALIDATE LastRunDate CASHFLOWS
Step to avoid inserting duplicate rows from an existent day in FactTable CashFlows

5. SEQ IMPORT SpotRates
Step to import currency rates based on Euro.

6. SEQ TRUNCATE Tables (Temporary)
Step to truncate tables. I will improve this step soon.

7. SEQ IMPORT TO FactTable CASHFLOWS
Step to Import data into FactTable CashFlows, FactTable BondsCouponLiq e BondsAmortLiq

8. SEQ GET IDENTITY Initial Values
Step to GET initial dimension identity values.
http://pedrocgd.blogspot.com/2007/05/ssis-populating-dimension_28.html

9. SEQ IMPORT TO FactTable DADOS
Step to import data into FactTable Dados and populate dimensions CounterParty and Instruments. Inside the sequence container there is several dataflows to import data into FactDados for each instrument like Equities, Bonds, Swaps, CapsFloor, Credit Swaps…

10. SEQ UPDATE FactTable DADOS
Step to update several fields like basel weight and risk into the rows inserted in the previous sequence container

11. SHRINK Database dbRentabilidade
Step to compact space in the database

12. SEND MAIL with SSIS Report
Step to send an e-mail with SSIS Report.

So, what improvements you think that could be done? Feel free to send feedback!!
Regards!

BI Events - SAS Portugal

Next november, it will be a big event about Business Intelligence and It will be organized by SAS Portugal. The BI comunity think that will be the bigger BI event in Portugal.

...And of course, I will be there!

See further details in the link below:
http://www.sas.com/offices/europe/portugal/eventos/sfp2007/index.html



Regards!

TPC02-SIAD - BI and decision-make definitions

As my teacher told us in the last class, business intelligence is putting intelligence in the business of an organization.

For me, the main purpose of BI is to provide information that support decisions and provide all the indicators that allows me to know If I took the best decision or not. BI filter out irrelevant information and focus only in the information that user needs.

I don’t need to be a decision-maker to know that for take a decision I must have value data and intelligence to decide. This intelligence to decide could be for example when you have value information and understand some information like market changes, risk assessment, poor employees performance… and can take preventive decisions in order to reach the organization strategy.

Giving some time to “gurus” of business intelligence I found an article that refers some relevant comments from Herbert Simon relating to decision-making.

“It is work of choosing issues that require attention, setting goals, finding or designing suitable courses of action, and evaluating and choosing among alternative actions. The first three of these activities (fixing agendas, setting goals and designing actions) are usually called problem solving; the last, evaluating and choosing, is usually called decision-making.” in TDWI website



“All four steps of the decision process elaborated by Herbert Simon and many others can be satisfied with analytics software that allows people at any skill level to perform the required operations. Super users will always have a role, and most people in an organization will never develop skills or interest in pursuits such as stochastic processes or simulation, but framing a problem and building a model can be a very simple process. Today, most BI efforts are driven by data, not by models, and the user interfaces, best practices and training are aligned with this approach. Toppling the pyramid means breaking through the data-only model and finding ways to distribute models and applications that can be used and shared by everyone.” in TDWI website

Tomorrow I will try to add additional comments.
regards

OFF-TOPIC - LinkedIn

Dear Friends,

Do you already know LinkedIn?! If not, go to http://www.linkedin.com/...

Is a website where you can store your profile and found old collegues and receive job notifications!

Add your profile today and and me as your connection!

Regards!

TPC01-SIAD - Master HomeWork

Dear Friends,
This subject is dedicated to my master homeworks and it will be written in portuguese.
Let's do it...

Todos os dias me deparo com novos problemas e como consequencia novos objectivos. Faz parte da vida o aparecimento de novos problemas, nós própris por vezes parece que andamos á procura de problemas, mas sem problemas a vida fica sem objectivo e não faz sentido. Por isso mesmo a minha missão nesta vida é cumpri-los na integra e continuar a ser feliz como tenho sido até agora.
De seguida apresento o meu quadro de problemas e objectivos que foi solicitado como TPC.

Cumprimentos a todos e bom trabalho!

BI Master - 1. Weekend

As I told you before, I started my master in “Integrated Systems to Decisions Support” . This master will be on Fridays and Saturdays.

The master in this first trimester will be focus in 3 main subjects:
- Integrated Systems to Decisions Support (Maria José Trigueiros - ISCTE)
- Management of DataWarehouse Projects (Patricia Narciso - GTBC)
- Data Analysis (Teresa Calapez - ISCTE)

I. Integrated Systems to Decisions Support
These first lessons was something seemed like a group therapy!
The main purpose was to make all the people “speak the same language”. There is some basic concepts that you think you know, but in the reality we have some difficult to give a definition to them.

It was some questions that made us to think…
What's a computer?
What's a problem?
Have all the problems one decision?
If there isn’t alternatives, there is a decision?
What's intelligence?

Feel de Gap!


II. Management of DataWarehouse Projects
In these first lessons, Patricia explained the most important differences between Bill Inmon and Ralph Kimball aproaches, and gave us a generic architecture that I think is a miscellaneous of both.

“Numerous data warehouse practitioners have questioned the different approaches to data warehousing as taught by Bill Inmon and Ralph Kimball. Religious wars have erupted between devout followers of each. As the industry has matured, the two philosophies have become much more aligned with each other. However, the language used to describe their methodology leads to questions of compatibility.”
Read full Susana Gallas article following this link
http://www.dmreview.com/editorial/newsletter_article.cfm?nl=dmdirect&articleId=1400

There is a discussion about these two approaches in the link:
http://blogs.ittoolbox.com/bi/confessions/archives/kimball-vs-inmonor-how-to-build-a-data-warehouse-10987

III. Data Analysis
The general feeling is that these lessons will be very hard.
It will be focus in data analysis with a lot of statistical techniques.
I only had a lesson in this subject and for that reason, I will resume this subject better in the next week.

Regards!

BI Certifications

Dear friends,
Nothing is more important that some entity recognize us an experienced and certified person. For that reason, is very important to learn more and get the best certifications of the world. If someone already has it, please give us some feedback about it. I will try to do the Microsoft exams during this month, and I will post here my comments.
See the links below and read all the benefits and requirements you have.

1. MCTS: SQL Server 2005 Business Intelligence

“The Microsoft Certified Technology Specialist (MCTS) certifications enable professionals to target specific technologies and distinguish themselves by demonstrating in-depth knowledge and expertise in their specialized technologies.
Microsoft Certified Technology Specialists in Microsoft SQL Server 2005 Business Intelligence (MCTS: SQL Server 2005 BI) implement and maintain business intelligence solutions. They have thorough knowledge of the Microsoft SQL Server 2005 Business Intelligence Development tool suite. They have knowledge of database schemas and of debugging, monitoring, and troubleshooting Business Intelligence solutions, and they know how to work with Microsoft .NET Framework. They can write database queries, use SQL Server Analysis Services (SSAS) data mining algorithms, and create and deploy SQL Server Reporting Services (SSRS) reports. Additionally, they author and deploy SQL Server Integration Services (SSIS) packages. Typically, Technology Specialists pursue careers as database administrators, database developers, or business intelligence developers. They may also be developers and systems administrators who do not work with SQL Server daily but who want to show their breadth of technology experience.”
In http://www.microsoft.com/learning/mcp/mcts/bi/default.mspx


2. Certified Business Intelligence Professional (CBIP)
“TDWI, the leading association for business intelligence and data warehousing professionals, offers the industry's most comprehensive certification program available: the Certified Business Intelligence Professional (CBIP). CBIP, a true test-based certification program, is offered in five key specialties for Business Intelligence success: Leadership & Management, Business Analytics, Data Analysis & Design, Data Integration, and Administration & Technology”
In http://www.tdwi.org/Certification/CBIP/index.aspx

Regards.

BI Events

Dear Friends,
There will be some very good BI conferences on September and October. I would love to attend it, but unfortunately I spent all my money in the BI master that I will begin next Friday.

PASS BI (Colorado, USA)
https://www.sqlpass.org/summit/Pages/default.aspx

TDWI (Orlando, USA)
http://www.tdwi.org/education/conferences/orlando2007/index.aspx

Check the above links with conference dates, contents, prices…
I’m sure that these events will be a fantastic BI Events and next year I will try to fly from Portugal to United States to see both!!!
Regards!

BI Master

Dear Friends,
After a long period, here am I again. :-)
This post is dedicated to my BI master that I will start on the next Friday for a duration of 2 years.

Check the content of the master:
http://dcti.iscte.pt/mestrados/msiad/base/frames.htm

I’m very motivated to get this master, it will give me a very good background in the BI technology. I will try to post here some relevant themes in order to get some comments from you in the MSDN Forums or right here in the blog.
Kind regards