New in SQL Server 2005 for Developers (Part 1)

June 10th, 2009 No comments

May be I’m writing this very late, but will be useful for someone who is looking for features that are introduced in 2005. The content may not be properly formatted as it was prepared in a hurry.

Common Table Expression

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to:

  • Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.
  • Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
  • Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
  • Reference the resulting table multiple times in the same statement.

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

Sample (Northwind database):

WITH TopCustomers (City, Cnt)

AS

(

      SELECT City, count(*) FROM customers

      GROUP BY City

      HAVING count(*) > 1

)

SELECT * FROM

      Customers C inner join TopCustomers on C.City = TopCustomers.City

 

TOP function

SQL2005 allows TOP within the sub query also:

— Simple TOP query

SELECT TOP(5) *

FROM Employees;

— Nested TOP query

SELECT *

FROM (SELECT TOP(5) *

      FROM Employees

      ORDER BY [EmployeeId])

AS E

Order by EmployeeId Desc

Pivot Table

Another feature in 2005 is the Pivot table:

 

SELECT ProductId, Sum(Quantity*UnitPrice)

FROM [Order Details]

GROUP BY ProductId;

 clip_image0011

 

The same query as a Pivot table:

WITH Sales(ProductId, SalesAmt)

AS

      (SELECT ProductId, Quantity*UnitPrice

            FROM [Order Details])

SELECT ‘Sales’ AS SalesByProduct, [23], [46], [69], [15], [3]

FROM

      (SELECT ProductId, SalesAmt FROM Sales) AS SourceTable

PIVOT

      (

      SUM(SalesAmt)

      FOR ProductId IN ([23], [46], [69], [15], [3])

      ) AS PivotTable;

 clip_image002

 

Though this doesn’t look very flexible, there will be instances where this can be applicable.

 

 

Categories: Idola Tags:

The Official Whitehouse Photostream

June 5th, 2009 No comments

Obama

President Barack Obama bends over so the son of a White House staff member can pat his head during a family visit to the Oval Office May 8, 2009. The youngster wanted to see if the President’s haircut felt like his own. (Official White House Photo by Pete Souza)
This official White House photograph is being made available for publication by news organizations and/or for personal use printing by the subject(s) of the photograph. The photograph may not be manipulated in any way or used in materials, advertisements, products, or promotions that in any way suggest approval or endorsement of the President, the First Family, or the White House.

Find more photos check out The Official Whitehouse Photostream

Categories: Fun Tags:

Picture of the Year/2008 – Wikimedia Commons

June 3rd, 2009 No comments

The Wikimedia Commons Picture of the Year 2008 competition is now over and the winner has been selected. In the final 712 Wikimedians voted, of which 74 voted for the winner, Horses on Bianditz mountain. With 71 votes Fire breathing takes the second place. The third place is for Steam locomotives in the roundhouse with 46 votes.

via Commons:Picture of the Year/2008 – Wikimedia Commons.

Categories: Fun Tags:

Identifying employee skill gaps

June 2nd, 2009 1 comment

Identifying the areas where organizations need to improve their capabilities most is an important step in any change effort. Such a skill is helpful particularly in times of cost-cutting—where limited training resources must be used to close the critical competency gaps that prevent companies from meeting business objectives.

However, many training programs don’t yield the desired results. One reason is that they are usually launched without sufficient knowledge of where the gaps in employee skills exist. We find that a good way to pinpoint these learning needs is to survey employees and let them evaluate the current skill levels of their peers and estimate the skill level their group must reach in order to be successful. An added advantage of this inclusive approach is that it heightens employees’ awareness of their learning needs and helps break down any resistance to learning new skills.

via Identifying employee skill gaps – The McKinsey Quarterly – Identifying employee skill gaps – Organization – Talent .

Categories: News Tags:

A new tool for communication and collaboration on the web

June 2nd, 2009 No comments

Google Wave is a new tool for communication and collaboration on the web, coming later this year. Watch the demo video below, sign up for updates and learn more about how to develop with Google Wave. Google Wave will be available later this year.

Categories: Fun, News, Technology Tags:

ShifD

June 2nd, 2009 No comments

ShifDHow many times have you sent yourself an email or SMS of something you want to remember, like a book or a URL and then it gets lost in your inbox, never to be seen again. With ShifD you can easily send an SMS of a note, place or link that will appear on your computer.

The ShifD website is focused on a simple, clean and friendly design. You can add content with tags that are easily editable. Add a Link to read on your mobile phone later or a note to access from your home computer. Find more about ShifD here.

