Data Warehouse
The Commerce Server 2000 Data Warehouse is a set of processes that a system administrator uses to import and maintain data in a combination of a SQL Server database, and an Online Analytical Processing (OLAP) database. The Data Warehouse imports a large amount of site usage data collected from different data sources. This data is gathered from your Web server logs, the Commerce Server databases (profiles, catalogs, campaigns, and transactions) and other data sources that you specify. The Data Warehouse then manages the data in the SQL Server and OLAP databases. These databases are then used to produce reports and to analyze and view population segments.
The Data Warehouse is designed to support robust query and analysis. You use the Analysis modules in Commerce Server Business Desk to analyze the data in the Data Warehouse, for example, to identify user trends or to analyze the effectiveness of a campaign, and then update your site to target content to specific user groups or to sell specific products.
Using the Data Warehouse in Web Site Management
Data Warehouse Structure
Importing Data to the Data Warehouse
Preparing Data for Analysis with OLAP Server
Analyzing Your Data
Using the Data Warehouse in Web Site Management
The Data Warehouse combines data from multiple sources into one common structure, giving the data consistency for producing reports and analyzing and viewing population segments. This is called the Commerce Server Business Analytics System. Business analytics is a way of looking at your Web site data to answer specific questions about the performance of your Web site. You can use the information provided by business analytics to improve your customer service and to target content to users.
Business analytics provides you with specific measurements of your Web site performance, including:
What URL did the user visit before accessing your site (the referring URL)?
What type of advertising works?
What pages are the most popular on your Web site?
Which products sell the best?
Which users buy the most?
You can use these measurements to make changes to your Web site that will increase sales and retain users. For example, assume you display an advertisement to promote a new product. After the ad is displayed for a week, you can run a report to determine whether the ad increased sales of the product. If it did not increase sales, you can update you site, for example, to display a 10 percent discount for the new product, and then after a week, determine whether the discount improved sales.
Data Warehouse Structure
The Data Warehouse has two main parts:
Physical store. A Microsoft SQL Server database that you can query using SQL queries, and an OLAP database that you can use to run reports.
Logical schema. A conceptual model that maps to the data in the physical store.
The following figure shows the relationship between the physical store and the logical schema.
.gif "Ee799251.cs_bd_concepts_dwarchitecture(en-US,CS.10).gif")
Physical Store
The physical store for the Data Warehouse includes one database that you can query using SQL queries. The physical store contains all the data that you have imported from different sources.
Commerce Server automatically builds the physical store for the Data Warehouse in both the SQL Server database and in the OLAP database. The Data Warehouse provides the data necessary for all the Commerce Server reports available in the Analysis modules in Business Desk .
There is no need for you to directly modify the physical store for the Data Warehouse. If you need to extend the Data Warehouse, for example, to encompass third-party data, a site developer can programmatically add the fields you need through the logical schema.
Logical Schema
The logical schema provides an understandable view of the data in the Data Warehouse, and supports an efficient import process. For example, a site developer uses the logical schema to modify the location of data stored in the underlying physical tables. When a site developer writes code to add, update, or delete data in the Data Warehouse, the developer interacts with the logical schema. When Commerce Server accesses data in the Data Warehouse, it accesses the data through the logical schema. Only the site developer needs detailed knowledge of the logical schema.
A logical schema includes the following:
Class. A logical collection of data members. For example, the RegisteredUser class contains data members describing a registered user.
Data member. A structure that stores a piece of data. For example, the E-mail data member of the RegisteredUser class stores the e-mail address for a registered user.
Relation. A connection between two classes in a parent-child relationship. This relationship defines the number of instances of each class, and it provides the mechanism for sharing data members between classes. For example, RegisteredUser is a parent to the child class Request. There can be many requests for one registered user.
The logical schema uses classes, data members, relations, and other data structures to map data in the physical store.
Importing Data to the Data Warehouse
The data that populates the Data Warehouse typically comes from multiple data sources: Web server logs, Commerce Server databases, and other data sources that you specify. The following figure shows the sources for operational data, and how the data might be used to support tasks run from Business Desk.
.gif "Ee799251.cs_bd_concepts_dwprocess(en-US,CS.10).gif")
Because the Data Warehouse is not part of your run-time environment, a system administrator must determine how frequently to import the operational data into the Data Warehouse. For example, you can set up the Data Warehouse so that it automatically imports new data every day or every week. The frequency with which you will need to import data depends on the amount of new data collected every day in your operational data sources. Commerce Server includes custom Data Transformation Service (DTS) tasks that simplify the importing of data into the Data Warehouse. These DTS tasks import data that is used with the reports available from Business Desk.
Even though the operational data can be imported from different types of databases—or from storage media that are not databases—all of the data is structured in a consistent manner after it is gathered into the Data Warehouse. For example, you might have one data source in which the first and last name of a user are stored in the same field, and another in which the first and last names are stored in separate fields. When this data is imported into the Data Warehouse, it is automatically structured to be consistent, thus enabling your analysis activities.
Preparing Data for Analysis with OLAP Server
After data is imported into the Data Warehouse SQL Server database, it must be prepared for analysis so business managers can run reports against it. To prepare data for reporting, the system administrator runs a DTS task that exports a selected subset of data from the SQL Server database to the OLAP database. In the OLAP database, the data is stored in multidimensional cubes.
By storing data in OLAP cubes, instead of in relational tables in SQL Server, the Data Warehouse can retrieve data for reporting purposes more quickly. The data can be retrieved from the cubes faster because it is aggregated. That is, data that belongs together is already associated so it is easier to retrieve than searching an entire relational database for the smaller parts. For example, using OLAP server you can run a report that lists users who visit your site based on the time of their visit and on the ASP page that they access first. It would be extremely difficult to run such a report against a large SQL Server database.
In multidimensional cubes, data is grouped in two kinds of structures:
Measures. The numeric values that are analyzed.
Dimensions. A business entity, such as color, size, product, or time. For example, you would use the color dimension to contrast how many red products and blue products were sold, the size dimension to contrast how many large and small products were sold.
It is the relationship between the dimension (for example, color) and measure (for example, number of products sold) structures that provides the basis for your reports about user activity.
The following figure illustrates the dimensions and measures in a report.
.gif "Ee799251.cs_bd_concepts_report(en-US,CS.10).gif")
Analyzing Your Data
To analyze data about user activity on your site, you use the Analysis modules in Business Desk. You can use the Analysis modules to run reports against the Data Warehouse, or to view and analyze Segment models, which identify segments of the user population visiting your site.
Reports
Commerce Server provides two types of reports that you can use to analyze user data:
Dynamic reports are created at runtime. Each time the report is run, it gathers the most recent data in the Data Warehouse. Only the report definition, which remains the same over time, is stored. Commerce Server does not save completed dynamic reports; however, you can export dynamic report results to Microsoft® Excel and store them there. For information about exporting the results of a dynamic report to Microsoft Excel, see Exporting a Dynamic Report.
Static reports are run immediately upon request, and then stored, with the data, in Completed Reports. The reports appear in a browser window in HTML format. You can export static report results to the List Manager module, and then use the list in a direct mail campaign. You can send these reports to others using e-mail, post them on your Web site, and edit them in other applications. For example, using Microsoft Internet Explorer, you can export the report into a Word document, and then edit it.
Commerce Server 2000 includes several reports that you can run from Business Desk, such as the Executive Summary Report, the Products by Quantity Sold Report, and the Hits by User Report. You can use one or more of these reports, or you can customize a report to retrieve other data. For a list of reports included with Commerce Server, see Commerce Server Reports.
Segment Models
Segment models identify groups of users who share similar characteristics. You can use the Commerce Server Predictor resource to search the data in the Data Warehouse to generate Segment models. You analyze Segment models using the Segment Viewer module in Business Desk. For conceptual information about Segment models, see Prediction and Data Mining.