1.0       Introduction


End-user application developers are increasingly building applications  around Web pages. The user interface development environment of choice is now a web page development environment. Data warehousing is one of the core responsibilities of information Technology. In many ways Data Warehousing fulfils the promise of  “getting the data out” after the OLTP based system “gets the data in”. The web revolution has certainly not replaced the need for the data warehouse. In fact the web revolution has raised everyone’s expectations much higher that all sorts of information will be seamlessly published through web browser interface. The audience for data warehouse data has grown from internal management to encompass customers, partners and much larger pool of internal employees. The web’s focus on the customer experience has made many organization aware of learning about the customer and giving the customer useful information.


The web revolution has propelled the data warehouse out onto the main stage, because in many situations the data warehouse must be the engine that controls or analysis the web experience. In order to step up to this new responsibility, the data warehouse must adjust. The nature of the data warehouse needs to be somewhat different.  (Kimball, p 4) As a result, our data warehouses are becoming data webhouses. The data warehouse is becoming the infrastructure that supports customer relationship management (CRM). And the data warehouse is being asked to make the customer clickstream available for analysis. This rebirth of data warehousing architecture is called the data webhouse.  (Kimball,  1999 (July))



2.0       The need for Data Webhouses



Consider the marketing analysts. Five years ago they were saying, “Soon, we will have databases recording every customer interaction, no matter how seemingly insignificant….” Then, they would go on to predict how that holy grail, the complete customer record, would allow us to understand customer behavior, interests, and needs in greater depth than ever before. From that understanding would come growth, profit, customer satisfaction — everything a business executive could want.


Well, when it comes to the complete customer record, the Internet has let the genie out of the bottle. The “clickstream”, that record of every mouse click or keystroke of every visitor to a Web site, has generated a more complete customer record than has ever existed in any form of commerce.  (Kimball,  2000 (April))



The data webhouse is a web instantiation of the data warehouse. The webhouse plays a central and a crucial role in the operations of the web enabled business. The Data Webhouse will,


·         Houses and publishes click stream data and other behavioral data from the web that drive an understanding of customer behavior.


·         Act as a foundation for web enabled decision making. The data webhouse must allow its users to make decisions about the web, as well as make decisions using the web.


·         Act as  a medium that publishes data to the customers, business partners, and employees appropriately, but at the same time protects the enterprise’s data against unattended use.







3.0       Data Webhouse Architecture


Web-created demands are drawing the data warehouse increasingly closer to the front line of operational reporting and operational response generation, forcing to rethink data warehouse architecture. Today's dramatically increased pace of business decision-making requires not only a comprehensive snapshot of the business in real time, but simultaneously, answers to broad questions about customer behavior. The data warehouse is taking central stage in the Web revolution, and it requires restating and adjusting our data warehouse thinking.


We believe Data Webhouse is a variation  of a traditional Data WareHouse.  But the architecture of a Data Webhouse is different from a data warehouse.


3.1        Features of the Data Webhouse


This “Data Webhouse” must:


·         Be designed from the start as a fully distributed system, with many independently developed nodes contributing to the overall whole. In other words, there is no center to the data Webhouse.

·         Not be a client/server system, but a Web-enabled system. This means a top-to-bottom redesign. A Web-enabled system delivers its results and exposes its interfaces through remote browsers on the Web.

·         Deal equally well with textual, numeric, graphic, photographic, audio, and video data streams because the Web already supports this mix of media.

·         Support atomic-level behavior data to at least the terabyte level in many data marts, especially those containing clickstream data. Many behavioral analyses must, by definition, crawl through the lowest level of data because the analysis constraints preclude summarizing in advance.

·         Respond to an end-user request in roughly 10 seconds, regardless of the complexity of the request

·         Include the user interface’s effectiveness as a primary design criterion. The only thing that matters in the data Webhouse is the effective publication of information on the Web. Delays, confusing dialogs, and the lack of the desired choices are all direct failures.  (Kimball, 1999, June)




With this data warehouse evolution, we have managed to make three big technical design factors more difficult.


o                    Challenges in Data Webhouse Design


·         Timeliness. Business results must now be available in real time. “As of the previous day” reporting, on the wish list two years ago, is no longer a sufficient pace. Increasingly more-efficient delivery pipelines with smaller, just-in-time inventories, along with mass customization, force us to quickly understand and respond to demand.