In its first introduction of a consumer technology solution, The New York Times Company has launched the beta version of  ShifD.  The application, which is accessible on the Web, various mobile devices and on the desktop of Adobe Air, allows users to save links, notes and places.  Users can update and retrieve data through all these platforms.

Above is our interview with Times developers Nick Bilton and Michael Young, who created ShifD.  We taped this piece earlier this week at the Times headquarters.

Read more at New York Times Company Launches ShifD, a Mobile/Web/Desktop Application for Storing Personal Information

Categories: Idola Tags:

A little about E-book readers

June 2nd, 2009 No comments

Now that there are many different e-book devices in market, there are also different formats available. Very simple is the plain text format which can be read using any text reader. If you want to use little advanced features like bookmarking, then you can download Adobe Digital Edition to read the EPUB editions.  Common formats are .epub and .mobi.  Check Project Gutenberg, ibiblio and openebook for free electronic books.

Categories: Technology Tags:

SlideRocket – The Leader In Online Presentations

June 2nd, 2009 No comments

SlideRocket is the leader in online presentation software. With SlideRocket you quickly make stunning presentations, manage a library of slides and assets, share them securely with colleagues, and measure how they’re used. SlideRocket redefines what presentations can do and what they can do for you.

SlideRocket is a revolutionary new approach to presentations. It’s the only presentation software that allows you to create stunning presentations, manage them intelligently, share them securely and then measure the results. SlideRocket also includes an online marketplace where you can find all the content and services you might need to make your presentations great. SlideRocket goes beyond traditional presentation tools by harnessing the power of the Internet and making everything available to you in an integrated and intuitive online interface. SlideRocket is provided in a software-as-a-service model in a variety of price points starting at free.

Learn more about SlideRocket at SlideRocket – The Leader In Online Presentations.

Categories: Fun, Technology Tags:

Anti-Money Laundering (AML) Source Tool

June 2nd, 2009 No comments

This research guide, or “source tool,” is a compilation of key AML laws, rules, and guidance applicable to broker-dealers. Several statutory and regulatory provisions, and related rules of the securities self-regulatory organizations (SROs), impose AML obligations on broker-dealers. There is also a wealth of related AML guidance materials. To aid research efforts into AML requirements and to assist broker-dealer compliance efforts, this source tool organizes key AML compliance materials and provides related source information.

When using this research “tool” or guide, you should keep the following in mind:

First, securities firms are responsible for complying with all applicable AML requirements. Although this research guide summarizes some of the key broker-dealer AML obligations, it is not comprehensive. You should not rely on the summary information provided, but should refer to the actual statutes, rules, orders, and interpretations.

Second, AML rules, regulations, and orders are subject to change and may change quickly. While the information summarized in this guide is current as of January 13, 2009, please note that in July 2007, the SEC approved the establishment of the Financial Industry Regulatory Authority (FINRA). FINRA consolidated the former NASD and the member regulation, enforcement, and arbitration operations of NYSE Regulation. The Source Tool reflects the historical issuance of AML rules and guidance by the NASD and NYSE.

Finally, you will find a list of telephone numbers and useful websites at the end of this guide. If you have questions concerning the meaning, application, or status of a particular law, rule, order, or interpretation, you should consult with an attorney experienced in the areas covered by this guide.

Disclaimer: This compilation was prepared by staff in the Office of Compliance Inspections and Examinations (OCIE), Securities and Exchange Commission. The Securities and Exchange Commission, as a matter of policy, disclaims responsibility for any private publication or statement by any of its employees. The views expressed in this document are those of the staff and do not necessarily represent the views of the Commission, or other Commission staff.

Find the complete article at Anti-Money Laundering (AML) Source Tool.
Categories: Banking and Compliance, News Tags:

Handbook for buying Compliance Solution

June 2nd, 2009 3 comments

What is Regulatory Compliance?

Regulatory compliance describes the goal that corporations or public agencies aspire to in their efforts to ensure that personnel are aware of and take steps to comply with relevant laws and regulations.

– Wikipedia

In general, compliance means conforming to a specification or policy, standard or law that has been clearly defined.

Compliance in an organization is a cluster of programs, policies and procedures to comply to regulation and can be broadly classified into:

  • BSA/AML Compliance
  • OFAC Compliance
  • IT Compliance
  • E-mail Compliance

The following sections briefly update the BSA part of the compliance.

A Compliance Solution

BSA/AML Functions

