Entering Data in a Slice

 

So far, you have seen PowerOLAP’s remarkable flexibility in organizing and displaying data within a Slice. In PowerOLAP®, you can also enter Fact Data in a Slice, much as you would enter data into a spreadsheet. A Slice does not store the Fact Data as such; rather, it is stored in the Cube. A Slice is simply the view into the Cube, like a window. If you enter or edit data in a Slice and then delete the Slice, the data is not deleted from the Cube.

Enter Data into Slice Cell/   
Recalculate to See Results

To enter data into a Cube via a Slice, simply type the data into a cell within the Slice grid.

Enter data as follows:

1.   Open a Slice,    
e.g., the Sales Account by Month slice.

2.   Click at the intersection of January and Quantity in the example Slice.

3.   Key in data, the number <5> in the example.

4.   Press Enter.

5.   Repeat the above steps for February with the number <10> if you are using the Sales Account by Month slice. (Don’t forget to hit Enter.)

6.   Press F9 to recalculate: all appropriate Aggregate members will be updated to reflect the changed value you entered.

 Tip

To set the calculation mode: select Edit, Options and click the radio button next to Automatic or Manual in the calculation mode frame.

NOTE: For most users, Manual is the preferable calculation mode. This will save time (particularly in large databases) otherwise spent continually recalculating while you rearrange Slice views or enter data.

The Slice, note above, has been reformatted to show individual Months, Quarterly totals, and Total Year on the top row. Note that—along with January and February— other cells have been updated (QTR1, Total Year, Revenue and Cost of Sales).

 Notes

About Entering Data in Aggregate Cells: PowerOLAP® will not normally accept data entry into Aggregate member cells. These cells are updated (i.e., calculated) by the PowerOLAP aggregation engine. For example, in the Sales Account by Month slice, QTR1 is an Aggregate member that consolidates the Detail members January, February and March. You can enter data into January, February and March, but not into QTR1 – QTR 4 or Total Year.

It is however important to know, that PowerOLAP® will allow you to write a formula that does supersede an Aggregate calculation, so that you will be able to enter numbers in an Aggregate cell where a model logically requires it—this is one of many powerful customization options of the product.

Leave this Slice open and proceed with the next exercise.

NOTE: If you close the database, you can save changes to the Slice when you exit (Click on YES).

The next hint concerns saving Fact Data changes like the one you made in the example file. Following that is a practice business scenario involving entering Fact Data in a Slice.

 Notes

Any changes you make to Fact Data within a Slice are saved only once you have saved the database, via the File, Save Database command.

If you do not wish to save Fact Data changes, you can enter data and test the impact on other data—then, if you close the database without saving it, no data will change in the database.

Using Slices for Business Purposes—   
Practice Scenario

In the following exercise you will practice entering data in new Slices, using the Slice functions discussed so far. You will begin to use the Avon Trading Company database in a real-world scenario to calculate Cost of Sales, Revenue and Margin. You will do this by accessing and entering data in new Slices from the Sales, Product and Margin cubes. In the course of demonstrating PowerOLAP’s power and flexibility through Slice functions, this exercise will also reveal the architecture of the Cubes in this database and the logic of their design for analysis and reporting. We shall see the interrelated nature of the Cubes in the Avon Trading Company database and will show how data flows through the Cubes.

1.   In the PowerOLAP ribbon, select Slice tab, New Slice command.
The New Slice dialog box appears.

2.   Select the Products cube.

3.   Click OK.

Slice of the Products cube

4.   Press F9 to bring in the data values.

Note that we are looking at Unit Cost and Unit Price data from 1998 (Year: 1998) for all products. We would like to input Unit Cost and Unit Price data for 2001, based on 2000 data, so we need to alter our Slice to see 2000 and 2001 numbers for Unit Cost and Unit Price.

5.   Click and drag the Year dimension to the columns, above Product Account.

Now we have nested our Dimensions in columns.

We do not need to see the 1998 and 1999 data, though we do want to retain the historical numbers for 2000 to guide us in our 2001 data input.