·         Data Volumes. The big move to mass customization means we now capture, analyze, and respond to every transaction in the business including every gesture a customer makes, both before and after operational or sales transactions and there seems to be no volume limit. For instance, the combined Microsoft-related Web sites, analyzed daily as a single entity, on some busy days have captured more than a billion page events!



·         Response Times. The Web makes fast response times critical. If something useful doesn't happen within 10 seconds the user may navigate to another page. Those of us who run big data warehouses know that many queries will take more than 10 seconds. (Kimball, 2000, p31)


o                    Sample Data WebHouse Architecture


As these design factors have become more difficult, we find ourselves supporting a broader continuum of users and requests. To address these issues, we need to adjust our data warehouse architecture. We can't just make our single database server increasingly powerful. We can't make it deliver all these complicated objects and hope to keep up with these escalating requirements.


The following diagram clearly explain the  a sample data ware house architecture.


Note :-

(Because of  the word limitations I have refrain from explaining the obvious and concentrated on the distinct deviations and the similarities from the  traditional data warehouse design architecture.)





(Source :- Kimball, 2000, p 32)



3.4        Deviations from Traditional Data Warehouse Architecture.


One way to take pressure off the main database engines is to build a powerful hot response cache (see above figure) that anticipates as many of the predictable and repeated information requests as possible. The hot response cache adjoins the application servers that feed the public Web server and the private firewall entry point for employees. A series of batch jobs running in the main webhouse application server creates the cache's data. Once stored in the hot response cache, the data objects can be fetched on demand through either a public Web server application or a private firewall application.


The fetched items are complex file objects, not low-level data elements. The hot response cache is therefore a file server, not a database. Its file storage hierarchy will inevitably be a simple kind of lookup structure, but it does not need to support a complex query access method.


3.4.1     The features of the “hot response cache”.


·         Custom greetings to Web visitors, consisting of both text and graphics

·         Dynamically chosen promotion content to Web visitors

·         XML-based (Extended Markup Language) , structured-form content to business partners (what we used to call EDI) requesting delivery status, order status, hours' supply in inventory (we used to measure days' supply, which is becoming obsolete), and critical-path warnings in the delivery pipeline

·         Low-level FAQ-like answers to problems and support requests

·         Top-line reports to management, needing significant integration across time (multi-year trends), customers, product lines, or geographies all delivered in three interchangeable formats including page-oriented report, pivot table, and graph, and frequently accompanied by images

·         Downloadable precomputed OLAP cubes for exploratory analysis

·         Data-mining studies, both near-term and long-term, showing the evolution of customer demographic and behavior clusters, and the effects of decisions about promotion content and Web site content on business done through the Web

·         Conventional aggregations that enhance query performance when drilling up through standard hierarchies in the major dimensions such as customer, product, and time. (Kimball, 2000, p34)


The hot response cache's management must help it support the application servers' needs. Ideally, a batch job will have computed and stored in advance the information object that the application server needs. All applications need to be aware that the hot response cache exists and should be able to probe it to see if the answer they want is already there. The hot response cache has two distinct modes of use; the nature of the visitor session requesting the data determines which one to use.


The guaranteed response time request must produce some kind of answer in response to a page request that the Web server is handling, usually in less than a second. If the requested object (such as a custom greeting, a custom cross-selling proposition, an immediate report, or an answer to a question) has not been precomputed and hence is not stored, a default response object must be delivered in its place, all within the guaranteed response time.


The accelerated response time request hopes to produce a response to the Web visitor's request but will default to computing the response directly from the underlying data warehouse if the precomputed object is not found immediately.


The application server should optionally be able to warn the user that there may be a delay in providing the response in this case. The Web server needs to be able to alert the application server if it detects that the user has gone on to another page, so the application server can halt the data warehouse process.


3.5        Similarities with Traditional Data Warehouses


Note that this strategy of seeking a precomputed answer and defaulting if necessary to the base data is exactly the way conventional aggregates have always worked in the data warehouse. The data warehouse aggregate navigator has always searched for aggregates to answer portions of an overall report query. If the navigator finds the aggregate, it uses it. But if it doesn't find the aggregate, it gracefully defaults to computing the answer slowly from the base data. Viewed this way, the hot response cache is a kind of supercharged aggregate navigator.  (Kimball, 2000, p35)


As we can see Data WebHosue is a refinement, not a distinct deviation from the traditional Data Warehouse.


4.0       Building the Data Warehouse



