Series 7

Transformer

What is Transformer? Transformer is the Cognos On-Line Analytical Processing Tool (OLAP) that provides a unique view of Enterprise data by consolidating many data sources into one PowerCube. A Transformer model is the blueprint for the data that will be displayed in a PowerCube along with how it will be structured and organized into dimensions. A cube that is created efficiently will allow users to “slice” and “dice” through PowerCubes and allow managers to quickly find trends that will help in making educated business decisions In this workshop, we will take a look at a Transformer model and discover a few common problems. We will discuss how to fix these problems and how to improve the overall design of the cube. The main difference between the Series 7 and Cognos8 versions of Transformer lie in a setting in the trnsfrmr.ini. The entry ‘EnablePCOptimizer’ is enabled by default in the Cognos8 version. PowerCubes created in the Series7 version of Transformer are usuable in Cognos8, though it is recommended to use the ‘pcoptimizer.exe’ to optimize the PowerCube for access by Cognos8. After a PowerCube has been updated with this utility it can still be used with Series 7 applications.

2

Getting Started

• On the Start page, double-click the Transformer icon • Once the application is finished resetting, Transformer will launch and you

may begin the workshop

Building the Transformer Cube You have just taken over a project for a co-worker who has left for a six-month trek in the Andes. The first task that you’re responsible for is to complete the main cube that they have been working on. Unfortunately, the individual who was responsible for this task before you, never got around to documenting the project, and you must now find out what is left to be finished. It is now up to you to fix the model and improve performance.

1. In Transformer, click the Open button on the toolbar, and open the model named c:\techforum\transformer\GO_Tech_Forum.mdl

The first thing that should be done with a new model is to check to see if there are any errors or warnings

2. To check the model, click the Check Model button on the toolbar

3

You can see that there are quite a few errors and warnings in this model; it sure is a good thing that you are on the job! Let’s examine the first error on the list.

What could have happened here is that changes were made to the data source but Transformer wasn’t updated. The columns that Transformer is currently referring to may no longer exist. Make a note that the data source in question is GO Retailers

4. To close the Check Model window click OK Let’s start investigating the Error message: Error: (TR2700) The columns in formulated data source GO Retailers don't match the original source columns.

1. In the Data Sources window, right-click the GO Retailers data source

and select Modify Columns

4

Notice on the Model: side of the dialog that the Display Name of RetailerContactCode has a plus sign + under the Mismatch (M...) column:

This means that the column that Transformer is using in the model cannot be found in the data source. Can you see why it can’t match the two together? If you look closely, you will see that the data source is referring to RETAILERCONTACT_CODE where as the Model is referring to RETAILER_CONTACT_CODE (with an additional underscore)

2. To correct this problem, on the left side pane (Source), select the RETAILERCONTACT_CODE from the Name: column, and from the right side (Model:) select the original name RETAILER_CONTACT_CODE from the Original Name column, and click the Match button so that Transformer can re-establish the link

3. Click OK to close the Column Matching dialogue box, and the Modify

Columns window Our data sources now match up. To investigate the next problem, click the Check Model button again

5

The warning that we’re concerned with now is: Warning: (TR2718) Dimension 'Sales Staff' has no measures associated with it. During PowerCube generation, no data source will be able to provide data for this dimension. This message can be a little overwhelming but it really isn’t too bad. The best way to appreciate what’s happening is to introduce “scope”. Scope simply identifies what dimensions a data source or measure provides information for:

1. Click OK to close the Check Model window 2. Right-click the first data source in the Data Sources window which is GO

Product Line, and select Show Scope Notice that a new Dimension Map window appears with the Products dimension highlighted in yellow. Yellow indicates that a data source or measure provides information for a particular dimension - basically, yellow is good.

3. Use the down-arrow ↓ to scroll through the dimensions in the drop-down list within the Dimension Map

If you scrolled through the dimensions in order from top to bottom, you should now be looking at the scope for the GO Orders data source. You’ll notice that all Dimensions are yellow except for Sales Staff. Since this is the data source upon which all Measures are based (i.e. it has all the transactional data), it is important to have all Dimensions yellow. This implies that the data will be valid for all dimensions. Otherwise, as the warning suggests, we will not have any valid data for the categories in the Sales Staff Dimension Another way to examine scope would have been By Measure. Try this and you will see that one of the dimensions is displayed in green:

1. In the Dimension Map, click the Measures radio button 2. Expand the drop-down list with your mouse and select Actual Revenue 3. Scroll through the list by using the down-arrow ↓ and notice that the Sales

Staff dimension is green for all measures This means that there is no direct tie to the measures and they will be allocated across the Sales Staff dimension. Transformer uses allocation to associate data to levels and dimensions where it does not necessarily have a relationship via the current dataset. This is not what we want so we will have to correct the model.

6

In order to provide scope to Sales Staff, we must ensure that the lowest level in the Sales Staff Dimension exists in the Transactional data source. This is particularly true since the Sales Staff dimension is based on only one data source and the Sales Staff level is designated as unique. Let’s examine the Source of the Sales Staff level:

1. Close the Dimension Map pane previously opened to inspect the scope by clicking the X in top-right corner

2. Click on the Data Sources pane and Expand the GO Sales Staff and the

GO Orders Data Sources. Take a look at them and see what columns you have available to you

