Analytic Challenges in the Banking Industry

Analytic Challenges in the Banking Industry from DMReview lists a number of real opportunities for high-impact data analysis in any Bank environment. Great ideas if you are, and it will make you think about your environment if you aren't.

Mark Rittman: OWB10g Online Training Resources

Mark Rittman posts about some free OWB10g Online Training Resources that are available, in great detail. Oracle Warehouse Builder is the native Oracle ETL tool, comparable to DTS or Informatica. The main drawback of OWB is that it only supports one destination: an Oracle database. But for people that can work within this limitation, it gets great reviews.

Mark has posted many good articles recently on his site, all very thorough and great to read. If you don't check his site every day, you just might miss something you're looking for.

Intelligent Enterprise Magazine: More Than Pie Charts

Intelligent Enterprise Magazine: More Than Pie Charts has the tagline "For actionable BI decisions, don't rely on simple, outdated visualization and presentation techniques."

A dizzying array of great technology exists today that addresses most business intelligence (BI)-centric issues. But then why is it that much of what is being implemented is uninspiring BI technology that leads to mediocre solutions? Case in point, the ever-present pie chart.


Can you effectively interrogate a large OLAP cube using a simple pie chart? Can you really report and communicate all the necessary insight through your dashboard, given the volume and complexity of data, with just a pie chart? The obvious answer is no. But any of the advanced tools necessary to address complex BI issues seem slow in adoption, including visualization (Gartner, Dec. 5, 2003). Even though more robust visualization tools and technologies are available, the pie chart continues to be thrown in front of users.

The article advocates some cutting-edge visualization such as 3d surface maps, for example:



My answer? You've got to be kidding. Of course Pie charts aren't good for OLAP cubes - they only show two dimensions, and they are not deigned for viewing time trends. That's what bar charts and line graphs are for.

I've designed a few dashboards, and a few visualization techniques for a variety of customers, and without exception the fastest and easiest way to process a large amount of information are pies, bars, and line graphs. And I'll add maps into the mix for a geographic dimension, they can help some process great deal of information quickly in some cases. But all of the fancy stuff? Spiders, Voronois, Kaplan-Meiers, Lineaments and others? They make great gee-whiz graphs, but in just about every case you can get more inforation faster from the basic ones. They are great eye candy and good conversation pieces, but I wouldn't challenge someone to make a quick decision by looking at any of them.

Stick with the tried and true choices (including pie charts) which are popular because they are proven.

How Analysis Services Works

Mosha Pasumansky should know - he is the development lead after all, and has leading it since before it was bought by Microsoft. He points to a presentation on only4gurus that explains a little bit about what is going on behind the scenes of Analysis Services. The fact that nearly everyone approaches it as a "black box" that works as it should is a testament to the thought that went into its development.

He also linked my blog off his... wow, I consider that quite an honor.

ProClarity for Reporting Services

ProClarity - ProClarity for Reporting Services is very timely - Reporting Services, the free SQL Server add-on meant to compete with expensive reporting suites such as Crystal Reports, is exploding in popularity. However one of its weaknesses (especially from my point of view) is its lack of good support for OLAP data sets. For Proclarity clients, looks like this may fit the bill.

More screenshots.

Getting to Know the SQL Server Manager Tools

The VS Data Team from Microsoft points out Getting to Know the SQL Server Manager Tools on Bill Ramos' blog. He is part of the upcoming web cast series focusing on SQL Server 2005 (link in the article) and goes over some of the new features of this tool, which replaces Enterprise Manager.

BeyondVC: Open source and software licensing

BeyondVC: Open source and software licensing: Intersting perspective at some of the resonable issues with choosing the open source option nowadays.

Data Warehouse Schema Diagram - from Only4Gurus.com

Data Warehouse Schema Diagram - from Only4Gurus.com: "The Commerce Server Data Warehouse is a combination of a SQL Server database, an Online Analytical Processing (OLAP) database, and a set of processes that a system administrator uses to import and maintain data. The Data Warehouse stores and manages data in the database for the purpose of performing business analytics: data mining, prediction, and analysis reporting. "

Microsoft Commerce Server isn't that common, but it is an ecommerce server with a built in warehouse. If you are responsible for reports, analysis, or trending for Commerce Server, this will be invaluable.

Clustering on Virtual Server 2005

I've covered on here clustering with VMWare in the past, if you happen to have Microsoft's Virtual Server (included in MSDN subsrciptions) Jeff Alexander points to instruction for Clustering on Virtual Server 2005.