6.   Right-click on the column label for year and select “Select Year Members“.
This will bring up the Edit Slice dialog box for Year dimension.

7.   Click on the Hierarchy tab.
Only the years 2000 through 2002 are included in the Hierarchy (though 1998 and 1999 are under the Members tab).

8.   Clear the Members in the Slice Content list box (use the Clear All button), then select 2000 and 2001, and drag them to the Slice Content list box.

9.   Click OK button (green checkmark).

The updated Slice now shows data only for 2000 and 2001.

Nested column dimensions in a Slice of the Product Cube

We would also like to limit our data input to a smaller range of products—a handful of items at the top of the list.

Subset Creation

We will now create a Subset of products, which can then be used for the configuration of any subsequent Slice.

1.   In the PowerOLAP ribbon, select Model tab, Dimension command.
The Dimensions dialog box appears.

2.   Select Products and then click on the Subsets button.
The Edit Subsets dialog box appears.

3.   Type a <Subset name>—e.g., MyProducts  and click Add.
The Edit Slice dialog box appears.

4.   In the Member list, select individual products you want to see — you can, for example, chose all products up through and including Tops Icecream by highlighting them.

5.   Drag the selection to the list on the right.

6.   Click the OK button (green checkmark).

7.   Click OK in the Edit Subsets dialog box, then OK in the Dimensions dialog box. This now returns you to your slice view.

8.   Next, in the Slice, right-click on the row label for  Product;         
once again, the Edit Slice dialog box appears.

9.   In the Members tab, click on the subset drop-down menu and select MyProducts, as shown:

10. Click on the Replace current list with selected subset button.
The Slice Content list box is now replaced with your selected members.

11. Click OK button (green checkmark) and update the Slice by pressing F9.

The Slice is now customized to meets our needs:

Enter Data

Back in the Slice, click on the cell at the intersection of 2001 Unit Cost and Aunt Re’s Chicken. We want to calculate Cost of Sales, Revenue and Margin, which we know are based on Unit Cost and Unit Price and are, themselves, recorded in the Productcube. 2001 Unit Costs are based on a 10-cent increase over 2000 numbers for every product. Thus, in this cell, we will enter $12.33.

1.   Type 12.33 in the cell.

2.   Press Enter.

3.   Continue adding data, 10 cents higher than the 2000 Unit Cost, as above, to the products’ 2001 Unit Cost Column.

The other element of our calculations is based on an increase to Unit Prices. We need to see how our Revenues look per product with a rise in the Unit Prices to the next $.99 level up from the 2000 price ($21.29 thus becomes $21.99).

4.   Enter <21.99> for 2001 Unit Price for Aunt Re’s Chicken.

5.   Continue down the list of products, rounding the 2000 Unit Price to the next $.99, for the 2001 Unit Price.

6.   You may fill in the 2001 Unit Cost numbers for the rest of your Product list as well. The resulting Slice should appear as follows:

Entering data in a Slice for Revenue projections

We have entered our numbers for 2001 Unit Cost and Price and would like to see the effect on Revenue. Since Revenue figures are accessible through a different Cube, we shall now open another Slice to continue our exercise. (The Product cube has revealed to us through its dimensionality of Product, Product Accounts, and Year, that it does not contain Revenue information).

7.   In the PowerOLAP ribbon, select Slice tab, New Slice command and choose the Margin cube from the New Slice dialog box.

8.   Press F9 to bring the data into the slice.

The initial Slice view of the Margin Cube fills the PowerOLAP application window, covering the Product slice and the Sales Account by Month slice (if open). Note that you can configure the Slices to share the application window in a Cascade or Tile setup. The following shows a chosen Cascade window:

Margin and Product slices in Cascading windows

The initial Margin slice view shows 1998 Actual data for USA and Aunt Re’s Chicken, with Month in Columns and Margin Account in Rows.

Change Slice View

We want to alter that Slice to show us the same array of Products in rows, and Years (2000 and 2001) in columns, from the Product slice. We want to see the Margin Account dimension nested in the Year dimension in columns:

