SQL Bits III - UK Event

Dear Friends,
Do you remember SQL Bits last march?! No? If not check my post here:
http://pedrocgd.blogspot.com/2008/03/tmp.html

Yes it’s true... SQL Bits will take place next September 2008 and I hope to be there!
SQL Bits is an event organized by UK SQL Server Community and if you attend the event, could be your opportunity to learn and share knowledge with the main gurus of SQL Server. I was in the last SQL Bits, and believe me... it’s a very important and very organized event that you must attend... Behind the SQL Bits you can see persons like Simon Sabin, Chris Webb, Tony Rogerson, Martin Bell, Darren Green and Allan Mitchell that has publish several books and has a lot of knowledge to share with us.

The SQL Bits III will be focus on four main areas:
1. Business Intelligence
2. Database Administration
3. Development
4. SQL Katmai version

And you can register on the sessions you prefer. And in the next event was introduced a new feature that could be very interesting... the Training Day... In this new feature you pay a simbolic price of 150£ to receive one intensive day training from SQL Bits organizers on one of the four main areas of the event.

What are you waiting for?! Check all the available sessions and register right now!!
http://www.sqlbits.com/information/Registration.aspx

Attention to Amin (Iran), Bruno Ferreira (Ireland), Ella Maschiach (Israel), Subhash Subramanyam (India) and other BI fanatics... this could be a good oportunity to meet you and take a good time!
Cheers!!

Business Intelligence Architecture

Dear Friends,
Once again, I’m writing a post with a long delay… Sorry… Today, I will follow the sugestion of several blog visitors, which is to describe and write more about what I’m doing in my master BI degree and my point of view of BI.

After nine months of master classes I’m able to describe my definition and my opinion of BI. My purposes with this post, and based not only in the classes but also in the several books I read and work experience as BI Developer, is to launch the discussion about what is BI and mainly how is BI structered.

Business words

The image shows the four pilars but also in which context the several business words related could apply in BI. Here in Portugal, and I thinks is the same in rest of the world, IT Consulting Companies usually create some confusion into the market, invented new business words, and curiously, almost times only these business words are new!

For me, BI is based in four main pilars:

Strategy

This pilar is where the top management defines the strategy and the path that must be followed to achieve the organization goals! The typical metodology to structure this strategy is the Balanced Scorecard (BSC) created by Kaplan and Norton. They recommend that managers gather information from four important perspectives:

Customer's perspective. How do customers see us?

Internal business perspective. What must we excel at?

Innovation and learning perspective. Can we continue to improve and create value for our services?

Financial perspective. How do we look to shareholders?

Processes

This pilar is not yet very evolved, and only few years ago, organizations start looking to processes in other perspective. Following Paul Harmon, in the article he wrote in http://www.bptrends.com/ about maturity level of organization processes, he realized that almost organizations are in a low level (2,5 in a range of 1 to 5) and still worst here in Portugal... And in my opinion, processes is the better way that an organization has to improve performance, reduce costs, and unfortunately in some organizations a better way to implement downsizing, because automating and improve a process may cause that a person could be moved to another unit or to be fired reducing costs for organization. It’s cruel, but almost organizations in the world focus on reducing costs finding a solution to fire people… but this is another discussion point! Business Process Management (BPM) is based in other past investigations like Business Process Reeigineering (BPR) and Total Quality Management (TQM). BPM or processes, could be monitorized by a Business Activity Monitoring (BAM) that is nothing more that a solution with KPI to monitor the performance of each process, and could be implemented a Business Process Management System (BPMS) that is responsible to follow the life cycle of each process (business object)

People

This pilar has the organizational hierarchy which shows who commands who, but not what each people do in organization. A good BI implementation must focus on people, not only to better manage the change of a new system, but also to gathering implicit knowledge that exists in each people of the organization transforming it in explicit knowledge.

Technology

