BI Tools - SQL 2008 R2 Digital Tour

My teacher Maria Jose Trigueiros is not physical present any more, but she’s always present in my mind. I’m sure that she’s watching what the persons that has the luck to has her as teacher and as a friend will be doing. She shared amazing words, enthusiasm and experiences with me that changed my life. Because I know that she wishes that I continue blogging... I’ll do it, and even with more power!

SQL Server 2008 R2 RTM is now available for download!
I already installed in a new virtual machine in order to preparing some posts about it. In Microsoft SQL Server 2008 R2, several components have new or improved features. I highlight the following new improvements:

PowerPivot for SharePoint
PowerPivot for SharePoint adds shared services and infrastructure for loading, querying, and managing PowerPivot workbooks that you publish to a SharePoint 2010 server or farm. To create PowerPivot workbooks, you use PowerPivot for Excel.

PowerPivot for Excel
PowerPivot for Excel is an add-in to Excel 2010 that can be downloaded from the web and installed on client workstations. You use PowerPivot for Excel to assemble and create relationships in large amounts of data from different sources, and then use that data as the basis for PivotTables and other data visualization objects that support data analysis in Excel

Reporting Services improvements
Reports can now include maps, sparklines, data bars, and indicators to depict data. Report parts enable collaboration through shared datasets, report items, and data regions that are centrally stored and managed. Dataset query results can be cached on first use or by schedule. SharePoint integration supports multiple SharePoint Zones, SharePoint Universal Logging, and local mode report viewing with Access Services and SharePoint lists. SharePoint lists, SQL Server 2008 R2 Parallel Data Warehouse, and SQL Azure Database can be used as data sources for reports

Take a look at additional news here
Take a tour on this new version of SQL through SQL 2008 R2 Digital Tour:

Regards,

Pedro

BI Conference - Tech Ed 2009, SQL Server 2008 R2

Last year Microsoft shared information about Kilimanjaro at the company’s second annual Business Intelligence Conference, which kicked off on October 6 in Seattle. Kilimanjaro is set to include self-service analysis tools (codenamed “Gemini”) that Microsoft is saying will allow information workers to better “slice and dice data and create their own BI (business intelligence) applications and assets to share and collaborate on from within the familiar, everyday Microsoft Office productivity tools they already use.”

But last week at TechEd 2009 conferences, Microsoft announced SQL Server 2008 R2, which you might recall as previous code of Kilimanjaro. What? You thought Kilimanjaro was SQL Server 2010? Well, if you Google it, you'll see that many others think the same, and in fact there are announcements from very significant sources over the last few months talking about SQL Server 2010. So the information now is that there is no SQL Server 2010. "Never was." That the BI and self-service tools that were part of the release were actually the R2 release. That "Kilimanjaro" always referred to R2. The next full release of SQL Server will follow the R2 release by about 24-36 months. You can expect to see full release of R2 in the first half of next year - CTPs should follow later this year for testing.

I understand that Microsoft, like any company has to adapt its strategy when the world is currently in a big financial problem. In my opinion, Microsoft will be easier to convince customers to upgrade a SQL 2008 to SQL 2008 R2 than convince SQL 2008 to SQL 2010... well ... but starting to be many products, many names, many service packs, many cumulative packages, many functionalities ... truly begin to create confusion in people, that usually have great difficulty accept the change. I hope at least, that Microsoft does not change the format and design of the next version of Excel which is expected to have many more capabilities than Excel 2007. We shall see ... I am very curious about the improvements on the analytical tools and on the "Master Data Services"

Also, their advertising video is pretty funny (the introduction part). You can also sign up to receive notifications of the CTP. You can see it here:

http://www.microsoft.com/sqlserver/2008/en/us/r2.aspx
Regards, Pedro

SQL2008 - Free Hosted Trial Offer

Dell, MaximumASP and the Professional Association for SQL Server (PASS) have partnered together to offer a free SQL Server 2008 account on a securely hosted environment. This offer allows anyone to try SQL Server 2008 today without downloading and installing the software.

Thanks to Ella, I knew this news and immediately made my registration.

This feedback, participantion and the free hosted environment, it'll be important for me because I will focus my BI dissertation using Microsoft tools, mainly SQL 2008.

For further details visit Ella’s blog.

Regards!

BI Book - Free SQL 2008 Book

Dear Friends,

Only to alert you that Microsoft Press is sharing with comunity a free SQL 2008 book.

It's available for a free download here:
http://csna01.libredigital.com/?urss1q2we6

If you want to begin the SQL 2008 Microsoft Certifications, could be a good start... and free!

Regards!

Pedro

BI Conference - SQL Bits III

Once again I went to the SQLBIts conference as I promised. You can refresh the previous event following this link http://pedrocgd.blogspot.com/2008/03/tmp.html

