BI Paper - SSIS Extract Data Multiple Worksheets

Some time ago in 2009, I wrote several papers from a community that I created in that year. But after the community website broke, I received dozens of requests to provide the papers for those who didn’t downloaded it at that time. For that reason I have attached to this post the paper again for everyone.

Scenario
Every month a company receive customers data from an excel file to extract and load data into an enterprise data warehouse. In brief, is required to extract data from a single excel file with multiple worksheets with same schema into a single destination table in SQL server.

I hope this paper could be useful

DOWNLOAD PAPER HERE

2012 Review

It passed 3 months from the previous post and I need to say sorry to all you that follow my blog. For all the visitors, friends and more than 500 followers I want to explain that the previous year were the most important of my life and for that reason I failed writing here more often. I started the most important project of my life with the birth of my daugter Diana, wrote an entire book with my friend Reza and moved from Lisboa to Oporto to embrace a new challenge in a new company (BI4ALL) and stay near my family

The most important project of my life and the first smile:

The most amazing city of the world: Porto

The book

... and he company I currently work and the event I organized in Porto

I promise that I'll write more often! 
I have several projects in mind to share with you all.
Pedro

BI Book - Packt Publishing reaches 1000 IT titles

Dear friends, 
The last two months was a big challenge for me. Working at one of the best BI companies of the world (BI4ALL) is not an easy task! But everything is running fine and it’s really amazing working with wonderful colleagues as I have at BI4ALL. But the reason for this email is to spread a marketing message from the Packt Publishing who gave me all the conditions and support to write an amazing SSIS book with my friend Reza Rad. 

Birmingham-based IT publisher Packt Publishing is about to publish its 1000th title. Packt books are renowned among developers for being uniquely practical and focused. Packt books cover highly specific tools and technologies which IT professionals might not expect to see a high quality book on. Packt would like you to join them in celebrating this milestone with a surprise gift – to get involved you just need to have already registered, or sign up for a free Packt account before 30th September 2012. Packt published their first book in April 2004. One of the most prolific and fastest growing tech book publishers in the world, they now have books on everything from web development to web graphics, e-learning to e-commerce, IT architecture to games, and app development. Packt supports many of the Open Source projects covered by its books through a project royalty donation, which has contributed over £300,000 to Open Source projects up to now. As part of the celebration Packt is allocating $30,000 to share between projects and authors in a genuinely unique way, soon to be disclosed on their website. 

Dave Maclean, founder of Packt Publishing explains, “At Packt we set out 8 years ago to bring practical, up to date and easy to use technical books to the specialist tools and technologies that had been largely overlooked by IT publishers. Today, I am really proud that with our authors and partners we have been able to make useful books available on over 1000 topics and make our contribution to the development community.” 

For more information about Packt, the kind of books they publish, and to sign-up for a free account before the 30th of September, 2012, please visit their website: www.PacktPub.com 

BI Challenge – Moving to BI4ALL

This week I’ve started a new challenge in my life: I moved to BI4ALL (www.bi4all.pt). BI4ALL is a specialized company on Business Intelligence and Corporate Governance and was identified by Microsoft and IBM as a company with more experience in developing Business Intelligence solutions in the Portuguese market. It has for some years a reference for me in BI, not only by the quality of the solutions developed, but also the quality of people who work here (about 60) that I had the privilege of socialize over the last years.

The BI4ALL with more than 10 years has the headquarters in Lisbon and is investing heavily in Porto, where it already has a customer base that represents 10% of the company and a Research Center for Business Intelligence Mobile Solutions (over a year). The purpose of BI4ALL will be to continue the growing on this amazing world of Business Intelligence and give the Portuguese companies a competitive advantage over its competitors. This blog has more than 5 years and I will continue to share my insights and news on this amazing field!

Pedro

BI Book - SSIS 2012 book winners

This week I’ve started a new challenge in my life: I moved to BI4ALL (www.bi4all.pt). BI4ALL is a specialized company on Business Intelligence and Corporate Governance and was identified by Microsoft and IBM as a company with more experience in developing Business Intelligence solutions in the Portuguese market. It has for some years a reference for me in BI, not only by the quality of the solutions developed, but also the quality of people who work here (about 60) that I had the privilege of socialize over the last years.

