Preparing Relational Data for Use with Xchange

Preparing Relational Data for Use with Xchange

To help you build a cube using Xchange, you will prepare your relational data, whether the data resides in a standard database application (e.g., accounting), a custom-tailored application (e.g., internal systems), or a data warehouse.

Using SQL views

Xchange works with SQL Views, which are user-defined “virtual tables” in your underlying database. Xchange references these SQL views in the same way it references tables, provided Allow View Tables feature has been selected within the Create Cube from Relational Source dialog.

createcuberelsourceallowviewtables.png

Figure 1.    Allow View Tables Selected

By selecting Allow View Tables, you can use SQL Views for dimension creation in the same manner that you use existing tables in the database. (SQL Views are created and maintained in your RDBMS—they are not created via Xchange.)

Creating additional tables

You can also facilitate Dimension creation by creating your own separate auxiliary tables for static data in your underlying database. Static data exists, for example, in a table like Months (no new Months will be added). The use of such auxiliary tables, with Member names that appear only once, can improve performance—Xchange can refer to the created auxiliary table rather than reviewing each line of each record to determine if an entry is unique and, if unique, create a Member.

Consider the following example OrderID table, from which can build a meaningful five-dimensional Cube.

Table 1.       OrderID Table

OrderID

Products

Units

Region

Months

Year

5677

ProdID1

4

USA

May

2009

5678

ProdID2

2

USA

May

2009

5679

ProdID1

8

Canada

June

2009

5680

ProdID3

3

USA

July

2009

5681

ProdID1

6

Mexico

July

2009

5682

ProdID3

10

Belgium

August

2009

The rows are records of Units sold of Products, with Region, Month, and Year indicated; Units would supply the figures for the Measures dimension. Let us assume that there is also a Products table in this database, which contains the list of products the company sells. To create the Products dimension, you would use Define Link dialog in Xchange to reach the Products table. (You could perhaps even build a six-dimensional Cube, reaching Customers through the OrderID field.)

Suppose that there is no equivalent Region table. In order to create a Region dimension, you would have to, therefore, check the Allow Duplicates feature, and proceed to buildRegion from the above table—the central Measures table.

Another option exists: add a Region table to your database. In it, you could list each country where you presently (and perhaps intend to) do business. You could also create ahierarchy of Regions—assuming this were meaningful for your data analysis purposes.

Below is an example of a table you might create as the basis of a Regions dimension—rather than using a field in your Measures table. By creating this table, you could add significant Regions hierarchy levels that would allow analysis and reporting by continent and company division (AcmeWest and AcmeAsia).

Table 2.       Example Regions Dimensions

Country

HierarchLevel1

HierarchLevel2

USA

North America

AcmeWest

Canada

North America

AcmeWest

Mexico

North America

AcmeWest

Belgium

Europe

AcmeWest

France

Europe

AcmeWest

Italy

Europe

AcmeWest

China

Asia

AcmeAsia

Singapore

Asia

AcmeAsia

Japan

Asia

AcmeAsia

Creating a time dimension

It is very likely that you will want to include some kind of Months and/or Years and/or Period dimension in your database, since it is relevant to analyze data over time (thus, generally we will call it a Time dimension). The demonstration in this guide, based on the Northwind database, did not include such a Dimension.

In the table shown in the previous section, there is a Months field that could be used as the basis for a Months dimension—you could create it from the Measures table or create your own Months table (as done with Regions, above), which would link to the central table.

The general strategy for creating a Time dimension is to first look for the key field in your central Measures table that has the relevant time indicator (it might be OrderDate, for example, rather than ShippingDate). If the field does not exist in the Measures table, it may exist in a table that can be linked to it. For example, in the Northwind database, the OrderID table (which is the Measures table) links to the Orders table (which contains several date fields).

It is possible that you will also want to create a View or additional table that can parse the data as it exists in a field that indicates Time. As an example, consider a Field that appears as follows:

102000

102000

112000

122000

Knowing your database, you would recognize this as a concatenation of Period (first two digits) and Year (following 4 digits). You could create a View that separates the two, then add a table for Period, in which ‘10’ has the Alias ‘October’ and another table that includes Years—i.e., 2007, 2008, 2009, 20010, etc. Through the use of View Tables and User-defined tables, you could then define links in Xchange and arrive at a useful Time dimension—i.e., Period, Month, Year, etc.

Using the DATETIME Field

It is possible, in Xchange, to create a Time dimension with Detail Members that are Days. You must consider this option carefully, since the dimension will have Members for each day a transaction occurs in your underlying database. Ask yourself: "Are Days meaningful to me as a way of analyzing my data?"

In the process of Dimension creation, if you select a field in the Define Members dialog that Xchange recognizes as the type DATETIME, after pressing Next, the following dialog will appear:

dateformatdialog.png

Figure 2.    Date Format Dialog

The following options are available in the dialog:

Month Format:

Shows a drop-down box that allows you to select the preferred Month format. The table below lists those formats:

Table 3.       Month Formats

Format

Sample

M/D/YY

8/12/09

MM/DD/YYYY

08/12/2009

D/M/YY

12/8/09

DD/MM/YYYY

12/08/2009

DD-Mon-YYYY

12-Aug-2009

Month Day, Year

August 12, 2009

Include Time:

Check this box to include the time in standard am/pm or military time. Once you select the desired format, you can click Back and check the Partial Fields dialog to the view values.

You may want to be careful as this could create a large dimension – especially if you include TIME, a Member will be created for every transaction.

Additional considerations for data preparation

  • The best way to build a Cube is to use a star schema wherever possible to Define Links. Alternatively, use a snowflake schema, but try to minimize the number of levels from the Measures table.

  • When you define a link out from the Measures table, the Members will come from the linked table (not the Measures table). Errors will occur if data exists in the Measures table that does not exist in the linked table. (For example, if a product appears in the Measures table that does not appear in the Products table). On the other hand, if Members exist in the linked tables that do not appear in the Measures table, you can potentially see many Members in proCube for which there is no data. (For example, a Products table with many discontinued products.) In this case, you might use a Conditional Retrieval statement to ensure that only “relevant” Members are brought into the Cube.

  • It may be preferable to perform calculations in a table within the source database rather that the Cube, so that the data is immediately available for viewing in proCube.

  • Finally, if you receive an Error Report, determine whether the error can be addressed and you can thereby prepare your data in such a way that it will be easier to build the Cube(s) you need for planning, analysis and reporting from the database.

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk