Data Warehouse

Published on December 2016 | Categories: Documents | Downloads: 52 | Comments: 0 | Views: 663
of 44
Download PDF   Embed   Report

Comments

Content

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 =

CreateObject("ADODB.Record.2.5")

oRec.Open "Class/OTCStore", oCn, 3, 2 '3-Read/Write Cursor, 2-CreateOverwrite oRec("IsPersistent") = 1 oRec("IsActive") = 1 oRec("ClassDefName") = "OTCStore" oRec("SourceDefName") = "test_source" oRec("GeneratePartitionDef") = 1 oRec("GenerateTableDef") = 1 oRec("GenerateIdentity") = 1

'Identity Member

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

'------ Create member - OTCAmount ----set oRec = CreateObject("ADODB.Record.2.5") oRec.Open "Member/" & "OTCStore" & "/" & "OTCAmount", oCn,3,2 '3-Read/Write Cursor, 2-CreateOverwrite oRec("MemberDefName") = "OTCAmount" oRec("GenerateColumnDef") = 1 oRec("IsPrimaryKey") = 0 oRec("IsMultiValued") = 0 oRec("DefaultValueAsStr") = "0" oRec("IsUniqueKey") = 0 oRec("IsDimension") = 0 oRec("TypeName") = "CURRENCY" oRec("__Commit") = 1 oRec.Fields.Update Wscript.Echo "OTCAmount Member created..." oRec.Close Set oRec = Nothing '----------------------------------------------------------------

25

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

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 Wscript.Echo "Relation to Products created..." oRec.Close Set oRec = Nothing '----------------------------------------------------------------

26

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

'----------------------------------------------------------------

28

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

= createobject("ADODB.Connection") = createobject("ADODB.Command")

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

set oRec =

CreateObject("ADODB.Record.2.5")

oRec.Open "Class/OTCStore", oCn, 3, 2 '3-Read/Write Cursor, 2-CreateOverwrite oRec("IsPersistent") = 1 oRec("IsActive") = 1 oRec("ClassDefName") = "OTCStore" oRec("SourceDefName") = "test_source" oRec("GeneratePartitionDef") = 1 oRec("GenerateTableDef") = 1 oRec("GenerateIdentity") = 1

'Identity Member

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 '---------------------------------------------------------------'***3. Create new Members '------ Create member - OTCAmount ----set oRec = CreateObject("ADODB.Record.2.5") oRec.Open "Member/" & "OTCStore" & "/" & "OTCAmount", oCn,3,2 '3-Read/Write Cursor, 2-CreateOverwrite oRec("MemberDefName") = "OTCAmount" oRec("GenerateColumnDef") = 1 oRec("IsPrimaryKey") = 0 oRec("IsMultiValued") = 0 oRec("DefaultValueAsStr") = "0" oRec("IsUniqueKey") = 0 oRec("IsDimension") = 0 oRec("TypeName") = "CURRENCY" oRec("__Commit") = 1 oRec.Fields.Update

37

Extending the Commerce Server 2000 Data Warehouse and Analytics

Wscript.Echo "OTCAmount Member created..." oRec.Close Set oRec = Nothing

'------ Create member - OTCQuantity -----

2

set oRec = CreateObject("ADODB.Record.2.5") oRec.Open "Member/" & "OTCStore" & "/" & "OTCQuantity", oCn, 3, '3-Read/Write Cursor, 2-CreateOverwrite oRec("MemberDefName") = "OTCQuantity" oRec("GenerateColumnDef") = 1 oRec("IsPrimaryKey") = 0 oRec("IsMultiValued") = 0 oRec("DefaultValueAsStr") = "0" oRec("IsUniqueKey") = 0 oRec("IsDimension") = 0 oRec("IsMeasure") = 1 oRec("TypeName") = "LONG" oRec("__Commit") = 1 oRec.Fields.Update

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

Declare @ReportName [nvarchar] (128) Select @ReportName = 'AllSalesReport' -- New report name Declare @Description [nvarchar] (128) Select @Description = 'All Sales Combined Report (OTC + OnLine) Declare @ReportCategory [nvarchar] (128) Select @ReportCategory = 'Custom Report' -- Report category Declare @ReportCreator [nvarchar] (128) Select @ReportCreator = 'Sanjeev' -- Created by Declare @CubeName [nvarchar] (128) Select @CubeName = 'AllSalesCube' -- Cube name as a data source ------------------------- Report Definition -----------------------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>' )

44

Sponsor Documents

Or use your account on DocShare.tips

Hide

Forgot your password?

Or register your new account on DocShare.tips

Hide

Lost your password? Please enter your email address. You will receive a link to create a new password.

Back to log-in

Close