The BI4ALL with more than 10 years has the headquarters in Lisbon and is investing heavily in Porto, where it already has a customer base that represents 10% of the company and a Research Center for Business Intelligence Mobile Solutions (over a year). The purpose of BI4ALL will be to continue the growing on this amazing world of Business Intelligence and give the Portuguese companies a competitive advantage over its competitors

This blog has more than 5 years and I will continue to share my insights, work and news on this amazing field!

We in BI4ALL make the things happen!!

Pedro

BI Book - Win Free Copies of SSIS 2012 book

I'm happy to inform you that the SSIS book that I wrote with my friend (Reza Rad) is now published!! I also want to let you know that Packt Publishing accepted my contest suggestion for offering 4 books (2 Print books + 2 e-books) to my blog readers. You just need to use your Twitter or LinkedIn accounts! Keep reading to find out how you can be one of the Lucky Winner.

How to Enter?

1-) Tweet this post and/or share this post in LinkedIn – you can use the tweet button at the top of the article.

2-) Visit the book page link, check out what’s inside and see what you would like to learn most from the books – pick one that you like the best

3-) Choose one of the following options to win the book:
Option 01 - Enter a comment under your twitter or LinkedIn account on what you’re looking forward to learn most in from this book. The two best comments win one print book and one e-book.
Example Comment:
"I'd like to own the Microsoft SQL Server 2012 Integration Services Expert Cookbook as I'd like to master all transformations in SSIS and their usages with real-world scenarios"

Option 02 - Write a post/article under Business Intelligence concepts to be published on this blog. The best two original posts/articles win one print book and one e-book

4-) Be sure that you are following me on Twitter (@PerfeitoP) and/or also on LinkedIn when the contest ends to monitor your participation and let you know the contest results. (Winners from the U.S. and Europe can either choose a physical copy of the book or the eBook. Users from other locales are limited to the eBook only)

Deadline

The contest for the best comment (Twitter and LinkedIn) and best post/article will close on 15/June/2012

Promo codes

If you don’t want to participate on this contest but want to buy the book, here you have special discount codes to use in the Packt Publishing website. They are as follows:
25% discount on print book PRSQL25
35% discount on e-book EBSQL35

Do let me know if you have any questions. Hope you all enjoy this book and let us know your feedback

BI White Paper - BI for Midsize Organizations

Today I’m opening a new section on the blog… the “BI White Paper”! And in this first post, I want to share with you a very interesting whitepaper that I was reading during the weekend. This whitepaper was written by Michael Schiff from SAP and is free (but requires registration). The article is written in a very interesting and objective way. I’ve included below the conclusion provided in the whitepaper to let you know more about it. I hope you enjoy the paper as much as me!

"Managers have the responsibility to make the best decisions possible, based upon the data available to them to learn more about how products from at the time. If their ability to analyze this data and transform it into useful information is improved, the overall quality of their decisions will improve as well. Business intelligence provides a spectrum of tools and solutions to support this aim. It’s the underlying technology behind more effective decision making. By helping align individual and departmental efforts with overall corporate strategies, it should lead to improved organizational results.

While many small and midsize companies have relied on spreadsheets as their primary Bi tool, most of them have come to realize that this is a stopgap solution. This is not to say that spreadsheets should be abandoned; rather, they should be a part of an organization’s Bi tool set. The key is to choose the right commercial Bi product suite that integrates with spreadsheet environments while supporting your long-term growth"

Read more downloading the white paper here

BI Book - Free SQL Server 2012 e-book

Dear friends,
If you want to know the new Microsoft SQL Server 2012, I highly recommend you to download the SQL Server 2012 free e-book (288 pages - 10.8MB) wrote by Ross Mistry and Stacia Misner. I already read it! It's a very interesting book that you should take a look!

PART I - DATABASE ADMINISTRATION (by Ross Mistry)

1.   SQL Server 2012 Editions and Engine Enhancements
2.   High-Availability and Disaster-Recovery Enhancements
3.   Performance and Scalability
4.   Security Enhancements
5.   Programmability and Beyond-Relational Enhancements

PART II - BUSINESS INTELLIGENCE DEVELOPMENT (by Stacia Misner)

6.   Integration Services (SSIS)
7.   Data Quality Services
8.   Master Data Services
9.   Analysis Services (SSAS) and PowerPivot
10. Reporting Services (SSRS)