Any AML compliance product should be a broader set of the following basic set of features:

  • Customer Due Diligence and Know your Customer refers to customizable policies and procedures of an organization to know the customers with whom they are dealing with. KYC should support different elements based on the organization’s needs.
  • Enhanced Due Diligence refers to the Customer Identification Program mandated by the USA Patriot Act. Tools within the system should allow users to prepare customized questionnaires for customer staging and proper workflow tools to validate.
  • Transaction Monitoring refers to suspicious activity and fraud monitoring on customer transactions which will be mostly by the use of rules according to client need based on BSA laws, regulations and directives. The laws are based on the geographical position of the financial institution and the customer the organization deal with. The function should initiate investigations automatically using the detection mechanism and support easy-to-use tools for a detailed investigation.
  • Case Management and Regulatory Reporting refers to investigation of cases and different methods of reporting the cases to different regulatory authorities.

Common functions

In addition to the above Compliance related feature, the product evaluator from the organization should also have answers to the following questions which mostly apply to any financial software applications.

  • Global audience. Make out a list of countries where the product can be used. Does the product provide compliance to all the regulatory needs for that country?
  • Multi-lingual. Are multiple languages supported? If yes, is multi-language support installation specific? How many languages are supported?
  • User and Rights Management. How are users managed within the application? Does the application use Active Directory? Can the user be given rights based on windows rights?
  • Work flow and Routing mechanisms. How can I create job functions within? How will I assign rights?
  • Batch processing. Will the app allow batch and bulk processing for certain tasks? Is there a list of such processes?
  • Data Import. What are the ways data can be imported? Direct from source systems through channels? Flat files? Database table transformation imports?
  • Information Reporting and Data Export. What are the ways to generate reports? Is there a way to generate reports automatically?
  • Tracing, Logging and Auditing. Does the application support customizable logging to direct to event viewers, flat files, databases and even sinks? Is enough information logged for auditors? For product support are there any trace switches?
  • Licensing. What type of licensing is available? Is there an evaluation version? Can I buy more products in the future?
  • Service and Support. What kind of customer services is provided? What are the terms and conditions?

Platform

In addition to the features, a detailed pre-requisite for the product should be obtained for the product cost statement.

  • Is a dedicated server necessary? What is the Operating System? Are there any limitations in choosing the OS?
  • What platform is it running on? Open source or Proprietary or Legacy? Java or .NET or PHP? Win forms or Web or both? IIS or Apache or Websphere?
  • Is the application dependent on a database? Is that database free? Does the organization already have license for the database? What database edition is required by the product?
  • Is the application using any third-party tool? Is a list of those third-party tools available? Do I need any email clients? How many tools are free? Any particular version? Can those tools be upgraded for free if necessary?
  • Do the end-users require any plug-ins or software to run the application? What is the setup cost for installing that plug-in? Does the end user need to change any browser settings for running scripts, ActiveX, etc?
  • What will be the setup cost? What kind of support is required by the organization for installation?
  • Any task scheduler necessary to run jobs?

Based on the organization’s software policy, a cost sheet can be generated using the following entities.

Features Check-list

AML Product

The following table can be used to check the product feature list before evaluating a product:

Feature

Available Yes/No

Comments

Web based application

Yes / No

List the advantages and disadvantages of having a web or windows based application over the others

Windows based application

Yes / No

Identify if all the features are available in windows or web as a whole. If not list the features that are available in each mode

Customer Due Diligence (CDD)

Yes / No

Risk classification

Yes / No

Customer risk scoring

Yes / No

Risk management

Yes / No

Enhanced Due Diligence (EDD)

Yes / No

Transaction Monitoring

Yes / No

Ready-made rules

Yes / No

Customize rules

Yes / No

Schedule monitoring

Yes / No

Case Management

Yes / No

Case Auditing

Yes / No

Case Investigation

Yes / No

Workflow management

Yes / No

Customize Pages

Yes / No

Selection filters

Yes / No

Regulatory reports

Yes / No

Suspicious Activity Report

Yes / No

Currency Transaction Report

Yes / No

Electronic filing

Yes / No

Custom regulatory reports

Yes / No

Reporting

Yes / No

Export as PDF, Excel, etc

Yes / No

Charts

Yes / No

Platform

The following table will help in putting up together the list of software necessary in addition to the products itself.

Component

Description (list all the required software)

Operating System (in Server)

Database (in Server)

Third Party tools (in Server)

Application Server

Data Access Components and Drivers

Task Schedulers

Office tools

Email client

Supported Browser

File reader

Report viewer

Links

The following pointers can be used for reference. These web links are pointers to the AML and Banking related web sites.

Read more…

Categories: Idola Tags: