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.
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.
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.
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—
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.
5. Click and drag the Year dimension to the columns, above Product Account.
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).
Nested column dimensions in a Slice of the Product Cube
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.
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.
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
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.
Margin and Product slices in Cascading windows
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.
Slice of 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).
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.
6. Click on the Margin slice to activate it—show 2000 figures.
7. Press F9.
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.
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.
The Excel Integration section fully summarizes Slice features and functions that were not discussed in the course of these exercises.