BI and Reporting enhancements in Microsoft SQL Server 2008 "Katmai"

Change Data Capture
Change Data Capture (CDC) is a really interesting feature in Katmai using which we can focus on specific business requirement to capture changed data. That means this component captures all changes asynchronously and then exposes the changes through T-SQL based on specific requirements.

The provision to capture schema-changes is also very useful in this component.



Merge SQL Statement
Truly fantastic and really useful........

So how many times you have used "IF EXISTS" to make sure that you insert new row otherwise update the existing row.....yes I am talking about UPSERT...

This MERGE statement takes care of this scenario and check for existing rows to make updates and inserts new rows in the table.

MERGE INTO FactTable F
USING TransactionTable T ON T.OrderID = F.OrderID
WHEN MATCHED THEN UPDATE
SET F.Quantity = T.Quantity
WHEN NOT MATCHED THEN INSERT (OrderID, Quantity)
VALUES (T.OrderID, T.Quantity)



C# support for SSIS scripting components



Doesn't require IIS for SSRS
Yeah, thats the good part, we don't need IIS anymore to load SSRS reports with SQL Server 2008. This is the best for a developer like me.......



Grouping Sets
So how many times you have asked by business users to give specific grouping totals, people in sales only ask for product level totals, management looks for employee-product totals for bonus, finance needs total on 10 different things........

Well now with this new feature we can define multiple grouping option within the same query....that means instead of writing multiple group by queries and then combining the result with UNION ALL, we can have just one query with multiple grouping sets



Tablix – This release of SQL Server introduces a new report component called tablix, technically it’s a combination of existing table and matrix in SSRS. We just need to change few basic properties and this will do the job, so no more patchwork required to achieve stacked columns, pivot and more than 1 subtotals.



RDL Object Model – Good news for developers who wants to write/extend reports programmatically.

Dundas RS controls – As we know that Microsoft recently acquired Dundas chart, so this release of Reporting Services will contain the full power of dundas, an absolutely must have in reporting chart world.



Soft Artisans Office Writer technology – Again, This acquisition will allow users to embed their reports in Microsoft Word and Excel documents. Talk to you manager, he will love this.



Export to Microsoft Word




SSIS Lookup "TxLookup" in SQL Server 2008
Microsoft is really working on this component, and now we can lookup into any data source, including other SSIS packages, great news. Along with this TxLookup also works on "pre-charge" query, currently we have cache-miss queries in SQL Server 2005 Integration Services.



SharePoint user interface to manage SSRS reports



Office based report authoring
Now we can used report authoring tool, which allow us to author our reports using excel or word based tools. That means we can put tables, formulas, and also apply formatting too.




FYI - Microsoft entry into MDM world
Microsoft have also purchased Stratature recently, which provides Master Data Management solutions. MDM solution is really useful and must have for organizations having multiple businesses. Read more on MDM at MSDN The What, Why, and How of Master Data Management

LINQ & LINQ to Entities in SQL Server 2008 "Katmai"

I am very excited with this new way to access data and recently learned something about LINQ and what SQL Server 2008 "Katmai" stores with respect to LINQ to Entities model.

Let’s have a look to...........Language Integrated Query (LINQ)

Today data can belong to different data domains like XML document, database, text file, excel spreadsheet. So various specific data access model are being used to access data from these sources. Let say XQuery is used to fetch XML data, SQL is used to query RDBMS and a custom code is written to query text files.

Now LINQ tries to resolve these issues by offering a uniform way to access and manage data. We can also see LINQ as a methodology that simplifies and unifies the implementation of any kind of data access.

LINQ consists of standard query operators to play with data irrespective of the data sources. So in a way we are on the path to use standard code against any data source like Microsoft SQL Server, Oracle, Access or Sybase. This makes developer life really easy, so there is no need to spend hours in learning various languages (access methods) to work in multiple projects.

So precisely Microsoft is extending the power of .Net languages specifically C# and Visual Basic.Net to include this kind of functionality named as LINQ. This kind of implementation reuses the developer's existing knowledge of Object Oriented programming to play with databases, XML, objects, and few other data domains.

“Microsoft original motivation behind LINQ was to address the impedance mismatch between programming languages and database.”

LINQ to SQL (previously known as DLINQ), translates LINQ expression into SQL Server queries. People have already started creating LINQ libraries to query WMI, LDAP, SharePoint data sources.

LINQ to Entities

LINQ to Entities is supported by latest release of ADO.NET Entity Framework. The primary benefit of this framework is to run C# LINQ queries against a conceptual data model instead of tables. So LINQ to Entities seems to be a superset of DLINQ. The entity framework provides an additional layer on database schema which is available in terms of entities or conceptual model to query in .Net language.

That means writing something against entity is far more easy and manageable than writing complex SQL queries.

var CAEmployee = from employee in Employees where employeeLocation is CaliforniaState select employee;

Above statement is really easy to understand and I am really not bothered about database schema. What all I know is “employees” and I want to fetch California employees.

I guess this makes lot of sense and I really appreciate this work by Microsoft.

Your Data, Any Place, Any Time - SQL Server 2008 "Katmai"

I guess now I should start writing my blog with the CTP (Community Technology Preview) release of SQL Server 2008 code name "Katmai". So let see what we got in this latest release of SQL Server from Microsoft.

So what Microsoft is calling its new release "Your data, any place, any time"......hmm.......sound good to me....

The major feature I see in this release is the capability to store data in file system along with existing relational and XML data. This new feature provides the facility to store large binary data in file system, still being a part of database with transactional consistency. So looks like an effective storage model with all database benefits.

Another interesting feature is working with entities instead of tables. So this new ADO.Net Entity Framework enables developers to work with business entities like "employee" or "student" instead of using tables.

LINQ (Language Integrated Query) another new feature is very useful with its capability to query the SQL Server in any programming language instead of using SQL to query the database.

Encrypting the whole database is another enhancement in this release. That means instead of Encrypting a table or column we can encrypt the whole database, log, or data file without making much change in application logic.

On the same lines encryption with third-party key is also beneficial for regulatory compliance.

Automatic recovery of corrupted data pages from mirrored server is another important enhancement in this release and the best part of this feature is transparency to end-user.

Resource Governor, as the name says coming up for the first time with SQL Server 2008. It’s very useful to define the priorities and resource limits, thus getting consistent and predictable performance benefits.

Go to http://www.microsoft.com/sql/prodinfo/futureversion/default.mspx to know more about SQL Server 2008.