As Windows NT Magazine's newly minted applications editor, I swung by the Microsoft SQL Server Developers Conference (DevCon) in early September, looking for an applications story. Naturally, Microsoft positions SQL Server as the premier database for Windows NT and is motivated to ensure that Microsoft's engine maintains a leadership position on Microsoft's enterprise OS platform. This position is one that Oracle and others will do their best to undermine as they allocate more development and marketing resources to their NT versions.
From an applications perspective, the DevCon started well. The keynote from Microsoft's Paul Flessner, general manager, SQL Server Unit, began by positioning SQL Server as a first-choice database engine for line-of-business (LOB) computing. He entitled his fifth slide, "Make It Easy--Customers Want a Solution, Not a Database." This sentiment is admirable but directed in this case to database solution developers rather than end users.
That focus got me thinking. What benefit does SQL Server really offer from an end-user, applications perspective? For SQL Server 6.5, the answer is a lot--a lot of potential, that is. So here are a couple of thoughts on how to use a handful of SQL Server 6.5 features to turn the relational database management system (RDBMS) engine into a genuine, added-value, information broker.
MOLAP: Microsoft OLAP
Unlike Oracle and Sybase, for example, Microsoft has not yet bought or built
any real online analytical processing (OLAP) functionality to enhance SQL
Server. Desktop OLAP delivers value to LOB users, whether they run it against
single applications, data marts, or data warehouses. OLAP is about constructing
views of the business and investigating those views from different
perspectives--something every LOB manager has to do. That requirement is why
Microsoft's lack of any clear OLAP offering and its reliance on third-party
application vendors is surprising.
The truth is that Microsoft has the foundation for a very powerful OLAP offering. The company just hasn't made the effort to pull the pieces together yet. In case you haven't heard, SQL Server 6.5 includes two fundamental OLAP data extraction capabilities: cube and rollup. Also, Microsoft Excel 5.0 includes two fundamental OLAP data presentation capabilities: Outlines and Pivot Tables. Combine the two and front end them with a wizard, and--voilà! Instant OLAP. Let's walk through what such a wizard could do.
The wizard would first ask a business analyst to define the key information dimensions that the enterprise stores in specific SQL Server application tables. The analyst would give these dimensions meaningful names and map them to their source database, table, and column. For example, the dimension account could be in the database gl, the table gl_account, and the column acc_code.
The wizard would then ask the analyst to define dimension sets and values. Sets are logical combinations of dimensions stored with their own set name. For example, the set market could consist of the dimensions product, salesperson, territory, and month. A "value" defines where to find the value associated with a particular set (for example, the value may be in a certain column within a particular transaction table) and defines how SQL Server is to aggregate (sum, avg, count, etc.) the value when someone queries the dimension set.
So far, this process has defined some metadata (data about data) that the wizard would store in tables for use later. This metadata is what OLAP application vendors such as Cognos and Business Objects call their catalog, or universe. In other words, SQL Server has already captured some real business intelligence.
Now you can leverage the business intelligence. At this point, the wizard would ask the analyst to define whether to create a cube or rollup query for each dimension set. As an alternative, the wizard could create a database view that represents a star, or fact, table for the dimension set. Next the wizard would ask the analyst to point to where the Excel data is stored and would ask whether to add the new OLAP capabilities to Excel. You could expect some considerable disk activity at this time, as the Wizard would be creating and testing SQL Server cube and rollup queries (or star views) and then adding them to a new Excel menu bar item that you could entitle olap.
The next time you fired up Excel, the new olap menu item would drop down. It would reveal a list of immediately useful, business-specific analysis options that would use the predefined cubes and rollups and would display their results either as Pivot Tables or Outlines. Imagine what a big win this capability would be for Microsoft, IS, and--most important--the information users.
User Alerts and Information Consoles
SQL Server 6.5 also builds on SQL Server 6.0's Mail API (MAPI) integration
capabilities: SQL Server 6.5 adds Hypertext Markup Language (HTML) publishing
capabilities and the ability to populate Microsoft Exchange folders with data.
What you have here is the foundation for email-based notification systems and
user-specific information consoles. A SQL Server stored procedure can respond
automatically to a database event by piping data to an email message that you
can route to one or more recipients. So this capability can deliver a useful
application-alert system. Equally, because you can use a SQL Server stored
procedure to build HTML pages for display in Internet browsers on a scheduled or
event-driven basis, you also have the foundation for an Internet/intranet
information console. Let's consider what Alert and Information Console wizards
could do.
The Alert Wizard would start by asking the business analyst to name an alert. Then the analyst would define the database, table, and columns for the alert to check; the event to trigger the alert; and the rule to use. The wizard could offer a basic set of events such as database inserts, updates, and deletes as choices, and a basic rule definition to check whether a value is less than, equal to, or greater than a specified constant.
If the analyst named one alert, the wizard would ask the analyst to define the message to send, the message's destination format, and the routing. The message text is what recipients would see, the destination option might be a simple email message or input into an Exchange folder, and the routing might be to a specific user or group of users selected from the mail directory. The wizard would finish by building and testing the Alert stored procedure. A successful build would create a functional, basic alert system that could deliver value to users the next time they opened their email inbox.
The Console Wizard would ask the analyst to name the console and to define a query to extract a meaningful data set, or to select an existing stored-procedure query to supply data to the information console. The wizard would then ask the analyst whether to create a new HTML page or to update an existing template with the data. The analyst would also specify the destination Web server so that Exchange could route the HTML pages to the correct location where users could retrieve them. You could combine the Console Wizard with the Alert capability so that you could specify which predefined Alerts to use to trigger the generation of the Console page.
Workflow Wizards
You can really see the potential of wizards when you consider them as the
starting point for more sophisticated workflows that involve decision-support or
transactional data. You could eventually combine the wizards. For example, the
Alert Wizard could be a starting point for either the OLAP or Console wizard.
Alternatively, you could eventually enhance each wizard to specify the instantiation of a registered business object (such as an OLE custom control-- OCX--or ActiveX control) that you could use to further extend the information workflow. For example, you could enhance the OLAP Wizard to instantiate a 3D data-visualization applet that receives the returned cube or rollup data and lets the user manipulate the information in a more contextually rich environment than, say, Excel can deliver. You could use the Alert Wizard to create a workflow item in a manager's Exchange inbox. This workflow item would also let the user load an applet that approves the transaction and moves it forward in its life cycle. The Console Wizard could provide HTML data for broadcasting to all users on a corporate intranet. For this purpose, you could use a Webcasting medium such as PointCast.
Play the Wizard
SQL Server 6.5 shows great potential, not just for delivering a database
engine but also for solving application needs of end users. I wouldn't be
surprised if Microsoft and key solution providers such as Timeline are working
on such user-centric, added-value wizards right now. If not, someone should be.
Maybe such wizards are already out there and need more exposure. In any case,
here's an opportunity to wave your magic wand by expressing your support,
dissent, or ideas for the OLAP, Alert, or Console wizard: Just email
stewart@winntmag.com.