3. In the Dimension Map, double-click the lowest level in the Sales Staff

dimension which is called Sales Staff

7

4. On the Source tab, you can see that both the Label and the Source are

using the same field from the data source. In this case it is SalesStaffName

5. Click OK to close the Level – Sales Staff dialog box

• In the Data Sources window, notice that the data source GO Orders has no SalesStaffName field available. This is the source of the problem since Transformer cannot relate the transactional data to the Sales Staff dimension. Note that we do have a SalesStaffCode field

At this point there would be two ways to address the issue:

(1) Modify the GO Orders data source to include the field SalesStaffName

(2) Change the Source of the Sales Staff level to use the existing GO

Orders field called SalesStaffCode

8

Since we know that the GO Sales Staff data source will be able to match each SalesStaffName to the respective SalesStaffCode, it is easier to simply change the Source of the level instead of the GO Orders data source.

6. In the Dimension Map window, double-click on the Sales Staff level and then under Source, click on SalesStaffName

• You will notice that a drop-down arrow appears on the right

7. Click the arrow and from the drop-down select Modify 8. Click on the More>>> button

9. From the list, select SalesStaffCode and then click OK

10. Click OK again to get you back to the Level – Sales Staff window

• You are now using the SalesStaffCode as the Source and you are

using SalesStaffName as the Label

11. Click OK and then click the Check Model button again

9

• You should now see one final Warning and no Error messages

Warning: (TR2751) This model contains one or more cubes with cube optimizations other than Auto-partition. Better performance may be realized if the optimization setting for these cubes is changed to Auto-Partition. It is being highlighted that the cube does not have auto-partitioning enabled. To enable Auto-partitioning, do the following:

1. Click OK to close the Check Model dialog box 2. In the PowerCubes window, double-click GO Tech Cube 3. Click the Processing tab 4. In the Optimization: drop-down list, select Auto-partition

• Notice how the Auto-Partition tab is now active.

5. Click the Auto-Partition tab

Retrieving information from a Cube Retrieving information from a cube is similar to retrieving files from a hard drive. If a drive is broken down into multiple partitions, it’s easier to find a file, and the same works with PowerCubes. The key thing when breaking a cube down into multiple partitions is determining the estimated number of consolidated records. The best place to get this number is from the Transformer log file. In this model, we only have a small set of data.

1. In the Estimated number of consolidated records: field, enter 9999

• When we build the cube later on, we’ll be able to see the different partitions

2. Click OK to Close the PowerCube window

10

This resolves the last of our major errors and warnings, but let’s check the model again.

1. Click the Check Model button again

• This looks a lot better

2. Click OK to Close the window. Now that all of the errors have been resolved, let’s make sure that we can build the cube:

3. Click the Create PowerCubes button on the toolbar Previously we adjusted the Estimated number of consolidated records field with 1,000. We can now check to make sure that there are multiple partitions within the cube.

4. Right-click the GO Tech Cube and select PowerCube Partition Status

We can see that our cube spans 6 partitions: 0 – 5. This will definitely help with performance in larger cubes but we probably won’t notice anything in this cube since it is fairly small

5. Click Close

11

Time Based Partitioned Cubes (TBPC) An important feature of Transformer is Time Based Partitioned Cubes (TBPC). Time based partitioning is similar to cube groups but it takes cube group functionality a step further. TBPC’s are setup to create a cube for each time period. You could set this up so that you could have a cube for each year, or a cube for each quarter, or a cube for each month. Transformer will create a main cube that automatically links to child cubes. The child cubes will be created in a folder within the location that you specify in Transformer. Conceptually, as long as you move all of the files together, the end users won’t see a difference. The advantage to using TBPC’s is that a cube can be accessed on its own or part of a group through the parent cube. Also, updates should be quicker since only the last cube should have to be updated. New to Transformer 7.3 is the ability to perform category counts on the time-based partition cubes. Let’s begin by setting up the measure to do this.

1. Right click in the Measures window and then select Insert Measure … 2. Provide a Measure name of Category Count

3. Click on the Type tab and then check the Category Count radio button.

4. Select Sales Staff for both the Dimension and the Level.

5. Click OK

12

Define the Time-Based partition cubes:

1. Right-click in the PowerCubes window and select Insert PowerCube 2. Name the cube TBPC 3. Click the Cube Group tab 4. Select the check box that reads Enable Time-based Partitioning

• Notice that as soon as you click the checkbox, you are forced to use

the Date dimension

5. Leave the Level: as Year 6. Click OK 7. Right-click on the TBPC cube in the PowerCubes window, and select

Create Selected PowerCube 8. After creating the PowerCube, launch Windows Explorer and navigate to

c:\techforum\transformer

• You can see that Transformer created the parent cube named TBPC.mdc along with a folder called TBPC containing the child cubes

9. From the File menu, select Close to close Windows Explorer, and you

should now be back to Transformer 10. Right-click on the TBPC cube in the PowerCubes window, and select

View PowerCube Now that you are in PowerPlay, you cannot tell that you are viewing a time based partitioned cube. Also, there will be no performance degradation when viewing these cubes. Now it’s time to log out:

• From the File menu click Exit for both PowerPlay and in Transformer

• If prompted, do not save changes

Congratulations…you have completed this workshop!