REX - Java OLAP browser with XMLA support for Analysis Services

Mosha Pasumansky (one of the original developers of Analysis Services) points to REX - a Java OLAP browser with XMLA support. The screenshots make it look like a very adequate alternative, even though I prefer web-based plug-ins. Definitely worth a look. requires that you have the XMLA SDK installed on your Analysis Services Server.

Returning Random Numbers for every row in a SELECT statement

A solution to a fairly common problem, mostly encountered during testing scenarios: Returning Random Numbers in a SELECT statement.

A good bookmark to keep.

SQL Server Integration Services: text mining

First, what is SQL Integration Services? It's just the new name in SQL Server 2005 for DTS.

Donald Farmer has an article, Things to try with SQL Server Integration Services: text mining, about two new objects in this new version, Term Lookup and Term Extraction:

Term Extraction enables you to retrieve the key terms from a Unicode string or text column. It scores terms based on a number of factors, including its English grammar and syntax (only English at the moment), but also parameters you can tune yourself such as the length of phrases and their occurrence. The output is a single column containing all the key terms found - and this output can be sorted, aggregated, routed and saved like any other data.



Term Lookup is, in a sense, the opposite functionality. If you have a reference table with a column containing key terms, you can lookup from an incoming text field to a reference column in that table to see if it contains key terms found in the reference table. You should also identify a column to pass-through from the original source, which enables you to refer the results back to the incoming records.

Donald's focus o his blog is this newest version of DTS, and he has a number of other posts up already.

Sun To Give Out Solaris OS For Free

Week old news, but relevant none the less: CRN | Breaking News | Sun To Give Out Solaris OS For Free:

Sun, which has never completely rebounded from the tech collapse in 2001, hopes the no-cost of Solaris 10 will not only attract customers but also expand the number of developers who write programs that work on computers running the operating system.

The result, Sun believes, will be renewed demand for its servers and services. The company also will charge subscription fees for Solaris support and service programs that are typically sought by the businesses and organizations that Sun targets.

OracleApps: Hyperion Consultants required for the Middle East

This is too cool not to pass on from Richard Byrom's OracleApps blog:Hyperion Consultants required for the Middle East:

I was e-mailed the other day by someone looking for Hyperions Consultants - we decided to put a post on the blog to see if we get any responses as it is highly likely there are some Oracle Consultants out there who know Hyperion. If you are a Hyperion Consultant, have Hyperion skills or know of anyone who may be interested then please let us know.

Follow the link for requirements and contact info.

Connecting to Analysis Services with HTTP or HTTPS

Analysis Services allows you to connect via a web service using SOAP, but there's not alot of documentation on this. Improved Web Connectivity in Microsoft SQL Server 2000 Analysis Services is an MSDN article that explains some of the technical requirements.

Not having this set up is the cause of alot of connectivity problems. For example, pivot tables from Office web components, which get embedded in a web page, will fail to connect from a remote machine unless this is working correctly. Other Excel based components that try to access an Analysis Services server over a network (usually by being embedded in a web page) will also not work without it. For example, in MS Project Server, if you can only view the Portfolio Analyzer views when logged in to the actual analysis services server, this is the cause.

Fixing it is easy if you know what you are doing. There is a file called msolap.asp included as part of the Analysis Services installation, usually under \Program Files\Microsoft Analysis Services\ . This file needs to be placed in the web root of the same machine (yes, IIS needs to be running). Then voila, it magically works.