I started to write this post in the University room booked to attend the SQLBits III event. Almost persons that attended to the event already returned to their homes, and unfortunately I had flight only Sunday morning at 8 am. I'm joined with my friends Bruno Ferreira (Portuguese like me that came from Irland), Alex Whittles (that came from London with his boss) and Tomislav Piasevoli (that came from Zagreb, Croacia). The event was significantly more interesting with these 3 excelent guys! I attached to this post several photos only to refresh the memory of people that was there and allow others to imagine how the event was.I have several things to write about this weekend in London, which was extremely good in some points and a litle dispappoint in others.

The University of Hertfordshire
This is the University where the conference toke place. Is a nice place but too quite for a person that goes alone from another country… Bruno was also there but he went to Irland on Saturday after the conference…

Last SQL Bits, in Birmingham (UK), the location of conference was better because it was near the city centre and because I went with my girlfriend, she could went to shopping while I was attending the event



The Training Day (Friday)
SQL Bits III organization included an extra day for intensive training that cost between 150£ and 200£ depending on the selected subjects. For those persons that never saw MSFT BI Tools and Business Intelligence approachs from Kimball an Inmon, I think that was good, but for others was extremely basic. But was my fault, because Chris Webb advised me… but I’m disappointed because I didn’t see the same organization and the same environment in training day (on Friday) than the free conference day (on Saturday) … and for those (like me and Bruno) who payed for the training day we make a question for ourselves… for which day we payed 150£?! Why we make the question? The reason is simple… the free conference had several partners (Microsoft, Quest, Xlcubed and others), extra events like dinner with beers and pizzas, more than 400 persons, a party and some other interesting stuff. We also thought the training day would be an intensive training using the laptop and making some advanced labs mainly in MDX and Data Mining.

The Conference Day (Saturday)
Some photos of conference environment and a photo of Bruno picking some pizza and beer! Bruno is the guy with black t-shirt! You can also see the taller guy… He’s Alex Whittles that I referenced before and he’s a cool and friendly guy!




The Conference Day Sessions
I’ll describe a a little resume of each session that I attended, maybe I will go into detail in a next post, but for now, I’ll do a short briefing.

Alan Mitchell session
Getting more value from your data through data mining
After seeing the Rafal Webcasts, it’s difficult to find a better session about Data Mining, but Allan was really great and focus on excel 2007 add-In for Data Mining. Allan showed how the business user can apply a Data Mining algorithm without statistical and developing skills. Don’t need to know formulas, don’t need to know programming, it’s all there!!!


The hard task will be the ETL that is the most important step in a Data Mining project. If the dataset is not well created, even the top statistical expert of the world cannot identify real patterns and trends

Gary Crawford session
Data Vizualisation - Make your reports useful to business - No more "Speed Dials"
Maybe some people didn’t saw anything new, but I loved this session. Gary gave some very good tips that must be taken when you create a report or dashboard. He started from default charts generated by excel wizard and gave some design tips to create more friendly and understandble charts for business reports

He also showed some gauges, thermometers and traffic lights usually used in some business reports. He explain some best pratiques that should be followed in order to avoid confused and unperceptable reports

And as he told… running a business is not driving a car. Cars are real time, need real time response. Business reports don’t move as you read them. He finished giving some tips for a good and more usefull report or dashboard


Andrew Sadler session
Microsoft's integrated end-to-end BI offering
I never heard about Andrew Sadler before, and was really a very good surprise for me. He was fast, precisely and efficiency, in the way he explained the integration that could be done with Sharepoint, Performance Point Server and ProClarity

In the demo prepared for the session, he used the Sharepoint portal as the main interface/front office and behind the scenes he created several different types of reports and charts in Performance Point Server (including proClarity) showed inside normal webparts of Sharepoint portal

Andrew also show some tips using MDX that could be applied on filtering the reports and charts created in the previous steps

Andrew Wiles session

Does your cube support your users reporting needs?

As Andrew Sadler, Andrew Wiles was also a very good surprise for me and for almost persons that was attending the session. He focused in user reporting needs as Gary in a previous session, not in design but in funcionalities… for example how a simple indicator YearToDate could be available to the business user? You will respond that I can create a calculated member or allow user to do that using the MDX functon YTD… but Andrew showed a new approach that is abstracting all this stuff using reporting dimensions… for budget reports I believe that could be helpfull… I’m still tring to understand all the the stuff and how could apply in the real world…. I’m ansious to see the material that Andrew promised to share with us…

This session was really the more original and harder! Check Alex post for further details http://www.purplefrogsystems.com/blog/?p=18

Colin Hardie session

Choices, Choices - Loading and Modelling SCD in SQL 2008 Suite

Colin Hardie work for the IMGroup (the main Microsoft partner) and showed the approach about Slowly Changing Dimensions (SCD) from Kimball and the capabilities of SCD transform of SQL Integration Services.

Nothing new, only the different alternatives to deal with Slowly Changing Dimensions, but Colin is very comunicative and also fast and precisely!

In the final, Simon Sabin, invited the attendences to eat some pizza, drink some beers and singing a song in auditorium!

Thanks to the organanizers, that although some little disappointing points, I'm sure that they did everything they could to do a fantastic event as it was in SQLBits III. Thanks to Simon, Chris Webb, Allan Mitchell, Tony, Darren Green and Jamie.

See you guys in next SQLBits!

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