Business Intelligence Development Studio

The Business Intelligence Development Studio has been improved in a couple of ways, so you can now produce aggregates of aggregates by nesting RDL aggregate expressions inside other RDL aggregate expressions. The example given is that if you were to use the expression

=Avg(Sum(Sales, "Month"), "Year") you'd get the average total monthly sales.

One useful addition is that of report variables that automatically increment to a series, so you no longer need to create your own custom code variables. You can also now analyse report data using the XML ATOM format.

Eventually, the most likely way your business users will work with SQL Server 2008 data is via Office 2010's PowerPivot for Excel 2010. This is an in-memory analytics engine that is designed to make it possible for users to work with large data sets in memory without their machines slowing to a crawl. PowerPivot looks great, but it does involve your business users upgrading to Excel 2010, and to get the full benefit of PowerPivot's server component the company will also need to move to SharePoint Server 2010.

(Click in picure to expand it)

 Analysing data using PowerPivot in Excel

Data Tier Application Projects

Data Tier applications are an extension to the way you can work with a database. A Data-Tier Applications consists of the database schema and the server-level objects such as logins that you need to support the database. It doesn't include the data. The idea is that you script any data that you might generally leave within a project - lookup tables, configuration tables, for example. You include the scripts in your database as objects. For example, you could have a stored procedure that populates configuration tables using Insert statements.

(Click in picure to expand it)

Working with a Data-Tier application

You can take an existing database and create a Data-Tier application that you can then deploy on any suitable server as a package. The package will include the database schema, stored procedures, user defined functions, everything necessary to set up the application. You can then simply hand over the package to a DBA for them to deploy. You can create Data-Tier applications in Visual Studio 2010, or in the SQL Server Management Studio; in both cases you get a wizard to help you through the process. Data-Tier applications look like a good thing; the worst thing about them is that Microsoft in its wisdom has come up with the acronym DAC to describe them -because DTA was too well known, apparently. This rather ignores the fact that SQL Server already uses DAC for Dedicated Administrator Connection.

The SQL Server Business Intelligence Development Studio (BIDS) is essentially the same in the new version apart from having support for the new report definitions and report server projects. You can build report definitions that are compatible with both SQL Server 2008 and SQL Server 2008 R2 Reporting Services, and preview them in the Report Designer.

Overall, this is a good general release of SQL Server, and Master Data Services and the improvements to the reporting services should make it worth you taking a look at it.

<ASIN:0470563117>

<ASIN:0071549447>

<ASIN:073562738X>

<ASIN:0735626243>

DAC - working with a Data-Tier application

MDS - setting up a master data services package

PowerPivot - analysing data using PowerPivot in Excel