Using Merge in SQL 2008

First let's begin with the basic type 1 usage.

All fairly self-explanatory. We have a source and a target. We join the two tables, identify new records, changed records and deleted records. And deal with them appropriately.
So let's move onto the type 2 changes. Here we need to:

• Insert New Records
• Disable changed records
• Add a new record for the changed records

That's 2 inserts and an update. The MERGE won't allow that so we have to nest our merge inside an insert. Luckily this works because MERGE allows output!

Ignore the outer INSERT for the moment. We have a MERGE statement which is very similar to our type 1 MERGE. Compare the Source and Target, INSERT new records as required. The difference being that when the matched record contains a change, instead of updating the changed field, we set the CurrentFlag and the EndDate so as to disable the record.

Of course that means we still don't have a record which contains our updated values. So, now lets turn our attention to the OUTPUT clause.

This will be familiar to anyone who used it in SQL 2005 in much the same context - to identify rows which have been modified/added/deleted. Really the origin of MERGE seems to be strongly rooted in the SQL 2005 OUTPUT clause. On the upside, unlike the SQL 2005 version and UPDATE is an UPDATE and not a DELETE and an INSERT. Small but handy! Anyway, where were we? Let's return the columns we need to create a new record and a special $Action column - this tells us what actually happened ie UPDATE, INSERT or DELETE. We need this because the OUTPUT will return everything that occured in the MERGE.

Now we have a set of rows that have been actioned by the MERGE. Still, we haven't actually done anything with them.
To do that, we enclose the entire MERGE statement, including it's OUTPUT clause of course, into brackets so we can make it the FROM clause for an INSERT. Our OUTPUT clause is return the columns we need for the INSERT remember? So now we just add our INSERT INTO at the front, add an alias to the closed bracket of the FROM clause AND, drumroll... whack a WHERE clause on the end. This where clause just filters the OUTPUT from the MERGE so we only insert new records for those that were updated, ignoring those that may have been inserted etc in the MERGE.

Microsoft Silverlight Financial Dashboards

Hello Everyone,

I just came back from my vacation and I found this interesting Article about Microsoft New Financial Dashboard using Silverlight The Reports are amazing and i find something awesome to talk about.

Please check the below this for this Demo

http://www.microsoft.com/msft/IC/FinancialStatements.aspx?table=IncomeStatements

The Layout was based on XBRL which is something I need to study more before I start talking about it.

Anyway I think that Microsoft Started doing something interesting in the BI After all

Microsoft SharePoint 2010 Sneak Peek Videos

Microsoft releases the first look for Microsoft SharePoint 2010 Extra Info can be found on

http://sharepoint.microsoft.com/2010/Sneak_Peek/Pages/default.aspx

It seems like Year 2010 will be an interesting on for all SharePoint Lovers :)

Localizing Reports and Reports Models

Reports and report models can be localized at many levels. Following are some ideas around the current support in SQL Server 2008 for each type of localization.

- Localized metadata at report design time

•  Example: Arabic report author sees “3ameel” instead of “Customer” in model explorer
• Report models do not support multiple languages for metadata names in a single model file
• It should not be difficult to build a custom solution to generate localized versions of the model file

1. Original .smdl file + XML file with localized entity/attribute/role names => localized .smdl file
2. Could use either XSLT or minimal code
3. If implemented, reports would run against any localized version of the model because IDs are unchanged

• Metadata displayed in the report (such as column labels) would be in the language of the person who designed the report. Since they are merely text values copied in from the model explorer, they would not change at report run time.

- Localized metadata at report run time

•  Example: Arabic report consumer sees “3ameel” instead of “Customer” in report column label
• SQL RS reports do not directly support localization of text labels in a report
• Some customers have implemented this using a custom “resource” assembly deployed on the server, and all labels in the report are replaced at report design time with expressions that retrieve the appropriate resource string from the custom assembly (sample)
• This is obviously cumbersome to set up at report design time, but it does work

- Localized data formatting at report run time

•  Example: Arabic report consumer sees numeric and date values in the report data formatted as “1.234,56” and “27/04/2009” instead of "1,234.56" and "04/27/2009".
• o This is supported by the default number formats available on the ribbon in Report Builder 2.0. In the dialog box, select the “Use regional formatting” checkbox.

- Localized data values at report run time

•  Example: Arabic report consumer sees product category “Akala” instead of “Bicycles” in report data
• This is typically done by storing localized values in the database as separate columns or as lookup tables based on user culture.
• Offhand I can’t think of a slick way to do this with report models. If you have some ideas, let me know.