SQL Server Day 2009

On Thursday, the 3rd of December 2009 SQLUG.BE (The Belgian SQL Server User Group) and Microsoft are teaming up to organize the second Belgian SQL Server Day. Focus of the day is off course SQL Server.

Last year, SQL Server Day 2008 was the biggest community driven SQL Server event in the Benelux, with more than 200 attendees.

The event will kick-off with an international speaker positioning the SQL Server data platform, today and tomorrow.

We have 6 break-out sessions with technical content on SQL Server. The speakers are recognized international SQL Server experts; including Dirk Gubbels, Chris Webb, Henk van der Valk, ...

During the lunch break our gold partners will present their solutions and offerings in the lightning sessions .

We conclude the day with a closing keynote on Gemini, the new breakthrough Self-Service Business Intelligence (BI) capabilities being delivered in SQL Server 2008 R2.

Full details and registration are available on www.sqlserverday.be

Next SQLUG.BE event - Zero-Downtime upgrade from SQL Server 2005

Next week we kick of another year of SQLUG.BE evening sesions.

To be sure we kick-off properly, we hired a top-speaker, namely Dirk Gubbels. Dirk is one of our best guest speakers and by far one of the best SQL Server professionals.

Dirk Gubbels is a senior consultant at Microsoft, and has been working with SQL Server since version 4.2.

As one of the few Microsoft Certified Database Architects he has been involved in the most demanding SQL server based applications in Belgium and all over the EMEA region.

His main focus areas are Design, Performance and Availability for both OLTP and Business Intelligence environments.

Upgrading from SQL Server 2005 to 2008 may look very easy and straightforward, but there are always risks involved.

The goal of this session is to identify these risks, and show proven ways to mitigate these, or reduce the impact.

The second part of the session will show some techniques for minimizing downtime during the upgrade, to a point where the application can remain online.

This session will focus on the upgrade process for the Database engine, not on the differences between the versions, or the upgrade process of other SQL Server components

Agenda:
18:00 Registration
18:30 Session Start
21:00 Session End

Location:
Microsoft BeLux
Corporate Village
Da Vincilaan 3
1935 Zaventem

this event is brought to you by Microsoft


Click here to register.

Excel 2010 - a personal sneak preview

For the last couple of years I have been working on quite a few projects where we used Excel 2007 as the number one front-end on top of Analysis Services 2005/2008 cubes. I am a big fan of the killer combination of Excel/Analysis Services. And I am pretty good in convincing (potential) customers of this. Recently, a customer spoke the infamous words :" It is a Revelation", when he saw me slice and dice his data in Excel. I couldn't agree less ;-)

Anyway, although I am a big fan, I must admit that I sometimes reached the boundaries of the pivot table functionality in Excel 2007. Earlier this week Microsoft released the Technology Preview of Office 2010 and Sharepoint Server. I was very eager to find out which topics on my personal feature wish list made it into the product.

In the following weeks I will play around with the bits and focus on the following topics:

• Excel as a front-end on Analysis Services 2005/2008
• Excel interaction with the upcoming fancy stuff in SQL Server 2008 R2 (Gemini, Kilimanjaro)
• Sharepoint Performance Point Services, the successor of PPS Monitoring and Analytics and the web based version of Excel
• Below you can find my *very personal* review after playing around a few hours with the Technology Preview the Excel 2010 fat client.

Below you can find my *very personal* review after playing around a few hours with the Technology Preview Excel 2010 fat client.

Installation

As usual finding the product key is the hardest part;-) Apart from that installation is a snap: next, next, ... finish.

Feedback

The CTP contains a very nice feedback mechanism. When you click the happy or unhappy icon in the taskbar, a input screen pops up where you can write your feedback.
One click of the mouse and your feedback is sent along with a screenshot to Microsoft. This is a lot more convenient that the traditional Connect website.

Show 1 measure multiple times + Show value as ....

You can now drag a measure more than once to the values area, and show the value in different forms.

Even cooler: Year To Date calculations without writing MDX

