Extending the Commerce Server 2000 Data Warehouse/Analytics March 2001
1
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication. This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, AS TO THE INFORMATION IN THIS DOCUMENT. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation. Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property. The example companies, organizations, products, people and events depicted herein are fictitious. No association with any real company, organization, product, person or event is intended or should be inferred.
2001 Microsoft Corporation. All rights reserved. Microsoft, ActiveX, FoxPro, FrontPage, Jscript, Outlook, Visual Basic, Visual C++, Visual InterDev, Visual J++, Visual Studio,
\u00a9
Win32, Windows, and Windows NT are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. The names of actual companies and products mentioned herein may be the trademarks of their respective owners.
2
Extending the Commerce Server 2000 Data Warehouse/Analytics
Contents Extending the Commerce Server 2000 Data Warehouse/Analytics...........................................................1 Contents...........................................................................................................................................................3 Objective.......................................................................................................................................................... 5 Introduction and Background.......................................................................................................................6
The Commerce Server Data Warehouse....................................................................................................6 Commerce Server Analytics\u2014Basic Architecture and Technical Components........................................8 The Need to Extend the Data Warehouse and Analytics...........................................................................9 Extending Data Warehouse Analytics\u2014Brief Overview........................................................................10 Background Overview of Technical Concepts............................................................................................ 11
Technical Structure of the Data Warehouse............................................................................................. 11 Overview of the Data Warehouse Schema\u2014Logical vs. Physical................................................... 11 Logical Schema................................................................................................................................ 11 Physical Schema/Data Store............................................................................................................. 12 The OLE DB Provider for Commerce Server (As the Data Warehouse Manager)................................. 12 Microsoft OLE DB Technology....................................................................................................... 13 OLE DB Provider for Commerce Server\u2014Overview..................................................................... 13 OLE DB Provider for Commerce Server\u2014Major Features and Benefits........................................ 15 OLE DB Provider for Commerce Server\u2014Limitations................................................................... 16 Technical Elements............................................................................................................................ ......16 Classes, Members, and Keys............................................................................................................ 16 Class and Member Relations............................................................................................................ 17 Single Instance Creation................................................................................................................... 17 ADO and VBScript.......................................................................................................................... 18 Sample Scenario Definition and Design...................................................................................................... 19
Overview of the Sample Scenario...........................................................................................................19 The Required End Result for Analysis....................................................................................................19 The Technical Scenario for Data Integration...........................................................................................20 Data Integration Design Details..............................................................................................................20 Development Steps........................................................................................................................................ 23
Create a New Class.................................................................................................................................. 24 Add New Members.................................................................................................................................. 25 Add Relations.......................................................................................................................................... 26 Import the External Data into the New Class.......................................................................................... 27 Create a SQL View to Join the Data........................................................................................................ 29 Create a New Cube.................................................................................................................................. 30 Create a New Report............................................................................................................................... 31 Run the New Report and the Chart from Business Desk........................................................................ 32 The New Report............................................................................................................................... 33 The New Chart................................................................................................................................. 34
3
Extending the Commerce Server 2000 Data Warehouse/Analytics Summary....................................................................................................................................................... 35 Appendices..................................................................................................................................................... 36
Appendix A: Sample Code—Creating the Schema Objects....................................................................36 Appendix B: Sample Code—Importing Data Using Single Instance Row Insert...................................40 Appendix C: Sample Code—SQL View to Merge the Online and OTC Data ........................................42 Appendix D: Sample Code—SQL or XML Code to Create a New Report Definition ...........................43
4
Extending the Commerce Server 2000 Data Warehouse and Analytics
Objective
This document provides an in-depth discussion regarding the extensibility features of the Commerce Server Data Warehouse and Analytics module. The purpose of this document is to:
•
Provide a high-level overview of the technology architecture involved in extending the Data Warehouse and Analytics to be able to handle the customization requirements for analysis and reporting.
•
Provide an in-depth analysis of the technical components and their interrelationships and integration.
•
Suggest a comprehensive step-by-step methodology to complete the project of extending and customizing the Data Warehouse and Analytics from start to finish.
•
Provide general technical design guidance for planning data integration into the Data Warehouse.
•
Provide sample programs for each step in the development process and drill down into the code for extending the schema, importing the data, creating the SQL objects, the cube design, and generating a report and a chart.
5
Extending the Commerce Server 2000 Data Warehouse and Analytics
Introduction and Background
This section provides background regarding the Commerce Server Data Warehouse and Analytics, and a general introduction to the concepts behind extending them. The following diagram provides a conceptual overview of the Data Warehouse.
Conceptual Overview
Interactive Analysis
Web Usage Users Transactions Catalog
Business Data Warehouse
Targeted Mail User Profile Management
Advertisements
Prediction Clustering
Other
Other
The Commerce Server Data Warehouse
The Commerce Server Data Warehouse is a central repository of the relevant electronic commerce and click-stream information. The Data Warehouse is populated through a set of processes that import and maintain data in a combination of a Microsoft SQL Server™ database and an OLAP database. The Data Warehouse imports a large amount of site usage data collected from different data sources. This data is gathered from the 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. The Analysis modules in Commerce Server Business Desk are used 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. This “Closed Loop” feature can provide effective feedback to the site.
6
Extending the Commerce Server 2000 Data Warehouse and Analytics
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 structure 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, target content to users, improve site performance, and so on. Business analytics provides you with specific measurements of the performance of your Web site, such as: •
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 your site, for example, to display a 10 percent discount for the new product. After a week, you can determine whether the discount improved sales. The following diagram provides an overview of the various processes in the Data Warehouse.
DW Process Flow Overview Event Data
Administration
Commerce events
Real Time Events
ADO Commerce OLE DB Provider
IIS WebTV Other Events Non--Event Data Event Data Commerce data
Commerce
s data & events e g a Web logs k c a P IP Resolution S T Delete Data D
Custom Task
CS Data Warehouse
k s e D s s e n i s u B r e v r e S e c r e m m o C
OnOn-line line Services Business Reports VB Office 2K ASP
SQL Server
OLAP Server
Other Data ADO / OLE DB
Other
HTML/XML CDO
IE
7
Extending the Commerce Server 2000 Data Warehouse and Analytics
Commerce Server Analytics—Basic Architecture and Technical Components
The following diagram provides an overview of the key components of Commerce Server Analytics.
Web Analysis Architecture Business Desk
Report Definitions
Reports in IE Dynamic Static
IIS Report Object
PivotList Chart
HTML Table Parameters
Data Source
SQL Server
MS OLAP Server List Manager Export Obj
Direct Mail
User Manager
The key components of Commerce Server Analytics are as follows: •
SQL Server hosts the relational database portion of the Commerce Server Data Warehouse. The Report table in this database contains the report definitions of the standard and user-defined reports.
•
Microsoft OLAP Server (Analysis Services 2000) hosts the OLAP portion of the Data Warehouse.
•
Microsoft Internet Explorer is a delivery tool that serves the Data Warehouse reports to the end user. Commerce Server Business Desk is the primary interface for the end user to perform reporting-related activities.
•
Dynamic reports are displayed in Internet Explorer through the Office Web Component (OWC) using the PivotList ActiveX® control, which connects to the specific OLAP cube that contains the data for the reports. The OWC enables the user to dynamically pivot, slice/dice, and drill down the contents of the reports.
•
Static reports are displayed in Internet Explorer by using HTML, by means of the Report Renderer component.
•
The contents of a report can be viewed in the graphical chart format by using the Chart button.
•
The export object can be used to export the data to List Manager or to Microsoft Excel by means of a single button on the displayed report.
8
Extending the Commerce Server 2000 Data Warehouse and Analytics
The Need to Extend the Data Warehouse and Analytics
The Commerce Server Data Warehouse provides comprehensive reporting capabilities regarding the commerce-centric metrics and the Web-centric metrics. This information is very useful from a business perspective and from the perspective of the IT professionals who manage the functionality, performance, and availability of the Commerce Server site. However, every business has its own unique processes that generate specific information that is critical for its operation. Every business also has unique reporting needs. A business with an online e-commerce site probably also has other sales channels and related manufacturing and distribution processes. The following list gives some examples of information that can be very useful and can provide value-added analysis and richer reporting:
•
Financial information from an ERP system
•
Customer information from a CRM system
•
Marketing trends information from a Sales system
•
Tax-related information from an IRS reporting system
To perform meaningful analysis and comprehensive reporting, it becomes critical for the business to integrate all the relevant information into a one-stop shop. Because the role of a Data Warehouse is to provide a single, reliable source of all business information, the Commerce Server Data Warehouse based on the powerful SQL Server and OLAP Analysis Services technologies can provide such a single central repository and can be used as a reliable source for analysis and reporting. The following sections provide an in-depth analysis of how to import and integrate the required information into the Data Warehouse by extending the schema and the related objects.
9
Extending the Commerce Server 2000 Data Warehouse and Analytics
Extending Data Warehouse Analytics—Brief Overview
The following steps provide a conceptual overview of the process of extending the Commerce Server Data Warehouse and Analytics: 1. Define the additional metrics and reporting needs. 2. Accordingly, define the external data acquisition and integration needs. 3. Finalize the logical model for the integration of new data structures into the Data Warehouse schema. 4. Extend the Data Warehouse schema by means of the OLE DB Provider for Commerce Server. 5. Import the external data into the Data Warehouse by means of the OLE DB Provider for Commerce Server. 6. Create a SQL View to generate a Fact table with the integrated data. 7. Create a new OLAP cube by using this Fact table in Analysis Services. 8. Create the new report(s) in the Data Warehouse. 9. Run and view the new report or reports by means of the Commerce Server Business Desk (Internet Explorer). Each of these steps is discussed in greater detail later in this paper.
10
Extending the Commerce Server 2000 Data Warehouse and Analytics
Background Overview of Technical Concepts Technical Structure of the Data Warehouse
The following diagram describes the overall structure of the Commerce Server Data Warehouse and the technical nature of its components. The key notion here is that the OLE DB Provider for Commerce Server is the critical bridging component between the Data Warehouse and the external processes and events that interact with the Data Warehouse. Another important facet of the Data Warehouse is that it uses SQL Server and the OLAP Analysis Services as the storage engines for hosting the relational and the multi-dimensional data structures. DW Technical Architecture Commerce
Scripts
Web Log
Events
ADO
Import
Campaigns Users Transactions
OLE DB Provider for Commerce Server
CS Data Warehouse
SQL SQL Server Serve
OLAP OLA Server Serve P
r
r
Overview of the Data Warehouse Schema—Logical vs. Physical
The Commerce Server Data Warehouse schema defines the contents of the Data Warehouse. The schema has two distinct layers: Logical and Physical. The Logical layer serves as the metadata for the Data Warehouse. The Physical schema holds all the persisted objects (from the Logical schema) in the SQL data store. The following sections describe the details regarding the two layers of the Data Warehouse schema.
Logical Schema
The logical schema provides an understandable view of the data in the Commerce Server Data Warehouse and supports an efficient import process. For example, a site developer uses the logical schema to modify the location of the 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.
11
Extending the Commerce Server 2000 Data Warehouse and Analytics
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.
•
Physical Schema/Data Store
The physical store for the Commerce Server Data Warehouse includes one database that you can query by 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 Commerce Server 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.
The OLE DB Provider for Commerce Server (As the Data Warehouse Manager)
This section describes the OLE DB Provider for Commerce Server and its role as the Commerce Server Data Warehouse Manager. After a brief review of OLE DB technology, this section provides an in-depth discussion of the features and functionality of the OLE DB Provider for Commerce Server.
12
Extending the Commerce Server 2000 Data Warehouse and Analytics
Microsoft OLE DB Technology
Microsoft developed the OLE DB technology to provide a layer of abstraction between the user application and the underlying data sources, which could be a Relational Data Base Management System (RDBMS), a non-relational DBMS, an indexed sequential access method (ISAM) database, or just a plan flat file. OLE DB is an open specification designed to build on the success of ODBC by providing an open standard for accessing all kinds of data throughout the enterprise. OLE DB is a core technology supporting universal data access. Whereas ODBC was created to access relational databases, OLE DB is designed for the relational and non-relational information sources, such as mail stores, text and graphical data for the Web, directory services, and IMS and VSAM data stored on the mainframes. OLE DB components consist of data providers, which expose data; data consumers, which use data; and service components, which process and transport data (for example, query processors and cursor engines). These components are designed to integrate smoothly to help OLE DB component vendors quickly bring highquality OLE DB components to market. OLE DB includes a bridge to ODBC to enable continued support for the broad range of ODBC relational database drivers available today. OLE DB is a set of Component Object Model (COM) interfaces that provide applications with uniform access to data stored in diverse information sources and that also provide the ability to implement additional database services. These interfaces support the amount of DBMS functionality appropriate to the data store, enabling it to share its data. There are two types of OLE DB applications: Consumers and Providers. A Consumer can be any application that uses, or consumes, OLE DB interfaces. For example, a Microsoft Visual C++® application that uses OLE DB interfaces to connect to a database server is an OLE DB consumer. The ActiveX Data Objects (ADO) object model that uses OLE DB interfaces is an OLE DB consumer. Any application that uses the ADO object model uses OLE DB interfaces indirectly through the ADO objects. An OLE DB Provider for Commerce Server implements OLE DB interfaces; therefore, an OLE DB Provider for Commerce Server allows consumers to access data in a uniform way through a known set of documented interfaces. In a sense, an OLE DB Provider for Commerce Server is similar to an ODBC driver that provides a uniform mechanism for accessing relational data. OLE DB Providers for Commerce Server provide a mechanism not only for relational data but also for non-relational types of data. Furthermore, OLE DB Providers for Commerce Server are built on top of COM interfaces that allow more flexibility, whereas ODBC drivers build on top of a C API specification.
OLE DB Provider for Commerce Server—Overview
The OLE DB Provider for Commerce Server is a versatile multi-purpose tool that serves not only as a SQL data storage engine, but also as the Commerce Server Data Warehouse Manager. As a typical SQL storage engine, the OLE DB Provider for Commerce Server optimizes the data updates and retrievals from the physical database. As the Data Warehouse Manager, the OLE DB Provider for Commerce Server manages the logical schema, including the classes, members, class relationships (for example, 1->M, M->M, 1->M/Inheritance), the surrogate keys (binary 6-byte IDs), and so on. In summary, the
13
Extending the Commerce Server 2000 Data Warehouse and Analytics
OLE DB Provider for Commerce Server is the Data Warehouse Manager that controls the entire logical schema in addition to the persistent physical storage of the Data Warehouse entities.
14
Extending the Commerce Server 2000 Data Warehouse and Analytics
An OLE DB Provider for Commerce Server is a COM component that accepts calls to the OLE DB API and processes each request against the data source. The OLE DB Provider for Commerce Server provides two distinct paths of functionality. The Fastload property passed in the connection string determines which of these functionalities is used. The Data Warehouse concentrates on bulk insertion and the ability of the OLE DB Provider for Commerce Server to automatically instantiate the parent classes when a child class is created.
OLE DB Provider for Commerce Server
ADO/OLEDB DB 2.5 2.5 ADO/OLE interface Lazy, batched Lazy, batched saves saves using bulk insert Distributed Di stributed Summarizations Optimizes data data for Optimizes for server side processing
OLE DB Interface Async Async post
Data queue Summarizatio Normalization and packing Async Async post
Package queue Send to Storage
OLE DB Provider for Commerce Server—Major Features and Benefits
The OLE DB Provider for Commerce Server not only provides the basic functionality of storing the data intelligently, but also acts as the Metadata Administrator and the Data Warehouse Manager to facilitate:
•
Management of the metadata catalog for the Data Warehouse.
•
Management of the logical entities and their mutual relationships.
•
Generation of Data Warehouse Dimensions and Facts.
•
Creation and control of the DW surrogate keys (6-byte IDs).
•
Persistence of the logical entities as SQL objects in the physical store.
15
Extending the Commerce Server 2000 Data Warehouse and Analytics
OLE DB Provider for Commerce Server—Limitations
Although the OLE DB Provider for Commerce Server is a complex and versatile feature of Commerce Server 2000, its advanced functionality is not fully developed in this initial version. The following are the current key limitations of the OLE DB Provider for Commerce Server:
•
The schema objects (for example, class and member) can be created but they cannot be deleted.
•
The schema objects (for example, class and member) can be created but they cannot be modified. For example, renaming a schema object or changing its data type is not supported.
•
Instance selection is not supported. For example, syntax such as “Select * from Foo” cannot be used to browse the data. The only way to browse the data is by using the physically persisted tables in the SQL database.
•
Because the OLE DB Provider for Commerce Server manages the data relationships, Referential Integrity in the physical SQL database is turned off by default, but it can be turned on.
Multi-valued members and many-to-many relationships extend the schema automatically by adding relevant table or tables. This can significantly degrade performance. It is expected that there will be an effort to eliminate these limitations to make the OLE DB Provider for Commerce Server more robust and versatile in future releases of Commerce Server.
•
Technical Elements Classes, Members, and Keys
Classes and members are the building blocks of the Commerce Server Data Warehouse schema. A class is a logical collection of data members. A member stores a piece of data. A class contains an arbitrary number of data members that define the logical scope of the class. New data members can be added at any time after the class is created. Conceptually, a class is analogous to a SQL table and a member is analogous to a column. A key is a data member or set of data members that uniquely identifies instances of a class. For example, the key for the Orde rFormLineItems class is a combination of the private lin eit em_id data member and the inherited OrderForm_Id and Ord erG ro u p _ I d data members. This means that instances of an Orde rFormLineItems class include a unique identifier for the line item plus unique identifiers for the Orde rFormHeader and OrderGroup classes.
16
Extending the Commerce Server 2000 Data Warehouse and Analytics
Class and Member Relations
The relations between classes and members define the various types of data relationships that are represented in the schema. A relation connects two classes in a parent/child association. This relation defines the number of instances of each class, also called the cardinality of the relation. In the Commerce Server Data Warehouse, nearly all relations are one-to-many. This means that zero or more child classes may exist for each parent class. For example, an individual order form has precisely one instance of the OrderFormHeader class. The order form may have multiple line items represented by multiple instances of the OrderFormLineItems class associated with the single OrderFormHeader class instance. A relation provides the mechanism for sharing data members between classes. A parent class may have zero or more data members defined as inheritable. An inheritable data member is copied to any child class when the relation is created. This is a logical inheritance defined by the schema, and is not inheritance from a C++ point of view. For example, the Orde rFormLineItems class inherits three data members from its parent class, Orde rFormHeader. One of these data members, OrderForm_Id, is used to identify the order form containing the line item. The following types of relations are supported in the OLE DB Provider for Commerce Server schema: 1. One to Many 2. One to Many with Inheritance 3. Many to Many
Single Instance Creation
An instance of a class represents the user data imported into the Commerce Server Data Warehouse. Conceptually, an instance of a class is analogous to a row of a table. After creating the class and the members (and the relations), the Class can be populated with data by creating new instances of the class. This is accomplished by using ADO to make a OLE DB Provider for Commerce Server call by means of any application language, such as Microsoft Visual Basic® or Visual Basic, Scripting Edition (VBScript). This is described in detail later in this paper, along with the sample code.
17
Extending the Commerce Server 2000 Data Warehouse and Analytics
ADO and VBScript
The Microsoft ActiveX Data Objects (ADO) technology is a high-level, languageindependent set of object-based data access interfaces optimized for data application. ADO enables client applications to access and manipulate data from a database server through an OLE DB Provider for Commerce Server. An effective method of communicating with the OLE DB Provider for Commerce Server is to use ADO along with a procedural programming language that can use the ADO API. The sample solution scenario provided in this white paper uses the VBScript language, because VBScript code can be easily transported and executed on virtually any computer running Microsoft Windows® without the need to use an advanced compiler. For more formal development and better performance, it might be better to use ADO by means of the compiler-based languages, such as Visual Basic or Visual C++.
18
Extending the Commerce Server 2000 Data Warehouse and Analytics
Sample Scenario Definition and Design
This section defines the sample scenario and the related design that will be used to elaborate on the extensibility concepts, and to design and develop the specific solution.
Overview of the Sample Scenario
In this sample scenario, a retail company sells books and CDs and has both over-thecounter (OTC) and online presence. In other words, the company has a chain of “brick and mortar” stores that sell the books and CDs. The company also sells the same merchandise through its Web site, which became operational over a year ago. The problem that needs to be solved is that the company's management wants to determine the effectiveness of the online store by analyzing the relationship between the sales from the OTC stores and the sales from the online store. The side-by-side comparison metrics between these two types of stores would provide the management with key information for strategic planning and future investing. The intended solution is to create the infrastructure to import the transactional sales data from the company’s ERP system and integrate it with the Commerce Server Data Warehouse that is a part of the online store implemented with Commerce Server 2000. This integrated data will enable us to create the data structures for facilitating the required analysis by comparing online sales/quantity and the OTC sales/quantity over the time and the product dimensions.
The Required End Result for Analysis
The required end result in the sample scenario is a report and chart showing comparisons of the sales amounts and quantities between the online sales and the OTC sales, across the Time dimension and the Product dimension with the ability drill-down into the deeper levels of the Time dimension (for example, year, quarter, month, and so on) and the Product dimension to find the specifics regarding a product group/category and SKU’s. The high-level comparison of the sales information in the chart form will facilitate trend analysis to determine whether there is clear business trend that implies a consistent growth in online sales and a negative pattern in OTC sales.
19
Extending the Commerce Server 2000 Data Warehouse and Analytics
The Technical Scenario for Data Integration
Now that the business scenario and the related requirements, have been clearly established, the next step is to define the technical scenario to implement the solution. From a technical perspective, the transactional data needs to flow from the ERP system containing the OTC sales data into the Commerce Server Data Warehouse, where it can be integrated with the online sales data to formulate the OLAP cube for analysis reporting. We will create a staging database named StageDB to accommodate the imported data from the ERP system before loading the data into the Data Warehouse. The following diagram provides a pictorial overview of the data flow from the ERP system into StageDB and the Data Warehouse, and then into the OLAP cube through a SQL View, and finally to Commerce Server Business Desk for the dynamic report delivery.
Data Flow Overview New Class (OTCStore OTCStore))
OrderFormLineItems Table
External Data
Commerce
StageDB
DW
Staging Database (from ERP)
View
AllSalesCube (new cube)
AllSalesReport (custom report)
Data Integration Design Details
The following list summarizes the existing and new data structures to be used in constructing the desired data integration solution:
•
The existing OrderFormLineItems table in the Commerce Server Data Warehouse will provide the online sales data.
•
The following columns from the OrderFormLineItems table in the Data Warehouse will be used to fetch the online transaction data: •
OrderFormLineItems.productsid
•
OrderFormLineItems.cy_lineitem_total
•
OrderFormLineItems.quantity
20
Extending the Commerce Server 2000 Data Warehouse and Analytics •
A new class named OTCStore will be created to host the external data for the OTC sales from the brick-and-mortar stores.
21
Extending the Commerce Server 2000 Data Warehouse and Analytics •
The following four new members will be created to host the external data: •
OTC_ProductNameCat (for logical key)
•
OTC_Date
•
OTC_Amount
•
OTC_Quantity
•
Two new relations for the new OTCStore class will be created to the Products class and the Date class in the Data Warehouse. This is necessary to establish Referential Integrity.
•
The single instance insert method of the OLE DB Provider for Commerce Server will be used to import the data from the staging database (StageDB). This will automatically generate the “Surrogate Keys” (implemented as binary 6-byte id’s in the Commerce DW) for the newly created instances (rows).
•
These binary 6-byte IDs (ProductsID and DateID) will be used as the as the dimension keys (surrogate keys) when the new cube with the integrated measures is created.
•
The following four columns will be used as the measures for the Fact table (as a SQL View) used to create the new cube: •
OrderFormLineItems.cy_lineitem_total
•
OrderFormLineItems.quantity
•
OTCStore.OTC_Amount
•
OTCStore.OTC_Quantity
22
Extending the Commerce Server 2000 Data Warehouse and Analytics
Development Steps
The following diagram provides a pictorial overview of the development steps involved in extending the Commerce Server Data Warehouse schema and the related objects for the analytics.
Development Steps Overview
Create a new class
Create a new report
Create a new Cube
Add new members to the class
Add Relations 10110010011 01000101100
Create a view to Join data
Import data into the new class
10110010111 10110010111
23
Extending the Commerce Server 2000 Data Warehouse and Analytics
Create a New Class
In this step, we extend the Commerce Server Data Warehouse schema by creating a new Class. The following VBScript/ADO code provides the detailed syntax as an illustrative example. Please note that the sample code snippet on this page is provided to highlight the nature of the specific code. The complete working code is provided in the appendices later in this white paper.
'--------------------------------------------------------------'*** Create a new Class Dim oRec set oRec =
oRec("ClassType" ) = 2 'for Fact Class ‘ (0-general, 1-dimension, 2-fact, 3-aggregate) oRec("__Commit") = 1 oRec.Fields.Update Wscript.Echo "New Class OTCStore created" oRec.close Set oRec = Nothing '----------------------------------------------------------------
24
Extending the Commerce Server 2000 Data Warehouse and Analytics
Add New Members
In this step, we create the new Members in the new Class created in the previous step. The following VBScript/ADO code provides the detailed syntax as an illustrative example. Please note that the sample code snippet on this page is provided to highlight the nature of the specific code. The complete working code is provided in the Appendices later in the paper. '---------------------------------------------------------------'*** Create new Members
Extending the Commerce Server 2000 Data Warehouse and Analytics
Add Relations
In this step, we create the new Relations that establish the relationships between the new Class and the two dimension Classes – Date and Pro duct. The following VBScript/ADO code provides the detailed syntax as an illustrative example. Please note that the sample code snippet on this page is provided to highlight the nature of the specific code. The complete working code is provided in the Appendices later in this white paper. '---------------------------------------------------------------'***4. Create new Relation to Products Dim OTCStoreToProductsRelation
Extending the Commerce Server 2000 Data Warehouse and Analytics
Import the External Data into the New Class
In this step, we import the external data into the new Class by using the single instance creation method. The following VBScript or ADO code provides the detailed syntax as an illustrative example. Please note that the sample code snippet on this page is provided to highlight the nature of the specific code. The complete working code is provided in the Appendices later in this white paper. '---------------------------------------------------------------'----- Set OLE DB Provider connection ----strURL = "URL=mscop://InProcConnect/Server=<server1>:database= retail_dw:catalog=DWSChema:user=sa:password=:FASTLOAD=True"
'----- Set SQL StageDB connection ----strSQLConn = "PROVIDER=SQLOLEDB;SERVER=<server1>;database=stageDB;user id=sa;password=;" dim objConn dim objRS dim fld dim objRec set objConn = CreateObject("ADODB.Connection") set objRec = CreateObject("ADODB.Record") set objRS = CreateObject("ADODB.Recordset") objConn.Open strURL ‘Open the OLE DB Provider connection objRS.open "select * from " & "stgOTCTable", strSQLConn while objRS.EOF <> True objRec.Open "Instance/" & "OTCStore", objConn, 3, 67108864 objRec("ProductNameCat") = objRS.Fields("stg_ProductNameCat") objRec("DTimeStamp") = objRS.Fields("stg_Date") objRec("OTCAmount") = objRS.Fields("stg_Amount") objRec("OTCQuantity") = objRS.Fields("stg_Quantity") objRec("__Commit") =1 objRec.fields.update ' Added one record ‘ Not flushed till connection is closed objRec.Close objRS.movenext Wend set objRec = Nothing set objrs = Nothing set objConn = nothing
27
Extending the Commerce Server 2000 Data Warehouse and Analytics
Extending the Commerce Server 2000 Data Warehouse and Analytics
Create a SQL View to Join the Data
In this step, we create the SQL View that combines the data from the online store and the OTC store. The following SQL code provides the detailed syntax that can be reused.
---------------------------------------------------------------------CREATE VIEW AllSalesView AS SELECT otc.productsid, otc.dateid, otc.otcamount as Amount, otc.otcquantity as Quantity, 'OTC' as StoreType FROM otcstore otc UNION ALL SELECT o.productsid, oh.dateid, o.cy_lineitem_total, o.quantity, 'OnLine' as StoreType FROM orderformlineitems o JOIN OrderFormHeader oh ON oh.ordergroupid = o.ordergroupid ---------------------------------------------------------------------
29
Extending the Commerce Server 2000 Data Warehouse and Analytics
Create a New Cube
In this step, we create a new cube by using the SQL View generated in the previous step as the Fact table. The new cube can be generated by using the Products and Date shared dimensions, and the Amount and Quantity facts. The following figure provides an overview of the specifications for the cube.
Please note that this new custom cube can be processed automatically as a part of the “Report Preparation” DTS task. The SQL table CubeProcInfo in the Commerce Server Data Warehouse database (for example, Retail_DW) contains the list of cubes that the “Report Preparation” DTS task will process. If the name of the new cube (AllSalesCube) is added to the CubeProcInfo table by inserting a new row, this cube will be processed every time the “Report Preparation” DTS task is executed.
30
Extending the Commerce Server 2000 Data Warehouse and Analytics
Create a New Report
In this step, we create the new report by inserting a row into the Report table of the Commerce Server Data Warehouse database. The following SQL/XML code provides the detailed syntax as an illustrative example. Please note that the sample code snippet on this page is provided to highlight the nature of the specific code. The complete working code is provided in the Appendices later in this white paper.
---------------------------------------------------------------------- Sample code only. Fully working code including the variable -- declarations is provided in the Appendix. Insert Into [dbo].[Report] ([DisplayName], [Description], [ReportType], [Category], [CreatedBy], [XMLData]) Values (@ReportName, @Description, @Dynamic_MDX, @ReportCategory, @ReportCreator, '<xml xmlns:x=''urn:schemas-microsoftcom:office:excel''> <x:PivotTable> <x:OWCVersion>9.0.0.3821</x:OWCVersion> <x:DisplayFieldList/> <x:FieldListTop>357</x:FieldListTop> <x:FieldListLeft>837</x:FieldListLeft> <x:FieldListBottom>726</x:FieldListBottom> <x:FieldListRight>1024</x:FieldListRight> <x:CacheDetails/> <x:ConnectionString>Provider=MSOLAP;Data Source=;Initial Catalog=; Client Cache Size=25;Auto Synch Period=10000</x:ConnectionString> <x:DataMember>' + @CubeName + '</x:DataMember> </x:PivotTable> </xml>' ) ---------------------------------------------------------------------
31
Extending the Commerce Server 2000 Data Warehouse and Analytics
Run the New Report and the Chart from Business Desk
In this step, we run the new report and the chart from Commerce Server Business Desk. The following figure provides an overview of the portfolio of standard reports in Business Desk.
The List of Reports in the Commerce BizDesk
32
Extending the Commerce Server 2000 Data Warehouse and Analytics
The New Report
The following figure provides an overview of the new report displayed in Internet Explorer. Please note that when the report is opened for the first time, it will be empty and specific data items will need to be selected from the Pivot List (for rows and columns) that will define the report format and content.
33
Extending the Commerce Server 2000 Data Warehouse and Analytics
The New Chart
The following diagram provides an overview of a sample chart that can be displayed by clicking the Chart button through the OWC in the Web browser (Internet Explorer). In the context of the sample scenario, this sample chart indicates that there is a clear trend of increasing online sales. This information can be very useful for the company's management to plan the strategic investments for the future.
34
Extending the Commerce Server 2000 Data Warehouse and Analytics
Summary
The following list briefly summarizes the key features and functionality of Commerce Server 2000 Data Warehouse and Analytics that this paper has covered:
•
Commerce Server 2000 uses an extensible schema based on the OLE DB technology that can be used for new subject area integration (for example, ERP, CRM, and so on).
•
The OLE DB Provider for Commerce Server serves as the Data Warehouse Manager and automates many important functions of the Data Warehouse.
•
The OLE DB Provider for Commerce Server supports efficient and easy data loading.
•
The schema extensions can be used for creating more effective OLAP cubes with integrated information.
The schema extensions can be used for creating extensible, flexible, and powerful custom reports tailored to ever-changing business and customer requirements. Several key Microsoft independent software vendors (ISVs) have created value-added solutions for additional metrics and reports—based on the Commerce Server Data Warehouse—that provide more effective analytics for Commerce Server.
•
35
Extending the Commerce Server 2000 Data Warehouse and Analytics
Appendices Appendix A: Sample Code—Creating the Schema Objects '---------------------------------------------------------------' This program provides the sample code for extending the ' Commerce Server Data Warehouse schema by means of the ' OLE DB Provider for Commerce Server and ‘ the ADO API using the VBScript language. ' This program creates a new Class, adds new Members, and then ' adds new Relations for the new Class to the existing Classes in ' the Data Warehouse schema. '---------------------------------------------------------------'***1. Set connections '----- Set Provider connection ----'ADO 2.5 connect string strURL = "URL=mscop://InProcConnect/Server=<server1>:database=retail_dw:catal og=DWSChema:user=sa:password=:FASTLOAD=True" Dim oCn Dim oCmd set oCn set oCmd
oCn.Open strURL 'Open the connection to the Commerce OLEDB Provider Set oCmd.ActiveConnection = oCn '------------------------------------------------' Turn on schema change mode '-----------------------------------------------oCmd.CommandText = "SchemaMode=1" oCmd.Execute Wscript.Echo "Schema mode set..." '--------------------------------------------------------------'***2. Create a new Class Dim oRec
36
Extending the Commerce Server 2000 Data Warehouse and Analytics
Wscript.Echo "OTCQuantity Member created..." oRec.Close Set oRec = Nothing '---------------------------------------------------------------'***4. Create new Relation to Products Dim OTCStoreToProductsRelation set oRec = CreateObject("ADODB.Record") oRec.Open "Relation/" & "OTCStore-Products-Relation",oCn,3,2 '3-Read/Write Cursor, 2-CreateOverwrite oRec("ParentClassName") = "Products" oRec("ParentClasskey") = "ProductsKey" oRec("ChildClassName") = "OTCStore" oRec("RelType") = 2 ' 1-M Relation oRec("__Commit") = 1 oRec.Fields.Update
38
Extending the Commerce Server 2000 Data Warehouse and Analytics
Wscript.Echo "Relation to Products created..." oRec.Close Set oRec = Nothing '---------------------------------------------------------------'***5. Create new Relation for Date Dim - OTCStoretoDateRelation set oRec = CreateObject("ADODB.Record") oRec.Open "Relation/" & "OTCStore-Date-Relation", oCn, 3, 2 '3-Read/Write Cursor, 2-CreateOverwrite oRec("ParentClassName") = "Date" oRec("ParentClasskey") = "DateKey" oRec("ChildClassName") = "OTCStore" oRec("RelType") = 2 ' 1-M Relation oRec("__Commit") = 1 oRec.Fields.Update Wscript.Echo "Relation to Date created..." oRec.Close Set oRec = Nothing '------------------------------------------------' Commit Schema '------------------------------------------------oCmd.CommandText = "CommitSchema" oCmd.Execute '------------------------------------------------' Turn off schema change mode '-----------------------------------------------oCmd.CommandText = "SchemaMode=0" oCmd.Execute set oCn = nothing 'data flush here for fastload set oCmd = nothing '---------------------------------------------------------------
39
Extending the Commerce Server 2000 Data Warehouse and Analytics
Appendix B: Sample Code—Importing Data Using Single Instance Row Insert '---------------------------------------------------------------'***1. Set connections '----- Set OLE DB Provider connection ----'ADO 2.5 connect string strURL = "URL=mscop://InProcConnect/Server=<server1>:database=retail_dw:catal og=DWSChema:user=sa:password=:FASTLOAD=True" '----- Set SQL StageDB connection ----'SQLOLEDB conn strSQLConn = "PROVIDER=SQLOLEDB;SERVER=<server1>;database=stageDB;user id=sa;password=;" dim dim dim dim
objConn objRS fld objRec
set objConn = CreateObject("ADODB.Connection") set objRec = CreateObject("ADODB.Record") set objRS = CreateObject("ADODB.Recordset") objConn.Open strURL
‘Open the OLE DB Provider connection
objRS.open "select * from
" & "stgOTCTable", strSQLConn
'--- create instance data by adding data to record fields which match the stage data recordset field names while objRS.EOF <> True ' Wscript.Echo "Inserting Rec ..." objRec.Open "Instance/" & "OTCStore", objConn, 3, 67108864 'adReadWrite , adcreateoverwrite objRec("ProductNameCat") = objRS.Fields("stg_ProductNameCat") objRec("DTimeStamp") = objRS.Fields("stg_Date") objRec("OTCAmount") = objRS.Fields("stg_Amount") objRec("OTCQuantity") = objRS.Fields("stg_Quantity")
40
Extending the Commerce Server 2000 Data Warehouse and Analytics
objRec("__Commit") =1 objRec.fields.update ' Added one record ‘ Not flushed till connection is closed objRec.Close objRS.movenext Wend set objRec = Nothing set objrs = Nothing set objConn = nothing '----------------------------------------------------------------
41
Extending the Commerce Server 2000 Data Warehouse and Analytics
Appendix C: Sample Code—SQL View to Merge the Online and OTC Data ---------------------------------------------------------------------CREATE VIEW AllSalesView AS SELECT otc.productsid, otc.dateid, otc.otcamount as Amount, otc.otcquantity as Quantity, 'OTC' as StoreType FROM otcstore otc UNION ALL SELECT o.productsid, oh.dateid, o.cy_lineitem_total, o.quantity, 'OnLine' as StoreType FROM orderformlineitems o JOIN OrderFormHeader oh ON oh.ordergroupid = o.ordergroupid ---------------------------------------------------------------------
42
Extending the Commerce Server 2000 Data Warehouse and Analytics
Appendix D: Sample Code—SQL or XML Code to Create a New Report Definition ----------------------------------------------------------------------- Use this script to create a new dynamic OLAP report in the -- Commerce Server Data Warehouse -- More detailed instructions are in the SDK ------------------------------------ To Create the Report Definition: ------------------------------------ Replace the Report Name, Description, Report Category, -- and Report Creator strings below. -- Replace 'AllSalesCube' with the desired cube name -- as the datasource. -- Run this script on your Data Warehouse (for example, Retail_DW). -- Now you have created the report definition for the new report. ---------------------- To Run the Report: ---------------------- In Commerce Server Business Desk, go to the Analysis reports module. -- If the new report does not appear, execute a find for 'All Reports' -- to refresh the list. -- Select the new report and run it. -- Select dimensions and measures from PivotTable Field List to create the desired view. -- Press the Save button to save the new report. ------------------------- Report Constants ------------------------- ReportType Declare @Dynamic_MDX tinyint Select @Dynamic_MDX = 1 ------------------------- Report Variables ------------------------
43
Extending the Commerce Server 2000 Data Warehouse and Analytics