1.   Rearrange the Dimensions of the Margin slice so that Product is in rows, Margin Account is nested in Year in columns, and the other Dimensions—Version, Region, and Month— are Pages.

2.   Choose subsets of Members in the Year and Product dimensions to mirror the Year and Product dimensions from the Product slice.

3.   Right-click on the column label for  Margin Account dimension to access the Edit Slice dialog and re-arrange the members in the Slice Content list box so that they are listed in this order: Quantity, Unit Cost, Unit Price, Cost of Sales, Revenue, Margin, Margin %.

4.   In the cell indicating Quantity for 2001 (you may have to scroll forward) for Aunt Re’s Chicken, enter the value 70—press F9 to update.

The resulting slice looks as follows (2000 columns are hidden to the left):

Slice of Margin cube

Please observe the following very important points about the Margin cube:

  • The data entered in the Product slice for 2001 Unit Cost and Unit Price has been added into the database and is now reflected in this Slice.
  • Scroll so you can see 2000 accounts. Notice our 2000 Quantity for Aunt Re’s Chicken, 5, comes from our entry in the Sales cube slice Sales Account by Month. We can see this entry here because we are looking at January data (we would see the February entry if we chose the member February of Month).
  • The value 70 entered at the intersection of 2001 Quantity for Aunt Re’s Chicken drives the Cost of Sales, Revenue and Margin/Margin % numbers.
  • Quantity is the only column of data allowing data input, and only for 2001 data (2000 Quantity data is driven by entry into the Sales cube through, for example the Sales Account by Month slice). All other figures are either calculations governed by formulas, or are figures brought in from another Cube (i.e., Unit Cost and Unit Price from the Product cube).

You may save/name your Margin and Product slices if desired.

Update and See Values

Now, to further demonstrate PowerOLAP’s capability to update Cubes—nearly instantaneously:

1.   Minimize the Product slice and Sales Account by Month and open a New Slice from the Sales cube.

2.   Rearrange the slice with Product in rows, Sales Account in columns, and Months in Pages. Limit the products to MyProducts so that the Slice looks like this:

3.   Arrange the Sales and Margin slices into a tile view from the Home tab so that both Slices are visible.

4.   Enter Quantity data for some of the products with zero values.

5.   Press F9.

Notice how the Revenue and Cost of Sales columns are updated with calculated values driven from the new Quantity numbers.

6.   Click on the Margin slice to activate it—show 2000 figures.

7.   Press F9.

Notice that the Quantity values from the Sales cube are brought in to the Margin cube now and that the formulas for Cost of Sales, Revenue, Margin and Margin % are all updated!

We have demonstrated the dynamic link between the Product and Margin cubes (Unit Price and Unit Cost come from Product), and between the Sales and Margin cubes (Actual 2000 Quantity numbers come from Sales), and back from Margin to Sales (calculations based on Unit Cost and Unit Price for Cost of Sales, Revenue and Margin). Moreover, we have seen PowerOLAP’s power and flexibility in data modeling, with its ease in data entry, instant updating, Slice rearranging and Subset creation—allowing us to view data exactly how we need to see it.

Now that we have linked data for 2000 Quantity between the Sales cube and the Margin cube, we can finish the Revenue calculations we began when we entered data into the Product cube for 2001 Unit Cost and Unit Price at the beginning of this exercise.

8.   Save the slice view for Margin cube as Product by Account & Year.

9.   In the Margin slice (Product by Account & Year), scroll over so that 2001 Margin Accounts are visible.

Quantity values are the only values we may contribute in this view, since the other values are “read only”—governed by formulas and brought in from other Cubes.

10. Enter data in the Quantity column for several products. (The value of 70 for Aunt Re’s Chicken was already entered). Press F9 to update.

Observe the values returned in the calculation columns, providing us with much insight into our products’ performance so far in 2001.

In our model, the Pies, Coffee and Ice cream did particularly well for Avon Trading Company in January 2001 in the USA—go figure!

The Excel Integration section fully summarizes Slice features and functions that were not discussed in the course of these exercises.