This pilar as the other pilars it’s very important and requires a big understand not about the tools but about the concepts. There is a lot of people is this subject that are tools oriented in spite of be concept oriented. What I’m trying to describe, and mainly after my master degree, is that the tool is not the most important thing in this pilar. What’s important is the concepts and the understanding of what you can do with the tools. For example, to create a BSC you don’t need to be worried about the tool you do that, but in the concepts required to create a sucessful BSC. You can use a tool from SAS or you can simple create it for example in Excel… maybe you don’t know, but excel is the most powerfull and more used tool in the perspective of end user… and in my opinion, with new version 2007, Excel will be even better!!! Tecnology and mainly data mining algorithms could answer important questions to the organization that could change the strategy of the Organization.

These pilares are all important and dependent of each other, but in my opinion, people is the higher important pilar, and not only the people that implement the new system, but also the people in organization that accept this system, because there is a tremendous resistance by people to a change and there is a lot of implicit knowledge in organization that must be converted in explicit knowledge.

There is a lot of things to write and explain in each pilar, but I will try to do that if you think that itcould be usefull for you!!!

Cheers!!!

FIRST BIRTHDAY BLOG

One year ago, I started blogging… I never thought that this blog could have the success that in my opinion it currently has… I started it before my master BI degree, and in spite that today I have lot of subjects to write, in that time it was only my experience in the my company job and the experience from MSDN Forums that I still usually give and receive feedback about operational problems that we face each day.

Today, I really understand the importance of knowledge and the importance of sharing it. In this blog I always try to describe all my evolution/experiences but also try to create a knowledge network through the Internet… its important not only share our knowledge but also stimulate others to do the same. Nothing is more important that do what we really like to do, and If I’m here blogging is because I love business intelligence, and I feel that blogging we can make some difference!

Thanks to my familly and my fantastic girlfriend that always supported me, for all the visitors and for all the persons that directly or indirectly stimulated and keeping the blog alive:

my master BI teacher Mrs. Maria José Trigueiros for all the amazing messages that she gave to me, and teachers as Mrs. Patricia Narciso, Mr. Rui Gonçalves, Mr. Azevedo Rodrigues for the excellent and very good comunication that they had transmitted.

my master BI colleagues
my friend Subhash Subramanyam from India

my friend Amin Jalali from Iran

my friend Bruno Ferreira from Ireland

my friend Ella Maschiach from Israel

my friends from Caixa Geral de Depósitos (Portugal)

my friends from Microsoft Forums

my friends from ExpertExchange website

and thanks to persons like Jamie Thomson, John Hennesey and Chris Webb that had spent some time to visit me here.

Some numbers from google analytics since september/07:

about 7.500 page views

about 2.500 unique visitors (by IP address)

