Entering Data in a Slice

Entering Data into a Slice

 

proCube lets you enter factdata in a slice view, much as you would enter data into a spreadsheet. A slice does not store the factdata ; rather, factdata is stored in the actual cube. A slice is simply a view into the cube—an electronic window. If you enter or edit data in a slice and then delete the slice, the data is not deleted from the cube.

Turning off constraints

To allow data to be entered into a slice, constraints must be turned off. By default, zero suppression is set to Hide Zeros. You will need to change zero suppression to Show Zeros to enable the addition of data. Go to Suppressing_zeros for more information.

To turn off zero suppression:

  1. Display the Report tab.

  2. Under the View group, change Hide Zeros to Show Zeros.

Entering data and recalculating

Entering data into a cell within the slice grid also enters that data into a cube.

To enter data into a slice:

  1. Open the Sales Accounts by Months Slice.

  2. Click in the cell intersection of “January” and “Quantity.”

  3. Enter 5 and press Enter.

  4. Repeat the above steps for February with the number 10.

  5. Press F9 to recalculate. Leave this slice open and proceed with the next exercise.

All appropriate aggregate members will be updated to reflect these new values.

enteringdataslice.png

Figure 1.    Entering Data in a Slice

Note that when you press F9, January and February, as well as other cells (QTR1, Total Year, Revenue and Cost of Sales) get updated.

proCube will not accept data entry into Aggregate member  cells or cells governed by Cube Formulas . These Members are updated, or calculated, by proCube. For example, in the Sales Accounts by Months 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. Entering data into an Aggregate member cell would be like overwriting a formula in a worksheet. Likewise, Revenue and Cost of Sales numbers are governed by a formula and cannot be keyed in.

Using slices for business purposes — practice scenario

In the following exercise, you will practice entering data in new Slices using the Slice functions previously discussed. Using the Acme Trading Company database in a real-world scenario, you will calculate Cost of Sales, Revenue and Margin by accessing and entering data in new Slices from the Sales, Product and Margin cubes. In the course of demonstrating proCube’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 will see the interrelated nature of the Cubes in the Acme Trading Company database and show how data flows through the cubes.

To begin this practice scenario:

  1. From the Report tab, click New Slice.

  2. In the New Slice dialog, select the Products cube.

  3. Click OK.

  4. Press F9 to bring in the data values.

We are looking at Unit Cost and Unit Price data from 2007 for all products. We would like to input Unit Cost and Unit Price data for 2010, based on 2009 data, so we need to alter our Slice to see 2008 and 2009 numbers forUnit Cost and Unit Price.

  1. Drag-and-drop the Year dimension to the columns list box, above Product Account.

Now we have nested our Dimensions in columns.

Subset Creation

We do not need to see the 2007 and 2008 data, though we do want to retain the historical numbers for 2009 to guide us in our 2010 data input. This narrowed-down display of Dimension members in a Slice is, as you have learned, a subset.

  1. Double-click the Year dimension to open the Edit Slice dialog.

Only the years 2007 through 2011 are included in the Hierarchy (though 2007 and 2008 are under the Members tab).

  1. Select 2009 and 2010, send them to the Slice Content list , and click OK.

We have now narrowed down our Slice to show data only for 2008 and 2009:

nestedcolumndimensions.png

Figure 2.    Nested Column Dimensions in a Product Slice

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

  1. Double-click the Product Dimension in the Rows list. The Edit Slice dialog opens.

  2. From the right pane, highlight all individual products from Aunt Re's Chicken to Tops Icecream by holding down the control button and highlighting your selection.

  3. Click Invert Selection button and delete the rest of the members from the list, as shown in Product Members for the Practice Scenario.

productspracticescenario.png

Figure 3.    Product Members for the Practice Scenario

  1. Click OK and then press F9 to update the slice.

We now have a narrowed down slice view that meets our needs as shown in Products for the Practice Scenario.

productsslicepracticescenario.png

Figure 4.    Products for the Practice Scenario

Notice that the Dimension list boxes in the figure above now signify that we are viewing subsets of both the Product and Year dimensions.

Saving Subsets

After creating subsets, we can save them in the Edit Slice Dialog via the Members tab. Saving subsets is particularly useful if you are working from a large list of members and often need to see the same defined subsets of Members. Subsets can also be used in cube formulas, greatly reducing the number of formulas that need to be written. Subsets also make it easy to maintain large member lists and simplify the granting of security privileges to large groups of people.

Let’s save our Product subset for future use:

  1. Double click the Product dimension in the Slice to bring up the Edit Slice Dialog.

  2. From the Members tab, confirm that our subset of chosen products appears on the right in the content list box.

  3. From the Edit Slice Dialog's toolbar, click Save. The Subset Name dialog opens.

  4. In the Subset Name: field, enter MyProducts and click OK.