There is one major exception - if Sharepoint is installed on the same server (as it sometimes is, and Project Server requires Sharepoint). In this case, msolap.asp must be excluded from the managed paths for the Sharepoint (this is done under the Sharepoint administration for the virtual server ("Define Managed Paths"). Exclude msolap.asp, and it will be clear to get out and do it's job.

Using CLR Integration in SQL Server 2005

There's alot to look forward to in SQL Server 2005, and one of the big improvements that can't be overlooked is the integration of the .NET CLR, replacing the T-SQL environment. In other words, stored procedures, triggers, and functions can be written in any .NET language, most notably C# and VB.NET, but also including such divergent choices as Perl, Python, and even COBOL. Whatever is your favorite, you can use it to run logic in the database.

Data Access and Storage Developer Center: Using CLR Integration in SQL Server 2005 is an MSDN article that has a rather detailed overview of how to use the CLR. It includes to pretty complete examples, ready to cut and paste as a template, that demonstrate running a production scheduling routine for a manufacturing facility, and of creating a data validation trigger to validate email and phone number columns. Lots of good examples, all with code in C#.

Wal-Mart's 460 TB Teradata warehouse

Slashdot points to The New York Times > What Wal-Mart Knows About Customers' Habits, a writeup on how WalMart uses 460 TB of customer transaction data to forecast demand and increase revenue. Prime example are how the CIO asked to forecast what items will be in most demand when a hurricane hits, based on past data (answer: 7 times the normal volume of pop-tarts, and even more than that in beer), and studies like if someone comes in for cold medicine, they also buy some soup and orange juice (so why discount all 3? 2 is plenty.)

Taking it one step farther, there's some discussion about how they can actually arrange for suppliers to own the item until it is sold at the register (so technically, wal-mart will own no inventory).

A very good read, and a great illustration about how to think of the datawarehouse as a tool to take advantage of opportunities based on past trends. It got me thinking along several lines about how to better leverage some of the projects I am working on.

1,000,000 Sample Records

Don't remember where I got this link from, but I thought I'd pass it on. Down a page or two is a collection of 1,000,000 sample names, SSNs, and phone numbers useful for testing, etc.

IBM's Database VP: Our Grid Scales Better

A short interview: IBM's Database VP: Our Grid Scales Better:

Q&A: Bob Picciano, vice president of database technology at IBM's Toronto lab, says the company continues to pummel main database rival Oracle in terms of price and scalability.

Sure, says IBM's database guru, Oracle is talking a good game on grid computing with Oracle Database 10g, but IBM's still pummeling its biggest database competitor in terms of price and in terms of how many nodes its technology has scaled to in production environments—try 1,000, compared with Oracle Corp.'s 16.

SQL Server 2005 Webcasts

Coming up in the first week of December.... SQL Server Developer Center:

The Microsoft SQL product team is hosting special week of live MSDN Webcasts to provide you a sneak peak at what Microsoft SQL Server 2005 Beta 2 has in store for the future database development. SQL Server 2005 offers a new paradigm for database development that integrates SQL Server and the CLR to provide several major benefits including enhanced programming mode, enhanced safety and security, user defined types and aggregates, and a common development environment, where database development is integrated into the Microsoft Visual Studio 2005 development environment.

Lot of good webcasts scheduled of all levels, including overviews of the management tools and the free add-ons. Watch those, and you can claim your place among SQL Server 2005 (aka Yukon) gurus.

Virtualisation and Reality

Sadagopan points out this Infoworld article, Virtualisation and Reality which discusses the growing acceptance of Virtual Servers.

Server Virtualisation is very cool stuff, allowing you to run one or several "virtual" stand alone servers within a normal OS environment. To your network, they actually do look like several unique machines, although they all run on your desktop, or server depending on which product you buy. The first time people see it and use it, it comes as a bit of a revelation how useful it can be. For geeks that like to dabble (a group most DBAs are probably part of) it can be a sandbox to try new ideas or programs, comletely fenced off and safe from your normal OS. The products (from VMWare and Microsoft (Virtual PC) almost exclusively) allow you to mount iso images, so you can do cool stuff like download and spin up and check out cool Knoppix images that you'd never mess with otherwise, because doing so used to require a seperate machine. I have posted links before to instructions on setting up RAC and an active-active cluster in VM sessions.

Among our clients, which include Fortune 500 companies and midsize, only two have bought in to Virtualization in a big way. One is a hospital holding company (all of a hospital's apps run on a single server, and when a new hospital is added, the machine's are simply duplicated. An entire environment set up in a matter of hours, and completely configured and ready to go), the other is one of the major car manufacturers Alabama facilities, who does it for the normal reasons, namely utilizing hardware more efficiently.

There are some undeniable advantages, such as backing up or duplicating a virtual machine is as easy as zipping up the files (the entire virtual machine, hd, memory, everything is in files) and copying them off. There's also some disadvantages, such as if the host OS goes, all the virtual mchines go with it. There are some failover features in the server products, but I have heard they are buggy. And then there are licensing wrinkles that come in to play. Microsoft is experimenting not charging for virtual instances if your company has a certain agreement in place, but only if you use Virtual PC, of course.

It's still something that you'll see soon if you haven't yet.

SharePoint Explorer tool

Sharepoint is the simple document managemtn/team portal that comes free with Windows Server 2003 (and with Frontpage 2002). It's extremely easy to set up and very intuitive to use, requiring no special skills whatsoever. You owe it to yourself to install and check it out if you haven't...

From Tariq Blog: A cool free tool to explore all the properties with every SP site on a server, including plenty of hidden ones. I just tried it out on an installation I have here, and it is very handy.

UKOUG Oracle Business Intelligence 10g Papers Available For Download

UKOUG Oracle Business Intelligence 10g Papers Available For Download from Mark Rittman, Oracle guru extrodinaire. Good stuff! The ppt slides include notes for the presentation, showing Oracle is serious about moving past the data warehose and getting closer to the end-user for a complete BI experience.

Fraudulent Oracle Trainer(s)

Before you consider purchasing Oracle from someone over the internet, read this. The examples in the Don Burleson column are actually so blatant it's kind of funny, until you realize real money has been lost.

Oracle Grid Computing - the Solution to Islands Of Computing

Supercomputing Online has someone at Oracle World Latin America in Brazil and reports on this session by Oracle Senior Vice President of Server Technologies Andy Mendelsohn: "Mr. Mendelsohn used Mercado Libre -- the largest online auction site in Latin America -- as an example of a customer who is moving to Oracle Enterprise Grid Computing. Mercado Libre saved US $500,000 in hardware costs using Oracle Real Application Clusters, Intel and Linux to consolidate and standardize their infrastructure. As a result, they have the ability to add capacity on demand to keep up with their rapid growth.

'When you build a building, you want each floor to be the same and not dramatically different,' explained Mr. Mendelsohn. 'The same is true when you are creating the data center of the future. You want to use standard components and at the lowest cost.' "

Who would disagree? Anyone getting a price quote from Oracle on the cost of implementation and support of such a solution might wonder about all that talk of lowest cost though, since with some fairly standard options the bill for software to Oracle alone would come in at $80,000/CPU or more, with another $16k+/year/CPU into perpetuity.

What's more, the only real-world, in production Grid systems I have yet to read about are built on open-source tools. Oracle has no first tier customers they hold up as success stories (the ones that do invariably use it in some sort of development environment, not production.)

BI App of the day

In the spirit of the election today, business2 posts a few links to some electoral vote prediction sites. The coolest one I think is the NYTimes.com 2004 Election Guide, which shows the state of things not only for the Presidential election, but all of the Congress seats too, plus allows you to very quickly see the outcomes of past Presidential elections. Cool stuff.

business2 names it the "least useful" but I disagree. Information like this is way harder to digest.

ASP.NET OLAP Web Control

Via Mosha Pasumansky, Richard Tkachuk (the guy who wrote the popular DHTML thin web AS client in the SQL Server Resource Kit) has authored an ASP.NET OLAP web control as a Pivot table-like Analyis Services front end. It requires the .NET framework 1.1 and is compatible with either AS 200 or Yukon.

CellSetGrid Intro

Cognos OLAP Overview

Pretty vanilla on-demand webinar from Cognos about what OLAP is and why you should care, which isn't all that special except that the speaker is Nigel Pendse of the world famous OLAP Report, universally considered the authority on the pulse of OLAP usage in the business world for everything from vendor market shares to industry penetration.

The first half is spent detailing the results from the last OLAP Repor (2003) as well as the most common benefits (in order: Saved Headcount in IS, Saved headcount in Bus. Depts, Reduced External IT costs, Reduced Non-IT costs, and Better Reporting). Requires signup.

RSS Reader Comparisons

My laptop had a problem recently and I had to ship it to HP. In the interim, I had to work on another computer, and since I use a news reader regularly, and I had to download one, I decided to check out some alternatives. I came across this list of RSS Readers.

YTo make a long story short, I tried a couple (including Pluck and Rocketinfo), and I ended up settling on the one I already was using, Sharpreader. It's simple, intuitive, and plain works.

For those interested, or if you are just looking for a hundred or so feeds to get started with, here's my OPML with all of the feeds I read.

Start-up Minefield

Off topic from the normal fare, but for anyone in a start up (or thinking about it) here'a little valuable education from Paul Allen: Internet Entrepreneur:

I have heard that Ray Noorda (Novell fame) used to say, Finders Keepers, Founders Weepers to indicate that most of the time inventors and entrepreneurs don't end up with much. Business savvy investors and later stage business managers can often clean up for themselves, leaving entrepreneurs with nothing but the pride of knowing what they started.

It appears that this has happened yet again, in a fairly high profile case.

I'm running a start up with a partner atm, so I might post some more links on such things from time to time.

Web 2.0 Presentations

Via Sadagopan, via Jeremy Zawodny, here are the Powerpoints from the web 2.0 conference last week. If you have some apare time, it's worth going through ones that sound interesting to you so you know what some very influential people are thinking about the near future.