And not forget… if you want a more specific book about SSIS 2012, don’t forget to buy the book I wrote and will be published during the next days.

BI Certifications - CBIP Material for the exam

Finally I got some time to write something more about CBIP certification.

First off all, don’t think this certification is the ticket for your next job. It’s just a motivation to let you learn more and improve your skills. Naturally that if you improve your skills, you have more conditions and recognition to get your BI job, but don’t think a certification by itself do it for you.  Relating to this certification, it's a way to TDWI makes money like all other certifications that exists around the world. But at least in this case, it seems more credible and more recognized than for example Microsoft certifications. Why? Just because there are a lot of “brain dumps” spread in the internet with exactly some questions of the real Microsoft exams. TDWI do a very good job on this field… although having a big number of question not aligned with the certification subject (like for example in ETL module) they are good and unique! The TDWI staff in Germany is also amazing and very kindly with you! See my experience there in 2009!

Now let’s go to the main reason to write this post! I’ve promised to the dozens of people that contacted me (almost since the post I wrote about CBIP experience in 2009) for help them on getting material guidance. For that reason here am I with the promised post! (Sorry for the late reply).

To make these exams, you should have minimal real-world, or at least huge academic experience, on the exam specialty that you’re thinking to get. If you have this pre-requisite then I think you should consider the suggestions I’m writing below.

Suggestion 01 - Examinations Guide

You MUST buy and read the CBIP Examinations Guide. This guide contains detailed exam outlines, reference reading lists, sample exam questions, and answer explanations for each exam included in the CBIP program. Testing software and practice timed exams are included with the guide. Taking the sample exams will identify areas where additional study and/or work experience may be necessary prior to testing. If you don’t have this examinations guide you will probably feel lost during the exam… And don’t forget that you don’t have too much time to think! The testing software is for sure crucial!

Suggestion 02 - Mandatory Books

You MUST buy and read the “Performance Dashboard” and “Successful Business Intelligence: Secrets to Making BI a Killer App” books! Why? The first, in addition to being a very interesting book, was written by the TDWI director at that time: Wayne Eckerson! And the second is a book that resumes the concept of BI in a simple and objective way. These two books are an investment that you should have! (And buy it… do not try to find illegal copies of this book on the internet… because doing so, you’ll not encourage authors to write more amazing contents like these ones). I should add that on these books, you will find several answers to some exam questions.

Now let’s go to remaining materials that although not mandatory, should be read at least some of the topics where exist some doubts. You probably don’t have time to read more books, but I also suggest several others in a previous post. Try also to get some information in the internet about the following topics: Dimensional Modeling (Techniques like Slowly Changing Dimension, Junk Dimension, Mini-Dimension, Outrigger, Role-Play Dimension… Fact table types: Transactional, Cumulative Snapshot, Periodic Snapshot, Factless and Factless Coverage), Data Warehousing architectures (Bus model, Hub and Spoke, Federated and Independent Data Marts), OLAP Architectures (MOLAP, ROLAP, HOLAP), Data Warehousing Methodologies (Both Kimball and Larissa Moss methodologies), Data Mining and make some research on terms like BPM, CRM, OLAP, ETL, BAM…
I hope this post helps you!
Regards,
Pedro

BI Award – SQL BI MVP Award 2012

Last Sunday, I received for the third year, the award from Microsoft for all participation on several communities and also for all contribution spreading to the world the Microsoft BI tools during 2011. These tools are in my opinion very robust, intuitive and with huge future! For that reason is a pleasure to work with.

Last year was the most hard for me… not only the book, but the full time job on BI with amazing projects on several interesting institutions. I hope to receive the award again next year and for that reason I’ll participate even more on communities and maybe write another book! I want to dedicated this award to:

- All blog visitors
- All the post-graduation students on BI from Universidade Portucalense (Porto) and ISCTE (MSIAD + MEI) (Lisboa) for all important feedback on the Microsoft tools and also the direct and indirect support they gave me during the last year

- To Dra. Maria José Trigueiros for all the magic words (You’re still alive in my life. I’ll never forget you!)
- To my family and my girlfriend for all the support they gave me!
- All those persons that believed in me, even more than myself

Thank you all! Regards.