What benefit does SQL Server 6.5 offer, from an end-user, applications perspective?
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.
Previous
Register