Filter Dimension members in report filter

Really cool: a big time saver when filtering large dimensions.

Slicers

Slicers are a great new way to filter and compare data for specific dimension members.

I was not able use them on a Analysis Services pivot table. In the screenshot I created a pivot table on an Excel range, but you get the idea.

UPDATE: Slicers didn't work on a SSAS cube because my VPC was running with Belgian (Dutch) regional settings. Switch your regional settings to US English and the slicers do work.

Sparklines

Sparklines are another great visualization. Sparklines are tiny little graphs, the size of a single cell. A great new way to visualize trends!

Sparklines and Slicers are shown in this little webcast

Dynamic Sets

Dynamic sets (eg. Top 25 Selling Products) were a great addition in Analysis Services 2008. Unfortunately Excel 2007 didn't handle them very well. Now they are fully supported :-)

Create custom set from Excel

Named set are really great when doing data analysis. You can now create them from Excel via very nice GUI.

There is even a little GUI which allows you to write MDX. A little code highlighting would have been nice ;-)

Really cool, we can now create sets from Excel. But how about calculated measures or members? Well it looks like this will be added in later CTPs. In the current CTP the menu items (Calculated Field, Calculated Item) are grayed out. Something to look forward to.

Writeback

Analysis Services has had a writeback functionality for quite some time now. This is a great feature for planning and budgeting applications because it allow end-users to validate different what-if scenario's. With Office 2010 this functionality is finally exposed in Excel. A killer feature!

Conclusion

After a few hours fun with Excel 2010, loud and clear: " It is a Revelation". Upgrading from Excel 2003 to Excel 2007 was like going from black and white television to color television. With Excel 2010 we get full HD digital televison on a gigantic plasma TV. I am looking forward to the upcoming CTPs and I hope that all the new stuff will make it in the RTM version.

PTPower - review

I recently ran into PTPower .  PTPower is a free Excel add-in which fixes 3 topics on my personal Excel/Analysis Services features whislist:

• An easy way find the underlying MDX SELECT statement of an Excel Pivot table
• Smoother development and debugging of calculated members
• Add calculated measures to an pivot table on the fly from Excel

PTPower is exposed as an Excel 2007 Add-In - within the "PivotTable Tools" Ribbon.

 

 

Just click the Show MDX button and PTPower will show you a popup window with the underlying MDX statement of the pivot table.  The MDX Statement is color highlighted and ready to be copy/pasted in MDX Studio, Report Builder, ...  Very simple, but just works :-)

The second feature is even cooler.  Developing and debugging calculated measures in BIDS is a bit cumbersome.  Most of the time I use the script view in BIDS for that. For every change in the MDX statement you have to deploy the cube (or at least the MDX scipt using BIDS Helper) and switch back and forth to the cube browser or Excel to see the result.

PTPower allows you to build and test your calculations from Excel using a nice GUI.  When you click the Calculation button a GUI similar to the calculation tab in BIDS pops up. Write your MDX calculation, click Save, click Close.  Excel will refresh your pivot table and you will see the results immediately.

 

 

Calculations created via PTPower are implemented as session members.  This means they are not saved to cube, but only available in the current session.  When you save the Excel file and reopen it, PTPower will recreate the session members.

And  this is very cool, because it allows end-user to add their own calculations from Excel.  Altough they will need to learn MDX.  Which might be a bit challenging for most end-users ;-)

Anyway, PTPower is nice addition to my Analysis Services toolkit. And is tooltje van de week.

Enabling Report Builder 2.0 Click Once

Starting from SQL Server 2008 SP1 Report Builder 2.0 can now be deployed as a click-once application. However this is not the default behavior after you install SP1.

If you click the "Report Builder" icon in the Report Manager, the old Report Builder will pop up.

To change this go to the Site Settings and change the URL under "Custom Report Builder launch URL" to following:

http://YourServer/ReportServer/ReportBuilder/ReportBuilder_2_0_0_0.application

This should do the trick. Have fun with Report Builder 2.0