subsetnamemyproducts.png

Figure 5.    Subset Name dialog

  1. From the Members Tab drop-down, open the MyProducts subset.

myproductssubset.png

Figure 6.    MyProducts Subset

Entering data

Back in the Slice, click on the cell at the intersection of 2008 Unit Cost and Aunt Re’s Chicken. We want to calculate the cost of Sales, Revenue and Margin--costs which we know are based partly on Unit Cost and Unit Price, and which are recorded here in this Product cube. 2009 Unit Costs are based on a 25-cent increase over 2008 numbers for every product. Thus, in this cell, we will enter $12.24.

  1. Enter 12.24 in the cell.

  2. Press Enter.

  3. Continue adding data, 25 cents higher than the 2008 Unit Cost, as above, to the products’ 2009 Unit Cost Column.

practicescenario25centincrease.png

Figure 7.    25 Cent Increase in 2009 Unit Cost

The other element of our calculation 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 from the 2000 price ($21.29 became $21.99).

  1. Enter 21.99 for 2009 Unit Price for Aunt Re’s Chicken.

  2. Continue down the list of products, rounding the 2009 Unit Price to the next $.99.

The resulting slice should appear as follows:

practicecenario99centsunitcost2009.png

Figure 8.    2009 Unit Cost - Rounded up to $xx.99

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

  1. Click New Slice to open the New Slice Dialog.

  2. Select the Margin cube and click OK.

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

The initial Slice view of the Margin Cube fills the proCube application window, covering the Product slice and the Sales slice (if open).

The initial Margin slice view shows 2007 Actual data for USA and Aunt Re’s Chicken, shown by Month and by Margin Account.

Changing the slice view

For demonstration purposes, we want to alter this Slice to show us the same array of Products in rows, and Years (2008 and 2009) in columns from the Product slice. In addition, we want to see the Margin Accountdimension nested in the Year dimension in columns. We can also change the way the Margin Account dimension is ordered to better suit our needs:

  1. Pivot the dimensions of the Margin slice so that Product is in rows, Margin Account is nested in Year in columns, and the other Dimensions 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. Move the Members of the Margin Account dimension so that they are listed in this order: Quantity, Unit Cost, Unit Price, Cost of Sales, Revenue, Margin, Margin %.

The resulting slice looks like this (2008 columns are hidden to the left):

2009pivotdimensionsubsetsmarginslice.png

Figure 9.    Pivoted Dimensions with Subsets, Margin Slice

Observe the following important points about the Margin cube:

  • The data we entered in the Product slice for 2009 Unit Cost and Unit Price have been added into the database and are now reflected in this slice.

  • Scroll so you can see 2010 accounts. Notice our 2009 Quantity for Aunt Re’s Chicken, 5, comes from our entry in the Sales cube slice Sales Accounts by Months. 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 the Month Dimension).

  • Quantity is the only column of data allowing you to input data (but not for 2009 data, because 2009 Quantity data is driven by entry into the Sales cube through our Sales Account by Month slice). All other data 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).

[Optional] Save/name the Margin and Product slices.

Updating and viewing values

Now, to demonstrate the true power of proCube’s near-instantaneous capability to update cubes:

  1. 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 using the MyProducts subset so that the slice looks like this:

salescubeslice.png

Figure 10.           A Rearranged Sales Slice

  1. Enter Quantity data for products lacking values.

  2. Press F9.

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

  1. Click the Margin slice's tab to bring it to the front.

  2. 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!

newmargincubeslice.png

Figure 11.           New Margin Cube Slice - 2009

We have demonstrated the dynamic link between the Product and Margin cubes (Unit Price and Unit Cost come from Product), between the Sales and Margin  cubes (Actual 2009 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 proCube’s power and flexibility in data modeling with its ease in data entry, instant updating, slice pivoting and subset creation—feature that allow us to view data exactly how we need to see it.

Now that we have linked data for 2009 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 2010 Unit Cost and Unit Price at the beginning of this exercise.

  1. In the Margin slice (which we have saved and named Product by M Account & Year), scroll over so that 2010 Margin Accounts are visible.

Quantity values are the only values we may key in manually in this view, since the other values are “read only” (i.e., governed by formulas and brought in from other cubes).

  1. Enter data in the Quantity column for several products.

  2. Press F9.

Observe the automatic values returned in the calculation columns, providing us with much insight into our products’ performance so far in 2010. In our model, Pies, Coffee and Ice cream did particularly well for Acme Trading Company in January 2010 in the USA.

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk