Kasper de Jonge PowerPivot Blog

Microsoft announced a new SQL Server version, SQL Server 2008 R2 with realy great upgrades for BI. And you can register for the CTP.

Some very interesting new features are:

• Self Service Analysis with “Project Gemini”
  

  Build Robust Analytical Applications

  • Combines native Excel 2010 functionality with an in-memory, column oriented processing engine to allow users to interactively explore and perform calculations on millions of rows of data at lightening speeds

  • Streamlines the process of integrating data from multiple sources – including corporate databases, spreadsheets and external sources

  • Access PivotTables, slicers, and other familiar analysis features in Excel to create reports and perform advanced analysis

• More Powerful Ad-hoc Reporting with Enhanced Data Models

  Report with Ease

  • Decrease time and costs developing reports

  • Enable timely access to information to help make better decisions by empowering end users to easily design queries, reports and charts through a highly intuitive, drag and drop interface

  • Powerful and intuitive authoring and ad hoc reporting capabilities with enhanced data models

• “Grab & Go” Reporting

  Collaborate with Confidence

  • Central, secure location for IT administrators and users to publish content objects that can be broadly reused and easily customized to meet the users’ needs

  • Ensure consistency by creating and maintaining departmental content that can be accessed by the rest of the organization for building comprehensive business reports

  • Accelerate report creation by allowing end users to reuse existing components (queries, tables, charts, maps, gauges, logos) as building blocks for creating new reports

It even includes the new MDM with Master Data Services (MDS).

Read more and register for the CTP here :  http://www.microsoft.com/sqlserver/2008/en/us/R2.aspx

Today i had to create a package where i had to create a flat aggregation table with facts and measures for a operational system, the problem is that the data keeps refreshing every minute whereas the data is aggregate over an hour. That means data has to be updated or inserted.

My initial thought was to use SSIS to retrieve all of the rows intended for the dimension table in a data flow via a source component. Then the rows would be run through a lookup component to determine if they existed in the target table. Rows to be updated (those found in the lookup) would be run through an OLE DB Command component. Rows to be inserted would be sent to an OLE DB Destination.

But then I remembered the new SQL 2008 Merge statement which will give you the ability to write one single statement to update the row if it already exists otherwise it will insert a new row. Too bad there isn’t a Merge destination component in SSIS.

To use the merge statement I created a simple package with 2 SQL Execute tasks, one that inserts the new data in a temporary table and one that merges the data from the temporary table to the new aggregation table. I used the measure values as JOIN condition for the merge. Realy simple and not much work.

The merge statement is a great tool to keep the flow of update / insert really simple and without different timelines.

Sites i got my info from:
http://agilebi.com/cs/blogs/jwelch/archive/2007/07/05/sql-server-2008-using-merge-from-ssis.aspx
http://technet.microsoft.com/en-gb/library/bb522522.aspx
http://sqltips.wordpress.com/2007/08/27/merge-statement-of-sql-seerver-2008/

When developing a ETL package a lot of work goes into creating test data, usually you load a file in SSIS and enrich it with data. It could be very useful when you could script this test data so you can recreate it on thy fly or share it with you colleague developers.

Using SQL Server management studio you can generate a script from the data in your tables. Go to your database, right mouse click, Tasks and click Generate scripts

Click next on the splash screen, select your database, at the script options screen set the select script data value to True, set all other properties to false.

Click next, On the next screens select all tables you want to select and click the desired output and click finish. It will now create your data script.

Service Pack 1 for SQL Server 2008 is now available for download.  The service pack is primarily a roll-up of Cumulative Updates 1, 2, and 3 and minor fixes made in response to requests reported through the SQL Server community. 

While there are no new features in this service pack, a notable addition from a Reporting Services point of view is the ability to configure Click Once deployment to either launch Report Builder 1.0 or the updated Report Builder 2.0, directly from Report Manager (native mode) or SharePoint.  If you run Reporting Services in SharePoint integrated mode, make sure to also install the updated RS add-in for SharePoint.

Thanks Robert for the heads up.

Alexander kuznetsov of sqlblog.com has made a summary of previous posts about defensive database programming,defensive programming is an approach that is designed to ensure high quality of software by eliminating existing bugs and avoiding potential ones. The goal of defensive programming to make sure that software robustly and gracefully handles cases of unintended use. Programming defensively includes the following techniques:

• Explicitly list the assumptions that you made during development
• Whenever feasible, remove assumptions that are not essential
• Ensure that the essential assumptions always hold
• Use all your imagination to come up with cases of unintended use, trying to break your module. Incorporate these cases into your testing suite.
• Lay out your code in short, fully testable, and fully tested modules
• Reuse your code whenever feasible

Read the entire post here:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/03/08/summarizing-previous-posts-about-defensive-database-programming.aspx

Theo Lachev  has a new blog post with a step to step guide to integrating Report Builder 2.0 with SharePoint. One thing to notice here: it only works with SQL Server 2008 SP1!

Read the guide here: http://prologika.com/CS/blogs/blog/archive/2009/02/25/report-builder-2-0-clickonce.aspx

• Per terabyte pricing
• Linear and predictable performance
• 1.6-32 terabytes capacity
• A very competitive $/terabyte offering starting at around $13K (32 TB capacity) and ending at $25K (4 TB capacity)

Read more at Peter Koller:
http://peterkol.spaces.live.com/Blog/cns!68755AEAC31F9A6C!1022.entry

An interesting discussing at sqlblog.com on cloud computing, what are the pros and cons? and in what time can we expect this to take a flight ?

Read the discussion here: http://sqlblog.com/blogs/denis_gobo/archive/2009/02/25/12206.aspx