visitors from 89 different countries (mainly from Portugal, USA, UK, India, Iran, Canada, Australia, Germany, France, Israel…(in a next post I will paste here the google analytis reports)

I promise that I’ll try to always improve the quality and diversification of all my posts and in the way they fit in the Business Intelligence concepts.

THANKS!
Pedro

SSIS - Updating rows from a FlatFile

Dear Friends,
Today I will describe a subset of an ETL I made and I think that could be useful for you and it will be useful for me if I receive your feedback.

The Problem
I have customer’s data (sent by an application by text files to a specific file system share), that must be imported to a SQL Server 2005 database, more precisely, to the customers database table.

All Fridays, all the data in the text file, must be extracted, transformed and loaded into the SQL Server 2005 database. After the extraction the file must be moved to another directory in the file system. (…\ProcessedFiles)

The text file is a fixed column type, and there is a column that describes the event to follow when ETL update the Customers table.
N (means new customer to insert)
A (means update existent customer)
D (means update/inactivate existent customer)

The Solution
The data will be extracted from the text file into a preTable (“preCustomer”) without any transformation/rules (to avoid risks). For each row imported to this preTable, I will have an extra column that is a foreign key to my audit table information. Next, I read all the records from the last extraction, transform and load into Customer table.

The end user, from an asp application, will be able to correct or ignore the redirected rows, and next time the ssis package executes, these errors will disappear. (Note: the warning and error rows will be stored in redirectCustomer table, until some action (correct or ignore) from the end user)

Logical Data Flow
I have several steps that could be useful to explain, but today I will focus only in the subset that I think is more interesting. Below you can see the logical data flow for the ssis package number 3, that transform and load the customer data from the preCustomer table into customer table.

SSIS Package
And finally the ssis package...
(Note that the schema of ETL_Customer is identically to the ETL_redirectCustomer, for this reason you can union all the two sources in the pipeline)

Operational Risk
Remember that in a business intelligence project 70% of the risks occurred in the ETL module. So pay attention to the ETL and always try to identify the existence of an operational risk. I use the ssis error logs only for the real unexpected errors, if you think that there is a probability of some task fail, don’t wait to see the error log, try to mitigate the error before it!!!

“Operational risk is defined as the risk of loss resulting from inadequate or failed internal processes, people and systems or from external events. This defi nition includes legal risk, but excludes strategic and reputational risk.”
The European Commission’s Directive 2006/48/EC9

Some tips for using the best practices

I selected some important tips that could be followed in order to implement a good ETL project, using the best pratices.

- Always comment you project

- Always create the control flow and dataflow logical model and always before implementation of ssis package.

- For all the documents created, insert each document inside a template with all the important information to share with all the teams involved in the project. It’s very important that when you look for a document, you could identify the subject and scope of the document fast. For example, for the template I created for Logical Data Flow, I added important information as project, package, dataflow, developer/team, date, version, purpose, reference…
- Use naming conventions for the tasks and components of the SSIS Packages

- Use SSIS log provider to catch error and warning events.

- Could be useful implement not only the SSIS log provider, but also some custom logs, using event handlers.

- Test you package! How?! Use SQL Data Generator to generate thousands of rows to your source data. This software is very good, for each column you can select all the possible values that you want to random. (These values could come from a database, text file…)

I hope this post could be useful for you, and I also hope that you can give some feedbak about this.

Next post I will try to write something about data mining!!!

Cheers!!

BI Education - ExpertExchange Master Certification

Dear Friends,

After 2 months answering questions in www.experts-exchange.com I finally obtained the master certification in SQL Server 2005. This is one of the reasons that I didn’t update the blog so frequently! This is the certification that I won today:

There are 9 different ranks that you could achieve, I’m only in the first rank, but I will try to achieve the higher. Why you don’t join the game? It’s cool, you feel that understand something for some subject and you can help other people. lolol

The ranks http://www.experts-exchange.com/ranksDefined.jsp

It’s fantastic receive feedback from the asker as you can see for the image below

I will be waiting for you on ExpertsExchange!

Cheers!

BI Tools - SQL2008 CTPFebruary Bug (SSIS)

Dear friends,
I found the gold… no...no… I found the bug! J This mistake is because I never thought that a simple person like me, here in my small country with the name of Portugal, that sometimes people think the country is a state of Spain would found a Microsoft bug in SQL 2008 (SSIS)... ok… but let’s talk about the bug. Sorry for this introduction…

As I told you in a previous post, I’m developing a SSIS project for my master BI degree, using the new SQL 2008 CTPFebruary version. I found a bug, that I think is critical and very important.
If you already worked with SSIS 2005, you are probably familiarly with the warning messages that advise you for unused columns in your pipeline:

For those people who never saw the messages, I made a very simple example. (Note: One of the best practices to develop an ETL package is to try to minimize the size of each row in your pipeline. If you forget to delete these unused columns you will be decreasing ETL performance. If you already used SSIS 2005 and never saw the messages, take care)

This simple example in previous version of SSIS (2005) only executes a query in the table “Person.Contact” on AdventureWorks and inserts the returned rows into a temporary table.

Steps:
1. Add a dataflow “DFT TestBug” to the ControlFlow
2. Goto the dataflow created in the previous step
3. Add an OLEDB Source to get some data from AdventureWorks database:
SELECT Title, FirstName, MiddleName FROM Person.Contact
4. Add an OLEDB destination to insert the data into a #tempTable. Map the source and destination columns title and firstname and intentionally leave the column MiddleName without map.

As you can see for the image below, the SSIS 2005 advice you for the unused columns with warning messages. In the new version, these messages doesn't appear!!!

I posted this bug in MSDN Forums
http://forums.microsoft.com/Forums/ShowPost.aspx?PostID=3044877&SiteID=1&mode=1
and James Thomson and Bob Bojanic kindly suggested me to submit this bug to Microsoft. I submit the bug as you can see for the link below:

Sorry MSFT team for this post, but for me this messages are very important and it will be a problem if the final version doesn’t correct this!
Cheers!!!
Pedro

BI Education - The Data Warehouse Lifecycle Toolkit

Dear Friends,
I ‘m very busy with my job and my master BI degree, but I will try to come here with more frequently. I have too many things to blog and I don’t know where I will start from.

For now, I’ll write few words about the recent book of Ralph Kimball. I confess that I always was a fan of Kimball books, but normally those books have a lot of years. Although the subjects of almost the books are in use, I think that they was a little old and could be better adapted and integrated.

Ralph Kimball Group launched a new and fantastic book, “The Data Warehouse Lifecycle Toolkit” (second edition) on last January 2008. I read only four chapters yet, but I really feel that this book is better than others from Ralph Kimball Group. Check the contents of the book in the amazon or wiley website.

Obviously, I bought this book, and it has a fantastic price on Amazon (UK) at

http://www.amazon.co.uk/exec/obidos/ASIN/0470149779/ref=nosim/schildnet0a I paid for the book about 18€ (Euro) with shipment included and received in 2 days!!! (I’m in Portugal… don’t forget it) Normally, I buy the books at Amazon directly to the Amazon partners because is more cheaply, but in this case there is no difference between Amazon official price and the partners price.

I’m preparing a post about Reporting Services 2005 and 2008 querying an OLTP database.
Regards!!

BI Conference - SQLBits - Birmingham (UK) - After

Dear Friends,
I’m just arriving from Birmingham (UK) and I only have one Word to describe the experience: FANTASTIC! I have several things to write but if you weren’t there you missed a fantastic day!
As you already know, I’m currently living in Portugal but last Friday I pick my girlfriend and went to Birmingham (UK) to attend the SQLBits conference and back to the city where I’ve finished my degree some years ago. Microsoft Portugal is not investing too much in SQL BI tools, and in the last summer it had only one person specialized in these tools… To prove it, the next TechDays 2008 in Portugal there will be just a few sessions in SQL BI tools and probably too basic… The possibility to hear some “gurus” and the authors of my preferred books from Wrox and Wiley, it was an opportunity that I couldn’t missed!
I will describe in general how it was this journey to Birmingham and I will detail my preferred sessions on detail in a next post.

The Conference
There was several sessions in this conference, and I attended the sessions related with Business Intelligence MS SQL tools. Darren Green, Allan Mitchell and Chris Webb didn’t disappointed me in their sessions, and the money expended in this journey was a very good investment on my career and a good opportunity to “open my eyes” more and more to the world.

Allan Mitchell Session
Allan spoke about the new features of SSIS 2008 and compares it with the SSIS 2005. He shows some details that usually some developers don’t pay much attention, the performance… he show some details behind the scene, and in my opinion he done it good!
MultiCast
Pipeline Limiter
LookUp Component (Cache Options)
Data Profiling Task
Change Data Capture

Chris Webb Session
This session was very interesting… I expecting to see something about new features and good advantages of SSRS, and what I saw was the opposite and I’m totally in agree with him and this session prove that this type of events are not to sell products but is a place where we can receive very good and sincerely tips and tricks from developers to developers. And Microsoft was one of the sponsors…

Darren Green Session
Darren session was very cool and he spoke about a subject that I normally in SSIS avoid but after the session I will take more attention… and the subject is custom data components. I bought the WROX book that Darren wrote and refer this subject but I didn’t read those chapters… But is really interesting , not only to understand better the pipeline engine but also to develop more specific and complex transformations.

The City and BullRing
As I told you before, I finished my degree in Birmingham some years ago. There are several improvements in the city but it still beauty! A very good improvement was the BullRing Center that is a big shopping place with an area equivalent to 26 football stadiums!!!





The Hotel
Normally, hotels in England are not comfortable or modern, but this Hotel is very modern and I knew a Portuguese man that works there!



The NightLife
The night bars and discos are incredible on pressure of safety guards…

So... in resume, was a fantastic journey!! I will try to detail more about the conference in a next post!

Cheers!!

BI Conference - SQLBits - Birmingham (UK)

Dear Friends,
Today I'm very happy!! I received the confirmation for the next SQLBits conference in Birmingham(UK). And I'm happy for several reasons...

• I will return to Birmingham where I finished my IT Degree some years ago (NewMan College 2001)
• I will attend to the sessions of Chris Webb and Darren Green that are one of my main sources of inspiration and Allan Mitchell that is one of the authors of my favorite SSIS book at WROX.
• Change knowledge with the BI comunity and see old and new friends.
  
• Learn more about BI and SQL 2008 tools
• Enjoy Birmingham lifestyle again!

I'm sure you have a lot of reasons like me to the conference. Dont waste more time, there are more than 400 people confirmed!!

For registations follow this link: http://www.sqlbits.com
There will be several sessions, I selected these ones:

Slot 1:
Master Data Management (MDM)
Taken by Sutha Thiru
Slot 2:
Extending SSIS with custom Data Flow components
Taken by Darren Green
Slot 3:
Understanding LINQ in .NET Framework V3.5 and Beyond
Taken by Mike Taulty
Slot 4:
Making more of SSIS in 2008
Taken by Allan Mitchell
Slot 5:
Using Analysis Services as a data source for Reporting Services reports
Taken by Chris Webb

The agenda: www.sqlbits.com/information/MainAgenda.aspx
I will book my flight and hotel tonight!!

And for some reason you cannot go to Birmingham, prepare to go to Glasgow on 10-May-2008... I will try to be there... so, vote in the sessions you want to see!! Follow the link image!

See you around!
Regards!

SSIS - Execute a package from an ASP.NET page

Dear Friends,
Today I'm describing the way I executed a package from an ASP.NET page and passing a parameter.

The Problem
I need to enable the user of an ASP application to execute a package and passing a parameter to it when the user press a button.

Solution

The first thing you must know, is where you ssis packge will be stored. You or the DBA Team could stor the SSIS package inside database, in the filesystem or in DTS. Each one has advantages and disadvantages, and in my opinion the package inside database could be better when you must unsure more security.

So, in my SSIS package I have created a global variable "strCFG_TextPath" and I will update this value from ASP page. I have created a function fExecSSISPackage in a clean webservice to make the call.
(You can create the function in the ASP page, but in my case I made like this because I insert all the connections to the database inside a WebService)

WebService

In this example the SSIS package is stored in the filesystem, but the function is prepared to accept each of the 3 types that I described before.

You only need to write few lines: (SourceLocation is the string value I want to pass to the SSIS package)

Dim myPackage as Package
Dim integrationServices As New Application
myPackage = integrationServices.LoadPackage(packagePath, Nothing)
myPackage.Variables("strCFG_TextPath").Value = sourceLocation
myPackage.Execute()

ASP.NET Page
To call the function in the WebService is simple... see the image:

I hope this post could be helpfull for you.
Kind Regards,
Pedro