Paste and Drill Through Functions

 

Paste Function

 

 

Paste

The Paste button allows you to paste a copied data value to another cell or a range of cells in a sliced OLAPTable, OLAPPivot or OLAPWriteTable worksheet. These types of PowerOLAP sliced worksheets require use of this special button to paste values because of the data entry qualities from Excel back to the PowerOLAP database. The Paste function, however, cannot be used in an OLAPReadWrite worksheet because of the cell reference formulas governing the cells.

1.   From the PowerOLAP application, choose the OLAPTable option as the Excel function via the Options command then create an excel worksheet out of a PowerOLAP slice (e.g., Sales Acount by Month) by pressing F8.

2.   Click on a cell containing Fact Data,  
e.g., on the July, Quantity value you entered previously, 1000.

3.   Copy this value as you normally would in Excel, by using Excel’s Copy button, , or by pressing Ctrl-C.

4.   Choose a destination cell for this copied value and highlight it,
e.g., to the cell containing May, Quantity data.

5.   From the Excel ribbon, go to the Add-Ins tab and click the Paste button to put the value into the chosen cell.

6.   Press F9 to recalculate. The July Quantity value is successfully copied to May.

Drill-Through Function

Drill
Through

The Drill-Through button allows you to see the “source” of an Aggregate data value, shown as list of Detail Member values in a new worksheet within the same workbook. (Note: you can even “drill-through” into the relational source of data if you are using OLAP Exchange® component—see the OLAP Exchange® section).

1.   Click on an Aggregate data value, such as Total Year, Quantity, I.e., cell B21.

2.   From the Excel ribbon, go to the Add-Ins tab  and click the Drill-Through button. The following pop-up window appears:

Alternatively you may also do the following to perform a drill-through on a certain aggregate cell directly from Excel:

  • Click on the corresponding aggregate cell in Excel, go to the Add-Ins tab of the Excel ribbon, click the PowerOLAP drop down menu and select Drill Through option.

Since we are not using OLAP Exchange®, i.e., we are not integrating data from a relational database, the two check boxes are grayed out, and the area below does not show any information. (If OLAP Exchange® were being used, we would be able to use the check boxes and Dimensions and Values/Display Order to show specific transactional values )

3.   Click OK.

This action automatically creates an additional Excel worksheet in the workbook showing the “sources” values of Total Year Quantity value listed, as below:

The four columns A – D across row 1 are the Dimensions Member labels. The Aggregate value itself is listed in E2. The Members that comprise that Aggregate value and each individual Fact Data value are listed beneath, in cells A3:E9.

Each additional Drill-Through action performed on a sliced worksheet creates additional worksheets.

 Tip

You can now save and name the Excel worksheet as you normally would (as an XLSX file), and this worksheet will remain connected to the PowerOLAP database.