Transposing Columns and Rows

Transpose columns and rows to transition data from various sources to an Essbase database. Transpose the columns before you start to work with the data in Integration Server.

Consider the following example in which you create multiple Essbase measures (Init_Sales, Subsequent_Sales, and Return_Sales) from a single database column (SALESTYPE).

The SALESACTUALS table contains the following columns:

Table 6. SALESACTUALS Table

PRODID	MKTID	TIMEID	INITSALES	SALESTYPE	PRODID
100	2	01-01- 2000	100.00	Sales	100
100	2	01-02-2000	10.00	Returns	100
100	2	01-03-2000	50.00	Subsequent	100
100	2	01-04-2000	20.00	Returns	100
100	2	01-01-2000	100.00	Sales	100
:	:	:	:	:	:

You want to create the Essbase outline in the hierarchy illustrated in Figure 4, Accounts Hierarchy:

Figure 4. Accounts Hierarchy

You want each SALESTYPE value to form the Essbase member: SALES to form the Init_Sales member, RETURNS to form the Return_Sales member, and SUBSEQUENT to form the Subsequent_Sales member. Create a view or table that transposes row values into column values, for example:

Table 7. View of SALESACTUALS Table with Transposed Columns

PRODID	MKTID	TIMEID	INITSALES	RETURNS	SUBSEQUENT
100	2	01-01-2000	100.00	0.00	0.00
100	2	01-02-2000	0.00	10.00	0.00
100	2	01-03-2000	0.00	0.00	50.00
100	2	01-04-2000	0.00	20.00	0.00
:	:	:	:	:	:

The following example of Oracle SQL defines the view shown in Table 7:

)
Create view SalesActuals_vw as
    (Select ProdId, MktId, TimeId,
         decode (SalesType, 'Sales', Sales, 0) "InitSales",
         decode (SalesType, 'Returns', Sales, 0) "Returns",
         decode (SalesType, 'Subsequent', Sales, 0)"Subsequent"
            from SalesActuals
)

After defining a new table or view, use it to create an OLAP model.