An interesting part of the emerging data webhouse is the data mart that  stores and presents the Web activity for later analysis. Fundamentally, we  want to analyze all the hits on our Web site. We want to build a  comprehensible view of the immense stream of clicks arriving at our sites,  whether we're dealing with our intranet users or with customers on our public  Web site. We call this aspect of our data webhouse the “clickstream data  mart.”



4.1        The Goals of the Clickstream Data Mart


The clickstream data mart can tell us a great deal about detailed customer behavior. If we have information on our customers' every click and gesture through our Web site, we should be able to answer such questions as:


·         What parts of our Web site get the most visitors?

·         What parts of the Web site do we associate most frequently with actual sales?

·         What parts of the Web site are superfluous or visited infrequently?

·         Which pages on our Web site seem to be "session killers," where the remote user stops the session and leaves?

·         What is the new-visitor click profile on our site?

·         What is the click profile of an existing customer? A profitable customer? A complaining customer that all too frequently returns our product?

·         What is the click profile of a customer about to cancel our service, complain, or sue us?

·         How can we induce the customer to register with our site so we learn some useful information about that customer?

·         How many visits do unregistered customers typically make with us before they are willing to register? Before they buy a product or service?


Given this information, can we imagine building the clickstream data mart using conventional slice and dice dimensional models? And if we manage to build it, how can we hope to analyze the clickstream data mart to answer all these questions?


4.2        Building  Clickstream Data Mart


To build the clickstream data mart, let's use a simple, four-step methodology to build the dimensional model.


·         Define the source of our data,

·         Choose the grain of our fact table,

·         Choose the dimensions appropriate for that grain

·         Choose the facts appropriate for that grain.



4.2.1     The Data Source for the Clickstream Data Mart


We need to go after the most granular and detailed data possible describing the clicks on our Web server. Each Web server will potentially report different details, but at the lowest level we should be able to obtain a record for every page hit with the following information: precise date and time of page click; remote client's (requesting user's) IP address; page requested (with path to page starting at the server machine); specific control downloaded; and cookie information, if available.


The most serious problem, which permeates all analyses of Web clicking behavior, is that the page hits are often stateless (Does not remember what the user did in the last page) . Without surrounding context, a page hit may just be a random isolated event that is difficult to interpret as part of a user session. Perhaps the user linked to this page from some remote Web site and then left the page five seconds later without returning. It is difficult to make much sense out of such an event, so our first goal is to identify and label complete sessions.


The second serious problem is whether we can make any sense out of the remote client's IP address. If the only client identification is the IP address, we cannot learn much. Most Internet users come through an Internet service provider (ISP) that assigns IP addresses dynamically. Thus, remote users will have a different address in a later session than they have at the moment. We can probably track the individual session reliably, but we can't be sure when the user returns to the site in a different session.


We can significantly reduce these problems if our Web server creates cookies on the requesting user's machine. A cookie is a piece of information that the requesting user "agrees" to store and may agree to send to your Web server each time his browser opens one of your pages. A cookie usually does not contain much information, but it can identify the requesting user's computer unambiguously. Furthermore, it provides a way to link page hits across a complete user session. A cookie may contain significant information if the user has voluntarily registered with your Web server and provided other information, such as a true human name and a company affiliation. In such a case, the cookie provides an ID to data you have stored in one of your own databases.


In order to make the raw clickstream data usable in our data webhouse, we need to collect and transform the data so it has a session perspective. This process will be a significant step in the back room. We assume that we have some kind of cookie mechanism that lets us transform our data source into the following format:


• Precise date and time of page hit

• Identity of requesting user (consistent from session to session)

• Session ID

• Page and event requested.


4.2.2     The Fundamental Grain of the Clickstream Data Mart


We now see that each event an individual user invokes in a special session is the grain of our clickstream data mart fact table. Each event is an individual record, and each record is a event on a Web page. Note that the Web server may not notice events within the user interface of a downloaded Web page unless we have programmed the Web page to specifically alert the server when the event occurs. In the back room's extract and transform process, we filter out automatic events and focus on ones more related to page formatting than to user actions. These kinds of filtered events include the download of graphic images, such as GIFs adorning a requested page. So if we have 100,000 user sessions per day on our Web site, and if each session involves an average of eight meaningful events, then we will collect 800,000 records per day.  (Kimball, 2000, p158)


4.2.3     The Dimensions (Step 3) and the Facts (Step 4)


See the below diagram for the clickstream data mart's dimensional model. The dimensions are universal date, universal time, local date, local time, user, page, event, and session. We split the date from the time-of-day because these two time components have quite different descriptors. Date relates to calendar, weekdays, and seasons; and time-of-day relates to the specific spot we are in within a day. Most data warehouse fact tables that track specific times split the date from the time-of-day in this way. The date dimension clearly has a real join to a real dimension table with many textual attributes. The time-of-day dimension may be rather dull as a dimension unless we have some specific intervals during the day to which we are willing to assign names.




(Source :- Kimball, 2000, p 160)


We provide two versions of the date and time -- universal and local -- that let us align clickstream events in absolute time as well as relative to the user's wall clock. The analyst's query tool can perform this alignment, but this extra logic imposes an unreasonable burden on the application. Therefore, we prefer to provide two hard-wired entry points into each event's date/timestamp.


The user dimension should contain some useful information about who the user is, other than just a consistent machine ID. However, this will depend on whether we have coaxed the user into revealing facts about his or her identity.  (Kimball, 2000 (January))


The page dimension is important because it contains the meaningful context that tells the analyst the user's Web site location. Each Web page must contain some simple descriptors identifying the location and type of page. A complete path name is not nearly as interesting as such basic attributes as "Product Info," "Company Info," "Frequently Asked Questions," and "Order Form." A large Web site should have a hierarchical description associated with each page that gives progressively more detail about what the page is. This information needs to be stored in the page dimension and be maintained consistently as we update and modify the Web site. In other words, we have to update the production transaction system (the Web server) responsively to meet the needs of the data webhouse analysts.


Finally, the session dimension is more than just a tag that groups together all the page events that constitute a single user's session. This dimension is also where we label the session and trace its activity. We might characterize sessions as "Searching for Information," "Random Browsing," "Price and Feature Shopping," or "Placing an Order." We may be able to create these tags with simple criteria regarding what the user does during the session, or we may turn over the session record to a full-blown link analysis data mining package. In either case, the result is a set of descriptive tags we can put in the session dimension. The session should also be characterized by what we currently know about the customer, such as "Recent Large Purchaser," "Not Yet a Customer," or "Chronic Product Returner."  (Kimball, 2000 (January))



Our clickstream fact table only contains one fact, and that fact ("time spent") is an estimate. We try to accurately record the length of time the user spent on the page after the last click and before moving on. Because page browsing is basically stateless, we can never be completely sure whether the user has perhaps minimized the window or clicked on an unrelated site. We can only make an accurate estimate of the time spent on the page if we have a following event that is part of the session, but we have to be careful not to interpret long "times spent" too seriously.


4.3        Analyzing the Clickstream Data Mart


This dimensional design lets us perform many powerful queries. It is fairly easy to find the most frequently visited parts of the Web site and identify the most frequent users. We can also correlate pages and users to our more valuable customers because we know who places the order on the Web site.


The good news about this design is that we have successfully established a framework for collecting and analyzing all the clicks on our Web site. The bad news is that we really haven't shed much light on whether we are selling products or Web services. That confusion is quite deeply rooted and is one of the reasons that the Internet revolution is so interesting and important. (Kimball, 2000,  p 162)



5.0       Conclusion



The tremendous rush toward customer relationship management (CRM), e-business, and business intelligence has brought many end-user departments into the computer marketplace as new customers. This demand is almost entirely great news for us data warehouse and data Webhouse implementers.  (Kimball, 2000, June)


The web and the data warehouse  are drawn together like two powerful magnets. The web needs the warehouse for  many of it’s customer centric  functions  and the warehouse is been transformed by the demands of the web to Data Webhouses. Data Webhouses will play a major role in  the cooperate  world in the very near future.


6.0       Reference


·         Kimball. R “Data Webhouse Toolkit” , 2000, John Wiley & Sons



Kimball R.,  “More Than You Hoped For” , 2000 (April) , Intelligent Enterprise, http://www.intelligententerprise.com/000410/scalable.shtml




Kimball R., “Stirring Things  Up” , 1999,  (June), Intelligent Enterprise,  http://www.intelligententerprise.com/992206/warehouse.shtml




Kimball R. “The Data Webhouse Has No Center”, 1998 (July),  Intelligent Enterprise ,  http://www.intelligententerprise.com/991307/warehouse.shtml




Kimball R., “The Special Dimensions Of the Clickstream”, 2000  (January), Intelligent Enterprise,  http://www.intelligententerprise.com/000120/webhouse.shtml




Kimball R.,  “Welcoming the Packaged App” , 1998 (June) , Intelligent Enterprise,